要实现上述动态头，按每日统计，每月统计，每年统计。而时间是一直变化，所以我们需要表头也一直动态生成。
首先，我们需要定义所需要实体类
public class CountDayData {
@ExcelProperty(value = "业务员姓名")
private String saleName;
@ExcelProperty({"${day}", "订单总数"})
private Integer orderNum;
@ExcelProperty({"${day}", "销售额"})
private BigDecimal totalAmount;
@ExcelProperty({"${day}", "平均单价"})
private BigDecimal avaAmount;
@ExcelProperty({"${month}", "订单总数"})
private Integer orderNum1;
@ExcelProperty({"${month}", "销售额"})
private BigDecimal totalAmount1;
@ExcelProperty({"${month}", "平均单价"})
private BigDecimal avaAmount1;
@ExcelProperty({"${year}", "订单总数"})
private Integer orderNum2;
@ExcelProperty({"${year}", "销售额"})
private BigDecimal totalAmount2;
@ExcelProperty({"${year}", "平均单价"})
private BigDecimal avaAmount2;
public String getSaleName() {
return saleName;
}
public void setSaleName(String saleName) {
this.saleName = saleName;
}
public Integer getOrderNum() {
return orderNum;
}
public void setOrderNum(Integer orderNum) {
this.orderNum = orderNum;
}
public BigDecimal getTotalAmount() {
return totalAmount;
}
public void setTotalAmount(BigDecimal totalAmount) {
this.totalAmount = totalAmount;
}
public BigDecimal getAvaAmount() {
return avaAmount;
}
public void setAvaAmount(BigDecimal avaAmount) {
this.avaAmount = avaAmount;
}
public Integer getOrderNum1() {
return orderNum1;
}
public void setOrderNum1(Integer orderNum1) {
this.orderNum1 = orderNum1;
}
public BigDecimal getTotalAmount1() {
return totalAmount1;
}
public void setTotalAmount1(BigDecimal totalAmount1) {
this.totalAmount1 = totalAmount1;
}
public BigDecimal getAvaAmount1() {
return avaAmount1;
}
public void setAvaAmount1(BigDecimal avaAmount1) {
this.avaAmount1 = avaAmount1;
}
public Integer getOrderNum2() {
return orderNum2;
}
public void setOrderNum2(Integer orderNum2) {
this.orderNum2 = orderNum2;
}
public BigDecimal getTotalAmount2() {
return totalAmount2;
}
public void setTotalAmount2(BigDecimal totalAmount2) {
this.totalAmount2 = totalAmount2;
}
public BigDecimal getAvaAmount2() {
return avaAmount2;
}
public void setAvaAmount2(BigDecimal avaAmount2) {
this.avaAmount2 = avaAmount2;
}
}
创建 EasyExcelCellWriteHandler并继承CellWriteHandler
public class EasyExcelCellWriteHandler implements CellWriteHandler {
/**
错误信息处理时正则表达式的格式
*/
private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";
/**
操作列
*/
private final List<Integer> columnIndex;
private JSONObject headTitle;
PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");
public EasyExcelCellWriteHandler(List<Integer> columnIndex, Short colorIndex, HashMap<Integer, String> annotationsMap, HashMap<Integer, String[]> dropDownMap , String time, String month, String year, JSONObject sheetTitle) {
this.columnIndex = columnIndex;
this.headTitle = headTitle;
}
public EasyExcelCellWriteHandler(List<Integer> columnIndex, JSONObject headTitle) {
this.columnIndex = columnIndex;
this.headTitle = headTitle;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 动态设置表头字段
if (!ObjectUtils.isEmpty(head)) {
List<String> headNameList = head.getHeadNameList();
if (CollectionUtils.isNotEmpty(headNameList)) {
ArrayList<Properties> propertiesList = new ArrayList<>();
for (String key : headTitle.keySet()){
Properties properties = new Properties();
properties.setProperty(key, headTitle.getString(key));
propertiesList.add(properties);
}
for (int i = 0 ; i < headNameList.size() ; i++){
for (Properties properties : propertiesList) {
//表头中如果有${}设置的单元格，则可以自定义赋值。 根据构造方法传入的jsonObject对象
headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
}
}
}
}
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}headTitle格式为json，目的是为了方便有多个变化的表头一次性传递。
创建 EasyExcelSheetWriteHandler并继承SheetWriteHandler（这是为了创建标题）
public class EasyExcelSheetWriteHandler implements SheetWriteHandler {
private String title;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
public EasyExcelSheetWriteHandler() {
super();
}
public EasyExcelSheetWriteHandler(String title) {
this.title = title;
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = workbook.getSheetAt(0);
Row row1 = sheet.createRow(0);
row1.setHeight((short) 800);
Cell cell = row1.createCell(0);
//设置标题
cell.setCellValue(title);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 9));
}业务层
EasyExcelSheetWriteHandler writeHandler = new EasyExcelSheetWriteHandler(reportTitle);
JSONObject obj = new JSONObject();
obj.put("day", DateUtils.getDate(day));
obj.put("month", DateUtils.getMonth(day));
obj.put("year", DateUtils.getYear(day));
EasyExcelCellWriteHandler easyExcelTitleHandler = new EasyExcelCellWriteHandler(null, obj);
ExcelUtil<Report> util = new ExcelUtil<>(Report.class);
util.getLocalFile(filePath);
util.exportEasyExcelFile(filePath, saleList, "统计", writeHandler, easyExcelTitleHandler);
注：filePath为文件的全路径（文件存放路径+文件名）
ExcelUtil为easyExcel的方法类
saleList为数据exportEasyExcelFile方法
/**
* 对list数据源将其里面的数据导入到excel表单（EasyExcel）保存到本地
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @return 结果
*/
public void exportEasyExcelFile(String fileName, List<T> list, String sheetName, EasyExcelSheetWriteHandler writeHandler, EasyExcelCellWriteHandler easyExcelTitleHandler) throws Exception
{
EasyExcel.write(fileName, clazz)
.sheet(sheetName)
.relativeHeadRowIndex(1)
.registerWriteHandler(writeHandler)
.registerWriteHandler(easyExcelTitleHandler)
.doWrite(list);
}参考文章：https://blog.csdn.net/Jul_C18672868641/article/details/129022583 https://blog.csdn.net/m0_47185078/article/details/125444869
