How to insert formula in the few cells and lock it down?

Aspose.Cells Product Family
1

Dear Team,

I have requirement where we need to export data into excel along with formula for few cells and lock it down. Which properties do I need to set for them ?

I am using Aspose.Cell for .NET dll.

Thanks in advance,
Neel

2

@neel71183

Thanks for using Aspose APIs.

We will look into this issue and help you asap.

3

Thanks Shakeel for your acknowledgement. Just to add, I am using the version 16.12.0.0 for the same.

4

@neel71183

Thanks for using Aspose APIs.

Please see the following document for your reference.

Protecting Worksheets|Documentation

Please also try the following code. It unlocks cells B5, C5 and D5 where D5 contains formula. If you double click any cell other than these three cells, Microsoft Excel will prompt a warning message that cells are protected.

Please see the output Excel file generated by the code for your reference.

Download Link:
Output Excel File.zip (5.8 KB)

C#

//Create workbook
Workbook wb = new Workbook();

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

//Put some values in cells
ws.Cells["B5"].PutValue(300);
ws.Cells["C5"].PutValue(500);
ws.Cells["D5"].Formula = "=Sum(B5,C5)";

//Calculate workbook
wb.CalculateFormula();

//Unlock the cells B5, C5, D5
Cell c = ws.Cells["B5"];
Style s = c.GetStyle();
s.IsLocked = false;

//Unlocking
ws.Cells["B5"].SetStyle(s);
ws.Cells["C5"].SetStyle(s);
ws.Cells["D5"].SetStyle(s);

//Set protection that we allow selecting unlocked cells only
ws.Protection.AllowSelectingUnlockedCell = true;

//Protect the worksheet
ws.Protect(ProtectionType.All);

//Save the output Excel file
wb.Save("output.xlsx");
5

Thanks Shakeel :slight_smile: