Second Life of a Hungarian SharePoint Geek

July 26, 2018

Removing the password protection from an Excel sheet using PowerShell

Filed under: Open XML, PowerShell, Tips & Tricks — Tags: , , — Peter Holpar @ 23:24

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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: