Setting a Range of Cells in One Call

  • Article

A reader writes:

 

I've enjoyed reading your online blog about Excel/Dotnet integration. I have a question, that if anyone would know the answer to, hopefully you do.

 

Is there any trick that can be used to speed up insertion of large blocks of data into a worksheet from dotnet? For example, I may have a large rectangular array I retrieved as the result of a database query that I set into the Value of a range.

 

Here’s how you do this.  You need to create an object array dimensioned to the size of the block you want to set.  Populate the object array.  You also need to get a Range object that spans the size of block.  Then you use the Value2 property and set it to your object array.

 

Here’s an example that does this:

 

private void Sheet1_Startup(object sender, System.EventArgs e)

{

      int numberOfRows = 10;

      int numberOfColumns = 10;

      object[,] theValues = new object[numberOfRows, numberOfColumns];

      for (int i = 0; i < numberOfRows; i++)

      {

            for (int j = 0; j < numberOfColumns; j++)

            {

                  theValues[i, j] = i * j;

            }

      }

      Excel.Range theRange = this.Range["A1", "J10"];

      theRange.Value2 = theValues;

}