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.

ws.append() writes new rows below the existing data An existing sheet holds rows 1 to 3; calling ws.append() twice writes rows 4 and 5 to the first empty rows at the bottom, leaving the existing rows untouched. Existing sheet 1 Date · Server · Status 2 Web-01 · Info 3 Web-02 · Warn 4 Web-03 · Info 5 Web-04 · Error ws.append([...]) writes to the first empty row below new rows

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.

Frequently asked questions

Where does ws.append() put the new row? It writes to the first row after ws.max_row, the largest used row, and maps each list item to columns A, B, C, and so on. You never compute the position yourself in the common case.

Why did append() land in the wrong place?append() trusts ws.max_row. Trailing blank rows, a frozen summary block, or rows deleted without reopening the file can leave max_row stale, so the row lands too low. Write to explicit ws.cell(row=target, column=n) coordinates instead.

Can I append into only some columns? Yes. Pass a dict keyed by column letter ({"A": ..., "C": ...}) or 1-based index ({1: ..., 3: ...}). Unlisted columns stay empty for that row.

Why won't my appended dates sort or sum like dates? You probably appended them as strings. Append native datetime/date and int/float values, then set number_format on the cell for display — text values won't aggregate or format as real dates.

How do I avoid corrupting the file if the save is interrupted? Save to a temporary path, then os.replace(tmp, target) to swap it in atomically on the same filesystem. This limits the corruption window but cannot bypass an active file lock, which still raises PermissionError.

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