Quick Start > Create a Spreadsheet with ExcelTemplate

Create a Spreadsheet with ExcelTemplate

ExcelWriter's ExcelTemplate object opens an ExcelWriter template file, populates it with data from a specified data source, and generates a new Excel spreadsheet. An ExcelWriter template is a spreadsheet created in Microsoft Excel that contains 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 containing the marker. %%= specifies a database column, a 1-dimensional array that is a data source for a row, or a 2-dimensional array. %%=$ specifies a variable or a 1-dimensional array that is a data source for a single column.

A data marker may include both a data source name and a column name, for example, %%=Products.ProductId where "Products" is a database table, and "ProductId" is a column in the table. When the data source is a simple variable or a one-dimensional array, the data marker should begin with %%=$. For example, %%=$RecipientName where the data source for "RecipientName" is a variable that will be assigned in script.

The term "template" refers to an ExcelWriter template, not a Microsoft Excel template (.xlt file). However, ExcelWriter can open and generate both .xls and .xlt files. Use an .xlt file the same way you would an .xls file.

In the following example, ExcelTemplate opens a template spreadsheet, populates the template's data markers, and generates a new spreadsheet. The template contains two data markers: %%=$RecipientName and %%=$RecipientCompany. %%=$ (rather than %%=) indicates that the data source is a variable or a single column array. ExcelWriter sets the data sources for the data markers to two simple string variables.

Step 1: Create a Template

To generate an Excel spreadsheet with ExcelWriter, first create a template:

1. In Microsoft Excel, create a new file.
   
   
2. In one or more cells, enter data markers. For example, in cell B11 enter the variable data marker %%=$RecipientName and in cell B15 enter the variable data marker %%=$RecipientCompany.
   
   
3. Enter any static cell values in the spreadsheet. Static values and any Excel features included in the template (such as formatting, formulas, charts, and macros) will be included in the generated spreadsheet.

Step 2: Write the ExcelWriter Code

Next, create an ASP.NET script that uses ExcelTemplate to open the template, populate data markers, and generate a new Excel spreadsheet.

ExcelTemplate is in the SoftArtisans.OfficeWriter.ExcelWriter namespace. The object can be referenced as SoftArtisans.OfficeWriter.ExcelWriter.ExcelTemplate. To minimize typing and errors, use an Import directive to import the namespace to the aspx page, and reference the object as ExcelTemplate, without the namespace prefix. If you are coding directly in the .aspx page, following the Page directive, include:

	<%@ Import Namespace="SoftArtisans.OfficeWriter.ExcelWriter" %>

If you are coding in the code-behind page (as in the example above), include a using (C#) or Imports (VB.NET) statement at the top of the code-behind page:

	// [C#]
	using SoftArtisans.OfficeWriter.ExcelWriter;

	' [VB.NET]
	Imports SoftArtisans.OfficeWriter.ExcelWriter

To generate a new spreadsheet with ExcelWriter:

1. Create an instance of the ExcelTemplate object which represents the template Excel file, for example:

   	// [C#]
   	ExcelTemplate xlt = new ExcelTemplate();

   	' [VB.NET]
   	Dim xlt As New ExcelTemplate()

2. Call ExcelTemplate.Open to open a template Excel file, for example:

   	// [C#]
   	xlt.Open(@"C:\Templates\Template.xls");

   	' [VB.NET]
   	xlt.Open("C:\Templates\Template.xls");

   The Open method takes the file path of the template .xls file to open.

3. Use the ExcelTemplate.SetCellDataSource method to bind data sources to the template's data markers, for example:

   	// [C#]
   	// Bind the variables to the data markers
   	// %%=$RecipientName and %%=$RecipientCompany
   	xlt.SetCellDataSource("J. Smith", "RecipientName");
   	xlt.SetCellDataSource("SoftArtisans", "RecipientCompany");

   	' [VB.NET]
   	' Bind the variables to the data markers
   	' %%=$RecipientName and %%=$RecipientCompany
   	xlt.SetCellDataSource("J. Smith", "RecipientName")	
   	xlt.SetCellDataSource("SoftArtisans", "RecipientCompany")

4. Call ExcelTemplate.Process to populate the template's data markers with data source values:

   	// [C#]
   	xlt.Process();

   	' [VB.NET]
   	xlt.Process()

   The Process method enters data source values in the template's merge fields, and creates the output file (the new spreadsheet) in memory.

5. Call ExcelTemplate.Save to generate a new spreadsheet:

   	[C#]
   	xlt.Save(Page.Response, "Output.xls", false);

   	' [VB.NET]
   	xl.Save(Page.Response, "Output.xls", False)

   If you pass Save an HTTP Response object, ExcelWriter will stream the generated file to the client. Save's second parameter specifies a name for the generated Excel file; this name will be displayed in the download dialog when the file is streamed to the browser. If the third parameter is set to true, and the user chooses to open the file, the file will open in the browser window; if it is set to false (as in the example), and the user chooses to open the file, the file will open in Microsoft Excel.

   ExcelWriter allows you to save the generated file on the server or stream it to the client. For more information, see Output Options.

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.