Is it possible to unlock specific cells in a protected sheet (Using Aspose)

Question

I'm using Aspose-Cells and java to export excel templates in my system.

In this particular situation I'm generating a spreadsheet where I have two sheets that I want to protect. In one of them, I need to let the user edit only 4 cells. All the rest should be protected. The simplest implementation should be:

protect the sheet
unlock each cell I want to let the user edit.

The problem is that I was searching to check if it's possible to do this (protect the entire sheet and unlock only a few cells) and it seems to not to be possible. Please.. tell me I'm wrong and there's a way to do this, otherwise I'll have to lock all the existing cells in the sheet and unlock only 4 of them.. For my experience using another library (PHPExcel), it seems to be very costly in terms of performance (I had to apply it for 1000 rows and more then 40 columns, so it was really costly).

I havent heard of aspose-cells myself and so I dont know if I am reading the right documentation but here: http://www.aspose.com/docs/display/cellsjava/Style there is a `setLocked(booleanvalue)` method... and yes I assume pnuts is correct because `Excel` defaults to all cells being locked — chancea, Aug 01 '13 at 14:54
@chancea isn't it only for applying styles into cells? I mean.. I'm not pretty sure, but it seems to lock/unlock editing styles in a cell. I'm going to check it anyway thx.. — periback2, Aug 01 '13 at 16:01

score 2 · Accepted Answer · answered Aug 05 '13 at 07:13

It can be done easily with Aspose.Cells for Java. You can

First lock all the columns (all cells) in a worksheet
Unlock specific cells or range of cells

See the sample below.

String dataDir = "D:\\data\\";
// Create or load workbook
Workbook book = new Workbook();

// Get the first worksheet
Worksheet sheet = book.getWorksheets().get(0);

Style style;
StyleFlag flag = new StyleFlag();

// First lock all columns
for (int iCol=0 ; iCol<255 ; iCol++)
{
    // Get style of the column
    style = sheet.getCells().getColumns().get(iCol).getStyle();
    // Apply locking to the style
    style.setLocked(true);
    flag.setLocked(true);
    sheet.getCells().getColumns().get(iCol).applyStyle(style, flag);
}

// Get the range of cells, which we want to unlock
Range rangeUnlocked = sheet.getCells().createRange("A1:D4");
// Add a new style
int styleIndex = book.getStyles().add();
Style styleUnlocked = book.getStyles().get(styleIndex);
// Unlock cells
styleUnlocked.setLocked(false);
rangeUnlocked.setStyle(styleUnlocked);

// Protect the sheet
sheet.protect(ProtectionType.ALL);

//Save the Excel file
book.save(dataDir + "protectedrange.xlsx");

I work at Aspose as a Developer Evangelist.

Is it possible to unlock specific cells in a protected sheet (Using Aspose)

1 Answers1