它可以为用户在您的网页中查看大量有用的数据,通常这足以满足他们的需求,但是有时您可能希望以某种形式向用户提供他们可以实际使用的数据。 此代码向您展示了如何显示数据库中的数据,然后如何以可用的Excel电子表格的形式将数据提供给用户,然后他们可以将其拿走并自己玩耍。 我在此处显示的方法是首先显示数据,使用我的文章“如何动态搜索数据库表”,您可以使用户能够在生成数据之前重新查询数据。 这是第一页称为01viewdata.asp的代码:
<%@ Language=VBScript %>
<!-- #include file="../incfiles/adovbs.inc" -->
<%
'Declare some variables
dim Conn,RS1
'==================CONNECTION CODE TO SQL SERVER=======================
'Create a connection object to the database
Set Conn = Server.CreateObject("ADODB.Connection")
'Create a recordset object
Set RS1 = Server.CreateObject("ADODB.RecordSet")
'Feed the connection string into a variable called strConn
strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
"User Id=USERNAME;Password=PASSWORD;"
'Feed the connection string into the connection object
Conn.Open strConn
'==========================CONNECTION CODE=============================
'Create your sql statement
sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
'Obtain the data using all the work done above
RS1.Open sql1, Conn
'Test to see if we have a recordset coming back from the database and trap the error if there is no data
If RS1.eof Then
%>
<html>
<body>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
<tr>
<td> <font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
</tr>
</table>
<%
'If there is data carry on
Else
%>
<!--
We will need to fire the data we gather to the next page which produces the Excel sheet so here
we use a form to do so instead of formulating the sql statement again when we export to excel
I pass the statement above inside a hidden field called 'InpSQL' below, this makes sure that
what they see on this page is exactly what they get in Excel.
-->
<form method="POST" action="02extoex.asp">
<table border=0 width="100%" cellspacing="0" cellpadding="2">
<tr>
<td>
<input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">
<input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
</tr>
</table>
</form>
<!-- This area is for display to the user and shows how the data to be exported will look -->
<table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
<tr>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field1Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field2Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field3Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field4Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field5Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field6Header</font></td>
<td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field7Header</font></td>
</tr>
<%
'Whilst there are records to show keep going
Do While Not RS1.eof
%>
<!--
Note the use of in each field this makes sure the data looks OK in
a bordered table if the return is null.
-->
<tr>
<td><font face="Arial" size="2"><%=RS1("Field1Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field2Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field3Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field4Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field5Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field6Result")%> </font></td>
<td><font face="Arial" size="2"><%=RS1("Field7Result")%> </font></td>
</tr>
<%
'Loop through each record and write it to the screen
RS1.Movenext
Loop
End If
%>
</table>
</body>
</html>
<%
'Clean up and close the connections and recordset objects
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>
这是出口到excel的聪明点。
您可以直接从链接中触发此操作,而无需像上面一样显示它,如果这样做的话,将导致Excel电子表格直接存在。
这是第二页的链接所产生的名为02extoex.asp的页面的代码。
<%@ Language=VBScript %>
<!-- #include file="../incfiles/adovbs.inc" -->
<%
'Declare some variables
dim Conn,RS1
'==================CONNECTION CODE TO SQL SERVER=======================
'Create a connection object to the database
Set Conn = Server.CreateObject("ADODB.Connection")
'Create a recordset object
Set RS1 = Server.CreateObject("ADODB.RecordSet")
'Feed the connection string into a variable called strConn
strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
"User Id=USERNAME;Password=PASSWORD;"
'Feed the connection string into the connection object
Conn.Open strConn
'==========================CONNECTION CODE=============================
'Obtain the sql statement which we fed into the hidden field in the last page
sql1 = Request("InpSQL")
'If you aren't happy with doing that repeat your sql statement here
'sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
'The assumption here is that if we saw data in the last page the data exists so there is no need to
'test again for errors so we just go for the data
RS1.Open sql1, Conn
'This is the the code which tells the page to open Excel and give it the data to display
Response.ContentType = "application/vnd.ms-excel"
'You can give the spreadsheet a name at the point its produced
Response.AddHeader "Content-Disposition", "attachment; filename=MYSPREADSHEETNAME.xls"
%>
<!--
Note that I have formatted the output header here to a dark blue background and white text
this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.
-->
<table border="1" width="100%">
<tr>
<td bgcolor="#000080"><font color="#FFFFFF">Field1Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field2Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field3Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field4Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field5Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field6Header</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">Field7Header</font></td>
</tr>
<%Do While Not RS1.eof%>
<tr>
<td><%=RS1("Field1Result")%></td>
<td><%=RS1("Field2Result")%></td>
<td><%=RS1("Field3Result")%></td>
<td><%=RS1("Field4Result")%></td>
<td><%=RS1("Field5Result")%></td>
<td><%=RS1("Field6Result")%></td>
<td><%=RS1("Field7Result")%></td>
</tr>
<%
RS1.Movenext
Loop
%>
</table>
<%
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>
就是这样。
一种实用且简单的方法,可以使您的网站更具交互性。
From: https://bytes.com/topic/asp-classic/insights/706819-how-export-your-displayed-data-excel