在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>
<Option value="54">三维柱状图</Option>
<Option value="57">二维条形图</Option>
<Option value="60">三维条状图</Option>
<Option value="63">折线图</Option>
<Option value="72">曲线图</Option>
<Option value="76">折线面积图</Option>
<Option value="92">竖向圆柱图</Option>
<Option value="95">横向圆柱图</Option>
<Option value="-4102">饼图</Option>
<Option value="5">扇面图</Option>
<Option value="-4120">圆环图</Option>
<Option value="-4151">雷达图</Option>
</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>

</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

没有评论

在ASP中调用Excel画数据图表

文章TOP15