我正在尝试使用Apache POI为一个单元格添加下拉列表 . 下拉列表包含302个字符串 . 我总是遇到这个错误:Excel在test.xlsx中找到了不可读的内容 .
然后我做了以下测试 . 当项目数<= 88时,下拉列表成功创建 . 当数字> 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();
}
}
}
1 回答
首先,我发现这不是Apache POI错误 . 这是Excel的限制 . 这是link,
“在数据验证下拉列表中显示的项目数量有限:
该列表最多可显示工作表上的列表中的32,767项 . 如果在数据验证对话框(分隔列表)中键入项目,则限制为256个字符,包括分隔符 . “
显然,这一行显式地输入了超过256个字符 .
其次,这是我的解决方案 . 它工作正常 .