2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

How to generate a excel with a dropdown column using itext

My problem is i want to create a dropdown in excel sheet which i am generating using itext api. Now when i am giving my data array which is more that 255 char is not taking and giving exception. Anybody have faced issue and solved please suggest.
userimage
I did tried below code making a dropdown with more that 255 char in excel sheet.

List<String> spaceTypeDetails = null; // Will contain the data that
                                      // wanted in an excel as
                                      // dropdown.
Map<String, Object> model = new HashMap<String, Object>();
Map<String, Object> model1 = new HashMap<String, Object>();

HSSFWorkbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("ExcelTemplate");

HSSFRow row = (HSSFRow) sheet.createRow(0);

Cell cell = row.createCell(0);
Cell cell1 = row.createCell(1);
cell.setCellValue("BOOK1");
cell1.setCellValue("BOOK2");

Object[] values = spaceTypeDetails.toArray();
Sheet newsheet = workbook.createSheet("Sheet1");
String[] newValues = new String[values.length];
for (int i = 0; i < values.length; i++) {
     newValues[i] = (String) values[i];
     HSSFRow rows = (HSSFRow) newsheet.createRow(i);
     rows.createCell(0).setCellValue((String) values[i]);
}

workbook.setSheetHidden(1, true);
Row r = null;
Cell c = null;
Name name = null;

r = newsheet.createRow(0);
c = r.createCell(0);

List<String> list = (List<String>) spaceTypeDetails;

r = newsheet.createRow(10);
int l = 0;
for (String spaceType : list) {
     c = r.createCell(l++);
     c.getStringCellValue();
     c.setCellValue(spaceType);
}

name = newsheet.getWorkbook().createName();
name.setRefersToFormula("Sheet1!$B$11:$Z$11");
name.setNameName("spacetype");
for (int i = 1; i < 300; i++) {
     Row nr = sheet.createRow(i);
     for (int j = 0; j < 31; j++) {
           Cell cell2 = nr.createCell(j);
           if (j == 1 && i != 0) {
                  DVConstraint dvConstraint = DVConstraint
                                .createFormulaListConstraint("spacetype");
                  CellRangeAddressList addressList = new CellRangeAddressList(
                                i, i, j, j);
                  HSSFDataValidation dataValidation = new HSSFDataValidation(
                                addressList, dvConstraint);
                  dataValidation.setSuppressDropDownArrow(false);
                  ((HSSFSheet) sheet).addValidationData(dataValidation);
                  cell2.setCellStyle(left);
           }
     }
}


Hope this will work for you.

Answer is