EasyExcel简单的写和多table的写

EasyExcel写

基础数据结构

/**
 * 定义excel 导出内容，与导出样式
 *
 * @author zph
 * @date 2022/2/15 20:35
 */
public class MeasureExcelVO implements Serializable {

    private static final long serialVersionUID = -35362314781115090L;

    @Data
    @ApiModel("详情信息")
    @HeadRowHeight(20)
    @ContentRowHeight(15)
    @HeadStyle(fillForegroundColor = 22)
    @HeadFontStyle(fontHeightInPoints = 10)
    @ContentFontStyle(fontHeightInPoints = 10, fontName = "宋体")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
    public static class Table1 implements Serializable {

        private static final long serialVersionUID = 7275329823110111145L;

        @ColumnWidth(7)
        @ExcelProperty(value = {"水期"}, converter = WaterPhaseCoverter.class)
        @ApiModelProperty(value = "水期：0电量汇总, 1丰水期, 2平水期, 3枯水期")
        private Integer waterPhase;

        @ColumnWidth(7)
        @ExcelProperty(value = {"月份"})
        @ApiModelProperty(value = "月份")
        private Integer month;

        @ColumnWidth(12)
        @ExcelProperty(value = {"预估电量"})
        @ApiModelProperty(value = "预估电量")
        private BigDecimal forecastElectricity;

        public void setMonth(Integer month) {
            this.month = month;
            //枯水期为1月—4月、12月; 平水期为5月、11月 丰水期为6月—10月
            if (6 <= month && month <= 10) {
                this.waterPhase = 1;
            } else if (5 == month || month == 11) {
                this.waterPhase = 2;
            } else {
                this.waterPhase = 3;
            }
        }

        public static class WaterPhaseCoverter implements Converter<Integer> {

            @Override
            public Class<Integer> supportJavaTypeKey() {
                return Integer.class;
            }

            @Override
            public CellDataTypeEnum supportExcelTypeKey() {
                return CellDataTypeEnum.STRING;
            }

            @Override
            public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
                return null;
            }

            @Override
            public CellData<String> convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
                //水期：1丰水期, 2平水期, 3枯水期
                if (value.equals(IntUtil.INT_ZERO)) {
                    return new CellData<>("电量汇总");
                } else if (value.equals(IntUtil.INT_ONE)) {
                    return new CellData<>("丰水期");
                } else if (value.equals(IntUtil.INT_TWO)) {
                    return new CellData<>("平水期");
                } else if (value.equals(IntUtil.INT_THREE)) {
                    return new CellData<>("枯水期");
                } else {
                    return new CellData<>(value + "");
                }
            }
        }

    }

    /**
     * 单元格公式
     */
    public static class CellWriteHandler extends AbstractCellWriteHandler {

        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                     List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            //预估电量月份汇总
            if (cell.getRowIndex() == 13 && 2 == cell.getColumnIndex()) {
                // 汇总 = 月份之和
                cell.setCellFormula("=SUM(C2:C13)");
            }
        }

    }

    /**
     * 合并策略
     */
    public static class MergeStrategy extends AbstractMergeStrategy {

        //合并的条件
        private Integer conditionFieldIndex;
        //合并的字段
        private String filedName;
        //总的数据条数
        private Integer rowSize;
        //读取到的数据 样式：数据_行_列
        private List<String> datas = new ArrayList<>();

        // 禁止无参构造
        private MergeStrategy() {
        }

        /**
         * @param rowSize   Excel 数据行数
         * @param fieldName 要合并字段
         */
        public MergeStrategy(Integer rowSize, String fieldName) {
            this.rowSize = rowSize;
            this.filedName = fieldName;
        }

        /**
         * @param rowSize             Excel 数据行数
         * @param fieldName           要合并字段
         * @param conditionFieldIndex 是否合并判断字段下标 index 属性 (必须在合并字段前面，否则还没扫描到)
         */
        public MergeStrategy(Integer rowSize, String fieldName, Integer conditionFieldIndex) {
            this.rowSize = rowSize;
            this.filedName = fieldName;
            this.conditionFieldIndex = conditionFieldIndex;
        }

        /**
         * @param sheet            sheet页
         * @param cell             单元格
         * @param head             单元格头信息
         * @param relativeRowIndex 相对行索引（除去表头）
         */
        @Override
        protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
            int currentCellIndex = cell.getColumnIndex();
            //字段合并策略
            if (null != head && head.getFieldName().equals(filedName)) {
                //记录数据
                recordData(currentCellIndex, cell);
                //合并
                if (datas.size() >= rowSize) {
                    mergeFiled(sheet);
                }
            }
        }

        /**
         * 合并单元格
         */
        private void mergeFiled(Sheet sheet) {
            if (CollUtil.isEmpty(datas)) {
                return;
            }
            //开始
            String[] startData = datas.get(0).split("_");
            String startValue = startData[0];
            int startRow = Integer.parseInt(startData[1]);
            int startColumn = Integer.parseInt(startData[2]);
            //从第二条开始
            for (int i = 1; i < datas.size(); i++) {
                String[] currData = datas.get(i).split("_");
                int currRow = Integer.parseInt(currData[1]);
                int currColumn = Integer.parseInt(currData[2]);
                //条件不相同，且超过一行，则合并
                if (!StrUtil.equals(startValue, currData[0]) && currRow - startRow > 1) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, currRow - 1, startColumn, currColumn));
                    startValue = currData[0];
                    startRow = currRow;
                    startColumn = currColumn;
                }
                //如果条件不相同，且只有一行，则跳过
                else if (!StrUtil.equals(startValue, currData[0]) && currRow - startRow == 1) {
                    startValue = currData[0];
                    startRow = currRow;
                    startColumn = currColumn;
                }
                //最后一行一定要尝试合并一次
                if (i == datas.size() - 1 && currRow - startRow > 0) {
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, currRow, startColumn, currColumn));
                }
            }
        }

        /**
         * 合并单元格
         *
         * @param currentCellIndex 当前单元格索引
         * @param cell             单元格
         */
        private void recordData(int currentCellIndex, Cell cell) {
            //当前单元格的值
            String data = cell.toString();
            //当前单元格的行索引
            int rowIndex = cell.getRowIndex();
            //当前单元格的列索引
            int columnIndex = cell.getColumnIndex();
            //构造数据
            data = data + "_" + rowIndex + "_" + columnIndex;
            //是否有合并条件
            if (ObjectUtil.isNotNull(conditionFieldIndex)) {
                if (currentCellIndex < conditionFieldIndex) {
                    throw new BizException("合并的条件列暂未读取到");
                }
                data = cell.getRow().getCell(conditionFieldIndex) + "_" + data;
            }
            datas.add(data);
        }
    }

}

