How to invalidate writing to a protected cell

Aspose.Cells Product Family
1

Hello,

We would like to prevent any Write operation to a range within a protected worksheet containing a cell whose Locked property is set to true. In such cases, the Excel application prevents users editing the locked cells, and, if we try to write to them from VBA using the Excel Range object, we get an exception, which helps us to know that we attempted a Write to a cell we should not edit.

In Aspose, however, the Write attempt is successful, and no exception is thrown, meaning that we have no way of knowing that a protected cell was inadvertently modified. This is the sample code we used to verify this:

private static void TestAsposeWriteToProtectedCells()
{
var wb = new ASP.Workbook(@“C:\temp\Aspose protected sheet example.xlsx”);
var ws = wb.Worksheets[0];
<span style=“font-family: “Courier New”; font-size: small;”> ws.Cells[4, 3].Value = 200;
<span style=“font-family: “Courier New”; font-size: small;”> wb.Save(wb.FileName);
}

The file opened in the code (attached here) has a value of 11 in the protected sheet’s D4 cell. After running the code and opening the file, we see 200 instead of 11 in cell D4.

Is there a way to make Aspose throw an exception in these cases, or is there a fast way of determining if a range within a protected sheet contains locked cells without having to poll each cell individually? In the case of a single cell, this might not be much of an issue, but we want to be able to write entire tables of data in one Write operation, which would require polling each cell individually before attempting the Write.

Thanks for your help.
2

Hi,


Thanks for your posting and using Aspose.Cells.

Please use the Cell.GetStyle().IsLocked property to check if the cell is locked or not. If it is not locked then put some value inside it otherwise do not put any value. It should resolve your issue. Let us know your feedback.

Please see the following sample code for your reference.

C#
//Load your excel file
var wb = new Workbook(“s1.xlsx”);

//Access first worksheet
var ws = wb.Worksheets[0];

//Access your cell
Cell cell = ws.Cells[4, 3];

//Check the IsLocked property of the cell before writing
Style st = cell.GetStyle();
bool isLocked = st.IsLocked;

//If IsLocked false then write otherwise do not write
if (isLocked == false)
cell.PutValue(14);

//Save the output excel file on disk
wb.Save(“output.xlsx”);

3

Thanks for your reply, but our question was not so much with how to check the Locked property. It was more with how to do that without having to poll the Locked property of every cell, especially when writing a large table to a large range (in a single command).


Within Excel, we have the benefit of an exception being thrown when we attempt the write, but Aspose does not throw such an exception. The advantage of the exception is that we can simply attempt the write and react with a corrective measure only if an exception is thrown. The exception protects us from inadvertently altering cells that the user has tagged as Locked within a protected sheet.

Our conclusions from your reply are the following:
  • By design, Aspose allows code to alter the values of protected cells that are not be editable from Excel proper, VBA code, or an add-in, and you do not plan to modify this behaviour in future versions.
  • There is no quick way of determining whether one or more cells are locked within a large range without looping through all the cells.
If our conclusions are incorrect, please let us know.

Thanks.
4

Hi,


Thanks for your posting and using Aspose.Cells.

We have logged your requirement in our database for investigation. We will look into it and implement it if possible. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

  • CELLSNET-44800 - Throw some exception while writing to protected cell

5
Hi,

Thanks for using Aspose.Cells.

We do not support this feature for performance. And there is no quick way of determining whether one or more cells are locked within a large range without looping through all the cells because the lock property is the part of the cell's style.

6

Ok, we shall manually enforce the write protection of protected cells, then. Thx.

7

Hi,


Thanks for understanding the limitation. Hopefully, you will be able to sort out this with your own way. Let us know if you encounter any other issue, we will be glad to look into it and help you. Have a good day.