Python Add New Rows To Excel File (Append Prepend Insert)

Welcome to a tutorial on how to append, prepend, and insert new rows into an existing Excel file in Python. So you need to modify an Excel file in Python? Well, it’s not that difficult – Read on for an example!

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

QUICK SETUP

Python does not have native modules/functions to read Excel files. For this demo, we will create a virtual environment and install the openpyxl module.

• virtualenv venv
• venv\Scripts\activate (Windows) venv/bin/activate (Linux/Mac)
• pip install openpyxl

 

ADD NEW ROWS TO EXCEL

# (A) LOAD EXCEL FILE
from openpyxl import load_workbook
wb = load_workbook(filename = "demo.xlsx")
ws = wb.active
 
# (B) APPEND ROW
ws.append(["A", "B"])
 
# (C) PREPEND ROW
ws.insert_rows(1)
ws["A1"] = "C"
ws["B1"] = "D"
 
# (D) INSERT ROW (LOOP)
data = ["E", "F"]
r = 5
c = 1
ws.insert_rows(r)
for v in data:
  ws.cell(row=r, column=c, value=v)
  c += 1
 
# (E) INSERT ROW ("HACK")
ws.insert_rows(7)
ws._current_row = 6
ws.append(["G", "H"])
 
# (F) SAVE
wb.save("updated.xlsx")

1. Use the openpyxl module to load the Excel file.
   • For those who are new, a “workbook” refers to the entire file and it can contain many “worksheets”.
   • ws = wb.active will use the last worksheet you edited.
   • You can also use ws = wb["TITLE"] to refer to an exact worksheet.
2. ws.append() Self-explanatory, add a new row to the end of the worksheet.
3. Prepending is not as easy.
   • ws.insert_rows(1) Insert a new row right at the top.
   • ws["CELL"] = VALUE To assign the cell values.
4. If you need to insert a row at an exact location.
   • Use the same old ws.insert_rows(ROW) to insert a row.
   • As above, we can use ws["CELL"] to set a cell value.
   • Another method is to use ws.cell(row=R, column=C, value=V)
5. A convenient “hack” to insert a row.
   • Insert a row as usual, then use ws._current_row = ROW - 1 to set the internal pointer.
   • Use ws.append() to insert a new row.
6. Lastly, don’t forget to save the updated Excel file.

 

 

 

LINKS & REFERENCES

• openpyxl documentation
• Display Excel In HTML Table – Code Boxx