I am trying to add a drop down list for one cell using Apache POI. The drop down list contains 302 Strings. I always got this error: Excel found unreadable content in test.xlsx.
Then I did the following test. When number of items <=88, the drop down list created successfully. When the number >88, I got an error when opening the excel file and no drop down list.
Thank you !!!
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();
}
}
}
Go to Data > Data Validation. On the Settings tab, click in the Source box, and then on the worksheet that has the entries for your drop-down list, select all of the cells containing those entries. You'll see the list range in the Source box change as you select.
HSSFWorkbook − This class has methods to read and write Microsoft Excel files in . xls format. It is compatible with MS-Office versions 97-2003. XSSFWorkbook − This class has methods to read and write Microsoft Excel and OpenOffice xml files in .
Apache POI provides two methods that help count rows: getLastRowNum() and getPhysicalNumberOfRows().
First, I found this is not an Apache POI bug. It is a limitation from Excel. This is the link,
"There are limits to the number of items that will show in a data validation drop down list:
The list can show up to show 32,767 items from a list on the worksheet. If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators."
Obviously, this line explicitly types more than 256 characters.
constraint =validationHelper.createExplicitListConstraint(countryName);
Second, this is my solution. It works fine.
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();
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With