利用ASP製作EXECL報表方法

最後更新：2017-02-28 來源：互聯網

上載者：User

創建阿里雲帳戶，並獲得超過 40 款產品的免費試用版；而企業帳戶則可以享有總值 $1200 的免費試用版。立即註冊！

很多時候我們需要把表格形式的資料轉換成EXECL的形式呈現在使用者面前，其中有好幾個方法可以做到一點，我將介紹一種利用ASP完成的方法，該方法允許伺服器動態地建立EXECL報表而且不用佔用任何伺服器空間。該方法還允許多個使用者同時收到該資料。但是該方法至少需要EXECL 97的支援。
廢話少說，要完成這個工作最重要的是要告訴瀏覽器HTTP頭，就用如下代碼：

<%
Response.ContentType = "application/vnd.ms-excel"
%>

下面來看一個例子，假設現在有如下形式的資料：
flavor qty_baked qty_eaten qty_sold price
Boston 24 2 10 0.5
Jelly 24 1 12 0.5
Strawberry 36 1 15 0.5
Chocolate 24 2 6 0.75
Maple 12 1 6 0.75

客戶要求用EXECL的形式表現出來，並且希望其中能加上其他一些計算匯總

用如下代碼：
……
<%
Response.ContentType = "application/vnd.ms-excel"

set conntemp=server.createobject("adodb.connection")
cnpath="DBQ=" & server.mappath("/stevesmith/data/timesheet.mdb")
conntemp.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & cnpath
set RS=conntemp.execute("select * from donut")
%>
<TABLE BORDER=1>
<TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through Fields Names and print out the Field Names
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
j = 2 'row counter
For i = 0 to RS.Fields.Count - 1
%>
<TD><B><% = RS(i).Name %></B></TD>
<% Next %>
<TD><B>On Hand (calculated)</B></TD>
<TD><B>Gross (calculated)</B></TD>
</TR>
<%
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Loop through rows, displaying each field
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Do While Not RS.EOF
%>
<TR>
<% For i = 0 to RS.Fields.Count - 1
%>
<TD VALIGN=TOP><% = RS(i) %></TD>
<% Next %>
<TD>=b<%=j%>-c<%=j%>-d<%=j%></TD>
<TD>=d<%=j%>*e<%=j%></TD>
</TR>
<%
RS.MoveNext
j = j + 1
Loop
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
' % Make sure to close the Result Set and the Connection object
' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
RS.Close
%>
<TR BGCOLOR=RED>
<TD>Totals</TD>
<TD>=SUM(B2:B6)</TD>
<TD>=SUM(C2:C6)</TD>
<TD>=SUM(D2:D6)</TD>
<TD>n/a</TD>
<TD>=SUM(F2:F6)</TD>
<TD>=SUM(G2:G6)</TD>
</TABLE>
……
這樣我們就實現了目的，使用者可以在瀏覽器視窗就開啟它進行簡單操作，也可以儲存到硬碟上進行其他動作。我還將介紹一種利用filesystemobject操作的方法。請稍候。：）廢話少說，請看代碼：
runquery.asp

<%@ LANGUAGE="VBSCRIPT" %>
<%
'DSNless connection to Access Database
strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testDB.mdb")
%>
 請自己COPY這個檔案
<%
server.scripttimeout=1000
Response.Buffer = True

if(Request.Form("ReturnAS") = "Content") then
Response.ContentType = "application/msexcel"
end if
Response.Expires = 0

dim oConn
dim oRS
dim strSQL
dim strFile

Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
strSQL = BuildSQL()

oRS.Open strSQL, strDSNPath, adOpenForwardOnly, adLockReadOnly, adCmdText
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
<title>Excel Export Demo</title>
</head>
<body>
<%
if(Request.Form("ReturnAS") = "CSV") then
CreateCSVFile()
else if(Request.Form("ReturnAS") = "Excel") then
CreateXlsFile()
else if(Request.Form("ReturnAS") = "HTML") then
GenHTML()
else if(Request.Form("ReturnAS") = "Content") then
GenHTML()
end if
end if
end if
end if

Set oRS = Nothing
Set oConn = Nothing
Response.Flush
%>
</body>
</html>
<SCRIPT LANGUAGE=vbscript RUNAT=Server>
Function BuildSQL()
dim strSQL
dim strTemp

strTemp = ""
strSQL = "select year, region, sales_amt from sales"

if(Request.Form("Year") <> "ALL") then
strTemp = " where Year = "
strTemp = strTemp & Request.Form("Year")
end if

if(Request.Form("Region") <> "ALL") then
if(Len(strTemp) > 0) then
strTemp = strTemp & " and Region = "
else
strTemp = strSTL & " where Region = "
end if
strTemp = strTemp & "'"
strTemp = strTemp & Request.Form("Region")
strTemp = strTemp & "'"
end if

BuildSQL = strSQL & strTemp
End Function

Function GenFileName()
dim fname

fname = "File"
systime=now()
fname= fname & cstr(year(systime)) & cstr(month(systime)) & cstr(day(systime))
fname= fname & cstr(hour(systime)) & cstr(minute(systime)) & cstr(second(systime))
GenFileName = fname
End Function

