CODEWITHSUNDEEP
javaapache-poixlsx
paramvir
paramvir

Reputation: 404

Validations not working on xlsx file using apache-poi java

Hi I am working on creating a ".xlsx" file using apache-poi in java. So I do some validations and generate the file. Now these validations are enabled in the generated file, if I manually check the validations in the Data validations section of the file.

But when I put the illegal value in those columns, it don't show any alert message, something like "this value is not permitted" and go ahead to let the user put the illegal value. 

public static void main(String[] args) throws Exception 
   {
       XSSFWorkbook workbook = new XSSFWorkbook();
       //HSSFWorkbook workbook = new HSSFWorkbook();

        XSSFSheet spreadsheet = workbook.createSheet("Order Details");
        //HSSFSheet spreadsheet = workbook.createSheet("Order Details");
        // Create row object
        XSSFRow row;
        //HSSFRow row;

        CellStyle unlockedCellStyle = workbook.createCellStyle();
        unlockedCellStyle.setLocked(false);

        int rowid = 0;

        row = spreadsheet.createRow(rowid++);
        int cellid = 0;

        for (String st : XLS_HEADER.split(",")) {
            Cell cell = row.createCell(cellid++);
            cell.setCellValue(st);

        }
        CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);        

        DataValidationHelper dataValidationHelper = spreadsheet.getDataValidationHelper();
        DataValidationConstraint constraint = dataValidationHelper.createDateConstraint(OperatorType.BETWEEN,
                "25/10/2014", "30/10/2014", "dd/MM/yyyy");
        DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        spreadsheet.addValidationData(validation);

        FileOutputStream fileOut = new FileOutputStream("testing-file.xlsx");
        workbook.write(fileOut);
        fileOut.close();
        System.out.println("Your excel file has been generated!");
   }

If I do the same thing for HSSFWorkbook to create ".xls" file, it seems to work fine. It shows the alert pop up and don't let the user put the invalid value in the field.

Please help me here.

Upvotes: 1

Views: 1992

Answers (1)

paramvir
paramvir

Reputation: 404

Got the answer. turns out for each validation you have to explicitly set the alert box in XSSF. For my code example, it will be following:

validation.setShowErrorBox(true);

So for following validation

DataValidationHelper dataValidationHelper = spreadsheet.getDataValidationHelper();
DataValidationConstraint constraint = dataValidationHelper.createDateConstraint(OperatorType.BETWEEN,
                "25/10/2014", "30/10/2014", "dd/MM/yyyy");
DataValidation validation = dataValidationHelper.createValidation(constraint, addressList);
        spreadsheet.addValidationData(validation);

add the following

validation.setShowErrorBox(true);

before adding this validation to spreadsheet.

This is already enabled in HSSFWorkbook.

Upvotes: 2

Related Questions