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

using vsto, C# fx 3.5, how to check Excel Workbook or sheet is开发者_JS百科 password protected or not?

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.

Check the HasPassword property.

If you want to check if an excel workbook is password protected checking the HasPassword property on a workbook object will not work, because you must open a workbook before you can check the properties, and you can't open a workbook that is password protected if you don't have the password. See the problem?

This is the solution I found to know if the workbook has a password or not:

using Excel = Microsoft.Office.Interop.Excel;

Excel.Application xlsApp = new Excel.Application();
xlsApp.DisplayAlerts = false;

Excel.Workbooks wkbs = xlsApp.Workbooks;
Excel.Workbook wkb;


try
{
    wkb = wkbs.Open(path, ReadOnly: true, Password: "");
    //If you don't send a string for the password, it will popup a window
    //asking for the password and halt your program. If the workbook has no
    //password, it will open just fine.

}
catch (Exception ex)
{
    //If the file is password protected or otherwise unreadable, it will throw an exception.
}


wkb.Close(false);
xlsApp.Quit();