Creating Charts in ASP

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

When you present dynamic data in web pages, you can enhance the comprehension of the data by the user by providing a nice chart. Although there are many ways to generate charts in ASP, in this section we will focus on using a tool that you are already familiar with. Simply put, you will use the Microsoft Excel Chart Wizard to create a chart. A word of caution: Using Chart Wizard requires that you have a copy of Microsoft Office installed on your web server. Also, keep in mind that with this technique, Excel needs to be loaded into memory; therefore, using this approach for a high-volume web site is not recommended.

The example below demonstrates how to create a chart based on data pulled dynamically from the Microsoft Access sample Northwind database.

Step 1: Creating the ASP Script to Obtain the Data and Generate the Chart

1. Open Notepad.

2. Enter the ASP script shown below.

3. Save the ASP file as C:\ExcelWithASP\MakeChart.asp.

4. Close Notepad. MakeChart.asp code

' Constant declaration Const adOpenStatic = 3 Const adLockReadOnly = 1

Const xlColumnClustered = 51 Const xlRows = 1 Const xlLocationAsObject = 2 Const xlCategory = 1 Const xlPrimary = 1 Const xlValue = 2 Const xlHtml = 44

' Variable declaration dim myExcel ' Object variable representing Excel Application.

dim fso ' Object variable representing the FileSystemObject.

dim filename ' String variable to hold the name of the chart file.

dim conn ' Object variable representing the Connection object.

dim rst ' Object variable representing the Recordset object.

Dim wkb ' Object variable representing the Workbook object.

Dim rng ' Object variable representing the Range object.

Dim fld ' Dummy variable for enumerating fields.

Dim rowNum ' Row counter.

Dim colNum ' Column counter.

Dim varData ' Variant to hold returned Recordset.

Set myExcel= Server.Createobject("Excel.Application") Set fso = Server.Createobject("Scripting.FileSystemObject")

' If exists, delete the previously prepared chart filename = "c:\xlsChart.htm" If fso.FileExists(filename) Then fso.DeleteFile filename, True End If

' Release the FileSystemObject Set fso = Nothing

' Create a new workbook set wkb = myExcel.Workbooks.Add

' Get data to chart from Microsoft Access database ' Create and establish connection to the database Set conn = Server.CreateObject("ADODB.Connection")

' Open the Northwind database conn.Open "Driver={Microsoft Access Driver (*.mdb)};" &_ "DBQ=" & Server.MapPath("Northwind.mdb")

' Create recordset and retrieve values using the open connection Set rst = Server.CreateObject("ADODB.Recordset")

' Open the Recordset with a static cursor (3) in read-only mode (1) rst.Open "SELECT CategoryName As [Product Category], " &_ "SUM(Quantity) AS [Total Quantity Sold] " & _ "FROM Categories " & _

"INNER JOIN (Products INNER JOIN [Order Details] ON " & _ "Products.ProductID = [Order Details].ProductID) ON " & _ "Categories.CategoryID = Products.CategoryID " & _

"GROUP BY Categories.CategoryName " & _ "ORDER BY Categories.CategoryName", _ conn, adOpenStatic, adLockReadOnly

' Add field names as column headers. For fld = 0 to rst.Fields.Count - 1 colNum = colNum + 1

wkb.ActiveSheet.Cells(1, colNum).Value = _ rst.Fields(fld).Name


' Store the records in a variable varData = rst.GetRows()

'Place data from the database in a worksheet. For rowNum = 1 To rst.RecordCount For colNum = 0 To UBound(varData)

wkb.ActiveSheet.Cells(rowNum + 1, colNum + 1).Value = _ varData(colNum, rowNum - 1)

Next Next

' Close the Recordset and release the object rst.Close set rst = Nothing

' Close the Connection to the database conn.Close Set conn = Nothing

' Autofit the used range wkb.ActiveSheet.UsedRange.Columns.Autofit

' Set the range of the chart set rng = wkb.Sheets("Sheet1").Range("A1").CurrentRegion

