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. Image for the cookie policy date
close icon

ExportDataTable takes forever with UsedRangeIncludesFormatting

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.

1 Reply

SR Sridhar Syncfusion Team 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.



Live Chat Icon For mobile
Up arrow icon