140 lines
7.6 KiB
Python
140 lines
7.6 KiB
Python
from __future__ import annotations
|
|
|
|
from datetime import UTC, date, datetime, timedelta
|
|
from pathlib import Path
|
|
from xml.sax.saxutils import escape
|
|
from zipfile import ZIP_DEFLATED, ZipFile
|
|
|
|
MAIN_NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
|
|
REL_NS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
|
|
PKG_REL_NS = "http://schemas.openxmlformats.org/package/2006/relationships"
|
|
CONTENT_TYPES_NS = "http://schemas.openxmlformats.org/package/2006/content-types"
|
|
DOC_PROPS_NS = "http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"
|
|
CORE_PROPS_NS = "http://schemas.openxmlformats.org/package/2006/metadata/core-properties"
|
|
DC_NS = "http://purl.org/dc/elements/1.1/"
|
|
DCTERMS_NS = "http://purl.org/dc/terms/"
|
|
XSI_NS = "http://www.w3.org/2001/XMLSchema-instance"
|
|
|
|
|
|
def date_to_excel_serial(value: date | datetime | str) -> int:
|
|
if isinstance(value, str):
|
|
parsed = datetime.strptime(value, "%Y-%m-%d").date()
|
|
elif isinstance(value, datetime):
|
|
parsed = value.date()
|
|
else:
|
|
parsed = value
|
|
return (parsed - date(1899, 12, 30)).days
|
|
|
|
|
|
def _col_name(index: int) -> str:
|
|
letters = ""
|
|
while index:
|
|
index, remainder = divmod(index - 1, 26)
|
|
letters = chr(65 + remainder) + letters
|
|
return letters
|
|
|
|
|
|
def _cell_xml(value: object, row_index: int, col_index: int) -> str:
|
|
ref = f"{_col_name(col_index)}{row_index}"
|
|
if value is None or value == "":
|
|
return f'<c r="{ref}" t="inlineStr"><is><t></t></is></c>'
|
|
if isinstance(value, bool):
|
|
return f'<c r="{ref}" t="b"><v>{1 if value else 0}</v></c>'
|
|
if isinstance(value, (int, float)):
|
|
return f'<c r="{ref}"><v>{value}</v></c>'
|
|
if isinstance(value, (date, datetime)):
|
|
return f'<c r="{ref}" s="1"><v>{date_to_excel_serial(value)}</v></c>'
|
|
return f'<c r="{ref}" t="inlineStr"><is><t xml:space="preserve">{escape(str(value))}</t></is></c>'
|
|
|
|
|
|
def export_xlsx(path: str | Path, sheet_name: str, rows: list[list[object]]) -> None:
|
|
path = Path(path)
|
|
path.parent.mkdir(parents=True, exist_ok=True)
|
|
max_col = 1
|
|
max_row = max(1, len(rows))
|
|
sheet_rows = []
|
|
for row_index, row in enumerate(rows, start=1):
|
|
max_col = max(max_col, len(row))
|
|
cells = "".join(_cell_xml(value, row_index, col_index) for col_index, value in enumerate(row, start=1))
|
|
sheet_rows.append(f'<row r="{row_index}">{cells}</row>')
|
|
dimension = f"A1:{_col_name(max_col)}{max_row}"
|
|
worksheet_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<worksheet xmlns="{MAIN_NS}" xmlns:r="{REL_NS}">'
|
|
f'<dimension ref="{dimension}"/>'
|
|
'<sheetViews><sheetView workbookViewId="0"/></sheetViews>'
|
|
'<sheetFormatPr defaultRowHeight="15"/>'
|
|
'<sheetData>' + "".join(sheet_rows) + '</sheetData>'
|
|
'</worksheet>'
|
|
)
|
|
styles_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<styleSheet xmlns="{MAIN_NS}">'
|
|
'<numFmts count="1"><numFmt numFmtId="164" formatCode="yyyy-mm-dd"/></numFmts>'
|
|
'<fonts count="1"><font><sz val="11"/><name val="Constantia"/></font></fonts>'
|
|
'<fills count="2"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill></fills>'
|
|
'<borders count="1"><border><left/><right/><top/><bottom/><diagonal/></border></borders>'
|
|
'<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
|
|
'<cellXfs count="2"><xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/><xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/></cellXfs>'
|
|
'<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
|
|
'</styleSheet>'
|
|
)
|
|
content_types_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<Types xmlns="{CONTENT_TYPES_NS}">'
|
|
'<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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
|
|
'<Override PartName="/docProps/core.xml" ContentType="application/vnd.openxmlformats-package.core-properties+xml"/>'
|
|
'<Override PartName="/docProps/app.xml" ContentType="application/vnd.openxmlformats-officedocument.extended-properties+xml"/>'
|
|
'</Types>'
|
|
)
|
|
root_rels_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<Relationships xmlns="{PKG_REL_NS}">'
|
|
'<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
|
|
'<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>'
|
|
'<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>'
|
|
'</Relationships>'
|
|
)
|
|
workbook_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<workbook xmlns="{MAIN_NS}" xmlns:r="{REL_NS}">'
|
|
'<bookViews><workbookView xWindow="0" yWindow="0" windowWidth="24000" windowHeight="12000"/></bookViews>'
|
|
f'<sheets><sheet name="{escape(sheet_name)}" sheetId="1" r:id="rId1"/></sheets>'
|
|
'</workbook>'
|
|
)
|
|
workbook_rels_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<Relationships xmlns="{PKG_REL_NS}">'
|
|
'<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/styles" Target="styles.xml"/>'
|
|
'</Relationships>'
|
|
)
|
|
created = datetime.now(UTC).strftime("%Y-%m-%dT%H:%M:%SZ")
|
|
core_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<cp:coreProperties xmlns:cp="{CORE_PROPS_NS}" xmlns:dc="{DC_NS}" xmlns:dcterms="{DCTERMS_NS}" xmlns:dcmitype="http://purl.org/dc/dcmitype/" xmlns:xsi="{XSI_NS}">'
|
|
'<dc:creator>Jewelry Inventory</dc:creator>'
|
|
f'<dcterms:created xsi:type="dcterms:W3CDTF">{created}</dcterms:created>'
|
|
f'<dcterms:modified xsi:type="dcterms:W3CDTF">{created}</dcterms:modified>'
|
|
'</cp:coreProperties>'
|
|
)
|
|
app_xml = (
|
|
'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
|
|
f'<Properties xmlns="{DOC_PROPS_NS}" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes">'
|
|
'<Application>Jewelry Inventory</Application>'
|
|
'</Properties>'
|
|
)
|
|
with ZipFile(path, "w", compression=ZIP_DEFLATED) as archive:
|
|
archive.writestr("[Content_Types].xml", content_types_xml)
|
|
archive.writestr("_rels/.rels", root_rels_xml)
|
|
archive.writestr("docProps/core.xml", core_xml)
|
|
archive.writestr("docProps/app.xml", app_xml)
|
|
archive.writestr("xl/workbook.xml", workbook_xml)
|
|
archive.writestr("xl/_rels/workbook.xml.rels", workbook_rels_xml)
|
|
archive.writestr("xl/styles.xml", styles_xml)
|
|
archive.writestr("xl/worksheets/sheet1.xml", worksheet_xml)
|