ExcelWriter allows you to generate a spreadsheet from script alone - using the [ExcelApplication|Basic ExcelApplication Tutorial] object     - or from a template spreadsheet and a script, using [ExcelTemplate|Old Basic ExcelTemplate Tutorial]. ExcelTemplate provides an intuitive     high-performance way to import database values to a spreadsheet, but cannot otherwise     modify a spreadsheet at runtime. ExcelApplication's rich object model     allows you to modify every aspect of the spreadsheet at runtime. You can take advantage     of the features of both ExcelApplication and ExcelTemplate by using them together. For example, you can use ExcelTemplate to open     and populate an ExcelWriter template, then pass the populated workbook to ExcelApplication and add a chart.
To pass a workbook from ExcelTemplate to ExcelApplication,     do not call ExcelTemplate.Save. Instead, pass the ExcelTemplate object to ExcelApplication's Open method:
{code:c#}
ExcelTemplate xlt = new ExcelTemplate();
xlt.Open(templatePath);
xlt.BindData(data, "Sales", xlt.CreateDataBindingProperties());
xlt.Process();

//--- Create an instance of ExcelApplication and
//--- open the spreadsheet you created with ExcelTemplate.
//--- The spreadsheet will be returned as a Workbook
//--- object.
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(xlt);
{code}

h1. Code Sample: Passing ExcelTemplate to ExcelApplication

{csharp}
//--------------------------------------------------------------
//--- SoftArtisans OfficeWriter ExcelApplication Template-To-App Sample
//---
//--- Demonstrates how the ExcelTemplate and ExcelApplication objects
//--- can be used together in the same request.
//--- Populate an ExcelTemplate workbook with data and then open the
//--- populated workbook with the ExcelApplication API for further edits.
//---
//--- (c) 2009 SoftArtisans, Inc.
//--- Support: http://support.softartisans.com
//--- Sales:   sales@softartisans.com
//--------------------------------------------------------------

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SoftArtisans.OfficeWriter.ExcelWriter;

namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples
{
    /// <summary>
    /// Summary description for TemplateToApp.
    /// </summary>
    public class TemplateToApp : System.Web.UI.Page
    {
        /* Both ExcelApplication and ExcelTemplate
        * objects will be used for this demo.
        */
        private ExcelTemplate xlt;
        private ExcelApplication xlw;
        private Workbook wb;
        protected System.Web.UI.HtmlControls.HtmlInputButton btnTemplateToApp;
        private string connString =
            System.Configuration.ConfigurationManager.AppSettings["connString"];


        private void btnTemplateToApp_ServerClick(object sender, System.EventArgs e)
        {
            GenerateReport();
        }

        /// <summary>
        /// Build the report with ExcelApplication
        /// </summary>
        private void GenerateReport()
        {
            PopulateTemplate();
            AddChart();

            /* Save the report by streaming it
             * to the client's browser */
            xlw.Save(wb, Page.Response, "TemplateToApp.xls", false);
        }

        /// <summary> Add a column chart to the second worksheet using ExcelApplication.
        /// The chart will show data imported with the ExcelTemplate
        /// object.</summary>
        private void AddChart()
        {
            /* Get the first two worksheets and give them names */
            Worksheet ws = wb.Worksheets[0];
            ws.Name = "Data";

            Worksheet ws2 = wb.Worksheets.CreateWorksheet("ChartSheet");

            /* Create a chart on the second worksheet */
            Anchor anch = ws2.CreateAnchor(0, 0, 50, 50);
            Chart chrt = ws2.Charts.CreateChart(ChartType.Column.Clustered, anch);

            /* Set series and category data */
            Series srs1 = chrt.SeriesCollection.CreateSeries("=Data!B2:B7");
            chrt.SeriesCollection.CategoryData = "=Data!A2:A7";
            srs1.NameFormula = "=Data!A1";

            /* Configure the chart's legend */
            Legend lgnd = chrt.Legend;
            lgnd.Visible = true;
            lgnd.Location = Legend.LegendLocation.Right;

            /* Set the chart's Title string and display properties. */
            chrt.Title.Text = "AdventureWorks Global Sales";
        }

        /// <summary> Populate the template workbook with database
        /// data using the ExcelTemplate object.  The ExcelTemplate
        /// save() method returns a Workbook.
        /// </summary>
        private void PopulateTemplate()
        {
            /* Create an instance of ExcelTemplate and open
            * the template workbook
            */
            xlt = new ExcelTemplate();

            /* Open the template workbook */
            string templatePath = Page.MapPath("templates/TemplateToAppTemplate.xls");
            xlt.Open(templatePath);

            /* Get the data from the database and set it as
            * a data source.
            */
            DataTable dt = GetData();
            xlt.BindData(dt, "Sales", xlt.CreateDataBindingProperties());
            xlt.Process();

            /* Here the populated ExcelTemplate object
            * is being opened as an ExcelApplication Workbook.
            * The object can now be programatically manipulated
            * with the ExcelApplication API
            */
            xlw = new ExcelApplication();
            wb = xlw.Open(xlt);
        }

        /// <summary> Query the database for the data that will
        /// be imported by the ExcelTemplate object.
        /// </summary>
        /// <returns>DataTable of data for the report</returns>
        private DataTable GetData()
        {
            string sql = "SELECT Person.StateProvince.CountryRegionCode As Country, " +
                "SUM(Sales.SalesOrderHeader.TotalDue) As TotalSales " +
                "FROM Sales.SalesOrderHeader " +
                "JOIN Sales.CustomerAddress " +
                "ON Sales.SalesOrderHeader.CustomerID=Sales.CustomerAddress.CustomerID " +
                "JOIN Person.Address " +
                "ON Sales.CustomerAddress.AddressID=Person.Address.AddressID " +
                "JOIN Person.StateProvince " +
                "ON Person.StateProvince.StateProvinceID=Person.Address.StateProvinceID " +
                "GROUP BY Person.StateProvince.CountryRegionCode";

            DataTable dt = new DataTable();
            using(SqlConnection conn = new SqlConnection(connString))
                new SqlDataAdapter(sql, conn).Fill(dt);

            return dt;
        }

        #region Web Form Designer generated code
        override protected void OnInit(EventArgs e)
        {
            //
            // CODEGEN: This call is required by the ASP.NET Web Form Designer.
            //
            InitializeComponent();
            base.OnInit(e);
        }

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.btnTemplateToApp.ServerClick += new System.EventHandler(this.btnTemplateToApp_ServerClick);

        }
        #endregion


    }
}
{csharp}
{vbnet}
Option Strict On
Option Explicit On


