Passing ExcelApplication to ExcelTemplate

ExcelWriter allows you to generate a spreadsheet from script alone - using the ExcelApplication object - or from a template spreadsheet and a script, using ExcelTemplate. You can also use ExcelApplication and ExcelTemplate together. This section shows you how to generate a template spreadsheet with ExcelApplication and pass the spreadsheet to ExcelTemplate which then populates the template's data markers.

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

   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:

   %%=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:

   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:

   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:

   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:

   xlt.Save(Page.Response, "Report.xls", false);

Example

using SoftArtisans.OfficeWriter.ExcelWriter;

class AppToTemplate : System.Web.UI.Page
{
  protected void Page_Load(object sender, System.EventArgs e)
  {
    // Create ExcelApplication, a Workbook, and a Worksheet
    ExcelApplication xla = new ExcelApplication();
    Workbook wb = xla.Create();
    Worksheet ws = wb.Worksheets[0];
    
    // Write ExcelTemplate data markers into the workbook
    ws.Cells[0,0].Value = "%%=DSrcName.#1(fieldname)";
    ws.Cells[0,0].Style.Font.Bold = true;
    ws.Cells[1,0].Value = "%%=DSrcName.#1";

    ws.Cells[0,1].Value = "%%=DSrcName.#2(fieldname)";
    ws.Cells[0,1].Style.Font.Bold = true;
    ws.Cells[1,1].Value = "%%=DSrcName.#2";

    // Open the Workbook as ExcelTemplate
    ExcelTemplate xlt = new ExcelTemplate();
    xlt.Open(xla, wb);

    // Bind data using ExcelTemplate SetDataSource
    string[,] values = {{"New York", "NY"},
		{"Miami", "FL"},
		{"Boston", "MA"}};
    string[] names = {"City", "State"};
    xlt.SetDataSource(values, names, "DSrcName");
    xlt.Process();
    xlt.Save(Page.Response, "AppToTemplate.xls", false);
  }
}

Code Sample: Passing ExcelApplication to ExcelTemplate

Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.