Page History

...

1. Use ExcelApplication to create a worksheet and enter data marker strings in the worksheet's cells:

   Code Block
   c# c#
   ExcelApplication xla = new ExcelApplication(); Workbook wb = xla.Create(); Worksheet ws = wb.Worksheets[0]; for (int i = 0; i < colCount; i++) { //--- Form the current data marker. //--- Data markers are in this format: //--- %%=[dsrcName].[colName] string curMarker = String.Format("%%={0}.{1}", dsrcName, selectedCols[i].ToString()); ws[firstRow, firstCol + i].Value = curMarker; }

   A data marker is a cell value beginning with %%= or %%=$ that specifies a variable, an array, or a database column, to insert in the spreadsheet column containing the marker. The data marker may be followed by a modifier. For example, to include column headers in the populated data marker column, the data marker should include the (fieldname) modifier:

   Code Block
   %%=DataSource.EmployeeID(fieldname)

   For more information, see Creating Data Markers.
   #
   
2. To open the workbook you created as an ExcelTemplate, pass your ExcelApplication and Workbook objects to ExcelTemplate's Open method:

   Code Block
   c# c#
   ExcelTemplate xlt = new ExcelTemplate(); xlt.Open(xla, wb);

3. Connect to a database and execute a query to return a DataTable, DataView, or DataReader to use a data source for template data markers, for example:

   Code Block
   c# c#
   private DataTable GetEmployeeDataTable() { string EmployeeSQL = "SELECT TOP 10 FirstName + ' ' + LastName As Name, " + "Title FROM Employee"; DataTable dt = new DataTable(); using(SqlConnection conn = new SqlConnection(connString)) new SqlDataAdapter(EmployeeSQL, conn).Fill(dt); return dt; }

   Alternatively, the data source for the template may be a rectangular array.
   #
   
4. Set the returned DataTable, DataView, or DataReader as the data source for template data markers, and call Process to populate the data markers with data source values:

   Code Block
   c# c#
   xlt.SetDataSource(dt, dsrcName); xlt.Process();

5. Call Save to save the template on the server, return it in memory, or - as in the following example - stream it to the browser:

   Code Block
   c# c#
   xlt.Save(Page.Response, "Report.xls", false);

...