dcsimg

Use Excel to Create gif's of Charted Database Information

By ServerWatch Staff (Send Email)
Posted May 24, 1999


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")

Conn.Open("COUNTER")

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
   else
        months_hits = RS("MONTH")
    end if

   if datediff("d",RS("last_dt"), td_dt) <> 0 then
      todays_hits = 0
   else
      todays_hits = RS("today")
   end if

    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
    rs.movenext
loop
set rs = nothing

'now that we have the sheet filled we have to select it and create the chart

'Set the headings
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
objExcel.Charts.Add
'experiment with the next 2 settings, I have not found good docs as
'to what values correlate to

objExcel.activechart.ChartType = 100
objExcel.activechart.BarShape =3
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.activechart.SetSourceData  _
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 & "\"
end if
filename = filename & "temp.gif"

'now export the chart as a gif to be displayed within the page
objExcel.activechart.export    filename,   "GIF"
%>
<HTML>
<HEAD>
<TITLE>Chart of  Url Hits</TITLE>
</HEAD>

<BODY>
<img src="/images/chart2.gif"" alt="Suits real time charting example">
</BODY>
</HTML>


Here is a sample of a chart produced

Page 1 of 1


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.


 

 


Thanks for your registration, follow us on our social networks to keep up-to-date