In C #, how to use Excel Interop to speed up writing multiple cell values

Question

I have hardware for which I get 30 data points. Each of these points is written to the spreadsheet in several different places before the sheet becomes visible, then another program takes the top Excel spreadsheet. All these values ​​are required to be written to the spreadsheet before another program moves to another. If I write each cell individually, the recording takes about 50 ms, which takes about 1.25 seconds to complete the data collection.

If I could write all the values ​​in a table at a time, I feel that it will greatly speed up the writing of all these cells. The problem that I see is that ranges work very well for updating adjacent cells, where, since my data is not adjacent. Essentially, this will be an example of what I want to write:
A1 = 1
B23 = a
F8 = 2012/12/25
D53 = 4.1235
B2 = 5

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

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

Thanks Tom

Answer 1

If your architecture allows, another idea uses a hidden sheet with a continuous rectangular range, names its parts and uses these names on all other sheets.

Answer 2

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

Answer 3

You can write values ​​to adjacent cells that are somewhere on the side, for example, in column X, and have formulas in the target cells that belong to these updated cells. Thus, cell A1 will have the formula "= X1", cell B23 "= X2", etc.