0

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

luca
  • 3,248
  • 10
  • 66
  • 145
  • I don't think Excel allows this. If Excel doesn't allow a feature, you won't be able to do it with POI. If I misunderstood, and you can get the behavior you want with Excel, please tell us the steps you take in Excel to get the behavior you are looking for. – jmarkmurphy Feb 14 '18 at 15:38

1 Answers1

5

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();

 }

}
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • I know this is quite old comment, I have the exact requirement, I used your code block but lockFormatColumns(false) did not work, even with this configuration I cannot resize the column width, Any idea ? – Rajith K Jul 11 '22 at 06:07
  • @Rajith K: For me this works. The sheet is protected but formatting columns, including column width, is possible. What Excel version are you using? – Axel Richter Jul 11 '22 at 08:42
  • Worked fine for both Excel online from onedrive and windows !!! Thank you very much for your support, seems like libreoffice dosen't support this somehow. – Rajith K Jul 11 '22 at 13:43
  • 1
    @Rajith K: LibreOffice is not able to allow column formatting when sheet is protected. It only can allow to: Select protected cells, Select unprotected cells, Insert columns, Insert rows, Delete columns and Delete rows. See https://help.libreoffice.org/7.3/en-US/text/scalc/01/06060100.html. – Axel Richter Jul 11 '22 at 14:42