c# - how to check Excel Workbook or sheet is password protected or not?

Question

using vsto, C# fx 3.5, how to check Excel Workbook or sheet is password protected or not?

Answer 1

You can check if a workbook is password protected via the Workbook.HasPassword property. You can set the workbook password via the Workbook.SaveAs method:

Excel.Workbook myWorkbook = ...;

if (!myWorkbook.HasPassword)
{
   excelWorkbook.Application.DisplayAlerts = false;

   excelWorkbook.SaveAs(
        excelWorkbook.Name,
        Type.Missing,
        "My Password",
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing,
        Type.Missing);
}

A worksheet can have it's cell contents protected, the drawing objects protected, and/or the scenarios protected. These can be checked via the Worksheet.ProtectContents, Worksheet.ProtectDrawingObjects, and Worsksheet.ProtectScenarios properties, respectively. I do not know of any way of testing if the worksheet is protected with a password or not, other than trying to call Worksheet.Unprotect, passing in an empty string for the password, and then seeing if the worksheet was successfully unprotected:

bool wasPasswordProtected;

try
{
    myWorksheet.Unprotect(string.Empty);

    // Unprotect suceeded:
    wasPasswordProtected = false;  
}
catch
{
    // Unprotect failed:
    wasPasswordProtected = true;
}

You can set the protection setting for the worksheet via the Worksheet.Protect method. The following example will protect the worksheet if any of the three protection elements are not set. It also sets a password and passes in the 'UserInterfaceOnly' argument as 'true', which means that only the user is blocked from editing the worksheet, while code such as VBA, VB.NET, or C# would not be prevented from manipulating the worksheet. Setting 'UserInterfaceOnly' to 'false' would lock out all changes, whether made by the user or via code.

if(!myWorksheet.ProtectContents ||
   !myWorksheet.ProtectDrawinngObjects ||
   !myWorsksheet.ProtectScenarios)
{
    string myPassword = "...";

    bool protectContents = true;
    bool protectDrawingObjects = true;
    bool protectScenarios = true;

    bool userInterfaceOnly = true;

    myWorksheet.Protect(
        myPassword,
        protectDrawingObjects,
        protectContents, 
        protectScenarios,
        userInterfaceOnly,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing,    
        Type.Missing);

Note that the 'UserInterfaceOnly' does NOT persist when the workbook is saved and reverts to 'false' automatically when the workbook is closed. If you wish it to be 'true' in all future sessions as well, the 'UserInterfaceOnly' setting must be re-applied by calling the 'Worksheet.Protect' method each time the workbook is open. This can be be done by subscribing to the Workbook.Open event.

You also might want to read the help files regarding the Worksheet.Protect method so you can understand the optional parameters, paying particular attention to the 'UserInterfaceOnly' parameter.