xlsx

Excel workbooks (.xlsx)

openpyxl for anything that reads or edits. XlsxWriter only when writing a very large file from scratch, where it is faster and lighter but cannot read.

The single fact that causes the most wasted time:

openpyxl does not calculate. Writing =SUM(B2:B10) stores the formula string. The cached value stays empty until a real spreadsheet application opens and recalculates the file. load_workbook(data_only=True) returns None for every formula cell in a file that Excel has never opened.

So: if the user needs a workbook whose numbers are readable by other tools, run scripts/recalc.py after writing it. If you need to read values from a file someone sent you, data_only=True works — Excel already cached them.

Before editing a workbook, look at it

scripts/inspect_xlsx.py budget.xlsx

Prints each sheet with its dimensions, frozen panes, column widths, a preview grid, where the formulas are, merged ranges, named ranges, conditional formatting and charts. Do this first on any file you did not create — a spreadsheet's structure is rarely what the file name suggests.

scripts/inspect_xlsx.py budget.xlsx --sheet Q1 --range A1:F20   # focus
scripts/inspect_xlsx.py budget.xlsx --formulas                  # every formula

Creating a workbook

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Q1"                      # sheet names: <=31 chars, no : \ / ? * [ ]

ws.append(["Region", "Revenue", "Cost", "Margin"])
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="1F4E79")
    cell.alignment = Alignment(horizontal="center")

for row, (region, revenue, cost) in enumerate(data, start=2):
    ws.cell(row=row, column=1, value=region)
    ws.cell(row=row, column=2, value=revenue).number_format = '#,##0.00'
    ws.cell(row=row, column=3, value=cost).number_format = '#,##0.00'
    ws.cell(row=row, column=4, value=f"=B{row}-C{row}").number_format = '#,##0.00'

ws.freeze_panes = "A2"               # header stays visible
ws.auto_filter.ref = ws.dimensions
ws.column_dimensions["A"].width = 18 # openpyxl does NOT auto-size columns

wb.create_sheet("Notes")
wb.save("budget.xlsx")