2

I am trying to lock the data within an Excel Worksheet so that it is not possible to edit the data already written to the Worksheet, but leaving the rest of the empty spaces free for editing/ adding more data.

I have tried going through the whole sheet trying to set the cell style, using the code provided below but it is only the relevant code, but it doesn't work, which agrees to this question already asked

Lock single column in Excel using Apache POI

XSSFCellStyle lockCell = getFileWriter().getWorkbook().createCellStyle();
lockCell.setLocked(true);
for(Row row : sheet){
    for(Cell mycell : row){
        mycell.setCellStyle(lockCell);
    }
}

the opposite: by locking the whole sheet and setting the relevant rows' cell style to unlock, I have tried that but the cells without any data don't unlock and so it hasn't worked for me. In any case how far and wide should one unlock the cells as it is not known how much space is needed for the unknown amount of data to be added.

Any help or suggestions would be greatly appreciated.

Community
  • 1
  • 1
user3877109
  • 25
  • 1
  • 4
  • can you post the code which produces the problem? the opposite way, lock everything and unlock some cells is working as much as i know. – Christian Aug 10 '14 at 16:36
  • yes it does work, it was my mistake i am trying to unlock the cells after the area where there is data, which i think are not accessible until i create a new row and unlock them after? – user3877109 Aug 10 '14 at 17:13

2 Answers2

1

The iterator for a row will return no cells if the cells have not been explicitly created before. To unlock a cell it must be set explicitly to an unlocked style. Changing the default is not possible to my knowledge. So the only way i see is to create cells for every row up to a certain column index.

Christian
  • 3,551
  • 1
  • 28
  • 24
  • Why not change the default style? – Gagravarr Aug 10 '14 at 17:34
  • how would i create cells for every row upto a certain column index? do i use the creatRow(int n) method and createCell(int m) till the certain column index? – user3877109 Aug 10 '14 at 17:34
  • Sheet.createRow(i) for creating rows and Row.createCell(i) for creating cells. Create them only if getRow(i)/getCell(i) returns null, otherwise it was already created. – Christian Aug 10 '14 at 17:40
0

To unlock cells with no data, suposing that your sheet is protected, you must set the default style of the columns you want to be unlocked, using the setDefaultColumnStyle method.

In your case you must do something like:

CellStyle editableStyle = workbook.createCellStyle();
editableStyle.setLocked(false);

for (int i = 0; i < numColumns; i++) {
   sheet.setDefaultColumnStyle(i, editableStyle);
}
Italo Borssatto
  • 15,044
  • 7
  • 62
  • 88