easyExcel - 导出合并单元格
目录
- 前言
- 一、情景介绍
- 二、问题分析
- 三、代码实现
- 四、测试代码
前言
Java-easyExcel入门教程：https://blog.csdn.net/xhmico/article/details/134714025
之前有介绍过如何使用 easyExcel，以及写了两个入门的 demo ，这两个 demo 能应付在开发中大多数的导入和导出需求，不过有时候面对一些复杂的表格，就会有点不够用，该篇就是关于如何实现导出合并单元格
一、情景介绍
在做表格导出的开发中可能会遇到一些需要将表格中某些相同的单元格进行合并的情况，比如以下案例：
案例一：
将相同订单号的单元格进行合并
示例：
案例二：
将相同订单号的单元格进行合并，且同一订单号的总数和总金额进行合并
示例：
案例三：
将相同订单号的单元格进行合并，且同一订单号的总数和总金额进行合并，订单内相同商品分类进行合并，且分类总数和分类总金额根据订单号和商品分类进行合并
示例：
案例四：
将表中数据相同部分进行左右合并，上下合并
示例：
二、问题分析
上述几个案例是我在开发中遇见比较多的需要合并单元格的情况，在官方文档中也是有一小段的篇幅介绍了如何合并单元格
官方文档：合并单元格
但是很显然是无法满足上述案例中的要求的
在 POI 中我们可以使用 CellRangeAddress 进行单元格的合并，其构造方法如下：
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) {
...
}
firstRow：要合并的单元格区域的起始行索引（从 0 开始计数）lastRow：要合并的单元格区域的结束行索引（从 0 开始计数）firstCol：要合并的单元格区域的起始列索引（从 0 开始计数）lastCol：要合并的单元格区域的结束列索引（从 0 开始计数）
合并的单元格区域是一个矩形区域，由起始行和列索引与结束行和列索引确定
例如，可以使用 new CellRangeAddress(0, 1, 0, 2) 来指定要将第一行到第二行的前三列合并成一个单元格
同样在 easyExcel 中也可以使用 CellRangeAddress 来合并单元格，只需要定义一个专门用于合并单元格的拦截器，对比左右或者上下相邻的单元格其值是否一致，如果一致就通过 new CellRangeAddress(firstRow, lastRow, firstCol, lastCol) 设置好合并范围，再使用 Sheet 类的 int addMergedRegion(CellRangeAddress var1) 方法来添加合并的单元格
三、代码实现
AbstractMergeStrategy.java
import com.alibaba.excel.write.handler.CellWriteHandler;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* 合并单元格抽象类
*/
public abstract class AbstractMergeStrategy implements CellWriteHandler {
/**
* excel 最大行索引
*/
public final static int EXCEL_LAST_INDEX = 1048575;
/**
* 默认合并起始行
*/
public final static int DEFAULT_START_ROW_INDEX = 0;
public abstract void merge(Sheet sheet, Cell cell);
/**
* 获取单元格值
*/
public Object getCellValue(Cell cell) {
return cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
}
/**
* 解除已合并的单元格
*/
public void removeCellRangeAddress(Sheet sheet, int rowIndex, int columnIndex) {
List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();
for (int i = 0; i < mergedRegions.size(); i++) {
CellRangeAddress cellAddresses = mergedRegions.get(i);
// 判断上一行单元格是否已经被合并，是则先移出原有的合并单元，再重新添加合并单元
if (cellAddresses.isInRange(rowIndex, columnIndex)) {
sheet.removeMergedRegion(i);
break;
}
}
}
}
ColumnMergeStrategy.java
import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
/**
* 合并单元格策略：适用于列合并
*/
public class ColumnMergeStrategy extends AbstractMergeStrategy {
/**
* 合并起始行索引
*/
private final int mergeStartRowIndex;
/**
* 合并结束行索引
*/
private final int mergeEndRowIndex;
/**
* 待合并的列（如果没有指定，则所有的列都会进行合并）
*/
private final List<Integer> mergeColumnIndexList;
/**
* 待合并的主列
*/
private List<Integer> mergeMainColumnIndexList = new ArrayList<>();
/**
* 待合并的副列
*/
private List<Integer> mergeDeputyColumnIndexList = new ArrayList<>();
public ColumnMergeStrategy() {
this(DEFAULT_START_ROW_INDEX, EXCEL_LAST_INDEX);
}
public ColumnMergeStrategy(List<Integer> mergeColumnIndexList) {
this(DEFAULT_START_ROW_INDEX, EXCEL_LAST_INDEX, mergeColumnIndexList);
}
public ColumnMergeStrategy(List<Integer> mergeMainColumnIndexList, List<Integer> mergeDeputyColumnIndexList) {
if (CollUtil.isEmpty(mergeMainColumnIndexList)) {
throw new RuntimeException("The main column collection is empty");
}
mergeColumnIndexList = new ArrayList<>(mergeMainColumnIndexList);
if (CollUtil.isNotEmpty(mergeDeputyColumnIndexList)) {
boolean exitSameIndex = mergeDeputyColumnIndexList.stream().filter(mergeMainColumnIndexList::contains).findAny().orElse(null) != null;
if (exitSameIndex) {
throw new RuntimeException("The secondary column collection has the same elements as the main column");
}
mergeColumnIndexList.addAll(mergeDeputyColumnIndexList);
}
this.mergeMainColumnIndexList = mergeMainColumnIndexList;
this.mergeDeputyColumnIndexList = mergeDeputyColumnIndexList;
this.mergeStartRowIndex = DEFAULT_START_ROW_INDEX;
this.mergeEndRowIndex = EXCEL_LAST_INDEX;
}
public ColumnMergeStrategy(int mergeStartRowIndex) {
this(mergeStartRowIndex, EXCEL_LAST_INDEX);
}
public ColumnMergeStrategy(int mergeStartRowIndex, int mergeEndRowIndex) {
this(mergeStartRowIndex, mergeEndRowIndex, new ArrayList<>());
}
public ColumnMergeStrategy(int mergeStartRowIndex, int mergeEndRowIndex, List<Integer> mergeColumnIndexList) {
this.mergeStartRowIndex = mergeStartRowIndex;
this.mergeEndRowIndex = mergeEndRowIndex;
this.mergeColumnIndexList = mergeColumnIndexList;
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 头不参与合并
if (isHead) return;
// 如果当前行大于合并起始行则进行合并
if (cell.getRowIndex() >= mergeStartRowIndex && cell.getRowIndex() <= mergeEndRowIndex) {
// 判断是否是全列合并或者当前列在需要合并列中
if (CollUtil.isEmpty(mergeColumnIndexList) || (CollUtil.isNotEmpty(mergeColumnIndexList) && mergeColumnIndexList.contains(cell.getColumnIndex()))) {
// 合并单元格
this.merge(writeSheetHolder.getSheet(), cell);
}
}
}
public void merge(Sheet sheet, Cell cell) {
// 当前单元格行、列索引
int curRowIndex = cell.getRowIndex();
int curColumnIndex = cell.getColumnIndex();
// 合并区间
int startRow = curRowIndex;
// 当前单元格的值为
Object curCellValue = this.getCellValue(cell);
// 偏移量
int displacement = 0;
// 向上进行合并
while (true) {
// 向上移动一位
displacement = displacement + 1;
// 上一行的列位置
int aboveRowIndex = curRowIndex - displacement;
// 判断上一行是否合理
if (aboveRowIndex < 0 || aboveRowIndex < mergeStartRowIndex) {
break;
}
// 获取上一个单元格
Cell aboveCell = sheet.getRow(aboveRowIndex).getCell(curColumnIndex);
// 上一个单元格的值
Object aboveCellValue = this.getCellValue(aboveCell);
// 判断上一个单元格是否能合并
if (Objects.equals(curCellValue, aboveCellValue)) {
boolean needMerge = true;
// 判断当前列是否在副列范围内
if (mergeDeputyColumnIndexList.contains(curColumnIndex)) {
// 判断其对应的主列是否与上一行全部相同
for (Integer mainColumnIndex : mergeMainColumnIndexList) {
Cell mainCell = sheet.getRow(curRowIndex).getCell(mainColumnIndex);
Cell aboveMainCell = sheet.getRow(aboveRowIndex).getCell(mainColumnIndex);
Object mainCellValue = this.getCellValue(mainCell);
Object aboveMainCellValue = this.getCellValue(aboveMainCell);
if (!Objects.equals(mainCellValue, aboveMainCellValue)) {
needMerge = false;
break;
}
}
}
if (needMerge) {
startRow = aboveRowIndex;
// 移除原有的单元格
this.removeCellRangeAddress(sheet, aboveRowIndex, curColumnIndex);
} else {
break;
}
} else {
break;
}
}
if (startRow != curRowIndex) {
// 添加合并单元格
CellRangeAddress cellAddresses = new CellRangeAddress(startRow, curRowIndex, curColumnIndex, curColumnIndex);
sheet.addMergedRegion(cellAddresses);
}
}
}
FullCellMergeStrategy.java
import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.google.api.client.util.Maps;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
/**
* Excel导出单元格全量合并策略：
* - 该策略会左右，上下合并单元格
*/
public class FullCellMergeStrategy extends AbstractMergeStrategy {
/**
* 合并起始行索引
*/
private final int mergeStartRowIndex;
/**
* 合并结束行索引
*/
private final int mergeEndRowIndex;
/**
* 已合并的记录：
* - key: 对应行索引
* - value： 对应该行已合并过的单元格
*/
private final Map<Integer, List<int[]>> hadMergeRecord = Maps.newHashMap();
public FullCellMergeStrategy() {
this(DEFAULT_START_ROW_INDEX, EXCEL_LAST_INDEX);
}
public FullCellMergeStrategy(int mergeStartRowIndex) {
this(mergeStartRowIndex, EXCEL_LAST_INDEX);
}
public FullCellMergeStrategy(int mergeStartRowIndex, int mergeEndRowIndex) {
this.mergeStartRowIndex = mergeStartRowIndex;
this.mergeEndRowIndex = mergeEndRowIndex;
}
/**
* 在单元上的所有操作完成后调用(可以对单元格进行任何操作)
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 头不参与合并
if (isHead) return;
// 如果当前行大于合并起始行则进行合并
if (cell.getRowIndex() >= mergeStartRowIndex && cell.getRowIndex() <= mergeEndRowIndex) {
// 合并单元格
this.merge(writeSheetHolder.getSheet(), cell);
}
}
/**
* 当前单元格先向左合并，再向上合并
* @param sheet 当前sheet
* @param cell 当前单元格
*/
public void merge(Sheet sheet, Cell cell) {
// 当前单元格行、列索引
int curRowIndex = cell.getRowIndex();
int curColumnIndex = cell.getColumnIndex();
// 合并区间
int startRow = curRowIndex;
int startCol = curColumnIndex;
// 当前单元格的值为
Object curCellValue = this.getCellValue(cell);
int[] leftMergeColumn = null;
int removeCurRowCellRange = -1;
// 偏移量
int displacement = 0;
// 先向左进行合并
while (true) {
// 向左移动一位
displacement++;
// 左边单元格的索引位置
int leftColumnIndex = curColumnIndex - displacement;
if (leftColumnIndex < 0) {
// 左边单元格不存在，表明是该行第一个单元格，跳过合并
break;
}
// 获取左边单元格
Cell leftCell = sheet.getRow(curRowIndex).getCell(leftColumnIndex);
// 左边单元格的值
Object nextCellValue = this.getCellValue(leftCell);
// 如果相同则，则表明可以和左边的单元格进行合并
if (Objects.equals(curCellValue, nextCellValue)) {
// 查看当前行的所有已合并的单元格
List<int[]> mergeColumns = hadMergeRecord.get(curRowIndex);
if (CollUtil.isNotEmpty(mergeColumns)) {
// 判断左边的单元格是否处于合并状态
int[] lastMergeColumn = mergeColumns.get(mergeColumns.size() - 1);
if (leftColumnIndex >= lastMergeColumn[0] && leftColumnIndex <= lastMergeColumn[1]) {
// 修改单元格的合并范围
lastMergeColumn[1] = curColumnIndex;
startCol = lastMergeColumn[0];
// 移除左边原有的合并单元
removeCurRowCellRange = leftColumnIndex;
leftMergeColumn = lastMergeColumn;
} else {
// 左边单元格不在合并范围，则添加合并区间
startCol = leftColumnIndex;
int[] mergeColumn = {startCol, curColumnIndex};
mergeColumns.add(mergeColumn);
hadMergeRecord.put(curRowIndex, mergeColumns);
}
} else {
// 向左进行合并
startCol = leftColumnIndex;
// 添加合并区间
int[] mergeColumn = {startCol, curColumnIndex};
mergeColumns = new ArrayList<>();
mergeColumns.add(mergeColumn);
hadMergeRecord.put(curRowIndex, mergeColumns);
}
} else {
// 不同则直接跳出循环，合并终止
break;
}
}
boolean needRemoveCurRowCellRange = true;
// 重置偏移量
displacement = 0;
// 再向上进行合并
while (true) {
// 向上移动一位
displacement++;
// 上一行的列位置
int aboveRowIndex = curRowIndex - displacement;
// 判断上一行是否合理
if (aboveRowIndex < 0 || aboveRowIndex < mergeStartRowIndex) {
break;
}
// 获取上一个单元格
Cell aboveCell = sheet.getRow(aboveRowIndex).getCell(curColumnIndex);
// 上一个单元格的值
Object aboveCellValue = this.getCellValue(aboveCell);
// 判断上一个单元格是否能合并
if (Objects.equals(curCellValue, aboveCellValue)) {
// 判断上一个单元格是否为合并单元格
List<int[]> mergeColumns = hadMergeRecord.get(aboveRowIndex);
if (CollUtil.isNotEmpty(mergeColumns)) {
int[] aboveMergeColumn = null;
for (int[] mergeColumn : mergeColumns) {
if (curColumnIndex >= mergeColumn[0] && curColumnIndex <= mergeColumn[1]) {
aboveMergeColumn = mergeColumn;
break;
}
}
if (aboveMergeColumn != null) {
// 表明上一个单元格为合并单元格，再判断该合并单元格的区间是否与当前一致
if (aboveMergeColumn[0] == startCol && aboveMergeColumn[1] == curColumnIndex) {
startRow = aboveRowIndex;
// 移除原有的单元格
this.removeCellRangeAddress(sheet, aboveRowIndex, curColumnIndex);
}
} else {
startRow = aboveRowIndex;
}
} else {
startRow = aboveRowIndex;
// 移除原有的单元格
this.removeCellRangeAddress(sheet, aboveRowIndex, curColumnIndex);
}
} else {
int leftColumnIndex = curColumnIndex - 1;
if (leftColumnIndex < 0) {
break;
}
Cell leftAboveCell = sheet.getRow(aboveRowIndex).getCell(curColumnIndex);
Object leftAboveCellValue = this.getCellValue(leftAboveCell);
// 判断原左边单元格是否和左上单元格合并
if (Objects.nonNull(leftMergeColumn) && Objects.equals(curCellValue, leftAboveCellValue)) {
// 撤销合并
needRemoveCurRowCellRange = false;
startCol = curColumnIndex;
leftMergeColumn[1] = curColumnIndex -1;
}
break;
}
}
// 判断是否需要删除左边原合并的单元格
if (removeCurRowCellRange != -1 && needRemoveCurRowCellRange) {
this.removeCellRangeAddress(sheet, curRowIndex, removeCurRowCellRange);
}
if (startRow != curRowIndex || startCol != curColumnIndex) {
// 添加合并单元格
CellRangeAddress cellAddresses = new CellRangeAddress(startRow, curRowIndex, startCol, curColumnIndex);
sheet.addMergedRegion(cellAddresses);
}
}
}
四、测试代码
以下是用于验证情景说明中四个场景的测试代码：
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.mike.common.core.reactor.excel.strategy.FullCellMergeStrategy;
import com.mike.common.core.reactor.excel.strategy.ColumnMergeStrategy;
import com.mike.common.core.utils.DateUtils;
import com.mike.server.system.entity.OrderDetailEntity;
import org.junit.jupiter.api.Test;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;
public class TestDemo0 {
@Test
public void exportMerge() {
// 输出文件路径
String outFilePath = "D:\\excel-files\\exportMerge.xlsx";
Collection<?> data = data();
EasyExcelFactory.write(outFilePath, OrderDetailEntity.class)
// 案例一
//.registerWriteHandler(new ColumnMergeStrategy(Collections.singletonList(0)))
// 案例二
//.registerWriteHandler(new ColumnMergeStrategy(Collections.singletonList(0), Arrays.asList(8, 9)))
// 案例三
.registerWriteHandler(new ColumnMergeStrategy(Collections.singletonList(0), Arrays.asList(2, 10, 11)))
.registerWriteHandler(new ColumnMergeStrategy(Arrays.asList(0, 2), Arrays.asList(8, 9)))
.sheet("Sheet1").doWrite(data);
}
@Test
public void exportFullMerge() {
String outFilePath = "D:\\excel-files\\error.xlsx";
EasyExcelFactory.write(outFilePath)
.head(getCase4Head())
// 设置表头行高 30，内容行高 20
.registerWriteHandler(new SimpleRowHeightStyleStrategy((short)30,(short)20))
// 自适应表头宽度
//.registerWriteHandler(new MatchTitleWidthStyleStrategy())
// 案例四
.registerWriteHandler(new FullCellMergeStrategy())
.sheet("Sheet1").doWrite(getCase4Data());
}
private Collection<?> data() {
Map<String, List<String>> productMap = getProductMap();
List<String> statusList = Arrays.asList("待发货", "已发货", "运输中", "待取货", "已完成");
List<OrderDetailEntity> dataList = new ArrayList<>();
Random random = new Random();
int orderCount = random.nextInt(2) + 2;
for (int i = 0; i < orderCount; i++) {
String orderCode = "PL" + DateUtils.format(new Date(), "yyyyMMddHHmm") + "000" + i;
int orderDetailCount = random.nextInt(10) + 1;
List<OrderDetailEntity> detailEntities = new ArrayList<>();
Map<String, BigDecimal> categoryTotalQuantityMap = new HashMap<>();
Map<String, BigDecimal> categoryTotalPriceMap = new HashMap<>();
BigDecimal totalQuantity = BigDecimal.ZERO;
BigDecimal totalPrice = BigDecimal.ZERO;
for (int j = 0; j < orderDetailCount; j++) {
String orderDetailCode = UUID.randomUUID().toString();
String productCategory = new ArrayList<String>(productMap.keySet()).get(random.nextInt(productMap.size()));
List<String> productList = productMap.get(productCategory);
String productCode = "SKU" + (random.nextInt(1000)+1000);
String productName = productList.get(random.nextInt(productList.size())) + "-A" + random.nextInt(50);
BigDecimal price = new BigDecimal(random.nextInt(2000) + 800);
BigDecimal quantity = new BigDecimal(random.nextInt(5) + 1);
String status = statusList.get(random.nextInt(statusList.size()));
String key = orderCode + "-" + productCategory;
BigDecimal categoryTotalQuantity = categoryTotalQuantityMap.get(key);
if (categoryTotalQuantity == null) {
categoryTotalQuantity = quantity;
} else {
categoryTotalQuantity = categoryTotalQuantity.add(quantity);
}
categoryTotalQuantityMap.put(key, categoryTotalQuantity);
BigDecimal categoryTotalPrice = categoryTotalPriceMap.get(key);
if (categoryTotalPrice == null) {
categoryTotalPrice = price.multiply(quantity);
} else {
categoryTotalPrice = categoryTotalPrice.add(price.multiply(quantity));
}
categoryTotalPriceMap.put(key, categoryTotalPrice);
totalQuantity = totalQuantity.add(quantity);
totalPrice = totalPrice.add(price.multiply(quantity));
detailEntities.add(OrderDetailEntity.builder()
.orderCode(orderCode)
.orderDetailCode(orderDetailCode)
.productCategory(productCategory)
.productCode(productCode)
.productName(productName)
.price(price)
.quantity(quantity)
.status(status)
.build());
}
for (OrderDetailEntity item : detailEntities) {
String key = item.getOrderCode() + "-" + item.getProductCategory();
item.setCategoryTotalQuantity(categoryTotalQuantityMap.get(key));
item.setCategoryTotalPrice(categoryTotalPriceMap.get(key));
item.setTotalQuantity(totalQuantity);
item.setTotalPrice(totalPrice);
}
detailEntities = detailEntities.stream()
.sorted(Comparator.comparing(OrderDetailEntity::getOrderCode)
.thenComparing(OrderDetailEntity::getProductCategory))
.collect(Collectors.toList());
dataList.addAll(detailEntities);
}
return dataList;
}
private Map<String, List<String>> getProductMap() {
Map<String, List<String>> productMap = new HashMap<>();
// 家电
List<String> householdList = new ArrayList<>();
householdList.add("电视机");
householdList.add("冰箱");
householdList.add("洗衣机");
householdList.add("空调");
productMap.put("家电", householdList);
// 数码产品
List<String> digitalList = new ArrayList<>();
digitalList.add("手机");
digitalList.add("摄影机");
digitalList.add("电脑");
digitalList.add("照相机");
digitalList.add("投影仪");
digitalList.add("智能手表");
productMap.put("数码产品", digitalList);
// 健身器材
List<String> gymEquipmentList = new ArrayList<>();
gymEquipmentList.add("动感单车");
gymEquipmentList.add("健身椅");
gymEquipmentList.add("跑步机");
productMap.put("健身器材", gymEquipmentList);
return productMap;
}
private List<List<String>> getCase4Head() {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("导出时间");
head0.add("员工编码");
head0.add("员工编码");
List<String> head1 = new ArrayList<String>();
head1.add("导出时间");
head1.add("部门信息");
head1.add("部门编码");
List<String> head2 = new ArrayList<String>();
head2.add("导出时间");
head2.add("部门信息");
head2.add("部门名称");
List<String> head3 = new ArrayList<String>();
head3.add("导出时间");
head3.add("部门信息");
head3.add("负责人");
List<String> head4 = new ArrayList<String>();
head4.add("导出时间");
head4.add("个人信息");
head4.add("用户名称");
List<String> head5 = new ArrayList<String>();
head5.add("导出时间");
head5.add("个人信息");
head5.add("性别");
List<String> head6 = new ArrayList<String>();
head6.add("2024-04-09");
head6.add("个人信息");
head6.add("年龄");
List<String> head7 = new ArrayList<String>();
head7.add("2024-04-09");
head7.add("个人信息");
head7.add("出生日期");
List<String> head8 = new ArrayList<String>();
head8.add("2024-04-09");
head8.add("个人信息");
head8.add("学历");
List<String> head9 = new ArrayList<String>();
head9.add("2024-04-09");
head9.add("个人信息");
head9.add("电话号码");
List<String> head10 = new ArrayList<String>();
head10.add("2024-04-09");
head10.add("状态");
head10.add("状态");
list.add(head0);
list.add(head1);
list.add(head2);
list.add(head3);
list.add(head4);
list.add(head5);
list.add(head6);
list.add(head7);
list.add(head8);
list.add(head9);
list.add(head10);
return list;
}
private Collection<?> getCase4Data() {
List<Map<Integer, Object>> data = new ArrayList<>();
Map<Integer, Object> map1 = new HashMap<>();
map1.put(0,"exportTime");
map1.put(1,"exportTime");
map1.put(2,"exportTime");
map1.put(3,"exportTime");
map1.put(4,"exportTime");
map1.put(5,"exportTime");
map1.put(6,"currentData");
map1.put(7,"currentData");
map1.put(8,"currentData");
map1.put(9,"currentData");
map1.put(10,"currentData");
Map<Integer, Object> map2 = new HashMap<>();
map2.put(0,"employeeNo");
map2.put(1,"deptInfo");
map2.put(2,"deptInfo");
map2.put(3,"deptInfo");
map2.put(4,"userInfo");
map2.put(5,"userInfo");
map2.put(6,"userInfo");
map2.put(7,"userInfo");
map2.put(8,"userInfo");
map2.put(9,"userInfo");
map2.put(10,"status");
Map<Integer, Object> map3 = new HashMap<>();
map3.put(0,"employeeNo");
map3.put(1,"deptCode");
map3.put(2,"deptName");
map3.put(3,"deptHead");
map3.put(4,"username");
map3.put(5,"gender");
map3.put(6,"age");
map3.put(7,"birthday");
map3.put(8,"educational");
map3.put(9,"phone");
map3.put(10,"status");
data.add(map1);
data.add(map2);
data.add(map3);
return data;
}
}
相关封装类
OrderDetailEntity.java
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
// 头背景设置
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
//标题高度
@HeadRowHeight(30)
//内容高度
@ContentRowHeight(20)
//内容居中,左、上、右、下的边框显示
@ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, borderLeft = BorderStyleEnum.THIN, borderTop = BorderStyleEnum.THIN, borderRight = BorderStyleEnum.THIN, borderBottom = BorderStyleEnum.THIN)
public class OrderDetailEntity {
@ApiModelProperty(value = "订单号")
@ExcelProperty(value = "订单号")
@ColumnWidth(25)
private String orderCode;
@ApiModelProperty(value = "订单明细")
@ExcelProperty(value = "订单明细")
@ColumnWidth(40)
private String orderDetailCode;
@ApiModelProperty(value = "商品分类")
@ExcelProperty(value = "商品分类")
@ColumnWidth(20)
private String productCategory;
@ApiModelProperty(value = "商品编码")
@ExcelProperty(value = "商品编码")
@ColumnWidth(20)
private String productCode;
@ApiModelProperty(value = "商品名称")
@ExcelProperty(value = "商品名称")
@ColumnWidth(20)
private String productName;
@ApiModelProperty(value = "单价")
@ExcelProperty(value = "单价")
@ColumnWidth(10)
private BigDecimal price;
@ApiModelProperty(value = "数量")
@ExcelProperty(value = "数量")
@ColumnWidth(10)
private BigDecimal quantity;
@ApiModelProperty(value = "状态")
@ExcelProperty(value = "状态")
@ColumnWidth(10)
private String status;
@ApiModelProperty(value = "分类总数")
@ExcelProperty(value = "分类总数")
@ColumnWidth(20)
//@ExcelIgnore // 案例一、案例二放开该注解
private BigDecimal categoryTotalQuantity;
@ApiModelProperty(value = "分类总金额")
@ExcelProperty(value = "分类总金额")
@ColumnWidth(20)
//@ExcelIgnore // 案例一、案例二放开该注解
private BigDecimal categoryTotalPrice;
@ApiModelProperty(value = "总数")
@ExcelProperty(value = "总数")
@ColumnWidth(10)
//@ExcelIgnore // 案例一放开该注解
private BigDecimal totalQuantity;
@ApiModelProperty(value = "总金额")
@ExcelProperty(value = "总金额")
@ColumnWidth(10)
//@ExcelIgnore // 案例一放开该注解
private BigDecimal totalPrice;
}
参考文章：
EasyExcel自定义策略导出-合并单元格-设置表头和内容格式：https://blog.csdn.net/wmengnemw/article/details/141426333