Excel operators

commons.excel_operators

Classes:

Name	Description
ExcelTheme	Theme configuration for Excel spreadsheet styling.
ExcelCellFormat	Cell formatting configuration for Excel cells.
ExcelColumn	Configuration for an Excel column.
ExcelWriter	Writer for creating formatted Excel spreadsheets.

Attributes:

Name	Type	Description
T

T module-attribute

T = TypeVar('T')

ExcelTheme

Bases: TypedDict

Theme configuration for Excel spreadsheet styling.

Defines color scheme for various UI elements including primary colors, accent colors, neutral tones, and status indicators.

Attributes:

Name	Type	Description
primary_bg_color	str	Primary background color (hex format)
primary_color	str	Primary text/foreground color (hex format)
accent_1_bg_color	str	First accent background color (hex format)
accent_1_color	str	First accent text/foreground color (hex format)
accent_2_bg_color	str	Second accent background color (hex format)
accent_2_color	str	Second accent text/foreground color (hex format)
neutral_dark	str	Dark neutral color (hex format)
neutral_mid	str	Medium neutral color (hex format)
neutral_light	str	Light neutral color (hex format)
positive	str	Positive status indicator color (hex format)
caution	str	Caution/warning status indicator color (hex format)
negative	str	Negative/error status indicator color (hex format)

primary_bg_color instance-attribute

primary_bg_color: str

primary_color instance-attribute

primary_color: str

accent_1_bg_color instance-attribute

accent_1_bg_color: str

accent_1_color instance-attribute

accent_1_color: str

accent_2_bg_color instance-attribute

accent_2_bg_color: str

accent_2_color instance-attribute

accent_2_color: str

neutral_dark instance-attribute

neutral_dark: str

neutral_mid instance-attribute

neutral_mid: str

neutral_light instance-attribute

neutral_light: str

positive_bg_color instance-attribute

positive_bg_color: str

positive_color instance-attribute

positive_color: str

caution_bg_color instance-attribute

caution_bg_color: str

caution_color instance-attribute

caution_color: str

negative_bg_color instance-attribute

negative_bg_color: str

negative_color instance-attribute

negative_color: str

ExcelCellFormat

Bases: TypedDict

Cell formatting configuration for Excel cells.

All fields are optional (total=False). Used to customize the appearance of cells including alignment, colors, borders, and text wrapping.

Attributes:

Name	Type	Description
align	Literal['left', 'center', 'right']	Horizontal text alignment ("left", "center", or "right")
bg_color	str	Background color (hex format)
bold	bool	Whether text should be bold
border	int	Border width for all sides (integer value)
bottom	int	Bottom border width (integer value)
color	str	Text/foreground color (hex format)
left	int	Left border width (integer value)
right	int	Right border width (integer value)
text_wrap	bool	Whether text should wrap within the cell
top	int	Top border width (integer value)
valign	Literal['top', 'center', 'bottom']	Vertical text alignment ("top", "center", or "bottom")

align instance-attribute

align: Literal['left', 'center', 'right']

bg_color instance-attribute

bg_color: str

bold instance-attribute

bold: bool

border instance-attribute

border: int

bottom instance-attribute

bottom: int

color instance-attribute

color: str

left instance-attribute

left: int

right instance-attribute

right: int

text_wrap instance-attribute

text_wrap: bool

top instance-attribute

top: int

valign instance-attribute

valign: Literal['top', 'center', 'bottom']

ExcelColumn dataclass

ExcelColumn(
    name: str,
    title: Optional[str] = None,
    width: Optional[int] = 40,
    hidden: bool = False,
    format: Optional[
        Callable[
            [ExcelTheme, ExcelCellFormat], ExcelCellFormat
        ]
    ] = None,
    header_format: Optional[
        Callable[
            [ExcelTheme, ExcelCellFormat], ExcelCellFormat
        ]
    ] = None,
    modify_cell: Optional[
        Callable[
            [ExcelTheme, Workbook, Worksheet, int, int],
            None,
        ]
    ] = None,
)

Configuration for an Excel column.

Defines the properties and formatting for a single column in an Excel worksheet, including name, display title, width, and custom formatting functions.

Attributes:

