A friend of mine called me and ask if I know how to remove the write protection of a Excel file. No, I’ve never heard of such a thing so she explained that she was using a template form which she can fill out but can not edit the predefined cells or the sheets themselves.

Since the file was readable and not encrypted, I thought ‘how hard can it be?!', there must be a way to edit this thing without rebuilding it in a new Excel file.

After a short internet research I found multiple approaches:

  1. Open it with LibreOffice and reexport it as a new xlsx file.
  2. Open it with Google Sheets and reexport it as a new xlsx file.
  3. Manually edit the files with a text editor
  4. Lots of shady online tools
  5. Brute forcing the password with Visual Basic Scripts

First I tried opening the file in LibreOffice but sadly I couldn’t remove the write protection. LibreOffice was also asking for a password. Then another friend found a LibreOffice version in a Fedora 32 which seems to ignore this password setting but after he exported the file as .xlsx again most references where broken and the manual fixing of those would probably take hours.
Then we tried the same thing with Google Sheets. The application also ignores the password setting and simple loads all sheets fully editable but sadly when reexporting the files, all references are broken.

At this time I felt rather triggered!

To describe that feature briefly: One can set a protection on sheets and cells which keeps users from editing them accidentally. That’s reasonable and even useful.
But then one could also set a password which is needed to unlock these cells and there is absolutely no technical mechanism ensuring that this password is checked. It completely depends on the implementation of the program which is used to work with that sheet. If it just doesn’t parse the password setting there is nothing preventing the editing. What kind of a functionality is that?!

Maybe it’s better to go one layer beneath. XLSX files are basically zipped directories of XML files. So first I extracted the document to be able to read its internals:

highway17 ~/W/excel> cp -av write_protected_excel_shiet.xlsx write_protected_excel_shiet.zip
'write_protected_excel_shiet.xlsx' -> 'write_protected_excel_shiet.zip'
highway17 ~/W/excel> unzip write_protected_excel_shiet.zip -d write_protected_excel_shiet.unzipped
Archive:  write_protected_excel_shiet.zip
  inflating: write_protected_excel_shiet.unzipped/[Content_Types].xml  
  inflating: write_protected_excel_shiet.unzipped/_rels/.rels  
  inflating: write_protected_excel_shiet.unzipped/xl/workbook.xml  
  inflating: write_protected_excel_shiet.unzipped/xl/_rels/workbook.xml.rels  
  inflating: write_protected_excel_shiet.unzipped/xl/styles.xml  
  inflating: write_protected_excel_shiet.unzipped/xl/worksheets/sheet1.xml  
  inflating: write_protected_excel_shiet.unzipped/xl/worksheets/sheet2.xml  
  ...
  inflating: write_protected_excel_shiet.unzipped/xl/worksheets/sheet12.xml  
 extracting: write_protected_excel_shiet.unzipped/xl/media/image1.png  
  inflating: write_protected_excel_shiet.unzipped/xl/worksheets/_rels/sheet11.xml.rels  
  ...

Some guides in the internet describe that the sheetProtection setting should be in the workbook.xml but I found two other Protection settings. In the xl/styles.xml file there are some xml tags which included applyProtection=. Some set to 1, some to 0. So as a first try I set them all to zero with

sed -i 's/applyProtection="1"/applyProtection="0"/g'

Afterwards LibreOffice rendered the file totally scrambled and Excel refused to open it. I was thinking about some checksum which verifies if the file might be corrupted but I couldn’t find anything about it.

So I search in the other files and found things like this in the xl/worksheets/sheetN.xml files:

highway17 ~/W/e/w/x/worksheets> grep -rio '<[^>]*Protection[^>]*' *
sheet1.xml:<sheetProtection selectLockedCells="1" selectUnlockedCells="1"/
sheet2.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet3.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet4.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet5.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet6.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet7.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet8.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet9.xml:<sheetProtection password="90AE" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/
sheet10.xml:<sheetProtection sheet="1" objects="1" scenarios="1" selectLockedCells="1" autoFilter="0" selectUnlockedCells="1"/
sheet11.xml:<sheetProtection sheet="1" objects="1" scenarios="1" selectLockedCells="1" selectUnlockedCells="1"/

The password isn’t there in plaintext of course. Some hashing must be in use but I didn’t investigate much further since I want to fully remove the password instead of recovering it. However I just removed all these “sheetProtection” tags and zipped the whole thing together again.

egrep -lRZ '<sheetProtect[^>]*' . | xargs -0 -l sed -i 's/<sheetProtection[^>]*//g'
cd ../../..
zip -r unlocked.xlsx ./*

That worked! I could open the file with LibreOffice, the forms were in order, everything was unprotected and editable and last but not least Excel was able to open the files with working references.