3

I am working with Excel Interop in C# and am writing code to do somewhat complicated manipulation of tables with tens of thousands of rows of data. It tends to run pretty slow.

I often have data stored in some kind of variables that I perform some intermediate calculations on before ultimately writing the results to a worksheet.

I am sure this is not a good approach, but I frequently find myself building a temporary worksheet that will be deleted when my code is finished executing, so that I can use Excel methods of the Range property on the data rather than trying to implement similar methods in an array. Most frequently, this involves things like sorting by multiple columns, removing duplicates, etc. These columns almost always have many different types of data (strings, whole numbers, numbers with two decimal places - although the actual data types stored in these cells does not seem to always be straightforward (100000000.01 for example seems to sometimes be of type double and other times be decimal). The easiest way to deal with all of the different data types seems to be just using an Excel range, and this is why my code is frequently creating temporary worksheets, then deleting them in my code.

What is the proper, efficient way to deal with these types of situations?

svick
  • 236,525
  • 50
  • 385
  • 514
Jeremy S.
  • 25
  • 3
  • I have never found an 'efficient' way to deal with Excel interop - I'm curious to see what others can add. – Mike Aug 04 '14 at 17:50
  • 1
    Did you try using LINQ with a custom object implementing `IComparer`and `IEqualityComparer`? – Lucas Trzesniewski Aug 04 '14 at 18:09
  • Have you thought about using a library that manipulates the XML directly rather than going through COM to a running Excel process? Seems like this would at least cut out one middle man. – mason Aug 04 '14 at 20:49
  • 1
    Excel Interop is slow. You should try an Excel library for .NET. – alex.pulver Aug 05 '14 at 11:58
  • It really depends on what you are doing. If you want to remove duplicates then load the range into a collection that already has a .RemoveDuplicate or similar built in. If you want to sort, use .AutoFilter or again load the range into a collection. This question is too broad to answer, narrow it down if you can –  Aug 05 '14 at 12:34
  • and feel free to check out my other answers to kind of a similar topic. [**HERE**](http://stackoverflow.com/questions/22760591/convert-excel-range-to-array-using-c-sharp/22762413#22762413), [**HERE**](http://stackoverflow.com/questions/24677342/apply-autofilter-in-an-excel-if-character-length-of-cell-is-greater-than-a-limi), and [**HERE**](http://stackoverflow.com/questions/22791116/how-to-add-values-to-a-spreadsheet-from-a-dictionary/22804788#22804788) –  Aug 05 '14 at 12:34
  • Having experienced lots of pain using Excel Interop, I would highly recommend you use the EPPlus library instead. http://epplus.codeplex.com/ The API is good and it's fast. – Steve Oct 30 '14 at 08:08

1 Answers1

1

Take a look at these links

  1. http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx
  2. Excel Interop - Efficiency and performance
  3. http://xldennis.wordpress.com/2012/01/30/best-practices-export-data-to-excel/

If you are updating individual cells that is going to be very slow. But in general, excel interop is slow. Have you considered maybe creating a CSV file, and then using excel interop to convert the final result?

Community
  • 1
  • 1
Mez
  • 4,666
  • 4
  • 29
  • 57