' Create a chart based on pulled data wkb.Charts.Add

' Format the chart wkb.ActiveChart.ChartType = xlColumnClustered

' Specify the data source of the chart wkb.ActiveChart.SetSourceData rng, xlRows

' Place the chart on the second sheet wkb.ActiveChart.Location xlLocationAsObject, "Sheet2"

' Add chart and value axis titles With wkb.ActiveChart .HasTitle = True

.ChartTitle.Characters.Text = "Quantity Sales by Category" .Axes(xlValue, xlPrimary).HasTitle = True

.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Quantity"

End With

' Save the workbook file as a Web Page (in HTML format) wkb.SaveAs filename, xlHtml

' Close the workbook myExcel.ActiveWorkbook.Close

' Shut down Excel application and release the object myExcel.Quit

Set myExcel = Nothing

' Display the generated Web Page in the browser

Response.Redirect filename

The ASP script shown above is well commented, so you should not have any trouble understanding the entire process. In short, we will start by defining constants and variables. Constant declaration will allow you to use the intrinsic constants instead of their values and make the code easier to understand. Before creating a new workbook, the script uses the FileSystemObject to delete the previously prepared HTML file if it exists. The remaining part of the ASP script can be broken into the following main sections:

■ Creating a new workbook

■ Connecting to the Access database and obtaining the data

■ Writing out column headings and the data to a web page (notice how the GetRows method is used to store the data in a two-dimensional array)

■ Closing the Recordset and connection to the Access database

■ Creating and formatting the chart. Some of the code for this section can be recorded using the macro recorder if you are not very familiar with Chart objects, methods, and properties.

■ Saving the workbook as a web page. When you save a workbook file as a web page, the document is saved as an HTML file. In addition, a folder containing all the supporting files that are referenced by the HTML file is created on your hard drive. This folder is named name_files, where name is the document name. Therefore, when your ASP code saves the workbook in HTML format using the following statement:

wkb.Save As filename, xlhtml you should see on your computer a folder of supporting files named xlsChart_files.

If you'd rather keep the supporting files in the same folder as the HTML file, you can indicate your preference in the Options dialog box. Simply click the General tab and press the Web options button. Next, click the Files tab, and clear the Organize supporting files in a folder check box.

■ Closing the open file and quitting Excel

■ Displaying the generated web page in a browser. Response.Redirect tells the browser to request a different page (in this case, the newly created xlsChart.htm file).

Step 2: Running the ASP Script—MakeChart.asp

To try out your ASP script, perform the following:

1. Open your Internet browser.

2. Enter the following address: http://localhost/accessDB/Make-Chart.asp. You should see the xlsChart.htm file in the browser with Sheet1 displaying the data pulled from the Access database.

'3 c:\KkChart-htm

File Edit View Favorites

Tools Help


^ Back ' ig 0 a

^ Search

"jy Favorites

ijHistory »

Address jl^jC:\xtsChart.htm

- ¿>Go

Product Category Total Quantity Sold

Beverages 9532

Condiments 5298

Confections 7906

Dairy Products 9149

Grains/Cereals 4562

Meat/Poultry 4399

Produce 2590

Seafood 7681

Product Category Total Quantity Sold

Beverages 9532

Condiments 5298

Confections 7906

Dairy Products 9149

Grains/Cereals 4562

Meat/Poultry 4399

Produce 2590

Seafood 7681

Sheetl I Sheet2 | Sheet3 |

''J My Computer

Figure 16-40:

Data used for charting can be obtained dynamically from the Microsoft Access database.

3. Click the Sheet2 tab to view the chart.

Figure 16-41:

You can use the Microsoft Excel Chart Wizard to generate a chart in an ASP page.

Figure 16-41:

You can use the Microsoft Excel Chart Wizard to generate a chart in an ASP page.

Note: To see other examples of charting using Excel, see Appendices A and D.

Was this article helpful?

0 0

Post a comment