Worksheet protection and individual cell locking in Excel using Aspose.Cells for Java

Aspose.Cells Product Family
1

Hello,

I am currently testing the Aspose.Cells for Java capabilities on data protection and locking. This is the code I am running:

public void run() throws IOException {

Workbook wb = new Workbook();

wb.getWorksheets().removeSheet(0);

Worksheet unprotectedSheet = wb.getWorksheets().addSheet(“UNPROTECTED”);

unprotectedSheet.getCells().getCell(“A1”).setValue(“A”);

unprotectedSheet.getCells().getCell(“A2”).setValue(“B”);

Worksheet protectedSheet = wb.getWorksheets().addSheet(“PROTECTED”);

Protection prot = new Protection();

prot.setPassword(“secret”);

protectedSheet.protect(prot);

// Doesn’t work without this

protectedSheet.getCells().getColumn(0).getStyle().setCellLocked(false );

protectedSheet.getCells().getCell(“A1”).getStyle().setCellLocked(false );

protectedSheet.getCells().getCell(“A1”).setValue(“UNLOCKED”);

protectedSheet.getCells().getCell(“A2”).getStyle().setCellLocked(true );

protectedSheet.getCells().getCell(“A2”).setValue(“LOCKED”);

wb.save(“test_result.xls”);

}

//--------//

When I open the resulting file in Excel, I am expecting to find 2 worksheets. One that is unprotected, one one that is protected. In the protected one, I am expecting that cell A1 is not locked, and cell A2 is locked.

The tested code works. But it as the side effect to unlock a lot of cells. The whole A colunm is unlocked, but the A2 cell.
If I remove the red line above, I expect to have the whole A column locked, but the A1 cell. I fact, the A1 cell is locked also, despite I set its style to unlocked. Is this a bug?

Would there be a way to unlock just one cell in a worksheet?

Regards,
Vincent

2

Hi Vincent,

Well, for your info, all the cells in a worksheet are initialized to be set with Locked on attributes by default.....right click on any cell in a simple worksheet and click Format Cells.... option, now click the last tab named "Protection" and you may see that "Locked" checkbox is checked by default. So, one should uncheck this locked checkbox first if he wants to set only a few cells/row/column in a worksheet. The process is simple one; you will unlock all the cells in the worksheet first, now lock your desired cells or row(s) or column(s), lastly, you will lock the worksheet and that's it.

May the following sample codes (which demonstrates below) help you for your requirements, kindly consult it.

Sample codes:

Lock only A1, A2 and A3 cells in the worksheet, the rest of the cells are not locked at all.

// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.getWorksheets().getSheet(0);

// Define the style object.
Style style;

// First Loop through all the columns in the worksheet and unlock them.
for(int i = 0; i <= 255; i ++)

{

style = sheet.getCells().getColumns().getColumn(i).getStyle();
style.setCellLocked(false);

}


// Lock the three cells...i.e. A1, B1, C1.
style = sheet.getCells().getCell("A1").getStyle();
style.setCellLocked(true);
style = sheet.getCells().getCell("B1").getStyle();
style.setCellLocked(true);
style = sheet.getCell("C1").getStyle();
style.setCellLocked(true);

// Finally, Protect the sheet now.
Protection protection = new Protection();
sheet.protect(protection);

// Save the excel file.
wb.save("d:\\test\\lockedcells.xls", FileFormatType.EXCELXP);

Lock the first Column (A Column), the rest of the columns/cells are not locked at all.

// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.getWorksheets().getSheet(0);

// Define the style object.
Style style;
// Define the styleflag object.
StyleFlag flag;

// First Loop through all the columns in the worksheet and unlock them.
for(int i = 0; i <= 255; i ++)

{

style = sheet.getCells().getColumns().getColumn(i).getStyle();
style.setCellLocked(false);
flag = new StyleFlag();
flag.setLocked(true);
sheet.getCells().getColumns().getColumn(i).applyStyle(style, flag);

}

// Get the first column style.
style = sheet.getCells().getColumns().getColumn(0).getStyle();
// Lock it.
style.setCellLocked(true);
// Instantiate the flag.
flag = new StyleFlag();
// Set the lock setting.
flag.setLocked(true);

// Apply the style to the first column.
sheet.getCells().getColumns().getColumn(0).applyStyle(style, flag);

// Protect the sheet.
Protection protection = new Protection();
sheet.protect(protection);

// Save the excel file.
wb.save("d:\\test\\lockedcolumn.xls", FileFormatType.EXCELXP);

For further reference, please check the following docs:

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/protecting-worksheets.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/advanced-protection-settings-since-excel-xp.html

http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/unprotect-a-worksheet.html

Hopfully, it will help you,

Thank you.

3

Thank you Amjad,



I have bookmarked this documentation.

Today, my need is not to lock a few cells in a sheet, but to UNLOCK a few cells.



This is easily feasable in Excel by setting the property on an
individual cell. But it looks like it is not possible in Aspose.Cells
since I have to unlock large groups of cells.



Regards,

Vincent

4

Hi Vincent,

Well, to unlock your desired cells, first you will unprotect the worksheet i.e.., worksheet.unprotect method, then, you may unlock those cells e.g..,

Style style1 = sheet.getCells().getCell("B1").getStyle();

style1.setCellLocked(false);

and that's it. I think this procedure is same with MS Excel, is not it?

Check the doc: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/unprotect-a-worksheet.html

Thank you.

5

Hello Amjad,

Thank you for the suggestion.

Now I can’t understand the interest of unprotecting thr worksheet. This will lead to all the cells being unprotected, no matter if they are locked or not. Am I wrong ?

What I need is to be able to use Aspose.Cells to create a workbook, containing a protected worksheet where all cells but the A1 are locked. Meaning, when the workbook is open in Excel, one will be able to edit the A1 cell, but no other.

If it is feasible, would it be possible for you to show me how ?
If not, I guess it is a missing feature or a bug.

As an example, please find attached an xls file like I would like it to be. Easily done with Excel by unlocking cell A1 (right click, format cell, …) and protecting the worksheet (Tools, protection, …).

Thank you for the support.
Vincent

6

Hi Vincent,

Thanks for providing us further details with template file,

We understand you need now. And yes it is possible with Aspose.Cells for Java.

Here is the sample code for your requirement.

Sample code:

// Create a new workbook.
Workbook wb = new Workbook();
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.getWorksheets().getSheet(0);
// Define the style object.
Style style;
// Lock all the cells but A1 cell.
style = sheet.getCells().getCell("A1").getStyle();
style.setCellLocked(false);
sheet.getCell("A1").setStyle(style);
Protection protection = new Protection();
sheet.protect(protection);
 // Save the excel file.
wb.save("e:\\files\\onecellunlocked.xls");

Thank you.

7

Thank you Amjad,

Your code sample answers my need.
Now I know I have to reinject the Style into the Cell for my modifications to be taken into account. It is weird, though, that it seems it is not always needed (see my first example).

Best regards
Vincent