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:

1. protect the sheet
2. 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).

Answer 1

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

1. First lock all the columns (all cells) in a worksheet
2. 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.