We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

ExportDataTable takes forever with UsedRangeIncludesFormatting

Thread ID:





113730 Oct 7,2013 07:16 AM UTC Oct 8,2013 10:37 AM UTC ASP.NET MVC (Classic) 1
Tags: XlsIO
Dennis van Emmerik
Asked On October 7, 2013 07:16 AM UTC

I'm using XlsIO version 11.3040.0.30

I'm trying to import an Excel-sheet with formulas. I want to have the calculated values for the formulas into a DataTable. The code i use is something like this:

ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
IWorkbook workbook = application.Workbooks.Open(Server.MapPath("~/Excel/Budget-2013-10-03.xlsx"), ExcelOpenType.Automatic);
IWorksheet workSheet = workbook.Worksheets[0];

int startRow = 8;
IRange exportRange = workSheet.Range[startRow, 1, workSheet.UsedRange.LastRow, workSheet.UsedRange.LastColumn];

workSheet.UsedRangeIncludesFormatting = false;

DataTable Result = workSheet.ExportDataTable(exportRange, ExcelExportDataTableOptions.ColumnNames | ExcelExportDataTableOptions.ComputedFormulaValues | ExcelExportDataTableOptions.DetectColumnTypes);

When I include the the bold line (UsedRangeIncludesFormatting = false), the import takes forever (cpu=100%) and ends ultimately (after 30+ mins) in a 'Invalid Handle'.
When I don't include the bold line (or set UsedRangeIncludesFormatting = true), the import finishes in a couple of seconds.

Because of the formula's, we upgraded our package from v8 to v11 to have the functionality of calculations (hence the sheet.EnableSheetCalculations()). However even with sheet.DisableSheetCalculations() the ExportDataTable takes forever.

Sridhar [Syncfusion]
Replied On October 8, 2013 10:37 AM UTC

Hi Dennis van Emmerik,

Thank you for using Syncfusion products.

We need an excel file which causes the performance issue at your side so that we are able to reproduce the reported performance issue at our side.

Please get the usage of UsedRangeIncludesFormatting property with Essential XlsIO.


If true, the cell is included into UsedRange when it has some record created for it even if data is empty (maybe some formatting    changed, maybe not - cell was accessed and record was created).


If false, in this case XlsIO tries to remove empty rows and columns from all sides to make UsedRange smaller.


Also, please update us with the System configuration using at your side (Operating System (32-bit/ 64-bit) , Ram capacity) to analyse further on this performance issue.



This property loops through each row and column of the worksheet used range and checks for an empty row and column.


Please let us know if you need any further assistance.




This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon