Office Open XML Documents

.xlsx, .docx and .pptx files are Office Open XML (ECMA-376, ISO/IEC 29500): zip archives of XML parts, met as inputs from and outputs for business users. This script writes the revenue table with openpyxl, then reads the worksheet XML straight from the zip:

xlsx.py: writing a workbook and reading its XMLPython

import zipfile, duckdb
from lxml import etree
from openpyxl import Workbook
ch3 = "/mnt/d/Books/Data Engineering/demos/ch03/out/orders.parquet"
rows = duckdb.sql(f"""SELECT c.subject, sum(o.i.qty * o.i.unit_price)::DOUBLE
    FROM (SELECT status, unnest(items) AS i FROM '{ch3}') o
    JOIN 'catalog.parquet' c ON c.book_id = o.i.book_id
    WHERE o.status <> 'cancelled' GROUP BY ALL ORDER BY 2 DESC""").fetchall()
wb = Workbook()
wb.active.title = "Revenue"
for row in [("Subject", "Gross")] + rows:
    wb.active.append(row)
wb.save("revenue.xlsx")
with zipfile.ZipFile("revenue.xlsx") as z:                # an .xlsx file is a zip of XML parts
    print(len(z.namelist()), "parts:", ", ".join(n for n in z.namelist() if "/s" in n))
    sheet = etree.fromstring(z.read("xl/worksheets/sheet1.xml"))
ns = {"s": "http://schemas.openxmlformats.org/spreadsheetml/2006/main"}
for c in sheet.xpath("//s:row[2]/s:c", namespaces=ns):
    print(etree.tostring(c, encoding="unicode").replace(f' xmlns="{ns["s"]}"', ""))

Output

9 parts: xl/worksheets/sheet1.xml, xl/styles.xml
<c r="A2" t="inlineStr"><is><t>Technology</t></is></c>
<c r="B2" t="n"><v>934530.5</v></c>

Cells are c elements with a reference (r="B2") and a type; stream big sheets with iterparse (Streaming Large Documents).

MENU