JAVA大数据导出EXCEL方案

终身幼稚园

发布于 2019-07-19 16:56:44

6K1

发布于 2019-07-19 16:56:44

文章被收录于专栏：终身幼稚园终身幼稚园

前言

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是一种通用的、相对简单的文件格式，广泛的应用在程序之间转移表格数据。

它通常具有以下特征：

1）纯文本，可以使用Excel和文本编辑器打开；

2）每条记录被分隔符分隔为字段（典型分隔符有逗号、分号或制表符；有时分隔符可以包括可选的空格）；

3）常用导出方式不会引起内存溢出问题。

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

导出方案

一、使用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();
}

二、使用缓冲流生产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();
}

三、使用JXL方式导出EXCEL。

方案简介

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

基本特征如下：

1）生成Excel文件

2）从工作簿和电子表格导入数据

3）获得行和列的总数此方式对中文支持很好，不会出现乱码情况，支持单元格的常用操作，满足一般需求，该方式在写入效率上优于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();
}