Page History

Wiki Markup

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#

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


    }
}

Vbnet

Option Strict On
Option Explicit On

{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 =



        ' 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



    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

 Sub

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


        GenerateReport()

End

Sub

 Sub

    ''' <summary>


    ''' Build the report with ExcelApplication


    ''' </summary>


    Private Sub GenerateReport()


        PopulateTemplate()


        AddChart()

' Save the report by streaming it
'* to the client's browser



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

End

Sub

 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 =


        ' Get the first two worksheets and give them names
        Dim ws As Worksheet = wb.Worksheets(0)


        ws.Name = "Data"

Dim ws2 As Worksheet =



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

' Create a chart on the second worksheet
Dim anch As Anchor =



        ' Create a chart on the second worksheet
        Dim anch As Anchor = ws2.CreateAnchor(0, 0, 50, 50)

Dim chrt As Chart =


        Dim chrt As Chart = ws2.Charts.CreateChart(ChartType.Column.Clustered, anch)

' Set series and category data
Dim srs1 As Series =



        ' 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



        ' Configure the chart's

legend
Dim lgnd As Legend = chrt.Legend

 legend
        Dim lgnd As Legend = chrt.Legend
        lgnd.Visible =

True

 True
        lgnd.Location = Legend.LegendLocation.Right

' Set the



        ' Set the chart's Title string and display properties.


        chrt.Title.Text = "AdventureWorks Global Sales"

End

Sub

 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 =


        ' 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()



        ' 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 =



        ' 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

 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

 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)



        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


        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.


            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



    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



    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

 Designer
        'Do not modify it using the code editor.


        InitializeComponent()


    End Sub



#End Region



End Class


End Namespace
{vbnet}

Space Shortcuts

Page tree

Versions Compared

Old Version 8

New Version 9

Key