<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

public class ExcelDropdownDemo {
    
    public void createSingleDropdown() throws IOException {
        // 創(chuàng)建工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("數(shù)據(jù)錄入");
        
        // 定義下拉選項(xiàng)
        String[] options = {"待處理", "處理中", "已完成", "已關(guān)閉"};
        
        // 創(chuàng)建數(shù)據(jù)驗(yàn)證助手
        DataValidationHelper helper = sheet.getDataValidationHelper();
        
        // 創(chuàng)建約束：從數(shù)組創(chuàng)建下拉列表
        DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
        
        // 設(shè)置驗(yàn)證范圍（例如在A2:A100創(chuàng)建下拉框）
        CellRangeAddressList addressList = new CellRangeAddressList(1, 99, 0, 0);
        
        // 創(chuàng)建數(shù)據(jù)驗(yàn)證對(duì)象
        DataValidation validation = helper.createValidation(constraint, addressList);
        
        // 設(shè)置相關(guān)屬性
        validation.setShowErrorBox(true);
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("輸入錯(cuò)誤", "請(qǐng)從下拉列表中選擇");
        
        // 應(yīng)用到工作表
        sheet.addValidationData(validation);
        
        // 寫(xiě)入文件
        try (FileOutputStream fos = new FileOutputStream("template.xlsx")) {
            workbook.write(fos);
        }
        workbook.close();
    }
}

public void createCascadingDropdown() {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("多級(jí)聯(lián)動(dòng)");
    
    // 第一級(jí)：省份
    String[] provinces = {"浙江省", "江蘇省", "廣東省"};
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint provinceConstraint = 
        helper.createExplicitListConstraint(provinces);
    
    // 第二級(jí)：城市（使用名稱(chēng)定義）
    Name cityName = workbook.createName();
    cityName.setNameName("cityData");
    cityName.setRefersToFormula("INDIRECT($A$2)"); // 根據(jù)A2單元格的值動(dòng)態(tài)引用
    
    // 定義命名區(qū)域
    String[] zhejiangCities = {"杭州", "寧波", "溫州"};
    String[] jiangsuCities = {"南京", "蘇州", "無(wú)錫"};
    String[] guangdongCities = {"廣州", "深圳", "東莞"};
    
    // 在隱藏工作表存儲(chǔ)城市數(shù)據(jù)
    Sheet hiddenSheet = workbook.createSheet("hiddenData");
    for (int i = 0; i < provinces.length; i++) {
        String[] cities = null;
        if (i == 0) cities = zhejiangCities;
        else if (i == 1) cities = jiangsuCities;
        else cities = guangdongCities;
        
        Row row = hiddenSheet.createRow(i);
        for (int j = 0; j < cities.length; j++) {
            row.createCell(j).setCellValue(cities[j]);
        }
    }
    
    // 創(chuàng)建數(shù)據(jù)驗(yàn)證
    DataValidationConstraint cityConstraint = 
        helper.createFormulaListConstraint("cityData");
    CellRangeAddressList cityRange = new CellRangeAddressList(1, 1, 1, 1);
    DataValidation cityValidation = helper.createValidation(cityConstraint, cityRange);
    sheet.addValidationData(cityValidation);
}

@Service
public class DynamicDropdownService {
    
    @Autowired
    private DepartmentRepository departmentRepo;
    
    public void createDynamicDropdown(Workbook workbook, Sheet sheet) {
        // 從數(shù)據(jù)庫(kù)獲取部門(mén)列表
        List<Department> departments = departmentRepo.findAll();
        String[] deptNames = departments.stream()
            .map(Department::getName)
            .toArray(String[]::new);
        
        // 創(chuàng)建下拉驗(yàn)證
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = 
            helper.createExplicitListConstraint(deptNames);
        
        CellRangeAddressList addressList = 
            new CellRangeAddressList(1, 1000, 2, 2); // C列
        
        DataValidation validation = helper.createValidation(constraint, addressList);
        validation.setSuppressDropDownArrow(true); // 隱藏下拉箭頭
        sheet.addValidationData(validation);
    }
}

// 設(shè)置輸入提示
validation.createPromptBox("部門(mén)選擇", "請(qǐng)從列表中選擇所屬部門(mén)");
validation.setShowPromptBox(true);

// 設(shè)置錯(cuò)誤提示
validation.createErrorBox("無(wú)效輸入", "輸入值不在可選范圍內(nèi)");
validation.setErrorStyle(DataValidation.ErrorStyle.WARNING);

