SPB Git

Repository Public

dump_xlsx.py 11.5 KB 218 lines python

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
Files main
Menu

Theme