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

问题描述：

在使用SXSSFWorkbook作为excel导出方法使用时（poi-ooxml-3.14.jar）,当导出的文件过大（这里使用了100万条测试数据，生成的excel文件大小为29.8M，临时xml文件为622M）,这个时候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 streaif (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 fileex.printStackTrace();}}}}public static Object getPrivateAttribute(Object containingClass,String fieldToGet) throws NoSuchFieldException,IllegalAccessException {// get the field of the containingClass instanceField declaredField = containingClass.getClass().getDeclaredField(fieldToGet);// set it as accessibledeclaredField.setAccessible(true);// access itObject 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++;// 数据行从第二行开始，索引下标1row = 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"));// 导出excelworkbook.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的临时文件不会被立即清理。