Unlocking a range of cells not working

Aspose.Cells Product Family
1

I am trying to limit interactions with a spreadsheet to simply entering numbers in designated cells. There are multiple ranges, and two different formats (2 decimal places and no decimals).


I have the following (subset of the) code:

Dim workbook As New Aspose.Cells.Workbook()
workbook.ChangePalette(System.Drawing.ColorTranslator.FromHtml("#D9D9D9"), 50)
Dim s As Aspose.Cells.Style
s = workbook.Styles(workbook.Styles.Add)
Dim sFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
s.Font.Name = “Tahoma”
s.Font.Size = 8
s.IsLocked = True
sFlag.FontName = True
sFlag.FontSize = True
sFlag.HorizontalAlignment = True
sFlag.Locked = True
Dim sDecimal As Aspose.Cells.Style
sDecimal = workbook.Styles(workbook.Styles.Add)
sDecimal.Number = 2
Dim sDecimalFormatFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
sDecimalFormatFlag.NumberFormat = True
sDecimalFormatFlag.Locked = False
sDecimal.IsLocked = False

Dim sNumber As Aspose.Cells.Style
sNumber = workbook.Styles(workbook.Styles.Add)
sNumber.Number = 0
Dim sNumberFormatFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
sNumberFormatFlag.NumberFormat = True
sNumberFormatFlag.Locked = False
sNumber.IsLocked = False

’ first format/lock all columns
For i As Integer = 0 To 255
workbook.Worksheets(0).Cells.Columns(CByte(i)).ApplyStyle(s, sFlag)
Next i
’ There are several lines setting some column headers, etc. here
workbook.Worksheets(0).Cells.CreateRange(2, 1, 12, 5).ApplyStyle(sDecimal, sDecimalFormatFlag)
workbook.Worksheets(0).Cells.CreateRange(2, 6, 12, 5).ApplyStyle(sNumber, sNumberFormatFlag)
workbook.Worksheets(0).Protect(Aspose.Cells.ProtectionType.All)

What I wind up with is a worksheet that is completely locked to editing. When I check the individual cell properties, they are still Locked.
2

The key was to set:


sDecimalFormatFlag.Locked = True
sNumberFormatFlag.Locked = True

Apparently, those are flags that tell Aspose.Cells to check the IsLocked flag on the Style entity. Documentation fails to mention that.
3

Hi,

Thanks for your posting and using Aspose.Cells.

We tested your issue with the following code and it worked fine. Yes, StyleFlag.Locked property tells Aspose.Cells to apply locked settings or not.

Here is a documentation reference.

StyleFlag.Locked Property

Locked setting will be applied.

public bool Locked {get; set;}

I have also attached the output xls file generated by the code for a reference.

VB.NET
Dim workbook As New Aspose.Cells.Workbook()
workbook.ChangePalette(System.Drawing.ColorTranslator.FromHtml("#D9D9D9"), 50)
Dim s As Aspose.Cells.Style
s = workbook.Styles(workbook.Styles.Add())
Dim sFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
s.Font.Name = "Tahoma"
s.Font.Size = 8
s.IsLocked = True
sFlag.FontName = True
sFlag.FontSize = True
sFlag.HorizontalAlignment = True
sFlag.Locked = True
Dim sDecimal As Aspose.Cells.Style
sDecimal = workbook.Styles(workbook.Styles.Add)
sDecimal.Number = 2

Dim sDecimalFormatFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
sDecimalFormatFlag.NumberFormat = True
sDecimalFormatFlag.Locked = True
sDecimal.IsLocked = False

Dim sNumber As Aspose.Cells.Style
sNumber = workbook.Styles(workbook.Styles.Add)
sNumber.Number = 0
Dim sNumberFormatFlag As Aspose.Cells.StyleFlag = New Aspose.Cells.StyleFlag
sNumberFormatFlag.NumberFormat = True
sNumberFormatFlag.Locked = True
sNumber.IsLocked = False

' first format/lock all columns
For i As Integer = 0 To 255
workbook.Worksheets(0).Cells.Columns(CByte(i)).ApplyStyle(s, sFlag)
Next i
' There are several lines setting some column headers, etc. here
Dim r As Range = workbook.Worksheets(0).Cells.CreateRange(2, 1, 12, 5)
r.ApplyStyle(sDecimal, sDecimalFormatFlag)

r = workbook.Worksheets(0).Cells.CreateRange(2, 6, 12, 5)
r.ApplyStyle(sNumber, sNumberFormatFlag)
workbook.Worksheets(0).Protect(Aspose.Cells.ProtectionType.All)


workbook.Save("test.xls", SaveFormat.Excel97To2003)