How do I create a table using Openpyxl's table module?

Question

I'm attempting to create a script to process several Excel sheets at once and one of the steps i'm trying to get Python to handle is to create a table using data passed from a pandas data frame. Creating a table seems pretty straightforward looking at the documentation.

Following the example from here:

        # define a table style
        mediumstyle = TableStyleInfo(name='TableStyleMedium2', showRowStripes=True)

        # create a table
        table = Table(displayName='IdlingReport', ref='A1:C35', tableStyleInfo=mediumstyle)

        # add the table to the worksheet
        sheet2.add_table(table)

        # Saving the report
        wb.save(openexcel.filename)
        print('Report Saved')

However this creates an empty table, instead of using the data present in cells 'A1:C35'. I can't seem to find any examples anywhere that go beyond these steps so any help with what I may be doing wrong is greatly appreciated.

The data in 'A1:C35' is being written to Excel as follows:

    while i < len(self.sheets):
        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            writer.book = excelbook
            writer.sheets = dict((ws.title, ws) for ws in excelbook.worksheets)
            self.df_7.to_excel(writer, self.sheets[i], index=False, header=True, startcol=0, startrow=0)
            writer.save()
        i += 1

The output looks something like this

Time                Location                   Duration
1/01/2019     [-120085722,-254580042]           5 Min
1/02/2019     [-120085722,-254580042]           15 Min
1/02/2019     [-120085722,-254580042]           7 Min

Just to clarify right now I am first writing my data frame to Excel and then after formatting the data I've written as a table. Reversing these steps by creating the table first and then writing to Excel fills the table, but gets rid of the formatting(font color, font type, size, etc). Which means I'd have to add an additional step to fix the formatting(which i'd like to avoid if possible).

What do you mean by empty table? What data is in the rows A1:C35? — Charlie Clark, Oct 24 '19 at 10:33
I've edited my question to include the code i'm using to write my data frame to Excel. Hope it helps make things a little clearer. — joelime, Oct 24 '19 at 17:20

score 1 · Answer 1 · answered Oct 24 '19 at 01:00

Your command

# create a table
table = Table(displayName='IdlingReport', ref='A1:C35', tableStyleInfo=mediumstyle)

creates a special Excel object — an empty table with the name IdlingReport.

You probably want something else - to fill a sheet of your Excel workbook with data from a Pandas dataframe.

For this purpuse there is a function dataframe_to_rows():

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

wb = Workbook()
ws = wb.active   # to rename this sheet:  ws.title = "some_name"
                 # to create a new sheet: ws = wb.create_sheet("some_name")

for row in dataframe_to_rows(df, index=True, header=True):
    ws.append(row)    # appends this row after a previous one

wb.save("something.xlsx")

See Working with Pandas Dataframes and Tutorial.

I'm actually already writing to Excel using pd.to_excel. I've edited my question above to include this section of code as well as what the output looks like. — joelime, Oct 24 '19 at 17:25

How do I create a table using Openpyxl's table module?

1 Answers1