CODEWITHSUNDEEP
c#excelparallel-processingparallel-extensions
Alberto
Alberto

Reputation: 704

Excel in parallel

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?

Upvotes: 0

Views: 1922

Answers (1)

David Heffernan
David Heffernan

Reputation: 613451

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.

Upvotes: 1

Related Questions