Export to Excel

Discussion in 'ASP General' started by Matt, Jul 13, 2005.

  1. Matt

    Matt Guest

    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.
    Matt, Jul 13, 2005
    #1
    1. Advertising

  2. Matt

    Matt Guest

    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.
    Matt, Jul 14, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?RGF2aWQgVmFsbGU=?=

    Invalid export DLL or export format

    =?Utf-8?B?RGF2aWQgVmFsbGU=?=, Oct 29, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    3,603
    =?Utf-8?B?RGF2aWQgVmFsbGU=?=
    Oct 29, 2003
  2. =?Utf-8?B?U3JpZGhhcg==?=

    using Microsoft Excel image for Export to Excel button

    =?Utf-8?B?U3JpZGhhcg==?=, Dec 9, 2005, in forum: ASP .Net
    Replies:
    0
    Views:
    3,083
    =?Utf-8?B?U3JpZGhhcg==?=
    Dec 9, 2005
  3. =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=

    Export to Excel (Default File Type - Excel)

    =?Utf-8?B?SGVtYW50IFNpcGFoaW1hbGFuaQ==?=, Apr 24, 2006, in forum: ASP .Net
    Replies:
    15
    Views:
    17,470
    syed hassan
    May 21, 2009
  4. Grey
    Replies:
    4
    Views:
    1,943
    Mark Rae [MVP]
    Oct 17, 2007
  5. Maarten Porters
    Replies:
    1
    Views:
    419
    Florian Gilcher
    Jul 28, 2008
Loading...

Share This Page