EasyExcel 设置自定义表头

实体类写法

 //@ExcelProperty(value = {"${bigHead}","${dateHead}","团号"}, index = 0) 配置两行表头写法
    @ExcelProperty(value = {"${bigHead}","团号"}, index = 0)
    private String opuOrderNo;      // 团号
    @ExcelProperty(value = {"${bigHead}","房号"}, index = 1)
    private String roomNo;          // 房号
    @ExcelProperty(value = {"${bigHead}","房型"}, index = 2)
    private String roomMode;        // 房型
    @ExcelProperty(value = {"${bigHead}","姓名"}, index = 3)
    private String name;            // 姓名
    @ExcelProperty(value = {"${bigHead}","入住日期"}, index = 4)
    private String startDate;       // 入住日期
    @ExcelProperty(value = {"${bigHead}","离店日期"}, index = 5)
    private String endDate;         // 离店日期
    @ExcelProperty(value = {"${bigHead}","房价"}, index = 6)
    private String roomPrice;          // 房价
    @ExcelProperty(value = {"${bigHead}","房晚"}, index = 7)
    private String stayDay;            // 房晚

package com.zbscxy.devOpsManage.util;

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 org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.util.ObjectUtils;
import org.springframework.util.PropertyPlaceholderHelper;

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

/**
 * @ClassName: ExcelTitleHandler
 * @Description:
 * @Author: zmm
 * @Date: 2023/9/25 17:34
 */
public class ExcelTitleHandler implements CellWriteHandler {
    /**
     * 错误信息处理时正则表达式的格式
     */
    private final String EXCEL_ERROR_REG = "^(.*)(\\(错误:)(.*)(\\))$";

    private String bigHead;

    private String dateHead;

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

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


    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
        // 动态设置表头字段
        if (!ObjectUtils.isEmpty(head)) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtils.isNotEmpty(headNameList)) {
                Properties properties = new Properties();
                properties.setProperty("bigHead", bigHead);
                properties.setProperty("dateHead", dateHead);
                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<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    }
}

工具类写法 

/**
     * 导出 （无需设置表头、通过注解在导出class上：@ExcelProperty即可）
     * @param response
     * @param excelName excel名称
     * @param sheetName sheet名称
     * @param clazz     导出标题类(必须添加导出注解)
     * @param data      导出数据
     * @param titleName  表头
     * @throws Exception
     */    
public static void exportData(HttpServletResponse response, String excelName, String sheetName, Class clazz, List data, String titleName) throws Exception {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        excelName = URLEncoder.encode(excelName, "UTF-8").replaceAll("\\+", "%20");
        String filename = new String(excelName.getBytes("UTF-8"), "ISO-8859-1");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + ExcelTypeEnum.XLSX.getValue());
        response.setHeader("filename", filename + ExcelTypeEnum.XLSX.getValue());

        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头部标题居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        String dateHead = ""; // 第二行表头  留着作为案例
        EasyExcel.write(response.getOutputStream(), clazz)
                //插入数据
                .sheet(sheetName)
                .registerWriteHandler(new ExcelTitleHandler(titleName, dateHead))
                .registerWriteHandler(horizontalCellStyleStrategy)
                .doWrite(data);
    }

方法调用 

 public void exportRoomStatisticsList(Long classId, HttpServletResponse response) {
        try {
            //获取报表数据
            BasicAndStatisticsClassStayVo basicAndStatisticsClassStayVo = getRoomStatisticsListData(classId);
            //将详情数据 添加到 tbClassStaySettlementImportVos中
            List<TbClassStaySettlementImportVo> tbClassStaySettlementImportVos = getClassStayStuDetails(basicAndStatisticsClassStayVo);
            //添加空数据,占两行 与统计数据隔离
            addNullData(tbClassStaySettlementImportVos);
            //添加统计数据表头
            addStatisticsTitleData(tbClassStaySettlementImportVos);
            //添加统计数据
            addStatisticsData(basicAndStatisticsClassStayVo, tbClassStaySettlementImportVos);
            String excelName = "培训班学员每日住宿房型数量报表-" + System.currentTimeMillis();
            String sheetName = "培训班学员每日住宿房型数量报表";
            String className = basicAndStatisticsClassStayVo.getClassStayStuDetails().get(0).getClassName();
            EasyExcelUtils.exportData(response, excelName, sheetName, TbClassStaySettlementImportVo.class, tbClassStaySettlementImportVos,className);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }