我正在尝试使用Apache POI为一个单元添加一个下拉列表。 下拉列表包含302个string。 我总是得到这个错误：Excel在test.xlsx中发现不可读的内容。
然后我做了下面的testing。 当项目数<= 88时，下拉列表创build成功。 当数字大于88时，打开excel文件时出错，没有下拉列表。
谢谢 ！！！
import org.apache.poi.xssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.ss.usermodel.*; import java.io.FileOutputStream; import java.io.IOException; import java.util.TreeSet; public class Test { public static void main(String[] args) { TreeSet<String> temp_rxGroups = new TreeSet<String>(); for (int i = 0; i < 100; i++) { temp_rxGroups.add("" + i); } String[] countryName = temp_rxGroups.toArray(new String[temp_rxGroups.size()]); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet realSheet = workbook.createSheet("realSheet"); XSSFSheet hidden = workbook.createSheet("hidden"); for (int i = 0, length= countryName.length; i < length; i++) { String name = countryName[i]; XSSFRow row = hidden.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(name); } Name namedCell = workbook.createName(); namedCell.setNameName("hidden"); namedCell.setRefersToFormula("hidden!$A$1:$A$" + countryName.length); DataValidation dataValidation = null; DataValidationConstraint constraint = null; DataValidationHelper validationHelper = null; validationHelper=new XSSFDataValidationHelper(hidden); CellRangeAddressList addressList = new CellRangeAddressList(0,10,0,0); //line constraint =validationHelper.createExplicitListConstraint(countryName); dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(true); workbook.setSheetHidden(1, true); realSheet.addValidationData(dataValidation); FileOutputStream stream = new FileOutputStream("c:\\test.xlsx"); workbook.write(stream); stream.close(); } } }
首先，我发现这不是一个Apache POI错误。 这是Excel的限制。 这是链接 ，
“数据验证下拉列表中显示的项目数量是有限制的：
该列表可以显示在工作表上显示的32,767个项目。 如果您将项目键入到数据验证对话框（分隔列表）中，则限制为256个字符，包括分隔符。
显然，这行明确地显示了超过256个字符。
constraint =validationHelper.createExplicitListConstraint(countryName);
其次，这是我的解决方案。 它工作正常。
public class Test { public static void main(String[] args) throws IOException{ TreeSet<String> temp_rxGroups = new TreeSet<String>(); for (int i = 0; i < 302; i++) { temp_rxGroups.add("" + i); } String[] countryName = temp_rxGroups.toArray(new String[temp_rxGroups.size()]); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet realSheet = workbook.createSheet("realSheet"); XSSFSheet hidden = workbook.createSheet("hidden"); for (int i = 0, length= countryName.length; i < length; i++) { String name = countryName[i]; XSSFRow row = hidden.createRow(i); XSSFCell cell = row.createCell(0); cell.setCellValue(name); } DataValidation dataValidation = null; DataValidationConstraint constraint = null; DataValidationHelper validationHelper = null; validationHelper=new XSSFDataValidationHelper(realSheet); CellRangeAddressList addressList = new CellRangeAddressList(0,0,0,0); constraint =validationHelper.createFormulaListConstraint("hidden!$A$1:$A$" + countryName.length); dataValidation = validationHelper.createValidation(constraint, addressList); dataValidation.setSuppressDropDownArrow(true); workbook.setSheetHidden(1, true); realSheet.addValidationData(dataValidation); FileOutputStream stream = new FileOutputStream("c:\\test.xlsx"); workbook.write(stream); stream.close(); } }