Adding Custom Table Styles to Excel with Python

Info

If you're wondering what Excel's ListObject (table style) is, check out this blog post:

Introducing How to Add Custom Table Styles in Excel

The two on the right are custom table styles.
You can apply them by running the following command in PowerShell:

python .\excelActivebookAddTableStyle.py

Preparation and Verification

Make sure Python and xlwings are installed.
1. Reference: Installing Python and xlwings

Check if Python is Installed

Run the following and check if a version number appears:
1. ```
 python --version
```

Check if xlwings is Installed

Run the following and check if xlwings is listed:

```
 pip list
```

Example output:

 Package Version
 ------- -------
 pip     25.0.1
 pywin32 310
 xlwings 0.33.11

Folder Structure

You can save the files anywhere as long as Python can run them.
As long as the modules folder is in the same directory as excelActivebookAddTableStyle.py, it will work.

/
├ excelActivebookAddTableStyle.py
└ modules
  ├ addTableStyle.py
  ├ addTableStyleLightBlue.py
  └ addTableStyleLightGreen.py

Copy Files According to the Folder Structure

Source Files

excelActivebookAddTableStyle.py

# Example usage:
#   python .\excelActivebookAddTableStyle.py
# Debugging:
#   python .\excelActivebookAddTableStyle.py --debug

import argparse
import xlwings as xw
from modules.addTableStyleLightGreen import addTableStyleLightGreen
from modules.addTableStyleLightBlue import addTableStyleLightBlue

def main(debug: bool):
    # 既に起動しているExcelアプリケーションに接続
    # Connect to an already running Excel application
    try:
        app = xw.apps.active  # アクティブなExcelインスタンスを取得
        # app = xw.apps.active  # Get active Excel instance
        if debug:
            print("INFO: Connected to Excel application")
    except Exception as e:
        raise RuntimeError("ERROR: Excelが起動していません。Excelを起動してください。") from e
        # raise RuntimeError(“ERROR: Excel is not running, please start Excel.”) from e

    # アクティブなブックを取得
    # Get the active book
    try:
        wb = app.books.active
        bok = wb.api  # ここで underlying COM オブジェクトを取得
        # bok = wb.api  # get underlying COM object here
        if debug:
            print(f"INFO: Active workbook obtained: {bok.Name}")
    except Exception as e:
        print(f"ERROR: Failed to get active workbook: {e}")
        raise

    # テーブルスタイルを適用する関数を呼び出す
    # Call a function to apply a table style
    try:
        style_name = addTableStyleLightBlue(bok, debug)
        if debug:
            print(f"INFO: Added style: {style_name}")
    except Exception as e:
        print(f"ERROR: Failed to add TableStyleLightBlue style: {e}")

    try:
        style_name = addTableStyleLightGreen(bok, debug)
        if debug:
            print(f"INFO: Added style: {style_name}")
    except Exception as e:
        print(f"ERROR: Failed to add TableStyleLightGreen style: {e}")

    # COMオブジェクトの解放
    # Release COM object
    finally:
        bok = None
        if debug:
            print("INFO: COM objects released")

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description="Excel Table Style Adder")
    parser.add_argument('--debug', action='store_true', help="Enable debug mode")
    args = parser.parse_args()
    main(args.debug)

modules/addTableStyle.py

def RGB(r, g, b):
    # VBA の RGB(r, g, b) は r + (g * 256) + (b * 65536) と同等です
    # RGB(r, g, b) in VBA is equivalent to r + (g * 256) + (b * 65536)
    return r + (g << 8) + (b << 16)

# 定数(enum相当):
# Constants (equivalent to enum):
XL_WHOLE_TABLE    = 0
XL_HEADER_ROW     = 1
XL_TOTAL_ROW      = 2
XL_ROW_STRIPE1    = 5

XL_EDGE_LEFT      = 7
XL_EDGE_TOP       = 8
XL_EDGE_BOTTOM    = 9
XL_EDGE_RIGHT     = 10
XL_INSIDE_VERTICAL    = 11
XL_INSIDE_HORIZONTAL  = 12

XL_HAIRLINE       = 1

# 色情報用のクラス
# Color information class
class Colors:
    def __init__(self, font, line, back_dark, back_light):
        self.font = font
        self.line = line
        self.back_dark = back_dark
        self.back_light = back_light

def addTableStyle(bok, style_name, colors, debug=False):
    """
    bok: Excel COM object
    style_name: Name of table style to be created
    colors: Colors object
    debug: true=debug mode, false=normal mode
    """
    if debug:
        print(f"INFO: Adding table style: {style_name}")

    # 既存の同名スタイルがあれば削除する
    # Remove existing style with same name if any    
    try:
        existing_style = bok.TableStyles(style_name)
        existing_style.Delete()
        if debug:
            print(f"INFO: Existing style '{style_name}' deleted")
    except Exception as e:
        pass

    # 新しいテーブルスタイルを追加
    # Add new table style    
    try:
        table_style = bok.TableStyles.Add(style_name)
        if debug:
            print(f"INFO: Table style added successfully. table_style name: {table_style.Name}")
    except Exception as e:
        print(f"ERROR: Failed to add table style: {e}")
        raise


    # 全利用シーンで使用可能
    # Can be used in all usage scenarios
    table_style.ShowAsAvailableTableStyle = True
    table_style.ShowAsAvailablePivotTableStyle = True
    table_style.ShowAsAvailableSlicerStyle = False
    table_style.ShowAsAvailableTimelineStyle = False

    # 全体のテーブル要素の設定
    # Setup of entire table elements
    elem = table_style.TableStyleElements(XL_WHOLE_TABLE)
    elem.Borders(XL_EDGE_TOP).Color = colors.line
    elem.Borders(XL_EDGE_BOTTOM).Color = colors.line
    elem.Borders(XL_EDGE_LEFT).Color = colors.line
    elem.Borders(XL_EDGE_RIGHT).Color = colors.line
    elem.Borders(XL_INSIDE_VERTICAL).Color = colors.line
    elem.Borders(XL_INSIDE_VERTICAL).Weight = XL_HAIRLINE
    elem.Borders(XL_INSIDE_HORIZONTAL).Color = colors.line
    elem.Borders(XL_INSIDE_HORIZONTAL).Weight = XL_HAIRLINE

    # 行ストライプ要素の設定
    # Set row stripe elements
    elem = table_style.TableStyleElements(XL_ROW_STRIPE1)
    elem.Interior.Color = colors.back_light

    # ヘッダ行要素の設定
    # Set header row element
    elem = table_style.TableStyleElements(XL_HEADER_ROW)
    elem.Clear()
    elem.Interior.Color = colors.back_dark
    elem.Borders(XL_EDGE_BOTTOM).Color = colors.line
    fo = elem.Font
    fo.FontStyle = "太字"
    # fo.FontStyle = "bold"
    fo.Color = colors.font

    # トータル行要素の設定
    # Set total line element
    elem = table_style.TableStyleElements(XL_TOTAL_ROW)
    elem.Clear()
    elem.Interior.Color = colors.back_dark
    elem.Borders(XL_EDGE_BOTTOM).Color = colors.line
    fo = elem.Font
    fo.FontStyle = "太字"
    # fo.FontStyle = "bold"
    fo.Color = colors.font

    print(f"テーブルスタイルの登録成功: {style_name}")
    # print(f"Table style registration succeeded: {style_name}")

    return style_name

modules/addTableStyleLightBlue.py

from modules.addTableStyle import addTableStyle, Colors, RGB
import os

def addTableStyleLightBlue(bok, debug=False):
    if debug:
        print(f"INFO: {os.path.basename(__file__)} - テーブルスタイル追加")
        # print(f"INFO: {os.path.basename(__file__)} - Additional table style")
    colors = Colors(
        font=RGB(31, 78, 120),
        line=RGB(155, 194, 230),
        back_dark=RGB(152, 192, 228),
        back_light=RGB(221, 235, 247)
    )
    return addTableStyle(bok, "TableStyleLightBlue", colors, debug)

modules/addTableStyleLightGreen.py

from modules.addTableStyle import addTableStyle, Colors, RGB
import os

def addTableStyleLightGreen(bok, debug=False):
    if debug:
        print(f"INFO: {os.path.basename(__file__)} - テーブルスタイル追加")
        # print(f"INFO: {os.path.basename(__file__)} - Additional table style")
    colors = Colors(
        font=RGB(55, 86, 35),
        line=RGB(112, 173, 71),
        back_dark=RGB(198, 224, 180),
        back_light=RGB(248, 255, 242)
    )
    return addTableStyle(bok, "TableStyleLightGreen", colors, debug)

Explanation of Each Source File

excelActivebookAddTableStyle.py

The main script to execute
Adds the table styles
If you add new files beyond addTableStyleLightBlue.py, you’ll need to update this script to include and run them as well
1. Look for the section: Call a function to apply a table style

addTableStyle.py

Core logic for applying the table style
This is the implementation. Although it's written in Python, it's quite similar to how you'd write it in VBA

addTableStyleLightBlue.py

File for each table style
Separates design from logic—this one handles the style. Just specify the RGB values
1. Example: font=RGB(31, 78, 120)
Defines colors and uses addTableStyle to create the table style

How to Run

Open the Excel workbook where you want to apply the style and make it the active workbook

Run the following in PowerShell:

 python .\excelActivebookAddTableStyle.py

If the custom style is added successfully, you’re done!

Preparation and Verification​

Check if Python is Installed​

Check if xlwings is Installed​

Folder Structure​

Copy Files According to the Folder Structure​

Explanation of Each Source File​

excelActivebookAddTableStyle.py​

addTableStyle.py​

addTableStyleLightBlue.py​

How to Run​

Preparation and Verification

Check if Python is Installed

Check if xlwings is Installed

Folder Structure

Copy Files According to the Folder Structure

Explanation of Each Source File

excelActivebookAddTableStyle.py

addTableStyle.py

addTableStyleLightBlue.py

How to Run