VB icon

Office Web Chart

Submitted on: 1/5/2015 11:09:00 AM
By: Billy Yawn (from psc cd)  
Level: Beginner
User Rating: By 7 Users
Compatibility: ASP (Active Server Pages), HTML, VbScript (browser/client side)
Views: 1823
     This code will create an Office web chart from the data supplied from a database. It will then create a gif file and place it on a web page.
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
' Name: Office Web Chart
' Description:This code will create an Office web chart from the data supplied from a database. It will then create a gif file and place it on a web page.
' By: Billy Yawn (from psc cd)
' Inputs:You need a database with data in it that you want to plot
' Assumes:You need office web components installed on the server

<%@ Language=VBScript %>
<META NAME="GENERATOR" Content="Microsoft FrontPage 4.0">
dim sql
'SQL Statment to select data from a database. This will be bound to a chart.
sql = "SELECT Date, [Open], High, Low, Last, [Day Volume], OBV from symbol"
'Create ADO connection, recordset and command object for database
Set cnn = Server.CreateObject("ADODB.Connection") 
Set rst = Server.CreateObject("ADODB.Recordset")
Set cmd = Server.CreateObject("ADODB.Command")
cnn.Open "DSN=Stocks;uid=sa;pwd=;"
Set cmd.ActiveConnection = cnn
cmd.CommandText = sql
rst.CursorLocation = adUseClient
rst.Open cmd, , adOpenStatic, adLockBatchOptimistic
'Define a chartspace and create an Office Web Chart object
dim oChartSpace, oConst
set oChartSpace = server.CreateObject("OWC.Chart")
set oConst = oChartSpace.Constants
With oChartSpace
.Border.Color = oConst.chColorNone
.Interior.Color = "gainsboro"
.HasChartSpaceTitle = True
.ChartSpaceTitle.Caption = "Open - High - Low - Close"
.ChartSpaceTitle.Font.Name = "Tahoma"
.ChartSpaceTitle.Font.Size = 12
.ChartSpaceTitle.Font.Bold = True
End With
'Bind database recordset to chart datasource
set oChartSpace.DataSource = rst
'Define first chart in chartspace
dim oChart1
set oChart1 = oChartSpace.Charts.Add
dim oSeries
set oSeries = oChart1.SeriesCollection.Add
'Specify the Marker style of the Series
oChartSpace.Charts(0).SeriesCollection(0).Marker.Style = oConst.chMarkerStyleNone
oChartSpace.Charts(0).SeriesCollection(0).Marker.Size = 1
'A OHLC chart is a stock Open - High - Low - Close chart
oChartSpace.Charts(0).Type = oConst.chChartTypeStockOHLC
'Add the dates and OHLC values
With oChartSpace.Charts(0).SeriesCollection(0)
.Caption = "Daily"
.SetData oConst.chDimCategories, 0, 0 'Dates item 0 from sql statement
.SetData oConst.chDimOpenValues, 0, 1 'Open item 1 from sql statement
.SetData oConst.chDimHighValues, 0, 2 'High item 2 from sql statement
.SetData oConst.chDimLowValues, 0, 3 'Low item 3 from sql statement
.SetData oConst.chDimCloseValues, 0, 4 'Close item 4 from sql statement
End With
oChartSpace.Charts(0).Axes(oConst.chAxisPositionBottom).HasMajorGridlines = true
'Remove the ticklabels
oChart1.Axes(oConst.chAxisPositionBottom).HasTickLabels = False
'--- Create a Column Chart for Volume ---
'Add a second chart to the Chartspace for the Volume
Set oChart2 = oChartSpace.Charts.Add
oChart2.Type = oConst.chChartTypeColumnClustered
oChart2.SetData oConst.chDimCategories, 0, 0 'Dates item 0 from sql statement
oChart2.SetData oConst.chDimValues, 0, 5 'Volume item 5 from sql statement
oChart2.SeriesCollection(0).Caption = "Volume"
Set oChart3 = oChartSpace.Charts.Add
oChart3.Type = oConst.chChartTypeLine
oChart3.SetData oConst.chDimCategories, 0, 0 'Dates item 0 from sql statement
oChart3.SetData oConst.chDimValues, 0, 6 'OBV item 6 from sql statement
oChart3.SeriesCollection(0).Caption = "On Balance Volume"
'--- Apply Formatting Common to Both Charts ---
'Make the HLC chart twice as large as the Volume chart
oChart1.HeightRatio = 200
oChart2.HeightRatio = 100
oChart3.HeightRatio = 100
For each oCht in oChartSpace.Charts
 'Display the legend to the Right of the Chart, remove the legend border and change the color
 oCht.HasLegend = True
 oCht.Legend.Position = oConst.chLegendPositionRight
 oCht.Legend.Border.Color = oConst.chColorNone
 oCht.Legend.Interior.Color = "gainsboro"
 'Remove tick labels for both axes
 oCht.Axes(oConst.chAxisPositionBottom).MajorTickmarks = oConst.chTickMarkNone
 oCht.Axes(oConst.chAxisPositionLeft).MajorTickmarks = oConst.chTickMarkNone
 'Change the weight and color of the axes lines
 oCht.Axes(oConst.chAxisPositionBottom).Line.Color = "SLATEGRAY"
 oCht.Axes(oConst.chAxisPositionLeft).Line.Color = "SLATEGRAY"
 oCht.Axes(oConst.chAxisPositionBottom).Line.Weight = oConst.owcLineWeightMedium
 oCht.Axes(oConst.chAxisPositionLeft).Line.Weight = oConst.owcLineWeightMedium
 'Display the category axis gridlines
 oCht.Axes(oConst.chAxisPositionBottom).HasMajorGridlines = true
 'Change gridlines color
 oCht.Axes(oConst.chAxisPositionLeft).MajorGridlines.Line.Color = "BURLYWOOD"
 oCht.Axes(oConst.chAxisPositionBottom).MajorGridlines.Line.Color = "BURLYWOOD"
 'Change color of all series
 For each oSeries in oCht.SeriesCollection
 oSeries.Interior.Color = "SLATEGRAY"
 oSeries.Border.Color = "SLATEGRAY"
 oSeries.Line.Color = "SLATEGRAY"
 'Change plotarea color
 oCht.PlotArea.Interior.Color = "BISQUE"
 'Change Font style for the axis and the legends
 oCht.Axes(oConst.chAxisPositionBottom).Font.Name = "Tahoma"
 oCht.Axes(oConst.chAxisPositionLeft).Font.Name = "Tahoma"
 oCht.Legend.Font.Name = "Tahoma"
'This exports a gif from the chart object so the remote user does not need office to see the chart
'Use the SessionID and file count to determine a unique filename
sFilename = Session.SessionID & "_" & Session("FSO").GetTempName
'Create a GIF image of the Chart
oChartSpace.ExportPicture Server.MapPath(sFilename), "GIF", 700, 500
'Store the filename in the Session object and increment the file count variable
Session ("GIF" & Session("nGIFCount")) = Server.MapPath(".") & "\" & sFileName
Session("nGIFCount") = Session("nGIFCount") + 1
Set oChartSpace = Nothing
Set rst = Nothing
Set cnn = Nothing
<FONT FACE="Tahoma">
<IMG SRC='<%=sFilename%>'>
You also need to add the following to your global.asa file
Sub Session_OnStart
	Set Session("FSO") = CreateObject("Scripting.FileSystemObject")
End Sub
Sub Session_OnEnd
	dim x
	for x = 0 to Session("n") - 1
		Session("FSO").DeleteFile Session("sTempFile" & x), True
end sub

Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Beginner category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

 There are no comments on this submission.

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)

To post feedback, first please login.