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的临时文件不会被立即清理。