小桀
EasyExcel工具简单整理

EasyExcel工具简单整理

项目中原有的封装过于麻烦，学习成本太高了，出现问题定位成本太高，所以我对EasyExcel简单的封装了下：

EasyExcelUtilsWithNewScheme.java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74


package com.sdstc.anta.material.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class EasyExcelUtilsWithNewScheme {

    public static void writeExcelWithModel(OutputStream outputStream,
                                           List<? extends Object> dataList,
                                           Class<? extends Object> classT,
                                           String sheetName,
                                           WriteHandler... writeHandlers) {

        ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName);

        for (WriteHandler writeHandler : getDefaultWriteHandlerList()) {
            excelWriterSheetBuilder.registerWriteHandler(writeHandler);
        }

        if (null != writeHandlers && writeHandlers.length > 0) {
            for (WriteHandler writeHandler : writeHandlers) {
                excelWriterSheetBuilder.registerWriteHandler(writeHandler);
            }
        }

        // 开始导出
        excelWriterSheetBuilder.doWrite(dataList);
    }

    private static List<WriteHandler> getDefaultWriteHandlerList() {
        List<WriteHandler> writeHandlerList = new ArrayList<>();
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setWrapped(false);
        setBorderStyle(headWriteCellStyle);
        List<WriteCellStyle> contentWriteCellStyleList = new ArrayList<>();
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        setBorderStyle(writeCellStyle);
        contentWriteCellStyleList.add(writeCellStyle);
        writeHandlerList.add(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyleList));
        writeHandlerList.add(new LongestMatchColumnWidthStyleStrategy());
        return writeHandlerList;
    }

    private static void setBorderStyle(WriteCellStyle writeCellStyle) {
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        writeCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    }

}

目前开发的Handlers：

DropDownSheetHandler.java

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112


package com.sdstc.anta.material.utils.excel.handlers_with_new_scheme;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class DropDownSheetHandler implements SheetWriteHandler {

    private static final String DROP_BOX_HIDDEN_SHEET = "DROP_BOX_HIDDEN_SHEET";

    // 下拉框值
    private final HashMap<Integer, String[]> dropDownMap;

    public DropDownSheetHandler(HashMap<Integer, String[]> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        Workbook workbook = writeWorkbookHolder.getWorkbook();

        // region 在另一张Sheet中准备下拉数据

        // 因为行只能创建一次，所以需要计算出最大行
        int maxRows = Integer.MIN_VALUE;
        for (String[] arr : dropDownMap.values()) {
            if (arr.length > maxRows) {
                maxRows = arr.length;
            }
        }

        Sheet dropBoxHiddenSheet = workbook.createSheet(DROP_BOX_HIDDEN_SHEET);

        for (int i = 0; i < maxRows; i++) {
            Row row = dropBoxHiddenSheet.createRow(i);
            for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {
                if (i < entry.getValue().length) {
                    row.createCell(entry.getKey()).setCellValue(entry.getValue()[i]);
                }
            }
        }

        // 隐藏Sheet
        if (!workbook.isSheetHidden(workbook.getSheetIndex(DROP_BOX_HIDDEN_SHEET))) {
            workbook.setSheetHidden(workbook.getSheetIndex(DROP_BOX_HIDDEN_SHEET), true);
        }

        // endregion

        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

        for (Map.Entry<Integer, String[]> entry : dropDownMap.entrySet()) {

            String excelTag = getExcelTag(entry.getKey(), entry.getValue().length);

            DataValidationConstraint dataValidationConstraint = helper.createFormulaListConstraint(
                    String.format("%s!%s", DROP_BOX_HIDDEN_SHEET, excelTag));

            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65536, entry.getKey(), entry.getKey());

            DataValidation dataValidation = helper.createValidation(dataValidationConstraint, cellRangeAddressList);

            // 设置错误提示
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.setShowErrorBox(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox("错误提示", "请选择下拉框内选项!");

            writeSheetHolder.getSheet().addValidationData(dataValidation);
        }
    }

    private String getExcelTag(int row, int length) {
        if (row < 0) {
            throw new RuntimeException("Wrong RowIndex Show Bigger Then 0");
        }

        int leftRow = row / 26;
        int rightRow = row % 26;

        String leftRowChar = leftRow == 0 ? "" : String.valueOf((char) (65 + leftRow - 1));
        String rightRowChar = String.valueOf((char) (65 + rightRow));

        return String.format("$%s%s$%d:$%s%s$%d", leftRowChar, rightRowChar, 1, leftRowChar, rightRowChar, length);
    }
}

ColorWithHeadCellHandler.java

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68


package com.sdstc.anta.material.utils.excel.handlers_with_new_scheme;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;

import java.util.List;

public class ColorWithHeadCellHandler implements CellWriteHandler {

    //操作列
    private final List<Integer> colorList;
    //颜色
    private final Short color;

    public ColorWithHeadCellHandler(List<Integer> colorList, Short color) {
        this.colorList = colorList;
        this.color = color;
    }

    @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 writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, 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) {

            // 设置标题字体样式
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            WriteFont headWriteFont = new WriteFont();

            if (CollectionUtils.isNotEmpty(colorList)
                    && color != null
                    && colorList.contains(cell.getColumnIndex())) {
                // 设置字体颜色
                headWriteFont.setColor(color);
            }

            headWriteCellStyle.setWriteFont(headWriteFont);
            headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());

            CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
            cell.setCellStyle(cellStyle);
        }
    }
}

使用时的案例代码：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34


@Override
public void exportTemplate(OutputStream outputStream) {

    // 增补系列
    String[] supplementSeriesArr = new String[]{"篮球", "跑鞋", "综训", "生活", "儿童"};
    // 申请季节
    String[] applySeasonsArr = new String[YEARS * 4];

    int start = LocalDateTime.now().getYear() % 2000;
    for (int i = 0; i < YEARS; i++) {
        for (int j = 0; j < 4; j++) {
            applySeasonsArr[4 * i + j] = String.format("%sQ%s", start + i, j + 1);
        }
    }

    HashMap<Integer, String[]> dropDown = new HashMap<>();
    dropDown.put(1, applySeasonsArr);
    dropDown.put(5, supplementSeriesArr);

    // 必填字段
    List<Integer> columns = Arrays.asList(0, 1, 4, 5);

    DropDownSheetHandler dropDownSheetHandler = new DropDownSheetHandler(dropDown);
    ColorWithHeadCellHandler colorWithHeadCellHandler = new ColorWithHeadCellHandler(columns, IndexedColors.RED.index);

    EasyExcelUtilsWithNewScheme.writeExcelWithModel(
            outputStream,
            new ArrayList<>(),
            MaterialSupplementDto.class,
            "面料增补",
            dropDownSheetHandler, colorWithHeadCellHandler);
}


1
2
3

参考资料

  1. EasyExcel增加下拉选择框
  2. 2.1.3版本 写Excel 列宽自适应策略的问题