Category / Section
How to overcome ThreadAbortException while saving an Excel file using XlsIO.Base assembly
1 min read
This article explains how to overcome ThreadAbortException while saving an Excel file using XlsIO.Base assembly in Web platforms.
Why exception is thrown?
XlsIO.Base uses Response.End method internally for saving a workbook. So when a workbook is saved with HttpResponse, this Response.End method throws ThreadAbortException. You can use a try-catch statement to catch this exception. The Response.End method ends the page execution and shifts the execution to the Application_EndRequest event in the application's event pipeline. The line of code that follows Response.End is not executed.
To over come this problem, SendStream method is used in XlsIO. The following sample code explains it.
Sample to avoid ThreadAbortException
//Saves workbook as stream with specified ExcelSaveType workbook.SaveAs(stream, ExcelSaveType.SaveAsXLS); //Adds header for specified HttpContentType SendStream(stream, "output.xlsx", ExcelHttpContentType.Excel2016);
The following C#/VB complete code snippet shows how to avoid exception while saving a file with HttpResponse using XlsIO.
C#
using System;
using System.IO;
using Syncfusion.XlsIO;
namespace CreateSpreadsheet
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnCreateExcel_Click(object sender, EventArgs e)
{
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Instantiate the excel application object
IApplication application = excelEngine.Excel;
//Setting Default version.
application.DefaultVersion = ExcelVersion.Excel2016;
//Create workbook
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
//Adding data into worksheet
sheet[1, 1].Text = "Products";
sheet[1, 2].Text = "Qtr1";
sheet[1, 3].Text = "Qtr2";
sheet.Range["A1:C1"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["A1:C1"].CellStyle.Font.Bold = true;
sheet[2, 1].Text = "Alfreds Futterkiste";
sheet[2, 2].Number = 744.6;
sheet[2, 3].Number = 162.56;
sheet[3, 1].Text = "Antonio Moreno Taqueria";
sheet[3, 2].Number = 5079.6;
sheet[3, 3].Number = 1249.2;
sheet[4, 1].Text = "Around the Horn";
sheet[4, 2].Number = 1267.5;
sheet[4, 3].Number = 1062.5;
sheet[5, 1].Text = "Bon app";
sheet[5, 2].Number = 1418;
sheet[5, 3].Number = 756;
sheet[6, 1].Text = "Eastern Connection";
sheet[6, 2].Number = 4728;
sheet[6, 3].Number = 4547.92;
sheet[7, 1].Text = "Ernst Handel";
sheet[7, 2].Number = 943.89;
sheet[7, 3].Number = 349.6;
sheet.SetColumnWidth(1, 25.0);
sheet.SetColumnWidth(2, 12.0);
sheet.SetColumnWidth(3, 12.0);
MemoryStream stream = new MemoryStream();
//Save and close the document
workbook.SaveAs(stream, ExcelSaveType.SaveAsXLS);
SendStream(stream, "output.xlsx", ExcelHttpContentType.Excel2016);
}
}
/// <summary>
/// Adds HttpResponse
/// </summary>
/// <param name="stream">Stream to generate HttpResponse</param>
/// <param name="fileName">Name of the file output file</param>
/// <param name="contentType">Content type to use</param>
private void SendStream(MemoryStream stream, string fileName, ExcelHttpContentType contentType)
{
Response.Clear();
Response.ContentType = GetContentTypeString(contentType);
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();
}
/// <summary>
/// Returns string that corresponds to contentType
/// </summary>
/// <param name="contentType">Content type for browser</param>
/// <returns>String that corresponds to contentType</returns>
private string GetContentTypeString(ExcelHttpContentType contentType)
{
switch (contentType)
{
case ExcelHttpContentType.Excel97:
return "Application/x-msexcel";
case ExcelHttpContentType.Excel2000:
return "Application/vnd.ms-excel";
case ExcelHttpContentType.Excel2007:
case ExcelHttpContentType.Excel2010:
case ExcelHttpContentType.Excel2013:
case ExcelHttpContentType.Excel2016:
return "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
case ExcelHttpContentType.CSV:
return "text/csv";
case ExcelHttpContentType.ODS:
return "application/vnd.oasis.opendocument.spreadsheet";
default:
throw new ArgumentOutOfRangeException("contentType");
}
}
}
}
VB
Imports System
Imports System.IO
Imports Syncfusion.XlsIO
Namespace CreateSpreadsheet
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnCreateExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim excelEngine As ExcelEngine = New ExcelEngine
'Instantiate the excel application object
Dim application As IApplication = excelEngine.Excel
'Setting Default version.
application.DefaultVersion = ExcelVersion.Excel2016
'Create workbook
Dim workbook As IWorkbook = application.Workbooks.Create(1)
Dim sheet As IWorksheet = workbook.Worksheets(0)
'Adding data into worksheet
sheet(1, 1).Text = "Products"
sheet(1, 2).Text = "Qtr1"
sheet(1, 3).Text = "Qtr2"
sheet.Range("A1:C1").HorizontalAlignment = ExcelHAlign.HAlignCenter
sheet.Range("A1:C1").CellStyle.Font.Bold = True
sheet(2, 1).Text = "Alfreds Futterkiste"
sheet(2, 2).Number = 744.6
sheet(2, 3).Number = 162.56
sheet(3, 1).Text = "Antonio Moreno Taqueria"
sheet(3, 2).Number = 5079.6
sheet(3, 3).Number = 1249.2
sheet(4, 1).Text = "Around the Horn"
sheet(4, 2).Number = 1267.5
sheet(4, 3).Number = 1062.5
sheet(5, 1).Text = "Bon app"
sheet(5, 2).Number = 1418
sheet(5, 3).Number = 756
sheet(6, 1).Text = "Eastern Connection"
sheet(6, 2).Number = 4728
sheet(6, 3).Number = 4547.92
sheet(7, 1).Text = "Ernst Handel"
sheet(7, 2).Number = 943.89
sheet(7, 3).Number = 349.6
sheet.SetColumnWidth(1, 25)
sheet.SetColumnWidth(2, 12)
sheet.SetColumnWidth(3, 12)
Dim stream As MemoryStream = New MemoryStream
'Save and close the document
workbook.SaveAs(stream, ExcelSaveType.SaveAsXLS)
Me.SendStream(stream, "output.xlsx", ExcelHttpContentType.Excel2016)
End Sub
'<summary>
'Adds HttpResponse
'</summary>
'<param name="stream">Stream to generate HttpResponse</param>
'<param name="fileName">Name of the file output file</param>
'<param name="contentType">Content type to use</param>
Private Sub SendStream(ByVal stream As MemoryStream, ByVal fileName As String, ByVal contentType As ExcelHttpContentType)
Response.Clear()
Response.ContentType = Me.GetContentTypeString(contentType)
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
'<summary>
'Returns string that corresponds to contentType
'</summary>
'<param name="contentType">Content type for browser</param>
'<returns>String that corresponds to contentType</returns>
Private Function GetContentTypeString(ByVal contentType As ExcelHttpContentType) As String
Select Case (contentType)
Case ExcelHttpContentType.Excel97
Return "Application/x-msexcel"
Case ExcelHttpContentType.Excel2000
Return "Application/vnd.ms-excel"
Case ExcelHttpContentType.Excel2007, ExcelHttpContentType.Excel2010, ExcelHttpContentType.Excel2013, ExcelHttpContentType.Excel2016
Return "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Case ExcelHttpContentType.CSV
Return "text/csv"
Case ExcelHttpContentType.ODS
Return "application/vnd.oasis.opendocument.spreadsheet"
Case Else
Throw New ArgumentOutOfRangeException("contentType")
End Select
End Function
End Class
End Namespace
