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

JLohman
JLohman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
This is a good example because it writes to the server.  There is a way to open up as excel in the browser but if the excel file is too large it can be very very slow.


<%@ Language=VBScript %>
<%Option Explicit%>

<%
' ### for this example i'm using an array for data instead of a DB result ###

' ### DIM variables ###
DIM exampleArray(5), strFileName, strPath, arr_exampleArray, sPword
exampleArray(0) = "1,John,Smith,28,john.smith@something.com"
exampleArray(1) = "2,Peter,Jones,25,peter.jones@something.com"
exampleArray(2) = "3,Adam,Peterson,35,adam.peterson@something.com"
exampleArray(3) = "4,Mick,Dawson,28,mick.dawson@something.com"
exampleArray(4) = "5,Jane,Gillan,31,jane.gillan@something.com"
exampleArray(5) = "6,Sally,Johnson,22,sally.johnson@something.com"

strFileName = "test_excel.xls"
strPath = "\Reports\Create_Excel\" & strFileName
%>
<html>
<head>
<title>Create Excel Report</title>
</head>
<body>
<%
DIM fso, act, a
set fso = createobject("scripting.filesystemobject")
Set act = fso.CreateTextFile(server.mappath(strPath), true)

act.WriteLine("<html><body>")
act.WriteLine("<table border=""1"">")
act.WriteLine("<tr>")
act.WriteLine("<th nowrap>USER ID</th>")
act.WriteLine("<th nowrap>FIRST NAME</th>")
act.WriteLine("<th nowrap>LAST NAME</th>")
act.WriteLine("<th nowrap>EMAIL ADDRESS</th>")
act.WriteLine("</tr>")
for a = 0 to 5
      arr_exampleArray = Split(exampleArray(a),",")
      act.WriteLine("<tr>")
      act.WriteLine("<td align=""Left"" nowrap>" & arr_exampleArray(0) & "</td>" )
      act.WriteLine("<td align=""Left"" nowrap>" & arr_exampleArray(1) & "</td>" )
      act.WriteLine("<td align=""Left"" nowrap>" & arr_exampleArray(2) & "</td>" )
      act.WriteLine("<td align=""Left"" nowrap>" & arr_exampleArray(4) & "</td>" )
      act.WriteLine("</tr>")
next
act.WriteLine("</table></body></html>")
act.Close


if fso.FileExists(server.mappath(strPath)) Then
      Response.Write "Excel report has been created."
end if

set act = nothing
set fso = nothing
%>
</body>
</html>
Top Expert 2011

Commented:
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

Author

Commented:
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
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Top Expert 2011

Commented:
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.

Author

Commented:
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.
Top Expert 2011
Commented:
Looks like you need one because that tag won't be recognized without iis.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial