Use Excel to Create gif's of Charted Database Information
by Bob Dombroski
The guys in suits decide they want graphs and charts of some data in your database and they want it online, in real time. No problem, lots of ASP com objects to choose from that can generate graphs from data. Ooops, forgot you are over budget this quarter and must make do with what you have. Here we go again, pulling stuff out of a hat. Heres an example of Excel used as a graph/chart generator.The guys in suits decide they want graphs and charts of some data in your database and they want it online, in real time. No problem, lots of ASP com objects to choose from that can generate graphs from data.
I have added a excel charting enhancement to my hit counter article. This Excel example does not go into depth on how we get the hit count data into the database, rather shows examples of using the data once we have it there.
Set objExcel = CreateObject("Excel.Sheet")
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Conn.Execute("SELECT * FROM COUNTS order by TOTALS")
td_dt = now() 'get todays date
'loop thru the hit counter to fill the excel sheet
Do While not rs.eof
'determine if hits total within this month and today
if datediff("M",RS("last_dt"), td_dt) <> 0 then
months_hits = 0
months_hits = RS("MONTH")
if datediff("d",RS("last_dt"), td_dt) <> 0 then
todays_hits = 0
todays_hits = RS("today")
i = myCnt
'fill cells, each page has 4 columns, name, grand total, month and todays hits
objExcel.ActiveSheet.Cells(i + 1, 1 ) = rs("url")
objExcel.ActiveSheet.Cells(i + 1, 2 ) = rs("totals")
objExcel.ActiveSheet.Cells(i + 1, 3 ) = months_hits
objExcel.ActiveSheet.Cells(i + 1, 4 ) = todays_hits
myCnt = myCnt + 1
set rs = nothing
'now that we have the sheet filled we have to select it and create the chart
objExcel.ActiveSheet.Range("b1:d1").Value = Array("Total","Monthly","Today")
'create a string from b2 to dx where x is the number of rows created(myCnt)
startstr = "b2:"
endstr = "d" & mycnt
objExcel.ActiveSheet.Range(startstr & endstr).Select
'Create the chart
'experiment with the next 2 settings, I have not found good docs as
'to what values correlate to
objExcel.activechart.ChartType = 100
objExcel.activechart.HasTitle = True
'Create a title for the chart
objExcel.activechart.ChartTitle.Text = _
"URL hits, by month, day and grand total"
'select the range of rows
objExcel.Sheets("Sheet1").Range("A1:" & endstr),1
'create the filename we want the picture saved as
filename = server.mappath("/images")
if mid(filename,len(filename),1) <> "\" then
filename = filename & "\"
filename = filename & "temp.gif"
'now export the chart as a gif to be displayed within the page
objExcel.activechart.export filename, "GIF"
<TITLE>Chart of Url Hits</TITLE>
<img src="/images/chart2.gif"" alt="Suits real time charting example">
Here is a sample of a chart produced