Name	Type	Description
name	str	Column name (used to reference data in DataFrame)
title	Optional[str]	Display title for column header (defaults to name if not provided)
width	Optional[int]	Column width in character units (default: 40)
format	Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]]	Optional function to customize cell format. Takes (theme, base_format) and returns modified ExcelCellFormat
header_format	Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]]	Optional function to customize header cell format. Takes (theme, base_format) and returns modified ExcelCellFormat
modify_cell	Optional[Callable[[ExcelTheme, Workbook, Worksheet, int, int], None]]	Optional callback for advanced cell modification. Takes (theme, workbook, worksheet, column_index, row_count) and allows direct manipulation of cells in the column

name instance-attribute

name: str

title class-attribute instance-attribute

title: Optional[str] = None

width class-attribute instance-attribute

width: Optional[int] = 40

hidden class-attribute instance-attribute

hidden: bool = False

format class-attribute instance-attribute

format: Optional[
    Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]
] = None

header_format class-attribute instance-attribute

header_format: Optional[
    Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]
] = None

modify_cell class-attribute instance-attribute

modify_cell: Optional[
    Callable[
        [ExcelTheme, Workbook, Worksheet, int, int], None
    ]
] = None

ExcelWriter

ExcelWriter(
    header_format: ExcelCellFormat,
    std_cell_format: ExcelCellFormat,
    theme: ExcelTheme,
)

Writer for creating formatted Excel spreadsheets.

Provides high-level API for creating Excel files from pandas DataFrames or Python objects with consistent formatting, theming, and styling.

Type Aliases:

Name	Description
CellFormat	Alias for ExcelCellFormat
Column	Alias for ExcelColumn
Theme	Alias for ExcelTheme

Usage

writer = ExcelWriter.create() writer.write_excel_from_df(df, Path("output.xlsx"), columns)

Initialize ExcelWriter with formatting configuration.

Note: Direct instantiation is discouraged. Use ExcelWriter.create() factory method instead for default configurations.

Parameters:

Name	Type	Description	Default
header_format	ExcelCellFormat	Format configuration for header cells	required
std_cell_format	ExcelCellFormat	Format configuration for standard data cells	required
theme	ExcelTheme	Color theme for styling	required

Classes:

Name	Description
StandardFormats	Collection of standard cell format presets.

Methods:

Name	Description
create	Create an ExcelWriter instance with optional custom configuration.
write_excel_from_df	Write a pandas DataFrame to an Excel file with formatting.
write_excel_from_objects	Write a list of Python objects to an Excel file with formatting.
write_excel_multi_sheet	Write multiple sheets to an Excel file with formatting.

Attributes:

Name	Type	Description
CellFormat
Column
Theme
Workbook
Worksheet
header_format
std_cell_format
theme

Source code in src/commons/excel_operators.py

def __init__(self, header_format: ExcelCellFormat, std_cell_format: ExcelCellFormat, theme: ExcelTheme) -> None:
    """Initialize ExcelWriter with formatting configuration.

    Note: Direct instantiation is discouraged. Use ExcelWriter.create() factory
    method instead for default configurations.

    Args:
       header_format: Format configuration for header cells
       std_cell_format: Format configuration for standard data cells
       theme: Color theme for styling
    """

    self.header_format = header_format
    self.std_cell_format = std_cell_format
    self.theme = theme

CellFormat class-attribute instance-attribute

CellFormat = ExcelCellFormat

Column class-attribute instance-attribute

Column = ExcelColumn

Theme class-attribute instance-attribute

Theme = ExcelTheme

Workbook class-attribute instance-attribute

Workbook = xlsxwriter.Workbook

Worksheet class-attribute instance-attribute

Worksheet = worksheet.Worksheet

header_format instance-attribute

header_format = header_format

std_cell_format instance-attribute

std_cell_format = std_cell_format

theme instance-attribute

theme = theme

StandardFormats

Collection of standard cell format presets.

Provides commonly used border format configurations. Border widths: - 1: Thin border - 2: Medium border

Attributes:

