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 explained that she was using a template form which she can fill out but can not edit the predefined cells or the sheets themselfs.

Since the file was readable and not crypted, 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 approches:

  1. Open it with LibreOffcie 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 exprted 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 sum this up: One can set a protection on sheets and cells which keeps users from editing them accidentally. That reasonable and even useful.
But then one could also set a password which is needed to unlock these cells and there is absolutly no technical mechasim ensureing that this password is checked. It completly depends on the implementation of the program which is used to work with that sheet. If it just dosn’t parse the password setting there is nothing preventing the editing. What a functionality is that?

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

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 found two other Protection settings. In the xl/styles.xml file there are some xml tags which included applyProtection=. Some set to one some to zero. 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. However i just removed all these setting with 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.