Custom XLSX Styles for Report Exports

Starting with Version 16, you can customize the appearance of Excel exports for standard Script Reports and Query Reports using the get_xlsx_styles method.

Available from Version 16

Note:

The get_xlsx_styles() method is called only when exporting standard Script Reports or Query Reports to Excel (.xlsx). It is not used for CSV exports.

Basic Usage

Define a get_xlsx_styles() function in your report’s Python file and use the XLSXStyleBuilder class to register and apply styles.

from frappe.utils.xlsxutils import XLSXMetadata, XLSXStyleBuilder

def get_xlsx_styles(metadata: XLSXMetadata) -> dict:
    builder = XLSXStyleBuilder(metadata)

    # Add your custom styles here...

    return builder.result

Tip:

Header formatting, filters, currency formatting, and field type styles are automatically applied when XLSXStyleBuilder is initialized.

To disable the default styling, initialize the builder as follows:

builder = XLSXStyleBuilder(metadata, default_styling=False)

XLSXMetadata

The metadata object provides information about the report being exported along with helper methods for working with rows and columns.

Attribute	Type	Description
`report_name`	`str`	Name of the report.
`filters`	`dict`	Raw filter values.
`column_map`	`dict[int, dict]`	Maps column indexes to column definitions.
`row_map`	`dict[int, dict \| list]`	Maps row indexes to report data.
`applied_filters_map`	`dict[int, list]`	Maps filter row indexes to label and value pairs.
`has_total_row`	`bool`	Indicates whether the report contains a total row.
`has_indentation`	`bool`	Indicates whether indentation styling is available.

Note:

All row and column indexes use 0-based indexing, following the indexing convention used by XlsxWriter.

XLSXStyleBuilder

The XLSXStyleBuilder class is used to register reusable styles and apply them to rows, columns, or individual cells.

Registering Styles

style_id = builder.register_style({
    "bold": True,
    "bg_color": "#FFFF00"
})

Applying Styles

After registering a style, it can be applied to different parts of the worksheet.

# Apply to an entire column
builder.style_column(col_idx, style_id)

# Apply to an entire row
builder.style_row(row_idx, style_id)

# Apply to a specific cell
builder.style_cell(row_idx, col_idx, style_id)

Supported Style Properties

Font Styles

Customize text appearance using font-related properties.

builder.register_style({
    "bold": True,
    "italic": True,
    "underline": True,
    "font_size": 12,
    "font_color": "#FF0000",
    "font_name": "Arial"
})

Background Colors

Apply background colors to cells or ranges.

builder.register_style({
    "bg_color": "#FFFF00"
})

Borders

Configure border thickness and colors for cells.

builder.register_style({
    "border": 1,
    "border_color": "#000000"
})

builder.register_style({
    "left": 2,
    "right": 2,
    "top": 1,
    "bottom": 3
})

Alignment

Control horizontal alignment, vertical alignment, indentation, and text wrapping.

builder.register_style({
    "align": "center",
    "valign": "vcenter",
    "text_wrap": True,
    "indent": 2
})

Number Formats

Specify custom formatting for numeric values.

builder.register_style({
    "num_format": "#,##0.00"
})

Styles can be reused across multiple rows, columns, and cells after they are registered.

Complete Example

The following example demonstrates how to register reusable styles, format columns, highlight rows, and apply conditional formatting based on report values.

from frappe.utils.xlsxutils import XLSXMetadata, XLSXStyleBuilder

def get_xlsx_styles(metadata: XLSXMetadata) -> dict:
    builder = XLSXStyleBuilder(metadata)

    success_style = builder.register_style({
        "font_color": "#006100",
        "bg_color": "#C6EFCE"
    })

    warning_style = builder.register_style({
        "font_color": "#9C5700",
        "bg_color": "#FFEB9C"
    })

    error_style = builder.register_style({
        "font_color": "#9C0006",
        "bg_color": "#FFC7CE"
    })

    highlight_style = builder.register_style({
        "bold": True,
        "bg_color": "#DDEBF7"
    })

    total_row_border = builder.register_style({
        "border": 3,
        "border_color": "#15137e"
    })

    right_align = builder.register_style({
        "align": "right"
    })

    amount_col_idx = builder.field_index_map.get("net_amount")

    if amount_col_idx is not None:
        builder.style_column(amount_col_idx, right_align)

    if metadata.has_total_row:
        builder.style_row(metadata.get_last_row_index(), total_row_border)

    status_col_idx = builder.field_index_map.get("status")

    for row_idx, row_data in metadata.row_map.items():
        if not isinstance(row_data, dict):
            continue

        if status_col_idx is not None:
            status = row_data.get("status")

            if status == "Completed":
                builder.style_cell(row_idx, status_col_idx, success_style)

            elif status == "Pending":
                builder.style_cell(row_idx, status_col_idx, warning_style)

            elif status == "Cancelled":
                builder.style_cell(row_idx, status_col_idx, error_style)

        if amount_col_idx is not None:
            amount = row_data.get("net_amount")

            if amount and amount > 10000:
                builder.style_cell(row_idx, amount_col_idx, highlight_style)

    return builder.result

Best Practice:

Register each style only once and reuse the returned style ID throughout the report. This reduces duplicate style definitions and improves export performance.

Reference

For the complete list of supported formatting properties, refer to the official XlsxWriter format documentation.

XLSXStyleBuilder supports the same formatting properties available through the XlsxWriter library.

Rating: 0 / 5 (0 votes)