How to Manage Security & Authentication in Excel XLSX Using .NET C#

Whether governmental, financial, sales, or educational, most businesses today find that Excel spreadsheets are an essential tool used to store all kinds of data. Some examples of how this is used in industry (but not limited to this list) are: storing data for finance, sales, customer data, product details, student grades and performance, personal information, etc.

Often these are sensitive and confidential data and thus need to be protected from any change, whether accidental or deliberate.

In this blog, we'll cover different levels of security that can be applied programmatically to an excel document using the DsExcel C# .NET library, including: 

• Securing Workbook (or Worksheet) using Password in C# 
• Adding authenticity to documents using Digital signatures in C#
• Configuring permissions for exported PDF in C# 

Securing a Workbook (and Worksheet) using Password in C# 

Document protection using a password is applied in several ways using the DsExcel C# .NET library, depending on the type or level of security required for a document. It includes: 

1. Securing the entire spreadsheet document or file

This approach ensures data access to authentic users only. When you open a secured document, it asks for a password, as shown below:

To add a password at the document level using DsExcel for C# .NET, define an instance of XlsxSaveOptions class and set the Password property as in the code below:

Workbook workbook = new Workbook();
workbook.Open("sales.xlsx");
XlsxSaveOptions saveOptions = new XlsxSaveOptions();
saveOptions.Password = "testOpen";
workbook.Save("Sales_Password.xlsx", saveOptions);

2. Protecting the structure of the Workbook

This type of security option protects the structure of the workbook from modification. It prohibits users from viewing hidden worksheets, adding, moving, deleting, hiding, or renaming worksheets, moving, resizing, or closing the workbook window, or hiding/unhide windows as shown below:

To add this security using DsExcel for C# .NET, call the Protect method with the IWorkbook interface as shown below:

workbook.Protect();

This method comes with three overloads. In the absence of any arguments, both the structure and window operations are disabled. And no password is required to unprotect them. 
The other overload allows disabling structure or window optionally. 

workbook.Protect(true, false);

The third overload allows setting a password that will be required when the workbook needs to be unprotected.

workbook.Protect("password", true, false);

To unprotect a workbook, use the Unprotect method of the IWorkbook interface as shown below:

workbook.Unprotect();  //unprotect a workbook
 
or 
 
workbook.Unprotect("password");  //unprotect a password protected workbook

3. Protecting Specific Sheets

Certain business situations require protecting specific sheets from modifications, for example, sheets with sales or performance data. When you try to edit the content on a protected sheet, it gives the warning shown below:

To protect a sheet using GcExcel C#.Net, use the Protect method with the IWorksheet Interface as shown below:

workbook.Worksheets["sheet name or index"].Protect();
 
OR
 
workbook.Worksheets["sheet name or index"].Protect("password"); //provide a password which will be required while unlocking the sheet

To unprotect a protected worksheet, use Unprotect method with the IWorksheet interface as shown below:

workbook.Worksheets["sheet name or index"].Unprotect();
 
OR
 
workbook.Worksheets["sheet name or index"].Unprotect("password");

Alternatively, you can also use the Protection property with the IWorksheet interface. In the later section, this method exposes several advanced protection Booleans for enabling and disabling restrictions. However, it does not offer to protect the worksheet with a password.

workbook.Worksheets[0].Protection = true;  //enable worksheet protection
 
workbook.Worksheets[0].Protection = false; //disable worksheet protection

4. Protecting Specific Elements on a Sheet

Sometimes it is not necessary or required to protect an entire sheet, rather specific elements such as charts, rows, columns, or cells in a sheet. For example, cells with calculated data, rows with totals, conditionally formatted cells, etc.

This security ensures avoiding changes to data/content and changes to worksheet structure or elements. 

The former is achieved using the Locked property with IRange objects such as Cells, Rows, Columns, or Range. The latter is achieved by configuring the custom security options with the IProtectionSettings interface.

Locking/Unlocking Elements

When you protect a sheet using the methods described in the previous section, the cells in the worksheet are locked, and the CRUD operations are restricted on rows, columns, or any other elements.
To lock specific cells or elements, first, unlock all the cells/elements and then set the Locked property to true for specific elements as shown in the code below:

workbook.Worksheets[0].Protect();
workbook.Worksheets[0].Cells.Locked = false;
workbook.Worksheets[0].Rows[30].Locked = true;  //Row with totals is locked, other can be edited

Securing Worksheet Structure with Custom Settings

DsExcel for C# .NET offers customization options to allow certain functionalities to users while keeping the entire worksheet protected. The properties are found within the IProtectionSettings interface. It corresponds to the settings in MS Excel as shown below:

Use these settings with Protect method or Protection property as in the code below:

workbook.Worksheets[0].Protect("password");
IProtectionSettings protectionSettings = workbook.Worksheets[0].ProtectionSettings;
protectionSettings.AllowDeletingRows = false;
protectionSettings.AllowDeletingColumns = false;
protectionSettings.AllowFiltering = true;
protectionSettings.AllowFormattingCells = true;
protectionSettings.AllowInsertingColumns = true;
protectionSettings.AllowFormattingColumns = true;
protectionSettings.AllowFormattingRows = true;
protectionSettings.AllowInsertingHyperlinks = true;
protectionSettings.AllowUsingObjects = true;
protectionSettings.AllowInsertingRows = true;
protectionSettings.AllowSorting = true;
protectionSettings.AllowUsingPivotTables = true;
 
OR 
 
 
workbook.Worksheets[0].Protection = true;
workbook.Worksheets[0].ProtectionSettings.AllowDeletingRows = false;
workbook.Worksheets[0].ProtectionSettings.AllowDeletingColumns = false;
workbook.Worksheets[0].ProtectionSettings.AllowFiltering = true;
workbook.Worksheets[0].ProtectionSettings.AllowFormattingCells = true;
workbook.Worksheets[0].ProtectionSettings.AllowInsertingColumns = true;
workbook.Worksheets[0].ProtectionSettings.AllowFormattingColumns = true;
workbook.Worksheets[0].ProtectionSettings.AllowFormattingRows = true;
workbook.Worksheets[0].ProtectionSettings.AllowInsertingHyperlinks = true;
workbook.Worksheets[0].ProtectionSettings.AllowUsingObjects = true;
workbook.Worksheets[0].ProtectionSettings.AllowInsertingRows = true;
workbook.Worksheets[0].ProtectionSettings.AllowSorting = true;
workbook.Worksheets[0].ProtectionSettings.AllowUsingPivotTables = true;

Adding Digital Signatures using C# .NET 

Digital signatures are used to authenticate electronic documents. It confirms that the information originated and was verified by the signer and has not been altered. Adding a digital certificate to an excel document using DsExcel API for C# .Net is a two-step process as discussed below:

Step 1 - Adding a Signature Line

The signature line is a signature placeholder for digital signatures, as shown below:

To add a signature line to the document using DsExcel, use the AddSignatureLine method of the ISignatureSet interface. It can be configured for your business need using the properties defined in the ISignatureSetup interface. Follow the code below to add and configure a signature to the excel document:

ISignature signature = workbook.Signatures.AddSignatureLine(workbook.ActiveSheet, 100.0, 50.0);
ISignatureSetup setup = signature.Setup;
setup.ShowSignDate = true;
setup.AllowComments = true;
setup.SigningInstructions = "Please check the content before signing.";
setup.SuggestedSigner = "James Lawyer";
setup.SuggestedSignerEmail = "support@grapecity.com";
setup.SuggestedSignerLine2 = "Department Head";

Step 2 - Signing the Signature Line

Once you have a signature line, you need to sign the signature line with a signing certificate to provide the signer's identity. Follow the steps mentioned in Generate Certificate document to generate a certificate file (.pfx). When you have the certificate, use the Sign method of the ISignature interface to add a digital signature. The two required arguments with this method are the X509Certificate and SignatureDetails, as shown in the code below:

//Set Signature details
var details = new SignatureDetails
    {
       Address1 = "Your Address1",
       Address2 = "Your Address2",
       SignatureComments = "Final",
       City = "Your City",
       StateOrProvince = "Your State",
       PostalCode = "Your Postal Code",
       CountryName = "Your Country",
       ClaimedRole = "Your Role",
       CommitmentTypeDescription = "Approved",
       CommitmentTypeQualifier = "Final"
    };
 
//Initialize Signature
var cert = new X509Certificate2("GcTest.pfx","password");
 
//Sign the signature line
signature.Sign(cert, "James Lawyer", details);

When the signature is signed, it appears in the excel document as shown below:

DsExcel provides you with APIs to edit, delete, move, export, countersign, verify, and do more with a digital signature. You can follow the documentation and explore the demos to learn more about them. 

Securing the exported PDF using C#  

PDF is one of the most used document formats for sharing, transferring, or moving files. DsExcel for C# .NET offers several security options that can be applied while exporting the excel document to PDF format and securing the data within.

The exported pdf can be secured in two ways:

1. by applying restrictions on the operations that can be performed on/with the pdf
2. setting a password 

Permission-based restrictions

Permissions determine the kind of access that should be granted to the user on the pdf document. Using permissions, users are prohibited from printing, copying, and extracting content, modifying documents, and filling AcroForms (or Interactive Forms). 

Password-based security

DsExcel API for C# .NET allows setting the user and owner passwords. When these passwords are selected, you are prompted for a password on file open as shown below:

User password encrypts the entire file and prevents its opening. The content is not visible unless the user provides a valid password.

The owner password does not encrypt a document but is used to control the operations that can be performed on it. It allows you to perform operations such as printing, copying, and modifying documents that are otherwise restricted to other users.

To set the security options for PDF using the DsExcel, set the properties available with the PdfSaveOptions and PdfSecurityOptions classes as in the code below:

PdfSecurityOptions securityOptions = new PdfSecurityOptions
{
    //Sets the user password.
    UserPassword = "user",
    //Sets the owner password.
    OwnerPassword = "owner",
    //Allow to print pdf document.
    PrintPermission = false,
    //Print the pdf document in high quality.
    FullQualityPrintPermission = false,
    //Allow to copy or extract the content of the pdf document.
    ExtractContentPermission = false,
    //Allow to modify the pdf document.
    ModifyDocumentPermission = false,
    //Allow to insert, rotate, or delete pages and create bookmarks or thumbnail images of the pdf document.
    AssembleDocumentPermission = false,
    //Allow to modify text annotations and fill the form fields of the pdf document.
    ModifyAnnotationsPermission = false,
    //Filling the form fields of the pdf document is not allowed.
    FillFormsPermission = false
};
 
PdfSaveOptions pdfSaveOptions = new PdfSaveOptions
{
    //Sets the security settings of the pdf.
    SecurityOptions = securityOptions
};
 
//Save the workbook into pdf file.
workbook.Save(outputStream, pdfSaveOptions);

These different security methods can be combined to protect, validate, and securely share confidential information stored in spreadsheet documents. 

Read the documentation and explore the demo to see the PDF security options in action.