Unlock protected Excel sheet with notepad

This post shows how to remove password protection on Excel files without having the need to install specific tools, you just need an ZIP reader and a notepad.

How it works?

Office 365 files, and so Excel files are collections of XML zipped files. It means that you can inspect the guts of your file simply by changing its extension from XLSX to ZIP.

Password protection on Excel file is not safely implemented, when you add a password protection, Excel add into a specific XML file a tag with you password hashed. If the tag is present the sheet is protected within the Excel interface, if the tag is missing the sheet is unprotected.

Procedure is then as simple as:

1. Open the XLSX file as an ZIP file;
2. Locate the XML file of the locked sheet;
3. Remove the protection tag;
4. Revert ZIP file into XLSX file.

Hands on

We have tailored a protected XLSX file for the demonstration. It contains a single sheet which is protected:

Make a copy of your file for safety. Rename the copy of the file, by changing its extension from XLSX to ZIP and say YES to the dialog.

Now open the ZIP file and navigate to .\xl\worksheets and locate the file sheet1.xml which contains all information about the protected sheet.

Open the file in a editor (choose one with search function, line wrap and syntax color to make your life easier) but the simple Windows notepad should suffice. The file looks like:

As we can see, the data of the sheet (values, formula, formats) are present. More of interest for us, there is a XML tag called sheetProtection which contains our hashed password:

<sheetProtection algorithmName="SHA-512" hashValue="PNO0CmMYMKZX472SvxDKR8/ByDG11hBeNZfv1L+oaqgkxh8HWeqJAhrGSXnADSxemwVTnDEClg6hRAJQuKeYhA=="
saltValue="3y5NgoBn79E3RuvzhFVZgw==" spinCount="100000" sheet="1" objects="1" scenarios="1"/>

Suppress exactly and only this tag from the file and save it. Now you can revert the extension of your file from ZIP to XLSX (again saying yes to the dialog).

Now you can reopen the hacked file, the password protection for this specific sheet is gone for good.

Automation

The above process is simple but a bit cumbersome and not totally error proof. Here is a simple python script unlocking all sheet at once an preserving the original source if desired:

import argparse

import openpyxl


def unlock(input_file, output_file):

    workbook = openpyxl.load_workbook(filename=input_file)
    for sheet in workbook:
        sheet.protection.disable()
    workbook.save(filename=output_file)
    workbook.close()


if __name__ == "__main__":

    parser = argparse.ArgumentParser(
        prog='Excel Worksheets Unlocker',
        description='Remove password protection of all sheets of an Excel file',
    )
    parser.add_argument('-i', '--input', type=str, required=True)
    parser.add_argument('-o', '--output', type=str, required=True)
    parameters = parser.parse_args()
    
    unlock(parameters.input, parameters.output)

To unlock the sheets, just issue the following command:

python unlock.py -i protected.xlsx -o hacked.xlsx

That will generate a new file called hacked.xlsx with all sheets unlocked.