怎么样在ASP中调用Excel画数据图表？

在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()">

<Option value="51">二维柱形图</Option><!--xlColumnClustered

<Option value="52">xlColumnStacked</Option>

<Option value="53">xlColumnStacked100</Option>-->

<Option value="54">三维柱状图</Option><!--xl3DColumnClustered

<Option value="55">xl3DColumnStacked</Option>

<Option value="56">xl3DColumnStacked100</Option>-->

<Option value="57">二维条形图</Option><!--xlBarClustered

<Option value="58">xlBarStacked</Option>

<Option value="59">xlBarStacked100</Option>-->

<Option value="60">三维条状图</Option><!--xl3DBarClustered

<Option value="61">xl3DBarStacked</Option>

<Option value="62">xl3DBarStacked100</Option>-->

<Option value="63">折线图</Option><!--xlLineStacked

<Option value="64">xlLineStacked100</Option>

<Option value="65">xlLineMarkers</Option>

<Option value="66">xlLineMarkersStacked</Option>

<Option value="67">xlLineMarkersStacked100</Option>

<Option value="68">xlPieOfPie</Option>

<Option value="69">xlPieExploded</Option>

<Option value="70">xl3DPieExploded</Option>

<Option value="71">xlBarOfPie</Option>-->

<Option value="72">曲线图</Option><!--xlXYScatterSmooth

<Option value="73">xlXYScatterSmoothNoMarkers</Option>

<Option value="74">xlXYScatterLines</Option>

<Option value="75">xlXYScatterLinesNoMarkers</Option>-->

<Option value="76">折线面积图</Option><!--xlAreaStacked

<Option value="77">xlAreaStacked100</Option>

<Option value="78">xl3DAreaStacked</Option>

<Option value="79">xl3DAreaStacked100</Option>

<Option value="80">xlDoughnutExploded</Option>

<Option value="81">xlRadarMarkers</Option>

<Option value="82">xlRadarFilled</Option>

<Option value="83">xlSurface</Option>

<Option value="84">xlSurfaceWireframe</Option>

<Option value="85">xlSurfaceTopView</Option>

<Option value="86">xlSurfaceTopViewWireframe</Option>

<Option value="15">xlBubble</Option>

<Option value="87">xlBubble3DEffect</Option>

<Option value="88">xlStockHLC</Option>

<Option value="89">xlStockOHLC</Option>

<Option value="90">xlStockVHLC</Option>

<Option value="91">xlStockVOHLC</Option>-->

<Option value="92">竖向圆柱图</Option><!--xlCylinderColClustered

<Option value="93">xlCylinderColStacked</Option>

<Option value="94">xlCylinderColStacked100</Option>-->

<Option value="95">横向圆柱图</Option><!--xlCylinderBarClustered

<Option value="96">xlCylinderBarStacked</Option>

<Option value="97">xlCylinderBarStacked100</Option>

<Option value="98">xlCylinderCol</Option>

<Option value="99">xlConeColClustered</Option>

<Option value="100">xlConeColStacked</Option>

<Option value="101">xlConeColStacked100</Option>

<Option value="102">xlConeBarClustered</Option>

<Option value="103">xlConeBarStacked</Option>

<Option value="104">xlConeBarStacked100</Option>

<Option value="105">xlConeCol</Option>

<Option value="106">xlPyramidColClustered</Option>

<Option value="107">xlPyramidColStacked</Option>

<Option value="108">xlPyramidColStacked100</Option>

<Option value="109">xlPyramidBarClustered</Option>

<Option value="110">xlPyramidBarStacked</Option>

<Option value="111">xlPyramidBarStacked100</Option>

<Option value="112">xlPyramidCol</Option>

<Option value="-4100">xl3DColumn</Option>

<Option value="4">xlLine</Option>

<Option value="-4101">xl3DLine</Option>-->

<Option value="-4102">饼图</Option><!--xl3DPie-->

<Option value="5">扇面图</Option><!--xlPie

<Option value="-4169">xlXYScatter</Option>

<Option value="-4098">xl3DArea</Option>

<Option value="1">xlArea</Option>-->

<Option value="-4120">圆环图</Option><!--xlDoughnut-->

<Option value="-4151">雷达图</Option><!--xlRadar-->

</Select>

<Script language=javascript>

function initMenu(formobj)

{

var nType="<%=initType%>";

var i;

for(i=0;i<formobj.sel.options.length;i++)

{

if(formobj.sel.options[i].value==nType)

{

formobj.sel.options[i].selected=true;

break;

}

}

}

</Script>

把以上代码存成一个通用文件，命名为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)

%>

<html>

<head><title>部门员工分布图</title></head>

<body>

<Table align=center>

<form name="form1" method=post>

<Tr>

<Td>

<!--#include file="DataToChart.asp"-->

</Td>

</Tr>

<Tr>

<Td align=center>

<img src="<%=photoPath%>?abc=<%=rnd()%>" border=0>

</Td>

</Tr>

</form>

</Table>

<Script language=javascript>

function changePict()

{

document.form1.action="./../../Chinese/HRM/Dept_Chart.asp?sel=1&"+Date();

document.form1.submit();

}

initMenu(document.form1);

</Script>

</body>

</html>

以上是调用Excel画数据图表的通用方法，各位有好的建议请发邮件给我：zlyperson@163.net

本文地址：http://www.45fan.com/a/question/67359.html