Solved

export html recordset table to excel using javascript

Posted on 2009-05-15
18
1,997 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

JavaScript can be used in a browser to change parts of a webpage dynamically. It begins with the following pattern: If condition W is true, do thing X to target Y after event Z. Below are some tips and tricks to help you get started with JavaScript …
This article demonstrates how to create a simple responsive confirmation dialog with Ok and Cancel buttons using HTML, CSS, jQuery and Promises
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…

760 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

19 Experts available now in Live!

Get 1:1 Help Now