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();
		}
	}
	JdbcUtil.closeJdbc(rs);
	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时容易引发内存溢出的情况，请妥善处理。

---

喜欢可以关注公众号: 终身幼稚园

转载于:https://juejin.im/post/5d077df5518825412e53f95a