// 創(chuàng)建文本長(zhǎng)度約束
DataValidationConstraint lengthConstraint = 
    helper.createTextLengthConstraint(
        DataValidationConstraint.OperatorType.BETWEEN, 
        "1", "50"
    );

CellRangeAddressList range = new CellRangeAddressList(1, 100, 3, 3);
DataValidation validation = helper.createValidation(lengthConstraint, range);

// 避免為每個(gè)單元格單獨(dú)創(chuàng)建驗(yàn)證
CellRangeAddressList largeRange = 
    new CellRangeAddressList(1, 10000, 0, 0);

// 緩存常用下拉選項(xiàng)
private static final Map<String, String[]> DROPDOWN_CACHE = 
    new ConcurrentHashMap<>();

public String[] getCachedOptions(String type) {
    return DROPDOWN_CACHE.computeIfAbsent(type, k -> 
        loadOptionsFromDB(k)
    );
}

// 當(dāng)選項(xiàng)超過(guò)255個(gè)字符時(shí)，使用隱藏工作表存儲(chǔ)
Sheet hidden = workbook.createSheet("options");
Row row = hidden.createRow(0);
for (int i = 0; i < options.length; i++) {
    row.createCell(i).setCellValue(options[i]);
}

// 使用名稱(chēng)引用
Name namedRange = workbook.createName();
namedRange.setNameName("largeOptions");
namedRange.setRefersToFormula("options!$A$1:$Z$100");

// 處理不同Excel版本
if (workbook instanceof XSSFWorkbook) {
    // .xlsx文件支持更多選項(xiàng)
    validation.setShowErrorBox(true);
} else if (workbook instanceof HSSFWorkbook) {
    // .xls文件選項(xiàng)有限制
    validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
}

public class ExcelDropdownUtil {
    
    public static void addDropdown(Sheet sheet, int firstRow, int lastRow, 
                                  int firstCol, int lastCol, String[] options) {
        
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = 
            helper.createExplicitListConstraint(options);
        
        CellRangeAddressList addressList = 
            new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        
        DataValidation validation = helper.createValidation(constraint, addressList);
        
        // 默認(rèn)配置
        validation.setEmptyCellAllowed(true);
        validation.setShowPromptBox(true);
        validation.createPromptBox("提示", "請(qǐng)從下拉列表中選擇");
        
        sheet.addValidationData(validation);
    }
}

/**
     * 模板下載
     */
    @Override
    public void exportExcel(HttpServletResponse response) throws ApiException {
        List<CsiServer> csiServerList = csiServerMapper.getAllServer();
        List<String> serverList = csiServerList.stream()
                .map(CsiServer::getServerAllname)
                .filter(Objects::nonNull) // 防止 null 值
                .collect(Collectors.toList());

        if (serverList.isEmpty()) {
            serverList.add(" "); // Excel 下拉不能完全空，至少一個(gè)占位
        }

        String[] serverArray = serverList.toArray(new String[0]);

//        String debugJoined = String.join(",", serverArray);
//        log.info("下拉選項(xiàng)總長(zhǎng)度: {}, 內(nèi)容預(yù)覽: {}", debugJoined.length(),
//                debugJoined.length() > 100 ? debugJoined.substring(0, 100) + "..." : debugJoined);
//
//        if (debugJoined.length() > 255) {
//            log.warn("?? 下拉選項(xiàng)超長(zhǎng)（{} > 255），Excel將無(wú)法顯示下拉框！", debugJoined.length());
//            // 臨時(shí)截?cái)鄿y(cè)試
//            // serverArray = new String[]{"選項(xiàng)過(guò)多，請(qǐng)聯(lián)系管理員"};
//        }

        Workbook workbook = null;
        InputStream templateStream = null;

        try {
            // 1. 從 classpath 加載模板
            ClassPathResource classPathResource = new ClassPathResource(ORDERGRAIN_INBOUND_TEMPLATE);
            templateStream = classPathResource.getInputStream();

            // 2. 根據(jù)模板創(chuàng)建 Workbook（支持 .xlsx）
            workbook = new XSSFWorkbook(templateStream);

            // 3. 獲取要添加下拉框的工作表（假設(shè)是第一個(gè) sheet，或按名稱(chēng)獲?。?
            Sheet sheet = workbook.getSheetAt(0); // 或 workbook.getSheet("SheetName");

            // 4. 創(chuàng)建下拉框（例如：A列第2行到第1000行）
            DataValidation dataValidation = PoiUtil.getDataValidation(sheet, serverArray, 23, 1, 999);

            // 5. 添加驗(yàn)證規(guī)則
            sheet.addValidationData(dataValidation);

            // 6. 設(shè)置響應(yīng)頭，用于瀏覽器下載
            String fileName = URLEncoder.encode("收貨數(shù)據(jù)導(dǎo)入模板.xlsx", StandardCharsets.UTF_8.toString());
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            // 3. 設(shè)置字符編碼（避免文件名中文亂碼）
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());

            // 7. 寫(xiě)入響應(yīng)輸出流
            workbook.write(response.getOutputStream());
            response.getOutputStream().flush();

        } catch (IOException e) {
            throw new ApiException(ResultCode.FAULT,"導(dǎo)出Excel失敗");
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
                if (templateStream != null) {
                    templateStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

@Slf4j
public class PoiUtil {

    /**
     * Excel 下拉框數(shù)據(jù)驗(yàn)證工具方法
     * 功能：為指定工作表的目標(biāo)列、行范圍創(chuàng)建「靜態(tài)下拉框」（基于顯式選項(xiàng)數(shù)組），并配置錯(cuò)誤提示機(jī)制
     * 適用場(chǎng)景：下拉選項(xiàng)少（總字符數(shù)≤255，Excel原生限制）、選項(xiàng)固定的場(chǎng)景
     *
     * @param sheet           目標(biāo)工作表（如XSSFSheet、HSSFSheet），必須已創(chuàng)建且非空
     * @param dropDownOptions 下拉框選項(xiàng)數(shù)組，元素為字符串類(lèi)型；不可為null（空選項(xiàng)需傳空數(shù)組），元素不可含逗號(hào)（Excel下拉框默認(rèn)用逗號(hào)分隔選項(xiàng)）
     * @param column          下拉框所在列的索引（POI中列索引從0開(kāi)始，例：0=A列、1=B列、2=C列...）
     * @param firstRow        下拉框生效的起始行索引（POI中行索引從0開(kāi)始，例：0=Excel第1行、1=Excel第2行...），需≥0
     * @param lastRow         下拉框生效的結(jié)束行索引（需≥firstRow，例：99=Excel第100行），控制下拉框覆蓋的行數(shù)范圍
     * @return DataValidation 配置完成的數(shù)據(jù)驗(yàn)證對(duì)象（包含下拉框規(guī)則），需通過(guò) {@link Sheet#addValidationData(DataValidation)} 方法添加到工作表才會(huì)生效
     * @note 1. 字符限制：選項(xiàng)數(shù)組總字符數(shù)（含默認(rèn)分隔符逗號(hào)）不可超過(guò)255，否則Excel會(huì)報(bào)錯(cuò)，需改用「動(dòng)態(tài)下拉框」（隱藏工作表+公式引用）；
     * 2. 選項(xiàng)規(guī)范：數(shù)組元素不可包含逗號(hào)（,），否則會(huì)被Excel解析為多個(gè)選項(xiàng)，若需含逗號(hào)需自定義分隔符（需配合其他API）；
     * 3. 生效方式：返回的DataValidation對(duì)象需手動(dòng)添加到工作表，未添加則下拉框不顯示；
     * 4. 錯(cuò)誤提示：默認(rèn)啟用錯(cuò)誤提示框（輸入非下拉選項(xiàng)時(shí)觸發(fā)），可通過(guò)返回對(duì)象的setXXX方法調(diào)整提示文案、樣式；
     * 5. 版本兼容：支持Excel 2003（.xls，HSSFWorkbook）和2007+（.xlsx，XSSFWorkbook），無(wú)需修改方法邏輯
     */
    public static DataValidation getDataValidation(Sheet sheet,
                                                   String[] dropDownOptions,
                                                   int column,
                                                   int firstRow,
                                                   int lastRow) {
        // 1. 定義下拉框生效的單元格范圍：[firstRow, lastRow] 行 × [column, column] 列（單個(gè)列的連續(xù)行）
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, column, column);

        // 2. 獲取工作表的數(shù)據(jù)驗(yàn)證助手，用于構(gòu)建驗(yàn)證規(guī)則
        DataValidationHelper validationHelper = sheet.getDataValidationHelper();

        // 3. 創(chuàng)建顯式列表約束：下拉選項(xiàng)直接從傳入的數(shù)組中獲取，適用于選項(xiàng)固定的場(chǎng)景
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(dropDownOptions);

        // 4. 綁定約束與單元格范圍，生成數(shù)據(jù)驗(yàn)證對(duì)象（下拉框核心配置）
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);

        // 5. 配置錯(cuò)誤提示：輸入值不在下拉列表中時(shí)，顯示錯(cuò)誤提示框
        dataValidation.setShowErrorBox(true);

        return dataValidation;
    }

}