在ASP中怎樣用Excel寫報表?

在ASP中﹐怎樣將Sql Server或者Oracle數據庫的資料轉成Excel圖表(直條圖和折線圖)﹐并在網頁顯示出來?
最好有實例。
---------------------------------------------------------------

http://www.csdn.net/Subject/15/index.shtm
---------------------------------------------------------------

OWC，具体使用可以看OFFICE安装目录下2052里的MSOWC.CHM

柱图

1@ Language=VBScript 

  1   
  2Option Explicit   
  3'Response.Expires = -1   
  4'Response.ContentType = "image/gif"   
  5  
  6'页面级对象   
  7'--------------------   
  8Dim m_cn '链接对象   
  9Dim m_rs '记录集对象   
 10Dim m_cspace 'OWC.ChartSpace object   
 11Dim m_fso '文件系统对象   
 12Dim m_objBinaryFile '二进值文件流对象   
 13  
 14Dim m_sYear '需要查询的年份   
 15  
 16Dim sSQL '执行的SQL 语句   
 17Dim c 'OWC 常量对象   
 18Dim cht '图表临时参考变量   
 19Dim ax '数轴的临时参考变量   
 20Dim fnt 'OWCFont字体的临时参考变量   
 21Dim sFullFileName '输出GIF文件的临时参考路径   
 22  
 23'获得查询年份，缺省为2002   
 24m_sYear = Request.QueryString("year")   
 25if len(m_sYear) = 0 then m_sYear = "2002"   
 26  
 27'创建一个ADO链接和记录集对象   
 28set m_cn = Server.CreateObject("ADODB.Connection")   
 29set m_rs = Server.CreateObject("ADODB.Recordset")   
 30  
 31'链接到Access数据库，使用客户端的游标引擎打开记录集   
 32m_cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & Server.MapPath(".") & "\data.mdb"   
 33sSQL = "SELECT Month([OrderDate]) AS [Month], Invoices.Salesperson, " & _   
 34"Sum(Invoices.ExtendedPrice) AS Sales FROM Invoices " & _   
 35"WHERE Invoices.OrderDate between #1/1/" & m_sYear & "# and #12/31/" & m_sYear & "# " & _   
 36"GROUP BY Month([OrderDate]), Invoices.Salesperson"   
 37m_rs.CursorLocation = 3 'adUseClient   
 38m_rs.Open sSQL, m_cn, 3 'adOpenStatic   
 39  
 40'增加一个带有图例的簇列图表   
 41set m_cspace = server.CreateObject("OWC.Chart")   
 42set cht = m_cspace.Charts.Add()   
 43set c = m_cspace.Constants   
 44  
 45set m_cspace.DataSource = m_rs   
 46  
 47cht.Type = 1   
 48cht.HasLegend = True   
 49  
 50'设置图表的数据源，把销售人添加到序列名称，销售月份添加到分类组，销售金额作为图表中各项取值   
 51  
 52  
 53cht.SetData c.chDimSeriesNames, 0, "Salesperson"   
 54cht.SetData c.chDimCategories, 0, "Month"   
 55cht.SetData c.chDimValues, 0, "Sales"   
 56c.HasPercentage = True   
 57c.HasValue = False   
 58  
 59'增加一个图表标题，并格式化标题   
 60cht.HasTitle = True   
 61cht.Title.Caption = m_sYear & "的销售金额 "   
 62set fnt = cht.Title.Font   
 63fnt.Name = "宋体"   
 64fnt.Size = 10   
 65fnt.Bold = True   
 66  
 67'对分类轴添加标题，并格式化标题   
 68set ax = cht.Axes(c.chAxisPositionBottom)   
 69ax.HasTitle = True   
 70ax.Title.Caption = "月份"   
 71set fnt = ax.Title.Font   
 72fnt.Name = "宋体"   
 73fnt.Size = 8   
 74fnt.Bold = True   
 75  
 76'对数值轴添加标题，并格式化标题   
 77set ax = cht.Axes(c.chAxisPositionleft)   
 78'ax.NumberFormat = "Currency"   
 79ax.HasTitle = True   
 80ax.Title.Caption = "元"   
 81set fnt = ax.Title.Font   
 82fnt.Name = "宋体"   
 83fnt.Size = 8   
 84fnt.Bold = True   
 85fnt.color="red"   
 86  
 87'用一个临时变量名保存当前文件，这个文件名是唯一的   
 88set m_fso = CreateObject("Scripting.FileSystemObject")   
 89sFullFileName = Server.MapPath(".") & "\" & m_fso.GetTempName()   
 90m_cspace.ExportPicture sFullFileName, "GIF", 800, 400   
 91  
 92'使用 On Error Resume Next语句是为了确保我们删除了临时文件，即使一些函数调用失败   
 93on error resume next   
 94  
 95'GIF文件已经输出，我们可以通过COM组件把它的内容发送到客户端   
 96set m_objBinaryFile = server.CreateObject("BinFileWrite.GetFileStream")   
 97  
 98m_objBinaryFile.SendBinFile CStr(sFullFileName),"image/GIF",TRUE,FALSE,TRUE,TRUE,TRUE   
 99'GIF文件已经不需要了可以删除了   
100m_objBinaryFile.DeleteFile CStr(sFullFileName)   
101

饼图

1@ Language=VBScript 

1<html>
2<head>
3<meta content="Microsoft FrontPage 4.0" name="GENERATOR"/>
4<meta content="FrontPage.Editor.Document" name="ProgId"/>
5<title>利用OWC动态生成图表</title>
6</head>
7<body>

