java - 无法使用 POI 在 Excel 中生成下拉菜单

Question

我正在尝试使用 java apache POI 库在 Excel 中生成下拉列表。我尝试了以下方法。

我可以使用像

这样的直接方法生成下拉菜单

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
XSSFDataValidationHelper dvHelper = new 
XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
dvConstraint, addressList);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

但是我无法使用 createFormulaListConstraint 生成下拉列表(我想生成依赖的下拉列表，所以我使用了这种方法)

public void generateSheet(String type, List<Product> productList) {
    try {
  XSSFWorkbook workbook;
  File file = new File(type);
  FileOutputStream fos = new FileOutputStream(file);
  workbook = new XSSFWorkbook();
  XSSFSheet sheet = workbook.createSheet("Data Validation");

  Row row = null;
  Cell cell = null;
  Name name = null;

  row = sheet.createRow(9);
  cell = row.createCell(0);
  cell.setCellValue("APPLE");
  cell = row.createCell(1);
  cell.setCellValue("MANGOE");
  cell = row.createCell(2);
  cell.setCellValue("ORANGE");

  name = sheet.getWorkbook().createName();
  name.setNameName("FRUIT");
  name.setRefersToFormula("$A$10:$C$10");

  DataValidationHelper helper = null;
  DataValidationConstraint constraint = null;
  DataValidation validation = null;

  helper = sheet.getDataValidationHelper();
  constraint = helper.createFormulaListConstraint("FRUIT");
  validation = helper.createValidation(constraint, new CellRangeAddressList(0,0,0,0));
  sheet.addValidationData(validation);

  workbook.write(fos);
  fos.flush();
  fos.close();
  LOGGER.info("Finished processing sheet to download");
} catch (Exception e) {
  Logger.error("Exception while writing excel", e);
 }
}

但它正在生成损坏的文件，并且下拉列表未填充数据。我附上了打开 Excel 时收到的消息的屏幕截图。我还附上了修复Excel后得到的日志文件。

生成的文件链接:- https://www.dropbox.com/s/tdkimkjathazgki/TestFile.xlsx?dl=0

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>Repair Result to TestFile0.xml</logFileName>
    <summary>Errors were detected in file '/Users/xyz/Downloads/TestFile.xlsx'</summary>
    <removedRecords summary="Following is a list of removed records:">
        <removedRecord>
        Removed Records: Named range from /xl/workbook.xml (Workbook)
        </removedRecord>
    </removedRecords>
</recoveryLog>

Answer 1

最后我能够使用 POI 生成依赖下拉列表。

public void generateSheet(String type, List<Product> productList) {
    try {

      XSSFWorkbook workbook;
      File file = new File(type);
      FileOutputStream fos = new FileOutputStream(file);
      workbook = new XSSFWorkbook();

      String sname = "TestSheet";
      String parentName = "STATE";
      String childName1 = "GUJARAT";
      String childName2 = "KARNATAKA";
      String childName3 = "MAHARASHTRA";
      XSSFSheet sheet = workbook.createSheet(sname);

      Row row = null;
      Cell cell = null;
      row = sheet.createRow(9);
      cell = row.createCell(0);
      cell.setCellValue("Gujarat");
      cell = row.createCell(1);
      cell.setCellValue("Karnataka");
      cell = row.createCell(2);
      cell.setCellValue("Maharashtra");

      row = sheet.createRow(10);
      cell = row.createCell(0);
      cell.setCellValue("Ahmedabad");
      cell = row.createCell(1);
      cell.setCellValue("Rajkot");
      cell = row.createCell(2);
      cell.setCellValue("Gandhinagar");
      cell = row.createCell(3);
      cell.setCellValue("Surat");
      cell = row.createCell(4);
      cell.setCellValue("Vapi");

      row = sheet.createRow(11);
      cell = row.createCell(0);
      cell.setCellValue("Bangalore");
      cell = row.createCell(1);
      cell.setCellValue("Hasan");
      cell = row.createCell(2);
      cell.setCellValue("Mysore");
      cell = row.createCell(3);
      cell.setCellValue("Mangalore");

      row = sheet.createRow(12);
      cell = row.createCell(0);
      cell.setCellValue("Mumbai");
      cell = row.createCell(1);
      cell.setCellValue("Pune");
      cell = row.createCell(2);
      cell.setCellValue("Aurangabad");


      // 1. create named range for a single cell using areareference
      Name namedCell1 = sheet.getWorkbook().createName();
      namedCell1.setNameName(parentName);
      String reference1 = sname+"!$A$10:$C$10"; // area reference
      namedCell1.setRefersToFormula(reference1);

      Name namedCell2 = sheet.getWorkbook().createName();
      namedCell2.setNameName(childName1);
      String reference2 = sname+"!$A$11:$E$11"; // area reference
      namedCell2.setRefersToFormula(reference2);

      Name namedCell3 = sheet.getWorkbook().createName();
      namedCell3.setNameName(childName2);
      String reference3 = sname+"!$A$12:$D$12"; // area reference
      namedCell3.setRefersToFormula(reference3);

      Name namedCell4 = sheet.getWorkbook().createName();
      namedCell4.setNameName(childName3);
      String reference4 = sname+"!$A$13:$C$13"; // area reference
      namedCell4.setRefersToFormula(reference4);

      DataValidationHelper helper = null;
      DataValidationConstraint constraint = null;
      DataValidation validation = null;

      helper = sheet.getDataValidationHelper();
      constraint = helper.createFormulaListConstraint(parentName);
      validation = helper.createValidation(constraint, new CellRangeAddressList(0,0,0,0));
      sheet.addValidationData(validation);

      constraint = helper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
      validation = helper.createValidation(constraint, new CellRangeAddressList(0,0,1,1));
      sheet.addValidationData(validation);

      workbook.write(fos);
      fos.flush();
      fos.close();
      LOGGER.info("Finished processing sheet to download");
    } catch (Exception e) {
      Logger.error("Exception while writing excel", e);
    }
  }