Excel error when dropdown list value contains hyphen (-) with Apache POI

Question

I have added dropdowns in some columns in excel sheet using Apache poi. When one of the drop down values contains character hyphen (-), then on opening the excel sheet, it gives error - We found a problem with some content in "Text.xlsx".

Without hyphen, all works fine. My code of creating dropdown is same as the accepted solution mentioned for this question -Limitation while generating excel drop down list with Apache POI

Please suggest some solution.

Abhishek Abhishek · Accepted Answer · 2018-05-29T09:07:07

Below code is working fine for me:

        DataValidation dataValidation = null;
        DataValidationConstraint constraint = null;
        DataValidationHelper validationHelper = null;

         XSSFWorkbook wb = new XSSFWorkbook();
         XSSFSheet sheet1=(XSSFSheet) wb.createSheet("sheet1");


            validationHelper=new XSSFDataValidationHelper(sheet1);
            CellRangeAddressList addressList = new  CellRangeAddressList(0,5,0,0);
            constraint =validationHelper.createExplicitListConstraint(new String[]{"SELECT","10-11", "20", "30"});
            dataValidation = validationHelper.createValidation(constraint, addressList);
            dataValidation.setSuppressDropDownArrow(true);      
            sheet1.addValidationData(dataValidation);

            FileOutputStream fileOut = new FileOutputStream("c:\\temp\\abhishek.xlsx");
            wb.write(fileOut);
            fileOut.close();
            wb.close();

Output:

Excel error when dropdown list value contains hyphen (-) with Apache POI

1 Answers