Working with Excel - openpyxl Basics

What openpyxl is

openpyxl lets you work with .xlsx files:

• create workbooks
• read/write cells
• adjust styles

Create a workbook

from openpyxl import Workbook
 
wb = Workbook()
ws = wb.active
ws.title = "Report"
 
ws["A1"] = "Name"
ws["B1"] = "Score"
 
ws.append(["Alice", 95])
ws.append(["Bob", 88])
 
wb.save("report.xlsx")

Read an existing workbook

from openpyxl import load_workbook
 
wb = load_workbook("report.xlsx")
ws = wb["Report"]
 
print(ws["A2"].value, ws["B2"].value)

Tips

• Keep a template .xlsx if you need complex formatting
• For CSV, prefer the csv module or pandas

The model: workbook, sheet, cell

An Excel file is a workbook made of worksheets, and each worksheet is a grid of cells. openpyxl mirrors that.

• Workbook() creates a new file in memory. load_workbook(path) opens an existing one.
• wb.active is the sheet that opens first. wb["Report"] picks a sheet by name, and wb.create_sheet("Summary") adds one.
• ws["A1"] is a cell. Its content is .value. You can also use ws.cell(row=1, column=1), which is handy inside loops because the numbers can be variables.
• ws.append([...]) writes a list as the next row after the last one that has data.

Nothing is written to disk until you call wb.save("file.xlsx"). Saving to an existing name overwrites it. Save to a new name while you are testing.

Reading rows

iter_rows walks a range. With values_only=True you get plain values rather than cell objects, which is faster and simpler.

from openpyxl import load_workbook
 
wb = load_workbook("report.xlsx", read_only=True)
ws = wb["Report"]
 
header, *rows = list(ws.iter_rows(values_only=True))
print(header)                       # first row, as a tuple
 
for name, score in rows:
    if score is not None and score >= 90:
        print(name, "passed with", score)
 
wb.close()

header, *rows = ... splits the first row from the others. read_only=True streams the file, which matters for large workbooks. In that mode you cannot edit cells, so use it only for reading.

Formatting

Styles are objects you assign to a cell.

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
 
wb = load_workbook("report.xlsx")
ws = wb["Report"]
 
for cell in ws[1]:                                   # every cell in row 1
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="305496")
 
ws.column_dimensions["A"].width = 20
ws.freeze_panes = "A2"                               # keep the header visible
wb.save("report_styled.xlsx")

Common mistakes

• Saving while the file is open in Excel. On Windows this raises PermissionError. Close the file first.
• Only .xlsx. openpyxl does not read the old .xls format. Convert it, or use another library.
• Row and column numbers start at 1, not 0.
• Expecting formulas to have values. Formula cells hold text unless you open and save the file in Excel. Use data_only=True to read cached results.
• Losing content on round trip. openpyxl may drop some charts, images, and macros from a file made in Excel. Test on a copy.
• Overwriting the original. Write to a new file until the script is proven.

Adapting it

Read a sheet into a list of dictionaries using the header row, clean it, and write a new workbook with a bold header and frozen panes. This is the core of most Excel automation.

🧪 Try It Yourself

Exercise 1 – List Files with os.listdir

Press Run to see what your code prints.

Exercise 2 – Join Paths with os.path.join

Press Run to see what your code prints.

Exercise 3 – Write and Read a File

Press Run to see what your code prints.