前言
其实 easyexcel 官网例子十分多，也很完善，但是实际工作中，我们遇到的情况往往是这些例子的复数情况。当我们重叠一起使用，就发现有些地方没法像官网那样直接使用。我这里就遇到了，所以写下我的解决方案，记录下。因为工作中的报表具有商业价值，所以没法直接使用。我就针对我觉得比较麻烦的几种情况，自己想个复合例子，写个 demo 都包含进去。
期望生成的报表
sheet1
还有下拉
sheet2
示例代码
我这里偷懒了，有很多能抽出来复用的代码，我没抽，只是简单实现 demo，用于实际工作要自己优化下
package com.alibaba.easyexcel.test.demo.write;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson2.JSON;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.assertj.core.util.Sets;
import org.junit.jupiter.api.Test;
import java.lang.reflect.Field;
import java.util.*;
/**
* @author CaiZhuliang
* @date 2023/5/10
*/
@Slf4j
public class MyWriteTest {
@Test
public void complexDemo() {
// 合并单元格+动态列头+同个单元格内多样式+列头填充颜色+多sheet+批注
String fileName = TestFileUtil.getPath() + "complexDemo" + System.currentTimeMillis() + ".xlsx";
try (ExcelWriter excelWriter = EasyExcel.write(fileName, ComplexDemoDTO.class).inMemory(Boolean.TRUE).build()) {
// 头部样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
WriteSheet sheet1 = EasyExcel.writerSheet("资料")
// 背景填充颜色
.registerWriteHandler(horizontalCellStyleStrategy)
// 下拉
.registerWriteHandler(new SheetWriteHandler() {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
// 由于 性别 在 (2,1)，因此数据从 (2,2) 开始输入。假设现在我给两百行都做下拉
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 201, 2, 2);
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"男", "女"});
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
}
})
// sheet2 必填标识变成红色
.registerWriteHandler(new RowWriteHandler() {
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Integer relativeRowIndex, Boolean isHead) {
if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(2) == 0) {
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()) {
Cell cell = iterator.next();
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.startsWith("*")) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
RichTextString richStringCellValue = cell.getRichStringCellValue();
richStringCellValue.applyFont(0, 1, font);
}
}
}
}
})
// 批注
.registerWriteHandler(new RowWriteHandler() {
private final Set<String> COMMENT_HEAD = Sets.set("*入职日期", "*离职日期");
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Integer relativeRowIndex, Boolean isHead) {
/* 只处理 入职日期 离职日期，它们分别在 工作经历 -> 经历1/经历2 下面，所以批注要在以下坐标上创建
(5,2) (6,2) (8,2) (9,2) */
if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(2) == 0) {
// 因为目标单元格都在第三行，所以我们处理点在第三行上
Drawing<?> drawingPatriarch = writeSheetHolder.getSheet().createDrawingPatriarch();
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()) {
Cell cell = iterator.next();
int columnIndex = cell.getColumnIndex();
short col1 = (short) (columnIndex + 1);
short col2 = (short) (columnIndex + 3);
if (COMMENT_HEAD.contains(cell.getStringCellValue())) {
// 创建批注
Comment comment = drawingPatriarch.createCellComment(new XSSFClientAnchor(0, 0,
0, 0, col1, 2, col2, 4));
comment.setString(new XSSFRichTextString("1、必填\n" +
"2、填写格式:yyyy-mm-dd"));
row.getCell(columnIndex).setCellComment(comment);
}
}
}
}
})
.build();
WriteSheet sheet2 = EasyExcel.writerSheet("面试结果")
.head(dynamicHead())
// 背景填充颜色
.registerWriteHandler(horizontalCellStyleStrategy)
// sheet2 必填标识变成红色
.registerWriteHandler(new RowWriteHandler() {
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Row row, Integer relativeRowIndex, Boolean isHead) {
if (BooleanUtils.isTrue(isHead) && relativeRowIndex.compareTo(1) == 0) {
Iterator<Cell> iterator = row.cellIterator();
while (iterator.hasNext()) {
Cell cell = iterator.next();
String stringCellValue = cell.getStringCellValue();
if (stringCellValue.startsWith("*")) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
RichTextString richStringCellValue = cell.getRichStringCellValue();
richStringCellValue.applyFont(0, 1, font);
}
}
}
}
})
.build();
excelWriter.write(Collections.EMPTY_LIST, sheet1);
excelWriter.write(Collections.EMPTY_LIST, sheet2);
}
}
private List<List<String>> dynamicHead() {
// 里面的 list 是纵向维度，而最外面的 list 是横向维度，所以要看目标列头所在的位置填充
List<List<String>> headColList = ListUtils.newArrayList();
headColList.add(ListUtils.newArrayList("评价", "*序号"));
headColList.add(ListUtils.newArrayList("评价", "*姓名"));
headColList.add(ListUtils.newArrayList("评价", "*综合得分"));
headColList.add(ListUtils.newArrayList("评价", "*一面评分"));
headColList.add(ListUtils.newArrayList("评价", "*二面评分"));
headColList.add(ListUtils.newArrayList("评价", "三面评分"));
headColList.add(ListUtils.newArrayList("评价", "*HR评分"));
return headColList;
}
@Data
public static class ComplexDemoDTO {
@ExcelProperty("序号")
private String no;
@ExcelProperty({"基础信息", "*姓名"})
private String name;
@ExcelProperty({"基础信息", "性别"})
private String sex;
@ExcelProperty({"基础信息", "*年龄"})
private Integer age;
@ExcelProperty({"工作经历", "经历1", "*公司名称"})
private String companyOfOne;
@ExcelProperty({"工作经历", "经历1", "*入职日期"})
private String startDateOfOne;
@ExcelProperty({"工作经历", "经历1", "*离职日期"})
private String endDateOfOne;
@ExcelProperty({"工作经历", "经历2", "*公司名称"})
private String companyOfTwo;
@ExcelProperty({"工作经历", "经历2", "*入职日期"})
private String startDateOfTwo;
@ExcelProperty({"工作经历", "经历2", "*离职日期"})
private String endDateOfTwo;
}
}
固定表头&动态表头
可能还会遇到固定+动态表头的情况，我这里是用了反射解决的
示例代码如下
package com.alibaba.easyexcel.test.demo.write;
import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson2.JSON;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.assertj.core.util.Sets;
import org.junit.jupiter.api.Test;
import java.lang.reflect.Field;
import java.util.*;
/**
* @author CaiZhuliang
* @date 2023/5/10
*/
@Slf4j
public class MyWriteTest {
/**
* 动态头，实时生成头写入
* 思路：反射读取固定表头字段，再加动态部分表头，通过自己指定 head 的方式生成表头
*/
@Test
public void dynamicHeadWrite() {
String fileName = TestFileUtil.getPath() + "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName)
// 这里放入动态头
.head(myHead())
.sheet("模板")
// 当然这里数据也可以用 List<List<String>> 去传入
.doWrite(myData());
}
private List<List<String>> myHead() {
List<List<String>> list = ListUtils.newArrayList();
Field[] selfFields = MyDemoData.class.getDeclaredFields();
Field[] supperFields = MyDemoData.class.getSuperclass().getDeclaredFields();
List<Field> fieldList = ListUtils.newArrayList(supperFields);
fieldList.addAll(ListUtils.newArrayList(selfFields));
for (Field field : fieldList) {
ExcelIgnore annotation = field.getAnnotation(ExcelIgnore.class);
if (null != annotation) {
continue;
}
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (null != excelProperty) {
list.add(ListUtils.newArrayList(excelProperty.value()));
}
}
// 动态字段
list.add(ListUtils.newArrayList("扩展字段1"));
list.add(ListUtils.newArrayList("扩展字段2"));
return list;
}
private List<List<Object>> myData() {
// 假设从db拿到的数据
MyDemoData data = new MyDemoData();
data.setString("字符串" + 0);
data.setDate(new Date());
data.setDoubleData(0.56);
// 动态扩展字段
data.setExtColumnList(ListUtils.newArrayList("ext1", "ext2"));
List<MyDemoData> dataList = ListUtils.newArrayList(data);
Field[] selfFields = MyDemoData.class.getDeclaredFields();
Field[] supperFields = MyDemoData.class.getSuperclass().getDeclaredFields();
List<Field> fieldList = ListUtils.newArrayList(supperFields);
fieldList.addAll(ListUtils.newArrayList(selfFields));
List<List<Object>> list = ListUtils.newArrayList();
for (MyDemoData myData : dataList) {
List<Object> columnList = ListUtils.newArrayList();
for (Field field : fieldList) {
ExcelIgnore annotation = field.getAnnotation(ExcelIgnore.class);
if (null != annotation) {
continue;
}
field.setAccessible(true);
try {
Object obj = field.get(myData);
if (obj instanceof List) {
columnList.addAll((List<?>) obj);
} else {
columnList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
}
list.add(columnList);
}
return list;
}
}