Styling recipes

Totals row

sheetsmith does not compute totals or write formulas: the total is a data row like the others, usually the last element of the list, styled with the lastRow slot.

@ExcelSheet(body = @BodyStyles(lastRow = "total"))
@ExcelStyle(name = "total", bold = Toggle.TRUE, borderTop = Border.DOUBLE)
public record SalesRow(
        @ExcelColumn(header = "Region", order = 10) String region,
        @ExcelColumn(header = "Revenue", order = 20, format = "#,##0.00") BigDecimal revenue) {
}

List<SalesRow> rows = new ArrayList<>(regions);
rows.add(new SalesRow("Total", regions.stream().map(SalesRow::revenue).reduce(BigDecimal.ZERO, BigDecimal::add)));

With a preset, the total row also receives the zebra of its parity; add fillPattern = Fill.NO_FILL or a fillColor to total to control it. When the auto-filter is enabled, remember that the total row is part of the filtered range.

Zebra rows without a preset

@ExcelSheet(header = @HeaderStyles(base = "header"), body = @BodyStyles(even = "zebra"))
@ExcelStyle(name = "header", bold = Toggle.TRUE, borderBottom = Border.THIN)
@ExcelStyle(name = "zebra", fillColor = "#F2F2F2")
public record LogRow(
        @ExcelColumn(header = "When", order = 10, format = "dd/mm/yyyy hh:mm:ss") LocalDateTime when,
        @ExcelColumn(header = "Message", order = 20, width = 80) String message) {
}

Highlighting a key column and right-aligning numbers

@ExcelSheet(preset = TablePreset.MEDIUM, body = @BodyStyles(firstColumn = "key"))
@ExcelStyle(name = "key", bold = Toggle.TRUE)
@ExcelStyle(name = "right", align = Align.RIGHT)
public record StockRow(
        @ExcelColumn(header = "Item", order = 10) String item,
        @ExcelColumn(header = "On hand", order = 20, format = "#,##0", headerStyle = "right") int onHand,
        @ExcelColumn(header = "Reserved", order = 30, format = "#,##0", headerStyle = "right") int reserved) {
}

Numbers are right-aligned by Excel by default (GENERAL alignment); the right style aligns the header texts above them.

Framed table

@ExcelSheet(title = "Attendance", outerBorder = Border.MEDIUM, outerBorderColor = "#404040",
        body = @BodyStyles(base = "grid"))
@ExcelStyle(name = "grid", border = Border.HAIR, borderColor = "#BFBFBF")
public record AttendanceRow(
        @ExcelColumn(header = "Name", order = 10) String name,
        @ExcelColumn(header = "Present", order = 20) boolean present) {
}

The frame is drawn on the outer edges of header and data, over the hairline grid; the title stays outside.

Corporate report with a shared 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, wrapText = Toggle.TRUE,
        verticalAlign = VerticalAlign.CENTER)
@ExcelStyle(name = "corp-cell", fontName = "Arial", fontSize = 10)
@ExcelStyle(name = "corp-money", align = Align.RIGHT, dataFormat = "#,##0.00")
@ExcelStyle(name = "corp-total", bold = Toggle.TRUE, borderTop = Border.DOUBLE, borderTopColor = "#0B3D5C")
public final class CorporateStyles {
    private CorporateStyles() {
    }
}

@ExcelSheet(title = "Supplier balances", titleStyle = "corp-title",
        preset = TablePreset.LIGHT, accentColor = "#0B3D5C",
        styleSheets = CorporateStyles.class,
        header = @HeaderStyles(base = "corp-header"),
        body = @BodyStyles(base = "corp-cell", lastRow = "corp-total"),
        autoFilter = true)
public record SupplierBalanceRow(
        @ExcelColumn(header = "Supplier", order = 10, width = 40) String supplier,
        @ExcelColumn(header = "Balance", order = 20, styles = @ColumnStyles(base = "corp-money")) BigDecimal balance) {
}

Every report referencing CorporateStyles gets the same fonts and formats; the LIGHT preset with the corporate accent provides lines and zebra.