Name	Type	Description
border_bottom_thin	ExcelCellFormat	Thin bottom border (width 1)
border_bottom_medium	ExcelCellFormat	Medium bottom border (width 2)
border_left_thin	ExcelCellFormat	Thin left border (width 1)
border_left_medium	ExcelCellFormat	Medium left border (width 2)
border_right_thin	ExcelCellFormat	Thin right border (width 1)
border_right_medium	ExcelCellFormat	Medium right border (width 2)
border_top_thin	ExcelCellFormat	Thin top border (width 1)
border_top_medium	ExcelCellFormat	Medium top border (width 2)

border_bottom_thin class-attribute instance-attribute

border_bottom_thin: ExcelCellFormat = {'bottom': 1}

border_bottom_medium class-attribute instance-attribute

border_bottom_medium: ExcelCellFormat = {'bottom': 2}

border_left_thin class-attribute instance-attribute

border_left_thin: ExcelCellFormat = {'left': 1}

border_left_medium class-attribute instance-attribute

border_left_medium: ExcelCellFormat = {'left': 2}

border_right_thin class-attribute instance-attribute

border_right_thin: ExcelCellFormat = {'right': 1}

border_right_medium class-attribute instance-attribute

border_right_medium: ExcelCellFormat = {'right': 2}

border_top_thin class-attribute instance-attribute

border_top_thin: ExcelCellFormat = {'top': 1}

border_top_medium class-attribute instance-attribute

border_top_medium: ExcelCellFormat = {'top': 2}

create staticmethod

create(
    theme: Optional[ExcelTheme] = None,
    header_format_factory: Optional[
        Callable[
            [ExcelTheme, ExcelCellFormat], ExcelCellFormat
        ]
    ] = None,
    std_cell_format_factory: Optional[
        Callable[
            [ExcelTheme, ExcelCellFormat], ExcelCellFormat
        ]
    ] = None,
) -> ExcelWriter

Create an ExcelWriter instance with optional custom configuration.

Factory method that provides sensible defaults for theme and formatting. If no theme is provided, uses a blue-based professional color scheme.

Parameters:

Name	Type	Description	Default
theme	Optional[ExcelTheme]	Optional custom color theme. If None, uses default blue theme with primary color #00338D (deep blue) and complementary accents	None
header_format_factory	Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]]	Optional function to customize header format. Takes (theme, default_header_format) and returns modified format. Default header format includes: bold text, text wrap, top alignment, left alignment, primary theme colors, and medium bottom border	None
std_cell_format_factory	Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]]	Optional function to customize standard cell format. Takes (theme, default_cell_format) and returns modified format. Default cell format includes: top alignment and text wrap	None

Returns:

Type	Description
ExcelWriter	Configured ExcelWriter instance ready to write Excel files

Example

Use defaults

writer = ExcelWriter.create()

Custom theme

custom_theme = {"primary_bg_color": "#FF0000", ...} writer = ExcelWriter.create(theme=custom_theme)

Custom formatting

def my_header_fmt(theme, base_fmt): return {**base_fmt, "align": "center"} writer = ExcelWriter.create(header_format_factory=my_header_fmt)

Source code in src/commons/excel_operators.py

@staticmethod
def create(
    theme: Optional[ExcelTheme] = None,
    header_format_factory: Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]] = None,
    std_cell_format_factory: Optional[Callable[[ExcelTheme, ExcelCellFormat], ExcelCellFormat]] = None,
) -> "ExcelWriter":
    """Create an ExcelWriter instance with optional custom configuration.

    Factory method that provides sensible defaults for theme and formatting.
    If no theme is provided, uses a blue-based professional color scheme.

    Args:
       theme: Optional custom color theme. If None, uses default blue theme
          with primary color #00338D (deep blue) and complementary accents
       header_format_factory: Optional function to customize header format.
          Takes (theme, default_header_format) and returns modified format.
          Default header format includes: bold text, text wrap, top alignment,
          left alignment, primary theme colors, and medium bottom border
       std_cell_format_factory: Optional function to customize standard cell
          format. Takes (theme, default_cell_format) and returns modified format.
          Default cell format includes: top alignment and text wrap

    Returns:
       Configured ExcelWriter instance ready to write Excel files

    Example:
       # Use defaults
       writer = ExcelWriter.create()

       # Custom theme
       custom_theme = {"primary_bg_color": "#FF0000", ...}
       writer = ExcelWriter.create(theme=custom_theme)

       # Custom formatting
       def my_header_fmt(theme, base_fmt):
          return {**base_fmt, "align": "center"}
       writer = ExcelWriter.create(header_format_factory=my_header_fmt)
    """

    if not theme:
        theme = {
            "primary_bg_color": "#00338D",  # Deep Blue
            "primary_color": "#ffffff",
            "accent_1_bg_color": "#005EB8",  # Royal Blue
            "accent_1_color": "#ffffff",
            "accent_2_bg_color": "#94C7ED",  # Light Blue
            "accent_2_color": "#000000",
            "neutral_dark": "#4D4F53",
            "neutral_mid": "#A7A8AA",
            "neutral_light": "#E7E7E8",
            "positive_bg_color": "#76B947",
            "positive_color": "#000000",
            "caution_bg_color": "#FFC20E",
            "caution_color": "#000000",
            "negative_bg_color": "#D81E05",
            "negative_color": "#ffffff",
        }

    header_format: ExcelCellFormat = {
        "bold": True,
        "text_wrap": True,
        "valign": "top",
        "align": "left",
        "bg_color": theme["primary_bg_color"],
        "color": theme["primary_color"],
        **ExcelWriter.StandardFormats.border_bottom_medium,
    }
    if header_format_factory:
        header_format = header_format_factory(theme, header_format)

    std_cell_format: ExcelCellFormat = {"valign": "top", "text_wrap": True}
    if std_cell_format_factory:
        std_cell_format = std_cell_format_factory(theme, std_cell_format)

    return ExcelWriter(header_format, std_cell_format, theme)

write_excel_from_df

write_excel_from_df(
    df: DataFrame,
    path: Union[Path, BinaryIO],
    columns: Optional[List[ExcelColumn]] = None,
    sheet_name: str = "Sheet 1",
    sheet_fomatter: Optional[
        Callable[
            [
                Workbook,
                Worksheet,
                List[ExcelColumn],
                DataFrame,
            ],
            None,
        ]
    ] = None,
) -> None

Write a pandas DataFrame to an Excel file with formatting.

Creates an Excel workbook at the specified path with the DataFrame data. Automatically applies formatting, column widths, freeze panes, and autofilters.

Parameters:

Name	Type	Description	Default
df	DataFrame	pandas DataFrame to write to Excel	required
path	Union[Path, BinaryIO]	File path where Excel file should be written	required
columns	Optional[List[ExcelColumn]]	Optional list of ExcelColumn configurations. If None, creates default columns from df.columns with standard formatting	None
sheet_name	str	Name of the worksheet (default: "Sheet 1")	'Sheet 1'
sheet_fomatter	Optional[Callable[[Workbook, Worksheet, List[ExcelColumn], DataFrame], None]]	Optional callback for advanced worksheet customization. Takes (worksheet, workbook, columns, dataframe) for post-processing	None

Features

• Applies custom column widths and formatting
• Freezes top header row
• Adds autofilter to all columns
• Uses theme colors for headers
• Supports per-column format customization

Example

columns = [ ExcelColumn("name", width=30), ExcelColumn("email", width=40), ] writer.write_excel_from_df(df, Path("output.xlsx"), columns)

Source code in src/commons/excel_operators.py

