Tools Links Login

Office Web Chart

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.

Original Author: Billy Yawn

Inputs

You need a database with data in it that you want to plot

Assumptions

You need office web components installed on the server

Code

<%@ Language=VBScript %>




<%
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
    .Clear
    .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"
   Next

   '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"
  Next
'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
rst.Close
cnn.Close
Set oChartSpace = Nothing
Set rst = Nothing
Set cnn = Nothing
%>









Chart





















You also need to add the following to your global.asa file
Sub Session_OnStart
Set Session("FSO") = CreateObject("Scripting.FileSystemObject")
Session("n")=0
Session.Timeout=1
End Sub
Sub Session_OnEnd
dim x
for x = 0 to Session("n") - 1
Session("FSO").DeleteFile Session("sTempFile" & x), True
next
end sub

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 103 times

Categories

ASP/ HTML

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.