java - 如何使用POI API在Excel中验证特定 header 名称的所有列

我有一个带有标题，empName，empId，Department的excel。我需要提供下拉菜单供用户输入部门名称。用户应该只能输入下拉列表中存在的值。我尝试了下面的代码，但未验证部门列。
CellRangeAddressList addressList = new CellRangeAddressList（2，2，2，2）;
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint（new String [] {“ ACCOUNTS”，“ ISE”，“ SALES”}）;
        DataValidation dataValidation =新的HSSFDataValidation（addressList，dvConstraint）;
        dataValidation.setSuppressDropDownArrow（true）;
        sheet.addValidationData（dataValidation）;
        font.setFontHeightInPoints（（short）16）;

最佳答案

下面的代码创建一个工作簿，该工作簿的工作表包含标题单元格

  |    A    |   B   |     C      |
1 | empName | empId | Department |

A1:C1中的C2:C65536中的数据验证列表，或HSSF中的C2:C1048576中的数据验证列表。

该代码适用于二进制XSSF文件（*.xls）以及HSSF Office Open XML文件（*.xlsx）。

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddressList;

class CreateExcelDataValidationListsWholeColumn {

 public static void main(String[] args) throws Exception {

  Workbook workbook = new HSSFWorkbook();
  //Workbook workbook = new XSSFWorkbook();

  Sheet sheet = workbook.createSheet("Sheet1");

  String[] headers = new String[]{"empName", "empId", "Department"};
  Row row = sheet.createRow(0);
  for (int c = 0 ; c < headers.length; c++) {
   row.createCell(c).setCellValue(headers[c]);
  }

  //data validation in column B, except first row:
  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(new String[]{"ACCOUNTS", "ISE", "SALES"}) ;
  int lastRow = workbook.getSpreadsheetVersion().getLastRowIndex();
  CellRangeAddressList addressList = new CellRangeAddressList(1, lastRow, 2, 2); // C2:C65536 in HSSF or C2:C1048576 in XSSF
  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  validation.setShowErrorBox(true);
  sheet.addValidationData(validation); // data validation for C2:C65536 in HSSF or C2:C1048576 in XSSF

  FileOutputStream out = null;
  if (workbook instanceof HSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationListsWholeColumn.xls");
  } else if (workbook instanceof XSSFWorkbook) {
   out = new FileOutputStream("CreateExcelDataValidationListsWholeColumn.xlsx");
  }
  workbook.write(out);
  workbook.close();
  out.close();

 }
}