使用EasyExcel导出数据

Leopard锋

已于 2024-01-04 11:58:04 修改

阅读量2.9k

点赞数 20

文章标签： java excel

于 2023-12-16 15:32:58 首次发布

本文链接：https://blog.csdn.net/u014799292/article/details/135032491

版权

The maximum number of cell styles was exceeded. You can define up to 64000 styles

一、引入EasyExcel依赖


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

二、代码框架实现

1-1、无需复杂处理，直接使用工具类

定义具体对象，并添加@ExcelProperty，来指定列信息


    @Data
    @Builder
    @NoArgsConstructor
    @AllArgsConstructor
    @ExcelIgnoreUnannotated
    public static class DemoDataRespVO {

        @ExcelProperty(value = "数据1", index = 0)
        private String data1;

        @ExcelProperty(value = "数据2",index = 1)
        private String data2;

        @ExcelProperty("数据3")
        private String data3;

        @ExcelProperty("数据4")
        private String data4;

        @ExcelProperty("数据5")
        private String data5;

        @ExcelProperty("数据6")
        private String data6;

        @ExcelProperty("数据7")
        private String data7;

        @ExcelProperty("数据8")
        private String data8;

        @ExcelProperty("数据9")
        private String data9;

        @ExcelProperty("数据10")
        private String data10;

    }

1-2、创建一批假数据

private List<DemoDataRespVO> getData() {
        List<DemoDataRespVO> data = new ArrayList<>();
        DemoDataRespVO data1 = DemoDataRespVO.builder()
                .data1("数据1-1").data2("数据2-1").data3("数据3-1").data4("数据4-1").data5("数据5-1")
                .data6("数据6-1").data7("-数据7-1").data8("数据8-1").data9("数据9-1").data10("数据10-1").build();
        data.add(data1);
        DemoDataRespVO data2 = DemoDataRespVO.builder()
                .data1("数据1-2").data2("数据2-2").data3("数据3-2").data4("数据4-2").data5("数据5-2")
                .data6("数据6-2").data7("数据7-2").data8("数据8-2").data9("-数据9-2").data10("数据10-2").build();
        data.add(data2);
        return data;
    }

1-3、使用流的方式导出数据

@RestController
@RequestMapping("/test")
public class TestController {


    @GetMapping("/exportExcel1")
    public void getExportExcel1(HttpServletResponse response) throws IOException {

        String sheetName = "sheet名";
        String fileName = "文件名.xlsx";
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");

        List<DemoDataRespVO> dataList = getData();
        EasyExcel.write(response.getOutputStream(), DemoDataRespVO.class)
                .autoCloseStream(false)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(dataList);
    }
}

1-4、使用生成文件的方式导出数据

@GetMapping("/exportExcel1")
    public void getExportExcel1(HttpServletResponse response) throws IOException {

        File file = new File("H:/test.xls");
        OutputStream outputStream = Files.newOutputStream(file.toPath());
        List<DemoDataRespVO> dataList = getData();
        EasyExcel.write(outputStream, DemoDataRespVO.class)
                .autoCloseStream(false)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(sheetName).doWrite(dataList);
    }

PS：可先上传到文件服务器，再通过返回的下载地址链接返回前端。

1-5、验证结果

localhost:8622/api/v1/test/exportExcel1

