Summary Rows
Summary rows sit below a report's data: totals, averages, counts, or any value you compute. Each column declares what it puts in each summary row.
import { createExcelSchema } from "typed-xlsx";
type Sale = { region: string; units: number; revenue: number };
const schema = createExcelSchema<Sale>()
.column("region", {
header: "Region",
accessor: "region",
summary: (s) => [s.label("Total"), s.label("Best revenue")],
})
.column("units", {
header: "Units",
accessor: "units",
summary: (s) => [s.formula("sum"), s.empty()],
})
.column("revenue", {
header: "Revenue",
accessor: "revenue",
style: { numFmt: '"$"#,##0' },
summary: (s) => [
s.formula("sum"),
s.cell({
init: () => 0,
step: (max, row) => Math.max(max, row.revenue),
finalize: (max) => max,
}),
],
})
.build();
- 1
B5:C5summary.formula("sum"): a live formula, recalculated if a value above changes - 2
C6summary.cell(…): computed in JavaScript while rows are written, stored as a value
The array a column returns lines up by position: the first item goes in the first summary row, the second in the second, and so on. Summary rows exist in report mode only; an Excel table has a single totals row instead.
Formula or value
summary.formula() | summary.cell() | |
|---|---|---|
| Writes | an Excel formula over the column | a value you compute with a reducer |
| Updates when edited | yes, Excel recalculates it | no, it's a fixed value |
| Good for | sums, averages, counts, min and max | logic across several fields, or anything Excel can't express simply |
| Streaming | yes | yes, the reducer runs across every committed batch |
Formula summaries
Pass one of the shorthands, "sum", "average", "count", "min" or "max", or a callback for anything more:
import { createExcelSchema } from "typed-xlsx";
const schema = createExcelSchema<{ amount: number }>()
.column("amount", {
accessor: "amount",
summary: (s) => [
s.formula("sum"),
s.formula(({ column, fx }) => fx.round(column.cells().average(), 2)),
],
})
.build();
The callback receives column and the same fx functions as formula columns:
| Call | Writes |
|---|---|
column.cells().sum() | =SUM(C2:C1001) over the column's data range |
.average(), .count(), .min(), .max() | the matching range function |
fx.round(column.cells().sum(), 2) | =ROUND(SUM(C2:C1001),2) |
column.rows().average((row) => row.cells().sum()) | one value per logical row, then the average |
The range always covers the table's actual data rows. column.rows() matters when rows expand into sub-rows; Row Model shows how it differs from column.cells() on the same data.
Value summaries
summary.cell() is a reducer: init creates the accumulator, step runs once per row, finalize turns the result into the cell value. Use it when the summary needs several fields of the row:
import { createExcelSchema } from "typed-xlsx";
type Line = { gross: number; discount: number };
const schema = createExcelSchema<Line>()
.column("gross", {
header: "Gross",
accessor: "gross",
summary: (s) => [
s.cell({
init: () => ({ gross: 0, discount: 0 }),
step: (acc, row) => ({
gross: acc.gross + row.gross,
discount: acc.discount + row.discount,
}),
finalize: (acc) => acc.gross - acc.discount,
style: { numFmt: '"$"#,##0.00', font: { bold: true } },
}),
],
})
.build();
The reducer never sees all rows at once, which is why it also works when streaming.
Labels, blanks and spacers
summary.label(text) writes text, typically in the first column. For a column with nothing to show:
| Helper | Cell | Summary styling | Use it to |
|---|---|---|---|
summary.empty() | empty | yes, like the rest of the row | keep the row's look across a column with no value |
summary.spacer() | empty | no | leave a deliberate gap in the row |
Labels can be computed from the schema context, for example summary.label(({ ctx }) => ctx.t("total")).
Styling
Summary cells use the theme's summary slot by default. Each definition can also take:
style: aCellStyle; forsummary.cell()it can be a function of the finalized valueconditionalStyle: a native Excel conditional format, re-evaluated as the value changes
import { createExcelSchema } from "typed-xlsx";
const schema = createExcelSchema<{ amount: number }>()
.column("amount", {
accessor: "amount",
summary: (summary) => [
summary.formula("sum", {
style: { numFmt: '"$"#,##0.00', font: { bold: true } },
conditionalStyle: (conditional) =>
conditional.when(({ cell }) => cell.current().lt(0), {
fill: { color: { rgb: "FEE2E2" } },
font: { color: { rgb: "991B1B" } },
}),
}),
],
})
.build();
See Cell Styles and Conditional Styles.
When summary rows appear
A summary row is written when the schema is in report mode and at least one selected column defines a summary. Columns left out with select don't contribute.