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.
Unfortunately, activation email could not send to your email. Please try again.

ExportDataTable takes forever with UsedRangeIncludesFormatting

Thread ID:

Created:

Updated:

Platform:

Replies:

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

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 [Syncfusion]
Replied On October 8, 2013 06:37 AM

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


CONFIRMATION

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.

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.

;