在 Web 页面中使用图表（chart）表现数据

在 Web 页面中使用图表（chart）表现数据

---

在 Web 编程中经常需要做的一件事情就是把从数据库中查出的数据（数字）使用图表（chart）的形式在页面中表现出来。下面我们简单总结几种常见的做法。

1. 如果图表的样式只需要柱形图（bar）就可以的话，有一种非常简单，偷懒的方法，即使用某些 tag 的 width 属性来表现就可以。举例如下：

<IMG HEIGHT=5 WIDTH=<%= 数值 %> SRC="小方块.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=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='chart.gif' width='1' height='1'>" & _
"</td></tr></table>"
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></table>" & 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>