基于实体类的写

 public void quoteMeasure(HttpServletResponse response) {
        HashMap<Integer, BigDecimal> monthEleMap = new HashMap<>();
        //组装1-12月 预估电量
        for (int mon = 1; mon <= 12; mon++) {
            monthEleMap.put(mon, new BigDecimal(mon * 10));
        }
        try {
            //xls用这个
	        //response.setContentType("application/vnd.ms-excel");
	        //xlsx用这个
	        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("测算表格", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8'" + fileName + "'.xlsx");

            //构造数据
            List<MeasureExcelVO.Table1> table1Datas = new ArrayList<>();
            //构造1-12月数据
            for (Map.Entry<Integer, BigDecimal> entry : monthEleMap.entrySet()) {
                Integer month = entry.getKey();
                BigDecimal ele = entry.getValue();
                MeasureExcelVO.Table1 data = new MeasureExcelVO.Table1();
                data.setMonth(month);
                data.setForecastElectricity(ele);
                table1Datas.add(data);
            }
            //增加合计行
            {
                MeasureExcelVO.Table1 data = new MeasureExcelVO.Table1();
                data.setWaterPhase(IntUtil.INT_ZERO);
                table1Datas.add(data);
            }

            EasyExcel.write(response.getOutputStream(), MeasureExcelVO.Table1.class)
                    //合并策略
                    .registerWriteHandler(new MeasureExcelVO.MergeStrategy(table1Datas.size(), "waterPhase"))
                    //插入公式
                    .registerWriteHandler(new MeasureExcelVO.CellWriteHandler())
                    .sheet(0,"详情").doWrite(table1Datas);

        } catch (Exception e) {
            log.error("导出测算表格异常：{}", e.getMessage());
        }
    }

基于table的写

    public void quoteMeasure(HttpServletResponse response) {
        HashMap<Integer, BigDecimal> monthEleMap = new HashMap<>();
        //组装1-12月 预估电量
        for (int mon = 1; mon <= 12; mon++) {
            monthEleMap.put(mon, new BigDecimal(mon * 10));
        }
        try {
            //xls用这个
	        //response.setContentType("application/vnd.ms-excel");
	        //xlsx用这个
	        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("测算表格", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8'" + fileName + "'.xlsx");

            //构造数据
            List<MeasureExcelVO.Table1> table1Datas = new ArrayList<>();
            //构造1-12月数据
            for (Map.Entry<Integer, BigDecimal> entry : monthEleMap.entrySet()) {
                Integer month = entry.getKey();
                BigDecimal ele = entry.getValue();
                MeasureExcelVO.Table1 data = new MeasureExcelVO.Table1();
                data.setMonth(month);
                data.setForecastElectricity(ele);
                table1Datas.add(data);
            }
            //增加合计行
            {
                MeasureExcelVO.Table1 data = new MeasureExcelVO.Table1();
                data.setWaterPhase(IntUtil.INT_ZERO);
                table1Datas.add(data);
            }

            //excel表格工具
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"详情").build();
            WriteTable writeTable = EasyExcel.writerTable(0).head(MeasureExcelVO.Table1.class).needHead(true).build();
            //WriteTable writeTable2 = EasyExcel.writerTable(1).head(MeasureExcelVO.Table1.class).needHead(true).build();

            //定义Excel对象
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                    //合并策略
                    .registerWriteHandler(new MeasureExcelVO.MergeStrategy(table1Datas.size(), "waterPhase"))
                    //插入公式
                    .registerWriteHandler(new MeasureExcelVO.CellWriteHandler())
                    .build();
            //写入数据
            excelWriter.write(table1Datas, writeSheet, writeTable);
            //excelWriter.write(table1Datas, writeSheet, writeTable2);
            excelWriter.finish();

        } catch (Exception e) {
            log.error("导出测算表格异常：{}", e.getMessage());
        }
    }

导出结果