Java使用EasyExcel导出并上传到文件存储服务

前言

• 针对数万以上数据量的导出,使用EasyExcel,提升导出速度,降低服务器的内存占用.
• 导出时先上传至文件存储服务,再将文件地址返回给前端,避免浏览器直接处理文件流.

代码部分

pom文件

        <!--easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.2</version>
        </dependency>

工具类

    /**
     * 导出Excel返回FileInputStream
     */
    public static FileInputStream exportExcelStream(Class<?> clazz, List<?> list) {
        try {
            // 临时目录 临时文件名
            String tmp = System.getProperty("java.io.tmpdir");
            String fileName = String.valueOf(new Date().getTime());
            File file = new File(tmp + fileName + ".xlsx");
            FileOutputStream fileOutputStream =  new FileOutputStream(file);
            ExcelWriter excelWriter = write(fileOutputStream)
                    .registerWriteHandler(new ExcelColumnWidthHandler())
                    .registerWriteHandler(new ExcelSheetWriteHandler())
                    .registerWriteHandler(new ExcelStyleHandler())
                    .build();
            WriteSheet writeSheet = writerSheet(0, fileName).head(clazz).build();
            excelWriter.write(list, writeSheet);
            excelWriter.finish();
            return new FileInputStream(file);
        } catch (Exception e) {
            throw new ServiceException("导出Excel异常");
        }
    }

    /**
     * 导出示例
     */
    @PostMapping("/exportBill")
    public Result exportBill() throws IOException {
        BillPageVo vo = new BillPageVo();
        vo.setCompanyName("");
        vo.setThirdBillNo("");
        vo.setCompanyCode("");
        vo.setBillNo("");
        vo.setGoodsName("");
        FileInputStream fileInputStream = ExcelUtil.exportExcelStream(BillPageVo.class, Collections.singletonList(vo));
        // 上传文件到文件存储服务OSS
        String url = ossUtil.upload("test/excel/" + "车辆运单导出.xlsx", fileInputStream);
        fileInputStream.close();
        return Result.success(url);
    }

easyexcel配置

/**
 * 根据表头设置列宽
 */
public class ExcelColumnWidthHandler extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            int columnWidth = cell.getStringCellValue().length();
            columnWidth = Math.max(columnWidth * 2, 18);
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
}

/**
 * 设置单元格为文本格式
 */
public class ExcelSheetWriteHandler implements SheetWriteHandler {

    // 设置100列column
    private static final Integer COLUMN = 100;

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        for (int i = 0; i < COLUMN; i++) {
            // 设置为文本格式
            SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();
            CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
            // 49为文本格式
            cellStyle.setDataFormat((short) 49);
            // i为列，一整列设置为文本格式
            sxssfSheet.setDefaultColumnStyle(i, cellStyle);
        }
    }

}

/**
 * 自定义样式handler
 */
public class ExcelStyleHandler extends AbstractVerticalCellStyleStrategy {

    @Override
    protected WriteCellStyle contentCellStyle(Head head) {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        contentWriteFont.setFontName("等线");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        return contentWriteCellStyle;
    }
}