Many times we need to convert tabular data into execl in the form of a user, and there are several ways to do it, and I'll introduce a method that takes advantage of ASP, which allows the server to dynamically create EXECL reports without consuming any server space. The method also allows multiple users to receive the data at the same time. But the method requires at least EXECL 97 support.
Needless to say, the most important thing to do in this job is to tell the browser HTTP headers to use the following code:
<%
Response.ContentType = "Application/vnd.ms-excel"
%>
Let's take a look at an example, assuming that you now have the following form of data:
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
Customer requirements are expressed in the form of execl and want to add some other calculated totals
Use the following code:
......
<%
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 but 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>
......
This allows us to achieve the goal, the user can open it in the browser window for simple operation, or can be saved to the hard disk for other operations. I'll also introduce a way to take advantage of FileSystemObject operations. Please wait a moment. : Less nonsense, please look at the code:
Runquery.asp
<%@ language= "VBSCRIPT"%>
<%
' Dsnless connection to Access Database
Strdsnpath = "Provider=msdasql;driver={microsoft Access DRIVER (*.mdb)};D bq=" & Server.MapPath ("Testdb.mdb")
%>
<!--#include file= "adovbs.inc"--> please copy this file yourself
<%
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" >
<title>excel Export demo</title>
<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>
<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 as 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 & "Year" & Chr (+) & ","
strtext = strtext & Chr (+) & "Region" & Chr (+) & ","
strtext = strtext & Chr (+) & "Sales" & Chr (+) & ","
A.writeline (strtext)
Do Until ors.eof
For i = 0 to Ors.fields.count-1
strtext = CHR & Ors.fields (i) & Chr (a) & ","
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 ' 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= "ten" marginheight= "a" scrolling= "Auto" frameborder= "yes" >
<frame name= "Result" src= "welcome.html" marginwidth= "ten" marginheight= "ten" scrolling= "Auto" frameborder= "yes" >
</FRAMESET>
Request.htm
<title>sales demo</title>
<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>
Welcome.htm
<title>sales demo</title>
<body>
</body>
Database structure
Testdb.mdb
Table Sales
Year figures
Region text
Sales_amt currency
The original source of this article is a foreign website, and after the Batman of Hugh.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
