Thursday, 19 February 2009

Writing an xlsx document to the response stream using ASP.NET and OOXML

Have spent a most frustrating day trying to output an Excel document created using OOXML sdk 2.0 in a web service to the web browser.
There are two elements to solving this problem>
  1. Using a web service to transfer binary data
  2. Opening Excel on the browser using the new xlsx format
This is actually pretty straight forward, as long as you use a byte array, the data is base64 encoded in the background automatically. On the client, use the right content type and use BinaryWrite instead of Write which is where I was having the problem:
Public Function PerformanceReportByUser(ByVal startDate As Date, ByVal endDate As Date) As Byte()
'Get the path to the template
Dim path As String = Server.MapPath("Templates/PerformanceByUserTemplate.xlsx")

'Get the stream containing the report package
Dim stream As MemoryStream = PerformanceReport.ExecuteByUser(path, startDate, endDate)
Return stream.ToArray()
End Function
On the client, process the byte array. Note the content type, and use of BinaryWrite.
Dim bytes() As Byte = portalservice.PerformanceReportByUser(CDate(ctlStartDate.Text), CDate(ctlEndDate.Text))

'Send response with content type to display as MS Excel
Response.Buffer = True
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "performance.xlsx"))

'The following directive causes a open/save/cancel dialog for Excel to be displayed
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

'Write to response

'Response.Flush() 'Do not flush if using compression

No comments: