Format as Table

Sheet.AddTable produces what Excel’s ribbon calls “Format as Table”: a named range that carries its own style, keeps its alternating row colors as rows are sorted or filtered, and gives each column a name that formulas can refer to. It is a real object in the file rather than formatting applied cell by cell, which is why the banding survives operations that would scramble manual fills.

Banding by table or by cell style

If the sheet is a static report nobody will re-sort, cell styles are fine and give you complete control. If it is data someone will interact with, use a table.

Creating one

tbl := sheet.AddTable("A1:C7", "SalesTable")
tbl.SetStyle(spreadsheet.TableStyleMedium9)
tbl.SetShowFirstColumn(true)

The range must cover the header row plus at least one data row. Column names come from the cells in the first row of that range; an empty header cell becomes Column1, Column2 and so on, and duplicate headers get a numeric suffix so they stay unique.

A new table arrives with an auto-filter on its header row and TableStyleMedium2 with row stripes already enabled, so the two calls above are adjustments rather than requirements. The style constants come in three families, TableStyleLight1 through 21, TableStyleMedium1 through 28 and TableStyleDark1 through 11, matching the gallery Excel shows. SetStyle also accepts an arbitrary string, which is how you apply a custom style defined in the workbook’s own tableStyles part.

Tables belong to the workbook rather than the sheet once created. Workbook.Tables() lists them all and Workbook.Table(name) looks one up by name, which is how you get back to a table read from an existing file.

Totals row

A totals row is part of the table reference, not an extra row outside it. Extend the reference by one row, turn the totals row on, then say what each column should show:

tbl.SetReference("A1:C8")
tbl.SetTotalsRow(true)
if firstCol, ok := tbl.Column(0); ok {
    firstCol.SetTotalsRowLabel("Total")
}
if salesCol, ok := tbl.Column(2); ok {
    salesCol.SetTotalsRowFunction(sml.ST_TotalsRowFunctionSum)
}

SetReference and SetTotalsRow both re-derive the auto-filter range, shrinking it to exclude the totals row, so the order of those two calls does not matter. Column is 0-based from the left edge of the table and returns false when the index is out of range.

SetTotalsRowLabel and SetTotalsRowFunction are mutually exclusive on a single column, as they are in Excel. A label is plain text; a function is one of the sml.ST_TotalsRowFunction values (Sum, Average, Count, Min, Max and the rest).

Declaring the totals row does not put values in it. The declaration tells Excel how to treat that row; the cells still have to exist. The example writes them itself with a SUBTOTAL(109, ...) formula, function 109 being the sum that ignores rows hidden by a filter, which is what makes the total track the filter rather than the whole column.

Limitations

AddTable does not return an error. When the name is empty, starts with a digit or contains a space, when the name collides with an existing table anywhere in the workbook, when the range does not parse, or when it spans fewer than two rows, it logs at debug level and returns a zero-value Table. Calling SetStyle on that zero value dereferences a nil pointer, so a silently rejected table shows up as a panic several lines later rather than at the call that caused it. Check your name and range if that happens.

The name collision check is case-insensitive, deliberately: Excel treats table display names that way, and a workbook containing both MyTable and mytable fails to open.

Column names are read once, when the table is created. Rewriting a header cell afterwards changes what the sheet displays but not what the table calls that column.

Tables are not removable through the API. Table part indexes are assigned on the assumption that nothing is ever removed.

Run the example

The example builds a six-row regional sales sheet, wraps it in a table styled TableStyleMedium9, and adds a totals row summing the sales column. Every range in it is derived from the length of the data slice, which is the pattern to copy: the header row, the data rows and the totals row all have to agree, and computing them from one source keeps them agreeing when the data changes.

git clone https://github.com/unidoc/unioffice-examples.git
cd unioffice-examples/spreadsheet/format-as-table
go run main.go

If this is your first time using UniOffice, follow the getting started guide to create an API key and set up your development environment.