How to add a Table to Excel Worksheet using openpyxl

Question

I'm trying to add a table to an Excel worksheet using openpyxl.

I know how to add a dataframe to a Worksheet, and it works great. Here's my sample code:

import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame({
    'name': ['Lorem', 'Ipsum', 'Dolor', 'Sit', 'Amet'],
    'value_1': [4, 7, 2, 4, 6],
    'value_2': [1.23, 4.56, 7.13, 0.12, 0.]
})

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
for cell in ws[1]:
    cell.style = 'Pandas'
wb.save('pandas_example.xlsx')

However, I need to perform two aditional tasks:

Create a Table (data list object) using the inserted data, and
Style the table (colors, font weight, etcetera).

How can I do that?

It's covered in the documentation: https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html — Charlie Clark, Nov 15 '22 at 18:18
@CharlieClark Found it. So, task 1 is complete. And, although I know how to format the sheet cells individually, and can do that in this case, I'd like to know if there's a way to define a new table style (if I need to add many tables to the workbook, it would be nice to simply apply a single style instead of "manually" set the format for each cell. Can you point me in the right direction? — Barranka, Nov 15 '22 at 18:21

Barranka · Answer 1 · 2022-11-15T18:17:56.167

Well, for task one, I figured out a way. I'm not sure if it's the right way, but here it goes:

import pandas as pd

from openpyxl import Workbook
# Additional imports:
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame({
    'name': ['Lorem', 'Ipsum', 'Dolor', 'Sit', 'Amet'],
    'value_1': [4, 7, 2, 4, 6],
    'value_2': [1.23, 4.56, 7.13, 0.12, 0.]
})

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
# I don't need to style this as Pandas, so I'll skip it; 
# the table will (should) take care of the formatting.

# The reference is needed, so we have to get it somehow.
# Since the data is inserted starting in cell A1, all that remains 
# is to get the last column and last row address:
table_ref = 'A1:%s%d' % (get_column_letter(len(df.columns)), len(df.index))
# Add the table to the worksheet:
tab = Table(displayName='tbl_test', ref=table_ref)
ws.add_table(tab)

wb.save('pandas_example.xlsx')

This works like a charm! It adds the table and creates the autofilter and everything I need for task one. As for task two, I still don't know how to address it.

Update

Found a way to style the table. All I need is to style the header, so I can use a default table style and override only the header. The full code:

import pandas as pd

from openpyxl import Workbook
# Additional imports:
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows

df = pd.DataFrame({
    'name': ['Lorem', 'Ipsum', 'Dolor', 'Sit', 'Amet'],
    'value_1': [4, 7, 2, 4, 6],
    'value_2': [1.23, 4.56, 7.13, 0.12, 0.]
})

wb = Workbook()
ws = wb.active

for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)
# I don't need to style this as Pandas, so I'll skip it; 
# the table will (should) take care of the formatting.

# The reference is needed, so we have to get it somehow.
# Since the data is inserted starting in cell A1, all that remains 
# is to get the last column and last row address:
table_ref = 'A1:%s%d' % (get_column_letter(len(df.columns)), len(df.index))
# Add a table style
style = TableStyleInfo(name='TableStyleLight8', 
                       showFirstColumn=False, showLastColumn=False, 
                       showRowStripes=False, showColumnStripes=False)
tab.tableStyleInfo = style
# Add the table to the worksheet:
tab = Table(displayName='tbl_test', ref=table_ref)
ws.add_table(tab)

# Customize cells individually
fill = PatternFill(patternType='solid', fgColor=Color("b1005c"))
alignment = Alignment(horizontal='left', vertical='top', indent=1)
border = Border(bottom=Side(border_style='thick', color='000000'))
for cell in ws[1]:
    cell.fill = fill
    cell.alignment = alignment
    cell.border = border

# Save the workbook
wb.save('pandas_example.xlsx')

I don't know if this is the best way to go (iterating over the cells one by one is not what I had in mind), but it works.

If anyone has a better approach, please let me know .

How to add a Table to Excel Worksheet using openpyxl

1 Answers1