Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

...

ExcelWriter

...

allows

...

you

...

to

...

generate

...

a

...

spreadsheet

...

from

...

script

...

alone

...

-

...

using

...

the

...

ExcelApplication

...

object

...

-

...

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

...

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 Block
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 

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


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}