Reading and Writing Excel Files in Python

Excel workbooks are everywhere in business: sales reports, attendance sheets, price lists, invoices. Python can read and write .xlsx files directly, automating hours of manual spreadsheet work. The openpyxl library gives cell-level control — formulas, formatting, multiple sheets, charts — while pandas reads and writes whole tables in one line.

This lesson covers creating workbooks, writing and reading cells and rows, formulas and styles, multiple sheets, reading data into Python structures, and using pandas with Excel.

Installing the Libraries

Install with pip install openpyxl pandas. openpyxl handles the modern .xlsx format; pandas uses openpyxl under the hood for Excel files. (Old .xls files need the xlrd package.)

Workbooks, Sheets and Cells with openpyxl

Workbook() creates a new workbook and load_workbook(path) opens one. A workbook contains worksheets (wb.active, wb["Sheet name"], wb.create_sheet()). Access cells as ws["B2"] or ws.cell(row=2, column=2), append whole rows with ws.append([...]), and iterate with ws.iter_rows(min_row=2, values_only=True). Cells can hold formulas ("=SUM(C2:C10)"), and load_workbook(path, data_only=True) reads the values Excel last calculated.

Formatting

openpyxl.styles provides Font, PatternFill, Alignment and Border; cell.number_format sets formats such as "#,##0.00"; ws.column_dimensions["A"].width sets widths; ws.freeze_panes = "A2" freezes the header row.

pandas for Whole Tables

pd.read_excel("file.xlsx", sheet_name="Sales") loads a sheet into a DataFrame; df.to_excel("out.xlsx", index=False) writes one; pd.ExcelWriter writes several DataFrames to different sheets. Use pandas for analysis and openpyxl for precise formatting.

Examples

Creating a formatted Excel report with openpyxl

Python

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

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

ws.append(["Product", "Units", "Price", "Revenue"])
data = [("Python course", 120, 2999), ("SQL course", 80, 1999), ("Workbook", 300, 499)]
for row, (product, units, price) in enumerate(data, start=2):
    ws.append([product, units, price, f"=B{row}*C{row}"])
ws.append(["Total", "=SUM(B2:B4)", None, "=SUM(D2:D4)"])

header_fill = PatternFill("solid", fgColor="1F4E78")
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")
for col in ("C", "D"):
    for cell in ws[col][1:]:
        cell.number_format = "#,##0.00"
ws.column_dimensions["A"].width = 18
ws.freeze_panes = "A2"

wb.save("sales_report.xlsx")
print("saved", ws.max_row, "rows x", ws.max_column, "columns")

Output

saved 5 rows x 4 columns

Reading data from an existing workbook

Python

from openpyxl import load_workbook

wb = load_workbook("sales_report.xlsx")
ws = wb["Sales"]
print(wb.sheetnames, ws["A2"].value, ws.cell(row=2, column=2).value)

for product, units, price, revenue in ws.iter_rows(min_row=2, max_row=4, values_only=True):
    print(f"{product:<14} {units:>4} x {price:>5} (formula: {revenue})")

# Add a second sheet and save
summary = wb.create_sheet("Summary")
summary["A1"] = "Best seller"
summary["B1"] = max(ws.iter_rows(min_row=2, max_row=4, values_only=True), key=lambda r: r[1])[0]
wb.save("sales_report.xlsx")
print(wb.sheetnames)

Output

['Sales'] Python course 120
Python course   120 x  2999 (formula: =B2*C2)
SQL course       80 x  1999 (formula: =B3*C3)
Workbook        300 x   499 (formula: =B4*C4)
['Sales', 'Summary']

Reading and writing Excel with pandas

Python

import pandas as pd

df = pd.DataFrame({
    "student": ["Asha", "Ravi", "Meera", "Kiran"],
    "course": ["Python", "Python", "SQL", "SQL"],
    "marks": [91, 72, 88, 65],
})

with pd.ExcelWriter("results.xlsx") as writer:
    df.to_excel(writer, sheet_name="All", index=False)
    df.groupby("course", as_index=False)["marks"].mean().to_excel(writer, sheet_name="Averages", index=False)

sheets = pd.read_excel("results.xlsx", sheet_name=None)     # dict of all sheets
print(list(sheets))
print(sheets["Averages"])

Output

['All', 'Averages']
   course  marks
0  Python   81.5
1     SQL   76.5

Common Mistakes

Expecting openpyxl to calculate formulas — it stores them; Excel calculates them when the file is opened.
Reading formula cells without data_only=True and getting "=SUM(...)" instead of values.
Trying to open .xls files with openpyxl (use xlrd or convert to .xlsx).
Keeping the file open in Excel while Python tries to save it (PermissionError on Windows).

Key Points to Remember

pip install openpyxl pandas to work with .xlsx files.
openpyxl: Workbook/load_workbook, sheets, cells, append, iter_rows, formulas, styles.
Use number formats, column widths and freeze panes for professional reports.
pandas read_excel/to_excel/ExcelWriter handle whole tables and multiple sheets.

Practice the examples

Change an input, predict the result, then compare it with the output. Explain why the result changes.

Use your local project environment for these examples. Codelab currently runs Python and HTML/CSS/JavaScript; framework examples may need project dependencies.