export html recordset table to excel using javascript

Hi,

Shouldn't the exportHtmlTable function be inside the <% %> section?

-r-

just build your recordset and display them like normal in a table, and at the top of that page put the:

<%            
Response.Clear()
Response.Buffer = True
Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"  
Response.ContentType = "application/vnd.ms-excel"
%>

Thank for the help!!  When I put the code at the top of the page the html page automatically prompts to save the excel file when the page loads.  I'd like to be able to select a user from a dropdown menu then click on export.  The excel spreadsheet also, does not contain any of the table values from the html tables.  

Below is my code.

<!--#include virtual="/globalelements/includes/banner.inc" -->
 
<div id="maincol">
<div id="maincontent">
    <div> 
      <%
	Dim iStr
	
	
 
	Set dbConn = Server.CreateObject("ADODB.Connection")
	dbConn.ConnectionTimeout = 3
	strDSN = "Provider=SQLOLEDB; Data Source=nyc-test-02; Initial Catalog=registration; User ID=psa; 
 
password=dragon44"
 
	dbConn.Open strDSN
 
 
iStr = Request("instruct")
 
Set rs = Server.CreateObject("ADOBD.Recordset")
 
if iStr="" then iStr=Request.QueryString("instrName")
sqlsel= "Select className, instructorName FROM instructor_evaluation Where instructorName = '" & iStr & "' ORDER BY 
 
instructorName"
 
 
Set rs = dbConn.Execute(sqlsel)
Set rsInstruct = Server.CreateObject("ADOBD.Recordset")
sqlinstruct = "SELECT distinct instructorName FROM instructor_evaluation ORDER BY instructorName"
Set rsInstruct = dbConn.Execute(sqlinstruct)
 
%>
 
<FORM name="myform1" method="POST">
 
<%
Response.Write "<table>"
Response.Write "<tr>"
Response.Write "<td><select name=instrName><option value='''' selected>Instructor's Name</option>"
Do While Not rsinstruct.EOF 
Response.Write "<option value=''" & rsInstruct("instructorName") &"''>"& rsInstruct("instructorName") &"</option>"
rsInstruct.MoveNext
Loop
Response.Write rsInstruct("instructorName")
Response.Write "</select>"
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</FORM>"
Response.Write "</table>"
Response.Write "<BR>"
Response.Write "</tr>"
%>
 
 
<FORM>
<INPUT type="button" value="Submit" id=cmdCheckForm name=cmdCheckForm onclick="javascript: foo
 
(document.myform1.instrName.options[document.myform1.instrName.selectedIndex].text)";> 
</FORM>
 
 
<FORM>
<INPUT type="button" value="Export" id=cmdCheckForm2 name=cmdCheckForm2 onclick="javascript: exportHtmlTable()";/> 
</FORM>
 
<h3>Instructor Evaluation Report: <%=Response.Write(istr)%></h3>
<table class="report" cellpadding="0" cellspacing="0">
<tr>
<th>Class Name</th>
<th>Instructor Name</th>
</tr>
 
<%
Do While Not rs.EOF
%>
 
<td><%=rs("className")%></td>
<td><%=rs("instructorName")%></td>
</tr>
 
<%
rs.MoveNext
Loop
 
Response.Clear()
Response.Buffer = True
Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"  
Response.ContentType = "application/vnd.ms-excel"
</script>
%>
 
</tr>
</table>
 
</div>
</div>
</div>
 
</FORM>
</body>
</html>

Select allOpen in new window

"When I put the code at the top of the page the html page automatically prompts to save the excel file when the page loads." <- copy the code to another page, and link to it

"I'd like to be able to select a user from a dropdown menu then click on export.  The excel spreadsheet also, does not contain any of the table values from the html tables." <- are you sure there are records to satisfy the recordset query?  Do the records display on a page without the excel code?

I got the export working fine now but the data does not appear in the spreadsheet.  The dropdown list populates the table fine on the page though.

Ok.

