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" with 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')

Answer 1

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')

Answer 2

Stumbled across this problem 2020 with openpyxl==3.0.5 Hope it is okay to share my solution as well for others who might be in the same situation.

Goal: to read in new data from data.csv and add to the existing file.xlsx, so the formulas still work. Column names stay the same.

Input:

1. XLSX template file with formula on one sheet and data on other sheet (file.xlsx)
2. data.csv with new data

Output: XLSX file with new data and updated table range used in formula

"""python imports"""
import openpyxl
import pandas

"""Load input workbook"""
wb = openpyxl.load_workbook(filename='file.xlsx')

"""Activate worksheet named 'data'."""
ws = wb['data']

"""Read in new data from data1.csv to pandas.dataframe"""
new_dataframe = pandas.read_csv("data1.csv")

"""Iterate over dataframe rows and write values to worksheet"""
for i, row in new_dataframe.iterrows():
    # ws.append leaves first line empty
    # to get data written from first row,  need to use writing to cell
    if i == 0:
        for c, value in enumerate(row, start=1):
            ws.cell(row=2, column=c).value = value
    else:
        current_row = [row.col1, row.col2, row.col3]
        ws.append(current_row)

"""Change table range"""
ws.tables['MyTable'].ref = "A1:E5"

"""Save workbook"""
wb.save(filename='file.xlsx')

Answer to Ethan problem: how to just change range:

# Find right table
my_table = ws.tables['Template']
# Change range
my_table.ref = ref="A7:{}{}".format(column_string(maxRef[1], maxRef[0]))
# change style
my_table.tableStyleInfo = my_style
wb.save('WorkbookName')