Restrict (Lock/Unlock) excel operations in JAVA using POI

There was a requirement in which user should be able to download an excel report from application, make some amendments and upload the excel again into application.

However there were a few validations we wanted to apply on amended excel file

• User should be able to edit only two columns out of 34 columns, rest all columns should be uneditable
• User should not be able to add new column
• User should not be able to add new row
• User should not be able to change / alter cell format for any of the cells in excel file

In order to achieve this, we thought of two options

1. Validate the file - For number of rows, number of columns, cell types, etc
2. Restrict the user for editing the information in downloaded excel file

At first, we thought #2 would be very difficult ti implement using java application and #1 would have been easy. However there were too many limitations and performance issues with #1- I mean you need to validate each and every cell in an excel file and throw an error if user has by mistake modified something which is not expected. Also resulting in an error would also cause user trouble to correct the file and re-upload. This also involves a lot of error handling and correct error message information maintenance to be provided to the user.

So we thought to go with #2.
After a quick search, we found very good APIs provided by POI which will very easily let you restrict user for editing the excel file.
Below is code snippet to restrict the user

package test1;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelLocks {
    public static void main(String[] args) {
        try {
            String filename = "C:/RestrictedExcel.xlsx";
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.createSheet("FirstSheet");

            // Restrict deleting columns
            sheet.lockDeleteColumns();
            // Restrict deleting rows
            sheet.lockDeleteRows();
            // Restrict formatting cells
            sheet.lockFormatCells();
            // Restrict formatting columns
            sheet.lockFormatColumns();
            // Restrict formatting rows
            sheet.lockFormatRows();
            // Restrict inserting columns
            sheet.lockInsertColumns();
            // Restrict inserting rows
            sheet.lockInsertRows();
            // Lock the sheet
            sheet.enableLocking();
            // Set width for the column
            // Set the width (in units of 1/256th of a character width)
            sheet.setColumnWidth(2, 5000);
            // Hide F column in sheet
            sheet.setColumnHidden(5, true);
            // Set green colour for the sheet
            sheet.setTabColor(3);

            // Define unlock style for the cells to be unlocked
            CellStyle styleForUnLocking = workbook.createCellStyle();
            styleForUnLocking.setLocked(false);
            workbook.lockStructure();

            XSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell(0).setCellValue("Col1");
            rowhead.createCell(1).setCellValue("Col2");
            rowhead.createCell(2).setCellValue("Col3");
            rowhead.createCell(3).setCellValue("Col4");

            for (int i = 1; i < 25; i++) {
                for (int j = 0; j < 4; j++) {
                    XSSFRow row = sheet.createRow((short) i);
                    Cell myCell1 = row.createCell(j++);
                    myCell1.setCellValue("row" + i + j);
                    Cell myCell2 = row.createCell(j++);
                    myCell2.setCellValue("row" + i + j);
                    Cell myCell3 = row.createCell(j++);
                    myCell3.setCellValue("row" + i + j);
                    // Keep this cell unlocked
                    myCell3.setCellStyle(styleForUnLocking);
                    Cell myCell4 = row.createCell(j++);
                    myCell4.setCellValue("row" + i + j);
                }
            }

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

        } catch (Exception ex) {
            System.out.println(ex);

        }
    }
}