Apache POI Excel Sheet Protection and Data Validation

Question

I am using Apache POI to generate Excel File (.xlsx format)

I got stuck with two things.

First, I want to disable few features like format cells in the generated excel file.

I have used to following code to achieve it (got it from Apache POI - How to protect sheet with options?.

sheet.lockDeleteColumns();
sheet.lockDeleteRows();
sheet.lockFormatCells();
sheet.lockFormatColumns();
sheet.lockFormatRows();
sheet.lockInsertColumns();
sheet.lockInsertRows();
sheet.getCTWorksheet().getSheetProtection().setPassword(pwdBytes);
sheet.enableLocking();
workbook.lockStructure();

But this code making the generated sheet as read only. I am not able to enter any data in the generated excel sheet.

Please tell me how to disable only few features and enable the read and write at the same time.

Second, there is a date column in Excel file. i want to apply some date validation (something like isValidDate) at column level.

I have tried XSSFDataValidationHelper createCustomConstraint, but no success, validation is not working for date column. when i open the generated excel file i am getting below error

Excel found unreadable content in file.

I guess something is wrong in the function(createCustomConstraint("ISNUMBER()");.

Below is code snippet i have used.

XSSFDataValidationConstraint dateConstraint = (XSSFDataValidationConstraint) dvHelper
                .createCustomConstraint("ISNUMBER()");
        CellRangeAddressList dateAddressList = new CellRangeAddressList(0,
                10000, 2, 3);//1000rows and 3rd & 4th column
        DataValidation dateValidation = dvHelper.createValidation(
                dateConstraint, dateAddressList);
        dateValidation.setEmptyCellAllowed(true);
        dateValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dateValidation.createErrorBox("Error", "InValid Date.");
        dateValidation.setShowErrorBox(true);
        sheet.addValidationData(dateValidation);

Please help me to solve these two issues.

Thanks in Advance.

Solution

I believe your question has two parts.

First part:- I believe locking and protecting are two different things.

sheet.protectSheet(String password);

will make the sheet read-only. To set the lock options, calling functions such as lockFormatCells() and lockDeleteColumns() along with sheet.enableLocking() is enough.

Second part:- If you want to apply DateValidation, you should call something like this.

DataValidationConstraint activationDateConstraint = validationHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "=VLOOKUP($D3,Offices!$B$2:$D$6,3,FALSE)", "=TODAY()", "dd/mm/yy");

Comments

[Ahmad Nadeem]

sheet.enableLocking(); and sheet.protectSheet(String password); both are same, i think password is the only difference where as lockFormatCells(); and lockDeleteColumns(); are already depricated.