2-1、自定义表头格式，并对数据做处理


    @GetMapping("/exportExcel2")
    public void getExportExcel2(HttpServletResponse response) throws IOException {
        exportExcel(response);
    }

    /**
     * 导出Excel流
     *
     * @param response
     * @throws IOException
     */
    public void exportExcel(HttpServletResponse response) throws IOException {

        // 设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=testName.xlsx");

        // 获取输出流
        response.flushBuffer();
        //创建自定义的表头（可自行判断增减 列）
        ArrayList<List<String>> head = new ArrayList<>();
        head.add(CollectionUtil.newArrayList("标题1", "标题1"));        //0
        head.add(CollectionUtil.newArrayList("标题2", "标题2"));        //1
        head.add(CollectionUtil.newArrayList("标题3", "标题3"));        //2
        head.add(CollectionUtil.newArrayList("标题4", "标题4"));        //3
        head.add(CollectionUtil.newArrayList("标题5", "标题5"));        //4
        //！！！！ 如果是合并表头，数组头一个设置名称一样即可
        head.add(CollectionUtil.newArrayList("合并标题", "子标题1"));      //5
        head.add(CollectionUtil.newArrayList("合并标题", "子标题2"));      //6
        head.add(CollectionUtil.newArrayList("合并标题", "子标题3"));      //7
        head.add(CollectionUtil.newArrayList("合并标题", "子标题4"));      //8
        head.add(CollectionUtil.newArrayList("合并标题", "子标题5"));      //9
        //输出流

        //表头：原本是 write(response.getOutputStream(), XXX.class) ，XXX是默认表头格式
        //表头：现在设置为null, 使用 head 入参 自定义的表头，视情况自行选择
        //registerWriteHandler  自定义表格样式
        // sheet 自定以 sheet名称
        // doWrite(getData()) 行数据，-----！！！ 数据列 需要跟表头一样长度
        EasyExcel.write(response.getOutputStream(), null).head(head)
                .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                .registerWriteHandler(new RegisterStyle())
                .sheet("sheetName").doWrite(getData());
    }

    /**
     * 自定义样式
     */
    static class RegisterStyle extends AbstractColumnWidthStyleStrategy {

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell,
                                      Head head,
                                      Integer relativeRowIndex, Boolean isHead) {
            //这里有10个字段，所以只是设置10个列的宽度即可
            if (cell.getColumnIndex() < 10) {
                //setColumnWidth （i,j），i是列的下标，从0开始，j是列的宽度，单位是1/256个字符宽度，所以代码中要乘以256
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), 256 * 12);
            }
            //只是自定义的，判断那些列需要做处理
            boolean flag = cell.getColumnIndex() == 6 || cell.getColumnIndex() == 8;
            // isHead 表头， “!isHead”：跳过表头行
            if (!isHead && flag) {
                // 获取当前单元格的数值（存入String格式用 getStringValue 获取）
                // 获取当前单元格的数值（存入Number格式用 getNumberValue 获取）
                String stringValue = cellDataList.get(0).getStringValue();
                //创建单元格
                CellStyle cellStyle = cell.getSheet().getWorkbook().createCellStyle();
                cellStyle.cloneStyleFrom(cell.getCellStyle());
                if (stringValue.startsWith("-")) {
                    // - 开头表示负数
                    //单元格数值小于0的  ， 字体展示为 红色
                    Font font = cell.getSheet().getWorkbook().createFont();
                    font.setColor(IndexedColors.RED.getIndex());
                    cellStyle.setFont(font);
                }
                cell.setCellStyle(cellStyle);
            }
        }

    }

2-2、验证结果

localhost:8622/api/v1/test/exportExcel2

效果是跟我们想要的一样，这个时候开始皆大欢喜了。。。。。。错！

三、问题暴露及修复思路

1、问题原由

本次自定义样式是继承 AbstractColumnWidthStyleStrategy 重写 setColumnWidth 方法实现，但这个实现类底层有自己的限制性条件，如果导出的行数据过多大几千起步且需要修改单元格样式（不做修改操作是没事的），是会直接报错：

The maximum number of cell styles was exceeded. You can define up to 64000 styles

知其原由，找到限制的位置在：createCellStyle()

Workbook：

public interface Workbook extends Closeable, Iterable<Sheet> {
    //...................
    CellStyle createCellStyle();
    //...................
}

XSSFWorkbook：

public class XSSFWorkbook extends POIXMLDocument implements Workbook, Date1904Support {
    //...................
    public XSSFCellStyle createCellStyle() {
        return this.stylesSource.createCellStyle();
    }
    //...................
}

StylesTable：

public class StylesTable extends POIXMLDocumentPart implements Styles {
    //...................
	public XSSFCellStyle createCellStyle() {
        if (this.getNumCellStyles() > MAXIMUM_STYLE_ID) {
            throw new IllegalStateException("The maximum number of Cell Styles was exceeded. You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook");
        } else {
            int xfSize = this.styleXfs.size();
            CTXf xf = org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf.Factory.newInstance();
            xf.setNumFmtId(0L);
            xf.setFontId(0L);
            xf.setFillId(0L);
            xf.setBorderId(0L);
            xf.setXfId(0L);
            int indexXf = this.putCellXf(xf);
            return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, this.theme);
        }
    }
    //...................
    //...................
    static {
        MAXIMUM_STYLE_ID = SpreadsheetVersion.EXCEL2007.getMaxCellStyles();
    }
}

SpreadsheetVersion：

