Java使用POI的SXSSFWorkbook与HSSFWorkbook导出复杂表头
一、HSSFWorkbook与SXSSFWorkbook的区别：
HSSFWorkbook是对Excel2003以前的版本进行操作的，即后缀名为.xls
SXSSFWorkbook时对Excel2007的版本就行操作，即后缀名为.xlsx
ps:
1、SXSSFWorkbook是在POI3.8版本开始提供基于XSSF低内存占用的SXSSF
SXSSF通过一个滑动窗口来限制访问Row的数量从而达到低内存占用的目录，XSSF可以访问所有行。SXSSF通过设置rowAccessWindowSize的大小实现滑动窗口中存储数据量的row数量，当行数超过滑动窗口设定的值时，此时旧的数据不再出现在滑动窗口且不可对此进行
访问，同时将此数据写入磁盘。也可通过flushRow(int keepRows)设定行数通过周期性的调用来修改。
注意：针对 SXSSF Beta 3.8下，会有临时文件产生，比如：
poi-sxssf-sheet4654655121378979321.xml
文件位置：java.io.tmpdir这个环境变量下的位置
Windows 7下是C:\Users\xxxxxAppData\Local\Temp
Linux下是 /var/tmp/
2、当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook
二、所需jar包
1、SXSSFWorkbook所需jar包
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.1</version> </dependency> <dependency> <groupId>org.apache.xmlbeans</groupId> <artifactId>xmlbeans</artifactId> <version>2.6.0</version> </dependency>
2、HSSFWorkbook所需jar
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>
三、代码实例：
1、SXSSFWorkbook：
package com.test.excel; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; public class SXSSFExecelPOI { public static void main(String[] args) { try { boolean createExcelFile = createExcelFile(); if (createExcelFile) { System.out.println("SXSSF_excel导出成功"); } else { System.out.println("SXSSFexcel导出失败"); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static boolean createExcelFile() throws Exception { boolean state = false; OutputStream os = null; FileOutputStream fos = null; String resultFileName = null; String fileName = ""; try { org.apache.poi.ss.usermodel.Workbook wb; fileName = "kemu.xlsx"; wb = createReportWorkbook(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); resultFileName = "E:\\" + File.separator + "excel" + File.separator + sdf.format(new Date()) + File.separator + fileName; resultFileName = resultFileName.replaceAll("\\\\", "/"); File file = new File(resultFileName); if (!file.exists()) { file.getParentFile().mkdirs(); } fos = new FileOutputStream(file); os = new BufferedOutputStream(fos, 1024); wb.write(os); os.flush(); state = true; } finally { if (os != null) os.close(); } return state; } private static Workbook createReportWorkbook() { String[] titles = { "时间", "科目", "科目", "科目", "科目", "备注" }; String[] handClum = { "0,1,0,0", "0,0,1,4", "0,1,5,5" }; String[] titles2 = { "时间", "科目一", "科目二", "科目三", "科目四", "备注" }; // 100 指定Excel在屏幕尺寸下可滑动数据为100条 SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格垂直居中 Sheet sheet = wb.createSheet("sheet1"); for (int i = 0; i < titles.length; i++) { sheet.setColumnWidth(i, 5000); } Row row = sheet.createRow(0);// 创建表头1 for (int i = 0; i < titles.length; i++) { Cell cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(titles[i]); } // 动态合并单元格 for (int i = 0; i < handClum.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = handClum[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(1);// 创建表头2 for (int i = 0; i < titles2.length; i++) { Cell cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(titles2[i]); } List<keMu> kemus = new ArrayList<keMu>(); for (int i = 0; i < 1000; i++) { keMu kemu = new keMu(); kemu.setCreateDate("2018/11/06"); kemu.setKe1("理论" + i); kemu.setKe2("模拟" + i); kemu.setKe3("上机" + i); kemu.setKe4("实操" + i); kemu.setRemark("通过"); kemus.add(kemu); } // 填充数据 if (kemus != null && kemus.size() > 0) { for (int i = 0; i < kemus.size(); i++) { Row contentRow = sheet.createRow(i + 2);// 填充类容,从第2行开始,0行给表头 if (i % 100 == 0) { try { ((SXSSFSheet) sheet).flushRows(100); } catch (IOException e) { e.printStackTrace(); } } for (int j = 0; j < titles2.length; j++) { Cell cell = contentRow.createCell(j); cell.setCellStyle(style); keMu content = kemus.get(i); switch (j) { case 0: cell.setCellValue(content.getCreateDate()); break; case 1: cell.setCellValue(content.getKe1()); break; case 2: cell.setCellValue(content.getKe2()); break; case 3: cell.setCellValue(content.getKe3()); break; case 4: cell.setCellValue(content.getKe4()); break; case 5: cell.setCellValue(content.getRemark()); break; } } } } return wb; } static class keMu { private String createDate; private String ke1; private String ke2; private String ke3; private String ke4; private String remark; public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } public String getKe1() { return ke1; } public void setKe1(String ke1) { this.ke1 = ke1; } public String getKe2() { return ke2; } public void setKe2(String ke2) { this.ke2 = ke2; } public String getKe3() { return ke3; } public void setKe3(String ke3) { this.ke3 = ke3; } public String getKe4() { return ke4; } public void setKe4(String ke4) { this.ke4 = ke4; } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark; } } }
2、HSSFWorkbook：
package com.test.excel; import java.io.BufferedOutputStream; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.HorizontalAlignment; import org.apache.poi.ss.usermodel.VerticalAlignment; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; public class HSSFExcel { public static void main(String[] args) { try { boolean createExcelFile = createExcelFile(); if (createExcelFile) { System.out.println("HSSF_excel导出成功"); } else { System.out.println("hssf_excel导出失败"); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static boolean createExcelFile() throws Exception { boolean state = false; OutputStream os = null; FileOutputStream fos = null; String resultFileName = null; String fileName = ""; try { org.apache.poi.ss.usermodel.Workbook wb; fileName = "hkemu.xls"; wb = createReportWorkbook(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); resultFileName = "E:\\" + File.separator + "excel" + File.separator + sdf.format(new Date()) + File.separator + fileName; resultFileName = resultFileName.replaceAll("\\\\", "/"); File file = new File(resultFileName); if (!file.exists()) { file.getParentFile().mkdirs(); } fos = new FileOutputStream(file); os = new BufferedOutputStream(fos, 1024); wb.write(os); os.flush(); state = true; } finally { if (os != null) os.close(); } return state; } private static Workbook createReportWorkbook() { String[] titles = { "时间", "科目", "科目", "科目", "科目", "备注" }; String[] handClum = { "0,1,0,0", "0,0,1,4", "0,1,5,5" }; String[] titles2 = { "时间", "科目一", "科目二", "科目三", "科目四", "备注" }; // 创建HSSFWorkbook对象，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 在wb中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet("test"); CellStyle style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置单元格垂直居中 for (int i = 0; i < titles.length; i++) { sheet.setColumnWidth(i, 5000); } // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow(0);// 创建表头1 for (int i = 0; i < titles.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(titles[i]); } // 动态合并单元格 for (int i = 0; i < handClum.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = handClum[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } row = sheet.createRow(1);// 创建表头2 for (int i = 0; i < titles2.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(titles2[i]); } List<keMu> kemus = new ArrayList<keMu>(); for (int i = 0; i < 1000; i++) { keMu kemu = new keMu(); kemu.setCreateDate("2018/11/06"); kemu.setKe1("理论" + i); kemu.setKe2("模拟" + i); kemu.setKe3("上机" + i); kemu.setKe4("实操" + i); kemu.setRemark("通过"); kemus.add(kemu); } // 填充数据 if (kemus != null && kemus.size() > 0) { for (int i = 0; i < kemus.size(); i++) { HSSFRow contentRow = sheet.createRow(i + 2);// 填充类容,从第2行开始,0行给表头 for (int j = 0; j < titles2.length; j++) { HSSFCell cell = contentRow.createCell(j); cell.setCellStyle(style); keMu content = kemus.get(i); switch (j) { case 0: cell.setCellValue(content.getCreateDate()); break; case 1: cell.setCellValue(content.getKe1()); break; case 2: cell.setCellValue(content.getKe2()); break; case 3: cell.setCellValue(content.getKe3()); break; case 4: cell.setCellValue(content.getKe4()); break; case 5: cell.setCellValue(content.getRemark()); break; } } } } return wb; } static class keMu { private String createDate; private String ke1; private String ke2; private String ke3; private String ke4; private String remark; public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } public String getKe1() { return ke1; } public void setKe1(String ke1) { this.ke1 = ke1; } public String getKe2() { return ke2; } public void setKe2(String ke2) { this.ke2 = ke2; } public String getKe3() { return ke3; } public void setKe3(String ke3) { this.ke3 = ke3; } public String getKe4() { return ke4; } public void setKe4(String ke4) { this.ke4 = ke4; } public String getRemark() { return remark; } public void setRemark(String remark) { this.remark = remark; } } }
