Xlsx Skill: Create, Read, and Edit Excel Workbooks with Hermes Agent

The Xlsx Skill is for anyone who needs to work with Excel .xlsx files programmatically. It wraps Python and the openpyxl library, so you can create styled workbooks with multiple sheets, formulas, and charts, or inspect and modify existing files. You can also convert between .xlsx and CSV. Every helper script is a command-line tool built with argparse, which means you can call it from a terminal, a script, or another automation. The tools output JSON, making it easy to parse results in other steps of a workflow, and they handle UTF-8 encoding explicitly, so non-ASCII characters in your data are less likely to cause surprises. Reach for this when you need to generate a report, update a spreadsheet that a colleague or client sent you, or turn a CSV export into a formatted workbook. It pairs well with other skills that produce or consume structured data, since the JSON output lets you chain commands together. The main caveat is that these are command-line tools, not a Python library you import, so you will be working with file paths and arguments rather than writing Python code directly.

When to Use

Reach for Xlsx when you need to produce or consume modern Excel workbooks programmatically. It covers the full range of what a spreadsheet user expects: multi-sheet reports, number formatting, styling, merged cells, frozen panes, autofilters, conditional formatting, charts, data-validation dropdowns, native Excel tables, defined names, hyperlinks, cell notes, and sheet protection. If your deliverable is a polished .xlsx report, this is the tool.

It also handles the read side. You can inventory what a workbook contains, dump data as JSON or CSV, and distinguish formulas from cached values. Notes, defined names, and tables are all accessible, so you can extract more than just raw cell values.

Editing existing files is a common workflow, and Xlsx supports it: set cells, append rows, insert or delete rows and columns. The reference-aware behavior via xlsx_restructure.py means formulas and named ranges adjust when the structure changes, which saves you from manually fixing broken references. You can also copy or rename sheets, and manage tables, names, notes, and protection.

If you need to recalculate formulas without opening Excel, there is a headless path through LibreOffice using xlsx_recalc.py. This is handy for refreshing volatile functions or ensuring cached values are up to date before you distribute a file.

CSV interop is covered too, with type inference and support for non-UTF-8 encodings, so you can move data between CSV and Excel without losing formatting or character sets.

One clear limitation: this is not for the legacy .xls binary format. If you encounter an old .xls file, convert it first with LibreOffice using soffice --headless --convert-to xlsx old.xls, then work with the resulting .xlsx.

In practice, you will reach for this when you are building reporting pipelines, migrating data between systems, or automating spreadsheet maintenance. It pairs well with data processing workflows where Excel is the final output format, and it avoids the need for a desktop Excel installation for most operations.

Prerequisites

To follow this guide, you need Python 3.10 or newer, along with the openpyxl library, which you can install with pip install openpyxl. That is the only third-party package required; everything else you will use comes from the Python standard library, so you do not need to set up a virtual environment with a long list of dependencies.

You will likely reach for this setup when you are already comfortable with Python and need to read, write, or manipulate Excel files programmatically. The openpyxl library handles the .xlsx format directly, so you do not need Excel installed on your machine.

Optionally, you can install LibreOffice (specifically the soffice command) if you need to recalculate formulas or convert between spreadsheet formats without opening a graphical interface. This is useful in automated workflows, such as when you generate a report and then need to produce a PDF or a different spreadsheet version. Keep in mind that this is an optional addition; if you do not need those capabilities, you can skip it and still complete everything in this guide.

How to Run

All helper scripts live in this skill's scripts/ directory and are meant to be executed with the terminal tool. Every script accepts --help, so you can always check its options before running it.

Here is the full set of commands you will use, in the order you are most likely to need them:

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

Start with xlsx_create.py when you have a JSON spec that describes the workbook you want to build. The spec is authored with write_file, so you can keep it version-controlled or generate it programmatically. After creating the file, use xlsx_read.py to verify what you got: --sheets lists the sheet names, --json --sheet Data dumps the contents of a specific sheet as JSON, and --formulas shows the formulas rather than their computed values. That last one is handy when you need to confirm that a formula was written correctly before you trust the numbers.

When you need to change a value, xlsx_edit.py is the tool. The --set B2=42 syntax targets a single cell, and --recalc forces the workbook to recompute formulas after the edit, which matters if other cells depend on B2. For structural changes, xlsx_restructure.py lets you insert rows; the --insert-rows 3:2 argument means insert 2 rows starting at row 3, so adjust those numbers to match your layout.

If you only need to refresh formula results without editing anything, run xlsx_recalc.py on the file. This is useful when the workbook was created by another tool and the cached values are stale.

Finally, the two conversion scripts bridge the gap between Xlsx and plain CSV. csv_to_xlsx.py takes a CSV file and wraps it into an Xlsx workbook, with --encoding utf-8 to handle non-ASCII text. The reverse, xlsx_to_csv.py, extracts a specific sheet (here Data) into a CSV file, which is convenient for feeding into other data pipelines or for quick inspection in a text editor.

When you run any script that outputs JSON, you can either pipe it to read_file for a formatted view or just read it directly from stdout. The choice depends on whether you want to keep the output for later or just glance at it.

Quick Reference

