[alibaba/easyexcel]设置单元格格式无效

异常代码

// 缓存cellStyle
   public class SalaryExportCellWriteHandler implements CellWriteHandler {

    /**
     * 导出需要设置单元格为百分数的字段
     */
    private static List<String> percentCellTypeList = Lists.newArrayList("lastYearAprilSalaryRatio", "lastYearSpecialSalaryRatio", "thisYearQ1SalaryRatio", "basicAdjustRatio", "flexibleAdjustRatio", "recommendedAnnualSalaryIncreasedRatio");

    private CellStyle cellStyle;

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

    @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) {
        if (!isHead) {
            if (Objects.isNull(cellStyle)) {
                cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
            }
            // 设置百分比单元格格式
            if (percentCellTypeList.contains(head.getFieldName())) {
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            } else {
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
            }
            cell.setCellStyle(cellStyle);
        }
    }
}

// 不缓存cellStyle 每次都创建
public class SalaryExportCellWriteHandler implements CellWriteHandler {

    /**
     * 导出需要设置单元格为百分数的字段
     */
    private static List<String> percentCellTypeList = Lists.newArrayList("lastYearAprilSalaryRatio", "lastYearSpecialSalaryRatio", "thisYearQ1SalaryRatio", "basicAdjustRatio", "flexibleAdjustRatio", "recommendedAnnualSalaryIncreasedRatio");

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
    }

    @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) {
        if (!isHead) {
            CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
            // 设置百分比单元格格式
            if (percentCellTypeList.contains(head.getFieldName())) {
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            } else {
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
            }
            cell.setCellStyle(cellStyle);
        }
    }
}

异常提示 请提供完整的异常提示,记住是全部异常！ 建议描述 如果是按照上面的方式把cellStyle缓存起来的话设置的单元格格式无效，但是像下面这样每次都创建一个cellStyle是有效的，麻烦请教下是什么原因？ ps: 上面那种debug的时候是进去的，但是就是无效

msl1024bit

您的SalaryExportCellWriteHandler 是否每次都new ?必须每次都new 。

zhuangjiaju

您的SalaryExportCellWriteHandler 是否每次都new ?必须每次都new 。

是每次都new的

msl1024bit

不可能。你debug确认下，是否每个cell都进来设置了

zhuangjiaju

EasyExcel.write(out, clazz).sheet().registerWriteHandler(new SalaryExportCellWriteHandler()).doWrite(data); 这样是代表每次都new吧？确实每个cell都进来设置了。

另外：我发现如果把两个格式分成两个独立的cellStyle变量是ok的

`private CellStyle percentCellStyle; private CellStyle generalCellStyle;

@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}

@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) {
    if (!isHead) {
        // 设置百分比单元格格式
        if (percentCellTypeList.contains(head.getFieldName())) {
            if (Objects.isNull(percentCellStyle)) {
                percentCellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
            }
            percentCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
            cell.setCellStyle(percentCellStyle);
        } else {
            if (Objects.isNull(generalCellStyle)) {
                generalCellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
            }
            generalCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("General"));
            cell.setCellStyle(generalCellStyle);
        }
    }
}`

msl1024bit

我想设置部分表头的背景样式，但是不起作用，使用的仍然是默认的样式。请问可能是什么问题？

@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                             List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
    if (BooleanUtils.isTrue(isHead)) {
        CellStyle cellStyle = writeSheetHolder.getSheet().getWorkbook().createCellStyle();
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        Font font = writeSheetHolder.getSheet().getWorkbook().createFont();
        font.setFontName("微软雅黑");
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    // 头的策略
    } else {
        int a = 1;
    }
}

发现问题了：

for (WriteHandler writeHandler : handlerList) {
        if (writeHandler instanceof CellWriteHandler) {
            ((CellWriteHandler)writeHandler).afterCellDispose(writeContext.writeSheetHolder(),
                writeContext.writeTableHolder(), cellDataList, cell, head, relativeRowIndex, isHead);
            }
}

handlerList 里有两个handler。 HorizontalCellStyleStrategy，一个我自己定义的handler。然后默认的handler覆盖掉了我自己定义的样式

debug出来了。在注册handler前设置userDefault = false 就行。

    EasyExcel
            .write(fileName, ComplexHeadData.class)
            .useDefaultStyle(false)
            .registerWriteHandler(customerCommonStrategy)
            .sheet("模板")
            .doWrite(data());

HeroHong

使用最新代码，可以直接在clazz的字段上加注解 @ContentStyle（dataFormat = 0xa） // 设置百分比单元格格式

dota17

使用最新代码，可以直接在clazz的字段上加注解 @ContentStyle（dataFormat = 0xa） // 设置百分比单元格格式

你好，如果我是不生成对象的导出（头和内容均使用List<List>），应该如何设置contentStyle呢？

Umenezumi

只单独设置cellstyle不行,debug发现会被FillStyleCellWriteHandler覆盖样式,需要在context中设置关闭

@Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        context.setIgnoreFillStyle(true);
}

MnsYF

[alibaba/easyexcel]设置单元格格式无效

回答