easyexcel 导出excel 首行高度，行样式，颜色，合并单元格，例子样式

一个周多的工作终于实现了，具体需求看上一篇，

需求

实现

我用easyexcel实现了过程，终于，疯狂冲突，人工智能就跟个傻子一样，说的就是你，而且整天就是冲突，疯狂冲突，重写接口之后一旦冲突就出现了导出空白文件，只能一个一个的找冲突的位置，贼难，还好找到了大佬的文章，https://blog.csdn.net/m0_47786753/article/details/138268275?spm=1001.2014.3001.5501
疯狂推荐

@Operation(summary = "人员维护模版下载", description = "人员维护模版下载")
    @PostMapping("persondowntemplate1")
    public void personDownTemplate1(HttpServletResponse response) throws Exception{

        List<MarkEmployeeInfoExcelCopy> list = new ArrayList<>();
        List<MarkEmployeeInfoDetailCopy> list1 = new ArrayList<>();

//        list.add(dataRow);
        com.alibaba.excel.ExcelWriter excelWriter = null;
        String note = "1.前四行数据，系统不读取，不需要删除\n" +
                "2.约束区县，请输入约束的部门或者区县或者地市\n" +
                "3.约束岗位族的值列表，请参考第二个Sheet2，多个岗位族，请使用英文逗号隔开\n" +
                "4.日期格式：yyyy-mm-dd\n" +
                "5.下拉数据的值，不允许输入下拉值以外的数据";
        String dateHead = "人员契约化管理导入";
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=人员维护导入模板.xlsx" );
            int rowindex = 0;
            short height = 2000;

            try {
                // 这里 指定文件
                excelWriter = EasyExcel.write(response.getOutputStream()).build();

                WriteSheet writeSheet1 = EasyExcel.writerSheet(0, "人员契约化管理导入").head(MarkEmployeeInfoExcelCopy.class)
                        .registerWriteHandler(new ExcelTitleHandler(note, dateHead))
                        .registerWriteHandler(new HeadStyleWriteHandler())
                        .registerWriteHandler(new CustomRowHeightStyleStrategy(rowindex, height))
                        .registerWriteHandler(new CustomSheetWriteHandler())
                        .build();
                MarkEmployeeInfoExcelCopy dataRow = new MarkEmployeeInfoExcelCopy();
                dataRow.setOrgName("请参考hr标准组织名称");
                dataRow.setStaffNo("0000001");
                dataRow.setJoinEnterpriseWay("社会招聘");
                dataRow.setIfDistrict("是");
                dataRow.setConstraintDistrictName("泰安市分公司管理层");
                dataRow.setConstraintDistrictStartDate("2024-04-11");
                dataRow.setConstraintDistrictEndDate("2024-06-11");
                dataRow.setIfJobFamily("是");
                dataRow.setConstraintJobFamilyName("基层组织建设与党员教育管理");
                dataRow.setConstraintJobFamilyStartDate("2024-04-11");
                dataRow.setConstraintJobFamilyEndDate("2024-06-11");
                list.add(dataRow);
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                excelWriter.write(list, writeSheet1);

                WriteSheet writeSheet2 = EasyExcel.writerSheet(1, "Sheet2").head(MarkEmployeeInfoDetailCopy.class).build();
                MarkEmployeeInfoDetailCopy markEmployeeInfoDetailCopy = new MarkEmployeeInfoDetailCopy();
                markEmployeeInfoDetailCopy.setJobFamilyName("12312");
                list1.add(markEmployeeInfoDetailCopy);
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                excelWriter.write(list1, writeSheet2);

            } finally {
                // 千万别忘记finish 会帮忙关闭流
                if (excelWriter != null) {
                    excelWriter.finish();
                }
            }

        }catch (Exception e){
            log.error("123123");
        }

    }

@Data
public class MarkEmployeeInfoExcelCopy {

    /**
     * 组织名称
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","组织名称"})
    private String orgName;

    /**
     * 员工编号
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","* 员工编号"})
    private String staffNo;


    /**
     * 加入本企业途径
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","加入本企业途径"})
    private String joinEnterpriseWay;

    /**
     * 是否启用约束区县，0不生效，1生效
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","是否启用约束区县"})
    private String ifDistrict;

    /**
     * 约束区县名称
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束区县"})
    private String constraintDistrictName;

    /**
     * 约束区县开始日期
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束区县开始日期"})
    private String constraintDistrictStartDate;

    /**
     * 约束区县结束日期
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束区县结束日期"})
    private String constraintDistrictEndDate;

    /**
     * 是否启用约束岗位族，0不生效，1生效
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","是否启用约束岗位族"})
    private String ifJobFamily;

    /**
     * 约束岗位族名称
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束岗位族"})
    private String constraintJobFamilyName;

    /**
     * 约束岗位族开始日期
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束岗位族开始日期"})
    private String constraintJobFamilyStartDate;

    /**
     * 约束岗位族结束日期
     */
    @ExcelProperty(value = {"${bigHead}","${dateHead}","约束岗位族结束日期"})
    private String constraintJobFamilyEndDate;



}

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 (!ObjectUtil.isEmpty(head)) {
            List<String> headNameList = head.getHeadNameList();
            if (CollectionUtil.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));
                }
            }
        }
    }
}

