Cell protection

  1. Excel gets generated from data table. The excel file has A to N Column. The column A to K should be protected. Column L to N should not be protected. This is done by using aspose.cells.

    2. J column has some values. If the value in J is 20000 then that row should be protected till the column L. Through aspose.cells how to do this in addition to the point 1.

    I tried but could not protect that.

Hi Harish,


Thank you for contacting Aspose support.

Please check the following piece of code that accomplishes both of your requirements. Please also check the detailed articles on Protecting Worksheets & Find/Search Data as both concepts are used in the following snippet. I have also attached the source & resultant spreadsheets here for your reference.

C#

var book = new Workbook(dir + “book1.xlsx”);
//Get first worksheet in collection
var sheet = book.Worksheets[0];
//Create a Style
var style = book.CreateStyle();

//Loop over all columns and unlock them before locking the desired ones
for (int i = 0; i <= 255; i++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, new StyleFlag() { Locked = true});
}

//Create a range spanning over columns A to K
//MaxDataRow returns the last row index that contains data
var range = sheet.Cells.CreateRange(0, 0, sheet.Cells.MaxDataRow + 1, CellsHelper.ColumnNameToIndex(“K”) + 1);

//Set Locked property to true and set style to range
style.IsLocked = true;
range.ApplyStyle(style, new StyleFlag() { Locked = true });


//Search for particular string and lock corresponding row
//Instantiate FindOptions Object
FindOptions findOptions = new FindOptions();
//Create a Cells Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.StartColumn = CellsHelper.ColumnNameToIndex(“J”);
ca.EndRow = sheet.Cells.MaxDataRow;
ca.EndColumn = CellsHelper.ColumnNameToIndex(“J”);

//Set cells area for find options
findOptions.SetRange(ca);

//Set where & how to look for specified value
findOptions.LookInType = LookInType.Values;
findOptions.LookAtType = LookAtType.EntireContent;

Cell cell = null;
do
{
//Find the cell with specified value
cell = sheet.Cells.Find(“Some Text”, cell, findOptions);
if (cell != null)
{
//Lock row
sheet.Cells.Rows[cell.Row].ApplyStyle(style, new StyleFlag() { Locked = true });
}
}
while (cell != null);

//Protect the sheet
sheet.Protect(ProtectionType.Contents);
book.Save(dir + “output.xlsx”);

In the above excel which you shared, if the Column J has value of 10, 100, 1000, 100000 etc… only the cell having 1000 and with the corresponding row has to get protected till the column A to L. If the cell having other than 1000, then that row should get protected from A to K, Will the above code performs this functionality?

THANKS IN ADVANCE

Hi again,


Please note, the provided solution is generic in order to demonstrate the usage of Aspose.Cells APIs to protect the worksheet for a range of cells and specific row based on search results. You should amend the code to accommodate your exact requirements.