0

I have a program that inserts data in excel and creates graphs. All the "number operations" are done in the program (with linq), so excel is only used for creating graphs. The final excel has around 300 worksheets (50 sheet with data and 250 graphs). And here is the performance problem. While the operations while linq and regex are very quickly, excel takes more than 4 minutes in generating all the graphs. I thought about parallel extensions and split the graphs in 5 excel files.

My question is if I´m losing my time. I mean: 250 graphs in 4 minutes is like 1 second per graph. Should excel in parallel generate graphs quicker than that? I´ve also noticed that excel (as most programs), works slower when the file is larger. If you have an excel of 10MB and inserts a graph it takes 1 second, and if you have an excel of 10KB it inserts at the moment (may be it´s only my impression...).

And if the answer is I should use parallel extensions, should I work with the same excel application and different excel files or better a different excel application for each file?

Anthon
  • 69,918
  • 32
  • 186
  • 246
Alberto
  • 704
  • 2
  • 12
  • 26
  • Is excel an absolute requirement? You may get better performance generating the charts with a 3rd party chart tool. Google Charts even https://developers.google.com/chart/ – Andrew Grothe Apr 04 '13 at 11:22
  • i guess the easiest way to find out its to actually write a sample and test it! –  Apr 04 '13 at 11:24
  • 1
    Or even the mschart control. (very similar to the Excel one) http://archive.msdn.microsoft.com/mschart – DasKrümelmonster Apr 04 '13 at 11:24
  • @DasKrümelmonster Cool! I´ll have a look at mschart. Do you know if are compatible all the excel chart options (i mean axis, series...) , and if it´s possible to insert the mschart as objects in a word file like excel charts? – Alberto Apr 04 '13 at 11:34
  • There are third party libraries which can create generic Excel-charts, like EPPLUS – Christian Sauer Apr 04 '13 at 11:37

1 Answers1

1

Trying to do this with Excel is going to lead to lots of pain. Use one of the many excellent .net charting libraries for your charts.

If for some other reason you must use Excel then you will need separate workbooks. Only one thread at a time can operate on a workbook. I believe that multiple threads can operate in parallel on multiple workbooks and while I've never done it myself, I see no reason why it should not work.

It's quite possible that your existing code could be speeded up significantly. Are you disabling screen updates whilst adding data to Excel? Is automatic re-calculation disabled? Are you pushing data onto Excel cell by cell, or an entire range in one go? Cell by cell is crushingly slow. You want to minimise the number of calls you make to Excel since the IPC cost of callin an out-of-proc COM server is significant.

David Heffernan
  • 601,492
  • 42
  • 1,072
  • 1,490
  • About .net charting libraries I´m considering now that option. It seems a very good option. Screen updating is disable and invisible. I have no formulas, and the data is pushed on an entire range on one go. The problem is drawing charts. It takes about 1 second per graph, but there are 250... About separate workbooks, yes, but same Excel.Application for each workbook or a different one for each workbook? – Alberto Apr 04 '13 at 11:40
  • @Alberto: I doubt Excel supports MTA (but I have no proof of that) so using the same excel application would just marshal all calls into the same STA thread, so you'd still be running singlethreaded. – Zarat Apr 04 '13 at 11:47
  • @Zarat You'd have to create a separate instance of the COM server for each thread. I think that's possible. – David Heffernan Apr 04 '13 at 11:48
  • @Alberto Judging by your comments, you should ditch Excel for sure. You can do this with no threads very easily and expect to process hundreds of charts per second. – David Heffernan Apr 04 '13 at 11:51
  • @DavidHeffernan I´ll try EPPLUS and mschart. Both seem great libraries! – Alberto Apr 04 '13 at 12:41