xlsx_read.py 5.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160
  1. #!/usr/bin/env python3
  2. """Read an .xlsx workbook: inventory, JSON/CSV dumps, formula listing.
  3. Modes (pick one):
  4. --sheets JSON inventory: sheet names, dimensions, row/col counts
  5. --json dump one sheet's rows as a JSON array of arrays
  6. --csv dump one sheet as CSV to stdout or --out
  7. --formulas JSON list of formula cells {"cell", "formula", "cached"}
  8. --notes JSON list of cell notes/comments across sheets
  9. --names JSON map of workbook defined names
  10. Options:
  11. --sheet NAME sheet to dump (default: active sheet)
  12. --data-only load cached formula RESULTS instead of formula strings.
  13. Caveat: openpyxl never computes formulas; cached values
  14. exist only if the file was last saved by Excel/LibreOffice.
  15. --encoding ENC encoding for --csv --out files (default utf-8)
  16. --out PATH write --csv output to a file instead of stdout
  17. Usage:
  18. xlsx_read.py book.xlsx --sheets
  19. xlsx_read.py book.xlsx --json --sheet Data
  20. xlsx_read.py book.xlsx --csv --sheet Data --out data.csv
  21. xlsx_read.py book.xlsx --formulas
  22. xlsx_read.py book.xlsx --notes
  23. xlsx_read.py book.xlsx --names
  24. """
  25. from __future__ import annotations
  26. import argparse
  27. import csv
  28. import json
  29. import sys
  30. from datetime import date, datetime, time
  31. from openpyxl import load_workbook
  32. def jsonable(value):
  33. if isinstance(value, (datetime, date, time)):
  34. return value.isoformat()
  35. return value
  36. def sheet_rows(ws):
  37. return [[jsonable(c) for c in row] for row in ws.iter_rows(values_only=True)]
  38. def cmd_sheets(wb):
  39. info = []
  40. for ws in wb.worksheets:
  41. info.append({
  42. "name": ws.title,
  43. "dimensions": ws.dimensions,
  44. "max_row": ws.max_row,
  45. "max_col": ws.max_column,
  46. "merged": [str(r) for r in ws.merged_cells.ranges],
  47. "charts": len(getattr(ws, "_charts", [])),
  48. "freeze_panes": ws.freeze_panes,
  49. "autofilter": ws.auto_filter.ref,
  50. "tables": {t.displayName: t.ref for t in ws.tables.values()},
  51. "protected": bool(ws.protection.sheet),
  52. })
  53. names = {name: dn.attr_text for name, dn in wb.defined_names.items()}
  54. print(json.dumps({"sheets": info, "defined_names": names},
  55. ensure_ascii=False, indent=2))
  56. def cmd_notes(wb, sheet):
  57. out = []
  58. sheets = [sheet] if sheet else wb.sheetnames
  59. for name in sheets:
  60. for row in wb[name].iter_rows():
  61. for cell in row:
  62. if cell.comment is not None:
  63. out.append({"sheet": name, "cell": cell.coordinate,
  64. "text": cell.comment.text,
  65. "author": cell.comment.author})
  66. print(json.dumps({"notes": out}, ensure_ascii=False, indent=2))
  67. def cmd_names(wb):
  68. names = {name: dn.attr_text for name, dn in wb.defined_names.items()}
  69. print(json.dumps({"defined_names": names}, ensure_ascii=False, indent=2))
  70. def cmd_formulas(path, sheet):
  71. wb_f = load_workbook(path, data_only=False)
  72. wb_v = load_workbook(path, data_only=True)
  73. out = []
  74. sheets = [sheet] if sheet else wb_f.sheetnames
  75. for name in sheets:
  76. ws_f, ws_v = wb_f[name], wb_v[name]
  77. for row in ws_f.iter_rows():
  78. for cell in row:
  79. if isinstance(cell.value, str) and cell.value.startswith("="):
  80. out.append({
  81. "sheet": name,
  82. "cell": cell.coordinate,
  83. "formula": cell.value,
  84. "cached": jsonable(ws_v[cell.coordinate].value),
  85. })
  86. print(json.dumps({"formulas": out}, ensure_ascii=False, indent=2))
  87. def main(argv=None):
  88. ap = argparse.ArgumentParser(description="Read/inspect an .xlsx workbook.")
  89. ap.add_argument("file", help="path to .xlsx file")
  90. mode = ap.add_mutually_exclusive_group(required=True)
  91. mode.add_argument("--sheets", action="store_true")
  92. mode.add_argument("--json", action="store_true")
  93. mode.add_argument("--csv", action="store_true")
  94. mode.add_argument("--formulas", action="store_true")
  95. mode.add_argument("--notes", action="store_true")
  96. mode.add_argument("--names", action="store_true")
  97. ap.add_argument("--sheet", help="sheet name (default: active)")
  98. ap.add_argument("--data-only", action="store_true",
  99. help="return cached formula results (see module docstring)")
  100. ap.add_argument("--encoding", default="utf-8")
  101. ap.add_argument("--out", help="output file for --csv")
  102. args = ap.parse_args(argv)
  103. if args.formulas:
  104. cmd_formulas(args.file, args.sheet)
  105. return 0
  106. wb = load_workbook(args.file, data_only=args.data_only)
  107. if args.sheets:
  108. cmd_sheets(wb)
  109. return 0
  110. if args.notes:
  111. cmd_notes(wb, args.sheet)
  112. return 0
  113. if args.names:
  114. cmd_names(wb)
  115. return 0
  116. ws = wb[args.sheet] if args.sheet else wb.active
  117. rows = sheet_rows(ws)
  118. if args.json:
  119. print(json.dumps({"sheet": ws.title, "rows": rows}, ensure_ascii=False))
  120. else: # --csv
  121. if args.out:
  122. with open(args.out, "w", newline="", encoding=args.encoding) as fh:
  123. csv.writer(fh).writerows(
  124. [["" if v is None else v for v in r] for r in rows])
  125. print(json.dumps({"ok": True, "out": args.out, "rows": len(rows)}))
  126. else:
  127. w = csv.writer(sys.stdout)
  128. for r in rows:
  129. w.writerow(["" if v is None else v for v in r])
  130. return 0
  131. if __name__ == "__main__":
  132. try:
  133. sys.exit(main())
  134. except Exception as exc:
  135. print(json.dumps({"ok": False, "error": str(exc)}), file=sys.stderr)
  136. sys.exit(1)