Lock individual cells

Aspose.Cells Product Family
1

Hi,

I am having problems with the Aspose.Excel component. I wish to lock the entire worksheet except for a few cells. Although when i use the following syntax:

First out i call the following line of code to lock down the entire worksheet:
objExcel.Worksheets[0].Protect(ProtectionType.All);

Then i apply the following style object settings - plus data
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_BudgetUser].Style = objStyleAmountColumnDetail;
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_BudgetUser].Style.IsLocked = false;
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_CommentUser].Style = objStyleTextColumnDetail;
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_CommentUser].Style.IsLocked = false;

if (objDataRow[“comment_101”] != DBNull.Value)
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_CommentAdmin].PutValue(objDataRow[“comment_101”].ToString());

if (objDataRow[“comment_901”] != DBNull.Value)
objExcel.Worksheets[0].Cells[intRow, ExcelSettings.ColumnNo_CommentUser].PutValue(objDataRow[“comment_901”].ToString());

And open the workseet to Excel (from ASP.NET), the entire worksheet is still unlocked, nothing has been applied…

Could you please help me on how to lock down the entire worksheet and only keep a few cells unlocked?

Sincerely,
/Hans Sixh?j
Systems developer, MCSD
Visma Consulting AB
Sweden

2

Hi Hans,

If you protect the worksheet, cells are locked to end users, not Aspose.Excel. Please check

for more information.

3

Hi,

Thanks for your quick response.

My english must have slipped, so there was a misunderstanding (too much working :-))

I wish to lock cells in the end user Excel sheet. Although when setting the property IsLocked = true for some of the cells, these cells are not locked.

Is there any way for me to have say 1 cell locked and the rest of the worksheet unlocked?

Sincerely
/Hans

4

Dear Hans,

Please try the following code:

Excel excel = new Excel();
excel.Worksheets[0].Cells["A1"].PutValue("hello");
Style style = excel.DefaultStyle;
style.IsLocked = false;
excel.DefaultStyle = style;
excel.Worksheets[0].Cells["A1"].Style.IsLocked = true;

excel.Worksheets[0].Protect(ProtectionType.All);

excel.Save("d:\\test\\abc.xls");

You can see that A1 is locked and all other cells are unlocked.

5

Hi!

Its working now.

Thanks for your excellent support!
/Hans

6

@sixhoj,
Aspose.Excel is replaced by Aspose.Cells that is a much advanced and feature-rich product. You can protect the workbook as a whole or protect the individual worksheets. It allows to protect/lock individual cells also demonstrated in the following sample code:

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

// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];

// Define the style object.
Style style;

// Define the styleflag object
StyleFlag styleflag;

// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{

    style = sheet.Cells.Columns[(byte)i].Style;
    style.IsLocked = false;
    styleflag = new StyleFlag();
    styleflag.Locked = true;
    sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);

}

// Lock the three cells...i.e. A1, B1, C1.
style = sheet.Cells["A1"].GetStyle();
style.IsLocked = true;
sheet.Cells["A1"].SetStyle(style);
style = sheet.Cells["B1"].GetStyle();
style.IsLocked = true;
sheet.Cells["B1"].SetStyle(style);
style = sheet.Cells["C1"].GetStyle();
style.IsLocked = true;
sheet.Cells["C1"].SetStyle(style);

// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save("output.xls", SaveFormat.Excel97To2003);

Refer to the following article for more information about protecting worksheets and cells:
Protecting Worksheets

Download the latest free trial version for testing:
Aspose.Cells for .NET (Latest Version)

A comprehensive solution is available here that can be used to test different features of this product.