Excel Drop down using DataValidationHelper (POI)

Question

I am generating an excel file (.xlsx) using SXSSFWorkbook (stream) with 1 million rows. One column has to contain a dropdown with 4-5 values. I am able to generate this but I have two problems-

The excel is taking lot of time to generate. 7 minutes with dropdown and 11 seconds without for 250,000 rows.
Unable to open the file once it is generated as it is asking to recover the data which will fail eventually. The message says "Errors were detected in file D:\Test.xlsx Removed Feature: Data validation from /xl/worksheets/sheet2.xml part"

The below is the code snippet

DataValidationHelper validationHelper = sh.getDataValidationHelper();
CellRangeAddressList addressList = new CellRangeAddressList(0, sh.getLastRowNum(), cellnum, cellnum);
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(new String[] { "High risk", "Medium risk", "Low risk", "No risk" });
DataValidation  dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setSuppressDropDownArrow(true);
sh.addValidationData(dataValidation);

Please suggest me better solutions.

Akash Akash · Accepted Answer · 2018-12-14T12:12:31

There is a simple solution for this. Create a hidden (or password protected) sheet in the excel, with the data you need in the drop-down menu. Then refer that sheet in the dataValidationConstraint tag. The download should not take much time. PFB sample code

  main(){
new_workbook = new XSSFWorkbook();
hiddenRiskSheet= new_workbook.createSheet("RiskHidden");
createRiskHiddenSheet(hiddenPrepaidSheet);
                hiddenRiskSheet.protectSheet("passw0rd");
                hiddenRiskSheet.enableLocking();
DataValidationHelper validationHelper = null;
CellRangeAddressList addressList = new CellRangeAddressList(0, sh.getLastRowNum(), cellnum, cellnum);
DataValidationConstraint constraint = validationHelper..createFormulaListConstraint("RiskHidden!$A$1:$A$4");
DataValidation  dataValidation = validationHelper.createValidation(constraint, addressList);
dataValidation.setSuppressDropDownArrow(true);
validationHelper.addValidationData(dataValidation); 
}   
public void createRiskHiddenSheet(XSSFSheet hiddenRiskSheet)
     {
            String[] risk = { "High risk", "Medium risk", "Low risk", "No risk" };
            for (int i = 0; i < 4; i++) {
                Row row = hiddenRiskSheet.createRow(i);

                    Cell cell = row.createCell(i);
                    String cat = risk[i];
                    cell.setCellValue(cat);
                }
            }
        }

Excel Drop down using DataValidationHelper (POI)

1 Answers