public enum SpreadsheetVersion {
    EXCEL97(65536, 256, 30, 3, 4000, 32767),
    EXCEL2007(1048576, 16384, 255, 2147483647, 64000, 32767);

    //...................
    private SpreadsheetVersion(int maxRows, int maxColumns, int maxFunctionArgs, int maxCondFormats, int maxCellStyles, int maxText) {
        this._maxRows = maxRows;
        this._maxColumns = maxColumns;
        this._maxFunctionArgs = maxFunctionArgs;
        this._maxCondFormats = maxCondFormats;
        this._maxCellStyles = maxCellStyles;
        this._maxTextLength = maxText;
    }
    //...................
    public int getMaxCellStyles() {
        return this._maxCellStyles;
    }
    //...................
}

定位到方法后，就要想办法处理了。

2、将底层实现或是枚举方法Copy重写

比较费时费力，而且需要熟悉接口层相关的关联依赖

3、重新定义实现类

将RegisterStyle 替换为重写 CellWriteHandler 的另一个自定义实现类 CustomCellStyleHandler

/**
 * excel 自定义样式
 */
public class CustomCellStyleHandler implements CellWriteHandler {
    
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                 Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        //此处提醒下 beforeCellCreate 的row.getCell(i) 已经获取不到值了， 只会返回NULL
        Cell cell = row.getCell(columnIndex);
    }

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        if (BooleanUtils.isNotTrue(context.getHead())) {
            // 获取当前行的行号
            Cell cell = context.getCell();
            if (ObjectUtil.isNotNull(cell)) {
                String stringValue = cell.getStringCellValue();
                if (CharSequenceUtil.isNotEmpty(stringValue) && stringValue.startsWith("-")) {
                    WriteCellData<?> cellData = context.getFirstCellData();
                    WriteCellStyle writeCellStyle = cellData.getOrCreateStyle();
                    WriteFont writeFont = new WriteFont();
                    writeFont.setColor(IndexedColors.RED.getIndex());
                    writeCellStyle.setWriteFont(writeFont);
                }
            }
        } else {
            // 表头自适应列宽
            Cell cell = context.getCell();
            int length = cell.getStringCellValue().getBytes().length;
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), length * 300);
        }
    }
}

测试1W数据，成功！！！！！（更大的数据量待各位自行验证）

遗留问题：2-1 步骤中的文件名 fileName=testName 如何设置为中文名称

四、百万级别数据导出

参考文章：百万级数据excel导出功能如何实现？

Controller层

添加 @Idempotent 注解，防止多次点击，处理幂等

    @GetMapping("/export-excel")
    @Operation(summary = "导出测试 Excel")
    @OperateLog(type = EXPORT)
    @Idempotent
    public void exportTestExcel(@Valid ReqVO reqVO) {
    	//type: 0-待执行，1-执行中，2-成功，3-失败
        downloadReportService.createDownloadResport(DownloadReportDO.builder().type(0).build());
        testService.exportExcel(reqVO);
    }

Service层

