Apache POI实现Excel下拉框：表头及后续行数据源的正确地址列表

嘿，这个需求我经常碰到！用Apache POI实现带表头+后续行的下拉框，核心是选对数据源的引用方式——分固定数据和动态新增数据两种场景，我给你拆解清楚：

一、固定数据源范围（数据量不会变化时） #

如果你的表头和数据行是固定的（比如表头在A1，数据到A10），直接用绝对单元格引用定义范围就行，这样下拉框复制到其他单元格时，数据源不会跑偏。

代码示例（XSSF格式，.xlsx） #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class FixedDropdownExample {
    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("Sheet1");
            
            // 先模拟填入表头和数据（A1:A10）
            for (int i = 0; i < 10; i++) {
                sheet.createRow(i).createCell(0).setCellValue(i == 0 ? "产品类别" : "类别" + i);
            }
            
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            
            // 定义数据源：表头A1到A10（绝对引用的$符号不能少！）
            String sourceRange = "$A$1:$A$10";
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(sourceRange);
            
            // 指定下拉框应用的单元格：比如B列所有行（行0到最后一行，列索引1对应B列）
            CellRangeAddressList addressList = new CellRangeAddressList(
                0, sheet.getLastRowNum(), 
                1, 1
            );
            
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            // 开启错误提示，防止用户输入不在列表里的内容
            validation.setShowErrorBox(true);
            validation.createErrorBox("输入无效", "请从下拉列表中选择选项");
            
            sheet.addValidationData(validation);
            
            // 写入文件
            try (FileOutputStream fos = new FileOutputStream("fixed_dropdown.xlsx")) {
                workbook.write(fos);
            }
        }
    }
}

关键细节 #

• 绝对引用$A$1:$A$10：去掉$的话，下拉框复制到其他单元格时，数据源会相对偏移（比如复制到B2，数据源会变成A2:A11），这通常不是我们想要的。
• CellRangeAddressList参数：前两个是起始/结束行，后两个是起始/结束列索引（列从0开始计数）。

二、动态数据源范围（后续会新增数据时） #

如果之后可能在数据源列新增行，固定范围就会失效，这时候可以用动态名称范围，通过Excel公式自动识别所有非空单元格。

代码示例 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class DynamicDropdownExample {
    public static void main(String[] args) throws IOException {
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            XSSFSheet sheet = workbook.createSheet("Sheet1");
            
            // 模拟初始数据（A1:A5）
            for (int i = 0; i < 5; i++) {
                sheet.createRow(i).createCell(0).setCellValue(i == 0 ? "客户类型" : "类型" + i);
            }
            
            // 1. 创建动态名称范围
            Name dynamicSource = workbook.createName();
            dynamicSource.setNameName("DynamicDropdownList");
            // 公式解释：以A1为起点，高度是A列非空单元格总数，宽度1列
            // 注意：Sheet1要和你的工作表名称完全一致
            dynamicSource.setRefersToFormula(
                "OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"
            );
            
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            
            // 2. 把动态名称绑定到下拉框约束
            DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint("DynamicDropdownList");
            
            // 3. 指定下拉框应用范围（比如B列）
            CellRangeAddressList addressList = new CellRangeAddressList(
                0, sheet.getLastRowNum(), 
                1, 1
            );
            
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
            
            // 写入文件
            try (FileOutputStream fos = new FileOutputStream("dynamic_dropdown.xlsx")) {
                workbook.write(fos);
            }
        }
    }
}

关键细节 #

• 动态公式OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)：
  • OFFSET：从A1开始，偏移0行0列，高度为A列非空单元格数量，宽度1列。
  • COUNTA：自动统计A列所有非空单元格，新增数据后下拉框会自动包含新内容。
• 如果数据源列有空行，COUNTA会在第一个空行处停止统计，可以改用这个公式规避：

  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH("*",Sheet1!$A:$A,-1))
  

额外注意事项 #

• 要是用旧版.xls格式（HSSF），逻辑类似，但需要用HSSFDataValidationHelper替代默认的DataValidationHelper。
• 工作表名称要和公式里的完全一致，否则下拉框会加载失败。
• 想把下拉框应用到多列？修改CellRangeAddressList的列索引范围就行（比如1,3对应B、C、D列）。

内容的提问来源于stack exchange，提问作者poonam manwani