public class HeadStyleWriteHandler extends AbstractCellStyleStrategy {
    @Override
    protected void setHeadCellStyle(CellWriteHandlerContext context) {
        // 获取和创建CellStyle
        WriteCellData<?> cellData = context.getFirstCellData();
        CellStyle originCellStyle = cellData.getOriginCellStyle();
        Cell cell = context.getCell();

        if (Objects.isNull(originCellStyle)) {
            originCellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
        }

        ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap()));

        // 设置背景颜色
        originCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        originCellStyle.setWrapText(true);
        // 重点！！！ 由于在FillStyleCellWriteHandler，会把OriginCellStyle和WriteCellStyle合并，会已WriteCellStyle样式为主，所有必须把WriteCellStyle设置的背景色清空
        // 具体合并规则请看WriteWorkbookHolder.createCellStyle方法
        WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
        writeCellStyle.setFillForegroundColor(null);
        // 重点！！！ 必须设置OriginCellStyle
        cellData.setOriginCellStyle(originCellStyle);

        WriteFont headWriteFont = new WriteFont();
        if (cell.getRowIndex() == 0) {
            headWriteFont.setColor(IndexedColors.RED.getIndex());
        }
        if(0 == context.getRowIndex()){
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        }else {
            writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        }

        cellData.getWriteCellStyle().setWriteFont(headWriteFont);
    }

    @Override
    protected void setContentCellStyle(CellWriteHandlerContext context) {

        if (context.getRelativeRowIndex() == 1){
            System.out.println(123);
        }
        // 获取和创建CellStyle
        WriteCellData<?> cellData = context.getFirstCellData();
        CellStyle originCellStyle = cellData.getOriginCellStyle();
        Cell cell = context.getCell();

        if (Objects.isNull(originCellStyle)) {
            originCellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
        }
        // 设置背景颜色
//        ((XSSFCellStyle) originCellStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(255, 255, 255), new DefaultIndexedColorMap()));

        originCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        originCellStyle.setBorderLeft(BorderStyle.THIN);                    //左边框线
        originCellStyle.setBorderTop(BorderStyle.THIN);                     //顶部框线
        originCellStyle.setBorderRight(BorderStyle.THIN);                   //右边框线
        originCellStyle.setBorderBottom(BorderStyle.THIN);                  //底部框线
        originCellStyle.setWrapText(true);
        // 重点！！！ 由于在FillStyleCellWriteHandler，会把OriginCellStyle和WriteCellStyle合并，会已WriteCellStyle样式为主，所有必须把WriteCellStyle设置的背景色清空
        // 具体合并规则请看WriteWorkbookHolder.createCellStyle方法
        WriteCellStyle writeCellStyle = cellData.getWriteCellStyle();
        writeCellStyle.setFillForegroundColor(null);
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 重点！！！ 必须设置OriginCellStyle
        cellData.setOriginCellStyle(originCellStyle);

        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)12);
        headWriteFont.setFontName("宋体");
        headWriteFont.setBold(false);
        headWriteFont.setColor(IndexedColors.RED.getIndex());
        cellData.getWriteCellStyle().setWriteFont(headWriteFont);
    }

}

public class CustomRowHeightStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private int rowIndex;
    private short height;
 
    public CustomRowHeightStyleStrategy(int rowIndex, short height) {
        this.rowIndex = rowIndex;
        this.height = height;
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        Row row = sheet.getRow(rowIndex);
        row.setHeight(height);
    }


}

@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());

        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(3, 10000, 2, 2);
        DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"校园招聘", "社会招聘","接收复转军人","成建制划转","劳务派遣"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);

        CellRangeAddressList cellRangeAddressList1 = new CellRangeAddressList(3, 10000, 3, 3);
        DataValidationHelper helper1 = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        DataValidationConstraint constraint1 = helper1.createExplicitListConstraint(new String[] {"是", "否"});
        DataValidation dataValidation1 = helper.createValidation(constraint1, cellRangeAddressList1);
        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation1);


        CellRangeAddressList cellRangeAddressList2 = new CellRangeAddressList(3, 10000, 7, 7);
        DataValidationHelper helper2 = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        DataValidationConstraint constraint2 = helper2.createExplicitListConstraint(new String[] {"是", "否"});
        DataValidation dataValidation2 = helper.createValidation(constraint2, cellRangeAddressList2);
        context.getWriteSheetHolder().getSheet().addValidationData(dataValidation2);
    }
}

真的太难了！！！！我一个需求干了一个周！！！！！一个周啊！！！！！疯了！！！！

问题

重写的接口afterSheetCreate，setColumnWidth，setHeadCellStyle，beforeCellCreate
当数据增加了一行例子之后，出现了导出文件是空白的，我查了好久真的好久，直到发现了上面的大哥的文章才意识到问题在接口重写，冲突，然后我又重写了setContentCellStyle接口之后才好用的，这个问题真的好难，真的好难！！！！太难啦