使用apachepoi库创建下拉列表导致java 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时出错：

We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of the workbook, Click Yes.

其他信息

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