Use formulas on a worksheet with a protected read only range using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
,
1

I am trying to have a worksheet with a read only range whilst the rest of the sheet is editable. I can get this by using the following code

Style readOnlyStyle = workBook.Styles[AsposeCellsConsts._READ_ONLY_RANGE_STYLE]

// create a new style if one does not already exist in this work book

if (readOnlyStyle == null)

{

readOnlyStyle = workBook.Styles[workBook.Styles.Add()] ;

readOnlyStyle.IsLocked = true ;

readOnlyStyle.Name = AsposeCellsConsts._READ_ONLY_RANGE_STYLE ;

}

StyleFlag readOnlyStyleFlag = new StyleFlag();

readOnlyStyleFlag.Locked = true;

range.ApplyStyle(workBook.Styles[AsposeCellsConsts._READ_ONLY_RANGE_STYLE], readOnlyStyleFlag) ;

workSheet.Protection.AllowEditingContent = false;

workSheet.Protection.AllowSelectingLockedCell = false;

workSheet.Protection.AllowSelectingUnlockedCell = true;

workSheet.Protection.Password = userPassword ;

If I use the above code then I have a worksheet with range read only and I cannot select the range which is what I want. I can edit the other cells of the worksheet which is alos what I want. However, the formulas do not work. I cannot sum cells either using the autosum function or maunally entering formula in the formula bar. If howver, I set the AlloweditingContent to true thrn that appears to override allowselectinglockedcell because I can now edit any fiel on the sheet including the locked cells and the formuals are now available.

In sumamry I want to be able to have the formulas available on the unlocked cells and the locked cells not to be selectable.

2

Hi,


For your code snippet and description, e.g.
“workSheet.Protection.AllowEditingContent = false;

workSheet.Protection.AllowSelectingLockedCell = false;

workSheet.Protection.AllowSelectingUnlockedCell = true;

workSheet.Protection.Password = userPassword ;

However, the formulas do not work. I cannot sum cells either using the autosum function or maunally entering formula in the formula bar. If howver, I set the AlloweditingContent to true thrn that appears to override allowselectinglockedcell because I can now edit any fiel on the sheet including the locked cells and the formuals are now available”

Sure, if you allow editing allowed, the worksheet will not be protected any more. When you lock some cells and disallow editing cells (normal cells / formula cells), how could simply use/see or manage locked cells formulas. For confirmation, please do your steps in MS Excel manually and you can see the similar results as provided by Aspose.Cells APIs. We have to follow MS Excel standards and cannot go against it.

I recommend you to kindly check the topic thoroughly for your complete reference:
http://docs.aspose.com/display/cellsnet/Protecting+Worksheets

If you still could not evaluate, please create a sample Excel file manually in MS Excel with your desired protection for your range of cells, save the file and post it here, we will check how to do it with Aspose.Cells for .NET APIs.

Thank you.