Writing to Excel Files

Creating a New Excel File

You can create Excel files from Python to save your data in a format anyone can open.

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet["A1"] = "Hello"
sheet["B1"] = "World"

workbook.save("output.xlsx")
print("File created")

What this does:

Creates a new workbook
Gets the active sheet
Writes values to cells
Saves as output.xlsx

Writing Single Cell Values

Using Cell Reference

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["A2"] = "John"
sheet["B2"] = 25

workbook.save("data.xlsx")

What this creates:

| Name | Age |
|------|-----|
| John | 25  |

Using Row and Column Numbers

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet.cell(row=1, column=1).value = "Product"
sheet.cell(row=1, column=2).value = "Price"
sheet.cell(row=2, column=1).value = "Laptop"
sheet.cell(row=2, column=2).value = 999

workbook.save("products.xlsx")

What this does: Same as above, but using row and column numbers instead of cell references.

Writing Multiple Rows

Using append()

The append() method adds a complete row at once.

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet.append(["Name", "Age", "City"])
sheet.append(["John", 25, "New York"])
sheet.append(["Sarah", 30, "London"])
sheet.append(["Mike", 28, "Tokyo"])

workbook.save("people.xlsx")

What this creates: Each list becomes a row in Excel. Much faster than writing cell by cell.

Writing from List of Lists

code.py

import openpyxl

data = [
    ["Product", "Quantity", "Price"],
    ["Laptop", 5, 999],
    ["Phone", 10, 599],
    ["Tablet", 7, 399]
]

workbook = openpyxl.Workbook()
sheet = workbook.active

for row in data:
    sheet.append(row)

workbook.save("inventory.xlsx")

What this does: Loops through list and adds each row to Excel.

Writing from Dictionary

code.py

import openpyxl

students = [
    {"name": "John", "math": 85, "science": 90},
    {"name": "Sarah", "math": 92, "science": 88},
    {"name": "Mike", "math": 78, "science": 85}
]

workbook = openpyxl.Workbook()
sheet = workbook.active

headers = ["name", "math", "science"]
sheet.append(headers)

for student in students:
    row = [student[key] for key in headers]
    sheet.append(row)

workbook.save("grades.xlsx")

What this does:

Writes header row with column names
For each student, creates a row with values in correct order
Saves to Excel file

Working with Multiple Sheets

Creating New Sheets

code.py

import openpyxl

workbook = openpyxl.Workbook()

sheet1 = workbook.active
sheet1.title = "Sales"

sheet2 = workbook.create_sheet("Expenses")
sheet3 = workbook.create_sheet("Summary", 0)

sheet1.append(["Product", "Revenue"])
sheet1.append(["Laptop", 5000])

sheet2.append(["Item", "Cost"])
sheet2.append(["Rent", 2000])

workbook.save("report.xlsx")

What this does:

Renames first sheet to "Sales"
Creates "Expenses" sheet at end
Creates "Summary" sheet at position 0 (beginning)
Adds data to different sheets

Writing to Specific Sheet

code.py

import openpyxl

workbook = openpyxl.Workbook()
sales_sheet = workbook.active
sales_sheet.title = "Q1 Sales"

q2_sheet = workbook.create_sheet("Q2 Sales")

sales_sheet.append(["Month", "Sales"])
sales_sheet.append(["Jan", 10000])

q2_sheet.append(["Month", "Sales"])
q2_sheet.append(["Apr", 12000])

workbook.save("quarterly.xlsx")

What this creates: Excel file with two sheets, each with its own data.

Updating Existing Files

Read an existing file, modify it, and save.

code.py

import openpyxl

workbook = openpyxl.load_workbook("data.xlsx")
sheet = workbook.active

sheet["A1"] = "Updated Value"

sheet.append(["New", "Row", "Added"])

workbook.save("data.xlsx")

What this does:

Opens existing data.xlsx
Changes cell A1
Adds a new row
Saves changes (overwrites original)

To save as new file:

code.py

workbook.save("data_modified.xlsx")

Inserting Rows and Columns

Insert Rows

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet.append(["A", "B", "C"])
sheet.append(["1", "2", "3"])

sheet.insert_rows(2)

sheet["A2"] = "Inserted"

workbook.save("output.xlsx")

What this does: Inserts a blank row at position 2. Existing rows move down.

Insert Columns

code.py

import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

sheet.append(["A", "B"])
sheet.append(["1", "2"])

sheet.insert_cols(2)

sheet["B1"] = "New Col"

workbook.save("output.xlsx")

What this does: Inserts a blank column at position 2. Existing columns move right.

Deleting Rows and Columns

Delete Rows

code.py

import openpyxl

workbook = openpyxl.load_workbook("data.xlsx")
sheet = workbook.active

sheet.delete_rows(3, 2)

workbook.save("data.xlsx")

What this does: Deletes 2 rows starting from row 3.

Delete Columns

code.py

import openpyxl

workbook = openpyxl.load_workbook("data.xlsx")
sheet = workbook.active

sheet.delete_cols(2, 1)

workbook.save("data.xlsx")

What this does: Deletes 1 column starting from column 2.

Practice Example

The scenario: You have sales data in Python. You want to create an Excel report with calculations.

code.py

import openpyxl

sales_data = [
    {"product": "Laptop", "quantity": 5, "price": 999},
    {"product": "Phone", "quantity": 10, "price": 599},
    {"product": "Tablet", "quantity": 7, "price": 399},
    {"product": "Monitor", "quantity": 8, "price": 299}
]

workbook = openpyxl.Workbook()
sheet = workbook.active
sheet.title = "Sales Report"

sheet.append(["Product", "Quantity", "Price", "Revenue"])

total_revenue = 0

for item in sales_data:
    product = item["product"]
    quantity = item["quantity"]
    price = item["price"]
    revenue = quantity * price

    sheet.append([product, quantity, price, revenue])

    total_revenue = total_revenue + revenue

last_row = sheet.max_row + 2
sheet.cell(row=last_row, column=1).value = "Total Revenue:"
sheet.cell(row=last_row, column=4).value = total_revenue

workbook.save("sales_report.xlsx")
print("Report created:", "sales_report.xlsx")
print("Total revenue:", total_revenue)

What this program does:

Creates new workbook with title "Sales Report"
Writes header row
Loops through sales data
Calculates revenue for each product
Writes product row to Excel
Keeps running total
Adds total row at bottom with some spacing
Saves completed report

Result in Excel:

| Product | Quantity | Price | Revenue |
|---------|----------|-------|---------|
| Laptop  | 5        | 999   | 4995    |
| Phone   | 10       | 599   | 5990    |
| Tablet  | 7        | 399   | 2793    |
| Monitor | 8        | 299   | 2392    |
|         |          |       |         |
| Total Revenue:     |       | 16170   |

Converting CSV to Excel

code.py

import csv
import openpyxl

workbook = openpyxl.Workbook()
sheet = workbook.active

with open("data.csv", "r") as file:
    reader = csv.reader(file)

    for row in reader:
        sheet.append(row)

workbook.save("data.xlsx")
print("CSV converted to Excel")

What this does: Reads CSV file and writes each row to Excel. Simple conversion.

Key Points to Remember

Create new Excel files with Workbook(). Use active sheet or create new sheets with create_sheet().

Write single cells with sheet["A1"] = value or sheet.cell(row, col).value = value.

Use append() to add entire rows at once. Much faster than writing cell by cell.

Save changes with workbook.save("filename.xlsx"). This creates the file on your computer.

You can load existing files, modify them, and save changes. Use load_workbook() to open, make changes, then save().

Common Mistakes

Mistake 1: Forgetting to save

code.py

workbook = openpyxl.Workbook()
sheet = workbook.active
sheet["A1"] = "Data"
# Forgot workbook.save()!  No file created!

Mistake 2: Wrong file extension

code.py

workbook.save("data.xls")  # Old format, use .xlsx

Mistake 3: Overwriting without warning

code.py

workbook.save("important.xlsx")  # This replaces existing file!

Check if file exists first or use different name.

Mistake 4: Starting from row 0

code.py

sheet.cell(row=0, column=1).value = "Data"  # Error!

Rows and columns start at 1.

Mistake 5: Not handling errors

code.py

workbook.save("/readonly/data.xlsx")  # May fail if no permission

Use try-except for file operations.

What's Next?

You now know how to write Excel files. Next, you'll learn about working with multiple Excel sheets - reading data from different sheets and organizing complex workbooks.