I have written a VSTO plugin in C# which populates several worksheets.
During the population process cell values or formula are added one at a time according to user-definable criteria taken from a hidden configuration worksheet.
Typically there are 200 rows with around 50 columns, containing SUMPRODUCT formula, VLOOKUPs, etc.
The cells are also formatted and some have conditional formatting.
You're going to say "that sounds slow" - right? You are correct.
Even with the display and auto calculation switched off during population, this still takes forever on my client's standard workstation (i5 processor, 4GB ram).
For example, adding 10 rows usually takes around 15 minutes on those workstations, but on my dev PC (i7 processor, 16gb ram) I can do 200 rows in 2 minutes.
I appreciate that the speed issue is most probably related to the many RPC calls which take place while each cell is modified.
Consequently I have been searching for a way to this more efficiently and have found a way to populate an object array with values, then copy these en-mass to a worksheet.
Unfortunately there is no such option when the worksheet contains mostly formula, and it doesn't address the formatting issues.
What I'm wondering is, are there any alternatives here? Is there a way to deactivate the RPC calls while the worksheet is built? Is there an object I can inherit from that I can use in an array to build an off-line copy of the worksheet and then copy en-mass to the worksheet?
I'm pretty sure that any suggestion you can supply will add to my reservoir of ideas, so grateful to all who can chip-in with valuable info!