def write_excel_from_df(
    self,
    df: pd.DataFrame,
    path: Union[Path, BinaryIO],
    columns: Optional[List[ExcelColumn]] = None,
    sheet_name: str = "Sheet 1",
    sheet_fomatter: Optional[
        Callable[[xlsxwriter.Workbook, worksheet.Worksheet, List[ExcelColumn], pd.DataFrame], None]
    ] = None,
) -> None:
    """Write a pandas DataFrame to an Excel file with formatting.

    Creates an Excel workbook at the specified path with the DataFrame data.
    Automatically applies formatting, column widths, freeze panes, and autofilters.

    Args:
       df: pandas DataFrame to write to Excel
       path: File path where Excel file should be written
       columns: Optional list of ExcelColumn configurations. If None, creates
          default columns from df.columns with standard formatting
       sheet_name: Name of the worksheet (default: "Sheet 1")
       sheet_fomatter: Optional callback for advanced worksheet customization.
          Takes (worksheet, workbook, columns, dataframe) for post-processing

    Features:
       - Applies custom column widths and formatting
       - Freezes top header row
       - Adds autofilter to all columns
       - Uses theme colors for headers
       - Supports per-column format customization

    Example:
       columns = [
          ExcelColumn("name", width=30),
          ExcelColumn("email", width=40),
       ]
       writer.write_excel_from_df(df, Path("output.xlsx"), columns)
    """

    if not columns:
        columns = [ExcelColumn(c) for c in df.columns]

    df = pd.DataFrame(df.to_dict(orient="records"))[[c.name for c in columns]]

    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        df.to_excel(writer, index=False, sheet_name=sheet_name)

        wb = cast(xlsxwriter.Workbook, writer.book)
        ws = cast(worksheet.Worksheet, writer.sheets[sheet_name])

        for i, col in enumerate(columns):
            hf = wb.add_format(
                col.header_format(self.theme, self.header_format) if col.header_format else self.header_format
            )
            cf = wb.add_format(col.format(self.theme, self.std_cell_format) if col.format else self.std_cell_format)

            ws.write(0, i, col.title or col.name, hf)

            if col.hidden:
                ws.set_column(i, i, None, cf, {"hidden": True})
            else:
                ws.set_column(i, i, col.width, cf)

            if col.modify_cell:
                col.modify_cell(self.theme, wb, ws, i, len(df))

        ws.freeze_panes(1, 0)
        ws.autofilter(0, 0, max(len(df), 1), len(columns) - 1)

        if sheet_fomatter:
            sheet_fomatter(wb, ws, columns, df)

    return

write_excel_from_objects

write_excel_from_objects(
    items: List[T],
    items_to_dict: Callable[[T], dict],
    path: Union[Path, BinaryIO],
    columns: List[ExcelColumn],
    sheet_name: str = "Sheet 1",
    sheet_fomatter: Optional[
        Callable[
            [
                Workbook,
                Worksheet,
                List[ExcelColumn],
                DataFrame,
            ],
            None,
        ]
    ] = None,
) -> None

Write a list of Python objects to an Excel file with formatting.

Converts objects to dictionaries, then to DataFrame, and writes to Excel. Provides a convenient interface for writing domain objects or dataclasses to Excel without manual DataFrame conversion.

Parameters:

Name	Type	Description	Default
items	List[T]	List of objects to write to Excel	required
items_to_dict	Callable[[T], dict]	Function that converts each object to a dictionary. Dictionary keys should match column names	required
path	Union[Path, BinaryIO]	File path where Excel file should be written	required
columns	List[ExcelColumn]	List of ExcelColumn configurations defining column order, widths, and formatting	required
sheet_name	str	Name of the worksheet (default: "Sheet 1")	'Sheet 1'
sheet_fomatter	Optional[Callable[[Workbook, Worksheet, List[ExcelColumn], DataFrame], None]]	Optional callback for advanced worksheet customization. Takes (worksheet, workbook, columns, dataframe) for post-processing	None

Note

If items list is empty, creates an Excel file with headers only.

Example

@dataclass class Person: name: str age: int

people = [Person("Alice", 30), Person("Bob", 25)] columns = [ ExcelColumn("name", width=30), ExcelColumn("age", width=10), ]

def person_to_dict(p: Person) -> dict: return {"name": p.name, "age": p.age}

writer.write_excel_from_objects( people, person_to_dict, Path("people.xlsx"), columns )

Source code in src/commons/excel_operators.py

