Passing ExcelTemplate to ExcelApplication

Passing Passing ExcelTemplate to ExcelApplication in ASP

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. This section shows you how to populate a template spreadsheet with the ExcelTemplate object, and pass the spreadsheet to ExcelApplication to add a chart.

Select the Chart worksheet to see the chart generated by ExcelApplication.

In this sample, form.asp defines an HTML form that submits a request to AddChart.asp. In AddChart.asp, ExcelTemplate opens the template Nwind_Sales_by_country_NoChart.xls, populates it, and passes it to ExcelApplication to be modified. ExcelApplication is used to add a chart to the template. The values imported from the database provide the source data for the chart.

In AddChart.asp, a SQL query is submitted to the Northwind database (Northwind.mdb) to retrieve total sales values by country:

SalesDataSql = "SELECT DISTINCT Customers.Country, " & _
	"Sum(([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS " & _
	"[Total Sales] FROM (Customers INNER JOIN ([Order Details] " & _
	"INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID) " & _
	"ON Customers.CustomerID = Orders.CustomerID) " & _
	"GROUP BY Customers.Country ORDER BY Customers.Country"		
'--- Create and open an ADO Connection.
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("connstring")
...
'--- Query the database and get a RecordSet.
Set RecSet = Conn.Execute(SalesDataSql)
...

The ExcelTemplate object opens the template Nwind_Sales_by_country_NoChart.xls, which contains two data markers: %%=Sales.#1 and %%=Sales.#2. These are bound to the RecordSet retrieved from the database:

Set XlwTempl = Server.CreateObject("SoftArtisans.ExcelTemplate")
...
'--- Open the template workbook.
XlwTempl.Open Server.MapPath("./Nwind_Sales_by_country_NoChart.xls")
...
'--- Set PreserveStrings=true to make Excel handle
'--- numeric strings as text values.
XlwTempl.PreserveStrings = True
'--- Set the data source for the template, binding
'--- the DataTable to the Sales data markers
'--- %%=Sales.*
XlwTempl.DataSource("Sales") = RecSet

The Process method, enters the RecordSet values in the template and generates a new spreadsheet. To pass the generated spreadsheet to ExcelApplication, the second parameter of Process - ProcessMethod - is set to saProcessOpenForScripting:

Set XlwApp = XlwTempl.Process("", saProcessOpenForScripting)

The spreadsheet is returned as XlwApp - an ExcelApplication object. The RecordSet was imported to the data markers on the first worksheet in the spreadsheet. A chart is now added to a second worksheet, which is named "Chart."

Set ws2 = XlwApp.Worksheets(2)
ws2.Name = "Chart"
Set Chart1 = ws2.Charts.Add(saxlsColumnChart,0,1,1,21,12)
...
Chart1.AutoScale = False

A set of custom fonts are created for the chart:

Set Chart1TitleFont = XlwApp.CreateFont()
...
Set Chart1AxisFont = XlwApp.CreateFont()
...
Set Chart1AxisTitleFont = XlwApp.CreateFont()
...
Set LegendFont = XlwApp.CreateFont()
...

The values imported to the first worksheet - named "Data" - are use to plot a data series in the chart, and to specify the range of category axis values:

Set Series1 = Chart1.SeriesCollection.Add("=Data!B2:B22",, 1)
Chart1.SeriesCollection.CategoryData = "=Data!A2:A22"

A legend is added to the chart, and several legend properties assigned:

Chart1.ShowLegend = True
Chart1.LegendLocation = saxlsObjectRight
Chart1.Legend.TextFont = LegendFont
Chart1.Legend.Area.ForegroundColor = RGB(255,255,204)

A name and column color are assigned to the chart's single data series:

Series1.Name = ws.Cells(1,1).Value
Series1.Area.ForegroundColor = RGB(128,128,0)

Display properties are set for the chart's Title, ChartArea, PlotArea, CategoryAxis, and ValueAxis:

Chart1.Title.Text = "Northwinds Global Sales"
Chart1.Title.TextFont = Chart1TitleFont
...
Chart1.ChartArea.Area.Pattern = saxlsNonePattern
Chart1.ChartArea.Area.ForegroundColor = RGB(128,128,0)
...
Chart1.PlotArea.Area.Pattern = saxlsNonePattern
Chart1.PlotArea.Area.Foregroundcolor = RGB(255,255,204)
...
Chart1.CategoryAxis.TextRotationAngle = 90
Chart1.CategoryAxis.LabelFont = Chart1AxisFont
...
Chart1.ValueAxis.LabelFont = Chart1AxisFont
Chart1.ValueAxis.Title.Text = ws.Cells(1,2).Value
...

Finally, ExcelApplication.Save is called to generate the new spreadsheet and stream it to the client:

Response.Clear
XlwApp.Save "SalesReportWithChart.xls", saOpenInExcel
...
Response.End

Top

Copyright © 2005, SoftArtisans, Inc.