SPB Git

Repository Public

gen_builtin_numfmt_xlsx.py 7.7 KB 120 lines python

1#!/opt/homebrew/bin/python32# =============================================================================3#                  SP BOUCHER | SOFTWARE & RESEARCH4#             ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5# =============================================================================6#7# PROJECT METADATA8# -----------------------------------------------------------------------------9# Project          : ORIX Sheet10# File             : Packages/SheetIO/Tests/SheetIOTests/Generators/gen_builtin_numfmt_xlsx.py11# Description      : Writes builtin_numfmt.xlsx: cells that rely on built-in12#                    number-format ids only (no <numFmts>).13# Module           : SheetIOTests14# Repository       : https://git.spboucher.ai/orix-sheet15# Created          : 2026-11-2116# Last Modified    : 2026-11-2117#18# =============================================================================19# AUTHOR & PROFESSIONAL AFFILIATION20# =============================================================================21#22# Author           : Simon-Pierre Boucher23# Position         : Professor of Finance24# Institution      : Université du Québec en Outaouais (UQO)25# Department       : Département des sciences administratives26# Organization     : SP Boucher27#28# Website          : https://spboucher.ai29# Git              : https://git.spboucher.ai30#31# =============================================================================32# OFFICIAL CONTACT DIRECTORY33# =============================================================================34#35# General          : info@spboucher.ai36# Professional     : contact@spboucher.ai37# Direct           : simon@spboucher.ai38# Research         : research@spboucher.ai39# Development      : dev@spboucher.ai40# Support          : support@spboucher.ai41# Security/Admin   : admin@spboucher.ai42# Notifications    : notifications@spboucher.ai43#44# Preferred Contact: support@spboucher.ai45#46# =============================================================================47# COPYRIGHT & INTELLECTUAL PROPERTY48# =============================================================================49#50# Copyright (c) 202651# Simon-Pierre Boucher52#53# All rights reserved where applicable.54#55# This file is governed by the applicable project license,56# copyright notices, institutional agreements and relevant law.57#58# Unless expressly authorized by the applicable license or59# rights holder, copying, redistribution, sublicensing,60# publication and commercial exploitation are not permitted.61#62# Existing third-party rights and notices remain unaffected.63#64# =============================================================================65# CONFIDENTIALITY & SECURITY66# =============================================================================67#68# Classification   : Public69# Distribution     : Public repository; use is governed by the project license70# Security Contact : admin@spboucher.ai71#72# Access to this source does not independently grant permission73# to redistribute or disclose confidential materials.74#75# Do not embed credentials, private keys, access tokens,76# certificates or confidential personal information.77#78# =============================================================================79# LICENSE & MAINTENANCE80# =============================================================================81#82# License          : None specified (no license file in this repository)83# Maintainer       : Simon-Pierre Boucher84# Technical Contact: dev@spboucher.ai85#86# =============================================================================87# END OF SP BOUCHER SOURCE HEADER88# =============================================================================8990"""Writes builtin_numfmt.xlsx: cells that rely on built-in number-format ids only (no <numFmts>).9192Column A holds the numFmtId, column B a value formatted with that id. Used by93BuiltinNumberFormatTests, and as the input of the LibreOffice cross-check described in94COMPATIBILITY.md:9596    python3 gen_builtin_numfmt_xlsx.py /tmp/builtin_numfmt.xlsx97    soffice -env:UserInstallation=file:///tmp/lo_profile_fix --headless \98        --convert-to "csv:Text - txt - csv (StarCalc):9,34,76,1,,1033,false,true" /tmp/builtin_numfmt.xlsx99100With an en-US LibreOffice profile the displayed values are: 14 -> 11/21/2026,10122 -> 11/21/2026 13:30, 37/38 -> (1,235), 39/40 -> (1,234.50), 45 -> 01:23, 46 -> 36:00:00,10247 -> 01:23.3, 48 -> 12.3E+3, 49 -> 42.103"""104import sys, zipfile105out = sys.argv[1]106ids = [14, 22, 37, 38, 39, 40, 45, 46, 47, 48, 49, 5, 7, 42, 44, 27, 30, 36]107vals = {14: 46347, 22: 46347.5625, 37: -1234.5, 38: -1234.5, 39: -1234.5, 40: -1234.5, 45: 0.5 + 83.26/86400, 46: 1.5, 47: 0.5 + 83.26/86400, 48: 12345.678, 49: 42, 5: -1234.5, 7: -1234.5, 42: 1234.5, 44: 1234.5, 27: 46347, 30: 46347, 36: 46347}108MAIN = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'109xfs = '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>' + ''.join('<xf numFmtId="%d" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>' % i for i in ids)110styles = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><styleSheet xmlns="%s"><fonts count="1"><font><sz val="11"/><name val="Calibri"/></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="%d">%s</cellXfs></styleSheet>' % (MAIN, len(ids)+1, xfs)111rows = ''.join('<row r="%d"><c r="A%d"><v>%d</v></c><c r="B%d" s="%d"><v>%r</v></c></row>' % (n+1, n+1, i, n+1, n+1, vals[i]) for n, i in enumerate(ids))112sheet = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><worksheet xmlns="%s"><cols><col min="2" max="2" width="30" customWidth="1"/></cols><sheetData>%s</sheetData></worksheet>' % (MAIN, rows)113with zipfile.ZipFile(out, "w", zipfile.ZIP_DEFLATED) as z:114    z.writestr('[Content_Types].xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><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/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/></Types>')115    z.writestr('_rels/.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><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>')116    z.writestr('xl/workbook.xml', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><workbook xmlns="%s" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="S" sheetId="1" r:id="rId1"/></sheets></workbook>' % MAIN)117    z.writestr('xl/_rels/workbook.xml.rels', '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><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/styles" Target="styles.xml"/></Relationships>')118    z.writestr('xl/styles.xml', styles)119    z.writestr('xl/worksheets/sheet1.xml', sheet)120
Files main
Menu

Theme