package com.kcim.common.util.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

/**
 * @program: CostAccount
 * @description:
 * @author: Wang.YS
 * @create: 2023-11-16 21:10
 **/

public class ExcelUtil {
    /**
     * 创建下拉列表选项【适用长度超过255】
     * @param workbook
     * @param values   下拉框的选项值
     * @param firstRow 起始行（从0开始）
     * @param lastRow  终止行（从0开始）
     * @param firstCol 起始列（从0开始）
     * @param lastCol  终止列（从0开始）
     */
    public static void createDropDownExpandList(Workbook workbook, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        Sheet sheet = workbook.getSheetAt(0);
        //创建名为"hidden"的新Sheet页
        Sheet hidden = workbook.createSheet("hidden");

        //遍历选项值填充到"hidden"页的第一列
        Cell cell = null;
        for (int i = 0; i < values.length; i++) {
            String value = values[i];
            Row row = hidden.createRow(i); //第i行
            cell = row.createCell(0);      //第1列
            cell.setCellValue(value);
        }

        //指定"hidden"页面选项值的坐标，上面均填充到第一列 所以从A1开始到A values的个数
        Name name = workbook.createName();
        name.setNameName("hidden");
        name.setRefersToFormula("hidden!$A$1:$A$" + values.length);

        //将"hidden"的数据加载
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint("hidden");

        //设置下拉框作用区域，起始行 终止行 起始列 终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        //设置第二个sheet页为隐藏
        workbook.setSheetHidden(1, true);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }
}
