Append data in excel using Python
In the above example, you will see that every time you try to write to a spreadsheet the existing data gets overwritten, and the file is saved as a new file. This happens because the Workbook() method always creates a new workbook file object. To write to an existing workbook you must open the file with the load_workbook() method. We will use the above-created workbook.
Example:
In this example, the openpyxl module is employed to load an existing Excel workbook (“sample.xlsx”). The program accesses cell ‘A3’ in the active sheet, updates its value to “New Data,” and then saves the modified workbook back to “sample.xlsx.”
Python3
# import openpyxl module import openpyxl wb = openpyxl.load_workbook( "sample.xlsx" ) sheet = wb.active c = sheet[ 'A3' ] c.value = "New Data" wb.save( "sample.xlsx" ) |
Output:
We can also use the append() method to append multiple data at the end of the sheet.
Example:
In this example, the openpyxl module is utilized to load an existing Excel workbook (“sample.xlsx”). A two-dimensional data structure (tuple of tuples) is defined and iteratively appended to the active sheet, effectively adding rows with values (1, 2, 3) and (4, 5, 6).
Python3
# import openpyxl module import openpyxl wb = openpyxl.load_workbook( "sample.xlsx" ) sheet = wb.active data = ( ( 1 , 2 , 3 ), ( 4 , 5 , 6 ) ) for row in data: sheet.append(row) wb.save( 'sample.xlsx' ) |
Output:
Working with Excel Spreadsheets in Python
You all must have worked with Excel at some time in your life and must have felt the need to automate some repetitive or tedious task. Don’t worry in this tutorial we are going to learn about how to work with Excel using Python, or automating Excel using Python. We will be covering this with the help of the Openpyxl module and will also see how to get Python in Excel.