'--------------------------------------------------------------
'--- SoftArtisans OfficeWriter ExcelApplication Template-To-App Sample
'---
'--- Demonstrates how the ExcelTemplate and ExcelApplication objects
'--- can be used together in the same request.
'--- Populate an ExcelTemplate workbook with data and then open the
'--- populated workbook with the ExcelApplication API for further edits.
'---
'--- (c) 2009 SoftArtisans, Inc.
'--- Support: http://support.softartisans.com
'--- Sales:   sales@softartisans.com
'--------------------------------------------------------------

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports SoftArtisans.OfficeWriter.ExcelWriter

Namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples

Public Class TemplateToApp
    Inherits System.Web.UI.Page

        ' Both ExcelApplication and ExcelTemplate
        '* objects will be used for this demo.
        '
        Private xlt As ExcelTemplate
        Private xlw As ExcelApplication
        Private wb As Workbook
        Private connString As String = System.Configuration.ConfigurationManager.AppSettings("connString")

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub

    Private Sub btnTemplateToApp_ServerClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTemplateToApp.ServerClick
        GenerateReport()
    End Sub

    ''' <summary>
    ''' Build the report with ExcelApplication
    ''' </summary>
    Private Sub GenerateReport()
        PopulateTemplate()
        AddChart()

        ' Save the report by streaming it
        '* to the client's browser
        xlw.Save(wb, Page.Response, "TemplateToApp.xls", False)
    End Sub

    ''' <summary> Add a column chart to the second worksheet using ExcelApplication.
    ''' The chart will show data imported with the ExcelTemplate
    ''' object.</summary>
    Private Sub AddChart()
        ' Get the first two worksheets and give them names
        Dim ws As Worksheet = wb.Worksheets(0)
        ws.Name = "Data"

        Dim ws2 As Worksheet = wb.Worksheets.CreateWorksheet("ChartSheet")

        ' Create a chart on the second worksheet
        Dim anch As Anchor = ws2.CreateAnchor(0, 0, 50, 50)
        Dim chrt As Chart = ws2.Charts.CreateChart(ChartType.Column.Clustered, anch)

        ' Set series and category data
        Dim srs1 As Series = chrt.SeriesCollection.CreateSeries("=Data!B2:B7")
        chrt.SeriesCollection.CategoryData = "=Data!A2:A7"
        srs1.NameFormula = "=Data!A1"

        ' Configure the chart's legend
        Dim lgnd As Legend = chrt.Legend
        lgnd.Visible = True
        lgnd.Location = Legend.LegendLocation.Right

        ' Set the chart's Title string and display properties.
        chrt.Title.Text = "AdventureWorks Global Sales"
    End Sub

    ''' <summary> Populate the template workbook with database
    ''' data using the ExcelTemplate object.  The ExcelTemplate
    ''' save() method returns a Workbook.
    ''' /summary>
    Private Sub PopulateTemplate()
        ' Create an instance of ExcelTemplate and open
        '* the template workbook
        '
        xlt = New ExcelTemplate

        ' Open the template workbook
        Dim templatePath As String = Page.MapPath("templates/TemplateToAppTemplate.xls")
        xlt.Open(templatePath)

        ' Get the data from the database and set it as
        '* a data source.
        '
        Dim dt As DataTable = GetData()
        xlt.BindData(dt,"Sales",xlt.CreateDataBindingProperties())
        xlt.Process()

        ' Here the populated ExcelTemplate object
        '* is being opened as an ExcelApplication Workbook.
        '* The object can now be programatically manipulated
        '* with the ExcelApplication API
        '
        xlw = New ExcelApplication
        wb = xlw.Open(xlt)
    End Sub

    ''' <summary> Query the database for the data that will
    ''' be imported by the ExcelTemplate object.
    ''' </summary>
    ''' <returns>DataTable of data for the report</returns>
    Private Function GetData() As DataTable
            Dim sql As String = "SELECT Person.StateProvince.CountryRegionCode As Country, " + _
                "SUM(Sales.SalesOrderHeader.TotalDue) As TotalSales " + _
                "FROM Sales.SalesOrderHeader " + _
                "JOIN Sales.CustomerAddress " + _
                "ON Sales.SalesOrderHeader.CustomerID=Sales.CustomerAddress.CustomerID " + _
                "JOIN Person.Address " + _
                "ON Sales.CustomerAddress.AddressID=Person.Address.AddressID " + _
                "JOIN Person.StateProvince " + _
                "ON Person.StateProvince.StateProvinceID=Person.Address.StateProvinceID " + _
                "GROUP BY Person.StateProvince.CountryRegionCode"

        Dim dt As DataTable = New DataTable
        Dim conn As SqlConnection = New SqlConnection(connString)
        Try
            Dim adpt As New SqlDataAdapter(sql, conn)
            adpt.Fill(dt)
        Finally
            If Not conn Is Nothing Then
                conn.Dispose()
            End If
        End Try

        Return dt
    End Function


#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm
    Protected WithEvents btnTemplateToApp As System.Web.UI.HtmlControls.HtmlInputButton

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

End Class
End Namespace
{vbnet}