对表格对象追加操作
使用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;
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);
}
}
|
