Making A Dynamically Created Excel Report Downloadable

Question

I have 2 blocks of code, if someone could help me put them together I would get the functionality I am looking for. The first block of code downloads a gridview to excel using the download dialog I am looking for:

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    ' Verifies that the control is rendered 
End Sub

Private Sub ExportToExcel(ByVal filename As String, ByVal gv As GridView, ByVal numOfCol As Integer)
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", filename))
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Dim sw As New StringWriter()
    Dim hw As New HtmlTextWriter(sw)

    gv.AllowPaging = False
    gv.DataBind()

    'Change the Header Row back to white color 
    gv.HeaderRow.Style.Add("background-color", "#FFFFFF")

    For i As Integer = 0 To numOfCol - 1
        gv.HeaderRow.Cells(i).Style.Add("background-color", "blue")
        gv.HeaderRow.Cells(i).Style.Add("color", "#FFFFFF")

    Next

    For i As Integer = 0 To gv.Rows.Count - 1
        Dim row As GridViewRow = gv.Rows(i)

        'Change Color back to white 
        row.BackColor = System.Drawing.Color.White

        For j As Integer = 0 To numOfCol - 1
            row.Cells(j).Style.Add("text-align", "center")
        Next


        'Apply text style to each Row 
        row.Attributes.Add("class", "textmode")

        'Apply style to Individual Cells of Alternating Row 
        If i Mod 2 <> 0 Then

            For j As Integer = 0 To numOfCol - 1
                row.Cells(j).Style.Add("background-color", "#CCFFFF")
                row.Cells(j).Style.Add("text-align", "center")
                '#C2D69B
                'row.Cells(j).Style.Add("font-size", "12pt")
            Next

        End If
    Next
    gv.RenderControl(hw)

    'style to format numbers to string 
    Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
    Response.Write(style)
    Response.Output.Write(sw.ToString())
    Response.Flush()
    Response.End()
End Sub

The second block of code is a sample report I am wish to be downloaded. So instead of downloading a gridview I want this function to accept a worksheet object.

Comment to Frank's suggestions... Frank thanks for your help this almost works for me. The problem is the code crashes if I dont have a dummy file called test.xls in my root folder. And when I put it there it then loads 2 workbooks test.xls[1] and Book 2 which test.xls is a blank workbook and Book 2 is the correct dymanically created report. I dont want to save this file in th eroot folder if I dont have too, I want the users to simply open of download it to their client. The code im using after the woorkbook is created is... Dim fn As String = "RptCrd_ " & "BUNDLE" & ".xls" Dim eio As String = "~/ContentDisposition/" & fn

    Dim exData As Byte() = File.ReadAllBytes(Server.MapPath(eio))
    Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fn))
    Response.ContentType = "application/x-msexcel"
    Response.BinaryWrite(exData)
    Response.Flush()
    Response.End()
    releaseObject(xlApp)
    releaseObject(xlWorkBook)

Answer 1

Maybe you need to add the proper content type and header to your Response so the browser knows you want to handle the download with Excel. You'd likely put this after the Response.Write(style); call.

Try Googling for MS Excel mime type, and also how to add a properly formatted Header and Content Type to your Response.

I just threw some code together to spit out a download to the browser so that it can handle it as an Excel spreadsheet. This should give you an example of how to format the Response Headers.

This just reads in an existing spreadsheet then feeds it to the response stream. This just demonstrates how to formulate a Response Header.

  byte[] excelData = File.ReadAllBytes(Server.MapPath("test.xls"));

  Response.AddHeader("Content-Disposition", "attachment; filename=test.xls");
  Response.ContentType = "application/x-msexcel";
  Response.BinaryWrite(excelData);