Visual C# Setting multiple cells at once with Excel Interop

3
votes

I wrote a c# app, which was one of my first (I am a noob here)! The app worked with Excel Interop and set a lot of cells which takes an extremely long time. After that I heard that it is possible to set a range of cells in just one call. That's what I'm trying to do and although there are several threads regarding this topic, none of them worked for me or I did not get it.

When you google, most people will suggest this method:

Excel.Range rng = (Excel.Range)xlWorkSheet.get_Range(xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[3, 3]);
rng.Value = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };

Or at least something with get_Range. But my problem is, I cannot execute get_Range, neither on worksheet nor on my workbook or initialized excel app. The method does not exist and I feel like what I found on the internet is all outdated!!!

I'm really hoping somebody can help me through this :) I would like to set a row or if it is not too complicated a whole sheet at once.

1
c#excelvisual-studio

1 Answers

6
votes

At least as of version 15.0 of the Excel object library, the get_Range() method has been removed and replaced with a Range property. This property can be used to perform the same functionality as was previously provided by get_Range().

Also note that setting the value to a 1-dimensional array will result in output like the following, which probably isn't what you want:

Bad output

The following code fixes both issues:

var rng = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[3, 3]];
rng.Value = new int[,] { { 1, 2, 3 }, { 4, 5, 6 }, { 7, 8, 9 } };

This results in output as follows:

Good output