#1
Make sure that on your export page, all you are doing is running the recordset, and writing a table of the data to the page

#2
Make sure this is the only thing at the top of your page
      Response.Buffer = True
      server.ScriptTimeout = 3000
      Response.ContentType = "application/vnd.ms-excel"
      Response.AddHeader "content-disposition", "attachment; filename = export.xls"


#3
Please post the code for the export page (the one you are linking to)

Here's the page

<%
Response.Clear()
Response.Buffer = True
Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"  
Response.ContentType = "application/vnd.ms-excel"
%>
 
<script type="text/javascript">
 
function exportHtmlTable()	{
	var sRet; 
	alert("test");
	sRet = ""
	alert(sRet);
	sRet = "<table class="+ "report"+ " cellpadding=" + "0"+ " cellspacing=" + "0" +">"; 
	sRet = sRet + "<td>Class Name</td>";
	alert(sRet);	
 
	sRet = sRet & "<td><%=rs("className")%></td>"	
	sRet = sRet & "<td><%=rs("instructorName")%></td>"	
	sRet = sRet & "</tr>"	
	sRet = sRet & "</table>"
 
}		
 
</script>

Select allOpen in new window

Ok.  So I was able to get the excel sheet populated but when I try to access the recordset I get an error on the page.  Any help would be greatly appreciated.  

<%
Response.Clear()
Response.Buffer = True
Response.AddHeader "Content-Disposition", "attachment;filename=Evaulation_export.xls"  
Response.ContentType = "application/vnd.ms-excel"
 
%>
 
<HTML>
<BODY>
 
<%
   	Dim iStr
	Set dbConn = Server.CreateObject("ADODB.Connection")
	dbConn.ConnectionTimeout = 3
	strDSN = "Provider=SQLOLEDB; Data Source=nyc-test-02; Initial Catalog=registration; User ID=psa; 
 
password=dragon44"
 
	dbConn.Open strDSN
 
 
iStr = Request("instruct")
 
Set rs = Server.CreateObject("ADOBD.Recordset")
 
if iStr="" then iStr=Request.QueryString("instrName")
sqlsel= "Select className, instructorName FROM instructor_evaluation Where instructorName = '" & iStr & "' ORDER BY 
 
instructorName"
 
Set rs = dbConn.Execute(sqlsel)
 
%>
</script>
<!-- Our table which will be translated into an Excel spreadsheet -->
<h3>Instructor Evaluation Report: <%=Response.Write(istr)%></h3>
<TABLE WIDTH=75% BORDER=1 CELLSPACING=1 CELLPADDING=1>
<TR>
   <TD><font size=+2>Class Name</font></TD>
   <TD><font size=+2>Instructor Name</font></TD>
</TR>
<!-- server-side loop adding Table entries -->
<% Do While Not rs.EOF %>
<TR>
   <TD><%=rs("className")%></TD>
   <TD><%=rs("instructorName")%></TD>
</TR>
<% rs.MoveNext
   loop
   ' Clean up
   rs.Close
   set rs = Nothing
   dbConn.Close
   set dbConn = Nothing
%>
</TABLE>
</BODY>
</HTML>

Select allOpen in new window

what's the error

I get a page cannot be found or unavailable.  It looks like it's having a problem parsing through my recordset.  When I take out the references to the recordset the excel spreadsheet generates without incident.

ok.  Using IE, go to "Tools" -> "Internet Options" -> 'Advanced' tab, and uncheck 'Show Friendly HTTP Errors' and try it again

without knowing what the error is, we won't be able to tell what's wrong.   The code is correct, so I doubt it's something with the code...

Do you have a link to the page?

I the following message

"Internet Explorer cannot download export_to_excel2a.asp from nyc-dev-01.  Internet Explorer was unable to open the internet site.  The requested site is either unavailable or cannot be found.  Please try again later."

does "export_to_excel2a.asp" exist?

are you using a compelte URL to the file?

yes, that's file above which contains the code to export to excel and create the table.

there is no link above to the page..  please post it