Message-ID: <373167234.10235.1711718722722.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10234_477766081.1711718722722" ------=_Part_10234_477766081.1711718722722 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Passing ExcelTemplate to ExcelApplication

Passing ExcelTemplate to ExcelApplication

ExcelWriter allows you to generate a spreadsheet from script alo= ne - using the ExcelApplication obje= ct - or from a template spreadsheet and a script, using ExcelTemplate. ExcelTemplate provides an intuitive high= -performance way to import database values to a spreadsheet, but cannot oth= erwise modify a spreadsheet at runtime. ExcelApplication's rich object mode= l allows you to modify every aspect of the spreadsheet at runtime. You can = take advantage of the features of both ExcelApplication and ExcelTemplate b= y using them together. For example, you can use ExcelTemplate to open and p= opulate an ExcelWriter template, then pass the populated workbook to ExcelA= pplication and add a chart.
To pass a workbook from ExcelTemplate to = ExcelApplication, do not call ExcelTemplate.Save. Instead, pass the ExcelTe= mplate object to ExcelApplication's Open method:

=20
=20
ExcelTemplate xlt =3D 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 =3D new ExcelApplication();
Workbook wb =3D xla.Open(xlt);
=20
=20

Code Sample: Passing ExcelTemplate to ExcelApplica= tion

=20
=20
//--------------------------------------------------------------
//--- 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 btnTemplateToA=
pp;
        private string connString =3D
            System.Configuration.ConfigurationManager.AppSettings["con=
nString"];


        private void btnTemplateToApp_ServerClick(object sender, System.Eve=
ntArgs 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", fals=
e);
        }

        /// <summary> Add a column chart to the second worksheet usin=
g 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 =3D wb.Worksheets[0];
            ws.Name =3D "Data";

            Worksheet ws2 =3D wb.Worksheets.CreateWorksheet("ChartShee=
t");

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

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

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

            /* Set the chart's Title string and display properties. */
            chrt.Title.Text =3D "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 =3D new ExcelTemplate();

            /* Open the template workbook */
            string templatePath =3D Page.MapPath("templates/TemplateTo=
AppTemplate.xls");
            xlt.Open(templatePath);

            /* Get the data from the database and set it as
            * a data source.
            */
            DataTable dt =3D GetData();
            xlt.BindData(dt, "Sales", xlt.CreateDataBindingProper=
ties());
            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 =3D new ExcelApplication();
            wb =3D 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 =3D "SELECT Person.StateProvince.CountryRegionC=
ode As Country, " +
                "SUM(Sales.SalesOrderHeader.TotalDue) As TotalSales &q=
uot; +
                "FROM Sales.SalesOrderHeader " +
                "JOIN Sales.CustomerAddress " +
                "ON Sales.SalesOrderHeader.CustomerID=3DSales.Customer=
Address.CustomerID " +
                "JOIN Person.Address " +
                "ON Sales.CustomerAddress.AddressID=3DPerson.Address.A=
ddressID " +
                "JOIN Person.StateProvince " +
                "ON Person.StateProvince.StateProvinceID=3DPerson.Addr=
ess.StateProvinceID " +
                "GROUP BY Person.StateProvince.CountryRegionCode"=
;

            DataTable dt =3D new DataTable();
            using(SqlConnection conn =3D 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 Desig=
ner.
            //
            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 +=3D new System.EventHandler(=
this.btnTemplateToApp_ServerClick);

        }
        #endregion


    }
}
=20
=20
=20
<p>Option Strict On<br />
Option Explicit On</p>


<p>&apos;--------------------------------------------------------=
------<br />
&apos;--- SoftArtisans OfficeWriter ExcelApplication Template-To-App Sa=
mple<br />
&apos;---<br />
&apos;--- Demonstrates how the ExcelTemplate and ExcelApplication objec=
ts<br />
&apos;--- can be used together in the same request.<br />
&apos;--- Populate an ExcelTemplate workbook with data and then open th=
e<br />
&apos;--- populated workbook with the ExcelApplication API for further =
edits.<br />
&apos;---<br />
&apos;--- (c) 2009 SoftArtisans, Inc.<br />
&apos;--- Support: <a class=3D"external-link" href=3D"=
;http://support.softartisans.com" rel=3D"nofollow">http:/=
/support.softartisans.com</a><br />
&apos;--- Sales:   sales@softartisans.com<br />
&apos;--------------------------------------------------------------<=
;/p>

