How to use openpyxl to add a new column to an existing excel table

I have a template spreadsheet with eixisting tables and formula referring to data in the tables.

As a template, the tables start with one row of empty data.

I wish to add data (rows and columns) to the tables but retain working formula.

There is an excellent post here describing how to add rows to en existing table. This is working in my code.

A second post here attempts to add columns to a table by replacing the table with a new one. Unfortunately this breaks the formula.

Here's a snipit of my code:

     sSheetName = 'test'
     workbook = openpyxl.load_workbook(excelDumpFilename)
     sheet=workbook[sSheetName]
     dfCSVData = pd.read_csv(os.path.join(sCSVLocation,sCSVFile))
     sheetRow = 2
     # loop around each row in the CSV data
     for CSVrow in dataframe_to_rows(dfCSVData, header=False, index=False):
         sheetCol = 2
         sheet.insert_rows(sheetRow, 1)
         # loop around each value of the data row
         for CSVvalue in CSVrow:
             sheet.cell(row=sheetRow, column=sheetCol).value=CSVvalue
             sheetCol+=1
         sheetRow+=1
     table    = sheet.tables[sSheetName]
     currentTitleCount = len(table.column_names) 
     tableRows = dfCSVData.shape[0]
     tableCols = dfCSVData.shape[1]
     # Update table titles for non-generic fields
     for index in range(currentTitleCount, tableCols):
         sheet.cell(row=2, column=tableColStart+index).value=dfCSVData.columns[index]
         table.column_names.append( dfCSVData.columns[index] )
     newRange=openpyxl.worksheet.cell_range.CellRange(
         min_row=tableRowStart,
         min_col=tableColStart,
         max_row=tableRowStart + tableRows,
         max_col=tableColStart + tableCols - 1
         #max_col=tableColStart + currentTitleCount - 1
     )
     tTable.ref=newRange.coord
     tTable.autoFilter.ref=newRange.coord
 workbook.save(excelDumpFilename)

dfCSVData contains my new table data but the dimensions are larger than the table in the template sheet.

You can see that I update the template sheet with extended column names.

I also attempt to alter the table column_names directly:
table.column_names.append( dfCSVData.columns[index] )
but this fails to make any changes (examined under debug).

Using tableRows extends the length of my table. This is working.

Using tableCols (a larger number) corrupts the Excel file and Excel removes the table entirely the next time you open the sheet.

Help? Thanks

Like I said in my comment, you should really simplify the example to, say, increase a table from A1:D4 to A1:F6 and provide some sample data. This makes it a lot easier for people to help.

table.column_names is a property that just returns a list of column names. That's why changing this has no effect. You need to update the table.TableColumns. As you are updating the worksheet you have to choices. Firstly, use the private method table._initialise_columns(), or do this manually by editing or extending table.TableColumns. It's worth looking at the openpyxl source to see how this works.

Unfortunately, it's essential that table.TableColumns exactly matches the worksheet. This makes it trickier in openpyxl to edit the table because the table isn't really a worksheet object (this is how it's implemented in OOXML).