Repository Public
1#!/usr/bin/env python32# =============================================================================3# SP BOUCHER | SOFTWARE & RESEARCH4# ARTIFICIAL INTELLIGENCE • FINANCE • ENGINEERING5# =============================================================================6#7# PROJECT METADATA8# -----------------------------------------------------------------------------9# Project : ORIX Sheet10# File : Packages/SheetIO/Tests/SheetIOTests/Generators/dump_xlsx.py11# Description : Dumps what openpyxl sees in a workbook as JSON on stdout.12# Module : SheetIOTests13# Repository : https://git.spboucher.ai/orix-sheet14# Created : 2026-11-2115# Last Modified : 2026-11-2116#17# =============================================================================18# AUTHOR & PROFESSIONAL AFFILIATION19# =============================================================================20#21# Author : Simon-Pierre Boucher22# Position : Professor of Finance23# Institution : Université du Québec en Outaouais (UQO)24# Department : Département des sciences administratives25# Organization : SP Boucher26#27# Website : https://spboucher.ai28# Git : https://git.spboucher.ai29#30# =============================================================================31# OFFICIAL CONTACT DIRECTORY32# =============================================================================33#34# General : info@spboucher.ai35# Professional : contact@spboucher.ai36# Direct : simon@spboucher.ai37# Research : research@spboucher.ai38# Development : dev@spboucher.ai39# Support : support@spboucher.ai40# Security/Admin : admin@spboucher.ai41# Notifications : notifications@spboucher.ai42#43# Preferred Contact: support@spboucher.ai44#45# =============================================================================46# COPYRIGHT & INTELLECTUAL PROPERTY47# =============================================================================48#49# Copyright (c) 202650# Simon-Pierre Boucher51#52# All rights reserved where applicable.53#54# This file is governed by the applicable project license,55# copyright notices, institutional agreements and relevant law.56#57# Unless expressly authorized by the applicable license or58# rights holder, copying, redistribution, sublicensing,59# publication and commercial exploitation are not permitted.60#61# Existing third-party rights and notices remain unaffected.62#63# =============================================================================64# CONFIDENTIALITY & SECURITY65# =============================================================================66#67# Classification : Public68# Distribution : Public repository; use is governed by the project license69# Security Contact : admin@spboucher.ai70#71# Access to this source does not independently grant permission72# to redistribute or disclose confidential materials.73#74# Do not embed credentials, private keys, access tokens,75# certificates or confidential personal information.76#77# =============================================================================78# LICENSE & MAINTENANCE79# =============================================================================80#81# License : None specified (no license file in this repository)82# Maintainer : Simon-Pierre Boucher83# Technical Contact: dev@spboucher.ai84#85# =============================================================================86# END OF SP BOUCHER SOURCE HEADER87# =============================================================================8889"""Dumps what openpyxl sees in a workbook as JSON on stdout.9091Used by the Swift tests as an independent reader of the files written by SheetIO:92values, formulas, styles, merges, validations, conditional formats, tables,93defined names, freeze panes, sizes, comments, hyperlinks, print settings.94Usage: dump_xlsx.py FILE.xlsx95"""96import datetime97import json98import sys99100import openpyxl101from openpyxl.utils import column_index_from_string, get_column_letter102from openpyxl.worksheet.formula import ArrayFormula103104105def color(c):106 if c is None:107 return None108 if c.type == "rgb" and isinstance(c.rgb, str):109 return c.rgb[-6:].upper()110 if c.type == "theme":111 return "theme:%s:%s" % (c.theme, c.tint)112 if c.type == "indexed":113 return "indexed:%s" % c.indexed114 return None115116117def value(v):118 if isinstance(v, (datetime.datetime, datetime.date, datetime.time)):119 return {"date": v.isoformat()}120 if isinstance(v, ArrayFormula):121 return {"array": v.text, "ref": v.ref}122 if isinstance(v, (int, float, str, bool)) or v is None:123 return v124 return str(v)125126127def dump(path):128 wb = openpyxl.load_workbook(path) # formulas as text129 wbv = openpyxl.load_workbook(path, data_only=True) # cached values130 out = {"sheets": [], "names": {}, "date1904": wb.epoch.year == 1904, "active": wb.index(wb.active),131 "props": {"title": wb.properties.title, "creator": wb.properties.creator, "subject": wb.properties.subject,132 "keywords": wb.properties.keywords, "description": wb.properties.description}}133 for name, dn in wb.defined_names.items():134 out["names"][name] = dn.attr_text135 for ws in wb.worksheets:136 wsv = wbv[ws.title]137 s = {"name": ws.title, "cells": {}, "state": ws.sheet_state, "freeze": ws.freeze_panes,138 "merges": sorted(str(m) for m in ws.merged_cells.ranges),139 "colWidths": {get_column_letter(i): d.width for d in ws.column_dimensions.values() if d.customWidth140 for i in range(d.min or column_index_from_string(d.index), (d.max or column_index_from_string(d.index)) + 1)},141 "colHidden": sorted(get_column_letter(i) for d in ws.column_dimensions.values() if d.hidden142 for i in range(d.min or column_index_from_string(d.index), (d.max or column_index_from_string(d.index)) + 1)),143 "rowHeights": {str(k): d.height for k, d in ws.row_dimensions.items() if d.height is not None},144 "rowHidden": sorted(k for k, d in ws.row_dimensions.items() if d.hidden),145 "autoFilter": ws.auto_filter.ref, "tabColor": color(ws.sheet_properties.tabColor),146 "gridLines": ws.sheet_view.showGridLines, "zoom": ws.sheet_view.zoomScale,147 "validations": [], "conditional": [], "tables": [], "comments": {}, "hyperlinks": {},148 "localNames": {n: d.attr_text for n, d in ws.defined_names.items()},149 "print": {"orientation": ws.page_setup.orientation, "paperSize": ws.page_setup.paperSize,150 "fitToWidth": ws.page_setup.fitToWidth, "fitToHeight": ws.page_setup.fitToHeight,151 "scale": ws.page_setup.scale, "left": ws.page_margins.left, "top": ws.page_margins.top,152 "area": ws.print_area, "titleRows": ws.print_title_rows, "titleCols": ws.print_title_cols,153 "header": ws.oddHeader.center.text if ws.oddHeader else None,154 "gridLines": ws.print_options.gridLines,155 "rowBreaks": [b.id for b in ws.row_breaks.brk]},156 "protected": bool(ws.protection.sheet), "charts": len(ws._charts), "images": len(ws._images)}157 for row in ws.iter_rows():158 for c in row:159 has_style = c.has_style and c.style_id != 0160 if c.value is None and not has_style:161 continue162 e = {"v": value(c.value)}163 if c.data_type == "f" or isinstance(c.value, ArrayFormula):164 e["cached"] = value(wsv[c.coordinate].value)165 if has_style:166 e["style"] = {167 "numFmt": c.number_format, "bold": bool(c.font.b), "italic": bool(c.font.i),168 "strike": bool(c.font.strike), "underline": c.font.u, "font": c.font.name, "size": c.font.sz,169 "color": color(c.font.color),170 "fill": color(c.fill.fgColor) if c.fill.fill_type == "solid" else None,171 "h": c.alignment.horizontal, "v": c.alignment.vertical, "wrap": bool(c.alignment.wrap_text),172 "rotation": c.alignment.text_rotation, "indent": c.alignment.indent,173 "shrink": bool(c.alignment.shrink_to_fit),174 "border": {side: [getattr(c.border, side).style, color(getattr(c.border, side).color)]175 for side in ("left", "right", "top", "bottom") if getattr(c.border, side).style},176 "locked": bool(c.protection.locked), "hidden": bool(c.protection.hidden)}177 if c.comment is not None:178 s["comments"][c.coordinate] = {"author": c.comment.author, "text": c.comment.text}179 if c.hyperlink is not None:180 s["hyperlinks"][c.coordinate] = {"target": c.hyperlink.target, "location": c.hyperlink.location,181 "tooltip": c.hyperlink.tooltip}182 s["cells"][c.coordinate] = e183 # comments / hyperlinks on otherwise empty cells184 for dv in ws.data_validations.dataValidation:185 s["validations"].append({"type": dv.type, "operator": dv.operator, "sqref": str(dv.sqref), "f1": dv.formula1,186 "f2": dv.formula2, "allowBlank": bool(dv.allow_blank), "hideDropDown": bool(dv.showDropDown),187 "errorTitle": dv.errorTitle, "error": dv.error, "promptTitle": dv.promptTitle,188 "prompt": dv.prompt, "errorStyle": dv.errorStyle})189 for rng, rules in ws.conditional_formatting._cf_rules.items():190 for r in rules:191 d = {"sqref": str(rng.sqref), "type": r.type, "operator": r.operator, "priority": r.priority,192 "formulas": list(r.formula), "stopIfTrue": bool(r.stopIfTrue), "text": r.text, "rank": r.rank,193 "percent": bool(r.percent), "bottom": bool(r.bottom), "aboveAverage": r.aboveAverage}194 if r.dxf is not None:195 d["dxf"] = {"bold": r.dxf.font.b if r.dxf.font else None,196 "color": color(r.dxf.font.color) if r.dxf.font and r.dxf.font.color else None,197 "fill": color(r.dxf.fill.bgColor) if r.dxf.fill else None}198 if r.colorScale is not None:199 d["colorScale"] = {"cfvo": [[c.type, c.val] for c in r.colorScale.cfvo],200 "colors": [color(c) for c in r.colorScale.color]}201 if r.dataBar is not None:202 d["dataBar"] = {"cfvo": [[c.type, c.val] for c in r.dataBar.cfvo], "color": color(r.dataBar.color)}203 if r.iconSet is not None:204 d["iconSet"] = {"name": r.iconSet.iconSet, "cfvo": [[c.type, c.val] for c in r.iconSet.cfvo],205 "reverse": bool(r.iconSet.reverse)}206 s["conditional"].append(d)207 for t in ws.tables.values():208 s["tables"].append({"name": t.displayName, "ref": t.ref, "columns": [c.name for c in t.tableColumns],209 "style": t.tableStyleInfo.name if t.tableStyleInfo else None,210 "totals": t.totalsRowCount, "header": t.headerRowCount,211 "totalsFunctions": [c.totalsRowFunction for c in t.tableColumns]})212 out["sheets"].append(s)213 return out214215216if __name__ == "__main__":217 json.dump(dump(sys.argv[1]), sys.stdout, ensure_ascii=False, default=str)218