Passing ExcelTemplate to ExcelApplication
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.
In the following example,
ExcelTemplate opens the template
SalesByCountry.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.
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)
...
Set XlwTempl = Server.CreateObject("SoftArtisans.ExcelTemplate")
...
'--- Open the template workbook.
XlwTempl.Open("C:\Templates\SalesByCountry_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)
'--- Add a chart the workbook.
Set ws2 = XlwApp.Worksheets(2)
ws2.Name = "Chart"
Set Chart1 = ws2.Charts.Add(saxlsColumnChart,0,1,1,21,12)
...
Chart1.AutoScale = False
'--- Use he values imported to the first worksheet - named "Data" - to
'--- plot a data series in the chart, and specify the range of category axis
'--- values.
Set Series1 = Chart1.SeriesCollection.Add("=Data!B2:B22",, 1)
Chart1.SeriesCollection.CategoryData = "=Data!A2:A22"
'--- Add a legend to the chart, and assign legend properties.
Chart1.ShowLegend = True
Chart1.LegendLocation = saxlsObjectRight
Chart1.Legend.TextFont = LegendFont
Chart1.Legend.Area.ForegroundColor = RGB(255,255,204)
'--- Call ExcelApplication.Save to generate the new spreadsheet
'--- and stream it to the client.
Response.Clear
XlwApp.Save "SalesReportWithChart.xls", saOpenInExcel
...
Response.End
Copyright 2007 © SoftArtisans, Inc. All Rights Reserved.
|
