5 Examples of Write/Append Data to Excel Using openpyxl

How to append data using openpyxl library

The openpyxl library has easy ways to add or append data to the Excel sheets.

The simplest way is to specify the cell number and give it a value and it will be replaced with the existing value or if the cell is empty, it is written to that empty cell.

An example replacing the existing value

In this example, we have a sheet with four headers. The sample Workbook contains “Product Information” and we have the following headers:

Product ID
Product Name
Price
Status

We will change the “Price” heading to “Product Price” by using openpyxl library.

For reference, this is the Sheet that we will work on:

Workbook Name: test_Excel.xlsx

Sheet Name: Product Information

We have dummy data as follows:

The code to replace the heading:

# Add/Append Data to Excel Sheets
# excel-python.py

from openpyxl import load_workbook

#Specify the Workbook

wb_append = load_workbook("test_Excel.xlsx")

sheet = wb_append.active

#Writing to C1 Cell

sheet["C1"] = "Product Price"

#Save the operation

wb_append.save('test_Excel.xlsx')

Output:

Writing a new row of data example

In this example, we will write a new row at the end of existing data. In this case, we know which row number is empty after the existing data i.e. A12 to D12.

Let’s provide the four values and save the operation and see the output:

The Code:

# Add/Append Data to Excel Sheets
# excel-python.py

from openpyxl import load_workbook

#Specify the Workbook

wb_append = load_workbook("test_Excel.xlsx")

sheet = wb_append.active

#Writing to A12 to D12 Cells

sheet["A12"] = "p-0011"

sheet["B12"] = "Brown Rice"

sheet["C12"] = "$5.7"

sheet["D12"] = "In Stock"

#Save the operation

wb_append.save('test_Excel.xlsx')

Output:

You can see the last row is added to the existing data.

Adding and fetching data

The Python program below inserts a new row to our sample file and then we fetch the complete dataset to confirm the entry in the Excel sheet.

Code:

# Add/Append Data to Excel Sheets
# excel-python.py

from openpyxl import load_workbook

#Specify the Workbook

wb_append = load_workbook("test_Excel.xlsx")

sheet = wb_append.active

#Writing to A12 to D12 Cells

sheet["A12"] = "p-0011"

sheet["B12"] = "Brown Rice"

sheet["C12"] = "$5.7"

sheet["D12"] = "In Stock"

#Save the operation

wb_append.save('test_Excel.xlsx')

#Fetching all rows from "Product Information" Sheet

for row in sheet.iter_rows(values_only=True):

    print(row)

Output:

('Product ID', 'Product Name', 'Product Price', 'Status')

('p-001', 'Wheat', 3.5, 'In Stock')

('p-002', 'Rice', 5.5, 'In Stock')

('p-003', 'Sugar', 2, 'In Stock')

('p-004', 'Maze', 1.5, 'In Stock')

('p-005', 'Tea Set', 5.55, 'In Stock')

('p-006', 'Dinner Set', 25.99, 'Out of Stock')

('p-007', 'Rice', 1.2, 'Out of Stock')

('p-008', 'Plates', 7, 'In Stock')

('p-009', 'Tea Spoon', 8, 'Out of Stock')

('p-0010', 'Table Spoon', 10, 'In Stock')

('p-0011', 'Brown Rice', '$5.7', 'In Stock')

You can compare the results in Python output and the image shared in the above example.

Appending data at the last of Sheet Example

In this example, we will append data to a Workbook sheet. Four rows are given and data is stored in a tuple of tuples. First, let us have a look at an example generally i.e. storing to a new workbook, and then we will work with our sample workbook in the next example.

Code for appending to a new workbook:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

#Append four columns data into five rows

rows = (

    ("Heading 1", "Heading 2", "Heading 3", "Heading 4"),

    (1, 1, 1, 1),

    (2, 2, 2, 2),

    (3, 3, 3, 3),

    (4, 4, 4, 4),

)

#Storing in tuple of tuples

for i in rows:

    ws.append(i)

#Saving the data

wb.save('append_test.xlsx')

Result:

The output of this code is creating a workbook with the name: “append_test.xlsx”. It contains five rows including a headings row as shown below:

Appending to an existing file

Running the above example code creates a new file every time and it looks like it does not append new lines at the end of the file – which should be the purpose of the append operation.

However, this is not the case. For appending at the end of an existing file, we need to load an existing file and then create rows (write new rows, export it, or whatever the source is), and then append that data to that loaded Excel file – in the active sheet; let us have a look with our sample excel file.

Code to append at the end of existing Excel sheet:

# Add/Append Data to Excel Sheets
# excel-python.py

from openpyxl import load_workbook

#Specify the Workbook

wb_append = load_workbook("test_Excel.xlsx")

sheet = wb_append.active
rows = (

("p-0011","Brown Rice", "$5.7", "In Stock"),

("p-0012","Black Rice", "$6.0", "In Stock"),

("p-0013","Barley", "$7.5", "In Stock"),

)

#Storing date in tuple of tuples

for row in rows:

    sheet.append(row)

#Saving the data in our sample workbook/sheet

wb_append.save('test_Excel.xlsx')

The result: