Excel导出百万级数据解决方案

因项目业务，需要导出百万级数据到excel，在研究了各种方案后，最终确定了用POI的SXSSFWorkbook。

SXSSFWorkbook是POI3.8以上新增的，excel2007后每个sheet支持104万行数据，基于此条件，将得到数据进行分页创建；

并且代码还要通用，无论你传递过来什么对象，多少列都要正常显示。

具体将excel分成4大区域：

1. 标题（title）
2. 查询条件（condition）：具体封装为map
3. 列头 （headList）：以bean队列形式传递，name为显示的中文名称，column为实际属性
4. 数据（dataList）：以map队列形式传递，key为实际属性（和head中的column要对应），value为实际值（特殊值建议提前格式化）

具体代码如下，只要注意headList中的column和dataList中的column对应，相信能适合大多数场景：

• 工具类

  “`

package com.cmos.utils;

import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.TreeMap;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.BorderStyle;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.FillPatternType;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.VerticalAlignment;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import com.cmos.utils.ExcelData.Head;

/**

• 导出excel，支持海量数据，可分sheet
• 实测：50万数据 40s左右
• 100万数据 80秒左右
• （具体和运行环境有关）
• @author zsx

• */

  public class ExcelUtil {

  final int memorySize = 100;//内存中只创建100个对象，写临时文件，当超过100条，就将内存中不用的对象释放

  final int sheetRowMax = 200000;// 单sheet最大行数，用于分页

  /**

  • 动态创建excel，将所有数据以ExcelData方式传递进来：
  • title：标题，用于显示在最上方
  • condition：查询条件，以map形式传递
  • headList：列头，以bean队列形式传递，name为显示的中文名称，column为实际属性
  • dataList：数据，以map队列形式传递，key为实际属性（和head中的column要对应），value为实际值（特殊值建议提前格式化）

    */

    public void export(ExcelData data,File file) throws IOException{

    long st = System.currentTimeMillis();

    ExcelUtil.judeFileExists(file);

    ExcelUtil eu = new ExcelUtil();

    OutputStream out = new FileOutputStream(file.getPath());

    eu.exportExcel(data, out);

    out.close();

    long et = System.currentTimeMillis();

    System.out.println(“export excel:”+(et-st)+”ms”);

    }

  /**

  • 导出核心
  • @param data

  • @param out

    */

    private void exportExcel(ExcelData data, OutputStream out) {

    // 校验

    if (data == null || data.getHeadList() == null || data.getDataList() == null) {

    System.out.println(“准备数据不能为空”);

    return;

    }

    // 数据准备

    String title = data.getTitle();

    Map

    //为防止数据量过大导致OOM，POI3.8以上支持大数据导出

    SXSSFWorkbook workbook = new SXSSFWorkbook(memorySize);

    Sheet sheet = null;

    int sheetSize = 1;

    if (dataList.size() > sheetRowMax) {

    if (dataList.size() % sheetRowMax == 0) {

    sheetSize = dataList.size() / sheetRowMax;

    } else {

    sheetSize = dataList.size() / sheetRowMax + 1;

    }

    }

    // 设置样式

    CellStyle titleStyle = getTitleStyle(workbook);

    CellStyle HeadStyle = getHeadStyle(workbook);

    CellStyle dataStyle = getDataStyle(workbook);

    // 设置数据

    for (int i = 0; i < sheetSize; i++) {

    int startInx = sheetRowMaxi;

    int endInx = sheetRowMax(i+1)-1;

    //获取当页数据

    if(sheetRowMax*(i+1)-1 > dataList.size()){

    endInx = dataList.size();

    }

    List

    }

    try {

    workbook.write(out);

    } catch (IOException e) {

    e.printStackTrace();

    }

  }

  /**

  • 合并单元格
  • @param sheet
  • @param headSize

  • @param conditionSize

    */

    private void mergeCell(Sheet sheet, int headSize, int conditionSize) {

    //TODO 合并后样式被改变，需要进行优化 by zsx 20170717

    CellRangeAddress titleCra = new CellRangeAddress(0, 0, 0, headSize – 1);

    sheet.addMergedRegion(titleCra);

    for (int i = 0; i < conditionSize; i++) {

    CellRangeAddress conditionKeyCra = new CellRangeAddress(i + 1, i + 1, 0, 1);

    sheet.addMergedRegion(conditionKeyCra);

    CellRangeAddress conditionValueCra = new CellRangeAddress(i + 1, i + 1, 2, 3);

    sheet.addMergedRegion(conditionValueCra);

    }

  }

  /**

  • 创建数据
  • @param sheet
  • @param cellStyle
  • @param i
  • @param dataList

    */

    private void createDataRow(Sheet sheet, CellStyle cellStyle, int index,List headList, List

  /**

  • 创建列头
  • @param sheet
  • @param cellStyle
  • @param j
  • @param headList

    */

    private void createHeadRow(Sheet sheet, CellStyle cellStyle, int index, List headList) {

    Row row = sheet.createRow(index);

    row.setHeight((short) 300);

    for(int i=0;i<headList.size();i++){

    Cell cell = row.createCell(i);

    cell.setCellStyle(cellStyle);

    XSSFRichTextString text = new XSSFRichTextString(headList.get(i).getName());

    cell.setCellValue(text);

    }

    }

  /**

  • 创建查询
  • @param sheet
  • @param cellStyle
  • @param condition

    */

    private void createConditionRow(Sheet sheet, CellStyle cellStyle, Map

  }

  /**

  • 设置列宽
  • @param sheet
  • @param headList

    */

    private void setSheetWidth(Sheet sheet, List headList) {

    for (int i = 0; i < headList.size(); i++) {

    sheet.setColumnWidth(i, 4000);

    }

    }

  /**

  • 创建标题
  • @param sheet
  • @param cellStyle
  • @param title

    */

    private void createTitleRow(Sheet sheet, CellStyle cellStyle, String title) {

    Row row = sheet.createRow(0);

    row.setHeight((short) 500);

    Cell cell = row.createCell(0);

    cell.setCellStyle(cellStyle);

    XSSFRichTextString text = new XSSFRichTextString(title);

    cell.setCellValue(text);

    }

  /**

  • 设置标题样式
  • @param workbook

  • @return

    */

    private CellStyle getTitleStyle(SXSSFWorkbook workbook) {

    // 生成一个样式(标题行)

    CellStyle headStyle = workbook.createCellStyle();

    // 设置这些样式

    headStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    headStyle.setBorderBottom(BorderStyle.THIN);

    headStyle.setBorderLeft(BorderStyle.THIN);

    headStyle.setBorderRight(BorderStyle.THIN);

    headStyle.setBorderTop(BorderStyle.THIN);

    headStyle.setAlignment(HorizontalAlignment.CENTER);

    headStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    // 生成一个字体

    Font headFont = workbook.createFont();

    headFont.setFontHeightInPoints((short) 16);

    headFont.setFontName(“Microsoft YaHei UI Light”);

    headFont.setBold(true);

    headFont.setBold(true);

    // 把字体应用到当前的样式

    headStyle.setFont(headFont);

    return headStyle;

    }

  /**

  • 设置列头样式
  • @param workbook

  • @return

    */

    private CellStyle getHeadStyle(SXSSFWorkbook workbook) {

    // 生成一个样式(标题行)

    CellStyle headStyle = workbook.createCellStyle();

    // 设置这些样式

    headStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);

    headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    headStyle.setBorderBottom(BorderStyle.THIN);

    headStyle.setBorderLeft(BorderStyle.THIN);

    headStyle.setBorderRight(BorderStyle.THIN);

    headStyle.setBorderTop(BorderStyle.THIN);

    headStyle.setAlignment(HorizontalAlignment.CENTER);

    headStyle.setAlignment(HorizontalAlignment.CENTER);

    // 生成一个字

    Font headFont = workbook.createFont();

    headFont.setFontHeightInPoints((short) 12);

    headFont.setFontName(“Microsoft YaHei UI Light”);

    headFont.setBold(true);

    // 把字体应用到当前的样式

    headStyle.setFont(headFont);

    return headStyle;

    }

  /**

  • 设置数据样式
  • @param workbook

  • @return

    */

    private CellStyle getDataStyle(SXSSFWorkbook workbook) {

    // 生成一个样式(标题行)

    CellStyle headStyle = workbook.createCellStyle();

    // 设置这些样式

    headStyle.setFillForegroundColor(HSSFColor.WHITE.index);

    headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    headStyle.setBorderBottom(BorderStyle.THIN);

    headStyle.setBorderLeft(BorderStyle.THIN);

    headStyle.setBorderRight(BorderStyle.THIN);

    headStyle.setBorderTop(BorderStyle.THIN);

    headStyle.setAlignment(HorizontalAlignment.CENTER);

    // 生成一个字体

    Font headFont = workbook.createFont();

    headFont.setFontHeightInPoints((short) 12);

    headFont.setFontName(“Microsoft YaHei UI Light”);

    // 把字体应用到当前的样式

    headStyle.setFont(headFont);

    return headStyle;

    }

  /**

  • 判断文件是否存在
  • @param file

    */

    public static void judeFileExists(File file) {

    if (!file.exists()) {

    try {

    file.createNewFile();

    } catch (IOException e) {

    e.printStackTrace();

    }

    }

    }

  /**

  • 使用 Map按key进行排序
  • @param map
  • @return

    */

    public static Map

}

- 测试用例

package test.cmos;

import java.io.File;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.junit.Test;

import com.cmos.utils.ExcelData;

import com.cmos.utils.ExcelData.Head;

import com.cmos.utils.ExcelUtil;

public class ExcelTest{

//模拟数据量static int dataSize = 500000;@Testpublic void testExport() throws IOException {    ExcelData data = createData();    new ExcelUtil().export(data, new File("E:/data.xlsx"));}/** * 模拟生成数据 * @return */private static ExcelData createData() {    long a = System.currentTimeMillis();    ExcelData data = new ExcelData();    data.setTitle("商户对账差异明细");    Map<String, String> c = new HashMap<String, String>();    c.put("来源系统", "10085销售订单");    c.put("对账日期", "2016-11-03到2016-11-05");    c.put("商户", "华为");    data.setCondition(c);    List<Head> headList = new ArrayList<Head>();    headList.add(new Head("来源系统", "a"));    headList.add(new Head("批次号", "b"));    headList.add(new Head("商户编号", "c"));    headList.add(new Head("商户名称", "d"));    headList.add(new Head("业务日期", "e"));    headList.add(new Head("订单号", "f"));    headList.add(new Head("我方金额", "g"));    headList.add(new Head("对方金额", "h"));    headList.add(new Head("差额", "i"));    data.setHeadList(headList);    List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();    for (int i = 0; i < dataSize; i++) {        Map<String, Object> map = new HashMap<String, Object>();        map.put("a", "a" + i);        map.put("b", "b" + i);        map.put("c", "c" + i);        map.put("d", "d" + i);        map.put("e", "e" + i);        map.put("f", "f" + i);        map.put("g", "g" + i);        map.put("h", "h" + i);        map.put("i", "i" + i);        dataList.add(map);    }    data.setDataList(dataList);    long b = System.currentTimeMillis();    System.out.println("create data:"+(b-a)+"ms");    return data;}

}

- 传入的导出对象

package com.cmos.utils;

import java.util.List;

import java.util.Map;

public class ExcelData {

//标题

private String title;

//查询条件

private Map

public String getTitle() {    return title;}public void setTitle(String title) {    this.title = title;}public List<Head> getHeadList() {    return headList;}public void setHeadList(List<Head> headList) {    this.headList = headList;}public List<Map<String, Object>> getDataList() {    return dataList;}public void setDataList(List<Map<String, Object>> dataList) {    this.dataList = dataList;}public Map<String, String> getCondition() {    return condition;}public void setCondition(Map<String, String> condition) {    this.condition = condition;}public static class Head{    private String name;    private String column;    public Head(){    }    public Head(String name,String column){        this.name = name;        this.column = column;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getColumn() {        return column;    }    public void setColumn(String column) {        this.column = column;    }}

}

“`