We help IT Professionals succeed at work.

export to excel in classic asp

Hello experts,
I have several classic asp pages coded by teammates that need export to excel functionality. I know very little about classic asp. Any specific advice on how to add an export to excel button to a page like this one? The button would just export the data currently being displayed in the html table to excel on the client.

<%
dim cnt
cnt=0

response.write "For Location "&request.form("level_name")&"<br> "
response.write "From "&formatdatetime(request.form("begin_dt"),2)&" to "&formatdatetime(request.form("end_dt"),2)&"<br> "

'Get list of people 
%>
<p><h4>Current Users in the System (1):</h4>
<table border=1>
<tr><td colspan=5></td>
<td align=center colspan=7 bgcolor=teal><font color=white><b>COURSES COMPLETED</b></font></td>
</tr>
<tr><th align=left>Employee</th>
<th align=left>Job Title</th>
<th align=left nowrap>DU Certified (2)</th>
<th align=left><a href=javascript:myPopup1('notes.asp')><font color=white>Rollout Date</font></a></th>
<th align=left>Early Adopter</th>
<th align=left>Status</th>
<th align=left>Setup/Searches</th>
<th align=left>CE AN</th>
<th align=left>CE PN</th>
<th align=left>PAF Ins.</th>
<th align=left>PAF Trust</th>
<th align=left>PAF Non-Trust</th>
</tr>

<!--#include virtual=include_files/incl_connect_select.asp--><%
SQL = " select data from table "


	oRS.Open SQL,db
	Do While not oRS.EOF

	cnt=cnt+1

	response.write "<tr>"
    response.write "<td nowrap>"&oRS("FullName")&"</td>"
    response.write "<td nowrap>"&oRS("job_title")&"</td>"
    
    if oRS("cert_date")<>"1/1/1900" then
    response.write "<td>"&formatdatetime(oRS("cert_date"),2)&"</td> "
    else
    response.write "<td>NA</td> "
    end if
    
    if oRS("begin_date")="1/1/1900" then
    response.write "<td>Independent</td> "
    else
    response.write "<td>"&oRS("begin_date")&"</td> "
    end if
    
    response.write "<td nowrap>"&oRS("early_app")&"</td>"
	response.write "<td nowrap>"&oRS("employee_status")&"</td>"
	response.write "<td nowrap>"&oRS("setup_searches")&"</td>"
	response.write "<td nowrap>"&oRS("ce_an")&"</td>"
	response.write "<td nowrap>"&oRS("ce_pn")&"</td>"
	response.write "<td nowrap>"&oRS("paf")&"</td>"
	response.write "<td nowrap>"&oRS("trust")&"</td>"
	response.write "<td nowrap>"&oRS("non_trust")&"</td>"
	
response.write "</tr>"

'End report users
	
	oRS.movenext
	Loop
	db.close
	set db = nothing

response.Write "</table></p>"


%>

Open in new window

Comment
Watch Question

LAMP Developer
BRONZE EXPERT
Commented:
You need to set the content type as Response.ContentType = "application/vnd.ms-excel" on the top to export as excel.

ref. http://www.tech-recipes.com/rx/1735/classic_asp_generate_excel_spreadsheets/

Author

Commented:
I wasn't able to see the info in that link. But I found this online and inserted it at the top of the code. It's opening excel and showing me the page but I don't see the data that should be in the html table. I just see the columns. Any idea why? Thanks.

<a href="SomePage.asp?excel=1&<%=request.querystring%>" target="_new">Export to excel </a> 
<%
if(request.querystring("excel")="1") then

response.Clear()
response.ContentType = "application/vnd.ms-excel"
'Response.AddHeader "Content-Disposition", "attachment;filename=FILENAME.xls"  

end if
%>

Open in new window

Commented:
I see this: <p><h4>Current Users in the System (1):</h4>

Leave that out. Just use a table and nothing else. Excel can only handle a table.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.