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 set
DocumentAssemblyOptions.UseSpreadsheetDataTypes so numbers, dates and booleans keep their native
cell types. No Microsoft Excel is involved, and the template stays a workbook the report owner
edits themselves.
Finance asks for the workbook, not a PDF of it, and that is a different job: the result has to stay
a spreadsheet — numbers in numeric cells, totals that recalculate, filters that still work.
By default the engine writes every value into a cell as a string. The report looks correct and is
useless for anything a spreadsheet is for: SUM over the column returns 0, sorting is
alphabetical, and a pivot table refuses the field.
DocumentAssemblyOptions.UseSpreadsheetDataTypes writes numbers, dates and booleans with their
native cell types instead. The difference is visible in the file itself — a text cell carries
t="s", a numeric one carries the value:
and visible on screen as alignment, because Excel puts real numbers against the right edge of the
cell and leaves text against the left.
Totals: a template expression, not a worksheet formula
A SUM() placed under the repeating row does not survive assembly. The row it refers to is
replaced while the data expands, and the result is SUM(#REF!).
Compute the total in the template instead, over the same collection the rows came from:
<<[ds.Sum(c => c.Price)]>>
With UseSpreadsheetDataTypes on, the total lands as a number as well, so formulas written on top
of it afterwards work. Formulas that do not point at the repeating range are untouched and
can be recalculated
during assembly.
Dropping the rows a condition left empty
Note
RemoveEmptyParagraphs covers spreadsheet and plain text templates since version 26.11.
In a spreadsheet a paragraph is a line of text, so a <<if>> that renders nothing no longer leaves
its blank rows behind. A report with several optional blocks used to end up full of gaps that
somebody deleted by hand every month. See
Removing empty paragraphs.
What else belongs in a workbook template
Several tables on one sheet, each with its own data source: pass more than one
DataSourceInfo to AssembleDocument, with a name each.
Charts whose series come from the data, including
series colours
taken from a field.
Grouping and aggregates inside the expressions: GroupBy, Sum, Count, Average. The
summary row is part of the template, not of your code.
PDF from the same call, for the copy that gets attached to an e-mail: add
new LoadSaveOptions(FileFormat.Pdf).
How do you generate an Excel report from a template in C#?
Put <<foreach [c in ds]>> … <</foreach>> tags in the cells of an .xlsx, then call
DocumentAssembler.AssembleDocument with the template, the output path and a DataSourceInfo.
Set DocumentAssemblyOptions.UseSpreadsheetDataTypes so the values keep their types.
Why are the numbers text in the generated workbook?UseSpreadsheetDataTypes is off by default. Without it every value is written as a string, which
is why SUM returns 0 and sorting is alphabetical.
Why does a SUM formula turn into SUM(#REF!)?
It referenced the repeating row, which is replaced during assembly. Use a template expression such
as <<[ds.Sum(c => c.Price)]>> for aggregates over the repeated data.
Can the same template produce a PDF?
Yes — add new LoadSaveOptions(FileFormat.Pdf) to the same AssembleDocument call.
Is Microsoft Excel required on the server?
No. GroupDocs.Assembly reads and writes the formats itself, on Windows and on Linux.