locked
Export filename RRS feed

  • Question

  • User269602965 posted

     I use the following to export Oracle DataSet to Excel by behind code Gridview generation and export to Excel.

    It works fine (no formatting in the simple example),

    Except for two issues:

    1.  The Excel spreadsheet opens with the filename of the web page  WebPageName.aspx

    and not as an XLS.  Not good idea for those just clicking SAVE.

    2.  Get the IE explorer 7 and 8 nag, Trying to Open File in Wrong Format.

    I tried Response.AddHeader, which allows me to control the filename, but then the sheet never opens.

    Any ideas would be appreciated.  Problem 1 must be fixed.  Problem 2 is lower priority.

    Thanks

       ' Export to Excel
      Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExportExcel.Click
        ExportDataSetToExcel()
      End Sub
      Private Sub ExportDataSetToExcel()
    
        Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnStr_EPIDB_APU").ConnectionString
        If String.IsNullOrEmpty(strConn) Then
          DisplayMessage("Cannot connect to database to get table.")
        End If
        Dim sSQL As String = " "
          sSQL += "SELECT * "
          sSQL += "FROM "
          sSQL += "  EMP.CHART_DATA "
          sSQL += "ORDER BY "
          sSQL += "  YEAR"
         Using oConn As New OracleConnection(strConn)
           Dim oAdapter As New Oracle.DataAccess.Client.OracleDataAdapter(sSQL, oConn)
           oConn.Open()
           Dim oDS As DataSet = New DataSet
           oAdapter.Fill(oDS)
           If oDS.Tables(0).Rows.Count = 0 Then
             DisplayMessage("No data rows returned from the database.")
           Else
             Response.Clear()
             Response.Charset = ""
             Response.ContentType = "application/vnd.ms-excel"
             Dim stringWrite As New System.IO.StringWriter()
             Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
             Dim dg As New DataGrid
             dg.DataSource = oDS.Tables(0)
             dg.DataBind()
             dg.RenderControl(htmlWrite)
             Response.Write(stringWrite.ToString())
             Response.[End]()
           End If
           oAdapter.Dispose()
           oConn.Close()
         End Using
         Response.End()
       End Sub
       Private Sub DisplayMessage(ByVal msgtext As String)
         Me.lbMessage.Text = msgtext
       End Sub
    
    


     

    Saturday, September 26, 2009 11:22 AM