<p>Imports System.Data<br />
Imports System.Data.SqlClient<br />
Imports System.Configuration<br />
Imports SoftArtisans.OfficeWriter.ExcelWriter</p>

<p>Namespace SoftArtisans.OfficeWriter.ExcelWriter.Samples</p>

<p>Public Class TemplateToApp<br />
    Inherits System.Web.UI.Page</p>

<p>        &apos; Both ExcelApplication and ExcelTemplate<br /=
>
        &apos;* objects will be used for this demo.<br />
        &apos;<br />
        Private xlt As ExcelTemplate<br />
        Private xlw As ExcelApplication<br />
        Private wb As Workbook<br />
        Private connString As String =3D System.Configuration.Configuration=
Manager.AppSettings(&quot;connString&quot;)</p>

<p>    Private Sub Page_Load(ByVal sender As System.Object, ByVal e A=
s System.EventArgs) Handles MyBase.Load<br />
        &apos;Put user code to initialize the page here<br />
    End Sub</p>

<p>    Private Sub btnTemplateToApp_ServerClick(ByVal sender As Syste=
m.Object, ByVal e As System.EventArgs) Handles btnTemplateToApp.ServerClick=
<br />
        GenerateReport()<br />
    End Sub</p>

<p>    &apos;&apos;&apos; &lt;summary&gt;<br /=
>
    &apos;&apos;&apos; Build the report with ExcelApplication&l=
t;br />
    &apos;&apos;&apos; &lt;/summary&gt;<br />
    Private Sub GenerateReport()<br />
        PopulateTemplate()<br />
        AddChart()</p>

<p>        &apos; Save the report by streaming it<br />
        &apos;* to the client&apos;s browser<br />
        xlw.Save(wb, Page.Response, &quot;TemplateToApp.xls&quot;, =
False)<br />
    End Sub</p>

<p>    &apos;&apos;&apos; &lt;summary&gt; Add a c=
olumn chart to the second worksheet using ExcelApplication.<br />
    &apos;&apos;&apos; The chart will show data imported with t=
he ExcelTemplate<br />
    &apos;&apos;&apos; object.&lt;/summary&gt;<br /&=
gt;
    Private Sub AddChart()<br />
        &apos; Get the first two worksheets and give them names<br /=
>
        Dim ws As Worksheet =3D wb.Worksheets(0)<br />
        ws.Name =3D &quot;Data&quot;</p>

<p>        Dim ws2 As Worksheet =3D wb.Worksheets.CreateWorksheet(&am=
p;quot;ChartSheet&quot;)</p>

<p>        &apos; Create a chart on the second worksheet<br /&=
gt;
        Dim anch As Anchor =3D ws2.CreateAnchor(0, 0, 50, 50)<br />
        Dim chrt As Chart =3D ws2.Charts.CreateChart(ChartType.Column.Clust=
ered, anch)</p>

<p>        &apos; Set series and category data<br />
        Dim srs1 As Series =3D chrt.SeriesCollection.CreateSeries(&quot=
;=3DData!B2:B7&quot;)<br />
        chrt.SeriesCollection.CategoryData =3D &quot;=3DData!A2:A7&=
quot;<br />
        srs1.NameFormula =3D &quot;=3DData!A1&quot;</p>

<p>        &apos; Configure the chart&apos;s legend<br /&g=
t;
        Dim lgnd As Legend =3D chrt.Legend<br />
        lgnd.Visible =3D True<br />
        lgnd.Location =3D Legend.LegendLocation.Right</p>

<p>        &apos; Set the chart&apos;s Title string and displ=
ay properties.<br />
        chrt.Title.Text =3D &quot;AdventureWorks Global Sales&quot;=
<br />
    End Sub</p>

<p>    &apos;&apos;&apos; &lt;summary&gt; Populat=
e the template workbook with database<br />
    &apos;&apos;&apos; data using the ExcelTemplate object.  Th=
e ExcelTemplate<br />
    &apos;&apos;&apos; save() method returns a Workbook.<br =
/>
    &apos;&apos;&apos; /summary&gt;<br />
    Private Sub PopulateTemplate()<br />
        &apos; Create an instance of ExcelTemplate and open<br />
        &apos;* the template workbook<br />
        &apos;<br />
        xlt =3D New ExcelTemplate</p>