Function GenHTML()
Response.Write("<DIV ALIGN=center><FONT SIZE=+1>Sales Reporting</FONT></DIV>")
Response.Write("<TABLE WIDTH=100% BORDER=1 CELLSPACING=1 CELLPADDING=1>")
Response.Write("<TR>")
Response.Write(" <TD>Year</TD>")
Response.Write(" <TD>Region</TD>")
Response.Write(" <TD>Sales</TD>")
Response.Write("</TR>")
if(oRS.BOF = True and oRS.EOF = True) then
Response.Write("Database Empty")
else
oRS.MoveFirst
Do While Not oRS.EOF
Response.Write("<TR>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Year").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Region").Value)
Response.Write("</TD>")
Response.Write("<TD>")
Response.Write(oRS.Fields("Sales_Amt").Value)
Response.Write("</TD>")
Response.Write("</TR>")
oRS.MoveNext
Loop
Response.Write("</TABLE>")
End if
End Function

Function CreateCSVFile()

strFile = GenFileName()
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(server.MapPath(".") & "\" & strFile & ".csv",True)
If Not oRS.EOF Then
strtext = chr(34) & "Year" & chr(34) & ","
strtext = strtext & chr(34) & "Region" & chr(34) & ","
strtext = strtext & chr(34) & "Sales" & chr(34) & ","
a.WriteLine(strtext)
Do Until oRS.EOF
For i = 0 To oRS.fields.Count-1
strtext = chr(34) & oRS.fields(i) & chr(34) & ","
a.Write(strtext)
Next
a.Writeline()
oRS.MoveNext
Loop
End If
a.Close
Set fs=Nothing
Response.Write("Click <A HRef=" & strFile & ".csv>Here</A> to to get CSV file")
End Function
Function CreateXlsFile()
Dim xlWorkSheet ' Excel Worksheet object
Dim xlApplication

Set xlApplication = CreateObject("Excel.application")
xlApplication.Visible = False
xlApplication.Workbooks.Add
Set xlWorksheet = xlApplication.Worksheets(1)
xlWorksheet.Cells(1,1).Value = "Year"
xlWorksheet.Cells(1,1).Interior.ColorIndex = 5
xlWorksheet.Cells(1,2).Value = "Region"
xlWorksheet.Cells(1,2).Interior.ColorIndex = 5
xlWorksheet.Cells(1,3).Value = "Sales"
xlWorksheet.Cells(1,3).Interior.ColorIndex = 5

iRow = 2
If Not oRS.EOF Then
Do Until oRS.EOF
For i = 0 To oRS.fields.Count-1
xlWorksheet.Cells(iRow,i + 1).Value = oRS.fields(i)
xlWorkSheet.Cells(iRow,i + 1).Interior.ColorIndex = 4
Next
iRow = iRow + 1
oRS.MoveNext
Loop
End If
strFile = GenFileName()
xlWorksheet.SaveAs Server.MapPath(".") & "\" & strFile & ".xls"
xlApplication.Quit ' Close the Workbook
Set xlWorksheet = Nothing
Set xlApplication = Nothing
Response.Write("Click <A HRef=" & strFile & ".xls>Here</A> to get XLS file")
End Function
</script>
%>

main.htm

<FRAMESET ROWS="20%,*">
<FRAME NAME="Request" SRC="request.html" MARGINWIDTH="10" MARGINHEIGHT="10" SCROLLING="auto" FRAMEBORDER="yes">
<FRAME NAME="Result" SRC="welcome.html" MARGINWIDTH="10" MARGINHEIGHT="10" SCROLLING="auto" FRAMEBORDER="yes">
</FRAMESET>

request.htm

<html>
<head>
<title>Sales Report Demo</title>
</head>

<body>

<DIV ALIGN="center"><FONT SIZE="+1">Sales Reporting</FONT></DIV>
<FORM ACTION="runquery.asp" METHOD="POST" target=Result>
Year <SELECT NAME="Year">
<OPTION VALUE="ALL">ALL</OPTION>
<OPTION VALUE="1995">1995</OPTION>
<OPTION VALUE="1996">1996</OPTION>
<OPTION VALUE="1997">1997</OPTION>
<OPTION VALUE="1998">1998</OPTION>
<OPTION VALUE="1999">1999</OPTION>
</SELECT>
?
Region <SELECT NAME="Region">
<OPTION VALUE="ALL">ALL</OPTION>
<OPTION VALUE="North">North</OPTION>
<OPTION VALUE="East">East</OPTION>
<OPTION VALUE="South">South</OPTION>
<OPTION VALUE="West">West</OPTION>
</SELECT>
?
Return Results Using
<SELECT NAME="ReturnAS">
<OPTION VALUE="HTML">HTML Table</OPTION>
<OPTION VALUE="Content">Content Type</OPTION>
<OPTION VALUE="CSV">CSV</OPTION>
<OPTION VALUE="Excel">Native Excel</OPTION>
</SELECT>
<INPUT TYPE="Submit" NAME="Submit" VALUE="Submit">
</FORM>
</body>
</html>

welcome.htm
<html>
<head>
<title>Sales Report Demo</title>
</head>

<body>

</body>
</html>

資料庫結構
testDB.Mdb
表sales
year 數字
Region 文本
Sales_Amt 貨幣

本文原始出處為國外一網站，並經過BATMAN的休正。

本文章原先以中文撰寫並發佈於 aliyun.com，亦設英文版本，僅作資訊用途。本網站不對文章的準確性，完整性或可靠性或其任何翻譯作出任何明示或暗示的陳述或保證。如對該文章有任何疑慮或投訴，請傳送電郵至 info-contact@alibabacloud.com 並提供相關疑慮或投訴的詳細說明。職員會於 5 個工作天內與您聯絡，一經驗證之後，即會刪除該侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

利用ASP製作EXECL報表方法

聯繫我們

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support