导出-多表头

1.导出的实体类

package com.weareint.produce.controller.report.resp.productiondailyreport;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.util.Date;

/**

 * @Date: 2024/3/7 11:21
 * @description:
 **/
@Data
public class ProductionDailyReportExportResp {

    @Schema(description = "序号")
    @ExcelProperty(value ={"${bigHead}","序号"}, index = 0)
    private Integer sequenceNo;

    @Schema(description = "日期")
    @ExcelProperty(value ={"${bigHead}","日期"}, index = 1,format = "yyyy-MM-dd")
    private Date date;

    @Schema(description = "主计划编码")
    @ExcelProperty(value ={"${bigHead}","主计划编码"}, index = 2)
    private String planCode;

    @Schema(description = "ERP工单号")
    @ExcelProperty(value ={"${bigHead}","ERP工单号"}, index = 3)
    private String erpCode;

    @Schema(description = "产线名称")
    @ExcelProperty(value ={"${bigHead}","产线名称"}, index = 4)
    private String productionLineName;

    @Schema(description = "客户")
    @ExcelProperty(value ={"${bigHead}","客户"}, index = 5)
    private String customerName;

    @Schema(description = "产品编码")
    @ExcelProperty(value ={"${bigHead}","产品编码"}, index = 6)
    private String materialCode;

    @Schema(description = "产品名称")
    @ExcelProperty(value ={"${bigHead}","产品名称"}, index = 7)
    private String materialName;

    @Schema(description = "电芯投入")
    @ExcelProperty(value ={"${bigHead}","${dateHead}","电芯投入"}, index = 8)
    private Integer inputBatteriesQuantity;

    @Schema(description = "模组1P8S数量")
    @ExcelProperty(value ={"${bigHead}","${dateHead}","${triHead}","1P8S"}, index = 9)
    private Integer module1p8sQuantity;

    @Schema(description = "模组1P5S数量")
    @ExcelProperty(value ={"${bigHead}","${dateHead}","${triHead}","1P5S"}, index = 10)
    private Integer module1p5sQuantity;

    @Schema(description = "PACK在制数量")
    @ExcelProperty(value ={"${bigHead}","${dateHead}","${fourdHead}","在制品"}, index = 11)
    private Integer packagingQuantity;

    @Schema(description = "PACK下线数量")
    @ExcelProperty(value ={"${bigHead}","${dateHead}","${fourdHead}","下线"}, index = 12)
    private Integer packOfflineQuantity;



    @Schema(description = "测试待入库数量")
    @ExcelProperty(value ={"${bigHead}","测试待入库"}, index =13)
    private Integer testWarehousedQuantity;


}

2.导出接口

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.weareint.basic.response.Result;
import com.weareint.produce.constants.CommonConstants;
import com.weareint.produce.controller.report.req.productiondailyreport.ProductionDailyReportQueryReq;
import com.weareint.produce.controller.report.resp.productiondailyreport.ProductionDailyReportExportResp;
import com.weareint.produce.controller.report.resp.productiondailyreport.ProductionDailyReportResp;
import com.weareint.produce.excel.easyexcel.ExcelTitleHandler;
import com.weareint.produce.service.report.ProductionDailyReportService;
import com.weareint.produce.utils.ExcelUtil;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
  


  @Operation(summary = "生产日报表导出")
    @PostMapping("/export")
    public void export(@Valid @RequestBody ProductionDailyReportQueryReq req, HttpServletResponse response) throws Exception{
        //各种业务处理
        List<ProductionDailyReportResp> dailyReportResps = productionDailyReportService.queryProDailyReportExport(req);
        List<ProductionDailyReportExportResp> exportResps = BeanUtil.copyToList(dailyReportResps, ProductionDailyReportExportResp.class);
        int sequenceNo = 0;
        for (ProductionDailyReportExportResp exportResp : exportResps) {
            exportResp.setSequenceNo(++sequenceNo);
        }
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("生产日报表", StandardCharsets.UTF_8)
                .replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        EasyExcel.write(response.getOutputStream(), ProductionDailyReportExportResp.class)
                .sheet("汇总")
                .registerWriteHandler(new ExcelTitleHandler("生产日报表","生产情况","模组在制品","PACK段"))
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(CommonConstants.INTEGER_TWENTY))
                .registerWriteHandler(ExcelUtil.getStyleStrategy())
                .doWrite(exportResps);
    }

3.工具类

package com.weareint.produce.excel.easyexcel;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
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 org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.PropertyPlaceholderHelper;

import java.util.List;
import java.util.Optional;
import java.util.Properties;

public class ExcelTitleHandler implements CellWriteHandler {

    private String bigHead;

    private String dateHead;

    private String triHead;

    private String fourdHead;

    private PropertyPlaceholderHelper placeholderHelper = new PropertyPlaceholderHelper("${", "}");

    public ExcelTitleHandler( String bigHead,String dateHead) {
        this.bigHead = bigHead; //表头1
        this.dateHead = dateHead;  //表头2
    }

    public ExcelTitleHandler( String bigHead,String dateHead,String triHead,String fourdHead) {
        this.bigHead = bigHead; //表头1
        this.dateHead = dateHead;  //表头2
        this.triHead = triHead;  //表头3
        this.fourdHead = fourdHead;  //表头4
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 动态设置表头字段
        if (!ObjectUtil.isEmpty(head)) {
            List<String> headNameList = head.getHeadNameList();
            if (CollUtil.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                Optional.ofNullable(bigHead).ifPresent(s -> properties.setProperty("bigHead", bigHead));
                Optional.ofNullable(dateHead).ifPresent(s -> properties.setProperty("dateHead", dateHead));
                Optional.ofNullable(triHead).ifPresent(s -> properties.setProperty("triHead", triHead));
                Optional.ofNullable(fourdHead).ifPresent(s -> properties.setProperty("fourdHead", fourdHead));
                for (int i = 0 ; i < headNameList.size() ; i++){
                    // 循环遍历替换
                    headNameList.set(i, placeholderHelper.replacePlaceholders(headNameList.get(i), properties));
                }
            }
        }
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head
            , Integer relativeRowIndex, Boolean isHead) {
    }
}