Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export HTML table to Excel 1

Status
Not open for further replies.

fdarkness

Programmer
Feb 17, 2006
110
CA
I've done searches in general, searches through the forum, and searches in the FAQs and I'm not quite getting the answer I'm looking for. I have most of it, but not quite all of it, so hopefully someone can help me with the last little bit.

My page is querying a database and then displaying the contents in an HTML table. In the old version of the site (written in ColdFusion), there's a button to click to save the contents of the table into an Excel sheet. The actual CF code is just a line before and after the lines of code that display the table.

I'm trying to replicate this in ASP, but the problems I'm running into seem to indicate that I would have to re-run the query to produce the Excel sheet. This is a bit of a problem because the queries *are* rather big and take time to run. I'd rather not do that. It would also require me to duplicate the code into a seperate page (unless there's a way around this?)

What I'd like is something similar to the CF page, where I can put in a chunk of code that says something like "any content from here on is exported into Excel when the 'export to Excel' button is pressed".

Can such a thing be done? I'm using the following pages to glean my code from so far:
 
Is the spreadsheet download exactly the same as the table?

If so you could set up a session variable

So do your loop, but rather than outputing the html add it to a session variable

Code:
session("output") = "<table>"&vbcrlf
do while not rs.eof
  session("output") = session("output") & "<tr>"&vbcrlf
  session("output") = session("output") & "  <td>"&rs("Val1")&"</td>"&vbcrlf
  session("output") = session("output") & "  <td>"&rs("Val2")&"</td>"&vbcrlf
  session("output") = session("output") & "</tr>"&vbcrlf
loop
session("output") = session("output") &"</table>"

response.write(session("output"))

Then using the excel code you found, use use the session variable instead of rerunning the query,. just do the excel code and response.write(session("output")

}...the bane of my life!
 
Code:
<!-- #INCLUDE file="functions_db.asp" -->
<%
if request.form("submit") <> "Download Excel Version" then
	dim rs
	strSQL = "SELECT * FROM tbl_xxx LIMIT 100"
	set rs=conn.execute(strSQL)
	if not rs.eof then
		  session("output") = "<style>table,body{font-family:arial;}</style>"&vbcrlf
		  session("output") = session("output") & "<table width='100%' cellspacing='0'>"&vbcrlf
		  session("output") = session("output") & "<tr>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 2px solid;'>Title</td>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 2px solid;border-left:black 2px solid;'>Description</td>"&vbcrlf
		  session("output") = session("output") & "</tr>"&vbcrlf	
		do while not rs.eof
		  session("output") = session("output") & "<tr>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 1px solid;'>"&rs("index_title")&"</td>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-left:black 2px solid;border-bottom:black 1px solid;'>"&rs("index_desc")&"</td>"&vbcrlf
		  session("output") = session("output") & "</tr>"&vbcrlf
		  rs.movenext
		loop
		session("output") = session("output") &"</table>"&vbcrlf
		response.write(session("output")&vbcrlf)
		response.write("<form method='post'><input type='submit' name='submit' value='Download Excel Version' /></form>")
	else
		response.write("no records")
	end if
	rs.close
	set rs=nothing
else
   Response.ContentType = "application/vnd.ms-excel"
   Response.AddHeader "content-disposition", "attachment; filename=Excel Output.xls"
   response.write(session("output"))
end if
%>

}...the bane of my life!
 
Sheco... it's a bit more than one line... my mistake. Here's what it is in ColdFusion:

Code:
<CFIF isDefined("form.toXLS.x")>
	<CFSET convert_type="xls">
	<CFSET convert_app="excel">
	<CFHEADER NAME="content-disposition" VALUE="attachment;filename=Report.#convert_type#">
	<CFCONTENT TYPE="application/ms#convert_app#" > 
	<HTML XMLNS:O="urn:schemes-microsoft-com:office:office"
	      XMLNS:X="urn:schemes-microsoft-com:office:#convert_app#"
	      XMLNS="[URL unfurl="true"]http://www.w3.org/TR/RC-html40">[/URL]
	<HEAD>
	<TITLE></TITLE>
	</HEAD>
	<BODY>

then there's code to display the site and there's this line to display the "button toolbar":
Code:
<CFINCLUDE TEMPLATE="/includes/ConvertToolBar.cfm">

I'm not entirely sure how it works as I didn't write this and the previous programmer wasn't very good at what he was doing. Suffice to say, I'm surprised I've managed to get this far in rewriting the website without freaking out given the really badly done queries and programming he's done and trying to interpret exactly what he's doing half the time.
 
guitardave78: it worked perfectly except for *one* little glitch. Somehow it pulled everything on the page into the Excel sheet, even though I only sent it the Session("output") content! Very weird.

(And no, I didn't assign any of the other page contents to the session variable)

Either way, it makes no nevermind to me. Management is used to doing some editting to the Excel sheets from this site. Thank you! :)
 
Yeah, dont include the body tag etc on the page
If you need them try this.
If it is in there it will be included in the excel output!!

eg;
Code:
<!-- #INCLUDE file="functions_db.asp" -->
<%
if request.form("submit") <> "Download Excel Version" then%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">[/URL]
<html xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml">[/URL]
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Excel Test</title>
</head>
<body>
	<%dim rs
	strSQL = "SELECT * FROM tbl_xxx LIMIT 100"
	set rs=conn.execute(strSQL)
	if not rs.eof then
		  session("output") = "<style>table,body{font-family:arial;}</style>"&vbcrlf
		  session("output") = session("output") & "<table width='100%' cellspacing='0'>"&vbcrlf
		  session("output") = session("output") & "<tr>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 2px solid;'><strong>Title</strong></td>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 2px solid;border-left:black 2px solid;'><strong>Description</strong></td>"&vbcrlf
		  session("output") = session("output") & "</tr>"&vbcrlf	
		do while not rs.eof
		  session("output") = session("output") & "<tr>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-bottom:black 1px solid;'>"&rs("index_title")&"</td>"&vbcrlf
		  session("output") = session("output") & "  <td valign='top' style='border-left:black 2px solid;border-bottom:black 1px solid;'>"&rs("index_desc")&"</td>"&vbcrlf
		  session("output") = session("output") & "</tr>"&vbcrlf
		  rs.movenext
		loop
		session("output") = session("output") &"</table>"&vbcrlf
		response.write(session("output")&vbcrlf)
		response.write("<form method='post'><input type='submit' name='submit' value='Download Excel Version' /></form>")
	else
		response.write("no records")
	end if
	rs.close
	set rs=nothing
%>
</body>
</html>
<%else
   Response.ContentType = "application/vnd.ms-excel"
   Response.AddHeader "content-disposition", "attachment; filename=Excel Output.xls"
   response.write(session("output"))
end if
%>

}...the bane of my life!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top