<p>        &apos; Open the template workbook<br />
        Dim templatePath As String =3D Page.MapPath(&quot;templates/Tem=
plateToAppTemplate.xls&quot;)<br />
        xlt.Open(templatePath)</p>

<p>        &apos; Get the data from the database and set it as<=
;br />
        &apos;* a data source.<br />
        &apos;<br />
        Dim dt As DataTable =3D GetData()<br />
        xlt.BindData(dt,&quot;Sales&quot;,xlt.CreateDataBindingProp=
erties())<br />
        xlt.Process()</p>

<p>        &apos; Here the populated ExcelTemplate object<br /=
>
        &apos;* is being opened as an ExcelApplication Workbook.<br =
/>
        &apos;* The object can now be programatically manipulated<br=
 />
        &apos;* with the ExcelApplication API<br />
        &apos;<br />
        xlw =3D New ExcelApplication<br />
        wb =3D xlw.Open(xlt)<br />
    End Sub</p>

<p>    &apos;&apos;&apos; &lt;summary&gt; Query t=
he database for the data that will<br />
    &apos;&apos;&apos; be imported by the ExcelTemplate object.=
<br />
    &apos;&apos;&apos; &lt;/summary&gt;<br />
    &apos;&apos;&apos; &lt;returns&gt;DataTable of data=
 for the report&lt;/returns&gt;<br />
    Private Function GetData() As DataTable<br />
            Dim sql As String =3D &quot;SELECT Person.StateProvince.Cou=
ntryRegionCode As Country, &quot; + _<br />
                &quot;SUM(Sales.SalesOrderHeader.TotalDue) As TotalSale=
s &quot; + _<br />
                &quot;FROM Sales.SalesOrderHeader &quot; + _<br =
/>
                &quot;JOIN Sales.CustomerAddress &quot; + _<br /=
>
                &quot;ON Sales.SalesOrderHeader.CustomerID=3DSales.Cust=
omerAddress.CustomerID &quot; + _<br />
                &quot;JOIN Person.Address &quot; + _<br />
                &quot;ON Sales.CustomerAddress.AddressID=3DPerson.Addre=
ss.AddressID &quot; + _<br />
                &quot;JOIN Person.StateProvince &quot; + _<br /&=
gt;
                &quot;ON Person.StateProvince.StateProvinceID=3DPerson.=
Address.StateProvinceID &quot; + _<br />
                &quot;GROUP BY Person.StateProvince.CountryRegionCode&a=
mp;quot;</p>

<p>        Dim dt As DataTable =3D New DataTable<br />
        Dim conn As SqlConnection =3D New SqlConnection(connString)<br /=
>
        Try<br />
            Dim adpt As New SqlDataAdapter(sql, conn)<br />
            adpt.Fill(dt)<br />
        Finally<br />
            If Not conn Is Nothing Then<br />
                conn.Dispose()<br />
            End If<br />
        End Try</p>

<p>        Return dt<br />
    End Function</p>


<p>#Region &quot; Web Form Designer Generated Code &quot;<=
/p>

<p>    &apos;This call is required by the Web Form Designer.<b=
r />
    &lt;System.Diagnostics.DebuggerStepThrough()&gt; Private Sub In=
itializeComponent()</p>

<p>    End Sub<br />
    Protected WithEvents Form1 As System.Web.UI.HtmlControls.HtmlForm<br=
 />
    Protected WithEvents btnTemplateToApp As System.Web.UI.HtmlControls.Htm=
lInputButton</p>

<p>    &apos;NOTE: The following placeholder declaration is requi=
red by the Web Form Designer.<br />
    &apos;Do not delete or move it.<br />
    Private designerPlaceholderDeclaration As System.Object</p>

<p>    Private Sub Page_Init(ByVal sender As System.Object, ByVal e A=
s System.EventArgs) Handles MyBase.Init<br />
        &apos;CODEGEN: This method call is required by the Web Form Des=
igner<br />
        &apos;Do not modify it using the code editor.<br />
        InitializeComponent()<br />
    End Sub</p>

<p>#End Region</p>

<p>End Class<br />
End Namespace</p>
=20
------=_Part_10234_477766081.1711718722722--