This table is the fastest way to find the command you need. Keep it open while you work through the rest of the guide. Each row pairs a task with the exact command line to run.

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](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

A few practical notes. The read commands (xlsx_read.py) are your first stop for inspecting a file without changing it. The edit commands (xlsx_edit.py) modify in place, so make a backup if the file matters. The restructure commands (xlsx_restructure.py) shift references, which is what you want when you add or remove rows or columns and need formulas to stay correct. The table commands assume you already have a native Excel table; if you do not, create one first with --add-table. The protect command is listed with a pointer to the Pitfalls section because sheet protection has traps that are easy to miss. The recalc commands are useful when you have formulas that were never evaluated, or when you want to force a recalculation on open.

Procedure

1. Create: Start by writing a JSON spec. The schema is documented in xlsx_create.py --help and its docstring. Each sheet can define rows (either 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. At the workbook level, defined_names maps names to refs. Cell objects can also take hyperlink and note. Typed values: JSON numbers and booleans pass through as-is; dates use the form {"value": "2026-01-31", "type": "date"}. Number formats are Excel format strings: currency "$#,##0.00", percent "0.0%", date "yyyy-mm-dd".

   This is your entry point for generating a workbook from scratch. You will likely pair it with a script that builds the JSON spec programmatically, especially if the data comes from a database or an API. The sparse cells override is handy when you only need to tweak a few cells without defining the whole sheet.

2. Formulas: Set formulas with "formula": "SUM(B2:B9)" in the spec, or with --set "C1==SUM(A:A)" in the editor. When writing formulas, add "full_calc_on_load": true in the spec, or --recalc in the editor. This sets the workbook's fullCalcOnLoad flag so Excel/LibreOffice recompute everything on open. openpyxl itself NEVER evaluates formulas.

   In practice, this means you should not expect computed values from openpyxl. If you need the results immediately, you will have to open the file in a real spreadsheet app or use the recalc tool described below.

3. Read: Use --sheets for an 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, and --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.

   This is the read path for most automation. If you are pulling data out of a spreadsheet that was hand-edited, the cached results will be there. If the file was generated by openpyxl, expect null for formula results until you run the recalc step.

4. 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.

   This is your go-to for quick tweaks: changing a value, renaming a sheet, or appending a row. Because it edits in place by default, make a backup or use --out when the change is risky.

5. 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.

   Reach for this when you are inserting or deleting rows or columns in a sheet that has any of those features. The not_shifted list tells you which references could not be adjusted, so you can review them manually. This is the safer choice over xlsx_edit.py for structural changes.

6. 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).

   Use these to move data between CSV and XLSX. The encoding flag matters when you are dealing with non-ASCII text: utf-8-sig avoids the BOM confusion in Excel, and cp1252 matches older Windows files.

Converting to PDF

When you need a PDF version of a spreadsheet, LibreOffice can do the conversion without opening a graphical interface. This is handy for batch jobs or server-side processing where you want to produce a PDF from an .xlsx file automatically.

Run the conversion from the command line, specifying the input file and an output directory:

soffice --headless --convert-to pdf report.xlsx --outdir out/
soffice --headless --convert-to csv report.xlsx --outdir out/  # 1st sheet only

The same approach works for CSV export, but note that only the first sheet is converted. If you need data from another sheet, use the dedicated script xlsx_to_csv.py --sheet NAME instead.

If soffice is not available on the system, you have two options: install LibreOffice, or pass the original file to the user without conversion. The latter is a reasonable fallback when you cannot add dependencies to the environment.

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. In practice, this means you cannot rely on openpyxl to compute values for you. If you need calculated results, ensure the file has been opened and saved in a spreadsheet application first. This pairs with any workflow that reads data for reporting or analysis, but it is a common source of confusion when you expect numbers and 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. When you are restructuring a sheet, always check the report to see what did not move. This is especially important if your workbook contains charts or images that depend on cell positions.
• 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. Use it only as a convenience to prevent accidental edits, not as a barrier against determined users.
• 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. If you load with data_only=True and then save, you will lose all formulas permanently. If you need to preserve formulas, load without that flag and handle the cached values separately.
• 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. If your workflow involves charts, plan to recreate them after any edit, or keep a backup of the original file.
• 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. This is a classic gotcha when moving between locales. Always specify the encoding and delimiter explicitly to avoid silent data corruption.
• Dates are datetimes: Excel stores dates as serial numbers; openpyxl returns datetime/date objects. Dumps here emit ISO strings. When you read a date cell, you get a Python datetime object, not a string. If you need a specific format, convert it yourself.
• Sheet names are capped at 31 chars and reject [ ] : * ? / \. When creating or renaming sheets, keep names short and avoid those characters, or you will hit errors.

Verification

After generating a workbook, run xlsx_read.py out.xlsx --sheets to confirm the sheet names, dimensions, merged ranges, and chart counts match what you intended. This is your first sanity check: it catches mismatched sheet order, off-by-one dimension errors, and missing merges before you invest time in deeper inspection.

Next, dump the data with --json and compare it against the source values. This is especially useful when you are converting from a CSV or a database export, because it shows exactly what landed in each cell, including types and empty strings.

If you have edited the file, re-dump the range you touched. When you wrote formulas, verify that --formulas lists them and that you applied --recalc. Skipping --recalc is a common mistake: the formulas may be present but their cached results stale, which can mislead anyone who opens the file without recalculating.

After running xlsx_restructure.py, read its JSON report first, then re-run --formulas and --sheets to confirm that references and ranges moved to the expected locations. The report tells you what the script intended; the re-runs tell you what actually happened. Compare the two to catch any mismatch between the plan and the result.

For a final visual check, open the workbook in LibreOffice with soffice --headless --convert-to pdf out.xlsx and inspect the PDF. This catches layout issues that a text dump cannot, such as column widths, row heights, and chart placement. It is a quick way to confirm the file opens cleanly in a real spreadsheet application.