基于EasyExcel实现动态设置单元格样式

背景

教务人员在设班排课系统中经常使用Excel进行批量导入新建/修改等操作，并且还要批量导出做一些自定义分析。之前系统采用的是常见的POI 用户模式处理Excel导入导出，但是数据量一大，就容易出现内存溢出，不得不在系统层面做20000条限制，虽然POI3.8+推出事件模式，但是操作繁琐。经过多方对比，并实际验证，最终决定采用阿里出品的EasyExcel：https://github.com/alibaba/easyexcel

EasyExcel本地性能测试（设置JVM最大内存750MB）

• 导入 Excel （5W行6列，FileSize：1.1M），内存使用138MB，耗时1.5s

• 一次性导入 Excel （59W行6列，FileSize：16.6M），内存使用635MB，耗时15s

• 分批次导入 Excel （59W行6列，FileSize：16.6M，批次：3000条），内存使用300MB，耗时23s

• 导出 Excel （10W行14列，FileSize：25.2.M），内存使用200MB，耗时7.5s

经过验证EasyExcel是可以满足非功能性需求的。对于功能性需求，EasyExcel的注解功能可以实现表头的单元格样式设置，或者整个sheet页的单元格样式设置，指定行高@HeadRowHeight(35)列宽@ColumnWidth(value = 16) v2.2.0-beta1及以上，可以设置背景、字体、内容样式等，但如何实现动态单元格的样式设置，注解暂时实现不了。

本文介绍通过实现easyExcel的SheetWriteHandler类、CellWriteHandler类等，单独设置指定坐标单元格样式

代码准备

Excel实体类：DynamicDemoDto

package com.op.sannji.dto;
 
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
 
@HeadRowHeight(35)
@ContentRowHeight(25)
public class DynamicDemoDto {
    @ExcelProperty(value = {"字段1"}, index = 0)
    @ColumnWidth(value = 24)
    private String name1;
 
    @ExcelProperty(value = {"字段2"}, index = 1)
    @ColumnWidth(value = 16)
    private String name2;
 
    @ExcelProperty(value = {"字段3"}, index = 2)
    @ColumnWidth(value = 16)
    private String name3;
 
    @ExcelProperty(value = {"字段4"}, index = 3)
    @ColumnWidth(value = 16)
    private String name4;
 
    @ExcelProperty(value = {"字段5"}, index = 4)
    @ColumnWidth(value = 16)
    private String name5;
 
    @ExcelProperty(value = {"字段6"}, index = 5)
    @ColumnWidth(value = 16)
    private String name6;
 
....getter setter
}

导出Excel工具类的方法

/**
 * 导出有数据且有格式的Excel
 * @param fileName - 文件名
 * @param sheetName - Excel内的sheet页名称
 * @param response
 * @param tempClass - 模板对应的实体类
 * @param data - Excel数据
 * @param cellOpts - 单元格下拉框
 * @param redCells - 需要标红的单元格
 * @param <T>
 */
public static <T> void exportWithStyle(String fileName, String sheetName, HttpServletResponse response,
                                       Class tempClass, List<T> data, Map<Integer, String[]> cellOpts,
                                       Map<Integer, List<Integer>> redCells) {
    preHandle(fileName, response);
 
    try (OutputStream outputStream = response.getOutputStream()) {
        data = CollectionUtils.isEmpty(data) ? Lists.newArrayList() : data;
        EasyExcel.write(outputStream, tempClass)
                .sheet(sheetName)
                .registerWriteHandler(new ExcelUtil.CustomSheetWriteHandler(cellOpts))
                .registerWriteHandler(new ExcelUtil.CustomCellWriteHandler(redCells))
                .doWrite(data);
    } catch (IOException e) {
 
    }
}

样式一：动态下拉框

固定下拉框内容：通过注解@ExplicitConstraint(source = {"是", "否"}) 实现

动态查询数据库下拉框内容：比如，在Excel中的第2列加载下拉框

代码：

设置动态下拉框值：

@GetMapping("/excel/export")
public void export(HttpServletResponse response) {
    // 查询sheet内下拉选项
    Map<Integer, String[]> cellOpts = new HashMap<>();
    String[] name1 = {"0","1","2","3","4","5","6","7","8","9","10","11","12"};
    String[] name2 = {"幼儿","一年级","二年级","三年级","四年级","五年级","六年级","七年级","八年级","九年级","十年级","十一年级","十二年级"};
    cellOpts.put(0, name1);
    cellOpts.put(1, name2);
 
    // 下载模板
    ExcelUpgradeUtil.exportWithStyle("demo", "demo", response, DynamicDemoDto.class, null, cellOpts, null);
}

动态下拉框实现代码，实现SheetWriteHandler接口类，重写afterSheetCreate方法：

