IDC资讯中心
在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
