1.HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别:
◎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万数据完美导出,总耗时1.8分钟
2.SXSSFWorkbook导出代码:
2.1导出代码(解决自适应列宽问题)
public void exportSheetSXSS(String fileName, String sheetName, String title, LinkedHashMap<String, String> keyNameMap, List<HashMap<String, Object>> dataList, int rowRowNum, HttpServletRequest request, HttpServletResponse response) {
SXSSFWorkbook workbook = null;
try {
//设置-1表示不限制大小,否则后面shee.getRow时一直为null
workbook = new SXSSFWorkbook(); // 创建工作簿对象
// 打开压缩功能 防止占用过多磁盘
workbook.setCompressTempFiles(true);
//设置标题和单元格样式
CellStyle columnTopStyle = IExclUtil.getColumnTopStyleXSS(workbook); //获取列头样式对象
CellStyle style = IExclUtil.getStyleXSS(workbook); //单元格样式对象
int columnNum = keyNameMap.size(); // 定义所需列数
SXSSFSheet sheet = workbook.createSheet(sheetName);// 创建工作表
// sheet.setRandomAccessWindowSize(dataList.size()+10);//windowSize为-1表示无限制访问。在这种情况下，所有尚未通过调用flushRows（）刷新的记录可用于随机访问。
//设置报表title
setTitleSXSS(sheet, columnTopStyle, columnNum, title, mergeRowsList);
//设置标题信息
Map<Integer,Integer> sizeMap = setHeadColumSXSS(sheet, columnTopStyle, rowRowNum, keyNameMap);
//将查询出的数据设置到sheet对应的单元格中,并自适应长度
initDataSXSSAndAutoExpandColumWidth(sheet, style, rowRowNum, dataList, keyNameMap,sizeMap);
//导出数据
exportSXSS(fileName, workbook, response, request);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
//使用完毕后将产生的临时文件删除 防止将磁盘搞满
workbook.dispose();
}
}
}
setRandomAccessWindowSize(-1)设置后,基本上就导不出来了
2.2设置报表title
default void setTitleSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int columnNum, String tileName, LinkedList<int[]> mergeRowsList) {
// 产生表格标题行
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.3设置报表列名,返回所有列名的索引及对应的列名长度
/**
* 设置列名,返回所有列名的索引及对应的列名长度
* @param sheet
* @param columnTopStyle
* @param rowRowNum
* @param keyNameMap
* @return 所有列名的索引及对应的列名长度,key是索引,value是长度(一定要用索引做key)
*/
default Map<Integer,Integer> setHeadColumSXSS(SXSSFSheet sheet, CellStyle columnTopStyle, int rowRowNum, LinkedHashMap<String, String> keyNameMap) {
// 将列头设置到sheet的单元格中
SXSSFRow rowRowName = sheet.createRow(rowRowNum);
int sizeIndex = 0;
Map<Integer,Integer> sizeMap = new HashMap<>();
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
SXSSFCell cellRowName = rowRowName.createCell(sizeIndex);//创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);//设置列头单元格的数据类型
String value = entry.getValue();
cellRowName.setCellValue(value);//设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle);
sizeMap.put(sizeIndex, Math.max(0, value.getBytes().length));
++sizeIndex;
}
return sizeMap;
}
2.4将查询出的数据设置到sheet对应的单元格中,并自适应长度
/**
* 将查询出的数据设置到sheet对应的单元格中,并自适应长度
* @param sheet
* @param style
* @param rowRowNum
* @param dataList
* @param keyNameMap
* @param sizeMap 长度map
*/
default void initDataSXSSAndAutoExpandColumWidth(SXSSFSheet sheet, CellStyle style, int rowRowNum, List<HashMap<String, Object>> dataList, LinkedHashMap<String, String> keyNameMap,Map<Integer,Integer> sizeMap) {
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<String, Object> obj = dataList.get(i);//遍历每个对象
SXSSFRow row = sheet.createRow(i + rowRowNum + 1);//创建所需的行数
int cellIndex = 0;
for (Map.Entry<String, String> entry : keyNameMap.entrySet()) {
SXSSFCell cell = null;
cell = row.createCell(cellIndex);
String key = entry.getKey();
Object o = obj.get(key);
if(o!=null){
String value = o.toString();
cell.setCellValue(value);
sizeMap.put(cellIndex,Math.max(sizeMap.get(cellIndex), value.getBytes().length));
}else{
cell.setCellValue("");
sizeMap.put(cellIndex,Math.max(sizeMap.get(cellIndex), 0));
}
cell.setCellStyle(style);
++cellIndex;
}
}
//列的索引做长度集合的key
for (Integer cellIndex : sizeMap.keySet()) {
int width =Math.min(65280,(sizeMap.get(cellIndex)+2) * 256);
sheet.setColumnWidth(cellIndex, width);
}
}
}
2.5导出
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.6 调用
@GetMapping("/download")
public void download(
HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "countFlag", required = false) String countFlag) {
Result<?> result = pbService.selectAl(countFlag);
List<HashMap<String, Object>> records = (List<HashMap<String, Object>>) result.getData();
ExportExcelUtil exportExcelUtil = new ExportExcelUtil();
exportExcelUtil.exportSheetSXSS("下载文件名","sheet名称","标题名称",keymap(),records, 2, request, response);
}
3.踩坑记
3.1列宽自适应失效(列宽不准确)问题
修改之后
解决代码见2.3与2.4
(原代码及问题)
//让列宽随着导出的列长自动适应
//表头数组
String[] headers = new String[keyNameMap.size()];
int ii = 0;
for (Iterator<String> 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);
}
3.1.1 sheet.autoSizeColumn(i);sheet.setColumnWidth(i, width);无效
列宽自适应失效的原因有可能是因为标题部分用了单元格合并,使用autoSizeColumn(i)后,导致sheet.getColumnWidth(i)无法获取到列的宽度,只能获取到默认长度,自然自适应就失效了.
解决方式一(大数据量无法使用)
在设置自适应列宽时,使用次方法 sheet.autoSizeColumn(i,true),有合并单元格用此方法
例:
for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i,true);
int width = Math.max(3480, Math.min(65280, sheet.getColumnWidth(i) * 17 / 10));
sheet.setColumnWidth(i, width);
}
弊端: public void autoSizeColumn(int column, boolean useMergedCells)方法效率极差,大数据量不可用
解决方式二(效率差)
思路就是设置完值之后,在取出来比较长度,效率差
default void autoExpandColumWidthMergeXSS(SXSSFSheet sheet, int columnNum,int rowNum) {
for (int colNum = 0; colNum < columnNum; colNum++) {
sheet.autoSizeColumn(colNum,true);
int columnWidth = sheet.getColumnWidth(colNum) / 256;
//rowNum是列名所在行
for (int a=rowNum; a < sheet.getLastRowNum(); a++) {
SXSSFRow currentRow = sheet.getRow(a);
if (currentRow != null) {
if (currentRow.getCell(colNum) != null) {
//取得当前的单元格
SXSSFCell currentCell = currentRow.getCell(colNum);
//如果当前单元格类型为字符串
int length = getCellValue(currentCell).getBytes().length;
if (columnWidth < length) {
//将单元格里面值大小作为列宽度
columnWidth = length;
}
}
}
}
int i = (columnWidth + 2) * 256;
if (i < 65280) {
sheet.setColumnWidth(colNum, i);
} else {
sheet.setColumnWidth(colNum, 6000);
}
}
}
/**
* Description: 获取单元格的值
*
* @param cell
* @return 单元格中的值
*/
public static String getCellValue(Cell cell) {
String value = "";
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue() + "";
break;
case Cell.CELL_TYPE_STRING:
value = cell.getRichStringCellValue() + "";
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
default:
break;
}
return value.trim();
}
解决方式三(完美解决) 代码在上面
思路:1.设置列名的时候,就把列索引及列名长度存到map里(取名sizeMap),key为列在excel里的索引,value为当前单元格字段长度
2.设置内容的时候,把单元格内容存进去的同时,获取其长度与sizeMap里该列以存的长度做比较,取最大的存进去
3.最后在调用setColumnWidth方法,把sizeMap里存的列及长度设置进去,完美解决
40万数据量,原本总用时2.5min,优化后总用时1.8min
3.2报错Handler dispatch failed; nested exception is java.lang.OutOfMemoryError: GC overhead limit exceeded
1.调整jvm内存大小即可,本地复现方法为:加jvm参数即可,如下图
2. 或者使添加vm参数-XX:+UseCompressedOops
4 结果
实测42万数据量,纯导出时间为35秒
参考链接:
POI SXSSF注意事项
5 SXSSFWorkbook windows下临时文件存放路径
C:\Users\你的用户\AppData\Local\Temp\poifiles
(AppData)为隐藏文件夹
能在此文件夹下看到临时文件
//使用完毕后将产生的临时文件删除 防止将磁盘搞满
workbook.dispose();
5349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
