|
在 Web 頁(yè)面中使用圖表（chart）表現(xiàn)數(shù)據(jù)
在 Web 編程中經(jīng)常需要做的一件事情就是把從數(shù)據(jù)庫(kù)中查出的數(shù)據(jù)（數(shù)字）使用圖表（chart）的形式在頁(yè)面中表現(xiàn)出來(lái)。下面我們簡(jiǎn)單總結(jié)幾種常見(jiàn)的做法。
1. 如果圖表的樣式只需要柱形圖（bar）就可以的話，有一種非常簡(jiǎn)單，偷懶的方法，即使用某些 tag 的 width 屬性來(lái)表現(xiàn)就可以。舉例如下：
<IMG HEIGHT=5 WIDTH=<%= 數(shù)值 %> SRC=http://cfan.net.cn/info/"小方塊.gif">
用這種思路，要是不嫌難看的話，你干脆用 for 循環(huán)控制 * 號(hào)的顯示個(gè)數(shù)也未嘗不可。;-)
如果想比較美觀的話，可以把 CSS 設(shè)計(jì)的好一些，再和 DHTML 結(jié)合。
這種方法的一個(gè)完整的例子見(jiàn)：
http://www.microsoft.com/workshop/database/datavis/datavis.asp
2. 一些老兄喜歡直接把圖片放在數(shù)據(jù)庫(kù)中，那我們看看怎么把它們調(diào)出來(lái)。
（如果這些圖片正好是圖表的話，我就不算離題。;-)）
IIS 的在線幫助中有這么個(gè)例子：
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"
' 連數(shù)據(jù)庫(kù)
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'")
' 取值要顯得專業(yè)些 ;-)
PicSize = oRs("logo").ActualSize
Pic = oRs("logo").GetChunk(PicSize)
' 再次強(qiáng)調(diào)回傳的是 gif 圖片，view source 是什么都看不到的
Response.BinaryWrite Pic
Response.End
%>
好，仔細(xì)看注釋的老兄（我可沒(méi)這好習(xí)慣;-)）會(huì)問(wèn)：這支程序 mypic.asp 在瀏覽器中最后的效果
相當(dāng)于 http://host/foo/mypic.gif，我想要有文字怎么辦？
很簡(jiǎn)單，寫個(gè) web page 中間加上 <img src=mypic.asp> 不就完了。 ;-)
3. 還有些老兄更甚，這些大俠的機(jī)器多半是 8 CPU 的 P III，他們使用 server-side 軟件，
比如 excel，現(xiàn)做一個(gè) chart 圖片，然后以 gif 格式傳給瀏覽器。多見(jiàn)于 CGI 高手。;-)
我們來(lái)看一個(gè)這樣的 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>"
%>
真正的懶人在寫這段代碼時(shí)還利用 excel 的 vba（:-P），絕對(duì)代碼快槍手，可是運(yùn)行效率----呸！;-)
4. 好了，該看一看專業(yè)運(yùn)動(dòng)員的做法了----使用 chart control。
哪種控件更好大家見(jiàn)仁見(jiàn)智，（比如有些老兄喜歡 Java Applets ;-) 還有些老兄喜歡自己用 C/C++ 開(kāi)發(fā)）為簡(jiǎn)化起見(jiàn)，這里我推薦微軟（;-)）的----Office 2000 Web Component。;-)
在前面一文中我介紹過(guò)控件與數(shù)據(jù)結(jié)合的幾種方式，我們來(lái)一一分析用 Excel 2000 的 chart control 如何實(shí)現(xiàn)。
A. 逐行賦值法
Excel 2000 chart control 有兩種賦值方法：數(shù)組，字符串。
數(shù)組法：
代碼示例：
----------------------------------
<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 個(gè)分類
categories(0) = "White"
categories(1) = "Black"
categories(2) = "Asian"
categories(3) = "Latino"
' 準(zhǔn)備活動(dòng) ;-)
ChartSpace1.Clear
ChartSpace1.Charts.Add
Set c = ChartSpace1.Constants
' 添加三個(gè)系列的值
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
' 錦上添花 ;-)
ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Perot"
' 設(shè)置
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
' 原來(lái)的注釋很無(wú)聊，被我刪掉了 ;-)
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>
-------------------------
無(wú)論是數(shù)組法還是字符串法，你都可以在 client-side 逐行插入 <%=value%>。
對(duì)于字符串法，你也可以在 server-side 就拼好一個(gè)字串，然后直接傳過(guò)來(lái)。
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, c.chDataLiteral, <% =stringValues%>
（好象要加引號(hào)哦。"<% =stringValues%>"，意思到了就中）
B. client-side recordset 法
Excel 2000 chart 這么 cool 的 control 當(dāng)然支持直接的 recordset 綁定。
代碼示例：
-------------------------
<html>
<body>
第一步：創(chuàng)建 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
' 找個(gè)英文 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
' 多拼了個(gè) 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 還沒(méi)有用于 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>
|
