export html recordset table to excel using javascript

I'm a newbie at web development.  I've created a page that creates a report into an html table using using recordsets.  I'd like to be able to have a submit button that will export the table into an excel spreadsheet. I'd appreciate any help with reading the table using recordset and where I should place the asp code.  Thanks.
function exportHtmlTable()	{
	var sRet; 
	alert("test");
	sRet = ""
	alert(sRet);
	sRet = "<table class="+ "report"+ " cellpadding=" + "0"+ " cellspacing=" + "0" +">"; 
	sRet = sRet + "<td>Class Name</td>";
	sRet = sRet & "<td><%=rs("className")%></td>"	
	sRet = sRet & "<td><%=rs("instructorName")%></td>"	
	sRet = sRet & "</tr>"	
	sRet = sRet & "</table>"
	
 
}
<%		
Response.Clear()
Response.Buffer = True
Response.AddHeader "Content-Disposition", "attachment;filename=export.xls"  
Response.ContentType = "application/vnd.ms-excel"
Response.Write exportHtmlTable()
Response.End()
%>

Open in new window

lpierreAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RoonaanCommented:
Hi,

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

-r-
0
kevp75Commented:
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"
%>
0
lpierreAuthor Commented:
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.  
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

lpierreAuthor Commented:
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>

Open in new window

0
kevp75Commented:
"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?
0
lpierreAuthor Commented:
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.
0
kevp75Commented:
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)
0
lpierreAuthor Commented:
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>

Open in new window

0
lpierreAuthor Commented:
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>

Open in new window

0
kevp75Commented:
what's the error
0
lpierreAuthor Commented:
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.
0
kevp75Commented:
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?
0
lpierreAuthor Commented:
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."
0
kevp75Commented:
does "export_to_excel2a.asp" exist?

are you using a compelte URL to the file?
0
lpierreAuthor Commented:
yes, that's file above which contains the code to export to excel and create the table.
0
kevp75Commented:
there is no link above to the page..  please post it
0
lpierreAuthor Commented:
Ok. I got it. It was a matter of removing the line
Set rs = Server.CreateObject("ADOBD.Recordset")
Thanks for the help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.