To generate an Excel report from a template in C#, put <<foreach>> tags in the cells of an .xlsx, fill it with DocumentAssembler.AssembleDocument, and switch on DocumentAssemblyOptions.UseSpreadsheetDataTypes so the numbers arrive as numbers. That option is the part everybody misses, and this post is mostly about why.

Because “can we get that as Excel?” almost never means “as a table”. It means somebody is going to sort it, filter it, add a column with a formula and send it on. A report that renders numbers as text looks identical in a screenshot and is useless the moment they try.

So the job is not producing a .xlsx. It is producing one that is still a workbook. The sample is on GitHub — excel-reports-from-template-net.

How do you make a row repeat in an Excel template?

The template is an ordinary workbook with tags in cells. A <<foreach>> that opens in one cell of a row and closes in another cell of the same row makes the whole row repeat, pushing everything below it down:

Client Manager Contract Price
<<foreach [c in ds]>><<[c.Client]>> <<[c.Manager]>> <<[c.Price]>><</foreach>>
Total <<[ds.Sum(c => c.Price)]>>
[
  { "Client": "Quayside Bistro",   "Manager": "A. Petrova",   "Price": 18400 },
  { "Client": "Northwind Traders", "Manager": "J. Okonkwo",   "Price": 42100 },
  { "Client": "Fabrikam Catering", "Manager": "M. Lindqvist", "Price": 7300 }
]
DocumentAssembler assembler = new DocumentAssembler
{
    Options = DocumentAssemblyOptions.UseSpreadsheetDataTypes
};

assembler.AssembleDocument(
    "templates/Data table.xlsx",
    "output/contracts.xlsx",
    new DataSourceInfo(new JsonDataSource("data/contracts.json"), "ds"));

Why are my numbers text in the generated workbook?

Because UseSpreadsheetDataTypes is off by default, and without it the engine writes every value into the cell as a string.

That is the bug nobody files. The report is produced, it opens, it looks right. Then somebody selects the price column and the status bar says Sum: 0, the filter offers them alphabetical order, and the pivot table refuses the field.

You can see it in the file itself. A text cell carries t="s" and an index into the shared string table; a numeric cell carries the number:

<!-- default -->                    <c r="C2" t="s"><v>6</v></c>
<!-- UseSpreadsheetDataTypes -->    <c r="C2"><v>18400</v></c>

And you can see it without opening the file at all: Excel pushes real numbers against the right edge of the cell and leaves text against the left. If the column in your report is left-aligned, you already know.

Why does my SUM formula become SUM(#REF!)?

Because it pointed at the repeating row. Put =SUM(C2:C2) under the tagged row and the assembled workbook comes back with SUM(#REF!) — the row the formula referred to is replaced while the data expands, and the reference goes with it.

Write the aggregate as a template expression instead, over the same collection the rows came from:

<<[ds.Sum(c => c.Price)]>>

With UseSpreadsheetDataTypes on it lands as a number — 92800 for the data above — so a formula somebody adds on top of it later works normally. Formulas that never pointed at the repeating range are untouched.

How do I drop the blank rows a condition left behind?

DocumentAssembler assembler = new DocumentAssembler
{
    Options = DocumentAssemblyOptions.RemoveEmptyParagraphs
};

New in 26.11 — RemoveEmptyParagraphs now covers spreadsheet and plain text templates.

In a spreadsheet a paragraph is a line of text, so a <<if>> that renders nothing no longer leaves its blank rows behind. If your monthly report has four optional sections and somebody deletes the gaps by hand every month, this is the option they have been waiting for.

What else belongs in a workbook template

  • Several tables on one sheet, each with its own data source: pass more than one DataSourceInfo, with a name each.
  • Charts whose series come from the data, including series colours taken from a field — and since 26.11 those may be hex strings such as #FFE699.
  • Grouping and aggregates inside the expressions: GroupBy, Sum, Count, Average. The summary row is part of the template, so the person who owns the report changes it without you.
  • PDF from the same call, for the copy that goes in the e-mail: add new LoadSaveOptions(FileFormat.Pdf).

ODS, XLS and XLT templates work the same way as XLSX.

Questions people ask

Do I need Microsoft Excel on the server? No. GroupDocs.Assembly reads and writes the spreadsheet formats itself, on Windows and on Linux.

Can the data be a .NET object instead of JSON? Yes — any object, DataTable, DataSet or IEnumerable<T> works as the data source, with the same tags in the template.

Does this run in a container? Yes, with fontconfig and a font package installed in the image, or the layout is decided by whatever substitute the container can find. See Generating documents in Docker.

What happens when a field is missing from the data? By default it is an error. A JSON Schema, AllowMissingMembers and InlineErrorMessages each handle it differently — see Templates that survive incomplete data.

Try it

git clone https://github.com/groupdocs-assembly/excel-reports-from-template-net.git
cd excel-reports-from-template-net
dotnet run --project src/ExcelReports -- all

The reference page is Generate Excel reports from a template. The same engine and the same data fill Word documents and PowerPoint decks.