Excel Spreadsheet (XLSX) Processing

When the user needs to read, create, or edit Excel files, follow this guide.

1. Reading XLSX files

Quick data overview

Use the

read

read(file_path="data.xlsx")

Returns tab-separated data per sheet. Good for quick overview.

Detailed access with openpyxl

from openpyxl import load_workbook

wb = load_workbook("data.xlsx", data_only=True)

for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    print(f"\n=== {sheet_name} ===")
    print(f"Rows: {ws.max_row}, Columns: {ws.max_column}")

    for row in ws.iter_rows(values_only=True):
        print(row)

With pandas (for analysis)

import pandas as pd

# Read single sheet
df = pd.read_excel("data.xlsx")

# Read specific sheet
df = pd.read_excel("data.xlsx", sheet_name="Sales")

# Read all sheets
dfs = pd.read_excel("data.xlsx", sheet_name=None)
for name, df in dfs.items():
    print(f"\n{name}: {df.shape}")
    print(df.head())

2. Creating XLSX files

Core principle: Use formulas, not hardcoded values

Always use Excel formulas instead of calculating in Python and inserting results. This keeps spreadsheets dynamic and updatable.

# Good: formula-based
ws["C2"] = "=A2*B2"

# Bad: hardcoded
ws["C2"] = 150  # calculated externally

Basic spreadsheet

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

wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Headers
headers = ["Product", "Q1", "Q2", "Q3", "Q4", "Total"]
for col, header in enumerate(headers, 1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True, size=12)
    cell.fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    cell.font = Font(bold=True, color="FFFFFF")
    cell.alignment = Alignment(horizontal="center")

# Data
data = [
    ["Widget A", 1200, 1350, 1100, 1500],
    ["Widget B", 800, 950, 1200, 1100],
    ["Widget C", 2000, 1800, 2200, 2400],
]

for row_idx, row_data in enumerate(data, 2):
    for col_idx, value in enumerate(row_data, 1):
        ws.cell(row=row_idx, column=col_idx, value=value)
    # Total formula
    ws.cell(row=row_idx, column=6, value=f"=SUM(B{row_idx}:E{row_idx})")

# Summary row
last_data_row = len(data) + 1
summary_row = last_data_row + 1
ws.cell(row=summary_row, column=1, value="Total")
ws.cell(row=summary_row, column=1).font = Font(bold=True)
for col in range(2, 7):
    col_letter = get_column_letter(col)
    ws.cell(row=summary_row, column=col,
            value=f"=SUM({col_letter}2:{col_letter}{last_data_row})")

# Column widths
ws.column_dimensions["A"].width = 15
for col in range(2, 7):
    ws.column_dimensions[get_column_letter(col)].width = 12

wb.save("sales.xlsx")

Number formatting

from openpyxl.styles import numbers

# Currency
cell.number_format = '$#,##0.00'

# Percentage
cell.number_format = '0.0%'

# Date
cell.number_format = 'YYYY-MM-DD'

# Thousands separator
cell.number_format = '#,##0'

# Custom: negative in parentheses
cell.number_format = '#,##0;(#,##0)'

Professional formatting standards

$#,##0

0.0%

(1,234)

"2024"

Color coding for financial models

3. Charts

from openpyxl.chart import BarChart, LineChart, PieChart, Reference

# Bar chart
chart = BarChart()
chart.title = "Quarterly Sales"
chart.x_axis.title = "Product"
chart.y_axis.title = "Revenue ($)"

data = Reference(ws, min_col=2, max_col=5, min_row=1, max_row=4)
cats = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.shape = 4  # Clustered bar
ws.add_chart(chart, "H2")

# Line chart
line = LineChart()
line.title = "Trend"
line.add_data(data, titles_from_data=True)
line.set_categories(cats)
ws.add_chart(line, "H18")

# Pie chart
pie = PieChart()
pie.title = "Q1 Distribution"
pie_data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=4)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(cats)
ws.add_chart(pie, "H34")

4. Common operations

Merge cells

ws.merge_cells("A1:D1")
ws["A1"] = "Merged Header"

Freeze panes

ws.freeze_panes = "A2"   # Freeze first row
ws.freeze_panes = "B2"   # Freeze first row and first column

Auto-filter

ws.auto_filter.ref = "A1:F4"

Conditional formatting

from openpyxl.formatting.rule import CellIsRule

# Highlight cells > 2000 in green
green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
ws.conditional_formatting.add(
    "B2:E4",
    CellIsRule(operator="greaterThan", formula=["2000"], fill=green_fill)
)

Multiple sheets

wb = Workbook()
ws1 = wb.active
ws1.title = "Summary"

ws2 = wb.create_sheet("Details")
ws3 = wb.create_sheet("Raw Data")

# Cross-sheet formula
ws1["A1"] = "=Details!A1"

5. CSV / TSV conversion

XLSX to CSV

import pandas as pd

df = pd.read_excel("data.xlsx")
df.to_csv("data.csv", index=False, encoding="utf-8-sig")

CSV to XLSX

import pandas as pd

df = pd.read_csv("data.csv")
df.to_excel("data.xlsx", index=False, sheet_name="Data")

6. Editing existing files

from openpyxl import load_workbook

wb = load_workbook("existing.xlsx")
ws = wb["Sheet1"]

# Modify cell
ws["A1"] = "New Value"

# Insert row
ws.insert_rows(2)

# Delete row
ws.delete_rows(5)

# Append data
ws.append(["New", "Row", "Data"])

wb.save("modified.xlsx")

7. Dependencies

Core (included with Muse):

• openpyxl

  — read, create, edit, charts, formatting

Optional (install as needed):

• pandas

  — data analysis:

  pip install pandas