Not able to view ComboBox in Excel using JXL API

Question

I am trying to show ComboBox in JXL API with following Code:

ArrayList<String> arrList = new ArrayList<String>();
arrList.add("DropDown1");
arrList.add("DropDown2");
arrList.add("DropDown3");
WritableCellFeatures cellFeatures = new WritableCellFeatures();
cellFeatures.setDataValidationList(arrList);

Blank b = null;
Label checkLabel = null;
for (int x = 0; x < xlData.size(); x++) {
    for (int i = 0; i <= 14; i++) {
        System.out.println("X:" + x + "I:" + i);
        if (i > 9) {
            checkLabel = new Label(i, x + xlHeader.size(),(String) arrList.get(0));
            //b = new Blank(i, x + xlHeader.size());
            //b.setCellFeatures(cellFeatures);
            checkLabel.setCellFeatures(cellFeatures);
            writableSheet.addCell(checkLabel);
            System.out.println("Combo Cell : " + x + ":" + i);
        }
    }
}

I have tried both "Blank" Cell as well as "Label". But still the Excel is not showing ComboBox.

Answer 1

If you call close() without calling write() first, a completely empty file will be generated.

Once you have finished adding sheets and cells to the workbook, you call write() on the workbook, and then close the file. This final step generates the output file (output.xls in this case) which may be read by Excel. credits this excellent tutorial it's required to add:

        copy.write(); 
        copy.close();

The cellFeatures needs to be re-instantiate inside the loop

according to my tests this code works fine:

        WritableCellFeatures cellFeatures =  null;
        Label checkLabel = null;
        for (int x = 0; x < xlData.size(); x++) {
            for (int i = 0; i <= 14; i++) {
                System.out.println("X:" + x + "I:" + i);
                if (i > 9) {
                   checkLabel = new Label(i, x + xlHeader.size(), (String) arrList.get(0));
                   cellFeatures = new WritableCellFeatures();
                   cellFeatures.setDataValidationList(arrList);
                   checkLabel.setCellFeatures(cellFeatures);
                   writableSheet.addCell(checkLabel);                           
                }
            }
        }
        // All cells modified/added. Now write out the workbook 
        workbook.write();
        workbook.close();

Even the Blank version works but in this case the cell hasn't an initial value

according to my tests also this code works fine:

        WritableCellFeatures cellFeatures =  null;
        Blank b = null;
        for (int x = 0; x < xlData.size(); x++) {
            for (int i = 0; i <= 14; i++) {
                System.out.println("X:" + x + "I:" + i);
                if (i > 9) {
                   b = new Blank(i, x + xlHeader.size());
                   cellFeatures = new WritableCellFeatures();
                   cellFeatures.setDataValidationList(arrList);
                   b.setCellFeatures(cellFeatures);
                   writableSheet.addCell(b);                           
                }
            }
        }
        // All cells modified/added. Now write out the workbook 
        workbook.write();
        workbook.close();

If you open the generated file .xls with Excel 2010 or Excel 2013 you may need to save as .xlsx to see the combo.

I experienced that opening .xls by Excel2010/2013, even if the cells actually contains a data validation list and the validation constraints works the data validation arrow are missing; that you need to save as in the new format if you want see the arrow and the combobox.

Moreover this drawback seems rather caused by the last Excel versions and not by JXL, demonstrated by the fact that opening the .xls in OpenOffice.org Cal 3.4.1 there is not any problem and the combo works correctly; this could be related to the fact that the current version jxl 2.6.12 2009-10-26 I use for the test Generates spreadsheets in Excel 2000 format

---

The Java code developed for this answer is available to anyone who wants to improve or fork and play with it.