I am using openpyxl to create a table in an Excel file. You can use TableStyleInfo to select a default style from Excel, but I want to create a custom style with the company colours.

While looking through the documentation, I found class TableStyleElement. It seems like this is what I'm looking for, but I cannot find any instruction on how to use it.

I have currently applied the correct colors by by applying a NamedStyle cells to specific cell ranges, but a custom style would be more elegant.

Is there anyone that has used TableStyleElement to create custom styles? If so, it would be great if you explain how to use it.

I am using openpyxl to create a table in an Excel file. You can use TableStyleInfo to select a default style from Excel, but I want to create a custom style with the company colours.

While looking through the documentation, I found class TableStyleElement. It seems like this is what I'm looking for, but I cannot find any instruction on how to use it.

I have currently applied the correct colors by by applying a NamedStyle cells to specific cell ranges, but a custom style would be more elegant.

Is there anyone that has used TableStyleElement to create custom styles? If so, it would be great if you explain how to use it.

• python
• openpyxl

• Could you clarify how you're applying the custom styles using TableStyleElement? – MJepbarov Commented Feb 17 at 10:32
• Are you looking to style the entire table or specific parts, like headers or data rows? – MJepbarov Commented Feb 17 at 10:32
• Notwithstanding you may have already used Openpyxl to create your workbook and table. Given that what you requite may take some research to implement using Openpyxl, if you want to do this programmatically in python it would be generally trivial to create a custom table style and apply to a table using Xlwings (or even win32com). Even if it's a short term option and if you have no restriction on using either of these modules – moken Commented 2 days ago

1 Answer 1

TableStyleElement in openpyxl is not well-documented, and it doesn’t let you create fully custom table styles like Excel. There is a way - to use NamedStyle and apply it to specific cells. A short example:

from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, PatternFill
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
    ["Name", "Age", "Department"],
    ["Mike", 30, "HR"],
    ["Joram", 25, "IT"],
    ["Andrew", 35, "Finance"]
]
for row in data:
    ws.append(row)

# NamedStyle for headers
header_style = NamedStyle(name="HeaderStyle")
header_style.font = Font(bold=True, color="FFFFFF")
header_style.fill = PatternFill("solid", fgColor="4F81BD")  # color hex

# applying style to the header row
for cell in ws[1]:
    cell.style = header_style

table = Table(displayName="CompanyTable", ref="A1:C4")
table.tableStyleInfo = TableStyleInfo(
    name="TableStyleMedium9", showFirstColumn=False,
    showLastColumn=False, showRowStripes=True, showColumnStripes=False
)

ws.add_table(table)
wb.save("styled.xlsx")