Append Data to Excel with Python

HOME

We are given an Excel file and our task is to append the data into this excel file using Python. In this article, we’ll explore openpyxl library to append data to an Excel file using Python.

Prerequisite:

Python is installed
PIP is installed
PyCharms is installed

Sample Excel

Step 1 – Install openpyxl

Openpyxl can be installed using pip

pip install openpyxl

Step 2 – Load the workbook

workbook = load_workbook("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

Step 3 – Select the active worksheet

sheet = workbook.active

Step 4 – Create new data to be appended to the Excel in the list of lists.

new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

Step 5 – Find the next available row in the sheet.

last_row = sheet.max_row + 1

Step 6 – Append the data to the excel by using the nested loops.

for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

Step 7 – Save the changes made to the workbook

workbook.save("C:\\Users\\Vibha\\Automation\\SearchInBing.xlsx")

The complete program is

from openpyxl.reader.excel import load_workbook
from openpyxl.styles import Font


#Load the workbook
workbook = load_workbook("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

sheet = workbook.active

#Sample data
new_data =[
    ["Scrum Master", "5-10 Years", "35K"],
    ["Consultatnt", "8-12 Years", "37K"]
]

#Append the new data to the sheet
last_row = sheet.max_row + 1
for i, row in enumerate(new_data, start=last_row):
    for j, value in enumerate(row, start=1):
        sheet.cell(row=i, column=j, value=value)

#Save the workbook
workbook.save("C:\\Users\\ykv12\\Documents\\Vibha\\Automation\\SearchInBing.xlsx")

print("Data is appended in the Excel file successfully")

The output of the above program is