excel-automation — Skillopedia

Excel Automation Create professional Excel files, parse complex financial models, and control Excel on macOS. For an image or scan of a paper form that needs editable cells and a printable layout, load Printable forms before creating the workbook. Follow the host's designated spreadsheet authoring tool; use the openpyxl examples below only when that tool is permitted. Quick Start Before a command brings Excel forward or changes its visible window, check user activity and other agents' occupancy. Use only foreground operations covered by the current task's authorization. If the surface is in u…

, ref)\n if not match:\n return ref, 0\n return match.group(1), int(match.group(2))\n\n\ndef extract_cells(zf: zipfile.ZipFile, sheet_path: str,\n shared: list[str]) -> dict[str, any]:\n \"\"\"Extract all cell values from a sheet XML.\"\"\"\n sheet_xml = ET.fromstring(zf.read(sheet_path))\n rows = sheet_xml.findall(f'.//{{{MAIN_NS}}}row')\n\n data = {}\n for row in rows:\n for cell in row.findall(f'{{{MAIN_NS}}}c'):\n ref = cell.get('r')\n cell_type = cell.get('t') # \"s\" = shared string, None = number\n val_el = cell.find(f'{{{MAIN_NS}}}v')\n\n if val_el is not None and val_el.text:\n if cell_type == 's':\n idx = int(val_el.text)\n data[ref] = shared[idx] if idx \u003c len(shared) else f'[SSI:{idx}]'\n elif cell_type == 'b':\n data[ref] = bool(int(val_el.text))\n else:\n try:\n num = float(val_el.text)\n data[ref] = int(num) if num == int(num) else num\n except ValueError:\n data[ref] = val_el.text\n\n return data\n\n\ndef extract_rows(cells: dict, start_row: int = 1,\n end_row: int | None = None) -> list[dict]:\n \"\"\"Organize cells into row-based structure for easier consumption.\"\"\"\n # Determine row range\n all_rows = set()\n for ref in cells:\n _, row_num = parse_cell_ref(ref)\n if row_num > 0:\n all_rows.add(row_num)\n\n if not all_rows:\n return []\n\n start = max(start_row, min(all_rows))\n end = min(end_row, max(all_rows)) if end_row else max(all_rows)\n\n rows = []\n for r in range(start, end + 1):\n row_cells = {\n ref: val for ref, val in cells.items()\n if parse_cell_ref(ref)[1] == r\n }\n if row_cells:\n rows.append({'row': r, 'cells': row_cells})\n\n return rows\n\n\ndef fix_defined_names(input_path: str, output_path: str) -> int:\n \"\"\"\n Remove corrupted DefinedNames entries (containing \"Formula removed\")\n and repackage the file.\n\n Returns the number of removed entries.\n \"\"\"\n import shutil\n import tempfile\n\n with tempfile.TemporaryDirectory() as tmp_str:\n tmp = Path(tmp_str)\n\n # Extract\n with zipfile.ZipFile(input_path, 'r') as zf:\n zf.extractall(tmp)\n\n # Fix workbook.xml\n wb_path = tmp / 'xl' / 'workbook.xml'\n tree = ET.parse(wb_path)\n root = tree.getroot()\n\n ns = {'main': MAIN_NS}\n defined_names = root.find('.//main:definedNames', ns)\n removed = 0\n if defined_names is not None:\n for name in list(defined_names):\n if name.text and \"Formula removed\" in name.text:\n defined_names.remove(name)\n removed += 1\n\n tree.write(wb_path, encoding='utf-8', xml_declaration=True)\n\n # Repackage\n with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zf:\n for fp in tmp.rglob('*'):\n if fp.is_file():\n zf.write(fp, fp.relative_to(tmp))\n\n return removed\n\n\n# ── CLI Entry Point ──────────────────────────────────────────────────\n\ndef main():\n if len(sys.argv) \u003c 2:\n print(\"Usage: parse_complex_excel.py \u003cexcel_file> [sheet_name]\")\n print(\"\\nExamples:\")\n print(\" parse_complex_excel.py model.xlsm # List all sheets\")\n print(\" parse_complex_excel.py model.xlsm DCF # Extract DCF sheet\")\n print(\" parse_complex_excel.py model.xlsm --fix # Fix corrupted names\")\n sys.exit(1)\n\n file_path = sys.argv[1]\n path = Path(file_path)\n\n if not path.exists():\n print(f\"File not found: {file_path}\")\n sys.exit(1)\n\n # Verify format\n fmt = verify_format(file_path)\n print(f\"File: {path.name}\")\n print(f\"Format: {fmt}\")\n\n # \"Microsoft Excel 2007+\" = ZIP-based xlsx/xlsm\n # \"Zip archive\" = generic ZIP (also valid)\n # \"Composite Document File\" = old BIFF .xls format\n is_zip_based = any(kw in fmt.lower() for kw in ['zip', 'excel 2007', 'ooxml'])\n if not is_zip_based:\n print(\"WARNING: File is not ZIP-based xlsx/xlsm.\")\n if 'composite' in fmt.lower() or 'biff' in fmt.lower():\n print(\"This appears to be an old .xls (BIFF format). Use xlrd instead.\")\n else:\n print(f\"Unexpected format. If it should be xlsx/xlsm, check the file.\")\n sys.exit(1)\n\n # Handle --fix flag\n if len(sys.argv) > 2 and sys.argv[2] == '--fix':\n out_path = str(path.with_stem(path.stem + '_fixed'))\n removed = fix_defined_names(file_path, out_path)\n print(f\"Removed {removed} corrupted DefinedNames entries.\")\n print(f\"Fixed file: {out_path}\")\n sys.exit(0)\n\n with zipfile.ZipFile(file_path, 'r') as zf:\n # List sheets\n sheets = list_sheets(zf)\n print(f\"\\nSheets ({len(sheets)}):\")\n for i, s in enumerate(sheets, 1):\n print(f\" {i}. {s['name']} → {s['path']}\")\n\n # If sheet name given, extract it\n if len(sys.argv) > 2:\n sheet_name = sys.argv[2]\n print(f\"\\nExtracting sheet: {sheet_name}\")\n\n sheet_path = get_sheet_path(zf, sheet_name)\n shared = build_shared_strings(zf)\n cells = extract_cells(zf, sheet_path, shared)\n\n print(f\"Total cells: {len(cells)}\")\n\n # Show first 20 rows\n rows = extract_rows(cells, start_row=1, end_row=20)\n for row in rows:\n print(f\" Row {row['row']:3d}: \", end=\"\")\n items = sorted(row['cells'].items(),\n key=lambda x: parse_cell_ref(x[0]))\n for ref, val in items[:8]:\n val_str = str(val)[:25]\n print(f\"{ref}={val_str} \", end=\"\")\n print()\n\n\nif __name__ == \"__main__\":\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":9307,"content_sha256":"581d8d5319ef49da20ba9aa35f7031f9671163345598614874b4cc5ec54a23b8"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Excel Automation","type":"text"}]},{"type":"paragraph","content":[{"text":"Create professional Excel files, parse complex financial models, and control Excel on macOS.","type":"text"}]},{"type":"paragraph","content":[{"text":"For an image or scan of a paper form that needs editable cells and a printable layout, load ","type":"text"},{"text":"Printable forms","type":"text","marks":[{"type":"link","attrs":{"href":"references/printable-forms.md","title":null}}]},{"text":" before creating the workbook. Follow the host's designated spreadsheet authoring tool; use the openpyxl examples below only when that tool is permitted.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"paragraph","content":[{"text":"Before a command brings Excel forward or changes its visible window, check user activity and other agents' occupancy. Use only foreground operations covered by the current task's authorization. If the surface is in use, occupancy is unknown, or unexpected input occurs, stop input and continue independent background work.","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Create a formatted Excel report\nuv run --with openpyxl scripts/create_formatted_excel.py output.xlsx\n\n# Parse a complex xlsm that openpyxl can't handle\nuv run scripts/parse_complex_excel.py model.xlsm # List sheets\nuv run scripts/parse_complex_excel.py model.xlsm \"DCF\" # Extract a sheet\nuv run scripts/parse_complex_excel.py model.xlsm --fix # Fix corrupted names\n\n# Control Excel via AppleScript (with timeout to prevent hangs)\ntimeout 5 osascript -e 'tell application \"Microsoft Excel\" to activate'","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Capability","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Tool","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"When to Use","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Create","type":"text","marks":[{"type":"strong"}]},{"text":" formatted Excel","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"openpyxl","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Reports, mockups, dashboards","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Parse","type":"text","marks":[{"type":"strong"}]},{"text":" complex xlsm/xlsx","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"zipfile","type":"text","marks":[{"type":"code_inline"}]},{"text":" + ","type":"text"},{"text":"xml.etree","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Financial models, VBA workbooks, >1MB files","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Control","type":"text","marks":[{"type":"strong"}]},{"text":" Excel window","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"AppleScript (","type":"text"},{"text":"osascript","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Zoom, scroll, select cells programmatically","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Tool Selection Decision Tree","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"Is the file simple (data export, no VBA, \u003c1MB)?\n├─ YES → openpyxl or pandas\n└─ NO\n ├─ Is it .xlsm or from investment bank / >1MB?\n │ └─ YES → zipfile + xml.etree.ElementTree (stdlib)\n └─ Is it truly .xls (BIFF format)?\n └─ YES → xlrd","type":"text"}]},{"type":"paragraph","content":[{"text":"Signals of \"complex\" Excel","type":"text","marks":[{"type":"strong"}]},{"text":": file >1MB, ","type":"text"},{"text":".xlsm","type":"text","marks":[{"type":"code_inline"}]},{"text":" extension, from investment bank/broker, contains VBA macros.","type":"text"}]},{"type":"paragraph","content":[{"text":"IMPORTANT","type":"text","marks":[{"type":"strong"}]},{"text":": Always run ","type":"text"},{"text":"file \u003cpath>","type":"text","marks":[{"type":"code_inline"}]},{"text":" first — extensions lie. A ","type":"text"},{"text":".xls","type":"text","marks":[{"type":"code_inline"}]},{"text":" file may actually be a ZIP-based xlsx.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Creating Excel Files (openpyxl)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Professional Color Convention (Investment Banking Standard)","type":"text"}]},{"type":"paragraph","content":[{"text":"Apply these colors to financial reports using that convention. For a paper form, preserve its intended print appearance; do not add input colors by default.","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Color","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"RGB Code","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Meaning","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Blue","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"0000FF","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"User input / assumption","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Black","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"000000","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Calculated value","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Green","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"008000","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Cross-sheet reference","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"White on dark blue","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"FFFFFF","type":"text","marks":[{"type":"code_inline"}]},{"text":" on ","type":"text"},{"text":"4472C4","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Section headers","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Dark blue text","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1F4E79","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Title","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Core Formatting Patterns","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"from openpyxl.styles import Font, PatternFill, Border, Side, Alignment\n\n# Fonts\nBLUE_FONT = Font(color=\"0000FF\", size=10, name=\"Calibri\")\nBLACK_FONT_BOLD = Font(color=\"000000\", size=10, name=\"Calibri\", bold=True)\nGREEN_FONT = Font(color=\"008000\", size=10, name=\"Calibri\")\nHEADER_FONT = Font(color=\"FFFFFF\", size=12, name=\"Calibri\", bold=True)\n\n# Fills\nDARK_BLUE_FILL = PatternFill(\"solid\", fgColor=\"4472C4\")\nLIGHT_BLUE_FILL = PatternFill(\"solid\", fgColor=\"D9E1F2\")\nINPUT_GREEN_FILL = PatternFill(\"solid\", fgColor=\"E2EFDA\")\nLIGHT_GRAY_FILL = PatternFill(\"solid\", fgColor=\"F2F2F2\")\n\n# Borders\nTHIN_BORDER = Border(bottom=Side(style=\"thin\", color=\"B2B2B2\"))\nBOTTOM_DOUBLE = Border(bottom=Side(style=\"double\", color=\"000000\"))","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Number Format Codes","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Format","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Code","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Example","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Currency","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'$#,##0'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"$1,234","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Currency with decimals","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'$#,##0.00'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"$1,234.56","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Percentage","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'0.0%'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"12.3%","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Percentage (2 decimal)","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'0.00%'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"12.34%","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Number with commas","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'#,##0'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1,234","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Multiplier","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"'0.0x'","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"1.5x","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Conditional Formatting (Sensitivity Tables)","type":"text"}]},{"type":"paragraph","content":[{"text":"Red-to-green gradient for sensitivity analysis:","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"from openpyxl.formatting.rule import ColorScaleRule\n\nrule = ColorScaleRule(\n start_type=\"min\", start_color=\"F8696B\", # Red (low)\n mid_type=\"percentile\", mid_value=50, mid_color=\"FFEB84\", # Yellow (mid)\n end_type=\"max\", end_color=\"63BE7B\" # Green (high)\n)\nws.conditional_formatting.add(f\"B2:F6\", rule)","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Execution","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"uv run --with openpyxl scripts/create_formatted_excel.py","type":"text"}]},{"type":"paragraph","content":[{"text":"Full template script: See ","type":"text"},{"text":"scripts/create_formatted_excel.py","type":"text","marks":[{"type":"code_inline"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Parsing Complex Excel (zipfile + xml)","type":"text"}]},{"type":"paragraph","content":[{"text":"When openpyxl fails on complex xlsm files (corrupted DefinedNames, complex VBA), use stdlib directly.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"XLSX Internal ZIP Structure","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":""},"content":[{"text":"file.xlsx (ZIP archive)\n├── [Content_Types].xml\n├── xl/\n│ ├── workbook.xml ← Sheet names + order\n│ ├── sharedStrings.xml ← All text values (lookup table)\n│ ├── worksheets/\n│ │ ├── sheet1.xml ← Cell data for sheet 1\n│ │ ├── sheet2.xml ← Cell data for sheet 2\n│ │ └── ...\n│ └── _rels/\n│ └── workbook.xml.rels ← Maps rId → sheetN.xml\n└── _rels/.rels","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Sheet Name Resolution","type":"text"}]},{"type":"paragraph","content":[{"text":"Sheet names in ","type":"text"},{"text":"workbook.xml","type":"text","marks":[{"type":"code_inline"}]},{"text":" link to physical files via ","type":"text"},{"text":"_rels/workbook.xml.rels","type":"text","marks":[{"type":"code_inline"}]},{"text":":","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"import zipfile\nimport xml.etree.ElementTree as ET\n\nMAIN_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'\nREL_NS = 'http://schemas.openxmlformats.org/officeDocument/2006/relationships'\nRELS_NS = 'http://schemas.openxmlformats.org/package/2006/relationships'\n\ndef get_sheet_path(zf, sheet_name):\n \"\"\"Resolve sheet name to physical XML file path inside ZIP.\"\"\"\n # Step 1: workbook.xml → find rId for the sheet name\n wb_xml = ET.fromstring(zf.read('xl/workbook.xml'))\n sheets = wb_xml.findall(f'.//{{{MAIN_NS}}}sheet')\n rid = None\n for s in sheets:\n if s.get('name') == sheet_name:\n rid = s.get(f'{{{REL_NS}}}id')\n break\n if not rid:\n raise ValueError(f\"Sheet '{sheet_name}' not found\")\n\n # Step 2: workbook.xml.rels → map rId to file path\n rels_xml = ET.fromstring(zf.read('xl/_rels/workbook.xml.rels'))\n for rel in rels_xml.findall(f'{{{RELS_NS}}}Relationship'):\n if rel.get('Id') == rid:\n return 'xl/' + rel.get('Target')\n\n raise ValueError(f\"No file mapping for {rid}\")","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Cell Data Extraction","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"def extract_cells(zf, sheet_path):\n \"\"\"Extract all cell values from a sheet XML.\"\"\"\n # Build shared strings lookup\n shared = []\n try:\n ss_xml = ET.fromstring(zf.read('xl/sharedStrings.xml'))\n for si in ss_xml.findall(f'{{{MAIN_NS}}}si'):\n texts = si.itertext()\n shared.append(''.join(texts))\n except KeyError:\n pass # No shared strings\n\n # Parse sheet cells\n sheet_xml = ET.fromstring(zf.read(sheet_path))\n rows = sheet_xml.findall(f'.//{{{MAIN_NS}}}row')\n\n data = {}\n for row in rows:\n for cell in row.findall(f'{{{MAIN_NS}}}c'):\n ref = cell.get('r') # e.g., \"A1\"\n cell_type = cell.get('t') # \"s\" = shared string, None = number\n val_el = cell.find(f'{{{MAIN_NS}}}v')\n\n if val_el is not None and val_el.text:\n if cell_type == 's':\n data[ref] = shared[int(val_el.text)]\n else:\n try:\n data[ref] = float(val_el.text)\n except ValueError:\n data[ref] = val_el.text\n return data","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Fixing Corrupted DefinedNames","type":"text"}]},{"type":"paragraph","content":[{"text":"Investment bank xlsm files often have corrupted ","type":"text"},{"text":"\u003cdefinedName>","type":"text","marks":[{"type":"code_inline"}]},{"text":" entries containing \"Formula removed\":","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"python"},"content":[{"text":"def fix_defined_names(zf_in_path, zf_out_path):\n \"\"\"Remove corrupted DefinedNames and repackage.\"\"\"\n import shutil, tempfile\n with tempfile.TemporaryDirectory() as tmp:\n tmp = Path(tmp)\n with zipfile.ZipFile(zf_in_path, 'r') as zf:\n zf.extractall(tmp)\n\n wb_xml_path = tmp / 'xl' / 'workbook.xml'\n tree = ET.parse(wb_xml_path)\n root = tree.getroot()\n\n ns = {'main': MAIN_NS}\n defined_names = root.find('.//main:definedNames', ns)\n if defined_names is not None:\n for name in list(defined_names):\n if name.text and \"Formula removed\" in name.text:\n defined_names.remove(name)\n\n tree.write(wb_xml_path, encoding='utf-8', xml_declaration=True)\n\n with zipfile.ZipFile(zf_out_path, 'w', zipfile.ZIP_DEFLATED) as zf:\n for fp in tmp.rglob('*'):\n if fp.is_file():\n zf.write(fp, fp.relative_to(tmp))","type":"text"}]},{"type":"paragraph","content":[{"text":"Full template script: See ","type":"text"},{"text":"scripts/parse_complex_excel.py","type":"text","marks":[{"type":"code_inline"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Controlling Excel on macOS (AppleScript)","type":"text"}]},{"type":"paragraph","content":[{"text":"All commands verified on macOS with Microsoft Excel.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Verified Commands","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Activate Excel (bring to front)\nosascript -e 'tell application \"Microsoft Excel\" to activate'\n\n# Open a file\nosascript -e 'tell application \"Microsoft Excel\" to open POSIX file \"/path/to/file.xlsx\"'\n\n# Set zoom level (percentage)\nosascript -e 'tell application \"Microsoft Excel\"\n set zoom of active window to 120\nend tell'\n\n# Scroll to specific row\nosascript -e 'tell application \"Microsoft Excel\"\n set scroll row of active window to 45\nend tell'\n\n# Scroll to specific column\nosascript -e 'tell application \"Microsoft Excel\"\n set scroll column of active window to 3\nend tell'\n\n# Select a cell range\nosascript -e 'tell application \"Microsoft Excel\"\n select range \"A1\" of active sheet\nend tell'\n\n# Select a specific sheet by name\nosascript -e 'tell application \"Microsoft Excel\"\n activate object sheet \"DCF\" of active workbook\nend tell'","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Timing and Timeout","type":"text"}]},{"type":"paragraph","content":[{"text":"Always add ","type":"text"},{"text":"sleep 1","type":"text","marks":[{"type":"code_inline"}]},{"text":" between AppleScript commands and subsequent operations (e.g., screenshot) to allow UI rendering.","type":"text"}]},{"type":"paragraph","content":[{"text":"IMPORTANT","type":"text","marks":[{"type":"strong"}]},{"text":": ","type":"text"},{"text":"osascript","type":"text","marks":[{"type":"code_inline"}]},{"text":" will hang indefinitely if Excel is not running or not responding. Always wrap with ","type":"text"},{"text":"timeout","type":"text","marks":[{"type":"code_inline"}]},{"text":":","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Safe pattern: 5-second timeout\ntimeout 5 osascript -e 'tell application \"Microsoft Excel\" to activate'\n\n# Check exit code: 124 = timed out\nif [ $? -eq 124 ]; then\n echo \"Excel not responding — is it running?\"\nfi","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Mistakes","type":"text"}]},{"type":"table","attrs":{"layout":null},"content":[{"type":"tr","content":[{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Mistake","type":"text"}]}]},{"type":"th","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Correction","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"openpyxl fails on complex xlsm → try monkey-patching","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Switch to ","type":"text"},{"text":"zipfile","type":"text","marks":[{"type":"code_inline"}]},{"text":" + ","type":"text"},{"text":"xml.etree","type":"text","marks":[{"type":"code_inline"}]},{"text":" immediately","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Count Chinese characters with ","type":"text"},{"text":"wc -c","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"wc -m","type":"text","marks":[{"type":"code_inline"}]},{"text":" (chars, not bytes; Chinese = 3 bytes/char)","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Trust file extension","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Run ","type":"text"},{"text":"file \u003cpath>","type":"text","marks":[{"type":"code_inline"}]},{"text":" first to confirm actual format","type":"text"}]}]}]},{"type":"tr","content":[{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"openpyxl ","type":"text"},{"text":"load_workbook","type":"text","marks":[{"type":"code_inline"}]},{"text":" hangs on large xlsm","type":"text"}]}]},{"type":"td","attrs":{"colspan":1,"rowspan":1,"colwidth":null,"alignment":""},"content":[{"type":"paragraph","content":[{"text":"Use ","type":"text"},{"text":"zipfile","type":"text","marks":[{"type":"code_inline"}]},{"text":" for targeted extraction instead of loading entire workbook","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Important Notes","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Execute Python scripts with ","type":"text"},{"text":"uv run --with openpyxl","type":"text","marks":[{"type":"code_inline"}]},{"text":" (never use system Python)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"LibreOffice (","type":"text"},{"text":"soffice --headless","type":"text","marks":[{"type":"code_inline"}]},{"text":") can convert formats and recalculate formulas","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Detailed formatting reference: See ","type":"text"},{"text":"references/formatting-reference.md","type":"text","marks":[{"type":"code_inline"}]}]}]}]}]},"metadata":{"date":"2026-11-02","name":"excel-automation","author":"@skillopedia","source":{"stars":1511,"repo_name":"claude-code-skills","origin_url":"https://github.com/daymade/claude-code-skills/blob/HEAD/daymade-docs/excel-automation/SKILL.md","repo_owner":"daymade","body_sha256":"fd8864c6a9c992629231f35268d69abf55f130491683e2fcd7aaba528d7aac38","cluster_key":"5fb69ae57cfbe08391b267abc2d461c8224764651a6e2ac77bb1139588ca0cb6","clean_bundle":{"format":"clean-skill-bundle-v1","source":"daymade/claude-code-skills/daymade-docs/excel-automation/SKILL.md","text_bytes":30055,"source_commit":"0633c92cdf1192ebed0b550384ea2a56f0abd520","skills_sh_rank":10102,"attachment_count":4,"text_attachments":4,"attachment_storage":"skill_attachments","binary_attachments":0},"cluster_size":1,"skill_md_path":"daymade-docs/excel-automation/SKILL.md","import_metadata":{"date":"2026-11-02","author":"@skillopedia","version":"v1","category":"documents-office","category_label":"Documents"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"documents-office","import_tag":"skills-sh-top20k-2026-11","description":"Creates formatted Excel reports with openpyxl, parses complex xlsm financial models with stdlib zipfile+xml when openpyxl fails, and controls Excel windows on macOS via AppleScript. Reconstructs images or scans of paper forms as editable XLSX files with printable layouts.","disable-model-invocation":true}},"renderedAt":1793666580934}

Important: agents should read /llm.txt, /llms.txt, or /.well-known/skills.json to discover the public Skillopedia API.