POI SXSSFWorkbook mac下查看poifiles生成的临时文件

问题描述：解决方案：

问题描述：

在使用SXSSFWorkbook作为excel导出方法使用时（poi-ooxml-3.14.jar）,当导出的文件过大（这里使用了100万条测试数据，生成的excel文件大小为29.8M，临时xml文件为622M）,这个时候xml文件不会自动删除，导致占用了磁盘空间，在生产环境时，当临时文件夹满了之后就无法正常导出。

---

解决方案：

1、通过代码手动删除poi生成的临时xml文件 public static void deleteSXSSFTempFiles(SXSSFWorkbook workbook) throws NoSuchFieldException, IllegalAccessException { int numberOfSheets = workbook.getNumberOfSheets(); // iterate through all sheets (each sheet as a temp file) for (int i = 0; i < numberOfSheets; i++) { Sheet sheetAt = workbook.getSheetAt(i); // delete only if the sheet is written by strea if (sheetAt instanceof SXSSFSheet) { SheetDataWriter sdw = (SheetDataWriter) getPrivateAttribute( sheetAt, "_writer"); File f = (File) getPrivateAttribute(sdw, "_fd"); try { f.delete(); } catch (Exception ex) { // could not delete the file ex.printStackTrace(); } } } } public static Object getPrivateAttribute(Object containingClass, String fieldToGet) throws NoSuchFieldException, IllegalAccessException { // get the field of the containingClass instance Field declaredField = containingClass.getClass().getDeclaredField( fieldToGet); // set it as accessible declaredField.setAccessible(true); // access it Object get = declaredField.get(containingClass); // return it! return get; }

2、输出 java.io.tmpdir 找到mac临时文件夹

public static boolean writeExcel_xlsx(String[] title,List<Object[]> dataList, OutputStream os) { boolean b = true; int exportSize = 1048576; SXSSFWorkbook workbook = null; try { if (dataList != null && dataList.size() > exportSize) { throw new Exception("导出数据大于导出限制数量：" + exportSize); } // 声明一个Excel工作薄 workbook = new SXSSFWorkbook(); SXSSFSheet sheet = workbook.createSheet(); int columnIndex = 0; int rowIndex = 0; SXSSFRow row = null; // 设置表头 row = sheet.createRow(rowIndex); // Excel头部样式 CellStyle cellStyleHead = workbook.createCellStyle(); cellStyleHead.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyleHead.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); cellStyleHead.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); cellStyleHead.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); cellStyleHead.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); cellStyleHead.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); Font fontHead = workbook.createFont(); fontHead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 fontHead.setFontHeightInPoints((short) 9);// 9号字体 fontHead.setFontName("宋体");// 宋体 cellStyleHead.setFont(fontHead); // 使用模板创建Excel表头 for (String str:title) { SXSSFCell cell = row.createCell(columnIndex); XSSFRichTextString richText = new XSSFRichTextString(str); cell.setCellValue(richText); // 指定头部样式 cell.setCellStyle(cellStyleHead); columnIndex++; } // 数据单元格样式 CellStyle bodyStyle = workbook.createCellStyle(); // 边框 bodyStyle.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND); bodyStyle.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND); bodyStyle.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND); bodyStyle.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND); // 宋体9号 Font font = workbook.createFont(); font.setFontHeightInPoints((short) 9); font.setFontName("宋体"); bodyStyle.setFont(font); // 填充数据 for (Object[] obj: dataList) { rowIndex++;// 数据行从第二行开始，索引下标1 row = sheet.createRow(rowIndex); for(int i =0;i<obj.length;i++) { // 全部以文体的方式导出 SXSSFCell cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); if(!StringUtils.isNull(obj[i])){ cell.setCellValue(obj[i].toString()); //设置单元格的值 } // 指定数据体样式 cell.setCellStyle(bodyStyle); sheet.setColumnWidth(i, 4000); } } System.out.println(System.getProperty("java.io.tmpdir")); // 导出excel workbook.write(os); ExcelUtils.deleteSXSSFTempFiles(workbook); } catch (Exception e) { e.printStackTrace(); b = false; } finally { if (os != null) { try { workbook.close(); os.close(); } catch (IOException e) { e.printStackTrace(); } } } return b; }

结果： 临时文件夹打印地址：/var/folders/55/cqv2kjns0b9cx62y3b5p91340000gn/T/ 我们进入到mac的临时文件夹，找到poifiles文件夹，导出结束后poi生产的临时xml文件被成功清理，如果不调用 ExcelUtils.deleteSXSSFTempFiles(workbook);，会产生600M的临时文件不会被立即清理。