0

As assigning to each cell in a large sheet via C# code using Excel API can be raelly inefficient, I am trying to assign to a range of cells. However, Range.Value2 only works a multi-dimensional array. In my case, I have a List of strings. Is there a way to do this? Converting back and forth between list and arrays can be inefficient.

thanks

user236215
  • 7,278
  • 23
  • 59
  • 87
  • Converting back and forth between lists and arrays of strings is *extremely* efficient in .NET, since the strings don't have to be copied, just the pointers (they are references to immutable objects). The cost of this will be tiny compared to the fact that the strings actually have to be copied (probably more than once) on the way into Excel. Also have a look here: http://stackoverflow.com/questions/3840270/fastest-way-to-interface-between-live-unsaved-excel-data-and-c-sharp-objects/3868370#3868370 – Govert Nov 13 '12 at 23:06

1 Answers1

1

The cost of Excel interop is the overhead of making a call into an out-of-process component. Work needs to be done to marshal the data across the process boundary. But that's a constant cost that depends on the amount of data. Less data is better but that's not a typical choice you have.

So the true cost is in the overhead of making the call in the first place. It is very substantial compared to the cost of making an in-process method call. Which takes a handful of cpu cycles, at worst. An out-of-process call requires two thread context switches, something that costs between 2000 and 10000 cycles at minimum, just for the processor overhead. You're on the high end of that one because the thread that needs to run inside Excel is owned by another process so the processor caches are junk. Add the latency of that thread responding, highly unpredictable.

So you've got a budget of tens of thousands of cpu cycles to make the conversion and get ahead. Spend them wisely. And of course, actually try it, nobody can give you a guarantee.

Hans Passant
  • 922,412
  • 146
  • 1,693
  • 2,536
  • Thanks. Thats why assigning to a range of cells will be a lot more efficient than assigning to a cell at a time, esp for a large tables like in my case. So my question remains how can I efficiently assign to a range of cells using Excel API without having to use multi dimensional arrays [,] – user236215 Nov 10 '12 at 02:54
  • You didn't make it to the last sentence in the answer, did you? – Hans Passant Nov 10 '12 at 05:54
  • Actually I did and don't see an answer. but thanks for your time. – user236215 Nov 10 '12 at 18:28