Locking a single row or column?

Aspose.Cells Product Family
1

I need to lock a single row and column on my sheet as well as not allow the user to delete or add a row. I tried using some sample posts that use protect but that protects the whole sheet and I can’t type in numbers anywhere in the sheet. Please advise how to handle this.

2

Hi;

Thank you for considering Aspose.

Well, you may use Style.IsLocked property for Rows,Columns and Cells to Lock them. Still you will need to enable the worksheet protection as well because MS Excel only allow protection on Rows,Columns & Cells, if the worksheet protection is enabled. Please see the following code,
Sample Code:

int index = workbook.Styles.Add();

Style stl = workbook.Styles[index];

stl.IsLocked = true;

StyleFlag stf = new StyleFlag();

stf.Locked = true;

worksheet.Cells.Rows[0].ApplyStyle(stl,stf);

worksheet.Cells.Columns[0].ApplyStyle(stl, stf);

worksheet.Cells[5, 5].Style.IsLocked=true;

worksheet.Protect(ProtectionType.All);

Thank You & Best Regards,

3

Hi,

For your info, all the cells in the worksheet are write protected by default i.e.., In MS Excel (97-2003), select any cell in a worksheet, click menu option Format|Cells...| and now click the Protection tab in the Format Cells dialog box, you will see the Locked checkbox is checked by default. So, you need to uncheck this checkbox first, lock the cells in your desired row and column and finally protect the sheet. May the following sample code help you to implement your desired task, please check it for your reference.

Note: In the above post (Nausherwan's post), he just described the general procedure of the protection, so you may ignore it if it is not very helpful to you.

Sample code:

Workbook wb = new Workbook();
// If you want to open the existing file, use this line, otherwise comment it.
//wb.Open("d:\\test\\testlock.xls");
Worksheet sheet = wb.Worksheets[0];
Style style;
StyleFlag flag;
// unlock all the column first.
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);
}

// Now lock first column
style = sheet.Cells.Columns[0].Style;
style.IsLocked = true;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.ApplyColumnStyle(0,style, flag);

// Now lock first row
style = sheet.Cells.Rows[0].Style;
style.IsLocked = true;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Rows[0].ApplyStyle(style, flag);

// Protect the sheet.
sheet.Protect(ProtectionType.All);
// Try some Advanced Protection options.
// Disable the deletion and insertion of rows.
// By default these settings are false also.
sheet.Protection.IsDeletingRowsAllowed = false;
sheet.Protection.IsInsertingRowsAllowed = false;

// Save the file.
wb.Save("f:\\test\\test_lockrowcolumn.xls");

Feel free to contact us anytime if you need further asisstance.

Thank you.

4

Hi,

I have also provided the parallel VB.NET code for your reference, check it below

Sample code:

Dim wb As New Workbook()
' If you want to open the existing file, use @this line, otherwise comment it.
'wb.Open("d:\\test\\testlock.xls")
Dim sheet As Aspose.Cells.Worksheet = wb.Worksheets(0)
Dim style As Aspose.Cells.Style
Dim flag As StyleFlag
Dim i As Integer
' unlock all the column first.
For i = 0 To 255
style = sheet.Cells.Columns(i).Style
style.IsLocked = False
flag = New StyleFlag()
flag.Locked = True
sheet.Cells.Columns(i).ApplyStyle(style, flag)
Next i

' Now lock first column
style = sheet.Cells.Columns(0).Style
style.IsLocked = True
flag = New StyleFlag()
flag.Locked = True
sheet.Cells.ApplyColumnStyle(0, style, flag)

' Now lock first row
style = sheet.Cells.Rows(0).Style
style.IsLocked = True
flag = New StyleFlag()
flag.Locked = True
sheet.Cells.Rows(0).ApplyStyle(style, flag)

' Protect the sheet.
sheet.Protect(ProtectionType.All)
' Try some Advanced Protection options.
' Disable the deletion and insertion of rows.
' By default these settings are false also.
sheet.Protection.IsDeletingRowsAllowed = False
sheet.Protection.IsInsertingRowsAllowed = False

' Save the file.
wb.Save("f:\test\test_lockrowcolumn.xls")

Thank you.