ExcelDna/NetOffice/Excel: Most efficient way to insert values?

Question

I'm building an addin for Excel using ExcelDna and NetOffice and fetch data from various source and insert this into worksheets.

My problem is that the insertion of data in the worksheet is really slow.

What is the most efficient way of inserting a block of values into Excel?

ResultCell[,] result = _currentView.GetResult(values, cursor);

int loopM = result.GetUpperBound(0);
int loopN = result.GetUpperBound(1);

for (int y = 0; y <= loopM; y++)
{
    for (int x = 0; x <= loopN; x++)
    {
        int a = xlCurrentCell.Row + row;
        int b = xlCurrentCell.Column + x;
        Excel.Range xlCell = (Excel.Range)xlActiveSheet.Cells[a, b];
        _SetValue(xlCell, result[y, x]);
    }
    row++;
}

...

private void _SetValue(Excel.Range xlCell, ResultCell cell)
{
    xlCell.ClearFormats();
    object value = cell.Value;

    if (value is ExcelError)
    {
        value = ExcelUtils.ToComError((ExcelError) value);
    }
    else if (ExcelUtils.IsNullOrEmpty(value))
    {
        value = "";
    }

    xlCell.Value = value;
    if (!string.IsNullOrEmpty(cell.NumberFormat))
    {
        xlCell.NumberFormat = cell.NumberFormat;
    }
}

Can the problem be that every insert becomes a screen update? I've tried with:

Excel.Application xlApp = new Excel.Application(null, ExcelDna.Integration.ExcelDnaUtil.Application);

xlApp.ScreenUpdating = false;

But that didn't have any effect.

Answer 1

Setting data per cell is slow. You can set the value of a large range to an array of values, and this will be immensely faster.

Temporarily switching off screen updating and recalculation will help too.

For lengthy and detailed discussions, including code for using the C API from you Excel-DNA add-in, see Fastest way to interface between live (unsaved) Excel data and C# objects