@Async
    @Override
    public void exportExcel(ReqVO reqVO) {
    	//查看是否有待执行下载的文件
        DownloadReportRespVO downLoadInfo = validAndGetInfoByType(reqVO.getType());
        downLoadInfo.setStatus(1);
        //设置文件名
        String filename = LocalDateTimeUtil.format(LocalDateTimeUtil.now(), "yyyyMMdd") + "测试表.xlsx";
        String filePath = 'H://' + filename;
        try {
        	//创建文件
            File file = new File(filePath);
            //目录不存在则创建目录
	        if (!file.getParentFile().exists()) {
	            Files.createDirectories(file.getParentFile().toPath());
	        }
            //文件不存在则创建文件
	        if (!file.exists()) {
	            Files.createFile(file.toPath());
	        }
            OutputStream outputStream = Files.newOutputStream(file.toPath());

           	//此处可往上参考：2-1、自定义表头格式，并对数据做处理
            //获取表头和列展示情况 
            ArrayList<List<String>> headTitleList = getHeadTitle(colMap);

            ExcelWriter excelWriter = EasyExcel.write(outputStream, null).head(headTitleList)
                    .autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
                    .registerWriteHandler(new CustomCellStyleHandler()).build();
            //查询数据总数
            Long totalCount = testMapper.selectCount(reqVO);
            log.info("本次 信息导出，查询到数据有 {} 条", totalCount);

            //此处开始做sheet切分用，每50W切换一个sheet
            long MAX_SINGLE_PAGE = 500000;
            if (ObjectUtil.isNotNull(totalCount) && totalCount != 0) {
            	//固定分页1，单页查询10W条
                reqVO.setPageNo(1);
                reqVO.setPageSize(100000);
                //计算出总共要几个sheet
                long sheetPageNumber = (totalCount % MAX_SINGLE_PAGE == 0) ? totalCount / MAX_SINGLE_PAGE : (totalCount / MAX_SINGLE_PAGE) + 1;
                List<DemoDataRespVO> dataList = CollUtil.newArrayList();
                WriteSheet writeSheet = null;
                for (int i = 0; i < sheetPageNumber; i++) {
                    writeSheet = EasyExcel.writerSheet("sheet" + i).build();
                    long currentDataTotal = (sheetPageNumber - 1) == i ? totalCount - (MAX_SINGLE_PAGE * i) : MAX_SINGLE_PAGE;
                    for (long tempTotal = 0L; tempTotal < currentDataTotal; ) {
                    	//查询分页接口，此处不例句展示
                        PageResult<DemoDataRespVO> pageReuslt = selecPageList(reqVO);
                        dataList = pageReuslt.getList();
                        if (CollUtil.isEmpty(dataList)) {
                            break;
                        }
                        //获取本次列表中最小的 id 游标，（！！列表数据是 id 倒序排序）
                        long minId = dataList.stream().mapToLong(CollectUserRevenueRespVO::getId).min().orElse(-1);
                        reqVO.setMinId(minId);
                        tempTotal += dataList.size();
                        //此处的getDisposeDataList是做数据的二次封装处理，无需处理的可跳过，这里不详解
                        excelWriter.write(getDisposeDataList(dataList), writeSheet);
                    }
                }
                //处理最后一行合计
                DemoDataRespVO respVO = selectTotal(reqVO);
                respVO.setData0("合计");
                dataList.add(respVO);
                excelWriter.write(getDisposeDataList(dataList, colMap), writeSheet);
                // 结束写入
                excelWriter.finish();

                //Excel处理成功，将表格上次到OSS存储桶位置，然后返回下载链接
                byte[] fileBytes = FileUtil.readBytes(file);
                FileCreateReqDTO fileCreateReqDTO = new FileCreateReqDTO();
                fileCreateReqDTO.setName(filename);
                fileCreateReqDTO.setPath(filePath);
                fileCreateReqDTO.setContent(fileBytes);
                Response<String> fileResult = fileTool.createFile(fileCreateReqDTO);
                if (fileResult.success()) {
                	String downUrl = fileResult.getData();
                	downLoadInfo.setDownUrl(downUrl);
                	downLoadInfo.setStatus(2);
                } else {
                	downLoadInfo.setStatus(3);
                }
            }
        } catch (Exception e) {
            downLoadInfo.setStatus(3);
            log.error("[exportExcel] [测试表 - 导出失败]] - {}", e);
            throw RunException("导出失败");
        } finally {
        	//更新本次导出下载表格的结果
            downloadReportService.updateDownloadResport(downLoadInfo);
        }
    }

处理思路

对于百万级起步的导出方法exportExcel使用的处理点：
1、service层使用异步，可让Controller层及时响应前端，进度通过中间表（download_report）查询
2、数据切割：按每50W一个sheet操作（上述方法中的数据计算是按50W为一个sheet，查询按每页10W数据处理，可自行优化计算方式，这里偷懒）
3、数据搜索：大数据量直接分页查询越往后越会超时，上述方法分页是ID倒序，每页以最后一个ID作为游标，从此ID定位再开始继续往下搜索，可提升查询效率
4、SQL查询使用ID嵌套定位查询（利用自身的主键索引理念），如下：

select t1.* 
from test_data t1,
    ( select id from data_collect_user_revenue where id &lt; #{minId} order by create_date desc, id desc limit #{(pageNo-1)*pageSize},#{pageSize}
    ) t2 where t1.id = t2.id

这里测试数据 1155371 条
上传成功后返回下载链接，点击后结果数据如下：

Leopard锋

关注

20
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
使用EasyExcel导出数据

*** 导出Excel流*/// 设置响应头// 获取输出流//创建自定义的表头（可自行判断增减列）head.add(CollectionUtil.newArrayList("标题1", "标题1"));//0head.add(CollectionUtil.newArrayList("标题2", "标题2"));//1head.add(CollectionUtil.newArrayList("标题3", "标题3"));//2。
复制链接

扫一扫