Java 利用POI对象 SXSSFWorkbook 导出Excel

利用 SXSSFWorkbook 生成导出.xlsx文件

一、基础介绍请戳：CSV与Excel的对比，POI导出Excel的数据量限制，HSSF、XSSF、SXSSF的区别

二、SXSSFWorkbook：低内存占用的工作簿

引用官方的介绍，简单概括就是：
SXSSF是对XSSF的一种流式扩展，特点和原理是采用了滑动窗口的机制，低内存占用，主要用于数据量非常大的电子表格而虚拟机堆有限的情况。

SXSSFWorkbook.DEFAULT_WINDOW_SIZE默认值是100，表示在内存中最多存在100个Row对象，当写第101个Row对象的时候就会把第1个Row对象以XML格式写入C:\Users\wange\AppData\Local\Temp路径下的临时文件中，后面的以此类推，始终保持内存中最多存在100个Row对象。

SXSSFWorkbook默认使用内联字符串而不是共享字符串表(SharedStringsTable)。启用共享字符串时，文档中的所有唯一字符串都必须保存在内存中，因此XSSF会占用更多的内存。

与XSSF的对比，在一个时间点上，只可以访问一定数量的Row；不再支持Sheet.clone()；不再支持公式的求值。但是除了滑动窗口，其余的EXCLE操作仍然使用的是XSSF的API。

官方提示导出EXCEL后应该调用wb.dispose()来删除之前保存的临时文件。

三、SXSSFWorkbook导出Excel示例

通过poi导出excel的过程大致是这样的：

• 导入POI的jar包，使用对应的POI对象（本篇选择SXSSFWorkbook）
• 创建 sheet 表
• 创建 row 行
• 创建 cell 每行的单元格（可设置数据的格式，和单元格的格式）
• 也可在所有数据写好之后，整体对某列某行来设置格式
• 通过IO流输出

SXSSFWorkbook的使用，与HSSFWorkbook基本一致，只需要换掉对应的Sheet、Row、Cell等类就可以了；

sheet，row，cell在建立的时候Index都是从0开始的；

/**
 * 生成导出.xls文件，使用SXSSFWorkbook
 */
public static File createExcelFile(List<Map<String, String>> exportData,
            Map<String, String> rowMapper, String outPutPath,
            String excelFileName) {
    File excelFile = null;
    FileOutputStream fOut = null;
    SXSSFWorkbook wb = null;
    try {
        excelFile = File.createTempFile(excelFileName, ".xls", new File(outPutPath));
        //wb对象
        wb = new SXSSFWorkbook();
        //创建sheet对象
        Sheet sheet = wb.createSheet();
        //设置列默认的宽度
        sheet.setDefaultColumnWidth(15);
        //创建表头行
        Row rowHead = sheet.createRow(0);
        //设置表头行内容，可以在这里对表头设置一些样式，标红呀，加粗之类的
        //样式代码在下面有示范
        int i = 0;
        for (String str : rowMapper.values()) {
            Cell cellHead = rowHead.createCell(i);
            cellHead.setCellValue(str);
            i++;
        }

单元格格式的创建需在循环体外，不然数据量过大时会报错：“The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook”

	    //设置表格主内容
	    //单元格格式的创建需在循环体外，不然数据量过大时会报错
	    CellStyle textStyle = wb.createCellStyle();
	    //用于格式化单元格的数据
	    DataFormat format = wb.createDataFormat();
	    //设置为单元格文本格式
	    textStyle.setDataFormat(format.getFormat("@"));
	    //不换行
		textStyle.setWrapText(false);
		int j = 0;
		for (Map<String, String> datamap : exportData) {
		    Row row = sheet.createRow(j + 1);
		    int k = 0;
		    for (String str : datamap.keySet()) {
		        Cell cell = row.createCell(k);
	  	      //判断针对单号数据，需要设置单元格格式为文本格式，避免科学计数法
	   	        if (("身份证号").equals(rowMapper.get(str))) {
	            	cell.setCellStyle(textStyle);
	            	cell.setCellValue(datamap.get(str));
	            	cell.setCellType(Cell.CELL_TYPE_STRING);
	        	} else {
	            	cell.setCellValue(datamap.get(str));
	        	}
	        	k++;
	 		}
	 		j++;
		}  
	    fOut = new FileOutputStream(excelFile);
	    //写内容，xls文件已经可以打开
	    wb.write(fOut);
	    //刷新缓冲区
	    fOut.flush();
    } catch (IOException e) {
        logger.error("export IOException");
    } finally {
        try{
           fOut.close();
           //删除之前保存的临时文件
           wb.dispose();
        }catch (Exception ex){
           logger.error("export io exception");
        }
    }
    return excelFile;
}

