pythonexcelopenpyxl

Change table range with openpyxl

Lets assume that I have a table named "balance" which contains calculated columns (columns that are automatically filled with formulas when a new line is added). I also have a program in Python that adds data to the "balance" table. The data is added correctly, however, the table does not automatically expand to accommodate the new data. As a result, I believe that I need to adjust the table reference manually.

For instance, if my initial table reference was "A1:N2," after the table has been populated with data, the reference should be changed to "A1:N101." What I require is a method to modify the table reference to "A1:N101."

I've tried all the methods suggested in this post: Manipulate existing excel table using openpyxl but none of them worked

Does anyone know how to do it properly?

Solution

  • The features like automatically expanding a table to include a new row or extend a column formula in a Table are part of the Excel Application. These features are not available with Openpyxl because that module does not use Excel.

    However to extend a Table in Openpyxl after adding row(s), it's very simple

    Note this will only work for adding additional rows to the Table. It will not work if adding additional columns. For that you would need to remove and recreate the Table.

    Given your example of data in a Table we'll assume is called 'balance' and adding one new row (row 3) to this Table.

    Code sample;
    In this example the first column is a Title and the last column is a total of the intermediately lines which contain numeric data
    The formula needs to be specific to the row since Openpyxl will add it as is.
    Assumes existing Table called 'balance' covering the range A1:N2

    from openpyxl import load_workbook

wb = load_workbook("Table.xlsx")
ws = wb["Sheet1"]

### Add new row, includes formula
ws.append(['Plums', 2000, 3000, 4000, 5000, 8000, 560, 6000, 2000, 3000, 4000, 5000, 500, '=SUM(B3:M3)'])

ws.tables['balance'].ref = "A1:N3"

wb.save('Table2.xlsx')

    Therefore for your case, just add all the additional rows and update the table reference;
    ws.tables['balance'].ref = "A1:N101"

    If you don't know the table name or if there is more than one table you can search for the table you require and obtain its co-ordinates using

    for tbl in ws.tables:
   print(f"Table: '{ws.tables[tbl].name}' Range: {ws.tables[tbl].ref}")