Setting a Range of Cells in One Call


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;

}

Comments (3)

  1. Brien says:

    Hi Eric,

    Is there any way to speed up what’s happening under the covers when you make the call ‘theRange.Value2 = theValues’. I’ve found that for large arrays there seems to be a lot of overhead in the call which I’m guessing is due to the overhead of marshalling the data from .NET to COM to whatever format Excel uses natively.

    If I want to do this is the fastest way possible, are there any tricks?

    Thanks,

    Brien

  2. Eric Carter says:

    I’m not aware of any tricks other than to try to minimize the number of calls from .NET to COM when possible. You are always going to pay an interop cost when moving from .NET to COM. You might look at something like Adam Nathan’s book on interop and see if there are any additional hints there, but I haven’t heard of anything.