Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Sample: Passing ExcelTemplate to ExcelApplication

[C#] | [VB.NET]

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