worksheet.ImportDataTable() performance

[Eric Arends]

I use the following code to populate a worksheet using a DataTable object:

public void AddDataToWorkbookStream(DataTable dataTable, Stream stream)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication excel = excelEngine.Excel;
IWorkbook workbook = excel.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.ImportDataTable(dataTable, true, 1, 1);
workbook.SaveAs(stream);
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
return;
}

The DataTable has some 500 rows and 80 columns. I expect the tables to be even bigger in the future. It takes some 3 minutes to create the workbook. The bottleneck is the worksheet.ImportDataTable() method.

Is there a faster way to populate a worksheet?

[Melba Winshia]

Hi Eric,

Thanks for your interest in Essential XlsIO.

I was able to reproduce the problem. However, our internal version is more optimized with respect to time taken and memory usage. I created a sample to import a datatable with 80 columns and 500 rows and tested in version 4.4.0.51. It took more than 3 minutes to import. But with our internal version it took only 2-3 seconds.

Our next release would be in the second week of March, which has great improvement in performance (reduced memory and better speed).

Kindly let me know if you have any other questions.

Regards,
Melba

[Eric Arends]

Hi Melba

Previously I was running the web application in Debug mode in VS2005. In Release mode I do not notice a slow performance. The workbook is created in 2-3 seconds, which is more than enough.

Interestingly enough and am no longer able to get a slow performance in Debug mode now. You wrote that you were able to reproduce the slow performance. Was this is Debug mode? Could you try again in Release mode and report on the performance. And then back in Debug mode?

Regards
Eric

[Melba Winshia]

Hi Eric,

Thanks for the update!

I tested in debug mode only. It took more than 3 minutes to import data in debug mode with version 4.4. But with our internal (upcoming) version it took just 2-3 seconds in debug mode. I tested in release mode (VS2005) in version 4.4. But it took the same 3 minutes (more than that). Could please let us know how you tested in release mode? This would help us to test with release mode and report on the performance.

Kindly let me know if you have any other questions.

Regards,
Melba

[Administrator]

When is the latest version coming out?

I am also having memory/performance problems in my evaluation....

[Melba Winshia]

Hi JimB,

I am glad to inform you that our latest version (V5) is scheduled to be released within this week.

Kindly let me know if you have any other questions.

Regards,
Melba

[Brett Wilbur]

Hello, any update on the new release with enhanced datatable import performance?

I am experiencing slow import times with my trial version as well, averaging about 50 rows per 10 seconds.

With the need to import up to 500-1000 rows, this could be a showstopper.

Thanks.

[Melba Winshia]

Hi Brett,

Thanks for your interest in Essential XlsIO.

Essential XlsIO Version 5 has great improvements in performance (reduced memory and better speed). I created a sample to import data table with 5000 rows and 10 columns. It took more than 3 minutes in version 4.4. But in version 5 it took only 2-3 seconds. Here is the video clip for your reference to show the performance improvements in version 5:

Performance.zip

Could you please refer the following forum thread to obtain the beta version of Essential Studio v5?

http://www.syncfusion.com/support/Forums/message.aspx?&MessageID=57573

Kindly let me know if you have any other questions.

Thanks,
Melba

[Brett Wilbur]

Hi Melba,

Working with one of your Account Managers (thanks Holley) I was able to test the beta 5 version.

Similar to your results, I experienced dramatically improved load times, arround 2700 rows in 1.5-2 seconds.

I look forward to working with XlsIO.

Thanks again for your help.

[Melba Winshia]

Hi Brett,

Thanks for the update.

Glad to hear that there is great improvement in load times in V5.

Kindly let me know if you have any other questions.

Thanks,
Melba

[Geogie Varughese]

Do you have any bench marks when the same example is loaded with formatting, formualas etc?

[Geetha M]

Hi Geogie,

Thank you for your interest in Syncfusion products.

Formulas can be inserted in any cell as done in the normal way. However, you should use default styles that would apply formatting easier and quicker. Please refer to our documentation in the link below.

ImprovingPerformance

Please try this and let me know if you have any questions.

Regards,
Geetha