Last Updated or created 2024-05-29
I needed to get some data from protected sheets.
But I got this on 100+ files:
F that:
Made a script to remove sheet protection and ran this in a loop over all files.
Bye bye protection.
./script.sh /mnt/fileserver/sensordata001.xlsx Parsing /mnt/fileserver/sensordata001.xlsx Archive: this.zip inflating: tmp/[Content_Types].xml inflating: tmp/_rels/.rels inflating: tmp/xl/_rels/workbook.xml.rels inflating: tmp/xl/workbook.xml inflating: tmp/xl/worksheets/sheet4.xml inflating: tmp/xl/worksheets/sheet2.xml inflating: tmp/xl/worksheets/sheet3.xml inflating: tmp/xl/worksheets/sheet1.xml inflating: tmp/xl/styles.xml inflating: tmp/xl/theme/theme1.xml inflating: tmp/xl/sharedStrings.xml inflating: tmp/docProps/core.xml inflating: tmp/docProps/app.xml adding: [Content_Types].xml (deflated 78%) adding: docProps/ (stored 0%) adding: docProps/core.xml (deflated 49%) adding: docProps/app.xml (deflated 54%) adding: _rels/ (stored 0%) adding: _rels/.rels (deflated 60%) adding: xl/ (stored 0%) adding: xl/worksheets/ (stored 0%) adding: xl/worksheets/sheet2.xml (deflated 92%) adding: xl/worksheets/sheet1.xml (deflated 46%) adding: xl/worksheets/sheet4.xml (deflated 92%) adding: xl/worksheets/sheet3.xml (deflated 92%) adding: xl/_rels/ (stored 0%) adding: xl/_rels/workbook.xml.rels (deflated 77%) adding: xl/workbook.xml (deflated 56%) adding: xl/theme/ (stored 0%) adding: xl/theme/theme1.xml (deflated 78%) adding: xl/styles.xml (deflated 57%) adding: xl/sharedStrings.xml (deflated 23%)
Bash script:
It leaves the original intact, but makes a unprotected copy named the same with unprot_ in front of it.
#!/bin/bash
if [ -n "$1" ]; then
echo "Parsing $1"
else
echo "usage : scriptname /path/to/sheet.xlsx"
exit 0
fi
name=$(basename $1)
dir=$(dirname $1)
rm -f this.zip
rm -rf tmp
mkdir tmp
cp "$1" this.zip
unzip this.zip -d tmp
find tmp/xl/worksheets/ -iname *xml -exec sed -i -e "s/<sheetProtection.*\/>//g" {} \;
cd tmp
rm -f "$dir/unprot_${name}"
zip -r "$dir/unprot_${name}" *
cd ..