Excel worksheet protection

With GemBox.Spreadsheet you can protect the worksheet in your Excel file. This protection prevents users from modifying (changing, moving, or deleting) the data in a worksheet from an Excel application.

Note that this protection doesn't have any effect on the behavior of GemBox.Spreadsheet, you can modify any protected worksheet just like the unprotected ones.

To enable the protection, you need to set the ExcelWorksheet.Protected property to true and optionally set the password using the WorksheetProtection.SetPassword method.

When the worksheet is protected, the cells that are locked (have the CellStyle.Locked property set to true) have restrictions that are specified in WorksheetProtection. The protection is not enforced on unlocked cells.

The following example shows how you can protect a worksheet and specify what cells are not protected.

using GemBox.Spreadsheet;

class Program
{
    static void Main()
    {
        // If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

        var workbook = new ExcelFile();
        var worksheet = workbook.Worksheets.Add("Sheet Protection");

        worksheet.Cells[0, 2].Value = "Only cells from A1 to A10 are editable.";

        for (int i = 0; i < 10; i++)
        {
            var cell = worksheet.Cells[i, 0];
            cell.SetValue(i);
            cell.Style.Locked = false;
        }

        worksheet.Protected = true;

        var protectionSettings = worksheet.ProtectionSettings;
        worksheet.Cells[3, 2].Value = "Sheet password is 123 (only supported for XLSX and XLS file format).";
        protectionSettings.SetPassword("123");

        worksheet.Cells[2, 2].Value = "Inserting columns is allowed (only supported for XLSX file format).";
        protectionSettings.AllowInsertingColumns = true;

        workbook.Save("Sheet Protection.%OutputFileType%");
    }
}

Imports GemBox.Spreadsheet

Module Program

    Sub Main()

        ' If using the Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook As New ExcelFile()
        Dim worksheet = workbook.Worksheets.Add("Sheet Protection")

        worksheet.Cells(0, 2).Value = "Only cells from A1 to A10 are editable."

        For i = 0 To 9 Step 1
            Dim cell = worksheet.Cells(i, 0)
            cell.SetValue(i)
            cell.Style.Locked = False
        Next

        worksheet.Protected = True

        Dim protectionSettings = worksheet.ProtectionSettings
        worksheet.Cells(3, 2).Value = "Sheet password is 123 (only supported for XLSX and XLS file format)."
        protectionSettings.SetPassword("123")

        worksheet.Cells(2, 2).Value = "Inserting columns is allowed (only supported for XLSX file format)."
        protectionSettings.AllowInsertingColumns = True

        workbook.Save("Sheet Protection.%OutputFileType%")

    End Sub
End Module

Note that the advanced options in WorksheetProtection are supported only in XLSX and XLS files. Also, allowing some objects to be editable is supported only in XLSX files.

To unprotect the protected worksheet, just set the ExcelWorksheet.Protected property to false. Note that GemBox.Spreadsheet can unprotect a worksheet without requiring its password.

To check if the protected worksheet has a password, use WorksheetProtection.HasPassword property.

To remove the password, provide null to the WorksheetProtection.SetPassword method.