SPB Git

Repository Public License

make-sample-workbooks.sh 4.5 KB 55 lines shellscript

1#!/usr/bin/env bash2# ORIX OS Automation3# File: Workflows/Samples/spreadsheet-validation/make-sample-workbooks.sh4# Description: Regenerates the two minimal .xlsx sample workbooks (one valid, one with broken rows) from hand-written SpreadsheetML parts, using only zip.5#6# Author: Simon-Pierre Boucher7# Contact: contact@spboucher.ai8#9# Usage: ./make-sample-workbooks.sh [output folder]     (default: the folder of this script)10set -euo pipefail11OUT="$(cd "${1:-$(dirname "$0")}" && pwd)"12WORK="$(mktemp -d)"13trap 'rm -rf "$WORK"' EXIT1415# $1 = file name, $2 = sheetData XML (rows), $3 = shared strings (one per line)16make_workbook() {17  local dir="$WORK/$1.d"18  mkdir -p "$dir/_rels" "$dir/xl/_rels" "$dir/xl/worksheets"19  cat > "$dir/[Content_Types].xml" <<'XML'20<?xml version="1.0" encoding="UTF-8" standalone="yes"?>21<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/><Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/></Types>22XML23  cat > "$dir/_rels/.rels" <<'XML'24<?xml version="1.0" encoding="UTF-8" standalone="yes"?>25<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>26XML27  cat > "$dir/xl/workbook.xml" <<'XML'28<?xml version="1.0" encoding="UTF-8" standalone="yes"?>29<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="Orders" sheetId="1" r:id="rId1"/></sheets></workbook>30XML31  cat > "$dir/xl/_rels/workbook.xml.rels" <<'XML'32<?xml version="1.0" encoding="UTF-8" standalone="yes"?>33<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/><Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/></Relationships>34XML35  {36    printf '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'37    while IFS= read -r s; do [ -n "$s" ] && printf '<si><t>%s</t></si>' "$s"; done <<< "$3"38    printf '</sst>\n'39  } > "$dir/xl/sharedStrings.xml"40  printf '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>%s</sheetData></worksheet>\n' "$2" > "$dir/xl/worksheets/sheet1.xml"41  rm -f "$OUT/$1"42  (cd "$dir" && zip -q -X -r "$OUT/$1" '[Content_Types].xml' _rels xl)43  echo "wrote $OUT/$1"44}4546# Shared strings: 0 id, 1 name, 2 amount, 3 Alpha, 4 Beta, 5 Gamma, 6 Delta, 7 total47STRINGS=$'id\nname\namount\nAlpha\nBeta\nGamma\nDelta\ntotal'48HEADER='<row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c><c r="C1" t="s"><v>2</v></c></row>'4950# data.xlsx: header + 4 complete rows (numbers, shared strings, one formula with its cached value).51make_workbook data.xlsx "$HEADER"'<row r="2"><c r="A2"><v>1001</v></c><c r="B2" t="s"><v>3</v></c><c r="C2"><v>120.5</v></c></row><row r="3"><c r="A3"><v>1002</v></c><c r="B3" t="s"><v>4</v></c><c r="C3"><v>75</v></c></row><row r="4"><c r="A4"><v>1003</v></c><c r="B4" t="s"><v>5</v></c><c r="C4"><v>310</v></c></row><row r="5"><c r="A5"><v>1004</v></c><c r="B5" t="s"><v>6</v></c><c r="C5"><f>C2+C3</f><v>195.5</v></c></row>' "$STRINGS"5253# data-invalid.xlsx: the "amount" column is called "total", row 3 has no id, row 4 is empty.54make_workbook data-invalid.xlsx '<row r="1"><c r="A1" t="s"><v>0</v></c><c r="B1" t="s"><v>1</v></c><c r="C1" t="s"><v>7</v></c></row><row r="2"><c r="A2"><v>1001</v></c><c r="B2" t="s"><v>3</v></c><c r="C2"><v>120.5</v></c></row><row r="3"><c r="B3" t="s"><v>4</v></c><c r="C3"><v>75</v></c></row><row r="4"><c r="A4" t="inlineStr"><is><t></t></is></c></row><row r="5"><c r="A5"><v>1004</v></c><c r="B5" t="s"><v>6</v></c><c r="C5"><v>10</v></c></row>' "$STRINGS"55
Files main
Menu

Theme