五分钟终结 Java 导出大数据 Excel 问题

Java当中常用的Excel文档导出主要有POI、JXL和“直接IO流”这三种方式，三种方式各自分别有不同的优势与缺点，下面将分行对其进行简单介绍。

常用格式

1. Excel2003格式

Excel2003支持每个工作表中最多有 65536 行和 256列。对于工作表能够支持的最大个数，受CPU反应速度和内存大小影响。采用常用导出方式导出数据时，需要注意的是Excel 2003行数和列数的限制。常用导出方式中的POI支持该格式的只有HSSF包，当导出数据量大于一页的最大行数（65536）时，可采取分页的形式进行存储。

常用导出方式推荐：1）POI；2）JXL。

对于推荐的导出方式对Excel 2003的支持，都对最大导出数据量做了限制，不能实现百万级大数据量的导出。

2. Excel2007格式

Excel 2007是Excel 2003的升级版，Excel 2007支持每个工作表中最多有 1,048,576 行和 16,384 列。采用常用导出方式导出数据时，需要注意的是Excel 2007行数和列数的限制，常用导出方式支持该格式的只有XSSF包，包含SXSSF扩展包，并且仅有SXSSF支持大数据。

常用导出方式推荐：1）POI3.8版本以上。

3. CSV格式

CSV是一种通用的、相对简单的文件格式，广泛的应用在程序之间转移表格数据。

它通常具有以下特征：

纯文本，可以使用Excel和文本编辑器打开；
每条记录被分隔符分隔为字段（典型分隔符有逗号、分号或制表符；有时分隔符可以包括可选的空格）；
常用导出方式不会引起内存溢出问题。

常用导出方式推荐：1）直接IO流。

导出方案

1. 使用Apache POI SXSSFWorkbook方式进行导出Excel

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。Apache的POI组件是Java操作Microsoft Office办公套件的强大API，由于Office 2007的文件结构完全不同于2003，POI有不同的处理API，当导出格式为Office 2003时POI调用的是HSSF包，当导出格式为Office 2007时，则调用XSSF包，而SXSSF包是POI3.8版本之上对XSSF的一个扩展，用于大数据量的导出，实际应用中可有效避免内存溢出的问题。

此方式对不仅支持对Excel的操作，也支持对Microsoft Office其他格式档案的操作，如：Word、PowerPoint等，支持单元格的复杂操作，并且该方式在读取效率上远远优于JXL方式，在数据处理量级上也远非JXL方式可比。

推荐使用情况：1大数据；2复杂的单元格要求；3）读取数据时。

具体使用调用POI何种包来实现导出功能，需要视需求而定。

代码实现：

public void exportExcle() {
    // 设置存储在内存的行数，多余的存储在硬盘
    int cacheItems = 100;
    Workbook wb = new SXSSFWorkbook(cacheItems);
    Sheet sh = wb.createSheet();
    // 查询数据
    ResultSet rs = queryData();
    int rownum = 0;
    while(rs.next()){
      Row row = sh.createRow(rownum);
      Cell cell = row.createCell(0);
      cell.setCellValue(rs.getString("c1"));
      cell = row.createCell(1);
      cell.setCellValue(rs.getString("c2"));
      ……
      cell = row.createCell(19);
      cell.setCellValue(rs.getString("c20"));
      rownum ++;
      // 每当行数达到设置的值就刷新数据到硬盘,以清理内存
      if(rownum % cacheItems == 0){
          ((SXSSFSheet)sh).flushRows();
      }
    }
    FileOutputStream out = new FileOutputStream("/excel_data.xlsx");
    wb.write(out);
    out.close();
}

2. 使用缓冲流生产csv格式文件

流是一组有顺序的，有起点和终点的字节集合，是对数据传输的总称或抽象。即数据在两设备间的传输称为流，流的本质是数据传输，根据数据传输特性将流抽象为各种类，方便更直观的进行数据操作。