Answers

  • User1052258516 posted

    Lets try a different method to implement your export download.  Personally I never use the current page I am on to do the downloading, mostly for the reasons you are having now like what happens if the download stalls my users are now forced to close the browser and start over.  What I usually do is implement a download handler using a HttpHandler.  Think of it as pure code that handles your request, it is also it is resusable since you can call it from any page and even pass in querystring parameters to filter with.

    To do this you will need to do two things

    First in your web site add a new Generic Handler from the Add New Item menu and call it ExportToExcel.ashx.  When it opens up copy the code below over the default template code

    Imports System.Web
    Imports System.Data
    Imports System.Web.Services
    Imports Oracle.DataAccess.Client
    
    Public Class ExportToExcel
        Implements System.Web.IHttpHandler
    
        ''' <summary>
        ''' Our data retrieval routine
        ''' </summary>
        ''' <returns>Dataset containing our data</returns>
        ''' <remarks></remarks>
        Private Function GetExcelData() As System.Data.DataSet
    
            Dim ds As DataSet = New DataSet
    
            Try
    
                Dim cnnString As String = ConfigurationManager.ConnectionStrings("ConnStr_EPIDB_APU").ConnectionString
                Dim sql As String = "SELECT * FROM EMP.CHART_DATA ORDER BY YEAR;"
    
                If String.IsNullOrEmpty(cnnString) Then
                    Throw New Exception("Connection string not configured in web.config!")
                End If
    
                Using cnn As New OracleConnection(cnnString)
    
                    cnn.Open()
    
                    Using adapter = New OracleDataAdapter(sql, cnn)
    
                        adapter.Fill(ds)
                    End Using
                End Using
    
            Catch ex As Exception
    
                Throw ' just a throw to pass the true exception up the to caller
            End Try
    
            Return ds
        End Function
    
        ''' <summary>
        ''' Our html retrieval routine
        ''' </summary>
        ''' <param name="hasOutput">Output flag to indicate if we have actual data for the excel export or some kind of issue</param>
        ''' <returns>HTML Markup</returns>
        ''' <remarks></remarks>
        Private Function GetExcelHtml(ByRef hasOutput As Boolean)
    
            Dim html As String = "<strong>The file you requested is unavailable at this time.</strong>"
            Dim ds As DataSet = Nothing
    
            Try
    
                ' get the data
                ds = GetExcelData()
    
                If (ds.Tables.Count.Equals(0)) Then
    
                    ' no data set our response
                    html = "<strong>No data table returned from the database.</strong>"
                    hasOutput = False
                ElseIf (ds.Tables(0).Rows.Count.Equals(0)) Then
    
                    ' no data set our response
                    html = "<strong>No data rows returned from the database.</strong>"
                    hasOutput = False
                Else
    
                    Dim sw As New System.IO.StringWriter()
                    Dim htw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
                    Dim dg As New DataGrid
    
                    With dg
                        .DataSource = ds.Tables(0)
                        .DataBind()
                        .RenderControl(htw)
                    End With
    
                    html = sw.ToString()
                    hasOutput = True ' everything worked out ok we have some kind of result
                End If
            Catch ex As Exception
    
                ' pump the error into the html but hide it from the user
                html = String.Format("{0}<div style='display: none'>{1}</div>", html, ex.Message)
                hasOutput = False
            End Try
    
            Return html
        End Function
    
        ''' <summary>
        ''' Our process method
        ''' </summary>
        ''' <param name="context">The current context of the request</param>
        ''' <remarks>Once IIS receives the request it is passed into this method</remarks>
        Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
    
            Dim hasOutput As Boolean = False
            Dim html As String = GetExcelHtml(hasOutput)
    
            With context
    
                .Response.Clear()
    
                ' if no output is preset then we could 
                ' - redirect to an error aspx page and pass querystring paramters
                ' - load an external html file and write it out to the response
                If (hasOutput) Then
    
                    .Response.ContentType = "application/vnd.ms-excel"
                    .Response.AddHeader("Content-Disposition", String.Format("attachment;filename=ExcelExport_{0}.xls", Now.ToString("yyyyMMdd")))
                Else
    
                    .Response.ContentType = "text/html"
                    html = String.Format("<html><head><title>Alert!</title></head><body>{0}</body></html>", html)
                End If
    
                .Response.Write(html)
            End With
        End Sub
    
        ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property
    
    End Class

    Basically its your own code just refactored a bit and broken out into pieces for readability sake.

    Once that is done the second thing you need to do is on the aspx page you have your export button on is to copy the JavaScript code below to the content section of your page.  This will make the button call the download and export feature.

    <script type="text/javascript">
        function exportToExcel() {
            var win = window.open('ExportToExcel.ashx', 'ExcelExport', 'left=20,top=20,width=400,height=100,toolbar=0,resizable=0');
            if (win == null)
                alert('A popup blocker is blocking the window, please allow popups from this site!');
    
            return false;
        }
    </script>

    Once that is done you need to add the OnClientClick attribute to your Button btnExportExcel like so: OnClientClick="return exportToExcel();"

    Ok what is going on here is pretty simple, the OnClientClick is going to open up and new window and call the 'ExportToExcel.ashx' handler in it's own process of sorts so that your aspx page will not actually do the work (if it works you can then remove the btnExportExcel_Click event in your aspx page).  The JavaScript function will always return "False" so that the button does not postback to the current page (we don't need to do this now as the handler will now handle the export/download).

    Now if you look at the handler code you will see that it handles various situations if there is no data, errors and such, since a HttpHandler is just a request process you can write anything out to the browser (html, documents, images, binary data, xml, JSON, etc).  If you wanted you could load up a html file from the file system and display that if there are issues rather than hard coding the html in the handler.

    Another nice thing is that you can debug this code like any other code just put a break point as you would normally and you can call it directly in the browser window to test if you want.  Now depending on how you did your security you may have to add some security checks but I am not sure about what you did so you can test this by callign the handler directly when not logged in (if you have security) and see what happens.

    Now I am not sure if this will help your situation but it may help in debugging.  Hopefully it works from the get go as I don't have a oracle database to test with.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 27, 2009 12:23 PM