四、SXSSF单元的样式设置，标红加粗之类

// 设置单元格各种样式
// 设置字体
Font font = wb.createFont();
//字体高度
font.setFontHeightInPoints((short) 11); 
//字体颜色
font.setColor(Font.COLOR_NORMAL); 
//字体
font.setFontName("宋体"); 

//设置单元格里的字体样式，使用上面设置对字体样式，以及设置单元格的格式
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
//水平布局：居中
cellStyle.setAlignment(CellStyle.ALIGN_CENTER); 
//单元格垂直居中
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//换行
cellStyle.setWrapText(true);

五、对于身份证等长数字数据，设置CELL单元格为文本格式

/**
*设置CELL格式为文本格式  
*/
CellStyle cellStyle = wb.createCellStyle();  
DataFormat format = wb.createDataFormat();  

//"@"是指文本的数据格式，主要是这段代码
cellStyle.setDataFormat(format.getFormat("@"));  
cell.setCellStyle(cellStyle);  
cell.setCellValue(你的值);
  
//CELL_TYPE_STRING是单元格格式，这里写不写没区别…… 原因如下解释
cell.setCellType(Cell.CELL_TYPE_STRING);

踩坑点：

• 通常大家都是想到既然是设置CELL格式肯定是通过cell.setCellType(HSSFCell.CELL_TYPE_STRING)然后插入数据再导出，诚然这种想法是对的，实际上不能起到任何作用，因为这个方法就是EXCEL默认的格式，写不写都一样;
• 如果数据量大的话，系统可能会报错“The maximum number of cell styles was exceeded. You can define up to 4000 styles in a .xls workbook”，原因是cellStyle创建的次数太多了，解决这个问题的方法很简单，在循环体外面创建单元格格式（即把它当成一个“全局”变量），不要在循环内部创建；
• 自调节单元格尺寸方法 autoSizeColumn(), api描述说数据较大时耗时非常大, 建议在结果的时候调用一次。（ps：autoSizeColumn会遍历每一列的每一行数据获取最大长度）
  
  

六、自定义写临时文件规则：SXSSFWorkbook wb = new SXSSFWorkbook(-1)

初始化设置为-1的时候我们可以自己定义写临时文件规则，比如每读1000行记录flush到临时一次，可以大大减少磁盘IO次数。

七、拼好Excel之后，文件下载方法：

方法1：

//创建xls文件，无内容 0字节
FileOutputStream fOut = new FileOutputStream(xlsFile);        
//写内容，xls文件已经可以打开
wb.write(fOut);                            
//刷新缓冲区
fOut.flush();
//关闭                            
fOut.close();                            

方法2：

 //生成流对象
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();    
//将excel写入流        
wb.write(byteArrayOutputStream);                                
//工具类，封装弹出下载框：        
String outFile = "excelFile.xls";
DownloadBaseAction down = new DownloadBaseAction();
down.download(byteArrayOutputStream, response, outFile);

方法3：（适用于struts2）

ServletActionContext.getResponse().setContentType("application/octet-stream");
String returnName = ServletActionContext.getResponse().encodeURL( new String("excelFile.xls".getBytes(), "ISO-8859-1"));
ServletActionContext.getResponse().addHeader("Content-Disposition", "attachment;filename=" + returnName);
wb.write(ServletActionContext.getResponse().getOutputStream());

八、SXSSFWorkbook 没有提供读取文件流的方法。因此读入大数据量的时候还是只能使用XSSFWorkbook来读取

OPCPackage pkg = OPCPackage.open(filename);
XSSFReader xssfReader = new XSSFReader(pkg);

使用SAX模型来解析EXCEL不像DOM模型一下把所有文件内容加载进内存，它逐行扫描文档，一边扫描，一边解析。所以那些只需要单遍读取内容的应用程序就可以从SAX解析中受益，这对大型文档的解析是个巨大优势。

参考链接