Removing the password protection from an Excel sheet using PowerShell

Today I received an Excel sheet that I should have to edit, although some cells whose value I had to change was protected by a password I did not know (Note: I’m using Excel 2013).

I’m quite familiar with Open XML, so I was sure, it must be possible by editing the XML files the Excel file consist of, but did not know exactly, which nodes should I edit or remove. A quick search provided me the information, how to do it manually or by using the Open XML SDK and C#.

Although I like C# and have Visual Studio installed on my PC, I decided to create a solution using PowerShell based on the C# example mentioned above, to enable the automated removal of password protection for a broader range of users. Prerequisite: the Open XML SDK must be installed on the computer.

The conversion was not really complex, a key point was the usage of the MakeGenericMethod to enable access to the generic methods of the Open XML object model.

The code snippet below illustrates the result:

1. # change the path to point to your password protected Excel sheet, and ensure that the sheet is not opened in Excel
2. $filePath = 'C:\Data\ProtectedSheet.xlsx'
3.  
4. # I have the DocumentFormat.OpenXml assembly in the Global Assembly Cache (GAC). If you are like me, you can use
5. #[System.Reflection.Assembly]::LoadWithPartialName('DocumentFormat.OpenXml')
6. # otherwise load the assembly from its installation path
7. [System.Reflection.Assembly]::LoadFrom('C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll')
8.  
9. $spreadSheetDocument = [DocumentFormat.OpenXml.Packaging.SpreadsheetDocument]::Open($filePath, $true)
10.  
11. # Worksheet class has a non-generic overload of the RemoveAllChildren method as well
12. $removeAllChildrenMethod = [DocumentFormat.OpenXml.Spreadsheet.Worksheet].GetMethods() | ? { $_.Name -eq 'RemoveAllChildren' -and $_.IsGenericMethod }
13. $removeAllChildrenMethodGeneric = $removeAllChildrenMethod.MakeGenericMethod([DocumentFormat.OpenXml.Spreadsheet.SheetProtection])
14.  
15. $sheets = $spreadSheetDocument.WorkbookPart.Workbook.ChildElements | ? { $_.LocalName -eq 'sheets'}
16. $sheets.ChildElements.Id | % {
17.     $relationshipId = $_.Value
18.     $worksheetPart = $spreadSheetDocument.WorkbookPart.GetPartById($relationshipId)
19.     $workSheet = $worksheetPart.Worksheet
20.     $removeAllChildrenMethodGeneric.Invoke($workSheet, [System.Object[]]@())
21.     $workSheet.Save()
22. }
23.  
24. $spreadSheetDocument.Close()
25. $spreadSheetDocument.Dispose()

As mentioned in the comment in the code, you should change the path to refer to the real location of your Excel file, and mustn’t have the document opened in Excel, otherwise it will lock the file and the script can’t change it. If you want to be sure, close all instances of Excel before staring the script.