记录工作中使用easyExcel实现复杂一对多excel表格导出及多sheet页导出

中才实用

已于 2023-06-19 10:43:13 修改

阅读量9.3k

点赞数 10

分类专栏： easyExcel 文章标签： easyExcel 一对多导出多sheet页导出复杂导出

于 2023-06-19 10:40:27 首次发布

本文链接：https://blog.csdn.net/weixin_39393393/article/details/131280729

版权

easyExcel 专栏收录该内容

1 篇文章

订阅专栏

该文章介绍了如何在一个工单对应多个项目，项目又对应多个配件信息的业务场景下，利用EasyExcel库进行数据导出。通过配置控制器、服务层实现以及自定义的导出类模板和合并策略处理器，实现了工单级联信息的Excel表格导出，包括工单号、车牌号、客户名称等详细信息，并支持多行合并。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

业务场景

一个工单对应多个项目，一个项目对应多个配件信息。这样形成了三层级联的一对多的业务场景。

实现效果如下：
在这里插入图片描述

功能实现

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);
            }
        }
    }
}

多sheet页导出

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();