而上面提到的POI和JXL实际都是基于IO流封装的工具。该方式采用最原始的形式进行导出工作，选择合适的流工作效率会非常出色。 但是仅支持对文本文件的操作，如：CSV、TXT等，且导出的文件会相对较大。

推荐使用情况：1）导出文件格式为文本文件；2）不在意导出的文件过大。

代码实现：

public void exportExcle() {
    File csvFile = null;
    BufferedWriter csvWriter = null;
    csvFile = new File("/excel_data.csv");
    File parent = csvFile.getParentFile();
    if (parent != null && !parent.exists()) {
        parent.mkdirs();
    }
    csvFile.createNewFile();

    // UTF-8使正确读取分隔符","
    csvWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            csvFile), "UTF-8"), 1024);
    // 查询数据
    ResultSet rs = queryData();
    while(rs.next()){
        StringBuffer sb = new StringBuffer();
        sb.append(""").append(rs.getString("c1")).append("";");
        sb.append(""").append(rs.getString("c2")).append("";");
        ……
        sb.append(""").append(rs.getString("20")).append(""");
        csvWriter.write(sb.toString());
        csvWriter.newLine();
    }
    csvWriter.close();
}

3. 使用JXL方式导出EXCEL

JXL是一个开源的Java Excel API项目。它能作为Java Excel API的一个共同的支持库，是因为它的基本功能是可创建，读取和写入电子表格。基本特征如下：

生成Excel文件
从工作簿和电子表格导入数据
获得行和列的总数此方式对中文支持很好，不会出现乱码情况，支持单元格的常用操作，满足一般需求，该方式在写入效率上优于POI方式。

但是需要注意：JXL只支持xls档案格式，并且处理的数据量非常有限。

推荐使用情况：1）数据量不是太大；2）单元格要求简单；3）写入数据时。

代码实现：

public void exportExcle() {
    WorkbookSettings wbSetting = new WorkbookSettings();
    wbSetting.setUseTemporaryFileDuringWrite(true);
    // 临时文件夹的位置
    wbSetting.setTemporaryFileDuringWriteDirectory(new File("/"));
    WritableWorkbook book = jxl.Workbook.createWorkbook(new File("/excel_data.xls"),wbSetting);

    // 设置样式，字体
    WritableFont font1 = new WritableFont(WritableFont.createFont("微软雅黑"), 10 ,WritableFont.BOLD);
    WritableFont font2 = new WritableFont(WritableFont.createFont("微软雅黑"), 9 ,WritableFont.NO_BOLD);
    WritableCellFormat wcf = new WritableCellFormat(font1);
    WritableCellFormat wcf2 = new WritableCellFormat(font2);

    // 平行居中
    wcf.setAlignment(Alignment.CENTRE);
    // 垂直居中
    wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
    wcf2.setAlignment(Alignment.CENTRE);
    wcf2.setVerticalAlignment(VerticalAlignment.CENTRE);


    // 查询数据
    ResultSet rs = queryData();
    int rownum = 0;
    // SHEET页大小，最大不超过65536
    int sheetCount = 50000;
    int sheetNum = 0;
    WritableSheet sheet = null;
    while(rs.next()){
        if(rownum % sheetCount == 0){
            sheetNum ++;
            rownum = 0;
            // 生成名为“第一页”的工作表，参数0表示这是第一页
            sheet = book.createSheet( "第"+ sheetNum +"页 " , sheetNum - 1);
        }
        // 页设置对象
        sheet.addCell(new Label( 0 , rownum, rs.getString("c1"), wcf2));
        sheet.addCell(new Label( 1 , rownum, rs.getString("c2"), wcf2));
        ...
        sheet.addCell(new Label( 18 , rownum, rs.getString("c19"), wcf2));
        sheet.addCell(new Label( 19 , rownum, rs.getString("c20"), wcf2));
        // 设置行高
        sheet.setRowView(rownum, 370);
        rownum ++;
    }

    // 写入数据并关闭文件
    book.write();
    book.close();
}