xlsx

name xlsx description Create, read, edit Excel .xlsx workbooks and CSVs. version 1.1.0 author Nous Research license MIT platforms ["linux","macos","windows"] metadata {"hermes":{"tags":["excel","spreadsheet","xlsx","csv","openpyxl","productivity"],"category":"productivity","related_skills":["docx","pdf","powerpoint"]}} Xlsx Skill Work with Excel .xlsx workbooks using Python and openpyxl: build styled multi-sheet workbooks with formulas and charts, inspect or dump existing files, edit cells and structure, and convert to/from CSV. All helper scripts are argparse CLIs that print JSON and use explicit UTF-8 I/O. When to Use Creating .xlsx reports: multiple sheets, number formats, styling, merged cells, freeze panes, autofilter, conditional formatting, charts, data-validation dropdowns, native Excel tables, defined names, hyperlinks, cell notes, sheet protection. Reading a workbook: sheet inventory, dumping data as JSON or CSV, listing formulas vs cached values, notes, defined names, tables. Editing existing files: set cells, append rows, insert/delete rows/columns (reference-aware via xlsx_restructure.py ), copy/rename sheets, tables, names, notes, protection. Recalculating formulas headlessly via LibreOffice ( xlsx_recalc.py ). CSV interop with type inference and non-UTF-8 encodings. Not for the legacy .xls binary format (use LibreOffice to convert first: soffice --headless --convert-to xlsx old.xls ). Prerequisites Python 3.10+ with openpyxl ( pip install openpyxl ). No other third-party packages are needed; everything else is stdlib. Optional: LibreOffice ( soffice ) for headless recalculation or format conversion. How to Run Run the helper scripts with the terminal tool from this skill's scripts/ directory (every script supports --help ): python scripts/xlsx_create.py spec.json report.xlsx # build from JSON spec python scripts/xlsx_read.py report.xlsx --sheets # inventory python scripts/xlsx_read.py report.xlsx --json --sheet Data python scripts/xlsx_read.py report.xlsx --formulas python scripts/xlsx_edit.py report.xlsx --sheet Data -- set B2=42 --recalc python scripts/xlsx_restructure.py report.xlsx --sheet Data --insert-rows 3:2 python scripts/xlsx_recalc.py report.xlsx python scripts/csv_to_xlsx.py data.csv out.xlsx --encoding utf-8 python scripts/xlsx_to_csv.py report.xlsx out.csv --sheet Data Author the JSON spec with write_file , inspect script JSON output with read_file or directly from stdout. Quick Reference Task Command Create workbook from spec xlsx_create.py spec.json out.xlsx Sheet names + dimensions xlsx_read.py f.xlsx --sheets Dump sheet as JSON xlsx_read.py f.xlsx --json --sheet S Dump sheet as CSV xlsx_read.py f.xlsx --csv --out d.csv List formulas + cached values xlsx_read.py f.xlsx --formulas Set a cell / formula xlsx_edit.py f.xlsx --set "A1==SUM(B:B)" Append a row xlsx_edit.py f.xlsx --append '[1,"x",true]' Insert 2 rows, refs NOT shifted xlsx_edit.py f.xlsx --insert-rows 3:2 Insert 2 rows, refs shifted xlsx_restructure.py f.xlsx --insert-rows 3:2 Delete a column, refs shifted xlsx_restructure.py f.xlsx --delete-cols B Create a native table xlsx_edit.py f.xlsx --add-table Sales:A1:C9 Append inside a table --table-append 'Sales=["West",5]' List tables xlsx_edit.py f.xlsx --list-tables Defined names --define-name "Rates='Data'!$B$2:$B$9" / --delete-name Rates / xlsx_read.py f.xlsx --names Hyperlink `--hyperlink "A1= https://example.com Cell note `--note "B2=Check this Protect sheet (see Pitfalls) --protect your-password --unlock B2:B9 Recalculate via LibreOffice xlsx_recalc.py f.xlsx Copy / rename sheet --copy-sheet Src:New --rename-sheet Old:New Force recalc on open xlsx_edit.py f.xlsx --recalc CSV -> styled xlsx csv_to_xlsx.py in.csv out.xlsx xlsx -> CSV xlsx_to_csv.py f.xlsx out.csv --encoding utf-8 Procedure Create : write a JSON spec (schema documented in xlsx_create.py --help and its docstring). Each sheet supports rows (scalars or styled cell objects), sparse cells overrides, column_widths , row_heights , merges , freeze_panes , autofilter , conditional_formats (cell_is rules and color scales), charts (bar/line/pie from cell ranges), validations (list dropdowns), tables (native Excel tables with a style name), and protection . Workbook-level defined_names maps names to refs. Cell objects also take hyperlink and note . Typed values: JSON numbers/bools pass through; dates use {"value": "2026-01-31", "type": "date"} . Number formats are Excel format strings: currency "$#,##0.00" , percent "0.0%" , date "yyyy-mm-dd" . Formulas : set with "formula": "SUM(B2:B9)" in the spec or --set "C1==SUM(A:A)" in the editor. When writing formulas, add "full_calc_on_load": true (spec) or --recalc (editor); this sets the workbook's fullCalcOnLoad flag so Excel/LibreOffice recompute everything on open. openpyxl itself NEVER evaluates formulas. Read : --sheets for inventory (names, dimensions, merged ranges, chart count, tables, protection, defined names), --json / --csv for data, --formulas to pair each formula string with its cached result, --notes for cell comments, --names for defined names. Cached results exist only if the file was last saved by a real spreadsheet app; files fresh from openpyxl return null there. To materialize results headlessly run xlsx_recalc.py file.xlsx (uses LibreOffice; prints {"recalculated": false, ...} and exits 0 when soffice is absent), then reload with --data-only . Edit : xlsx_edit.py applies renames/copies first, then structural row/column changes, then --set / --append . It edits in place unless --out is given — copy the file first if you need the original. Restructure : for insert/delete on sheets that have formulas, merges, tables, or filters, use xlsx_restructure.py instead of xlsx_edit.py . It rewrites formula references on ALL sheets (absolute $ refs, ranges, cross-sheet refs), shifts merges, autofilter, freeze panes, validation and conditional-format ranges, table refs, defined names, and row/column dimensions, then prints a JSON report including a not_shifted list. Rules and limits: references/restructuring.md . CSV interop : csv_to_xlsx.py infers int/float/bool/ISO-date per cell and styles the header row; xlsx_to_csv.py writes ISO dates and blank strings for empty cells. Both default to UTF-8 and accept --encoding (e.g. utf-8-sig for Excel-friendly BOM, cp1252 for legacy Windows exports). Converting to PDF LibreOffice converts headlessly (also works for CSV export of a single sheet): soffice --headless --convert-to pdf report.xlsx --outdir out/ soffice --headless --convert-to csv report.xlsx --outdir out/ # 1st sheet only Only the first sheet lands in a CSV; for other sheets use xlsx_to_csv.py --sheet NAME . If soffice is missing, install LibreOffice or hand the file to the user unconverted. Pitfalls openpyxl does not calculate. Formula results are available only via load_workbook(path, data_only=True) and only when the file was previously saved by Excel/LibreOffice. Otherwise you get None . xlsx_edit.py insert/delete does not shift references (raw openpyxl behavior). Use xlsx_restructure.py , which does — but even it cannot move chart anchors, images, or conditional-format RULE formulas; read its JSON report's not_shifted list and references/restructuring.md . Sheet protection is NOT security. --protect sets the standard xlsx sheet-protection hash: it signals "don't edit this" to well-behaved apps and nothing more. Anyone can strip it by editing the zip's XML or unchecking it in LibreOffice. Never rely on it for confidentiality or integrity; it does not encrypt anything. data_only=True then save silently discards all formulas (cached values replace them). Never save a workbook loaded that way unless that is the goal. Loading strips charts/images : openpyxl does not round-trip charts, so editing a charted workbook and saving drops the charts. Re-add charts after editing, or avoid re-saving charted files. CSV locale traps : always pass explicit encodings (the scripts already do) and remember European CSVs often use ; delimiters and decimal commas — use --delimiter ';' and expect strings like "12,5" to stay strings. Dates are datetimes : Excel stores dates as serial numbers; openpyxl returns datetime / date objects. Dumps here emit ISO strings. Sheet names are capped at 31 chars and reject [ ] : * ? / \ . Verification After creating: xlsx_read.py out.xlsx --sheets and confirm sheet names, dimensions, merged ranges, and chart counts match intent. Dump data with --json and compare against the source values. After edits: re-dump the touched range; if formulas were written, confirm --formulas lists them and that --recalc was applied. After xlsx_restructure.py : read its JSON report, then re-run --formulas and --sheets to confirm references and ranges landed where expected. For a full visual check, open in LibreOffice: soffice --headless --convert-to pdf out.xlsx and inspect the PDF.