使用apache-poi库创建下拉列表的Excel corrput

Question

需要描述

我需要用Apache POI在Excel中创建下拉列表。这个列表应该包含字符串。

码

XSSFSheet sheet = workbook.getSheet(fieldName.getTabName());
XSSFDataValidationHelper helper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) helper.createExplicitListConstraint(myarray);
CellRangeAddressList addressList = new CellRangeAddressList(3, 10, 5, 5);
XSSFDataValidation validation = (XSSFDataValidation) helper.createValidation(dvConstraint, addressList);
validation.setEmptyCellAllowed(false);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);

误差

就某些内容而言，它运行得很好。但是，如果数组myarray包含一些诸如STUDENT或HOUSE, DEF之类的值，则当我要打开Excel时，会出现一个错误：

我们发现'file.xlsx‘中的一些内容有问题。你想让我们尽可能多地恢复吗？如果您信任工作簿的来源，请单击“是”。

附加信息

my array是List<WebElement>。我拿到这张单子时：

Select select = new Select(By.xpath("myxpath"));
List<WebElement> opt = select.getOptions();
myarray = new String[opt.size()];
for(int i = 0; i < opt.size(); i++) 
    myarray[i] = opt.get(i).getText();

但是我认为这段代码不是问题，因为我能够获得所有相关文本的选项。当我将这些内容放入createExplicitListConstraint中时，就会发生一些奇怪的事情。

当我打印myarray的内容时，所有的值都正确地显示在控制台中。

Answer 1

显式数据验证列表的公式长度有限制。最大长度是255。如果显式数据验证列表的公式超过255个，则Excel在打开错误We found a problem with some content in ...时失败。如果您单击Yes，Excel会告诉您它已经修复了/xl/worksheet/sheet*.xml部件。之后，数据验证列表就不工作了。

复制示例：

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 CreateExcelDataValidationExplicitList {

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

  int arrSize = 50;
  String[] myarray = new String[arrSize+1];

  for (int i = 0; i < arrSize; i++) {
   myarray[i] = "abcd";
  }

  myarray[arrSize] = "abcde"; // this works
  //myarray[arrSize] = "abcdef"; // this fails

  String arrayFormula = String.join(",", myarray);
System.out.println(arrayFormula.length()); // up to 255 works, bigger lengths fail

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

  Sheet sheet = workbook.createSheet();

  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
  CellRangeAddressList addressList = new CellRangeAddressList(3, 10, 5, 5);
  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(myarray);

  DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
  validation.setEmptyCellAllowed(false);
  if (workbook instanceof XSSFWorkbook) validation.setShowErrorBox(true);

  sheet.addValidationData(validation);

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

 }
}

唯一的解决方案是将列表项放在单独工作表的单元格中，然后使用createFormulaListConstraint而不是createExplicitListConstraint。我已经在这里展示了这个：Apache POI Double Values in ComboBox。