How to append data in excel using openpyxl in Python
Suppose you want to add or append data to an excel sheet using Python. In this case, we can make use of the openpyxl library that is available in Python which can easily append data to an EXCEL file. Thus in the tutorial, we will learn how to append data in excel using openpyxl in Python.
Follow the steps given below to append data in excel using openpyxl in Python:
Step 1: Openpyxl Installation
To operate with EXCEL sheets in Python we can make use of openpyxl which is an open-source library available in Python. it is easy to use with simplified instructions and can be used to read and write data in these EXCEL sheets. To install openpyxl on your local computer execute the below-mentioned command.
pip install openpyxl
Step 2: Import the workbook module from Openpyxl
Next, we will import the openpyxl library of Python.
Step 3: Generate a new workbook
In this step, we will generate a completely new workbook that will initially contain a single worksheet. In order to activate this sheet and use it for read and write operations, we use the
wb = openpyxl.Workbook() ws = wb.active
Initially, the worksheet is empty as shown below:
Step 4: Define data to be appended
In this step, we are going to declare the data that is to be appended in the form of tuples. Let us consider our worksheet in EXCEL contains these attributes: Product, Cost Price, and Selling Price. Our aim in this scenario is to add three more rows containing the corresponding values to this EXCEL sheet. So we will define the group of values in a single tuple called ‘data’.
data = ( ("Product","Cost Price","Selling Price"), ("earpod",90, 50), ("laptop", 3000, 8200), ("smartphone", 5100, 7200) )
Step 5: Append data to Worksheet
In this step, we will apply a for loop on the data tuple. It will thus iterate over every row inside the tuple. Each row’s contents are appended to the excel Worksheet using the
append() function available in the openpyxl library.
append() function is used to add a group of values at the end of the current worksheet. If it is a completely new worksheet then it will simply add the values to this new worksheet.
for i in data: ws.append(i)
Step 6: Use save() function
Lastly, we will use the
save() function of openpyxl to save the entire workbook with the appended changes. The desired path where the workbook is to be saved is taken as a parameter to this function.
The final output.xlsx file is:
Thus we reach the end of this tutorial on how to append data in excel using openpyxl in Python. To read more about openpyxl click on the following link: Copy data from one excel sheet to another using openpyxl in Python
Leave a Reply