◎HSSFWorkbook一般用于Excel2003版及更早版本(扩展名为.xls)的导出,单Sheet导出条数上限是65535行,256列 ◎XSSFWorkbook一般用于Excel2007版(扩展名为.xlsx)的导出。单Sheet导出条数1048576行,16384列, 但是实测30万40万导出是,会报错Handler dispatch failed; nested exception is java.lang.OutOfMemoryError: GC overhead limit exceeded 正常导出的话,40万数据导出大概在1分钟左右 ◎SXSSFWorkbook一般用于大数据量的导出。 40万数据完美导出,耗时35秒2.SXSSFWorkbook导出代码:
2.1 导出对象封装
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ExportDto implements Serializable {
private static final long serialVersionUID = -2194048487324978603L;
private String sheetName;
private String title;
private String titleStyleMethod;
private Boolean specialTitle;
private List> dataList;
private LinkedHashMap keyNameMap;
private int rowRowNum;
private Boolean needMergedData;
private Integer[] mergeBasis;
private Integer[] mergeCells;
}
2.导出代码(解决自适应列宽问题)
//导出N个sheet页 excel2007版
public void exportNsheetSXSS(String fileName, List exportDtos, HttpServletRequest request, HttpServletResponse response) {
SXSSFWorkbook workbook = null;
try {
workbook = new SXSSFWorkbook(); // 创建工作簿对象
// 打开压缩功能 防止占用过多磁盘
workbook.setCompressTempFiles(true);
//设置标题和单元格样式
CellStyle columnTopStyle = IExclUtil.getColumnTopStyleXSS(workbook); //获取列头样式对象
CellStyle style = IExclUtil.getStyleXSS(workbook); //单元格样式对象
for (ExportDto exportDto : exportDtos) {
LinkedHashMap keyNameMap = exportDto.getKeyNameMap();//列名映射关系
List> dataList = exportDto.getDataList();//数据
int columnNum = keyNameMap.size(); // 定义所需列数
int rowRowNum = exportDto.getRowRowNum();
SXSSFSheet sheet = workbook.createSheet(exportDto.getSheetName());// 创建工作表
//sheet.setRandomAccessWindowSize(-1);//windowSize为-1表示无限制访问。在这种情况下,所有尚未通过调用flushRows()刷新的记录可用于随机访问。
oneSheetSXSS(workbook, columnTopStyle, style, exportDto, sheet, keyNameMap, dataList, columnNum, rowRowNum);
}
//导出数据
exportSXSS(fileName, workbook, response, request);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
//使用完毕后将产生的临时文件删除 防止将磁盘搞满
workbook.dispose();
}
}
}
setRandomAccessWindowSize(-1)设置后,基本上就导不出来了
2.3封装了一下导出一个sheet也的代码
public void oneSheetSXSS(SXSSFWorkbook workbook, CellStyle columnTopStyle, CellStyle style, ExportDto exportDto, SXSSFSheet sheet, LinkedHashMapkeyNameMap, List > dataList, int columnNum, int rowRowNum) throws IllegalAccessException, InvocationTargetException { //设置报表title String title = exportDto.getTitle(); if (StringUtils.isNotBlank(title)) { Boolean specialTitle = exportDto.getSpecialTitle(); //是特殊标题 if (specialTitle) { //样式都放在IExclUtil里,故写死 } else { setTitleSXSS(sheet, columnTopStyle, columnNum, title, mergeRowsList); } } //设置标题信息 setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap); //将查询出的数据设置到sheet对应的单元格中 if (exportDto.getNeedMergedData()) { //合并单元格的方法 } else { initDataSXSS(sheet, style, rowRowNum, dataList, keyNameMap); } //让列宽随着导出的列长自动适应 //表头数组 String[] headers = new String[keyNameMap.size()]; int ii = 0; for (Iterator iter = keyNameMap.keySet().iterator();iter.hasNext();) { String fieldName = iter.next(); headers[ii] = fieldName; ii++; } for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); int width = Math.max(3480, Math.min(65280, sheet.getColumnWidth(i) * 17 / 10)); sheet.setColumnWidth(i, width); } }
2.4设置报表title
default void setTitleSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, String tileName, LinkedListmergeRowsList) { // 产生表格标题行 SXSSFRow rowm = sheet.createRow(0); SXSSFCell cellTiltle = rowm.createCell(0); //合并单元格 sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, columnNum - 1)); if (!CollectionUtils.isEmpty(mergeRowsList)) { for (int[] a : mergeRowsList) { sheet.addMergedRegion(new CellRangeAddress(a[0], a[1], a[2], a[3])); } } cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(tileName); }
2.5设置报表列名
default void setHeadColumSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int rowRowNum, LinkedHashMapkeyNameMap) { // 将列头设置到sheet的单元格中 SXSSFRow rowRowName = sheet.createRow(rowRowNum); int sizeIndex = 0; for (Map.Entry entry : keyNameMap.entrySet()) { SXSSFCell cellRowName = rowRowName.createCell(sizeIndex);//创建列头对应个数的单元格 cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型 cellRowName.setCellValue(entry.getValue());//设置列头单元格的值 cellRowName.setCellStyle(columnTopStyle); ++sizeIndex; } }
2.6报表填数据
default void initDataSXSS(SXSSFSheet sheet, CellStyle style, int rowRowNum, List> dataList, LinkedHashMap keyNameMap) { if (dataList == null) { SXSSFRow row = sheet.createRow(rowRowNum + 1);//创建所需的行数 SXSSFCell cell = row.createCell(0); cell.setCellValue(""); cell.setCellStyle(style); } else { for (int i = 0; i < dataList.size(); i++) { HashMap obj = dataList.get(i);//遍历每个对象 SXSSFRow row = sheet.createRow(i + rowRowNum + 1);//创建所需的行数 int cellIndex = 0; for (Map.Entry entry : keyNameMap.entrySet()) { SXSSFCell cell = null; cell = row.createCell(cellIndex); Object o = obj.get(entry.getKey()); if(o!=null){ cell.setCellValue(o.toString()); }else{ cell.setCellValue(""); } cell.setCellStyle(style); ++cellIndex; } } } }
2.7导出
default void exportSXSS(String fileName, SXSSFWorkbook workbook, HttpServletResponse response, HttpServletRequest request) {
try (OutputStream ouputStream = response.getOutputStream()) {
response.setCharacterEncoding("UTF-8");
response.setContentType("multipart/form-data");
String userAgent = request.getHeader("User-Agent");
byte[] bytes = userAgent.contains("MSIE") ? fileName.getBytes() : fileName.getBytes("UTF-8"); // fileName.getBytes("UTF-8")处理safari的乱码问题
String fileNameEncode = new String(bytes, "ISO-8859-1"); // 各浏览器基本都支持ISO编码
response.setHeader("Content-disposition",
String.format("attachment; filename="%s"", fileNameEncode + ".xlsx"));
workbook.write(ouputStream);
ouputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
2.8 调用
@GetMapping("/download")
public void download(HttpServletRequest request, HttpServletResponse response, @RequestParam("type") String type,
@RequestParam("sjrq") Integer sjrq,
FtReqDto optionalParams) {
Result> result = ftService.download(type, sjrq, optionalParams);
List> records = (List>) result.getData();
ExportDto exportDto = ExportDto.builder().sheetName("sheet名字").keyNameMap(keymap()).dataList(records).rowRowNum(2).title("标题").specialTitle(false).needMergedData(false).build();
ExportExcelUtil exportExcelUtil = new ExportExcelUtil();
List exporlist = new ArrayList<>();
exporlist.add(exportDto);
exportExcelUtil.exportNsheetSXSS("文件名字" + sjrq, exporlist, request, response);
}
3.踩坑记
3.1列宽问题
修改之后
解决代码片段
//让列宽随着导出的列长自动适应
//表头数组
String[] headers = new String[keyNameMap.size()];
int ii = 0;
for (Iterator iter = keyNameMap.keySet().iterator();iter.hasNext();) {
String fieldName = iter.next();
headers[ii] = fieldName;
ii++;
}
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
int width = Math.max(3480, Math.min(65280, sheet.getColumnWidth(i) * 17 / 10));
sheet.setColumnWidth(i, width);
}
之前写的方法,填完数据之后在依次取出拿到合适的列宽,下面的写法会报错单元格已存在,后来开起了setRandomAccessWindowSize(-1),不报错,也无法导出来了
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
4 结果
实测42万数据量,纯导出时间为35秒
参考链接:
POI SXSSF注意事项
C:Users你的用户AppDataLocalTemppoifiles
(AppData)为隐藏文件夹
能在此文件夹下看到临时文件
//使用完毕后将产生的临时文件删除 防止将磁盘搞满
workbook.dispose();



