How to remove the password from a spreadsheet (.ods or Excel)

Do you have a password-protected spreadsheet and need to remove the password from it?

Yes? Then this post will be helpful to you.

There are some posts on the net suggesting that you can just open the .xls file as a zip archive and then edit the contents but I was unable to do that … perhaps the newer versions of Excel save their documents in ZIP .

So the method I use is:

1. [optional] Convert the Excel spreadsheet to OpenOffice format (.ods).
2. Unzip the OO file (.ods).
3. Remove the password protection
4. Re-zip back into a .ods file
5. [optional] Save the .ods file as an .xls.

Steps 1 and 5 are optional and necessary only if you have an Excel spreadsheet. If you have an OpenOffice or LibreOffice document you should skip them.

Let’s begin !

1. [optional] Convert your spreadsheet file to .ods format.
   You need to do this if your original file is an Excel document. If your file is already an OpenOffice or LibreOffice document then, of course, you do not need this step.
   OpenOffice and LibreOffice are interchangeable for our purpose, so I will just call them ‘OO’ below, for simplicity.
   To convert the file to .ods format you need to open it in OO and save it as a OO spreadsheet file (choose the .ods file format in the ‘Save As’ dialog).
   Now you have a .ods file and this document you CAN unzip!
2. Unzip the .ods file contents to a temporary dir

• [optional] You may want to rename your file to ‘.zip’ first, i.e. if you got “data.ods” after step 1, rename it to “data.zip” to make it easier to work with it as a ZIP file (some unarchivers may refuse to open it if it does not have the .zip extension).

Extract the file contents to some directory with 7zip or any other archiver app. Doing this in the linux shell you would execute:

$ unzip data.ods -d somedir

3. Remove the password protection.
One of the files is called content.xml. This is the file that specifies that some of the data in the spreadsheet is password protected

To remove the password protection change all occurrences of the string table:protected="true" to table:protected="false". You cand do this in the shell with this command:

$ sed -e 's/table:protected="true"/table:protected="false"/g' 
     < content.xml > tmp ; mv tmp content.xml

You now have in the ‘somedir’ directory the contents of the .ods file WITHOUT password protection. You need to assemble it back into a spreadsheet file.

4. Re-assemble the .ods spreadsheet file.
Easy as pie: select all the contents of ‘somedir’ and zip it up.

5. [optional] If you want to get an Excel spreadsheet out of this – open the ods in OO and save it as an Excel document!

DONE!
🙂