Java使用Apache?POI在Excel中創(chuàng)建下拉列表

更新時間：2026年03月23日 08:46:18 作者：Java小王子呀

在業(yè)務(wù)系統(tǒng)中,我們經(jīng)常需要導(dǎo)出包含下拉選擇框的Excel模板,用于規(guī)范數(shù)據(jù)錄入,Apache?POI作為Java操作Office文檔的主流工具,提供了完善的數(shù)據(jù)驗證功能,本文將詳細介紹如何使用POI在Excel中創(chuàng)建下拉列表,感興趣的可以了解下

概述

在業(yè)務(wù)系統(tǒng)中，我們經(jīng)常需要導(dǎo)出包含下拉選擇框的Excel模板，用于規(guī)范數(shù)據(jù)錄入。Apache POI作為Java操作Office文檔的主流工具，提供了完善的數(shù)據(jù)驗證功能。本文將詳細介紹如何使用POI在Excel中創(chuàng)建下拉列表。

核心實現(xiàn)

1. 基礎(chǔ)依賴

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

2. 單級下拉列表實現(xiàn)

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

3. 多級聯(lián)動下拉列表

public void createCascadingDropdown() {
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("多級聯(lián)動");
    
    // 第一級：省份
    String[] provinces = {"浙江省", "江蘇省", "廣東省"};
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint provinceConstraint = 
        helper.createExplicitListConstraint(provinces);
    
    // 第二級：城市（使用名稱定義）
    Name cityName = workbook.createName();
    cityName.setNameName("cityData");
    cityName.setRefersToFormula("INDIRECT($A$2)"); // 根據(jù)A2單元格的值動態(tài)引用
    
    // 定義命名區(qū)域
    String[] zhejiangCities = {"杭州", "寧波", "溫州"};
    String[] jiangsuCities = {"南京", "蘇州", "無錫"};
    String[] guangdongCities = {"廣州", "深圳", "東莞"};
    
    // 在隱藏工作表存儲城市數(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ù)驗證
    DataValidationConstraint cityConstraint = 
        helper.createFormulaListConstraint("cityData");
    CellRangeAddressList cityRange = new CellRangeAddressList(1, 1, 1, 1);
    DataValidation cityValidation = helper.createValidation(cityConstraint, cityRange);
    sheet.addValidationData(cityValidation);
}

4. 從數(shù)據(jù)庫動態(tài)加載選項

@Service
public class DynamicDropdownService {
    
    @Autowired
    private DepartmentRepository departmentRepo;
    
    public void createDynamicDropdown(Workbook workbook, Sheet sheet) {
        // 從數(shù)據(jù)庫獲取部門列表
        List<Department> departments = departmentRepo.findAll();
        String[] deptNames = departments.stream()
            .map(Department::getName)
            .toArray(String[]::new);
        
        // 創(chuàng)建下拉驗證
        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);
    }
}

高級配置

1. 輸入提示和錯誤信息

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

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

2. 限制輸入長度

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

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

性能優(yōu)化建議

批量設(shè)置驗證范圍

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

使用緩存機制

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

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

常見問題處理

下拉選項過多

// 當選項超過255個字符時，使用隱藏工作表存儲
Sheet hidden = workbook.createSheet("options");
Row row = hidden.createRow(0);
for (int i = 0; i < options.length; i++) {
    row.createCell(i).setCellValue(options[i]);
}

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

兼容性處理

// 處理不同Excel版本
if (workbook instanceof XSSFWorkbook) {
    // .xlsx文件支持更多選項
    validation.setShowErrorBox(true);
} else if (workbook instanceof HSSFWorkbook) {
    // .xls文件選項有限制
    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);
        
        // 默認配置
        validation.setEmptyCellAllowed(true);
        validation.setShowPromptBox(true);
        validation.createPromptBox("提示", "請從下拉列表中選擇");
        
        sheet.addValidationData(validation);
    }
}

總結(jié)

通過Apache POI的數(shù)據(jù)驗證功能，我們可以輕松實現(xiàn)Excel下拉框的創(chuàng)建。關(guān)鍵點包括：

使用DataValidationHelper創(chuàng)建約束
正確設(shè)置驗證范圍
合理處理多級聯(lián)動
注意性能和兼容性問題

掌握這些技巧后，就能創(chuàng)建出功能豐富、用戶友好的Excel數(shù)據(jù)錄入模板。

一般工作中需要后端下載時，多數(shù)用于從數(shù)據(jù)庫動態(tài)加載選項

/**
     * 模板下載
     */
    @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 下拉不能完全空，至少一個占位
        }

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

//        String debugJoined = String.join(",", serverArray);
//        log.info("下拉選項總長度: {}, 內(nèi)容預(yù)覽: {}", debugJoined.length(),
//                debugJoined.length() > 100 ? debugJoined.substring(0, 100) + "..." : debugJoined);
//
//        if (debugJoined.length() > 255) {
//            log.warn("?? 下拉選項超長（{} > 255），Excel將無法顯示下拉框！", debugJoined.length());
//            // 臨時截斷測試
//            // serverArray = new String[]{"選項過多，請聯(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è)是第一個 sheet，或按名稱獲取）
            Sheet sheet = workbook.getSheetAt(0); // 或 workbook.getSheet("SheetName");

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

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

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

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

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

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

        return dataValidation;
    }

}

效果圖