set m_cspace = server.CreateObject("OWC.Chart")
set DSC = server.CreateObject("OWC.DataSourceControl")
str="provider=microsoft.jet.oledb.4.0;data source=" &amp; Server.MapPath(".") &amp; "\data.mdb"
'Response.Write (str)
DSC.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=" &amp; Server.MapPath(".") &amp; "\data.mdb"

DSC.RecordsetDefs.AddNew "SELECT Month([OrderDate]) AS [Month], Invoices.Salesperson, " &amp; _
"Sum(Invoices.ExtendedPrice) AS Sales FROM Invoices " &amp; _
"WHERE Invoices.OrderDate between #1/1/2002# and #12/31/2002# " &amp; _
"GROUP BY Month([OrderDate]), Invoices.Salesperson",DSC.Constants.dscCommandText,"ChartData"

'BindChartToDSC m_m_cspace, DSC, "ChartData", "month", "Sales"
set c = m_cspace.Constants

m_cspace.Clear

' 绑定数据源
set m_cspace.DataSource = dsc

m_cspace.DataMember = ChartData

set cht = m_cspace.Charts.Add()
cht.HasLegend = True
cht.Type = 19

'增加一个图表标题，并格式化标题
cht.HasTitle = True
cht.Title.Caption = m_sYear &amp; "的销售金额 "
set fnt = cht.Title.Font
fnt.Name = "宋体"
fnt.Size = 10
fnt.Bold = True

set ser = cht.SeriesCollection.Add()
ser.SetData c.chDimCategories, 0, "Salesperson"
ser.SetData c.chDimValues, 0, "Sales"

set dls = ser.DataLabelsCollection.Add()
dls.HasPercentage = True
dls.HasValue = False

'm_sFilePath = ExportChartToGIF(m_m_cspace)
set m_fso = CreateObject("Scripting.FileSystemObject")
sFullFileName = Server.MapPath(".") &amp; "" &amp; m_fso.GetTempName()
m_cspace.ExportPicture sFullFileName, "GIF", 800, 400

'on error resume next
set m_objBinaryFile = server.CreateObject("BinFileWrite.GetFileStream")
Response.BinaryWrite m_objBinaryFile.GetFileBytes(CStr(sFullFileName))
m_objBinaryFile.SendBinFile CStr(sFullFileName),"image/GIF",TRUE,FALSE,TRUE,TRUE,TRUE
set fso=nothing
set m_m_cspace=nothing
set m_cht=nothing

1  
2
3
4   
5  
6</body>
7</html>

折线图

1@ Language=VBScript

<%
Option Explicit

'页面级对象
'--------------------
Dim m_cn '链接对象
Dim m_rs '记录集对象
Dim m_cspace 'OWC.ChartSpace object
Dim m_fso '文件系统对象
Dim m_objBinaryFile '二进值文件流对象

Dim m_sYear '需要查询的年份

Dim sSQL '执行的SQL 语句
Dim c 'OWC 常量对象
Dim cht '图表临时参考变量
Dim ax '数轴的临时参考变量
Dim fnt 'OWCFont字体的临时参考变量
Dim sFullFileName '输出GIF文件的临时参考路径

'获得查询年份，缺省为2002
m_sYear = Request.QueryString("year")
if len(m_sYear) = 0 then m_sYear = "2002"

'创建一个ADO链接和记录集对象
set m_cn = Server.CreateObject("ADODB.Connection")
set m_rs = Server.CreateObject("ADODB.Recordset")

'链接到Access数据库，使用客户端的游标引擎打开记录集
m_cn.Open "provider=microsoft.jet.oledb.4.0;data source=" & Server.MapPath(".") & "\data.mdb"
sSQL = "SELECT Month([OrderDate]) AS [Month], Invoices.Salesperson, " & _
"Sum(Invoices.ExtendedPrice) AS Sales FROM Invoices " & _
"WHERE Invoices.OrderDate between #1/1/" & m_sYear & "# and #12/31/" & m_sYear & "# " & _
"GROUP BY Month([OrderDate]), Invoices.Salesperson"
m_rs.CursorLocation = 3 'adUseClient
m_rs.Open sSQL, m_cn, 3 'adOpenStatic

'增加一个带有图例的簇列图表
set m_cspace = server.CreateObject("OWC.Chart")
set m_cspace.DataSource = m_rs
set cht = m_cspace.Charts.Add()
set c = m_cspace.Constants

cht.HasLegend = True

cht.Type =c.chChartTypeLineMarkers
'设置图表的数据源，把销售人添加到序列名称，销售月份添加到分类组，销售金额作为图表中各项取值

cht.SetData c.chDimSeriesNames, 0, "Salesperson"
cht.SetData c.chDimCategories, 0, "Month"
cht.SetData c.chDimValues, 0, "Sales"

cht.SeriesCollection(4).Interior.Color = "blue" '改变第四条线的颜色为蓝色
Cht.SeriesCollection(4).Line.Color = "blue"

'增加一个图表标题，并格式化标题
cht.HasTitle = True
cht.Title.Caption = m_sYear & "的销售金额 "
set fnt = cht.Title.Font
fnt.Name = "宋体"
fnt.Size = 10
fnt.Bold = True

'对分类轴添加标题，并格式化标题
set ax = cht.Axes(c.chAxisPositionBottom)
ax.HasTitle = True
ax.Title.Caption = "月份"
set fnt = ax.Title.Font
fnt.Name = "宋体"
fnt.Size = 8
fnt.Bold = True

'对数值轴添加标题，并格式化标题
set ax = cht.Axes(c.chAxisPositionLeft)
ax.NumberFormat = "Currency"
ax.HasTitle = True
ax

Published At
Categories with Web编程
comments powered by Disqus