ExportDataTable takes forever with UsedRangeIncludesFormatting

[Dennis van Emmerik]

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.EnableSheetCalculations();
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]

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.

 

Note:

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.

Thanks,

Sridhar.S