Python Excel AutomationPractical guides for developers
Start learning

Guide

Getting Started With Python Excel AutomationDeep dive

openpyxl: Append Data to an Existing Excel Sheet

Add new rows to an existing .xlsx file with openpyxl — using ws.append(), explicit row indexing, dict-based sparse appends, type-safe values, and atomic saves.

To add rows to an existing workbook, open it with load_workbook(), pick the worksheet, and call ws.append(). The method writes to the first empty row below your data and maps each list item to a column. Because openpyxl edits the Office Open XML directly, appending leaves existing values, formatting, and column widths intact. This guide shows the append patterns you actually need — and the cases where you should index rows explicitly instead. Every example runs: the first step builds the workbook we append to.

Step 1: Create the workbook to append to

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Q3_Data"
ws.append(["Date", "Server", "Status", "Note"])
ws.append(["2024-07-12", "Web-01", "Info", "Deploy 4.2"])
ws.append(["2024-07-14", "DB-02", "Warning", "Slow query"])
wb.save("monthly_report.xlsx")
print("Created with", ws.max_row, "rows")

Step 2: Append a single row

Load the file, select the sheet by name, append a list, and save. The list maps to columns A, B, C, D in order:

Python
from openpyxl import load_workbook

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

ws.append(["2024-07-15", "Server-04", "Critical", "Memory leak resolved"])

wb.save("monthly_report.xlsx")
print("Now", ws.max_row, "rows")

ws.append() always targets the row after the last one that contains data, so you never compute a position yourself for the common case.

Step 3: Append many rows in a loop

For batches, iterate and append. Accumulate the rows in memory first, then write them in one pass:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

batch = [
    ["2024-07-16", "DB-01", "Warning", "Index fragmentation"],
    ["2024-07-17", "Web-09", "Info", "Routine patch applied"],
    ["2024-07-18", "Cache-2", "Info", "TTL increased"],
]
for row in batch:
    ws.append(row)

wb.save("monthly_report.xlsx")
print("Appended", len(batch), "rows; total", ws.max_row)

Step 4: Append into specific columns with a dict

Pass a dict to write only certain columns, keyed by column letter or 1-based index. Unlisted columns stay empty. This is handy when a row only fills a few fields:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

# Only Date (A) and Status (C); Server and Note stay blank
ws.append({"A": "2024-07-19", "C": "Resolved"})

# Same idea using column numbers
ws.append({1: "2024-07-20", 3: "Info"})

wb.save("monthly_report.xlsx")
print("Row 8 values:", [c.value for c in ws[8]])

Step 5: Use the right types

Append native Python types, not strings, for anything you will sort, sum, or pivot later. Dates as datetime/date, numbers as int/float. A date stored as text will not aggregate or format like a real date:

Python
from datetime import date

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

ws.append([date(2024, 7, 21), "Batch-1", "Info", 1500])  # real date + real int
last_date = ws.cell(row=ws.max_row, column=1)
last_date.number_format = "yyyy-mm-dd"

wb.save("monthly_report.xlsx")
print("Appended typed row at", ws.max_row)

Step 6: Explicit row indexing when append misfires

ws.append() relies on ws.max_row, which tracks the largest used row. If a template has trailing blank rows, a frozen summary block, or rows that were deleted (deletion does not always shrink max_row until the file is reopened), append() can land in the wrong place. Write to explicit coordinates to guarantee placement:

Python
wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]

target = ws.max_row + 1
ws.cell(row=target, column=1, value="2024-07-22")
ws.cell(row=target, column=2, value="App-12")
ws.cell(row=target, column=3, value="Resolved")

wb.save("monthly_report.xlsx")
print("Wrote explicit row at", target)

Step 7: Save atomically

A crash or lock during wb.save() can corrupt the target file. Write to a temporary path first, then atomically replace the original with os.replace, which is a single filesystem operation:

Python
import os
from openpyxl import load_workbook

wb = load_workbook("monthly_report.xlsx")
ws = wb["Q3_Data"]
ws.append(["2024-07-23", "Web-03", "Info", "Health check"])

tmp = "monthly_report.tmp.xlsx"
wb.save(tmp)
os.replace(tmp, "monthly_report.xlsx")   # atomic on the same filesystem
print("Saved atomically;", ws.max_row, "rows total")

Constraints to keep in mind

  • File format: Only .xlsx, .xlsm, and .xltx/.xltm. A legacy .xls raises InvalidFileException — convert it first.
  • Formulas: append() writes raw values and formula strings; Excel recalculates formulas when it opens the file. openpyxl itself does not evaluate them.
  • File locks: If the workbook is open in Excel or another process, wb.save() may raise PermissionError. The atomic-save pattern above limits the window for corruption but cannot bypass an active lock.
  • Memory: load_workbook() reads the whole file into memory. For very large workbooks, append with a Workbook(write_only=True) stream or move bulk work to pandas.

Conclusion

ws.append() is the right tool when you can trust that ws.max_row reflects the real last-used row. For templates with trailing blank rows or deleted-row artifacts, skip it and write to explicit coordinates with ws.cell(row=target, column=n) instead. Either way, wrap the save in an atomic write-then-replace to protect against a corrupt file if the process is interrupted mid-save.

Where to go next