/**
 * 个性化sheet页，主要是下拉框
 */
public static class CustomSheetWriteHandler implements SheetWriteHandler {
 
    private Map<Integer, String[]> specialSheet;
 
    public CustomSheetWriteHandler(Map<Integer, String[]> specialSheet) {
        this.specialSheet = specialSheet;
    }
 
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder var1, WriteSheetHolder var2) {}
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (MapUtils.isEmpty(this.specialSheet)) {
            return;
        }
        // sheet创建成功之后，修改sheet，添加下拉选项
        handleSheet(writeSheetHolder, this.specialSheet);
    }
}
 
public static void handleSheet(WriteSheetHolder writeSheetHolder, Map<Integer,String []> mapDropDown) {
    Sheet sheet = writeSheetHolder.getSheet();
    ///开始设置下拉框
    DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
    for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
        /***起始行、终止行、起始列、终止列**/
        CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, entry.getKey(), entry.getKey());
        /***设置下拉框数据**/
        DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        /***处理Excel兼容性问题**/
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }
}

样式二：单元格背景颜色

表头背景色设置成红色： @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)

动态设置背景色：比如，在 第3行第2列、第5行第5，6列 背景色设置为红色

代码：

设置背景色是在单元格创建之后修改单元格样式，Excel内容不存在的话，样式设置不成功

@GetMapping("/excel/export")
public void export(HttpServletResponse response) {
    // 查询sheet内下拉选项
    Map<Integer, String[]> cellOpts = new HashMap<>();
 
    // 导出的具体数据
    List<DynamicDemoDto> dtos = Lists.newArrayList();
    DynamicDemoDto dto = null;
    for (int i = 1; i <= 6; i++) {
        dto = new DynamicDemoDto();
        dto.setName1(i + "");
        dto.setName2("男");
        dto.setName3("张三");
        dto.setName4("阿里");
        dto.setName5("腾讯");
        dto.setName6("头条");
        dtos.add(dto);
    }
 
    // 背景标红坐标，在 第3行第2列、第5行第5第6列 背景色设置为红色
    Map<Integer, List<Integer>> redCells = new HashMap<>();
    redCells.put(2, Lists.newArrayList(1)); // 第3行 第2列
    redCells.put(4, Lists.newArrayList(4,5)); // 第5行 第5第6列
 
    // 下载模板
    ExcelUpgradeUtil.exportWithStyle("demo", "demo", response, DynamicDemoDto.class,
            dtos, cellOpts, redCells);
}

实现CellWriteHandler类：

/**
 * 个性化单元格格式，只要是单元格颜色和批注
 */
public static class CustomCellWriteHandler implements CellWriteHandler {
 
    private Map<Integer, List<Integer>> specialCells;
 
    public CustomCellWriteHandler(Map<Integer, List<Integer>> specialCells) {
        this.specialCells = specialCells;
    }
    @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 afterCellDataConverted(WriteSheetHolder var1, WriteTableHolder var2, CellData var3, Cell var4,
                                       Head var5, Integer var6, Boolean var7) {}
 
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellData, Cell cell, Head head, Integer relativeRowIndex,
                                 Boolean isHead) {
        // 设置单元格颜色、批注
        handleCell(writeSheetHolder, cell, isHead, this.specialCells);
    }
}
 
 
/**
 * 处理单元格
 * @param writeSheetHolder
 * @param cell
 * @param isHead
 * @param specialCells
 */
public static void handleCell(WriteSheetHolder writeSheetHolder, Cell cell, Boolean isHead,
                              Map<Integer, List<Integer>> specialCells) {
    // 处理表头
    if (isHead) {
        // 表头单元格背景色设置
        // handleCellColor(writeSheetHolder, cell, IndexedColors.LIGHT_GREEN.index);
        return;
    }
    // 对specialCells中存在的需要标红的单元格进行标红
    if (MapUtils.isNotEmpty(specialCells) && specialCells.containsKey(cell.getRowIndex())
            && specialCells.get(cell.getRowIndex()).contains(cell.getColumnIndex())) {
        handleCellColor(writeSheetHolder, cell, IndexedColors.RED.index);
    }
}
 
private static void handleCellColor(WriteSheetHolder writeSheetHolder, Cell cell, short colorIndex) {
    Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
    CellStyle cellStyle = workbook.createCellStyle();
    // 单元格背景设置为红色
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
    cellStyle.setFillForegroundColor(colorIndex);//前景填充色
    cell.setCellStyle(cellStyle);
}

样式三：批注

代码：

动态批注Map

