|
在 Web 頁面中使用圖表（chart）表現數據
在 Web 編程中經常需要做的一件事情就是把從數據庫中查出的數據（數字）使用圖表（chart）的形式在頁面中表現出來。下面我們簡單總結幾種常見的做法。
1. 如果圖表的樣式只需要柱形圖（bar）就可以的話，有一種非常簡單，偷懶的方法，即使用某些 tag 的 width 屬性來表現就可以。舉例如下：
<IMG HEIGHT=5 WIDTH=<%= 數值 %> SRC=http://cfan.net.cn/info/"小方塊.gif">
用這種思路，要是不嫌難看的話，你干脆用 for 循環控制 * 號的顯示個數也未嘗不可。;-)
如果想比較美觀的話，可以把 CSS 設計的好一些，再和 DHTML 結合。
這種方法的一個完整的例子見：
http://www.microsoft.com/workshop/database/datavis/datavis.asp
2. 一些老兄喜歡直接把圖片放在數據庫中，那我們看看怎么把它們調出來。
（如果這些圖片正好是圖表的話，我就不算離題。;-)）
IIS 的在線幫助中有這么個例子：
http://localhost/IIsSamples/SDK/asp/docs/CodeBrws.asp?source=/IIsSamples/SDK/asp/Database/Blob_VBScript.asp
其核心代碼：
<%
' 聲明回傳的是 Gif 文件，不是平常的 HTML
Response.Buffer = TRUE
Response.ContentType = "image/gif"
' 連數據庫
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=LocalServer;UID=sa;PWD=;DATABASE=pubs"
' 查出存好的圖片
Set oRs = oConn.Execute("SELECT logo FROM pub_info WHERE pub_id='0736'")
' 取值要顯得專業些 ;-)
PicSize = oRs("logo").ActualSize
Pic = oRs("logo").GetChunk(PicSize)
' 再次強調回傳的是 gif 圖片，view source 是什么都看不到的
Response.BinaryWrite Pic
Response.End
%>
好，仔細看注釋的老兄（我可沒這好習慣;-)）會問：這支程序 mypic.asp 在瀏覽器中最后的效果
相當于 http://host/foo/mypic.gif，我想要有文字怎么辦？
很簡單，寫個 web page 中間加上 <img src=mypic.asp> 不就完了。 ;-)
3. 還有些老兄更甚，這些大俠的機器多半是 8 CPU 的 P III，他們使用 server-side 軟件，
比如 excel，現做一個 chart 圖片，然后以 gif 格式傳給瀏覽器。多見于 CGI 高手。;-)
我們來看一個這樣的 cool demo。
核心代碼：
<%
Set excel = GetObject("","Excel.Application")
If Err.Number <> 0 Then
Response.Write("Could not create Excel document. " + Err.Description+"")
Err.Clear
End If
excel.DisplayAlerts = False
Set workbooks = excel.Workbooks
Set wb = workbooks.Add
Set sheets = wb.Sheets
Set wsTotal = sheets.Add( ,,,-4167)
wsTotal.Name = "Total_Expenses"
Set range = wsTotal.Range("B1")
range.FormulaR1C1 = "1"
Set range = wsTotal.Range("C1")
range.FormulaR1C1 = "2"
Set range = wsTotal.Range("D1")
range.FormulaR1C1 = "3"
wsTotal.Activate
wsTotal.Select
Set range = wsTotal.Range("B1:D1")
excel.Charts.Add
excel.ActiveChart.ChartType = 51
excel.ActiveChart.SetSourceData range,2
excel.ActiveChart.Export "d:\test\exceltest"+".gif","GIF"
Response.Write "<img src=http://cfan.net.cn/info/d:/test/exceltest.gif>"
%>
真正的懶人在寫這段代碼時還利用 excel 的 vba（:-P），絕對代碼快槍手，可是運行效率----呸！;-)
4. 好了，該看一看專業運動員的做法了----使用 chart control。
哪種控件更好大家見仁見智，（比如有些老兄喜歡 Java Applets ;-) 還有些老兄喜歡自己用 C/C++ 開發）為簡化起見，這里我推薦微軟（;-)）的----Office 2000 Web Component。;-)
在前面一文中我介紹過控件與數據結合的幾種方式，我們來一一分析用 Excel 2000 的 chart control 如何實現。
A. 逐行賦值法
Excel 2000 chart control 有兩種賦值方法：數組，字符串。
數組法：
代碼示例：
----------------------------------
<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:350"></object>
<script language=vbs>
Sub Window_OnLoad()
Dim categories(3), values(3)
' 4 個分類
categories(0) = "White"
categories(1) = "Black"
categories(2) = "Asian"
categories(3) = "Latino"
' 準備活動 ;-)
ChartSpace1.Clear
ChartSpace1.Charts.Add
Set c = ChartSpace1.Constants
' 添加三個系列的值
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
' 錦上添花 ;-)
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"
' 設置
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
values(0) = 0.2 ' The White value.
values(1) = 0.06 ' The Black value.
values(2) = 0.17 ' The Asian value.
values(3) = 0.13 ' The Latino value.
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values
' Series two contains election data for Clinton.
' Update the values array, then set the chart data.
values(0) = 0.38 ' The White value.
values(1) = 0.82 ' The Black value.
values(2) = 0.28 ' The Asian value.
values(3) = 0.62 ' The Latino value.
ChartSpace1.Charts(0).SeriesCollection(1).Caption = "Clinton"
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, values
' Series two contains election data for Bush.
' Update the values array, and then set the chart data.
values(0) = 0.42 ' The White value.
values(1) = 0.12 ' The Black value.
values(2) = 0.55 ' The Asian value.
values(3) = 0.25 ' The Latino value.
ChartSpace1.Charts(0).SeriesCollection(2).Caption = "Bush"
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, c.chDataLiteral, values
' Make the chart legend visible, format the left value axis as percentage,
' and specify that value gridlines are at 10% intervals.
ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>
字符串法：
代碼示例：
--------------------------
<script language=vbs>
Sub Window_OnLoad()
Dim categories, values
' 原來的注釋很無聊，被我刪掉了 ;-)
ChartSpace1.Clear
ChartSpace1.Charts.Add
Set c = ChartSpace1.Constants
' 以 tab 為分隔符的字符串拼湊
categories = "White" & Chr(9) & "Black" & Chr(9) & "Asian" & Chr(9) & "Latino"
' Add three series to the chart.
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
' Series one contains election data for Perot.
' Set the series caption (the text that appears in the legend).
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"
' Set the categories for the first series (this collection is zero-based).
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, c.chDataLiteral, categories
' 以 tab 為分隔符的字符串拼湊
values = "0.2" & Chr(9) & "0.06" & Chr(9) & "0.17" & Chr(9) & "0.13"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, c.chDataLiteral, values
' Series two contains election data for Clinton.
' Update the values string, and then set the chart data.
values = "0.38" & Chr(9) & "0.82" & Chr(9) & "0.28" & Chr(9) & "0.62"
ChartSpace1.Charts(0).SeriesCollection(1).Caption = "Clinton"
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, values
' Series two contains election data for Bush.
' Update the values string, and then set the chart data.
values = "0.42" & Chr(9) & "0.12" & Chr(9) & "0.55" & Chr(9) & "0.25"
ChartSpace1.Charts(0).SeriesCollection(2).Caption = "Bush"
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, c.chDataLiteral, categories
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, c.chDataLiteral, values
' Make the chart legend visible, format the left value axis as percentage,
' and specify that value gridlines are at 10% intervals.
ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>
-------------------------
無論是數組法還是字符串法，你都可以在 client-side 逐行插入 <%=value%>。
對于字符串法，你也可以在 server-side 就拼好一個字串，然后直接傳過來。
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, <% =stringValues%>
（好象要加引號哦。"<% =stringValues%>"，意思到了就中）
B. client-side recordset 法
Excel 2000 chart 這么 cool 的 control 當然支持直接的 recordset 綁定。
代碼示例：
-------------------------
<html>
<body>
第一步：創建 Chart 和 ADO Connection object
<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:480"></object>
<object id=ADOConnection1 classid=CLSID:00000514-0000-0010-8000-00AA006D2EA4></object>
<script language=vbs>
Sub Window_OnLoad()
Dim rs, Categories, Values
' 找個英文 Access 97 里的 nwind.mdb 試試
Categories = ""
Values = ""
ADOConnection1.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=c:\nwind.mdb"
Set rs = ADOConnection1.Execute("SELECT * FROM [Category Sales for 1995]")
rs.MoveFirst
While Not rs.EOF
Categories = Categories & rs.Fields(0).Value & Chr(9)
Values = Values & rs.Fields(1).Value & Chr(9)
rs.MoveNext
Wend
rs.Close
ADOConnection1.Close
' 多拼了個 TAB，去了它
Categories = Left(Categories, Len(Categories) - 1)
Values = Left(Values, Len(Values) - 1)
' 很容易看懂吧
ChartSpace1.Clear
ChartSpace1.Charts.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Sales"
ChartSpace1.Charts(0).SeriesCollection(0).SetData ChartSpace1.Constants.chDimCategories,
ChartSpace1.Constants.chDataLiteral, Categories
ChartSpace1.Charts(0).SeriesCollection(0).SetData ChartSpace1.Constants.chDimValues,
ChartSpace1.Constants.chDataLiteral, Values
'-- As a final step, we turn this into a bar chart (instead of a column chart), and
'-- format the axis as US $.
ChartSpace1.Charts(0).Type = ChartSpace1.Constants.chChartTypeBarClustered
ChartSpace1.Charts(0).Axes(ChartSpace1.Constants.chAxisPositionBottom).NumberFormat = "$#,##0"
End Sub
</script>
</body>
</html>
-----------------------------------
C. 目前 Excel 2000 的 chart control 還沒有用于 VI6 的 Design Time Control 版，恨恨！
用ASP生成Chart (二維餅圖)
這是使用ActiveX Controls 的
<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<OBJECT classid="clsid:3A2B370C-BA0A-11D1-B137-0000F8753F5D"
id=MSChart1 style="LEFT: 0px; TOP: 0px" VIEWASTEXT height=300 width=300></OBJECT>
<SCRIPT LANGUAGE="VBScript">
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objconn.ConnectionString = "DSN=AdvWorks"
objConn.Open
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn
objRS.CursorLocation = 3
objRS.Open("select OrderDetailID,UnitPrice from Order_Details")
i=1
Response.Write "Const num = " & objrs.RecordCount & vbCr
Response.Write "Dim ID(" & objrs.RecordCount & ")" & vbCr
Response.Write "Dim Details(" & objrs.RecordCount & ")" & vbCr
Do While Not objRS.EOF
Response.Write("ID(" & i & ")=""" & objRS(0) & """" & Chr(13))
Response.Write("Details(" & i & ")=""" & objRS(1) & """" & Chr(13))
i=i+1
objRS.MoveNext
Loop
%>
MSChart1.TitleText = "Example"
MSChart1.RowCount = 1
MSChart1.ColumnCount = num
for i = 1 to num
MSChart1.Column = i
MSChart1.ColumnLabel = ID(i)
next
MSChart1.chartType = 14 '14是二維餅圖,擬合曲線我還不知道怎么畫
MSChart1.ShowLegend = True
MSChart1.ChartData = Details
</SCRIPT>
</BODY>
</HTML>
用ASP生成Chart
<SCRIPT LANGUAGE="VBScript" RUNAT="SERVER">
function makechart(title, numarray, labelarray, color, bgcolor, bordersize, maxheight, maxwidth, addvalues)
'Function makechart version 3
'Jason Borovoy
'title: Chart Title
'numarray: An array of values for the chart
'labelarray: An array of labels coresponding to the values must me present
'color If null uses different colors for bars if not null all bars color you specify
'bgcolor Background color.
'bordersize: border size or 0 for no border.
'maxheight: maximum height for chart not including labels
'maxwidth: width of each column
'addvalues: true or false depending if you want the actual values shown on the chart
'when you call the function use : response.write makechart(parameters)
'actually returnstring would be a better name
dim tablestring
'max value is maximum table value
dim max
'maxlength maximum length of labels
dim maxlength
dim tempnumarray
dim templabelarray
dim heightarray
Dim colorarray
'value to multiplie chart values by to get relitive size
Dim multiplier
'if data valid
if maxheight > 0 and maxwidth > 0 and ubound(labelarray) = ubound(numarray) then
'colorarray: color of each bars if more bars then colors loop through
'if you don't like my choices change them, add them, delete them.
colorarray = array("red","blue","yellow","navy","orange","purple","green")
templabelarray = labelarray
tempnumarray = numarray
heightarray = array()
max = 0
maxlength = 0
tablestring = "<TABLE bgcolor='" & bgcolor & "' border='" & bordersize & "'>" & _
"<tr><td><TABLE border='0' cellspacing='1' cellpadding='0'>" & vbCrLf
'get maximum value
for each stuff in tempnumarray
if stuff > max then max = stuff end if
next
'calculate multiplier
multiplier = maxheight/max
'populate array
for counter = 0 to ubound(tempnumarray)
if tempnumarray(counter) = max then
redim preserve heightarray(counter)
heightarray(counter) = maxheight
else
redim preserve heightarray(counter)
heightarray(counter) = tempnumarray(counter) * multiplier
end if
next
'set title
tablestring = tablestring & "<TR><TH colspan='" & ubound(tempnumarray)+1 & "'>" & _
"<FONT FACE='Verdana, Arial, Helvetica' SIZE='1'><U>" & title & "</TH></TR>" & _
vbCrLf & "<TR>" & vbCrLf
'loop through values
for counter = 0 to ubound(tempnumarray)
tablestring = tablestring & vbTab & "<TD valign='bottom' align='center' >" & _
"<FONT FACE='Verdana, Arial, Helvetica' SIZE='1'>" & _
"<table border='0' cellpadding='0' width='" & maxwidth & "'><tr>" & _
"<tr><td valign='bottom' bgcolor='"
if not isNUll(color) then
'if color present use that color for bars
tablestring = tablestring & color
else
'if not loop through colorarray
tablestring = tablestring & colorarray(counter mod (ubound(colorarray)+1))
end if
tablestring = tablestring & "' height='" & _
round(heightarray(counter),2) & "'><img src='http://cfan.net.cn/info/chart.gif' width='1' height='1'>" & _
"</td></tr>"
if addvalues then
'print actual values
tablestring = tablestring & "<BR>" & tempnumarray(counter)
end if
tablestring = tablestring & "</TD>" & vbCrLf
next
tablestring = tablestring & "</TR>" & vbCrLf
'calculate max lenght of labels
for each stuff in labelarray
if len(stuff) >= maxlength then maxlength = len(stuff)
next
'print labels and set each to maxlength
for each stuff in labelarray
tablestring = tablestring & vbTab & "<TD align='center'><" & _
"FONT FACE='Verdana, Arial, Helvetica' SIZE='1'><B> "
for count = 0 to round((maxlength - len(stuff))/2)
tablestring = tablestring & " "
next
if maxlength mod 2 <> 0 then tablestring = tablestring & " "
tablestring = tablestring & stuff
for count = 0 to round((maxlength - len(stuff))/2)
tablestring = tablestring & " "
next
tablestring = tablestring & " </TD>" & vbCrLf
next
tablestring = tablestring & "</TABLE></td></tr>" & vbCrLf
makechart = tablestring
else
Response.Write "Error Function Makechart: maxwidth and maxlength have to be greater " & _
" then 0 or number of labels not equal to number of values"
end if
end function
dim stuff
dim labelstuff
' Demo 1
stuff = Array(5,30)
labelstuff = Array("北京", "廣州")
Response.Write makechart("Demo 1", stuff, labelstuff, null, "gold",10, 50,40,true)
</SCRIPT>
|
