jnpf-common-v6x/jnpf-boot-common/jnpf-common-office/target/classes/jnpf/excel/handle/ExcelDataValidation.java

package jnpf.excel.handle;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import jnpf.excel.ExcelHelper;
import jnpf.excel.ExcelPostHandle;
import jnpf.model.ExcelColumnAttr;
import jnpf.util.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;

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

/**
 * excel添加数据下拉校验
 * 下拉选择（单选）、单选框组、开关导入EXCEL内生成【数据验证】下拉框
 */
public class ExcelDataValidation implements ExcelPostHandle {
    String sheetName;
    Map<String, ExcelColumnAttr> modelMap;
    ExcelHelper data;
    private Workbook workbook;
    int hiddenIndex = 0;
    int headerRowLen = 0;
    int lastRowNum = 0;
    Map<String, String[]> optionMap;

    /**
     * 用于下拉内容很多，字符超过255
     * <p>
     * 下拉选项（先将数据放到另一个sheet页，然后下拉的数据再去sheet页读取，解决普通下拉数据量太多下拉不显示问题）
     * <p>
     * firstRow 开始行号(下标0开始)
     * <p>
     * lastRow  结束行号，最大65535
     * <p>
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * <p>
     * lastCol 区域中最后一个单元格的列号
     * <p>
     * sheetIndex 创建的sheet的index。如果有多个下拉想放到sheet页，则需要设置不同的sheetIndex，（注意不能设置为0，0为主数据页）
     * <p>
     * selectList 下拉内容
     */

    public void selectLargeList(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, int sheetIndex, String[] selectList) {
        Sheet sheet = workbook.getSheetAt(0);
        //将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据
        String sheetName = "sheetName" + sheetIndex;
        Sheet hidden = workbook.createSheet(sheetName);
        //创建单元格对象
        Cell cell = null;
        //遍历我们上面的数组，将数据取出来放到新sheet的单元格中
        for (int i = 0, length = selectList.length; i < length; i++) {
            //取出数组中的每个元素
            String name = selectList[i];
            //根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
            Row row = hidden.createRow(i);
            //创建每一行中的第一个单元格
            cell = row.createCell(0);
            //然后将数组中的元素赋值给这个单元格
            cell.setCellValue(name);
        }
        // 创建名称，可被其他单元格引用
        String refers = sheetName + "!$A$1:$A$" + selectList.length;
        //这个3代表我的下拉数据从第三行开始生效，前面1,2行不显示下拉，你们根据自己情况修改。
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        //将第sheetIndex个sheet设置为隐藏
        sheet.addValidationData(dataValidation);
        hiddenIndex++;
    }

    @Override
    public void execute(ExcelHelper data, Map<String, Object> params) {
        hiddenIndex = 0;
        workbook = data.getWorkbook();
        optionMap = data.getOptionMap() != null ? data.getOptionMap() : new HashMap<>();
        List<ExcelExportEntity> list = data.getEntities();
        ExportParams exportParams = data.getExportParams();
        this.sheetName = exportParams.getSheetName();
        this.data = data;
        modelMap = data.getModelMap() != null ? data.getModelMap() : new HashMap<>();
        Sheet sheet = workbook.getSheet(exportParams.getSheetName());
        headerRowLen = data.isComplexTable() ? 2 : 1;
        lastRowNum = sheet.getLastRowNum();

        for (int i = 0; i < headerRowLen; i++) {
            Row headerRow = sheet.getRow(i);
            int lastCellNum = headerRow.getLastCellNum();
            for (int j = 0; j < lastCellNum; j++) {
                Cell cell = headerRow.getCell(j);
                addDataValidation(cell, j);
            }
        }
        // 隐藏校验sheet
        for (int i = 1; i <= hiddenIndex; i++) {
            workbook.setSheetHidden(i, true);
        }
    }

    private void addDataValidation(Cell cell, int columnIndex) {
        if (cell == null) return;
        String key = ((XSSFCell) cell).getRichStringCellValue().getString();
        if (StringUtils.isBlank(key)) return;
        String id = ExcelUtil.getIdFromCellValue(key);
        ExcelColumnAttr model = modelMap.get(id);
        if (Objects.isNull(model)) return;
        String[] options = optionMap.get(id);
        if (options != null && options.length > 0) {
            selectLargeList(workbook, headerRowLen, lastRowNum, columnIndex, columnIndex, columnIndex, options);
        }
    }
}