c# excel interop performance loading data

Question

I have an excel interop project that is being runned from a ribbon. My issue is that when loading data of 64k with 30 columns it is insanely slow. So I created another set of projects, one project that creates and launches an excel sheet from winforms window via button click and loads 100k rows just fine, the other project is an excel add in project created from visual studio. Loading data in the add in is incredibly slow. from examing it while loading rows which is done by creating a range on the sheet and setting 2 dimensional array to that range takes minutes bec there seems to be threads exiting during the process. Can anyone help me get clarity or a way to speed up the process. Thanks Code to where I am setting value to the sheet:

public static Interop.Range AddData(DataTable dataTable, Interop.Worksheet sheetToAddTo, int rowOffset) {
        Interop.Range tableRange = sheetToAddTo.Range[sheetToAddTo.Cells[1 + rowOffset, 1], sheetToAddTo.Cells[1 + rowOffset, 1]];
        if (dataTable != null && dataTable.Columns.Count > 0)
        {
            //create the object to store the column names
            object[,] columnNames;
            columnNames = new object[1, dataTable.Columns.Count];

            //add the columns names from the datatable
            for (int i = 0; i < dataTable.Columns.Count; i++)
                columnNames[0, i] = dataTable.Columns[i].ColumnName;

            //get a range object that the columns will be added to
            Interop.Range columnsNamesRange = (Interop.Range)sheetToAddTo.Range[sheetToAddTo.Cells[1 + rowOffset, 1], sheetToAddTo.Cells[1 + rowOffset, dataTable.Columns.Count]];

            //a simple assignement allows the data to be transferred quickly
            columnsNamesRange.Value2 = columnNames;

            //release the columsn range object now it is finished with
            columnsNamesRange = null;

            if (dataTable.Rows.Count > 0)
            {
                //create the object to store the dataTable data
                object[,] rowData;
                rowData = new object[dataTable.Rows.Count, dataTable.Columns.Count];

                //insert the data into the object[,]
                for (int iRow = 0; iRow < dataTable.Rows.Count; iRow++)
                    for (int iCol = 0; iCol < dataTable.Columns.Count; iCol++)
                        rowData[iRow, iCol] = dataTable.Rows[iRow][iCol];

                //get a range to add the table data into 
                //it is one row down to avoid the previously added columns
                Interop.Range dataCells = (Interop.Range)sheetToAddTo.Range[sheetToAddTo.Cells[2 + rowOffset, 1], sheetToAddTo.Cells[dataTable.Rows.Count + rowOffset + 1, dataTable.Columns.Count]];

                //assign data to worksheet
                dataCells.Value2 = rowData;

                //release range
                dataCells = null;
            }

            //return the range to the new data
            if (dataTable.Rows.Count > 0)
                tableRange = sheetToAddTo.Range[sheetToAddTo.Cells[1 + rowOffset, 1], sheetToAddTo.Cells[dataTable.Rows.Count + rowOffset + 1, dataTable.Columns.Count]];
            else
                tableRange = sheetToAddTo.Range[sheetToAddTo.Cells[1 + rowOffset, 1], sheetToAddTo.Cells[1 + rowOffset + 1, dataTable.Columns.Count]];
        }
        return tableRange;
    }

Answer 1

Most of the times, this is an issue with interop calls.

You shouldn't do this:

for (int col = 0; col < 30 ; col++)
{
    for (int row = 0; row < 30 ; row++)
    {
        object theValue = range.Cells[row,col].Value;
    }
}

but:

object[,] allValues = range.Value;

This increases performance a lot.