Free Support Forum - aspose.com

Problem with worksheet protection

Hello,

We upgraded from version 4.2.0.12 to 4.4.1.15 of Aspose.Cells. This code worked before the update:

' Instantiate an Workbook object that represents an Excel file
Dim newWorkbook As Workbook = New Workbook()

With newWorkbook.Worksheets(0)

' Set worksheet protection level - where locked cells are not allowed to be edited
.Protection.IsEditingContentsAllowed = False

' Import datatable to worksheet
.Cells.ImportDataTable(aDataTable, True, "A1")

' Set column properties
.Cells.Columns(0).Style.IsLocked = True ' Can't be edited
.Cells.SetColumnWidth(0, 0)
.Cells.Columns(1).Style.IsLocked = False
.Cells.SetColumnWidth(1, 80)
.Cells.Columns(2).Style.IsLocked = False
.Cells.SetColumnWidth(2, 80)

' Set source language header properties
With .Cells(0, 1).Style
.IsLocked = True ' Can't be edited
.BackgroundColor = Color.LightGray
End With

' Set target language header properties
With .Cells(0, 2).Style
.IsLocked = True ' Can't be edited
.BackgroundColor = Color.LightGray
End With

End With

' Save to excel file
newWorkbook.Save(strFilePath)

After the upgrade this code generates a excel file that protects all cells, even tough only one column and two cells is locked(IsLocked = true). With other words the hole worksheet are protected now.
Waiting for your response.

thanks in advance,

Sincerely,

Masod Saidi

Hi Masod,

We change the column style setting routine. Now Column.Style property is a read-only property. Please change your code as following to format column B and C.

Dim style As Style = .Cells.Columns(1).Style
style.IsLocked = False
Dim flag As StyleFlag
flag.Locked = True

.Cells.Columns(1).ApplyStyle(style, flag)

.Cells.SetColumnWidth(1, 80)

style = .Cells.Columns(2).Style
style.IsLocked = False

.Cells.Columns(2).ApplyStyle(style, flag)

Thanks! it helped.

A smaller problem how do i get the Color.LightGrey to work on cells?

Hi,

Please create a style object and set the related style attributes and apply the style to your desired cell using Cell.SetStyle() method.

E.g..,

Dim wb As Workbook = New Workbook()
'.
'.

'Since Ms Excel color palette (56 colors ...0-55 indexed) does not contain light grey color by default,
'so you have to add it to the palette first.
wb.ChangePalette(Color.LightGray,54)

Dim cells As Cells = wb.Worksheets(0).Cells
Dim cell As Cell = cells(0,0)
cell.PutValue("Testing")

Dim styles As Styles = wb.Styles
Dim index As Integer = styles.Add()
Dim style As Style = styles(index)
style.ForegroundColor = Color.LightGray
style.Pattern = BackgroundType.Solid
style.Font.Color = Color.Red
style.HorizontalAlignment = TextAlignmentType.Center

cell.SetStyle(style)

wb.Save("d:\test\lightgray_shadingcolor.xls")

Thank you.