@GetMapping("/excel/export")
public void export(HttpServletResponse response) {
    // 下拉框
    Map<Integer, String[]> cellOpts = new HashMap<>();
    // 导出的具体数据
    List<DynamicDemoDto> dtos = Lists.newArrayList();
    // 背景标红坐标，在 第3行第2列、第5行第5第6列 背景色设置为红色
    Map<Integer, List<Integer>> redCells = new HashMap<>();
 
    // 批注
    Map<Integer, String> commMap = new HashMap<>();
    commMap.put(0, "给表头的第一列添加批注");
    commMap.put(3, "给表头的第四列添加批注");
 
    // 下载模板
    ExcelUpgradeUtil.exportWithStyle("demo", "demo", response, DynamicDemoDto.class,
            dtos, cellOpts, redCells, commMap);
}
 
/**
 * 导出有数据且有格式的Excel
 * @param fileName - 文件名
 * @param sheetName - Excel内的sheet页名称
 * @param response
 * @param tempClass - 模板对应的实体类
 * @param data - Excel数据
 * @param cellOpts - 单元格下拉框
 * @param redCells - 需要标红的单元格
 * @param <T>
 */
public static <T> void exportWithStyle(String fileName, String sheetName, HttpServletResponse response,
                                       Class tempClass, List<T> data, Map<Integer, String[]> cellOpts,
                                       Map<Integer, List<Integer>> redCells, Map<Integer, String> commMap) {
    preHandle(fileName, response);
 
    try (OutputStream outputStream = response.getOutputStream()) {
        data = CollectionUtils.isEmpty(data) ? Lists.newArrayList() : data;
        EasyExcel.write(outputStream, tempClass)
                .sheet(sheetName)
                .registerWriteHandler(new ExcelUtil.CustomSheetWriteHandler(cellOpts))
                .registerWriteHandler(new ExcelUtil.CustomCellWriteHandler(redCells, commMap))
                .doWrite(data);
    } catch (IOException e) {
 
    }
}
 
/**
 * 个性化单元格格式，只要是单元格颜色和批注
 */
public static class CustomCellWriteHandler implements CellWriteHandler {
 
    private Map<Integer, List<Integer>> specialCells;
    private Map<Integer, String> commMap;
 
    public CustomCellWriteHandler(Map<Integer, List<Integer>> specialCells, Map<Integer, String> commMap) {
        this.specialCells = specialCells;
        this.commMap = commMap;
    }
    @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 afterCellDataConverted(WriteSheetHolder var1, WriteTableHolder var2, CellData var3, Cell var4,
                                       Head var5, Integer var6, Boolean var7) {}
 
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> cellData, Cell cell, Head head, Integer relativeRowIndex,
                                 Boolean isHead) {
        // 设置单元格颜色、批注
        handleCell(writeSheetHolder, cell, isHead, this.specialCells, this.commMap);
    }
}
 
/**
 * 处理单元格
 * @param writeSheetHolder
 * @param cell
 * @param isHead
 * @param specialCells
 */
public static void handleCell(WriteSheetHolder writeSheetHolder, Cell cell, Boolean isHead,
                              Map<Integer, List<Integer>> specialCells, Map<Integer, String> commMap) {
    // 处理表头
    if (isHead) {
        // 添加批注
        Drawing<?> drawing=writeSheetHolder.getSheet().createDrawingPatriarch();
        String desc = commMap.get(cell.getColumnIndex());
        if (!StringUtils.isEmpty(desc)) {
            addComment(drawing, cell, desc);
        }
        return;
    }
    // 对specialCells中存在的需要标红的单元格进行标红
    if (MapUtils.isNotEmpty(specialCells) && specialCells.containsKey(cell.getRowIndex())
            && specialCells.get(cell.getRowIndex()).contains(cell.getColumnIndex())) {
        handleCellColor(writeSheetHolder, cell, IndexedColors.RED.index);
    }
}
 
/**
 * 单元格添加批注
 * @param drawing
 * @param cell
 */
public static void addComment(Drawing<?> drawing, Cell cell, String desc) {
    // 参数：dx1：起始单元格的x偏移量
    //dy1：起始单元格的y偏移量
    //dx2：终止单元格的x偏移量
    //dy2：终止单元格的y偏移量
    //col1：起始单元格列序号，从0开始计算；
    //row1：起始单元格行序号，从0开始计算，如：col1=0,row1=0就表示起始单元格为A1；
    //col2：终止单元格列序号，从0开始计算；
    //row2：终止单元格行序号，从0开始计算，如：col2=2,row2=2就表示终止单元格为C3；
    Comment comment=drawing.createCellComment(new XSSFClientAnchor(0,0,0,0,cell.getColumnIndex(),0,cell.getColumnIndex() + 1,3));
    comment.setString(new XSSFRichTextString(desc));
    cell.setCellComment(comment);
}

通过以上的代码封装，EasyExcel不仅满足了性能需求，也满足了基本的功能需要。