【Java EasyExcel】自定义处理器

工具类

public class ExcelUtils {

    public static void setValidation(Sheet sheet, DataValidationHelper helper,
                                     DataValidationConstraint constraint,
                                     CellRangeAddressList addressList,
                                     String msgHead, String msgContext) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox(msgHead, msgContext);
        sheet.addValidationData(dataValidation);
    }

    public static String getRange(Integer offset, Integer rowId, Integer colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        }
        char endPrefix = 'A';
        char endSuffix = 'A';
        // 26-51之间，包括边界（仅两次字母表计算）或者是51以上
        if ((colCount - 25) / 26 == 0 || colCount == 51) {
            // 边界值
            if ((colCount - 25) % 26 == 0) {
                endSuffix = (char) ('A' + 25);
            } else {
                endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
            }
        } else {
            if ((colCount - 25) % 26 == 0) {
                endSuffix = (char) ('A' + 25);
                endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
            } else {
                endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                endPrefix = (char) (endPrefix + (colCount - 25) / 26);
            }
        }
        return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
    }
}

单列选择

public class RoleHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 500, 8, 8);
        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[]{"a", "b"});
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        writeSheetHolder.getSheet().addValidationData(dataValidation);
    }
}

多列级联

public class DeptHandler implements SheetWriteHandler {

    private static final String HIDE_SHEET_NAME = "hide_sheet";

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        final Sheet hideSheet = workbook.createSheet(HIDE_SHEET_NAME);
        workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), false);
        List<Dept> firstDeptList = setNameManage(hideSheet, workbook);
        setDataAndRule(writeSheetHolder, firstDeptList);
    }

    private List<Dept> setNameManage(Sheet hideSheet, Workbook workbook) {
        Integer rowIndex = NumberUtils.INTEGER_ZERO;
        Row firstRow = setRowCell(hideSheet.createRow(rowIndex++), rowIndex, "首行首列",
                null, null, null, false);
        List<Dept> deptList = SpringUtils.getBean(DeptService.class).list();
        List<Dept> firstDeptList = deptList.stream()
                .filter(dept -> !Optional.ofNullable(dept.getParentId()).isPresent())
                .collect(Collectors.toList());
        for (int i = 0; i < firstDeptList.size(); ++i) {
            Dept firstDept = firstDeptList.get(i);
            Cell firstCell = firstRow.createCell(i + 1);
            firstCell.setCellValue(firstDept.getName());
            List<Dept> secondDeptList = deptList.stream()
                    .filter(d -> firstDept.getId().equals(d.getParentId()))
                    .collect(Collectors.toList());
            if (ObjectUtils.isNotEmpty(secondDeptList)) {
                Row secondRow = setRowCell(hideSheet.createRow(rowIndex++), rowIndex, firstDept.getName(),
                        secondDeptList.size(), workbook, firstDept.getName(), true);
                for (int j = 0; j < secondDeptList.size(); ++j) {
                    Dept secondDept = secondDeptList.get(j);
                    Cell secondCell = secondRow.createCell(j + 1);
                    secondCell.setCellValue(secondDept.getName());
                    List<Dept> thirdDeptList = deptList.stream()
                            .filter(d -> secondDept.getId().equals(d.getParentId()))
                            .collect(Collectors.toList());
                    if (ObjectUtils.isNotEmpty(thirdDeptList)) {
                        Row thirdRow = setRowCell(hideSheet.createRow(rowIndex++), rowIndex, secondDept.getName(),
                                thirdDeptList.size(), workbook, secondDept.getName(), true);
                        for (int k = 0; k < thirdDeptList.size(); ++k) {
                            Dept thirdDept = thirdDeptList.get(k);
                            Cell thirdCell = thirdRow.createCell(k + 1);
                            thirdCell.setCellValue(thirdDept.getName());
                        }
                    }
                }
            }
        }
        return firstDeptList;
    }

    private Row setRowCell(Row row, Integer rowIndex,
                           String cellValue, Integer size,
                           Workbook workbook, String nameName,
                           boolean ifNeed) {
        row.createCell(NumberUtils.INTEGER_ZERO).setCellValue(cellValue);
        if (ifNeed) {
            final String range = ExcelUtils.getRange(NumberUtils.INTEGER_ONE, rowIndex, size);
            Name name = workbook.createName();
            name.setNameName(nameName);
            name.setRefersToFormula(HIDE_SHEET_NAME + "!" + range);
        }
        return row;
    }

    private void setDataAndRule(WriteSheetHolder writeSheetHolder, List<Dept> firstDeptList) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        final String messageHead = "提示";
        final String messageContext = "你输入的值未在备选列表中，请下拉选择合适的值";
        ExcelUtils.setValidation(sheet, helper, helper.createExplicitListConstraint(firstDeptList.stream().map(Dept::getName).toArray(String[]::new)),
                new CellRangeAddressList(1, 500, 5, 5), messageHead, messageContext);
        for (int i = 2; i < 501; ++i) {
            ExcelUtils.setValidation(sheet, helper,
                    helper.createFormulaListConstraint("INDIRECT($F$" + i + ")"),
                    new CellRangeAddressList(i - 1, i - 1, 6, 6), messageHead, messageContext);
            ExcelUtils.setValidation(sheet, helper,
                    helper.createFormulaListConstraint("INDIRECT($G$" + i + ")"),
                    new CellRangeAddressList(i - 1, i - 1, 7, 7), messageHead, messageContext);
        }
    }
}