How can I programmatically add rows to existing Excel table with Python

Question

Despite the various Python Excel-manipulation libraries and resources, I am unable to find a specific solution.

Right now, I have a template Excel file where a table exists. I would like to write a Python program in order to populate this table. Is this possible with any of the existing Excel libraries? The template Excel file has a sheet with an empty table (1st screenshot). I want to programmatically populate the table with some data from elsewhere. e.g.

data = [("TS0001", "1.0 Administration", "Root", "vdouk", "15/09/19", 8.0, "example 1"),
        ("TS0002", "1.0 Administration", "Root", "vdouk", "16/09/19", 3.0, "example 2"),
        ("TS0003", "4.0 Simulations", "Root", "vdouk", "16/09/19", 5.0, "example 3")]

Thus, I want finally to look like the 2nd screenshot. The total entries and subtotals are computed automatically from appropriate excel cell functions (already defined). Finally, note that this table is referenced from other parts of the workbook, meaning that it is a named data source (seen in the Excel name manager), so defining a new table will cause problems elsewhere. Can someone direct me to the most appropriate solution?

Here's the working logic.

1. Open an existing excel file.
2. Do not change any formatting.
3. Do not delete any existing data.
4. Update the excel file with new rows:
   1. Insert all new rows just below the header row.
   2. Make sure this puts new rows as part of the existing table.
5. Save and close the excel file.

Update

Right now, I am using openpyxl. The workaround I do is to edit the template .xlsx and add empty rows to the table. Then I write as many rows as my data tuples are by using openpyxl. The table totals seem to work, however some other excel macros seem to be breaking.

Answer 1

You may not update excel file with any existing Python modules but you can override the existing file with Openpyxl module.

With help of Openpyxl module you can read existing worksheet in memory append your new content and save it back to the same file:

from openpyxl import load_workbook
#Open an xlsx 
wb = load_workbook(filename = 'data.xlsx')
#Get the current Active Sheet
ws = wb.get_active_sheet()
#ws = wb.get_sheet_by_name("Sheet1")
# Get the row and column index in which you are going to write
ws.cell(1,1).value = 'your_value'
# save() will override existing file 
wb.save('data.xlsx')

Answer 2

Maybe you can use xltpl for this - A python module to generate xls/x files from a xls/x template.

Use your excel file as the template to generate a new file.

Answer 3

You can use Python module xlsxwriter. e.g

import xlsxwriter
workbook =xlsxwriter.Workbook('xD.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write(row, col,     'First Name')
workbook.close()