import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Arrays;
public class ExcelDropDownModifier {
public static void main(String[] args) throws Exception {
FileInputStream fis = new FileInputStream("path/to/your/excel.xls");
HSSFWorkbook workbook = new HSSFWorkbook(fis);
HSSFSheet sheet = workbook.getSheetAt(0); // 假设修改第一个工作表
String[] cers = Arrays.asList("男", "女", "第三性别").toArray(new String[0]);
// 创建数据验证帮助器
DataValidationHelper helper = sheet.getDataValidationHelper();
// 参数分别为 起始行索引(第5行)、结束行索引、起始列索引(第9列)、结束列索引(第9列)
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(4, sheet.getLastRowNum(), 8, 8);
// 创建数据验证约束,查询数据作为下拉列表的选项
DataValidationConstraint constraint = helper.createExplicitListConstraint(cers);
// 创建数据验证
DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
// 应用数据验证到工作表
sheet.addValidationData(validation);
// 写回修改
FileOutputStream fos = new FileOutputStream("path/to/your/excel_updated.xls");
workbook.write(fos);
fos.close();
// 关闭资源
workbook.close();
fis.close();
}
}