How to protect excel 2007

Aspose.Cells Product Family
1

I am using excel 2007. I have some troubles to protect the excel format to be modified. Can anyone help me on this. Thanks very very …much in advance.


The requirement is:
I don’t want users to change the format of the excel, e.g. insert a new row/column, delete a row/column, change the sequence of some rows. But users are allowed to update data in the excel.

I tried a lot of solutions to solve this problem, but nothing works. I put some example of my code here:

For Each ws As Worksheet In wb.Worksheets
 
                For Each wsCell As Aspose.Cells.Cell In ws.Cells
                    wsCell.GetStyle().IsLocked = True
                Next
 
                For Each wsColumn As Aspose.Cells.Column In ws.Cells.Columns
                    Dim style As Aspose.Cells.Style = wsColumn.Style
                    style.IsLocked = true
                    Dim styleFlag As StyleFlag = New StyleFlag()
                    styleFlag.Locked = True
                    wsColumn.ApplyStyle(style, styleFlag)
                Next
 
                For x As Integer = 3 To ws.Cells.Columns.Count - 1
                    ws.AutoFitColumn(x)
                    If ws.Cells.GetColumnWidth(x) > 20 Then
                        ws.Cells.SetColumnWidth(x, 20)
                    ElseIf ws.Cells.GetColumnWidth(x) < 10 Then
                        ws.Cells.SetColumnWidth(x, 10)
                    End If
                Next
 
                ws.Protection.IsDeletingColumnsAllowed = False
                ws.Protection.IsDeletingRowsAllowed = False
                ws.Protection.IsFormattingCellsAllowed = False
                ws.Protection.IsEditingScenariosAllowed = False
                ws.Protection.IsFilteringAllowed = False
                ws.Protection.IsFormattingColumnsAllowed = False
                ws.Protection.IsFormattingRowsAllowed = False
                ws.Protection.IsInsertingColumnsAllowed = False
                ws.Protection.IsInsertingRowsAllowed = False
                ws.Protection.IsSortingAllowed = False
                ws.Protection.IsUsingPivotTablesAllowed = False
            Next

Thanks a lot.
2

Hi,

Please provide us the sample Excel file which will contain your desired protection setting on it. We will analyze the sample and will suggest you the code snippet using Aspose.Cells API to perform the desired task.

Thanks,

3

Hi Salman,


Thanks for your quick reply. Here is my excel sample.Hope this can help.
4

Hi Salman,


I solved the problem by the following lines of code:

For Each wsColumn As Aspose.Cells.Column In ws.Cells.Columns
                    Dim style As Aspose.Cells.Style = wsColumn.Style
                    style.IsLocked = False
                    Dim styleFlag As StyleFlag = New StyleFlag()
                    styleFlag.Locked = True
                    wsColumn.ApplyStyle(style, styleFlag)
                Next
 
                ws.Protection.IsDeletingColumnsAllowed = False
                ws.Protection.IsDeletingRowsAllowed = False
                ws.Protection.IsFormattingCellsAllowed = False
                ws.Protection.IsEditingScenariosAllowed = False
                ws.Protection.IsFilteringAllowed = False
                ws.Protection.IsFormattingColumnsAllowed = False
                ws.Protection.IsFormattingRowsAllowed = False
                ws.Protection.IsInsertingColumnsAllowed = False
                ws.Protection.IsInsertingRowsAllowed = False
                ws.Protection.IsSortingAllowed = False
                ws.Protection.IsUsingPivotTablesAllowed = False
 
                ws.Protect(ProtectionType.All)

Thanks your attention.
5

Hi

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for your interest in Aspose products. For your convenience, I will move your request into Aspose.Cells forum.

Best regards,