java使用poi导出excel时，createCellStyle过多导致的异常

问题

The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook

问题原因

源代码：

/**
     * Create a cell style in this style table.
     * Note - End users probably want to call {@link XSSFWorkbook#createCellStyle()}
     * rather than working with the styles table directly.
     * @throws IllegalStateException if the maximum number of cell styles has been reached. 
     */
    public XSSFCellStyle createCellStyle() {
        if (getNumCellStyles() > MAXIMUM_STYLE_ID) {
            throw new IllegalStateException("The maximum number of Cell Styles was exceeded. " +
                      "You can define up to " + MAXIMUM_STYLE_ID + " style in a .xlsx Workbook");
        }

        int xfSize = styleXfs.size();
        CTXf xf = CTXf.Factory.newInstance();
        xf.setNumFmtId(0);
        xf.setFontId(0);
        xf.setFillId(0);
        xf.setBorderId(0);
        xf.setXfId(0);
        int indexXf = putCellXf(xf);
        return new XSSFCellStyle(indexXf - 1, xfSize - 1, this, theme);
    }

往里跟可以看到：

EXCEL2007(0x100000, 0x4000, 255, Integer.MAX_VALUE, 64000, 32767);

解决方法

由于我是采用自定义的方式，实现了接口CellWriteHandler，不能直接在for循环外创建对象，所以使用一个map进行存储，每次创建对象前先判断map中是否存在，存在则不再创建。

// 每次调用接口时需要对这两个map进行重新初始化。
private static Map<String,CellStyle> cellStyleMap = Maps.newHashMap();
private static Map<String,Font> fontMap = Maps.newHashMap();

CellStyle cellStyleDeal1 = cellStyleMap.get("cellStyleDeal1");
Font dealFont = fontMap.get("dealFont1");
if(cellStyleDeal1 == null){
    cellStyleDeal1 = workbook.createCellStyle();
    cellStyleMap.put("cellStyleDeal1",cellStyleDeal1);
}
if(dealFont == null){
    dealFont = workbook.createFont();
    fontMap.put("dealFont1",dealFont);
}

完整代码

@Component
public class CustomCellWriteHandler implements CellWriteHandler {

    private static Map<String,CellStyle> cellStyleMap = Maps.newHashMap();
    private static Map<String,Font> fontMap = Maps.newHashMap();

    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Cell cell = context.getCell();
        int rowIndex = cell.getRowIndex();
        String sheetName = cell.getSheet().getSheetName();
        Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
        // 设置背景色，内容不同行不同背景色
        byte[] rgb1 = new byte[]{(byte)235, (byte)242, (byte)254};
        byte[] rgb2 = new byte[]{(byte)227, (byte)236, (byte)251};
        byte[] rgbHead = new byte[]{(byte)203, (byte)226, (byte)248};



        // 4. 自定义样式处理，当前事件会在 数据设置到poi的cell里面才会回调，判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
        if (BooleanUtils.isNotTrue(context.getHead())) {
            
           CellStyle cellStyleDeal1 = cellStyleMap.get("cellStyleDeal1");
            Font dealFont = fontMap.get("dealFont1");
            if(cellStyleDeal1 == null){
                cellStyleDeal1 = workbook.createCellStyle();
                cellStyleMap.put("cellStyleDeal1",cellStyleDeal1);
            }
            if(dealFont == null){
                dealFont = workbook.createFont();
                fontMap.put("dealFont1",dealFont);
            }
            // 字体
            dealFont.setFontHeightInPoints((short)12);
            dealFont.setFontName("宋体");
            byte[] rgbFontDeal = new byte[]{(byte) 138, (byte) 138, (byte) 138};
            XSSFFont xssfDealFont = (XSSFFont)dealFont;
            xssfDealFont.setColor(new XSSFColor(rgbFontDeal,null));
            // 样式
            cellStyleDeal1.setFont(dealFont);
            cellStyleDeal1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cellStyleDeal1.setAlignment(HorizontalAlignment.CENTER);
            cellStyleDeal1.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyleDeal1.setBorderLeft(BorderStyle.THIN);
            cellStyleDeal1.setBorderBottom(BorderStyle.THIN);
            cellStyleDeal1.setBorderRight(BorderStyle.THIN);
            cellStyleDeal1.setBorderTop(BorderStyle.THIN);
            XSSFCellStyle xssfCellColorStyleDeal1 = (XSSFCellStyle) cellStyleDeal1;
            xssfCellColorStyleDeal1.setFillForegroundColor(new XSSFColor(rgb1, null));
            cell.setCellStyle(cellStyleDeal1);

        }
    }

// 2.0 重置handler的属性map
 customCellWriteHandler.cellStyleMap = Maps.newHashMap();
 customCellWriteHandler.fontMap = Maps.newHashMap();