javaexcelapache-poipassword-protectionxssf

Apache POI lock the cell but allow column resize


I create an Excel file through Apache POI XSSF and I lock the sheet with a password so user can't change the value of the first two row and first five columns (I lock the sheet and allowed editing of other cells). All work fine, the only problem is that the user can't resize the column so he can neither change nor resize the columns to read all the cells value. Is it possible to allow column resize even if the sheet is protected? Thi is my configuration

workbook = new XSSFWorkbook();
sheet = workbook.createSheet("Sheet1");
sheet.protectSheet("passwordExcel"); 
unlockedNumericStyle = workbook.createCellStyle(); 
unlockedNumericStyle.setLocked(false);
// Format cell for date
dateStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
sheet.autoSizeColumn(1);

I read about lockFormatCell() but I don't understand if it can help me. Thanks


Solution

  • To be able resizing the column size while sheet is protected, you will need setting XSSFSheet.lockFormatColumns to false.

    Complete example:

    import java.io.FileOutputStream;
    
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.*;
    
    public class CreateExcelXSSFProtectedSheet {
    
     public static void main(String[] args) throws Exception {
    
      Workbook workbook = new XSSFWorkbook();
    
      CreationHelper createHelper = workbook.getCreationHelper();
    
      CellStyle unlockedNumericStyle = workbook.createCellStyle();
      unlockedNumericStyle.setDataFormat(createHelper.createDataFormat().getFormat("$#,##0.00_);[Red]($#,##0.00)"));
      unlockedNumericStyle.setLocked(false);
    
      CellStyle dateStyle = workbook.createCellStyle();
      dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
    
      Sheet sheet = workbook.createSheet();
    
      Row row = sheet.createRow(0);
      Cell cell = row.createCell(1);
      cell.setCellValue("some data");
    
      row = sheet.createRow(1);
      cell = row.createCell(1);
      cell.setCellValue(-123456789.0123456);
      cell.setCellStyle(unlockedNumericStyle);
    
      row = sheet.createRow(2);
      cell = row.createCell(1);
      cell.setCellValue(new java.util.Date());
      cell.setCellStyle(dateStyle);
    
      ((XSSFSheet)sheet).lockFormatColumns(false);
    
      sheet.protectSheet("passwordExcel"); 
     
      sheet.autoSizeColumn(1);
    
      FileOutputStream out = new FileOutputStream("CreateExcelXSSFProtectedSheet.xlsx");
      workbook.write(out);
      out.close();
      workbook.close();
    
     }
    
    }