WEB开发网
开发学院WEB开发ASP 利用ASP制作EXECL报表方法(二) 阅读

利用ASP制作EXECL报表方法(二)

 2001-02-05 10:56:13 来源:WEB开发网   
核心提示:废话少说,请看代码:runquery.asp<%@ LANGUAGE="VBSCRipT" %><%'DSNless connection to access DatabasestrDSNPath = "PROVIDER=MSDASQL;DRIVER={Micro
废话少说,请看代码:
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>
       
  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的休正。

Tags:利用 ASP 制作

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接