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

它通常具有以下特征:

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();
}

总结

对于以上三种解决大数据量导出的方案,由于第三种解决方案限制了能够导出的最大数据量,故此不作推荐。

方案一和方案二对于大数据量的导出具有很好的支持,可见于对导出格式、占用的存储空间等要素来选择更适合的方案。

需要特别注意的是,两种方案都涉及到从数据库提取数据装载到List时容易引发内存溢出的情况,请妥善处理。

原文发布于微信公众号 - 终身幼稚园(gh_642061c72cce)

原文发表时间:2019-06-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券