利用ASP制作EXECL报表方法(二)
发布时间:2006-10-14 2:45:04   收集提供:gaoqian
废话少说,请看代码:
runquery.asp

<%@ LANGUAGE="VBSCRIPT" %>
<%
'DSNless connection to Access Database
strDSNPath = "PROVIDER=MSDASQL;DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("testDB.mdb")
%>
<!--#include file="adovbs.inc" -->  请自己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

<!-- frames -->
<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>
    &nbsp;        
    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>
    &nbsp;
    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>
    &nbsp;    
    &nbsp;
<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的休正。

 
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50