Solved

export html recordset table to excel using javascript

Posted on 2009-05-15
18
2,014 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:lpierre
  • 9
  • 7
18 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 24402381
Hi,

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

-r-
0
 
LVL 25

Expert Comment

by:kevp75
ID: 24402441
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
 

Author Comment

by:lpierre
ID: 24416558
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
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

 

Author Comment

by:lpierre
ID: 24416680
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
 
LVL 25

Expert Comment

by:kevp75
ID: 24416791
"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
 

Author Comment

by:lpierre
ID: 24416934
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
 
LVL 25

Expert Comment

by:kevp75
ID: 24417133
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
 

Author Comment

by:lpierre
ID: 24417218
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
 

Author Comment

by:lpierre
ID: 24417574
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
 
LVL 25

Expert Comment

by:kevp75
ID: 24420938
what's the error
0
 

Author Comment

by:lpierre
ID: 24425789
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
 
LVL 25

Expert Comment

by:kevp75
ID: 24425880
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
 

Author Comment

by:lpierre
ID: 24426425
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
 
LVL 25

Expert Comment

by:kevp75
ID: 24426512
does "export_to_excel2a.asp" exist?

are you using a compelte URL to the file?
0
 

Author Comment

by:lpierre
ID: 24426730
yes, that's file above which contains the code to export to excel and create the table.
0
 
LVL 25

Expert Comment

by:kevp75
ID: 24426847
there is no link above to the page..  please post it
0
 

Accepted Solution

by:
lpierre earned 0 total points
ID: 24427690
Ok. I got it. It was a matter of removing the line
Set rs = Server.CreateObject("ADOBD.Recordset")
Thanks for the help.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows how to create and access 2-dimensional arrays in JavaScript.  It includes a tutorial in case you are just trying to "get your head wrapped around" the concept and we'll also look at some useful tips for more advanced programmers. …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question