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

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