表格处理

Java表格处理-easyexcel框架使用和进阶

对表格对象追加操作

使用easyexcel，使用注解模板一般操作如下

注解模板

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import lombok.Data;
import lombok.EqualsAndHashCode;
import org.apache.poi.ss.usermodel.FillPatternType;

/**
 * Author:   caijc
 * Date:     2022/1/25 10:48
 */
@Data
@EqualsAndHashCode
// 头单元格样式
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 44)
@HeadFontStyle(fontHeightInPoints = 12)
public class BusinessTravelSalesDailyData {
    @ExcelProperty({"统计时间", "企业名称"})
    private String corpName;

    @ExcelProperty({"统计时间","客户经理"})
    private String manager;

    @ExcelProperty({"年交易总额","交易总额"})
    private Double totalAmount;

    @ExcelProperty({"年交易总额","去年同期交易总额"})
    private Double lastYearTotalAmount;

    @ExcelProperty({"国内机票","单量总计"})
    private Long dftOrderQuantity;

    @ExcelProperty({"国内机票","去年同期单量"})
    private Long dftLastYearOrderQuantity;

    @ExcelProperty({"国内机票","毛利"})
    private Double dftGrossMargin;

    @ExcelProperty({"国内机票","去年同期毛利"})
    private Double dftLastYearGrossMargin;
}

生成表格

1
2
3
4
5
6
7
8
9
10
List<BusinessTravelSalesDailyData> dataList = new ArrayList<>();
try (BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file))) {
            EasyExcel.write(out, BusinessTravelSalesDailyData.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(0, sheetName)
                    .doWrite(dataList);
        } catch (Exception e) {
            log.error("数据生成完毕 写入失败", e);
            throw e;
        }

因为在调用完doWrite()方法后，excel文件就已经生成，这时想再对表格进行操作，比如在表尾添加一个合计行，或者对已生成的数据进行额外操作，就必须再读取生成好的excel文件，进行后续操作。这样的追加操作就变得比较复杂

其实有一个比较好的方式可以很好实现追加操作

在分析源码的过程中，跟踪doWrite()方法，发现，在写入excel文件之前，会调用所有实现WorkbookWriteHandler接口的实现，所以，实现一个WorkbookWriteHandler实现类来完成追加操作

下面代码，是在表尾生成一个包含合计的单元格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 数据
        List<BusinessTravelSalesDailyData> dataList = new ArrayList<>();
        Integer sum = getSum(list, yearAmount);
        try (BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file))) {
            EasyExcel.write(out, BusinessTravelSalesDailyData.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .registerWriteHandler(new CustomSumDataHandler(sum)) // 设置合计填充数据
                    .sheet(0, sheetName)
                    .doWrite(dataList);
        } catch (Exception e) {
            log.error("数据生成完毕 写入失败", e);
            throw e;
        }
        log.info(file.getAbsolutePath());
        return file;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.huoli.ctar.tmc.csa.csm.job.statsRpt.excel.handle;

import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.huoli.ctar.tmc.vo.BusinessTravelSalesDailyEmailVo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * Author:   caijc
 * Date:     2022/2/9 14:29
 * Description: 自定义合计列
 */
public class CustomSumDataHandler implements WorkbookWriteHandler {
    Integer sum;

    public CustomSumDataHandler(Integer sum) {
        this.sum = sum;
    }

    @Override
    public void beforeWorkbookCreate() {

    }

    @Override
    public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {

    }

    @Override
    public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
        Sheet sheet = writeWorkbookHolder.getWorkbook().getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        int sumRowIndex = lastRowNum+1;
        Row row = sheet.createRow(sumRowIndex);
        CellStyle cellStyle = writeWorkbookHolder.getWorkbook().createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        Cell cellA1 = row.createCell(0);
        cellA1.setCellValue("总计:" + sum);
        cellA1.setCellStyle(cellStyle);
        CellRangeAddress cra = new CellRangeAddress(row.getRowNum(), row.getRowNum(), 0, 1);
        // 合并单元格
        sheet.addMergedRegion(cra);
    }
}