By WebNest Studio Engineering Team
Python Tutorial
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.
Why it matters
A great deal of business data lives in Excel workbooks, and producing or reading them automatically is one of the most requested uses of Python in offices: monthly reports, data clean-up, combining sheets. A script that does in seconds what takes someone an hour by hand is an easy way to show the value of programming.
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
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")
saved 5 rows x 4 columns
Reading data from an existing workbook
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)
['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
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"])
['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).
Exercise
Using openpyxl, which must be installed with pip install openpyxl, create a workbook with a sheet named Sales that has a header row and two data rows, and save it. Then load the file again and print the total of the Amount column.
Expected output: 570
from openpyxl import Workbook, load_workbook
workbook = Workbook()
sheet = workbook.active
# TODO: name the sheet "Sales"
# TODO: append the rows ["Item", "Amount"], ["Pen", 120] and ["Book", 450]
# TODO: save the workbook as sales.xlsx
# TODO: load sales.xlsx, read the Amount column (skipping the header) and print its total
Show hints
sheet.append([...])adds a row, andworkbook.save("sales.xlsx")writes the file.sheet.iter_rows(min_row=2, values_only=True)yields each data row as a tuple of values.
Show solution
from openpyxl import Workbook, load_workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = "Sales"
sheet.append(["Item", "Amount"])
sheet.append(["Pen", 120])
sheet.append(["Book", 450])
workbook.save("sales.xlsx")
loaded = load_workbook("sales.xlsx")["Sales"]
total = sum(row[1] for row in loaded.iter_rows(min_row=2, values_only=True))
print(total)
Quick quiz
1. Which library is commonly used to read and write .xlsx files cell by cell?
- A. csv
- B. openpyxl
- C. json
- D. pickle
Show answer
B. It is a third-party package and is installed with pip.
2. In openpyxl, what does sheet["B2"].value give?
- A. The address of the cell
- B. The whole of column B
- C. The content of cell B2
- D. The formatting of the cell
Show answer
C. A cell object also has attributes for its font, fill and number format.
3. Which pandas function reads a sheet of an Excel file into a DataFrame?
- A. pd.load_excel
- B. pd.excel
- C. pd.open_xlsx
- D. pd.read_excel
Show answer
D. pandas uses openpyxl underneath for .xlsx files.
Interview questions
When would you use openpyxl and when pandas for Excel files?
Show answer
Use openpyxl when you need control over individual cells: formatting, formulas, column widths, several sheets, or changing an existing workbook while keeping its layout. Use pandas when the sheet is a table of data to be filtered, grouped or combined: read_excel loads it into a DataFrame and to_excel writes one back. The two are often used together.
What is the difference between a CSV file and an Excel file?
Show answer
A CSV file is plain text containing one table, with no types, formatting or formulas, and any program can read it. An Excel .xlsx file is a compressed set of XML files that can hold several sheets, typed cells, formulas, formatting and charts, and needs a library to read. CSV is preferred for exchanging data between programs; Excel is used when people will work with the file.
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 in the playground
Write and run your own code for this topic, or start from the exercise or an example. Runs Python in your browser. Your code for each topic is kept in this browser.
Run the examples
Change an input, predict the result, then compare it with the output. Explain why the result changes.
These examples need a project environment on your computer, so they can be copied but not run here. Codelab runs Python, FastAPI backends with SQLite, plain Java 17 and HTML/CSS/JavaScript.
Written by
WebNest Studio Engineering Team
Practical lessons from developers working with Java, Python, React, databases and production systems.
Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering