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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

workbook.save("data_modified.xlsx")

Inserting Rows and Columns

Insert Rows

code.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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.pyPython

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

Mistake 2: Wrong file extension

code.pyPython

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

Mistake 3: Overwriting without warning

code.pyPython

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

Check if file exists first or use different name.

Mistake 4: Starting from row 0

code.pyPython

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

Rows and columns start at 1.

Mistake 5: Not handling errors

code.pyPython

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.