Manipulate existing excel table using openpyxl

Question

I'm currently honing my python / excel skills, and have run into an issue with openpyxl

I am trying to open a workbook, replace rows in an existing table, and save the workbook again.

Ideally I'd like to also first be able delete all rows from the table (Though retaining the table structure)

My initial workbook contains a sheet named "inputData". In this I have a table named "Data" columns A, B, C and 2 rows of data.

I also have a csv file named "input.csv" containing The same columns but 4 rows of data.

when I run my code, the data is written into the worksheet, but the table structure is not expanded to encompass the two new rows of data.

Any ideas of how to change the data source of a named table structure using openpyxl?

import csv
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = load_workbook(filename = 'workbook.xlsx')
ws = wb["inputData"]

with open('input.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader):
        if not i == 0:
            for j, cell in enumerate(row): 
                ws.cell(row=i+1, column=j+1).value = cell

wb.save('output.xlsx')

Link to files

Can you explain better what you're trying to do? What do you mean by table structure/data source? is it ws? Are you trying to delete a row? add a row? The code looks like it's just changing cells - is that what you want? — kabanus
In excel you are able to enter data directly into the worksheet, or create a table structure support.office.com/en-us/article/…, I wish to use python to manipulate an existing table, not just add rows to a flat worksheet — Henrik Poulsen
You will need to change the structure of the table yourself. This should be possible but it is undocumented. — Charlie Clark
So you want to add rows to ws (for example), and then save this changed table to the original document? — kabanus

Henrik Poulsen Henrik Poulsen · Accepted Answer · 2018-02-07T10:00:18

I figured out the answer to my question.

I am able to access the table from openpyxl, change the ref (range) and then save it back again.

This enables me to enter more data into the same table, and have my formulas on my other worksheet take the new data into account.

This will be a very helpful feature, when I need to push a lot of data into an existing excel sheet without breaking references in the future.

import csv
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
tableName = 'Data'

style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=False)

def colnum_string(n):
    string = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        string = chr(65 + remainder) + string
    return string

wb = load_workbook(filename = 'workbook.xlsx')
ws = wb["inputData"]

with open('input.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader):
        for j, cell in enumerate(row): 
            if not i == 0:
                ws.cell(row=i+1, column=j+1).value = float(cell)
            else:
                ws.cell(row=i+1, column=j+1).value = cell

            maxRef = [i,j]

for i, table in enumerate(ws._tables):
    if table.name == tableName:
        tableRef = i

resTable = Table(displayName="Data", ref="A1:{}{}".format(colnum_string(maxRef[0]), maxRef[1]))
resTable.tableStyleInfo = style

ws._tables[tableRef] = resTable

wb.save('output.xlsx')

Manipulate existing excel table using openpyxl

3 Answers