Making A Dynamically Created Excel Report Downloadable
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 m开发者_运维技巧e. 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)
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);
精彩评论