CODEWITHSUNDEEP
javaexcelapache-poipassword-protectionxssf
luca
luca

Reputation: 3318

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

Upvotes: 0

Views: 3016

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

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

 }

}

Upvotes: 5

Related Questions