Skip to content

Instantly share code, notes, and snippets.

@tomg404
Created January 2, 2024 19:39
Show Gist options
  • Save tomg404/8a14c1285c6667f2c729a95ee2a5fbe1 to your computer and use it in GitHub Desktop.
Save tomg404/8a14c1285c6667f2c729a95ee2a5fbe1 to your computer and use it in GitHub Desktop.
Microsoft Excel 2007+ write protection remover
#!/bin/sh
# Script to remove the write protection from all worksheets
# in an excel `.xlsx` file.
#
# DISCLAIMER: I tested this script with a single file on an arch linux system. The
# type of the file before and after editing is 'Microsoft Excel 2007+'. Check this
# first with `file table.xlsx`!
#
# The process of removing the write protection is really simple and looks like the following:
# 1. extract .xlsx file with unzip (excel files are actually just zip archives lol)
# 2. the worksheets should be located in `xl/worksheets/sheetX.xml`
# 3. edit `sheetX.xml` and remove the `<sheetProtection ... />` tag
# 4. repack to zip archive and rename to .xlsx
#
# The script doesn't actually repack the whole archive but modifies (updates)
# only the `sheetX.xml` files. Therefore nothing else is changed and the metadata
# stay untouched. I did this because the command `file table.xlsx` didn't recognize
# the file properly as 'Microsoft Excel 2007+' when extracting and repacking the
# whole archive.
FILEPATH=$1
FILEEXTENSION="${FILEPATH##*.}"
FILENAME="$(basename $FILEPATH)"
if [[ -z "$FILEPATH" ]]; then
echo "Usage: $0 <file.xlsx>"
exit 1
fi
# check if file exists
if [ ! -e "$FILEPATH" ] || [ ! -f "$FILEPATH" ]; then
echo "File does not exist"
exit 1
fi
# check for correct file extension
if [[ "$FILEEXTENSION" != "xlsx" ]]; then
echo "Error: File has to be in xlsx format!"
exit 1
fi
# check if zip and unzip exist
command -v "zip" &> /dev/null
if [[ $@ == "" ]]; then
echo "Error: command zip not found!"
exit 1
fi
command -v "unzip" &> /dev/null
if [[ $@ == "" ]]; then
echo "Error: command unzip not found!"
exit 1
fi
# create temporary working directory
WORKDIR=$(mktemp -d)
# copy and rename file to temp dir
WORKDIR_ZIP="$WORKDIR/archive.zip"
cp "$FILEPATH" "$WORKDIR"
mv "$WORKDIR/$FILENAME" "$WORKDIR_ZIP"
# check if any worksheets exist
CONTENTS=$(unzip -l "$WORKDIR_ZIP" | tee "$WORKDIR/contents.txt")
if [[ ! $CONTENTS == *"xl/worksheets/"* ]]; then
echo -e "$CONTENTS"
echo "Error: no worksheets found!"
exit 1
fi
LAST_PWD=$(pwd)
cd "$WORKDIR"
echo "Unzipping, modifying and updating single worksheets..."
FILES=$(grep -e 'xl/worksheets/[^/]*\.xml' "$WORKDIR/contents.txt" | awk '{print $4}')
for file in $FILES; do
echo "$file : extract"
unzip "$WORKDIR_ZIP" "$file" -d "$WORKDIR" > /dev/null
echo "$file : remove <sheetProtection...>"
sed -i 's/<sheetProtection.*\/>//g' "$WORKDIR/$file"
echo "$file : update in archive"
zip -u "$WORKDIR_ZIP" "$file" > /dev/null
done
cd "$LAST_PWD"
OUTPATH="/tmp/modified-$(basename $FILEPATH)"
mv "$WORKDIR_ZIP" "$OUTPATH"
echo "created file at $OUTPATH"
rm -rf "$WORKDIR"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment