要求导出的表格如图
实现这样表格 很多人会想到动态表头，easypoi可以直接实现，但是我用的是easyexcel,而easyexcel自身并没有提供自动合并的功能所以还是需要自己来合并。
首先我们得把原来嵌套的订单商品信息给平铺了，创建一个专门的导出对象OrderData，包含订单和商品信息，二级表头可以通过设置@ExcelProperty的value为数组来实现
@Data
@AllArgsConstructor
@NoArgsConstructor
public class OrderData {
@ExcelProperty(value = "订单编码")
@ColumnWidth(20)
@CollectCustomMerge(needMerge = true, isPk = true)
private String orderSn;
@ExcelProperty(value = "创建时间")
@ColumnWidth(20)
@DateTimeFormat("yyyy-MM-dd")
@CollectCustomMerge(needMerge = true)
private Date createTime;
@ExcelProperty(value = "收货地址")
@CollectCustomMerge(needMerge = true)
@ColumnWidth(20)
private String receiverAddress;
@ExcelProperty(value = {"商品信息", "商品编码"})
@ColumnWidth(20)
private String productSn;
@ExcelProperty(value = {"商品信息", "商品名称"})
@ColumnWidth(20)
private String name;
@ExcelProperty(value = {"商品信息", "商品标题"})
@ColumnWidth(30)
private String subTitle;
}
/**
* 自定义注解，用于判断是否需要合并以及合并的主键
* 标记哪些属性需要合并，哪个是主键
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface CollectCustomMerge {
/**
* 是否需要合并单元格
*/
boolean needMerge() default false;
/**
* 是否是主键,即该字段相同的行合并
*/
boolean isPk() default false;
}
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.easyexcle.demo.easyExcel.annotion.CollectCustomMerge;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: lkz
* @Title: CustomMergeStrategy
* @Description: 自定义单元格合并策略类CustomMergeStrategy，当Excel中两列主键相同时，合并被标记需要合并的列
* @Date: 2023/7/5 9:16
*/
public class CustomMergeStrategy implements RowWriteHandler {
/**
* 主键下标
*/
private Integer pkIndex;
/**
* 需要合并的列的下标集合
*/
private List<Integer> needMergeColumnIndex = new ArrayList<>();
/**
* DTO数据类型
*/
private Class<?> elementType;
public CustomMergeStrategy(Class<?> elementType) {
this.elementType = elementType;
}
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
// 如果是标题,则直接返回
if (isHead) {
return;
}
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
if (null == pkIndex) {
this.lazyInit(writeSheetHolder);
}
// 判断是否需要和上一行进行合并
// 不能和标题合并，只能数据行之间合并
if (row.getRowNum() <= 1) {
return;
}
// 获取上一行数据
Row lastRow = sheet.getRow(row.getRowNum() - 1);
// 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
if (lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())) {
for (Integer needMerIndex : needMergeColumnIndex) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(),
needMerIndex, needMerIndex);
sheet.addMergedRegionUnsafe(cellRangeAddress);
}
}
}
/**
* 初始化主键下标和需要合并字段的下标
*/
private void lazyInit(WriteSheetHolder writeSheetHolder) {
// 获取当前sheet
Sheet sheet = writeSheetHolder.getSheet();
// 获取标题行
Row titleRow = sheet.getRow(0);
// 获取DTO的类型
Class<?> eleType = this.elementType;
// 获取DTO所有的属性
Field[] fields = eleType.getDeclaredFields();
// 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
for (Field theField : fields) {
// 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
ExcelProperty easyExcelAnno = theField.getAnnotation(ExcelProperty.class);
// 为空,则表示该字段不需要导入到excel,直接处理下一个字段
if (null == easyExcelAnno) {
continue;
}
// 获取自定义的注解，用于合并单元格
CollectCustomMerge collectCustomMerge = theField.getAnnotation(CollectCustomMerge.class);
// 没有@CustomMerge注解的默认不合并
if (null == collectCustomMerge) {
continue;
}
for (int index = 0; index < fields.length; index++) {
Cell theCell = titleRow.getCell(index);
// 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
if (null == theCell) {
continue;
}
// 将字段和excel的表头匹配上
if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
if (collectCustomMerge.isPk()) {
pkIndex = index;
}
if (collectCustomMerge.needMerge()) {
needMergeColumnIndex.add(index);
}
}
}
}
// 没有指定主键，则异常
if (null == this.pkIndex) {
throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
}
}
}
public class CollectTest {
public static void main(String[] args) {
List<OrderData> orderDataList=new ArrayList<>();
orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称1","标题1"));
orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称2","标题2"));
orderDataList.add(new OrderData("2",new Date(),"北京","002","商品名称02","标题02"));
orderDataList.add(new OrderData("2",new Date(),"北京","003","商品名称03","标题1"));
orderDataList.add(new OrderData("1",new Date(),"南京","001","商品名称3","标题3"));
orderDataList.add(new OrderData("3",new Date(),"上海","001","商品名称3","标题3"));
orderDataList.add(new OrderData("3",new Date(),"天津","001","商品名称302","标题302"));
orderDataList.add(new OrderData("3",new Date(),"上海","001","商品名称303","标题303"));
orderDataList.add(new OrderData("4",new Date(),"杭州","001","商品名称4","标题4"));
orderDataList.add(new OrderData("5",new Date(),"杭州","001","商品名称5","标题5"));
EasyExcel.write("D:/pdf/CollectExport.xlsx").head(OrderData.class)
.registerWriteHandler(new CustomMergeStrategy(OrderData.class))
.sheet().doWrite(orderDataList);
}
}
