Skip to content
sheetsmith

Styling

This section collects everything about formatting: colours, the meaning of each group of attributes, the data format syntax and the cascade in practice. The complete lists of enum constants are in Appendix B.

Defining styles

Section titled “Defining styles”

A named style is declared with @ExcelStyle on the sheet class or on a style sheet, and applied through slots. The full attribute list is in section 4.3. A useful way to organise styles:

  • one style per purpose (header, zebra, money, total, key), not per cell;
  • combine purposes through the cascade instead of creating one style for every combination: a total style that only sets bold and a top border works on top of money, zebra or a preset.

Colours

Section titled “Colours”

Every colour attribute of sheetsmith (the colours of @ExcelStyle, @ExcelSheet.accentColor, @ExcelSheet.outerBorderColor, the application default accent colour) accepts:

Form Example Notes
hexadecimal #RRGGBB #1F4E79, #1f4e79 Upper or lower case. Normalised to upper case, so #1f4e79 and #1F4E79 are the same colour and share one cell style. Exactly six hexadecimal digits: #FFF and 1F4E79 are invalid.
IndexedColors constant name DARK_BLUE, GREY_25_PERCENT The name of a constant of Apache POI org.apache.poi.ss.usermodel.IndexedColors, case-sensitive, in upper case as declared. Not normalised. The full list is in Appendix B.9.
empty string "" Unset.

Any other value violates V-13. Prefer hexadecimal colours: they are exact, while indexed colours depend on the palette of the application that opens the file. AUTOMATIC is accepted; as an accent colour it is treated as black.

Alignment and text control

Section titled “Alignment and text control”
Attribute Notes
align GENERAL is the Excel default: text to the left, numbers and dates to the right. CENTER_SELECTION centres across adjacent cells with the same alignment without merging them. FILL repeats the content to fill the cell.
verticalAlign Excel default is BOTTOM. JUSTIFY and DISTRIBUTED affect wrapped text.
wrapText Wraps long text on several lines. sheetsmith does not set row heights: depending on the spreadsheet application, wrapped rows may be shown at the default height until the reader applies an automatic row height.
shrinkToFit Reduces the font size so the text fits. Ignored by Excel when wrapText is active.
rotation -90 to 90 degrees; positive values rotate counter-clockwise. 255 stacks the characters vertically.
indent Indentation level, 0 to 250, effective with left, right or distributed alignment.

Borders

Section titled “Borders”
  • Each side has a line (Border) and a colour.
  • border and borderColor set the four sides at once; the side-specific attributes win over them within the same style.
  • Across the cascade, each side is merged independently: a level that sets only borderBottom keeps the other three sides of the lower levels.
  • Border.NONE removes a line set at a lower level; Border.INHERIT keeps it.
  • A side with a line and no colour uses the automatic colour, usually black.
  • Borders of adjacent cells are stored independently: a bottom border on one row and a top border on the next row are two separate settings of two cells.
  • The outer frame (section 4.1.9) is a dedicated level that sets only the edges of the table.

Fills and fonts

Section titled “Fills and fonts”
  • Solid fill: set fillColor only. The pattern becomes SOLID_FOREGROUND automatically.
  • Patterned fill: set fillPattern, fillColor (colour of the pattern) and optionally fillBackgroundColor (colour behind the pattern).
  • No fill: fillPattern = Fill.NO_FILL removes a fill set at a lower level, for example a preset zebra on one column.
  • Fonts: fontName, fontSize, bold, italic, strikeout, underline, fontColor and script are merged attribute by attribute like the rest of the style. The font name must be available on the machine that opens the file, otherwise Excel substitutes it. Fonts are deduplicated in the file.

Data formats

Section titled “Data formats”

Every format of sheetsmith (@ExcelColumn.format, @ExcelStyle.dataFormat, the default formats of SheetsmithDefaults and the sheetsmith.formats.* properties) uses the Excel format syntax, the one of the Excel “Format Cells” dialog. It is not the syntax of java.time.format.DateTimeFormatter or java.text.DecimalFormat: the two look similar but are not the same. Formats are written to the file as they are, without translation and without validation.

Date and time codes

Meaning Excel DateTimeFormatter
Day of the month: 5, 05 d, dd d, dd
Day name: Mon, Monday ddd, dddd EEE, EEEE
Month: 3, 03 m, mm M, MM
Month name: Mar, March mmm, mmmm MMM, MMMM
Year: 26, 2026 yy, yyyy yy, yyyy
Hours, 0 to 23 h, hh H, HH
Hours, 1 to 12 with AM/PM h AM/PM, hh AM/PM h a, hh a
Minutes m, mm after an hour code or before a seconds code m, mm
Seconds s, ss s, ss
Elapsed hours beyond 24 [h] no equivalent

In Excel, m and mm mean the month, unless they follow an hour code or precede a seconds code, in which case they mean minutes. So dd/mm/yyyy hh:mm shows the day, the month, the year, the hours and the minutes. The Java pattern dd/MM/yyyy is written in Excel as dd/mm/yyyy, and HH:mm as hh:mm.

Number formats

Format Example output
0 1234
0.00 1234.50
#,##0 1,235
#,##0.00 1,234.50
0.0% 12.5% (for the value 0.125)
#,##0.00 "EUR" 1,234.50 EUR
#,##0.00;[Red]-#,##0.00 negative values in red
0.00E+00 1.23E+03
00000 00042 (leading zeros on numbers)
@ the value as text

In number formats, 0 is a digit always shown, # a digit shown only when significant, , the thousands separator, . the decimal separator, and text in double quotes is shown as it is. A format can have up to four sections separated by ;: positive, negative, zero, text. The separators actually displayed follow the regional settings of the person who opens the file: #,##0.00 is displayed as 1.234,50 on an Italian system.

In Java source code, double quotes inside a format must be escaped: format = "#,##0.00 \"EUR\"".

The cascade in practice

Section titled “The cascade in practice”

Consider this sheet class:

@ExcelSheet(
        preset = TablePreset.LIGHT,
        body = @BodyStyles(base = "cell", firstColumn = "key", lastRow = "total"))
@ExcelStyle(name = "cell", fontName = "Arial")
@ExcelStyle(name = "key", bold = Toggle.TRUE, fontColor = "#1F4E79")
@ExcelStyle(name = "total", bold = Toggle.TRUE, fontColor = "#000000", borderTop = Border.DOUBLE)
@ExcelStyle(name = "money", align = Align.RIGHT, dataFormat = "#,##0.00")
public record Row(
        @ExcelColumn(header = "Item", order = 10) String item,
        @ExcelColumn(header = "Amount", order = 20, styles = @ColumnStyles(base = "money"), format = "#,##0")
        BigDecimal amount) { }

With three data rows and the default accent #4472C4, the effective style of a few cells:

Cell Levels applied (low to high) Result
Item, row 1 (odd, first) preset base (bottom line #D0DCF0), preset odd (fill #E3EAF6), cell, key Arial, bold, #1F4E79 text, light fill, light bottom line
Amount, row 2 (even) preset base, cell, money, column format Arial, right aligned, format #,##0 (the column format beats money), light bottom line, no fill
Item, row 3 (odd, last) preset base, preset odd, cell, key, total Arial, bold, black text (total beats key: the row wins over the column), light fill, double top border, light bottom line
Amount, row 3 preset base, preset odd, cell, total, money, column format Arial, bold, black, right aligned, #,##0, double top border, light fill