Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

export html recordset table to excel using javascript

Posted on 2009-05-15
18
Medium Priority
?
2,103 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

In Part 1 (http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/A_7849-Hex-Maze.html) we covered the hexagonal maze basics -- how the cells are represented in a JavaScript array and how the maze is displayed.  In this part, we'…
A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one 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…

618 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