文章介绍了如何使用EasyExcel3.1.1版本在仓库管理系统中实现多列和单列相同数据的合并功能，包括自定义合并策略、控制层API、业务逻辑处理和自定义导出类模板。作者详细描述了如何通过`ExcelCellByMultiHandler`和`ExcelMergeCustomerCellHandler`处理多列和单列的合并情况。
目录
4、自定义导出类模板（InWarehouseSumExcel）
5、自定义导出类模板（RealTimeInventoryExcel）
首先此次使用的是easyexcel3.1.1版本，需要引入如下依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>其次，基本思路就是，通过继承CellWriteHandler接口编写合并策略（主要就是实现该方法afterCellDispose，该方法在对单元格的所有操作完成后调用），再构建表格处理对象ExcelWriter时，将所需合并策略传入即可。
一、多列相同合并
1、需求效果图
从效果图可以知道，需要将前五列信息相同的合并为一行，然后该五列合并为一行的信息对多个设备信息。实现过程，大致如下：
2、控制层
@GetMapping(value = "/inWarehouseExport", name = "仓库管理，导出入库库存统计")
public void inWarehouseExport(@RequestParam(value = "wareHouseId", required = false) Integer wareHouseId,
@RequestParam(value = "inWarehouseType", required = false) String inWarehouseType,
@RequestParam(value = "equipAttributeId", required = false) Integer equipAttributeId,
@RequestParam(value = "keyWords", required = false) String keyWords,
@RequestParam(value = "startDate", required = false) String startDate,
@RequestParam(value = "endDate", required = false) String endDate,
HttpServletResponse response) {
wmReportSumService.inWarehouseExport(wareHouseId, inWarehouseType, equipAttributeId, keyWords, startDate, endDate, response);
}3、业务层
public void inWarehouseExport(Integer wareHouseId, String inWarehouseType, Integer equipAttributeId, String keyWords, String startDate, String endDate, HttpServletResponse response) {
try {
// 定义日期格式
List<InWarehouseSumDto> list = inWarehouseMapper.queryInWarehouseSum(wareHouseId, inWarehouseType, equipAttributeId, keyWords, startDate, endDate);
if (CollectionUtils.isEmpty(list)) {
return;
}
//遍历结果，进行处理
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
List<InWarehouseSumExcel> excelList = new ArrayList<>();
list.forEach(e -> {
List<InventoryEquipInfoDto> equipInfoDtoList = e.getEquipInfoDtoList();
equipInfoDtoList.forEach(dto -> {
InWarehouseSumExcel sumExcel = new InWarehouseSumExcel();
BeanUtils.copyProperties(e, sumExcel);
BeanUtils.copyProperties(dto, sumExcel);
sumExcel.setInWarehouseTimeStr(formatter.format(e.getInWarehouseTime()));
excelList.add(sumExcel);
});
});
//将处理好的结果导出
PoiUtils.exportExcel(response, "入库库存统计", "入库库存", excelList, InWarehouseSumExcel.class, new ExcelCellByMultiHandler(6));
} catch (Exception e) {
log.error("入库库存报表导出失败", e);
}
}需要注意的是，这个传入的excelList结果，一定是要处理好的，要保证前五列相同的都是在一起的，中间不要隔着其他前五列不相同的数据。
4、自定义导出类模板（InWarehouseSumExcel）
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
@Data
@Accessors(chain = true)
@EqualsAndHashCode
@HeadRowHeight(60) //设置表头行高度
@ContentRowHeight(25) //设置内容行高度
@ColumnWidth(15) //指定列宽大小为15
@HeadStyle(fillForegroundColor = 44) //设置表头背景颜色
public class InWarehouseSumExcel implements Serializable {
@ExcelProperty({"仓库"})
@ColumnWidth(20)
private String warehouseName;
@ExcelProperty({"入库类型"})
private String inWarehouseTypeName;
@ExcelProperty({"入库日期"})
private String inWarehouseTimeStr;
@ExcelProperty({"入库单号"})
private String inWarehouseCode;
@ExcelProperty({"入库人"})
private String inWarehouseMan;
/**
* 以下是一个多层标头，为了达到这种效果easyExcel会把入库设备明细该列进行合并，然后上面的单层标头为了和这个多层标头
* 对应上，也会进行行合并，所以最后合并区域大小就为6，再使用合并策略的时候就需要指定初始合并区域大小为6
*/
@ExcelProperty({"入库设备明细","设备属性"})
private String equipAttributeName;
@ExcelProperty({"入库设备明细","设备名称"})
@ColumnWidth(20)
private String equipName;
@ExcelProperty({"入库设备明细","品牌"})
private String equipBrand;
@ExcelProperty({"入库设备明细","型号"})
@ColumnWidth(20)
private String equipModel;
@ExcelProperty({"入库设备明细","设备序列号"})
private String equipSerial;
@ExcelProperty({"入库设备明细","设备编码"})
private String equipCode;
@ExcelProperty({"入库设备明细","入库数量"})
private String num;
}
5、PoiUtils工具类
package vip.dtcloud.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
/**
* POI相关操作
*
* @author Louis
* @date Jan 14, 2019
*/
public class PoiUtils {
public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List excelList, Class<?> clazz, CellWriteHandler mergeStrategy) throws IOException {
fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
// 设置表头样式
WriteCellStyle headStyle = new WriteCellStyle();
headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置表格内容样式
WriteCellStyle bodyStyle = new WriteCellStyle();
bodyStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 拿到表格处理对象
ExcelWriter writer = EasyExcel.write(response.getOutputStream())
.needHead(true)
.excelType(ExcelTypeEnum.XLSX)
// 设置合并策略
.registerWriteHandler(mergeStrategy)
.registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, bodyStyle))
.build();
// 设置表格sheet样式,并写入excel
WriteSheet sheet = EasyExcel.writerSheet(sheetName).head(clazz).sheetNo(1).build();
writer.write(excelList, sheet);
writer.finish();
}
}
6、自定义多列相同合并策略处理类
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.List;
/**
* @ClassName:ExcelMergeCustomerCellHandler
* @Description:easyexcel合并策略处理类，多列相同合并策略
* @Version:1.0
* @Date:2023/9/7 09:36:52
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@Slf4j
public class ExcelCellByMultiHandler implements CellWriteHandler {
/**
* 用第几行开始合并 ，默认为1，因为第0行是标题，EasyExcel 的默认也是
*/
private int mergeRowIndex = 1;
/**
* 参与合并的列，起始列位置，默认为0
*/
private int mergeColumnStart = 0;
/**
* 参与合并的列，最后一列的位置，默认为4
*/
private int mergeColumnEnd = 4;
/**
* 默认初始合并区域大小，标头有合并的，则需要指定标头合并后的合并区域大小，没有的话就是0
*/
private int defaultMergeRegionSize = 0;
public ExcelCellByMultiHandler(int defaultMergeRegionSize) {
this.defaultMergeRegionSize = defaultMergeRegionSize;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
}
/*
* 在对单元格的所有操作完成后调用
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list,
Cell cell, Head head, Integer integer, Boolean aBoolean) {
//当前行
int curRowIndex = cell.getRowIndex();
//当前列
int curColIndex = cell.getColumnIndex();
if (curRowIndex > mergeRowIndex && curColIndex >= mergeColumnStart) {
// 读取到mergeColumnEnd列，开始合并
if (curColIndex == mergeColumnEnd) {
mergeWithPrevRow(writeSheetHolder, cell, curRowIndex);
}
}
}
/**
* @Description: 合并比较多列值是否相同
* @Date: 2023/9/8 16:31
* @Param writeSheetHolder: 通过此对象获取sheet等信息
* @Param cell: 当前单元格
* @Param curRowIndex: 当前行
* @return: void
**/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex) {
StringBuilder curString = new StringBuilder();
StringBuilder preString = new StringBuilder();
try {
for (int i = mergeColumnStart; i <= mergeColumnEnd; i++) {
Cell curCell = cell.getSheet().getRow(curRowIndex).getCell(i);
Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(i);
//根据单元格类型获取单元格的值
Object curData = curCell.getCellType() == CellType.STRING ? curCell.getStringCellValue() : curCell.getNumericCellValue();
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
curString.append(curData);
preString.append(preData);
}
// 比较当前行的单元格与上一行是否相同，相同合并当前单元格与上一行
Sheet sheet = writeSheetHolder.getSheet();
// 获取合并信息
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
if (curString.toString().equals(preString.toString())) {
boolean isMerged = false;
// 下标
int index = 0;
for (int i = 0; i < mergeRegions.size(); i++) {
CellRangeAddress cellRangeAddr = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (cellRangeAddr.isInRange(curRowIndex - 1, index)) {
//这个移除的要保证是也被合并的那个
sheet.removeMergedRegion(defaultMergeRegionSize);
cellRangeAddr.setLastRow(curRowIndex);
sheet.addMergedRegion(cellRangeAddr);
isMerged = true;
}
index++;
// 这个是为了在默认合并单元大小后，再重置index值，index表示的就是列，这样才能保证进行isInRange判断的时候能够对应上
if (index > defaultMergeRegionSize-1) {
index = 0;
}
}
if (!isMerged) {
for (int i = 0; i <= mergeColumnEnd; i++) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, i, i);
sheet.addMergedRegion(cellRangeAddress);
}
}
}else { //不相等则更新默认合并区域大小
defaultMergeRegionSize = mergeRegions.size();
}
} catch (Exception e) {
log.error("仓库管理设备出入库统计合并单元格当前str==========" + curString, e);
log.error("仓库管理设备出入库统计合并单元格上一次str==========" + preString, e);
}
}
}
二、单列相同合并
1、需求效果图
根据效果图，得知，需要把第一列相同的进行合并。合并思路，同多列合并相同，只是传入不同的合并策略。
2、控制层
@GetMapping(value = "/exportRealTimeInventory", name = "仓库管理，导出实时库存")
public void exportRealTimeInventory(@RequestParam(value = "wareHouseId", required = false) Integer wareHouseId,
@RequestParam(value = "equipAttributeId", required = false) Integer equipAttributeId,
@RequestParam(value = "equipNameId", required = false) Integer equipNameId,
@RequestParam(value = "keyWords", required = false) String keyWords,
HttpServletResponse response) {
wmReportSumService.exportRealTimeInventory(wareHouseId, equipAttributeId, equipNameId, keyWords, response);
}3、业务层
@Override
public void exportRealTimeInventory(Integer wareHouseId, Integer equipAttributeId, Integer equipNameId, String keyWords, HttpServletResponse response) {
try {
List<WarehouseInventoryDto> list = repertoryInfoMapper.queryByCondition(wareHouseId, equipAttributeId, equipNameId, keyWords);
if (CollectionUtils.isEmpty(list)) {
return;
}
List<RealTimeInventoryExcel> excelList = new ArrayList<>();
int inNumTotal = 0, outNumTotal = 0, numTotal = 0;
//遍历结果，进行处理
for (WarehouseInventoryDto dto : list) {
RealTimeInventoryExcel inventoryExcel = new RealTimeInventoryExcel();
BeanUtils.copyProperties(dto, inventoryExcel);
excelList.add(inventoryExcel);
//统计出汇总数据
inNumTotal = inNumTotal + dto.getInNum();
outNumTotal = outNumTotal + dto.getOutNum();
numTotal = numTotal + dto.getNum();
}
//将结果排序,使相同仓库名称的数据在一起，为后面行合并提供条件
Collator collator = Collator.getInstance(Locale.CHINA);
excelList.sort(Comparator.comparing(RealTimeInventoryExcel::getWarehouseName, (s1, s2) -> collator.compare(s2, s1)));
//追加汇总数据
RealTimeInventoryExcel inventoryExcel = new RealTimeInventoryExcel();
inventoryExcel.setWarehouseName(WmConstant.EXCEL_HEAD_SUMMARIZING).setInNum(inNumTotal)
.setOutNum(outNumTotal).setNum(numTotal);
excelList.add(inventoryExcel);
//将处理好的结果导出
PoiUtils.exportExcel(response, "实时库存统计", "实时库存", excelList, RealTimeInventoryExcel.class, new ExcelMergeCustomerCellHandler(new int[]{0}, 0));
} catch (Exception e) {
log.error("实时库存报表导出失败", e);
}
}同多列合并一样，要保证excelList集合结果中仓库名称相同的连在一起，即 需要合并的列对应的字段相同的记录需要在一起。
4、自定义单列相同合并策略
package vip.dtcloud.common;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
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;
/**
* @ClassName:ExcelMergeCustomerCellHandler
* @Description:easyexcel合并策略处理类,单列相同合并策略
* @Version:1.0
* @Date:2023/9/7 09:36:52
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelMergeCustomerCellHandler implements CellWriteHandler {
/**
* 一级合并的列，从0开始算
*/
private int[] mergeColIndex;
/**
* 从指定的行开始合并，从0开始算
*/
private int mergeRowIndex;
/**
* 在单元格上的所有操作完成后调用，遍历每一个单元格，判断是否需要向上合并
*/
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
// 获取当前单元格行下标
int currRowIndex = cell.getRowIndex();
// 获取当前单元格列下标
int currColIndex = cell.getColumnIndex();
// 判断是否大于指定行下标，如果大于则判断列是否也在指定的需要的合并单元列集合中
if (currRowIndex > mergeRowIndex) {
for (int i = 0; i < mergeColIndex.length; i++) {
if (currColIndex == mergeColIndex[i]) {
if (currColIndex <= 18) {
// 一级合并唯一标识
Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
// 判断两条数据的是否是同一集合，只有同一集合的数据才能合并单元格
if (preLevelOneCode.equals(currLevelOneCode)) {
// 如果都符合条件，则向上合并单元格
mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
break;
}
} else {
// 一级合并唯一标识
Object currLevelOneCode = cell.getRow().getCell(0).getStringCellValue();
Object preLevelOneCode = cell.getSheet().getRow(currRowIndex - 1).getCell(0).getStringCellValue();
// 二级合并唯一标识
Object currLevelTwoCode = cell.getRow().getCell(19).getStringCellValue();
Object preLevelTwoCode = cell.getSheet().getRow(currRowIndex - 1).getCell(19).getStringCellValue();
if (preLevelOneCode.equals(currLevelOneCode) && preLevelTwoCode.equals(currLevelTwoCode)) {
// 如果都符合条件，则向上合并单元格
mergeWithPrevRow(writeSheetHolder, cell, currRowIndex, currColIndex);
break;
}
}
}
}
}
}
/**
* 当前单元格向上合并
*
* @param writeSheetHolder 表格处理句柄
* @param cell 当前单元格
* @param currRowIndex 当前行
* @param currColIndex 当前列
*/
private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int currRowIndex, int currColIndex) {
// 获取当前单元格数值
Object currData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
// 获取当前单元格正上方的单元格对象
Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
// 获取当前单元格正上方的单元格的数值
Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
// 将当前单元格数值与其正上方单元格的数值比较
if (preData.equals(currData)) {
Sheet sheet = writeSheetHolder.getSheet();
List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
// 当前单元格的正上方单元格是否是已合并单元格
boolean isMerged = false;
for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
CellRangeAddress address = mergeRegions.get(i);
// 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
if (address.isInRange(currRowIndex - 1, currColIndex)) {
sheet.removeMergedRegion(i);
address.setLastRow(currRowIndex);
sheet.addMergedRegion(address);
isMerged = true;
}
}
// 若上一个单元格未被合并，则新增合并单元
if (!isMerged) {
CellRangeAddress cellRangeAddress = new CellRangeAddress(currRowIndex - 1, currRowIndex, currColIndex, currColIndex);
sheet.addMergedRegion(cellRangeAddress);
}
}
}
}
5、自定义导出类模板（RealTimeInventoryExcel）
package vip.dtcloud.domain.warehousemanage;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
/**
* @ClassName:RealTimeInventoryExcel
* @Description:实时库存导出类模板
* @Version:1.0
* @Date:2023/9/7 10:14:09
*/
@Data
@Accessors(chain = true)
@EqualsAndHashCode
@HeadRowHeight(60) //设置表头行高度
@ContentRowHeight(25) //设置内容行高度
@ColumnWidth(15) //指定列宽大小为15
@HeadStyle(fillForegroundColor = 44) //设置表头背景颜色
public class RealTimeInventoryExcel implements Serializable {
@ColumnWidth(20)
@ExcelProperty({"仓库"})
private String warehouseName;
@ExcelProperty({"设备属性"})
private String equipAttributeName;
@ExcelProperty({"设备名称"})
private String equipName;
@ExcelProperty({"品牌"})
private String equipBrand;
@ExcelProperty({"型号"})
private String equipModel;
@ExcelProperty({"总入库数量"})
private Integer inNum;
@ExcelProperty({"总出库数量"})
private Integer outNum;
@ExcelProperty({"当前库存数量"})
private Integer num;
}
扫一扫
1万+
到【灌水乐园】发言
