在ASP中调用Excel画数据图表 (转)

最新推荐文章于 2021-08-10 13:56:31 发布

转载最新推荐文章于 2021-08-10 13:56:31 发布 · 251 阅读

在ASP中调用Excel画数据图表 (转)[@more@]

在 ASP中调用 excel画数据图表，优势有很多，最突出的是可以输出多种形式的图形（总共有72种）。以下是我写的一个通用ASP方法调用Excel画数据图表。

'将数据图形化输出
'dataArray二维数组
'virtualFilePath输出图像文件名(虚拟路径)
'nType显示类型
Dim initType
Sub ExportPicture(dataArray,virtualFilePath,nType)
Dim excelapp ' As New excel.Application
Dim excelwbk ' As excel.Workbook
Dim excelcht ' As excel.Chart
Dim excelsht 'As excel.Worksheet
Dim idx,idy,ftype,usedData,totalcount,count:count = 1
On Error Resume Next

Set excelapp = Server.Createobject("Excel.Application")
Set excelwbk = excelapp.Workbooks.Add()
Set excelcht = excelwbk.Charts.Add()
Set excelsht = excelwbk.Worksheets.Add()
If UCase(Right(virtualFilePath,4)) = ".JPG" Or UCase(Right(virtualFilePath,4)) = ".JPEG" Then
ftype = "jpg"
Else
ftype = "gif"
End If
initType = nType
For idx=LBound(dataArray,1) To UBound(dataArray,1)
For idy=LBound(dataArray,2) To UBound(dataArray,2)
excelsht.Cells(idx+1,idy+1) = dataArray(idx,idy)
Next
Next

Set usedData = excelsht.usedRange
excelcht.SerieSCOllection.Add usedData

excelcht.HasLegend = True
excelcht.HasTitle = True
'excelcht.ChartTitle.Caption = "部门员工分布图"
excelcht.ApplyCustomType nType
excelcht.Export Server.Mappath(virtualFilePath), ftype
excelsht.Close False
excelwbk.Close False
Set usedData = Nothing
Set excelcht = Nothing
Set excelwbk = Nothing
Set excelapp = Nothing
End Sub
%>
<select name="sel" Onchange="changePict()">
二维柱形图<!--xlColumnClustered
xlColumnStacked
xlColumnStacked100--&gt
三维柱状图<!--xl3DColumnClustered
xl3DColumnStacked
xl3DColumnStacked100--&gt
二维条形图<!--xlBarClustered
xlBarStacked
xlBarStacked100--&gt
三维条状图<!--xl3 dbarClustered
xl3DBarStacked
xl3DBarStacked100--&gt
折线图<!--xlLineStacked
xlLineStacked100
xlLineMarkers
xlLineMarkersStacked
xlLineMarkersStacked100
xlPieOfPie
xlPieExploded
xl3DPieExploded
xlBarOfPie--&gt
曲线图<!--xlXYScatterSmooth
xlXYScatterSmoothNoMarkers
xlXYScatterLines
xlXYScatterLinesNoMarkers--&gt
折线面积图<!--xlAreaStacked
xlAreaStacked100
xl3DAreaStacked
xl3DAreaStacked100
xlDoughnutExploded
xlRadarMarkers
xlRadarFilled
xlSurface
xlSurfaceWireframe
xlSurfaceTopView
xlSurfaceTopViewWireframe
xlBubble
xlBubble3DEffect
xlStockHLC
xlStockOHLC
xlStockVHLC
xlStockVOHLC--&gt
竖向圆柱图<!--xlCylinderColClustered
xlCylinderColStacked
xlCylinderColStacked100--&gt
横向圆柱图<!--xlCylinderBarClustered
xlCylinderBarStacked
xlCylinderBarStacked100
xlCylinderCol
xlConeColClustered
xlConeColStacked
xlConeColStacked100
xlConeBarClustered
xlConeBarStacked
xlConeBarStacked100
xlConeCol
xlPyramidColClustered
xlPyramidColStacked
xlPyramidColStacked100
xlPyramidBarClustered
xlPyramidBarStacked
xlPyramidBarStacked100
xlPyramidCol
xl3DColumn
xlLine
xl3DLine--&gt
饼图<!--xl3DPie--&gt
扇面图<!--xlPie
xlXYScatter
xl3DArea
xlArea--&gt
圆环图<!--xlDoughnut--&gt
雷达图<!--xlRadar--&gt

把以上代码存成一个通用文件，命名为DataToChart.asp，再写一个调用的文件代码如下：

Response.ContentType="text/html;Charset=GB2312;"
Randomize
Dim dbrest,DBConn,photoPath,pidx,count,nnType:nnType=Request.Form("sel")
Set DBConn=Session("DBConn")
Dim dArray()

If Not IsNumeric(nnType) Or IsEmpty(nnType) Or nnType="" Then
nnType=51
End If
Set dbrest = Server.CreateObject("ADODB.Recordset")
dbrest.Open "Select Count(Organization.OrgId) From Org_User Right Join Organization On Org_User.OrgID=Organization.OrgID", DBConn, 1, 3
count = dbrest(0)
dbrest.close
If count<1 Then
count = 1
End If
dbrest.Open "Select OrgName,Count(Org_User.OrgId) From Org_User Right Join Organization On Org_User.OrgID=Organization.OrgID Group by Organization.OrgID,orgName", DBConn, 1, 3
ReDim dArray(1,dbrest.RecordCount-1)
pidx=0
While Not dbrest.EOF
dArray(0,pidx) = dbrest(0) & "(" & FormatPercent(dbrest(1)/count) & ")"
dArray(1,pidx) = dbrest(1)
pidx = pidx + 1
dbrest.MoveNext
Wend
dbrest.Close
Set dbrest=Nothing
photoPath = "./../../Chinese/Working/TempPhoto/HRM_" & Session("UserID") & ".gif"
Call ExportPicture(dArray,photoPath,nnType)
%>