All replies

  • User1052258516 posted

    Have you tried making the ContentType = "application/octet-stream"?  IE will recognize the file type by the extention in the file in most cases.

     

    Saturday, September 26, 2009 3:50 PM
  • User269602965 posted

    Thank you for your response.

     

    I tried this and it promptly opened the gridview into a web page with the same name as the

    web page calling the procedure in code behind.

     

    I am trying to export to Excel on the client workstation.

     

    I added

    Response.AddHeader("Content-Disposition", "attachment;filename=ExportGrid.xls")

    But then the hard drive churned for awhile and Excel never opens.

     

    Any other ideas?

     

    Saturday, September 26, 2009 10:32 PM
  • User1052258516 posted

    Lets try a different method to implement your export download.  Personally I never use the current page I am on to do the downloading, mostly for the reasons you are having now like what happens if the download stalls my users are now forced to close the browser and start over.  What I usually do is implement a download handler using a HttpHandler.  Think of it as pure code that handles your request, it is also it is resusable since you can call it from any page and even pass in querystring parameters to filter with.

    To do this you will need to do two things

    First in your web site add a new Generic Handler from the Add New Item menu and call it ExportToExcel.ashx.  When it opens up copy the code below over the default template code

    Imports System.Web
    Imports System.Data
    Imports System.Web.Services
    Imports Oracle.DataAccess.Client
    
    Public Class ExportToExcel
        Implements System.Web.IHttpHandler
    
        ''' <summary>
        ''' Our data retrieval routine
        ''' </summary>
        ''' <returns>Dataset containing our data</returns>
        ''' <remarks></remarks>
        Private Function GetExcelData() As System.Data.DataSet
    
            Dim ds As DataSet = New DataSet
    
            Try
    
                Dim cnnString As String = ConfigurationManager.ConnectionStrings("ConnStr_EPIDB_APU").ConnectionString
                Dim sql As String = "SELECT * FROM EMP.CHART_DATA ORDER BY YEAR;"
    
                If String.IsNullOrEmpty(cnnString) Then
                    Throw New Exception("Connection string not configured in web.config!")
                End If
    
                Using cnn As New OracleConnection(cnnString)
    
                    cnn.Open()
    
                    Using adapter = New OracleDataAdapter(sql, cnn)
    
                        adapter.Fill(ds)
                    End Using
                End Using
    
            Catch ex As Exception
    
                Throw ' just a throw to pass the true exception up the to caller
            End Try
    
            Return ds
        End Function
    
        ''' <summary>
        ''' Our html retrieval routine
        ''' </summary>
        ''' <param name="hasOutput">Output flag to indicate if we have actual data for the excel export or some kind of issue</param>
        ''' <returns>HTML Markup</returns>
        ''' <remarks></remarks>
        Private Function GetExcelHtml(ByRef hasOutput As Boolean)
    
            Dim html As String = "<strong>The file you requested is unavailable at this time.</strong>"
            Dim ds As DataSet = Nothing
    
            Try
    
                ' get the data
                ds = GetExcelData()
    
                If (ds.Tables.Count.Equals(0)) Then
    
                    ' no data set our response
                    html = "<strong>No data table returned from the database.</strong>"
                    hasOutput = False
                ElseIf (ds.Tables(0).Rows.Count.Equals(0)) Then
    
                    ' no data set our response
                    html = "<strong>No data rows returned from the database.</strong>"
                    hasOutput = False
                Else
    
                    Dim sw As New System.IO.StringWriter()
                    Dim htw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
                    Dim dg As New DataGrid
    
                    With dg
                        .DataSource = ds.Tables(0)
                        .DataBind()
                        .RenderControl(htw)
                    End With
    
                    html = sw.ToString()
                    hasOutput = True ' everything worked out ok we have some kind of result
                End If
            Catch ex As Exception
    
                ' pump the error into the html but hide it from the user
                html = String.Format("{0}<div style='display: none'>{1}</div>", html, ex.Message)
                hasOutput = False
            End Try
    
            Return html
        End Function
    
        ''' <summary>
        ''' Our process method
        ''' </summary>
        ''' <param name="context">The current context of the request</param>
        ''' <remarks>Once IIS receives the request it is passed into this method</remarks>
        Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
    
            Dim hasOutput As Boolean = False
            Dim html As String = GetExcelHtml(hasOutput)
    
            With context
    
                .Response.Clear()
    
                ' if no output is preset then we could 
                ' - redirect to an error aspx page and pass querystring paramters
                ' - load an external html file and write it out to the response
                If (hasOutput) Then
    
                    .Response.ContentType = "application/vnd.ms-excel"
                    .Response.AddHeader("Content-Disposition", String.Format("attachment;filename=ExcelExport_{0}.xls", Now.ToString("yyyyMMdd")))
                Else
    
                    .Response.ContentType = "text/html"
                    html = String.Format("<html><head><title>Alert!</title></head><body>{0}</body></html>", html)
                End If
    
                .Response.Write(html)
            End With
        End Sub
    
        ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
            Get
                Return False
            End Get
        End Property
    
    End Class

    Basically its your own code just refactored a bit and broken out into pieces for readability sake.

    Once that is done the second thing you need to do is on the aspx page you have your export button on is to copy the JavaScript code below to the content section of your page.  This will make the button call the download and export feature.

    <script type="text/javascript">
        function exportToExcel() {
            var win = window.open('ExportToExcel.ashx', 'ExcelExport', 'left=20,top=20,width=400,height=100,toolbar=0,resizable=0');
            if (win == null)
                alert('A popup blocker is blocking the window, please allow popups from this site!');
    
            return false;
        }
    </script>

    Once that is done you need to add the OnClientClick attribute to your Button btnExportExcel like so: OnClientClick="return exportToExcel();"

    Ok what is going on here is pretty simple, the OnClientClick is going to open up and new window and call the 'ExportToExcel.ashx' handler in it's own process of sorts so that your aspx page will not actually do the work (if it works you can then remove the btnExportExcel_Click event in your aspx page).  The JavaScript function will always return "False" so that the button does not postback to the current page (we don't need to do this now as the handler will now handle the export/download).

    Now if you look at the handler code you will see that it handles various situations if there is no data, errors and such, since a HttpHandler is just a request process you can write anything out to the browser (html, documents, images, binary data, xml, JSON, etc).  If you wanted you could load up a html file from the file system and display that if there are issues rather than hard coding the html in the handler.

    Another nice thing is that you can debug this code like any other code just put a break point as you would normally and you can call it directly in the browser window to test if you want.  Now depending on how you did your security you may have to add some security checks but I am not sure about what you did so you can test this by callign the handler directly when not logged in (if you have security) and see what happens.

    Now I am not sure if this will help your situation but it may help in debugging.  Hopefully it works from the get go as I don't have a oracle database to test with.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 27, 2009 12:23 PM
  • User269602965 posted

    Yes

    What you describe makes sense to me to avoid opening in the page that I am calling from.

    I appreciate the comprehensive response.

    But first I will do some reading on custom HTTPHandlers since this is a new topic for me.

    Thanks.

     

    Sunday, September 27, 2009 2:38 PM
  • User269602965 posted

    Yes

    It worked very well.  I had to make a few adjustments in VS2008, option strict = on, IIS7, and ASP.NET 3.5 as shown below.

    Now I would imagine one could work in some very basic formatting like background color for the Excel Row 1 Column Headers

    since it is an HTML stream?

    Thank you very much for time and help.

    Imports System.Configuration
    Imports System.Data
    Imports System.Web
    Imports System.Web.Services
    Imports System.Web.UI
    Imports System.Web.UI.WebControls
    Imports Oracle.DataAccess.Client
    ' Export dataset to Excel in a new page
    Public Class ExportToExcel
      Implements System.Web.IHttpHandler
      ' Process HttpHandler request
      Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        Dim hasOutput As Boolean = False
        Dim html As String = CStr(GetExcelHtml(hasOutput))
        With context
          .Response.Clear()
          ' if no output is preset then
          ' - redirect to an error aspx page and pass querystring paramters
          ' - load an external html file and write it out to the response
          If (hasOutput) Then
            .Response.ContentType = "application/vnd.ms-excel"
            .Response.AddHeader("Content-Disposition", String.Format("attachment;filename=ExcelExport_{0}.xls", Now.ToString("yyyyMMdd")))
          Else
            .Response.ContentType = "text/html"
            html = String.Format("<html><head><title>Alert!</title></head><body>{0}</body></html>", html)
          End If
          .Response.Write(html)
        End With
      End Sub
      ' Convert the Oracle dataset to HTML markup
      Private Function GetExcelHtml(ByRef hasOutput As Boolean) As String
        Dim html As String = "<strong>The file you requested is unavailable at this time.</strong>"
        Dim ds As DataSet = Nothing
        Try
          ' get the data
          ds = GetExcelData()
          If (ds.Tables.Count.Equals(0)) Then
            ' no data set our response
            html = "<strong>No data table returned from the database.</strong>"
            hasOutput = False
          ElseIf (ds.Tables(0).Rows.Count.Equals(0)) Then
            ' no data set our response
            html = "<strong>No data rows returned from the database.</strong>"
            hasOutput = False
          Else
            Dim sw As New System.IO.StringWriter()
            Dim htw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
            Dim dg As New DataGrid
            With dg
              .DataSource = ds.Tables(0)
              .DataBind()
              .RenderControl(htw)
            End With
            html = sw.ToString()
              hasOutput = True
          End If
        Catch ex As Exception
          ' add exception to html stream
          html = String.Format("{0}<div style='display: none'>{1}</div>", html, ex.Message)
          hasOutput = False
        End Try
        Return html
      End Function
      ' Get the Oracle dataset
      Private Function GetExcelData() As System.Data.DataSet
        Dim ds As DataSet = New DataSet
        Try
          Dim strConn As String = ConfigurationManager.ConnectionStrings("ConnStr_EPIDB_APU").ConnectionString
          If String.IsNullOrEmpty(strConn) Then
            Throw New Exception("Database connection string not found in application.")
          End If
          Dim sSQL As String = "SELECT * FROM EMP.CHART_DATA ORDER BY YEAR"
          Using cnn As New OracleConnection(strConn)
            cnn.Open()
            Using adapter = New OracleDataAdapter(sSQL, cnn)
              adapter.Fill(ds)
            End Using
          End Using
          Catch ex As Exception
            ' Throw exception back to the calling sub or function
            Throw
        End Try
        Return ds
      End Function
      ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
          Return False
        End Get
      End Property
    End Class


     

    Sunday, September 27, 2009 5:07 PM
  • User269602965 posted

    I am going to guess that since we created a DataGrid or GridView,

    we could use code behind to format the gird,

    and that will show up in the HTML stream

    rather than trying to make HTML markup on the fly.

    I will experiment with that

    as ALL my gridviews are built in code behind.

    Sunday, September 27, 2009 6:40 PM
  • User269602965 posted

     Yes you can add some basic formatting to the Excel sheet.

    I changed the datagrid to a gridview

     

            Dim sw As New System.IO.StringWriter()
            Dim htw As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(sw)
            Dim gv As New GridView
            With gv
               gv.Font.Name = "Arial"
               gv.Font.Size = 9
               gv.HeaderStyle.BackColor = Drawing.Color.LightGoldenrodYellow
               gv.HeaderStyle.Font.Bold = True
              .DataSource = ds.Tables(0)
              .DataBind()
              .RenderControl(htw)
            End With
            html = sw.ToString()
            gv.Dispose()
            hasOutput = True
          End If


     

    Sunday, September 27, 2009 7:21 PM