Charts, pivot tables and pictures

These are the parts of a template that refer to the rows a bound range generates, and they need almost nothing from the template author beyond drawing them on the data.

The rule throughout: draw it in the template over the single row the template has, and the engine points it at what was generated.

Charts

Draw the chart in the template with its series over the template's one repeated row. After generation the series cover every row that was generated. There is no tag and nothing to remember.

// In the template
var chart = sheet.Charts.Add(XLChartType.ColumnClustered);
chart.Series.Add("Quantity", "Sales!$B$4:$B$4", "Sales!$A$4:$A$4");

workbook.DefinedNames.Add("Sales", sheet.Range("A4:C5"));

Generate over twelve items and the series reads Sales!$B$4:$B$15 — value references and category references alike.

Why this needs code at all, when a formula or a picture does not: a chart series reference is a plain string in the model and a plain string in the file. Nothing shifts it when rows are inserted, so a template chart plotting one row would keep plotting that one row however many the report generated.

What the rewriter handles:

The template's series	After generation
On the repeated row	Stretched over every generated row
Covering the heading as well	Keeps its start, stretches its end
Below the range	Moved down by however much the range grew
Above the range	Left alone
On another sheet the report did not touch	Left alone
Over a range bound to an empty collection	Collapses with the range

A chart may sit on any sheet; what matters is the sheet its references name, not where the chart is drawn. A dashboard sheet plotting a data sheet's range follows that range.

note

Only single-area references are rewritten. A multi-area reference, a 3-D reference across sheets, or a whole-row or whole-column one is left exactly as the template had it — a wrong reference is worse for a report than a stale one.

Pictures

Nothing to do. A picture anchored below a bound range ends up below the generated rows, because its anchor is a live cell reference that the core library shifts along with everything else.

Pivot tables drawn in the template

This is the pattern to reach for. Build the pivot in Excel over the template's data rows and lay it out however the report wants — field order, subtotals, layout, number formats, styles. It is a real pivot table drawn by someone who could see it.

At generation the engine:

• re-points the cache at the rows that were actually generated,
• refreshes it, so the cached records are the report's data and not the template's,
• marks it to re-read on open, because Excel is the authority on its own pivot layout, and
• moves the pivot itself if it sat below the range and the generated rows would have grown over it.

A pivot sourced from an Excel table or a defined name needs no re-pointing — the table or name grew on its own — but still gets the refresh, which is the part that is easy to lose.

note

XLibur writes the pivot definition and the cache; Excel computes the body on open. You will not see aggregated values by reading the generated pivot sheet's cells back with XLibur. This is the same as pivot tables generally.

Pivot tables generated by the template

For a pivot whose shape belongs to the template rather than to whoever drew it — one template producing a pivot over whatever the data turns out to be — use <<Pivot>>.

Field tags go in the options row under the columns the pivot should use:

	A	B	C
3	Region	Category	Line total
4	{{ item.Region }}	{{ item.Category }}	{{ item.Total }}
5	<<Row>>	<<Column>>	<<Data title="Sold">><<Pivot dest="Summary!A3">>

dest

Required. It says where to put the pivot table, and it is read in template coordinates, like everything else a template writes: a cell named as being below the range comes out below the generated range, however many rows that turned out to be.

It takes any of:

dest="Summary!A3"     a sheet-qualified reference
dest="F1"             a plain reference, on the range's own sheet
dest="PivotCorner"    the name of a defined name covering exactly one cell

name="…" names the pivot table; without it the pivot is called Pivot1, Pivot2 and so on.

Field tags

Tag	Pivot area
<<Row>>	Row labels
<<Column>> / <<Col>>	Column labels
<<Page>>	Report filter
<<Data>>	Values

Each field is named by its column's heading — the row immediately above the range, which is where a pivot cache reads its field names from anyway, so a template does not write them twice.

<<Data>> sums by default. Name another summary as a bare flag or as a value:

<<Data avg>>            <<Data func=avg>>
<<Data count>>          <<Data func=min>>
<<Data title="Sold">>   label the value column something other than its field name

Recognised summaries: sum, count, counta, countnums, avg/average, min, max, product, stddev/stdev, stddevp/stdevp, var, varp. An unrecognised name sums.

When <<Pivot>> reports an error instead

• The range starts in row 1. There is no heading row above it to name the fields from, and inventing names would be worse than saying so.
• The range repeats across (<<Horizontal>>). Same reason: no heading row.
• The range is grouped (<<Group>>). The subtotal rows are part of the generated block, and the pivot would count them as data on top of the rows they already total. Pivot the range or group it, not both.
• A field tag with no <<Pivot>> in the same options row. It would have no effect, which is more likely a mistake than an intention.

Conditional formatting

A rule over the template's repeated rows is stretched over the generated block, not copied per row. A template that declares three rules produces three rules over however many rows it generated — not three rules per row.

// In the template, over the one repeated row
sheet.Range("C4:C4").AddConditionalFormat()
     .WhenGreaterThan(1000)
     .Fill.SetBackgroundColor(XLColor.LightGreen);

Generate over a thousand items and the workbook still holds exactly one rule, now covering C4:C1003.

Formulas

A template cell holding an ordinary Excel formula is copied into every generated row with its relative references re-pointed by the core library — =B4*1.2 becomes =B5*1.2, =B6*1.2 and so on. Absolute references ($B$1) stay put, exactly as they would if you copied the row down in Excel.

Defined names, merged cells, row heights and column widths are all carried the same way, because expansion inserts real sheet rows and copies the template block into them rather than rendering onto a scratch sheet.

Checking a generated workbook

The repository's EverythingAtOnce example builds one workbook covering every feature above and prints a checklist to work down in Excel. It exists because no automated test can answer the two questions that matter about a generated file: whether Excel opens it without offering to repair it, and whether what it then draws is what was meant.