472,784 Members | 889 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,784 software developers and data experts.

Export to Excel

I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate
through the recordset and display the data in a table. Before I iterate
through the recordset I instruct the browser that the content type is
Excel using the following line:

(Response.ContentType = "application/vnd.ms-excel")

This works fine with Excel 2003 but with older versions (I tested Excel
97) the HTML included within the include files (on Page One) is
displayed in an Excel worksheet.

Page One - Begins with Include files (which contain my subroutines and
databse connection information). Then I have a Select statement to
determine which export users want. When I hit the case I need I then
call a Sub routine that exports the data to Excel.

Page Two - this page contains all of my custom functions and
procedures.

Page One Example Code:
<!-- #INCLUDE FILE="../../../includes/common_settings.asp" -->
<!-- #INCLUDE FILE="../includes/composite_settings.asp" -->
<!-- #INCLUDE FILE="../includes/composite_functions.asp" -->

<%
sSymbol = Request("Symbol")
sExportType = Request("ExportType")

'Determine Selected Export Type
Select Case lcase(sExportType)
' '
'QUARTERLY EXPORT
Case "quarter"
' '
'Execute the Display Quarter Procedure
call DisplayQuarterExport(sSymbol)
' '
'ANNUAL EXPORT
Case "annual"
' '
'Execute the Display Annual Procedure
call DisplayAnnualExport(sSymbol)
' '
'RAW EXPORT
Case "raw"
' '
'Execute the Display Annual Procedure
call DisplayRawExport(sSymbol)
' '
End Select
%>

Page Two Procedure:
<%
Sub DisplayQuarterExport(pSymbol)
'Execute Quarter Function
set pRSQuarter = QuarterExportQuery(pSymbol)
' '
'Tell the Browser to redirect the output to Excel
Response.ContentType = "application/vnd.ms-excel"
' '
'Check is Recordset contains data
If pRSQuarter.RecordCount > 0 then
' '
'Move to the first record in the recordset
pRSQuarter.MoveFirst
' '
'Display the title bar
Response.Write "<table border=1>"
Response.Write "<tr>"
' '
'Iterate through the fields collection
for each field in pRSQuarter.Fields
Response.Write "<td>"
Response.Write "<b>" & replace(field.name,"_"," ") & "</b>"
Response.Write "</td>"
next
' '
Response.Write "</tr>"
' '
'Move to the first record in the recordset
pRSQuarter.MoveFirst
' '
'Display data
Do until pRSQuarter.EOF
Response.Write "<tr>"
' '
'Composite Code
Response.Write "<td>"
Response.Write trim(pRSQuarter.Fields("Composite").Value)
Response.Write "</td>"
'Composite Date
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Composite_Date").Value
Response.Write "</td>"
'Gross Return
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Gross").Value,2) & "%"
Response.Write "</td>"
'Gross UV
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Gross_UV").Value,2)
Response.Write "</td>"
'Net Return
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Net").Value,2) & "%"
Response.Write "</td>"
'Net UV
Response.Write "<td>"
Response.Write Round(pRSQuarter.Fields("Net_UV").Value,2)
Response.Write "</td>"
'Total Equity
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Total_Equity").Value
Response.Write "</td>"
'Total Fixed
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Total_Fixed").Value
Response.Write "</td>"
'Cash
Response.Write "<td>"
Response.Write pRSQuarter.Fields("Cash").Value
Response.Write "</td>"
' '
Response.Write "</tr>"
pRSQuarter.MoveNext
Loop
' '
'Close Quarter Export Recordset
CloseRS(pRSQuarter)
' '
'Close Database Connection
CloseDBConnection()
' '
Response.Write "</table>"
' '
'Display Error Message is no results were found
Else
Response.Write "Your Query returned (0) results."
' '
End If
' '
End Sub
%>

Again this works great if I have Excel 2003 but for older versions of
Excel it displays the HTML that is contained within the include files
on page one.

Here is my assumption but I have no clue why Excel 2003 works. I am
assuming that the include file content is written to the screen (behind
the scenes) and when I instruct the browser that the content is Excel,
the include file data is included in the "content" and is displayed in
Excel.

Please help, I am racking my head against the wall with this one.

Jul 22 '05 #1
1 4903
I figured out a solution.

Before I set the content type (Response.ContentType) I clear the buffer
by executing the following line.

Response.Clear

This cleared the existing content from the buffer.

Jul 22 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
2
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType =...
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.