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 of 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 the 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:
In the above graphic, you can see three new rows are appended at the end of existing data.