Category / Section
How to execute next line of codes after invoking Workbook.SaveAs() method in XlsIO web application?
2 mins read
By default, the code after the SaveAs method won’t execute in Web platforms. This is because, Response.End() method has been invoked inside IWorkbook.SaveAs to download the generated Excel document. However, this can be resolved by saving workbook object in a memory stream and execute the code after that. Finally, send the stream into the method as given in the code snippet to achieve the requirement.
C#
protected void Button1_Click(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; string fileName = Server.MapPath("App_Data/Sample.xlsx"); IWorkbook workbook = application.Workbooks.Open(fileName, ExcelOpenType.Automatic); IWorksheet sheet = workbook.Worksheets[0]; MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); //Downloading the Excel file. SendStream(stream, "Output.xlsx", ExcelVersion.Excel2016); } private void SendStream(MemoryStream stream, string fileName, ExcelVersion version) { Response.Clear(); switch (version) { case ExcelVersion.Excel97to2003: Response.ContentType = "Application/vnd.ms-excel"; break; default: Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; break; } Response.AddHeader("Content-Disposition", String.Format("{0}; filename={1};", "attachment", fileName)); Response.OutputStream.Write(stream.ToArray(), 0, Convert.ToInt32(stream.Length)); Response.Flush(); Response.Close(); }
VB
Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click Dim excelEngine As New ExcelEngine() Dim application As IApplication = excelEngine.Excel Dim fileName As String = Server.MapPath("App_Data/Sample.xlsx") Dim workbook As IWorkbook = application.Workbooks.Open(fileName, ExcelOpenType.Automatic) Dim sheet As IWorksheet = workbook.Worksheets(0) Dim stream As MemoryStream = New MemoryStream() workbook.SaveAs(stream) workbook.Close() excelEngine.Dispose() 'Downloading the Excel file. SendStream(stream, "Output.xlsx", ExcelVersion.Excel2016) End Sub Private Sub SendStream(stream As MemoryStream, fileName As String, version As ExcelVersion) Response.Clear() Select Case version Case ExcelVersion.Excel97to2003 Response.ContentType = "Application/vnd.ms-excel" Exit Select Case Else Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" Exit Select End Select Response.AddHeader("Content-Disposition", [String].Format("{0}; filename={1};", "attachment", fileName)) Response.OutputStream.Write(stream.ToArray(), 0, Convert.ToInt32(stream.Length)) Response.Flush() Response.Close() End Sub
The sample which illustrates the above behavior can be downloaded here.