In C#, how do I use the Excel Interop to speed up writing several cell values

Question

I have a piece of hardware for which I am getting 30 data points. Each of these points is recorded in a spreadsheet at several different places before the sheet is made visible, then another program takes over the excel spreadsheet. It is required all these values are written to the spreadsheet before the other program takes over. If I write each cell individually, the writes are taking approximately 50ms, which takes about 1.25 seconds to complete the data acquisition.

If I could write all the values to the spreadsheet at one time, I feel this will significantly speed up the writing of all these cells. The problem I see is that Ranges work very well for updating contiguous cells where as my data isn't contiguous. Essentially, this would be an example of what I want to write:
A1 = 1
B23 = a
F8 = 2012/12/25
D53 = 4.1235
B2 = 5

I have tried creating a range of "A1,B23,F8,D53,B2", then set the values using an array of values. I tried 3 different arrays: object[5], object[1,5], and object[5,1]. These all set the values of the specified cells in the range to the first index of the array I created in all cases.

Is there a way to update these 30 cells data without iterating through the cells one at a time?

Thanks, Tom

Answer 1

If your architecture would permit, another idea is using a hidden sheet with a continuous rectangular range, set names to its parts and use these names on all other sheets.

Answer 2

You could write the values to contiguous cells that are somewhere out of the way, say in column X, and have formulae in the target cells that refer to these updated cells. So cell A1 would have the formula "=X1", cell B23 "=X2" and so on.

Answer 3

I would define a rectangular range object that includes all the cells whose values you want to modify. Get a rectangular object[,] array from that range's value property. Write the new values to the array, and then set the range's value using the modified array.