当前位置：中国站长下载 → 文章中心 → 网页编程 → ASP编程 → 利用ASP制作EXECL报表方法（二）

利用ASP制作EXECL报表方法（二）

减小字体

增大字体作者：不详来源：不详发布时间：2006-8-13 0:40:09

废话少说，请看代码：
  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

[1] [2] 下一页

[] [返回上一页] [打印]

·上一篇文章：怎样把数据库结构显示出来的源代码
·下一篇文章：利用ASP制作EXECL报表方法（一）

利用ASP制作EXECL报表方法（二）

文章评论评论内容只代表网友观点，与本站立场无关！

频道栏目导航

本类热门阅览

相关文章