# name: processing-xlsx description: Creates, reads, and modifies Excel workbooks (.xlsx, .xlsm, .xltx) with openpyxl — cell values, formulas, formatting, multiple sheets. Use when the user asks to create, open, read, edit, update, or fix an Excel file or spreadsheet, mentions .xlsx/.xlsm/.xltx files, workbooks, worksheets, or Excel formulas. Do not use for .csv/.tsv files (plain-text tabular data) or for data-quality profiling.

# Processing XLSX

# When to use / when NOT to use

• Use for: creating, reading, or modifying .xlsx, .xlsm, .xltx workbooks — values, formulas, formatting, sheets.
• Do NOT use for: .csv/.tsv files (handle as plain text), data-quality profiling, or Google Sheets (different API).

# Quick reference

Default library: openpyxl. Escape hatch: pandas (read_excel/to_excel) only for bulk data I/O with no formulas or formatting.

Create:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Revenue"])
ws.append(["East", 1200])
ws["B3"] = "=SUM(B2:B2)"   # formulas as strings, never hardcoded results
wb.save("sales.xlsx")

Read — always two passes:

from openpyxl import load_workbook
wb_f = load_workbook("sales.xlsx")                  # pass 1: formula strings
wb_v = load_workbook("sales.xlsx", data_only=True)  # pass 2: cached values

data_only=True returns None for formulas if the file was never opened/recalculated by Excel or LibreOffice — report that, don't guess values.

Modify:

wb = load_workbook("sales.xlsx")   # never data_only when re-saving: cached-only load discards formulas
wb["Sales"]["B2"] = 1500
wb.save("sales.xlsx")

# Workflow

1. Classify the task: create / read / modify. For bulk dataframe dumps with zero formatting, use pandas; otherwise openpyxl.
2. When modifying, first read the file (two passes) and match its existing conventions: sheet names, header row, number formats, fonts.
3. Write formulas as strings ('=SUM(B2:B9)'); never compute a result in Python and hardcode it where a formula belongs.
4. Save, then validate: load_workbook(path) on the output — if it raises, fix before delivering. List sheet names and dimensions to confirm expected structure.
5. Report the output path and what changed (sheets touched, ranges written).

# Edge cases & failure modes

• openpyxl missing → pip install openpyxl (pandas path additionally needs pip install pandas).
• Corrupt / not a zip → load_workbook raises BadZipFile or InvalidFileException; report the file is not a valid xlsx, stop.
• Password-protected workbook → openpyxl cannot decrypt; tell the user to remove the password (openpyxl has no decryption support).
• Large file (>50 MB or >1M cells) → read with read_only=True, write with write_only=True; both stream instead of loading everything in memory.
• .xlsm macros → open with keep_vba=True and save as .xlsm, otherwise macros are silently stripped.

# References

Deeper recipes (formatting, charts, merged cells, find-and-replace, conversion, gotchas): see references/recipes.md.