Shared styles and corporate style

Why share styles

When an application produces several reports, declaring the same header, zebra and money styles on every sheet class leads to copies that drift apart over time. A style sheet declares named styles once; every sheet class that references it uses them by name. Changing the style sheet changes every report.

Combined with the application defaults (preset, accent colour, default formats) and the document properties, style sheets let every workbook of an application, or of an organisation through a shared library, follow the same business or corporate style.

Declaring a style sheet

@ExcelStyleSheet
@ExcelStyle(name = "corp-title", fontName = "Arial", fontSize = 16, bold = Toggle.TRUE, fontColor = "#0B3D5C")
@ExcelStyle(name = "corp-header", fontName = "Arial", bold = Toggle.TRUE, fillColor = "#0B3D5C",
        fontColor = "#FFFFFF", verticalAlign = VerticalAlign.CENTER, wrapText = Toggle.TRUE)
@ExcelStyle(name = "corp-cell", fontName = "Arial", fontSize = 10)
@ExcelStyle(name = "corp-zebra", fillColor = "#EEF4F8")
@ExcelStyle(name = "corp-money", align = Align.RIGHT, dataFormat = "#,##0.00")
@ExcelStyle(name = "corp-date", align = Align.CENTER, dataFormat = "dd/mm/yyyy")
@ExcelStyle(name = "corp-total", bold = Toggle.TRUE, borderTop = Border.DOUBLE, borderTopColor = "#0B3D5C")
public final class CorporateStyles {
    private CorporateStyles() {
    }
}

Using it

@ExcelSheet(
        title = "Monthly orders",
        titleStyle = "corp-title",
        styleSheets = CorporateStyles.class,
        header = @HeaderStyles(base = "corp-header"),
        body = @BodyStyles(base = "corp-cell", odd = "corp-zebra", lastRow = "corp-total"),
        autoFilter = true)
public record OrderRow(
        @ExcelColumn(header = "Order", order = 10) String number,
        @ExcelColumn(header = "Date", order = 20, styles = @ColumnStyles(base = "corp-date")) LocalDate date,
        @ExcelColumn(header = "Amount", order = 30, styles = @ColumnStyles(base = "corp-money")) BigDecimal amount) {
}

Rules

Rule	Consequence
Only classes with @ExcelStyleSheet can be referenced (V-09).	A forgotten annotation is reported, and the styles of that class are not available (references to them report V-06).
Names are unique within one style sheet (V-07).	The error names the style sheet class.
Style sheets referenced together must not share a name (V-08).	Two style sheets used by the same class cannot both define header. Use prefixes (corp-, fin-) to avoid collisions, or redefine the style on the class.
A style on the sheet class replaces a shared style with the same name.	Local customisation of a single report, without touching the style sheet. The replacement is complete: attributes are not merged.
Style sheets are not inherited and do not reference other style sheets.	Each sheet class lists the style sheets it uses.

Splitting style sheets

Large organisations can split the corporate style into several style sheets: a base one with fonts and headers, a finance one with money and percentage formats, a reporting one with totals. A sheet class lists the ones it needs: styleSheets = {CorporateStyles.class, FinanceStyles.class}. Prefixes keep the names unique across them.

Distributing the corporate style

A style sheet is an ordinary class. To share it across applications, place it, together with a recommended SheetsmithDefaults and DocumentProperties, in a small internal library on which every application depends:

public final class CorporateSheetsmith {

    public static final SheetsmithDefaults DEFAULTS =
            new SheetsmithDefaults("dd/mm/yyyy", "dd/mm/yyyy hh:mm", "#,##0.00", TablePreset.LIGHT, "#0B3D5C");

    public static final DocumentProperties DOCUMENT = new DocumentProperties("Example Ltd", "Example Reporting");

    public static Sheetsmith.Builder builder() {
        return Sheetsmith.builder().defaults(DEFAULTS).documentProperties(DOCUMENT);
    }

    private CorporateSheetsmith() {
    }
}

In Spring Boot applications, the same values go in a shared application.yml fragment or profile (see section 10.3).

Style sheets and presets together

Style sheets and presets combine: the preset gives the overall structure (lines, zebra) derived from the corporate accent, and the style sheet adds fonts, formats and the details the preset does not cover. Because the preset is the lowest level, the shared styles always win over it.

@ExcelSheet(preset = TablePreset.LIGHT, accentColor = "#0B3D5C",
        styleSheets = CorporateStyles.class,
        body = @BodyStyles(base = "corp-cell", lastRow = "corp-total"))