Avatar of JLohman
JLohman
 asked on

Database results displayed with ASP code - need to have an option to download to Excel

I have a report I am generating from data from an Access database. The report displays as an ASP page. I want an option to download the data in the report as an EXCEL spreadsheet. I would like to maintain the report with an option to "click here to download an EXCEL copy".

I am attaching the code.
code.txt
Microsoft ExcelASP

Avatar of undefined
Last Comment
sammySeltzer

8/22/2022 - Mon
SOLUTION
Scott Fell

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sammySeltzer

Here is another option for you.

On the code you attached, add a link to it titled click here to download an EXCEL copy

Something like:

< a href="exceldownload.asp">click here to download an EXCEL copy</a>

Then save the code below as exceldownload.asp

You will have it saved to a file called report.xsl

Of course you can change it to whatever you wish.

A couple of notes, this works for me but I did not test with connection to your db.

Second, if you wish to download per individual record, then you will need to pass id as querystring from your code to the code below.

If you have questions, ask.

<%
'Create recordset
 Dim strSQL , objRS

 strSQL = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, " & _
 		"tblWeb.SSLink, tblWeb.IntraLink, tblWeb.PubMedLink, " & _
 		"tblStaffDirectory.SDEmail " & _

	"FROM (tblGeneral INNER JOIN tblWeb ON tblGeneral.ChartID= tblWeb.ChartID) " & _
	"INNER JOIN tblStaffDirectory ON tblGeneral.ChartID = tblStaffDirectory.ChartID "
 		If Request.querystring("sort") = "" then
 			strSQL = strSQL & "ORDER BY City"
 		Else
  			strSQL = strSQL & "ORDER BY " & Request.querystring("sort")
  		End If			
	
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn"

	Dim objRS, i,j
	Set objRS = MyConn.Execute(strSQL)

	If objRS.EOF Then
		Response.Write "No records returned"
		Response.End
	End if

	Dim aryData, aryHeaders
	ReDim aryHeaders(objRS.Fields.Count)
	Dim intRecFirst, intRecLast
	Dim intFieldFirst, intFieldLast, intRecordCount

	For i=0 To objRS.Fields.Count - 1
	    aryHeaders(i) = objRS.Fields(i).Name
	Next
	aryData = objRS.GetRows

	'empty recordset
	objRS.Close
	Set objRS = Nothing

	'Find the Upper & Lower ends of the Array
	intRecFirst   = LBound(aryData, 2)
	intRecLast    = UBound(aryData, 2)
	intFieldFirst = LBound(aryData, 1)
	intFieldLast  = UBound(aryData, 1)
	intRecordCount = UBound(aryData, 2) + 1

    Dim fileName, format, contentType
    format = Cint(request("fm"))

    'set your variables and write data
    select case format
		case 1:PrintExcel'EXCEL
		case else: PrintExcel 'text
    end select

	Response.End()

	Sub SetHeaders(fileName, contentType)
		'add your headers
		Response.ContentType = contentType
		Response.AddHeader "Content-Disposition", "attachment; filename=" & fileName
	End Sub

	Sub PrintExcel
		SetHeaders strTable & "reports.xls", "Application/vnd.excel"

		Response.Write "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
		Response.Write "<head>"
		Response.Write "<!--[if gte mso 9]><xml>"
		Response.Write "<x:ExcelWorkbook>"
		Response.Write "<x:ExcelWorksheets>"
		Response.Write "<x:ExcelWorksheet>"
		Response.Write "<x:Name>"& strTable &" Report</x:Name>"
		Response.Write "<x:WorksheetOptions>"
		Response.Write "<x:Print>"
		Response.Write "<x:ValidPrinterInfo/>"
		Response.Write "</x:Print>"
		Response.Write "</x:WorksheetOptions>"
		Response.Write "</x:ExcelWorksheet>"
		Response.Write "</x:ExcelWorksheets>"
		Response.Write "</x:ExcelWorkbook>"
		Response.Write "</xml>"
		Response.Write "<![endif]--> "
		Response.Write "</head>"
		Response.Write "<body>"

		PrintTable "<td>","</td>","<tr>","</tr>","<table>","</table>"

		Response.Write "</body>"
		Response.Write "</html>"
	End Sub

	Sub PrintTable(f1,f2,r1,r2,t1,t2)
		Response.Write t1

		'add column headers
		Response.Write r1
		For i=0 To UBound(aryHeaders)
		    Response.Write f1 & aryHeaders(i) & f2
		Next
		Response.Write r2

		'add rows
		For i = intRecFirst To intRecLast
		    Response.Write r1

		    'loop thru all fields and display their values
		    For j = intFieldFirst To intFieldLast
		        Response.Write f1 & aryData(j, i) & f2
		    Next

		    Response.Write r2
		Next

		'close
		Response.Write t2
	End Sub


%>

Open in new window

JLohman

ASKER
Hi Sammy, I do not need to download any individual records.

I copied your code and included by database connection code (which works for many other reports):

<!-- #Include Virtual="CodeReuse/ConnComparisonChart2.inc" -->
  (code is attached)

I am getting the following error

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

A name was started with an invalid character. Error processing resource
...

<%
comparisonchart2.txt
sammySeltzer

My suspicion is that it has to do with the ">"  "<" characters in your function.

They tend to conflict with xml tagging.

Try 2 things and let me know what happens.

First, replace your function in the file you attached with this:

function protectSQL(objval)
  if trim(objval)	= "" then
   protectSQL	= "''"
  else
   objval	= replace(objval,"'","''")
   objval	= replace(objval,"""","''")
   protectSQL= "'" & trim(objval) & "'"
 end if
end function

Open in new window


This will do same things as far as protecting against injection and other special characters in comments.

Then on iis, right-click on your website, click on the asp.net tab and be sure the current version is selected.

Try now and post back what happens.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
JLohman

ASKER
I'm still getting the same error:

A name was started with an invalid character. Error processing resource ...

<%

I do not have an iis running on this computer.
ASKER CERTIFIED SOLUTION
sammySeltzer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question