一个工单对应多个项目，一个项目对应多个配件信息。这样形成了三层级联的一对多的业务场景。
实现效果如下：
1、引入maven
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
2、controller
@ApiOperation("客户账单信息导出")
@PostMapping("/exportWorkOrderCascadeCustomerInfo")
public void exportWorkOrderCascadeCustomerInfo(@RequestBody WorkOrderCascadeCustomerDto param, HttpServletResponse response) throws Exception {
workOrderService.exportWorkOrderCascadeCustomerInfo(param, response);
}
3、serviceImpl
需要注意result的数据结构如下：
最后通过合并策略处理器将上面的数据接口合并如下：
@Override
public void exportWorkOrderCascadeCustomerInfo(WorkOrderCascadeCustomerDto param, HttpServletResponse response) throws Exception {
List<WorkOrderCustomerCascade> result = workOrderMapper.getWorkOrderCascadeCustomerPageInfo(param)；
String fileName = java.net.URLEncoder.encode("工单级联信息" + DateFormatUtils.format(org.apache.commons.lang3.time.DateUtils.addDays(new Date(), -1), "yyyyMMdd") + ".xlsx", "UTF-8");
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
// 设置表头样式
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)
// 设置需要待合并的行和列。参数1：数值数组，指定需要合并的列；参数2：数值，指定从第几行开始合并
.registerWriteHandler(new ExcelMergeCustomerCellHandler(new int[]{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,20,21,22}, 1))
.registerWriteHandler(new HorizontalCellStyleStrategy(headStyle, bodyStyle))
.build();
// 设置表格sheet样式,并写入excel
WriteSheet sheet = EasyExcel.writerSheet("工单级联信息").head(WorkOrderCustomerCascade.class).sheetNo(1).build();
writer.write(result, sheet);
writer.finish();
}
4、自定义导出类模板（WorkOrderCustomerCascade）
package com.zdft.bhdcm.dispatch.entity.vo.excel;
import com.alibaba.excel.annotation.ExcelIgnore;
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 io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import java.math.BigDecimal;
import java.util.Date;
@Data
@EqualsAndHashCode
@HeadRowHeight(60)
@ContentRowHeight(25)
@ColumnWidth(15)
@HeadStyle(fillForegroundColor = 44)
@NoArgsConstructor
@AllArgsConstructor
public class WorkOrderCustomerCascade {
@ColumnWidth(20)
@ExcelProperty({"工单号"})
@ApiModelProperty(value = "工单号")
private String orderCode;
@ExcelProperty({"车牌号"})
@ApiModelProperty(value = "车牌号")
private String carNumber;
@ColumnWidth(25)
@ExcelProperty({"客户名称"})
@ApiModelProperty(value = "客户名称")
private String customerName;
@ColumnWidth(25)
@ExcelProperty({"修理厂名称"})
@ApiModelProperty(value = "修理厂名称")
private String factoryName;
@ExcelProperty({"订单类型"})
@ApiModelProperty(value = "订单类型")
private String workOrderType;
@ExcelProperty({"工单状态"})
@ApiModelProperty(value = "工单状态")
private String workOrderStatus;
@ExcelProperty({"故障提报类型"})
@ApiModelProperty(value = "故障提报类型")
private String faultSubmitType;
@ColumnWidth(25)
@ExcelProperty({"故障提报描述"})
@ApiModelProperty(value = "故障提报描述")
private String faultSubmitDesc;
@ColumnWidth(20)
@ExcelProperty({"工单创建时间"})
@ApiModelProperty(value = "工单创建时间")
private String createTime;
@ColumnWidth(20)
@ExcelProperty({"派单日期"})
@ApiModelProperty(value = "派单日期")
private String dispatchTime;
@ColumnWidth(20)
@ExcelProperty({"开始施工时间"})
@ApiModelProperty(value = "开始施工时间")
private String workStartTime;
@ColumnWidth(20)
@ExcelProperty({"完成施工时间"})
@ApiModelProperty(value = "完成施工时间")
private String workEndTime;
@ColumnWidth(20)
@ExcelProperty({"工单关单时间"})
@ApiModelProperty(value = "工单关单时间")
private String closeTime;
@ExcelProperty({"工时费合计"})
@ApiModelProperty(value = "工时费合计")
private BigDecimal itemPriceSum;
@ExcelProperty({"配件合计[元]"})
@ApiModelProperty(value = "配件合计[元]")
private BigDecimal partsTotalSum;
@ExcelProperty({"总合计"})
@ApiModelProperty(value = "总合计")
private BigDecimal allTotalSum;
@ExcelProperty({"海容供件[元]"})
@ApiModelProperty(value = "海容供件[元]")
private BigDecimal companyPartsTotalSum;
@ExcelProperty({"修理厂供件[元]"})
@ApiModelProperty(value = "修理厂供件[元]")
private BigDecimal factoryPartsTotalSum;
@ColumnWidth(25)
@ExcelProperty({"工单描述"})
@ApiModelProperty(value = "工单描述")
private String remark;
@ExcelIgnore
@ApiModelProperty(value = "项目ID")
private String itemId;
@ExcelIgnore
@ApiModelProperty(value = "项目编码")
private String itemCode;
@ColumnWidth(40)
@ExcelProperty({"项目信息", "项目名称"})
@ApiModelProperty(value = "项目名称")
private String itemName;
@ExcelProperty({"项目信息", "工时费单价[元]"})
@ApiModelProperty(value = "工时费单价[元]")
private BigDecimal repairUnitPrice;
@ExcelProperty({"项目信息", "项目数量"})
@ApiModelProperty(value = "维修项目次数")
private Integer itemCount;
@ExcelProperty({"项目信息", "工时费小计[元]"})
@ApiModelProperty(value = "工时费小计[元]")
private BigDecimal itemPrice;
@ExcelProperty({"配件信息", "供货渠道"})
@ApiModelProperty(value = "供货渠道")
private String goodsFlag;
@ExcelProperty({"配件信息", "配件编码"})
@ApiModelProperty(value = "配件编码")
private String productCode;
@ColumnWidth(40)
@ExcelProperty({"配件信息", "配件名称"})
@ApiModelProperty(value = "配件名称")
private String goodsName;
@ExcelProperty({"配件信息", "配件数量"})
@ApiModelProperty(value = "配件数量")
private Integer number;
@ExcelProperty({"配件信息", "生态价[元]"})
@ApiModelProperty(value = "生态价[元]")
private BigDecimal costPrice;
@ExcelProperty({"配件信息", "销售价[元]"})
@ApiModelProperty(value = "销售价[元]")
private BigDecimal salesPrice;
@ExcelProperty({"配件信息", "配件小计[元]"})
@ApiModelProperty(value = "配件小计[元]")
private BigDecimal partsTotal;
}
5、自定义合并策略处理类（ExcelMergeCustomerCellHandler）
package com.zdft.bhdcm.dispatch.service.impl;
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;
@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.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
// 获取当前单元格正上方的单元格对象
Cell preCell = cell.getSheet().getRow(currRowIndex - 1).getCell(currColIndex);
// 获取当前单元格正上方的单元格的数值
Object preData = preCell.getCellTypeEnum() == 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);
}
}
}
}
String userAgent = request.getHeader("User-Agent");
String fileFix = reportType == 1?"日报客户商品报表":"月报客户商品报表";
String fileFmt = fileFix + DateFormatUtils.format(DateUtils.addDays(new Date(), -1), "yyyyMMdd") + ".xlsx";
// 针对IE或者以IE为内核的浏览器：
String fileName = null;
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
fileName = java.net.URLEncoder.encode(fileFmt, "UTF-8");
} else {
// 非IE浏览器的处理：
fileName = new String(fileFmt.getBytes("UTF-8"), "ISO-8859-1");
}
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
for (int i = 0; i < result.size(); i++) {
CustomerResultModel customerResultModel = result.get(i);
String customerId = customerResultModel.getBindId();
String customerName = customerResultModel.getName();
List<GoosInfo> goods = customerMapper.queryCustomerGoodsInfo(customerId,reportType);
goods = goods.stream().map(t -> {
if ("2".equals(t.getIsConfirmContractPrice())) {
t.setIsConfirmContractPrice("已确认供货");
}
if ("0".equals(t.getIsConfirmContractPrice()) ) {
t.setIsConfirmContractPrice("待确认");
}
String goodItem = t.getGoodsCode();
if(StringUtils.isNotBlank(goodItem)){
Integer salesNumber = customerMapper.queryCustomerGoodsNumSum(customerId,goodItem);
t.setSalesNumber(String.valueOf(salesNumber));
}else{
t.setSalesNumber("0");
}
return t;
}).collect(Collectors.toList());
WriteSheet writeSheet = EasyExcel.writerSheet(i, customerName).head(GoosInfo.class).build();
excelWriter.write(goods, writeSheet);
}
excelWriter.finish();