Articles in this section
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.

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments
Please sign in to leave a comment
Access denied
Access denied