How to lock columns in Excel sheet

Aspose.Cells Product Family
1

Hi,

We want to make some columns read only (user cannot enter anything in that column) based on the conditional values in one excel work sheet.

Is there any method that can help us for this?

Our application environment is: Visual Studio 2010,

Server: Windows 2008

For Example:

Case1. : Column value1 and 2 need to be read only.

Value1 Value2

Case-2 If flagValue=True, Value1=ReadOnly

Value1 Value2
0
0
2

Hi,


I think you may make use of Protection options provided by Aspose.Cells APIs. For example, you may protect your desired columns, rows or even cells/range in the sheet for your requirements. Please see the topic for your complete reference:
Protecting Worksheets

Thank you.

3

Hi,

I have tried the code given in Protecting Worksheets still I am able to edit the values in the column.

I am using Aspose.Cells v 8.9.0.0.

Following is the code I have tried:

Style style;

StyleFlag flag;

for (int i = 0; i <= 255; i++)

{

style = workSheet.Cells.Columns[(byte)i].Style;

style.IsLocked = false;

flag = new StyleFlag();

flag.Locked = true;

workSheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);

}

workSheet.Cells.HideColumn(1);

style = workSheet.Cells.Columns[1].Style; 

style.IsLocked = true; 

flag = new StyleFlag();

flag.Locked = true;

workSheet.Cells.Columns[1].ApplyStyle(style, flag); //Not working

workSheet.Cells.ApplyColumnStyle(0, style, flag); //Not working

Here both the ApplyStyle() and ApplyColumnStyle() functions are not working.

Please help.

4

Hi Nishanth,

I have tested the following sample code with our latest version/fix: Aspose.Cells for .NET (Latest Version), it works fine and the output file (attached) is fine tuned. When you open the file into MS Excel, you will see the the first column is protected and you cannot add/edit any value in the first columns (A) cells:

e.g

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 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(“e:\test2\out1.xls”, SaveFormat.Excel97To2003);

By the way, I can see that you are missing the following line of code in your code segment:

// Protect the sheet.

workSheet.Protect(ProtectionType.All);

Thank you.

Save Edit