Python Read/Write Excel
Excel workbooks hold a lot of business and test data. The openpyxl library reads and writes .xlsx files cell by cell, and pandas can load a whole sheet into a table in one line.
- Create a workbook and write rows
- Read cells, rows and ranges
- Add formulas, styles and column widths
- Load sheets with pandas
Install openpyxl
openpyxl is not part of the standard library. Install it with pip:
python -m pip install openpyxlThe Try it button runs Python in your browser, where openpyxl may not be available. Run these examples on your computer after installing it.
Create a workbook and write data
from openpyxl import Workbook wb = Workbook()ws = wb.activews.title = "Results" ws.append(["Test", "Status", "Duration (s)"])ws.append(["login", "pass", 1.8])ws.append(["search", "fail", 3.2])ws.append(["checkout", "pass", 2.4])ws["E1"] = "Generated by Python" wb.save("results.xlsx")print("saved", ws.max_row, "rows and", ws.max_column, "columns")saved 4 rows and 5 columns
Read a workbook
from openpyxl import load_workbook wb = load_workbook("results.xlsx")print(wb.sheetnames)ws = wb["Results"] print(ws["A2"].value, ws.cell(row=2, column=2).value)for row in ws.iter_rows(min_row=2, max_col=3, values_only=True): print(row)['Results']
login pass
('login', 'pass', 1.8)
('search', 'fail', 3.2)
('checkout', 'pass', 2.4)values_only=True returns plain values instead of cell objects, which is what you want most of the time.
Read rows as dictionaries
from openpyxl import load_workbook ws = load_workbook("results.xlsx")["Results"]rows = ws.iter_rows(max_col=3, values_only=True)header = next(rows)records = [dict(zip(header, r)) for r in rows] print(records[1])print([r["Test"] for r in records if r["Status"] == "fail"]){'Test': 'search', 'Status': 'fail', 'Duration (s)': 3.2}
['search']Update cells and add formulas
from openpyxl import load_workbook wb = load_workbook("results.xlsx")ws = wb["Results"] ws["B3"] = "pass" # fix a valuews["C6"] = "=SUM(C2:C4)" # a formula, calculated when Excel opens itws["B6"] = "Total"wb.save("results.xlsx") print(load_workbook("results.xlsx")["Results"]["C6"].value)=SUM(C2:C4)
openpyxl stores formulas but does not calculate them. Excel (or LibreOffice) calculates the result when it opens the file. Load with data_only=True to read the last values Excel saved.
Styles and column widths
from openpyxl import load_workbookfrom openpyxl.styles import Font, PatternFill wb = load_workbook("results.xlsx")ws = wb["Results"] for cell in ws[1]: cell.font = Font(bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="0B7F62")ws.column_dimensions["A"].width = 14 for row in ws.iter_rows(min_row=2, max_row=4): if row[1].value == "fail": row[1].font = Font(color="C23A3A", bold=True) wb.save("results.xlsx")print(ws["A1"].font.bold, ws.column_dimensions["A"].width)True 14.0
Several sheets
from openpyxl import load_workbook wb = load_workbook("results.xlsx")summary = wb.create_sheet("Summary")summary.append(["Total tests", 3])wb.save("results.xlsx")print(load_workbook("results.xlsx").sheetnames)['Results', 'Summary']
Excel with pandas
pandas uses openpyxl under the hood and loads a whole sheet into a DataFrame:
import pandas as pd df = pd.read_excel("results.xlsx", sheet_name="Results", usecols="A:C", nrows=3)print(df)print(df["Duration (s)"].mean()) df.to_excel("copy.xlsx", index=False)print(pd.read_excel("copy.xlsx").shape)Test Status Duration (s) 0 login pass 1.8 1 search pass 3.2 2 checkout pass 2.4 2.466666666666667 (3, 3)
Check your understanding
Quick quiz
1. Which method adds a row after the last used row?
append writes a list as the next row.
2. What does values_only=True do in iter_rows?
It is the easiest way to get the data out.
3. Does openpyxl calculate formulas?
Load with data_only=True to read the values Excel cached.
Exercise: pass rate per status
Read results.xlsx and print how many tests have each status.
the Results sheet created in this lesson
pass 3
Hint 1
Loop over iter_rows(min_row=2, max_row=4, values_only=True) and count the second value.
from collections import Counterfrom openpyxl import load_workbook ws = load_workbook("results.xlsx")["Results"]statuses = Counter(row[1] for row in ws.iter_rows(min_row=2, max_row=4, values_only=True))for status, count in statuses.items(): print(status, count)pass 3
Rows 2 to 4 hold the three tests (row 6 is the total). Earlier in the lesson “search” was changed to “pass”, so all three now pass.
Program Practice
38 Python File Handling Programs
Every program has a step-by-step explanation, colour-coded code and its real output.
Python Excel FAQ
How do I read an Excel file in Python?
Use openpyxl: load_workbook("file.xlsx")["Sheet1"], then iter_rows(values_only=True). Or use pandas: pd.read_excel("file.xlsx").
Can openpyxl read .xls files?
No. openpyxl supports .xlsx, .xlsm, .xltx and .xltm. Convert old .xls files to .xlsx first.
How do I write data to a specific cell?
Assign to it: ws["B2"] = "pass" or ws.cell(row=2, column=2, value="pass"), then wb.save().
2 thoughts on “Python Read/Write Excel”