def write_excel_from_objects(
    self,
    items: List[T],
    items_to_dict: Callable[[T], dict],
    path: Union[Path, BinaryIO],
    columns: List[ExcelColumn],
    sheet_name: str = "Sheet 1",
    sheet_fomatter: Optional[
        Callable[[xlsxwriter.Workbook, worksheet.Worksheet, List[ExcelColumn], pd.DataFrame], None]
    ] = None,
) -> None:
    """Write a list of Python objects to an Excel file with formatting.

    Converts objects to dictionaries, then to DataFrame, and writes to Excel.
    Provides a convenient interface for writing domain objects or dataclasses
    to Excel without manual DataFrame conversion.

    Args:
       items: List of objects to write to Excel
       items_to_dict: Function that converts each object to a dictionary.
          Dictionary keys should match column names
       path: File path where Excel file should be written
       columns: List of ExcelColumn configurations defining column order,
          widths, and formatting
       sheet_name: Name of the worksheet (default: "Sheet 1")
       sheet_fomatter: Optional callback for advanced worksheet customization.
          Takes (worksheet, workbook, columns, dataframe) for post-processing

    Note:
       If items list is empty, creates an Excel file with headers only.

    Example:
       @dataclass
       class Person:
          name: str
          age: int

       people = [Person("Alice", 30), Person("Bob", 25)]
       columns = [
          ExcelColumn("name", width=30),
          ExcelColumn("age", width=10),
       ]

       def person_to_dict(p: Person) -> dict:
          return {"name": p.name, "age": p.age}

       writer.write_excel_from_objects(
          people, person_to_dict, Path("people.xlsx"), columns
       )
    """

    rows = [items_to_dict(i) for i in items]
    if not rows:
        rows = [{}]

    df = pd.DataFrame(rows)[[c.name for c in columns]]
    self.write_excel_from_df(df, path, columns, sheet_name, sheet_fomatter)

write_excel_multi_sheet

write_excel_multi_sheet(
    sheets: List[tuple[str, DataFrame, List[ExcelColumn]]],
    path: Union[Path, BinaryIO],
) -> None

Write multiple sheets to an Excel file with formatting.

Creates an Excel workbook with multiple worksheets, each with its own DataFrame and column configuration.

Parameters:

Name	Type	Description	Default
sheets	List[tuple[str, DataFrame, List[ExcelColumn]]]	List of tuples, each containing: - sheet_name: Name of the worksheet - df: pandas DataFrame to write - columns: List of ExcelColumn configurations	required
path	Union[Path, BinaryIO]	File path where Excel file should be written	required

Example

sheets = [ ("Users", users_df, user_columns), ("Orders", orders_df, order_columns), ] writer.write_excel_multi_sheet(sheets, Path("output.xlsx"))

Source code in src/commons/excel_operators.py

def write_excel_multi_sheet(
    self,
    sheets: List[tuple[str, pd.DataFrame, List[ExcelColumn]]],
    path: Union[Path, BinaryIO],
) -> None:
    """Write multiple sheets to an Excel file with formatting.

    Creates an Excel workbook with multiple worksheets, each with its own
    DataFrame and column configuration.

    Args:
       sheets: List of tuples, each containing:
          - sheet_name: Name of the worksheet
          - df: pandas DataFrame to write
          - columns: List of ExcelColumn configurations
       path: File path where Excel file should be written

    Example:
       sheets = [
          ("Users", users_df, user_columns),
          ("Orders", orders_df, order_columns),
       ]
       writer.write_excel_multi_sheet(sheets, Path("output.xlsx"))
    """
    with pd.ExcelWriter(path, engine="xlsxwriter") as writer:
        for sheet_name, df, columns in sheets:
            if not columns:
                columns = [ExcelColumn(c) for c in df.columns]

            df = pd.DataFrame(df.to_dict(orient="records"))
            if columns:
                df = df[[c.name for c in columns]]

            df.to_excel(writer, index=False, sheet_name=sheet_name)

            wb = cast(xlsxwriter.Workbook, writer.book)
            ws = cast(worksheet.Worksheet, writer.sheets[sheet_name])

            for i, col in enumerate(columns):
                hf = wb.add_format(
                    col.header_format(self.theme, self.header_format) if col.header_format else self.header_format
                )
                cf = wb.add_format(
                    col.format(self.theme, self.std_cell_format) if col.format else self.std_cell_format
                )

                ws.write(0, i, col.title or col.name, hf)

                if col.hidden:
                    ws.set_column(i, i, None, cf, {"hidden": True})
                else:
                    ws.set_column(i, i, col.width, cf)

                if col.modify_cell:
                    col.modify_cell(self.theme, wb, ws, i, len(df))

            ws.freeze_panes(1, 0)
            ws.autofilter(0, 0, max(len(df), 1), len(columns) - 1)