Page tree
Skip to end of metadata
Go to start of metadata

How to Use Templates

An ExcelWriter template is an Excel spreadsheet that contains ExcelWriter data markers. A data marker is a cell value beginning with %%= or %%=$ that specifies a database column, variable, or array to insert in the spreadsheet column. A data marker may include modifiers.

Creating Data Markers

A data marker is a cell value or sheet tab name beginning with %%= or %%=$ that specifies a database column, a variable, or an array to insert in the spreadsheet column or sheet tab containing the marker. Data markers are created in Microsoft Excel and bound to data sources in code. When you run the code, ExcelWriter populates the data markers with values from a data source.

Stretching Formulas

A data marker is a single cell value, but a data marker will usually be populated with several rows of cell values. For example, if you set the data source for the data marker %%=Products.ProductID, to the "ProductID" column in a database table, the marker cell and the cells below it will be populated with ProductID values. New rows will be inserted between the data marker row and the next row in the template that contains a value. This can affect formulas because the range of cell values a form

Using Charts with ExcelTemplate

When you include a chart in a template spreadsheet, you can use a data marker column as a data source for the chart. Excel will automatically adjust charts to include the number of rows that ExcelWriter assigns to the data marker column.

Drawing Objects and Data Markers

If you include drawing objects (charts, comments, etc.) in a template, ExcelWriter will preserve the objects in the generated workbook. However, depending on where the object is in the template, ExcelWriter may move or modify it.

Using an Array as a Data Source

The following sample generates an Excel spreadsheet from a template using arrays as data sources.

Using a Database as a Data Source

The sample above generates a spreadsheet from the template using ADO.NET objects as the data source.

Using Grouping and Nesting with ExcelTemplate

Starting in version 7.1 of ExcelWriter, you can use [grouping and nesting markers|Creating Data Markers#grouping] to display flat data in a grouped and nested format using the ExcelTemplate object.

Templates and PivotTables

A PivotTable report is an interactive table which allows the user to have multiple views of data. By changing the view, the PivotTable report can show different summaries of the data or drill down to display more details for particular areas. ExcelWriter allows you to include PivotTables in an ExcelWriter template file. If there are PivotTables in the original template file, they will be preserved when the template is processed using ExcelWriter.

ExcelTemplate Output Options

ExcelTemplate has several different options for saving an ExcelTemplate generated file.

  • No labels