Locking a whole column (.NET)

Aspose.Cells Product Family
1

Hi,

Trying to lock all cells in a column.

This peice of code doesnt seem to work. Do we need to unlock all cells first, before locking them again?

private void LockCells(Worksheet page1, int column)
{
Aspose.Cells.Style style;
StyleFlag flag = new StyleFlag() { Locked = true };

for (int i = 1; i < maxSpreadsheetRows ; i++)
{
style = page1.Cells[i, column].GetStyle();
style.IsLocked = true;

page1.Cells[i, column].SetStyle(style,flag);
}
}

2

Hi Dominic,


Thank you for contacting Aspose support.

If your requirement is to lock a complete column as titled then I would suggest you to use the alternative approach as demonstrated below that uses the instance of Column rather than Cell for locking. Please note, the code lock only first column, you may amend the code to work with more columns as per your requirements.

In case you still face any difficulty then please provide us your sample spreadsheet along with the column name that you wish to lock.

C#

// 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 flag;

// Get the first column style.
style = sheet.Cells.Columns[0].Style;

// Lock it.
style.IsLocked = true;

// Instantiate the flag.
flag = new StyleFlag();

// Set the lock setting.
flag.Locked = true;

// Apply the style to the first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);

// Protect the sheet.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save(“D:/lockedcolumn.xls”, SaveFormat.Excel97To2003);
3

Why is

sheet.Protect(ProtectionType.All);

required? Surely that locks the whole sheet, so there’s no point locking the column???

4

Hi Dominic,


You have to call the Worksheet.Protect method to lock the cells, rows and columns that has IsLocked property set to true for their Style objects. If you do not call the aforesaid statement nothing will be locked. However, you should first unlock (by setting the Style.IsLocked property to false) in order to apply the effect on selected items. Please check the updated code and it’s resultant spreadsheet (as attached) for your reference. You will notice only one column (A) is locked whereas the rest are editable. Please also check the detailed article on this subject.

C#

// 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 flag;

// 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;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}

// Get the first column style.
style = sheet.Cells.Columns[0].Style;

// Lock it.
style.IsLocked = true;

// Instantiate the flag.
flag = new StyleFlag();

// Set the lock setting.
flag.Locked = true;

// Apply the style to the first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);

// Protect the sheet.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save(“D:/lockedcolumn.xls”, SaveFormat.Excel97To2003);

5

I have attached a solution, where the first column does NOT get locked.

6

Your reply was a little quicker than my last reply.

I will try and unlock all cells first, then lock column 0.

Thanks for your help.

7

If you change the LockFirstColumn method of your application as follow, it should produce the desired results. Please check the attachment for the resultant spreadsheet in an archive.


C#

private void LockFirstColumn(Worksheet page1)
{
// Define the style object.
Aspose.Cells.Style style;

// Define the styleflag object.
StyleFlag flag;

// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
style = page1.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
page1.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}

// Get the first column style.
style = page1.Cells.Columns[0].Style;

// Lock it.
style.IsLocked = true;

// Instantiate the flag.
flag = new StyleFlag();

// Set the lock setting.
flag.Locked = true;

// Apply the style to the first column.
page1.Cells.Columns[0].ApplyStyle(style, flag);

page1.Protect(ProtectionType.All);
}
8

Hi,


I’m trying to achieve a slightly different goal than the original poster. In my scenario, some of the columns should be protected (locked) while others should be editable.

I tried using the provided code, which works well to lock columns but unfortunately locks all the sheet…

My project uses Aspose Cells version 8.3.2.1, but for the purpose of the unit test I upgraded to the latest version (NuGet package, 8.6.1.0).

Please look at the attached unit test and tell me what I do wrong…

Regards,
David.
9

Hi David,


Thank you for contacting Aspose support.

In order to allow editing for some of the cells (a range) you have to use the following approach. Please also check the attached input and output spreadsheets where the code is protecting the worksheet while allowing the range A1:A10 to be editable.

C#

var workbook = new Workbook(“D:/book1.xlsx”);
var style = workbook.CreateStyle();
style.IsLocked = false;
var worksheet = workbook.Worksheets[0];
var cells = worksheet.Cells;
var range = cells.CreateRange(“A1:A10”);
range.ApplyStyle(style, new StyleFlag() { Locked = true });
worksheet.Protect(ProtectionType.All);
workbook.Save(“D:/output.xlsx”);
10

Hi, thanks for your quick answer.


After reading and trying out your code, I understood a capital element : the StyleFlag parameter role is to apply a specific part of the Style parameter to the desired range, isn’t it ?

I think that the documentation is really not helpful and should be enhanced to highlight this important point…

Anyway, thank you !
I have been able to achieve what I desired thanks to your help.

11

Hi,


Good to know that your issue is sorted out now.

Please see the document that may help you to achieve your task for your reference:
http://www.aspose.com/docs/display/cellsnet/Protecting+Worksheets

And, yes, StyleFlag.Locked specifies for implementing the corresponding Style.IsLocked attribute accordingly.


Thank you.


12
When I lock a column, it seems that all columns cannot be resized anymore by the user.
I use this protection type:

sheet.Protect(ProtectionType.Contents);

Is there a way to allow the resizing of the (rest of the) columns when there is one locked column?


UPDATE:
I have found the answer here:
https://www.mrexcel.com/forum/excel-questions/53568-protect-lock-few-cells-but-allow-column-resize.html

You just use:

sheet.Protect(ProtectionType.Contents);
sheet.Protection.AllowFormattingColumn = true;


I leave the answer here, just to help others! ;)
Thanks.
13
Hi kostas,

Good to know that your got it right by adding the additional line of code which is correct:
i.e.,
sheet.Protection.AllowFormattingColumn = true;

Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.