Solved

export html recordset table to excel using javascript

Posted on 2009-05-15
18
2,003 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
This article discusses how to create an extensible mechanism for linked drop downs.
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now