Fastest way to interface between live (unsaved) Excel data and C# objects

Question

I want to know what the fastest way is of reading and writing data to and from an open Excel workbook to c# objects. The background is that I want to develop a c# application that is used from Excel and uses data held in excel.

The business logic will reside in the c# application but the data will reside in an Excel workbook. The user will be using Excel and will click a button (or do something similar) on the excel workbook to initiate the c# application. The c# application will then read data off the excel workbook, process the data, and then write data back to the excel workbook.
There may be numerous blocks of data that are required to be read off and written back to the excel workbook but they will normally be of a relatively small size, say 10 rows and 20 columns. Occasionally a large list of data may need to be processed, of the order of 50,000 rows and 40 columns.

I know that this is relatively easy to do say using VSTO but I want to know what the fastest (but still robust and elegant) solution is and get an idea of the speed. I don't mind if the solution recommends using third party products or uses C++.

The obvious solution is using VSTO or interop but I don't know what the performance is like versus VBA which I'm currently using to read in the data, or if there are any other solutions.

This was posted on experts exchange saying that VSTO was dramatically slower than VBA but that was a couple of years ago and I don't know if the performance has improved.

http://www.experts-exchange.com/Microsoft/Development/VSTO/Q_23635459.html

Thanks.

Answer 1

I'll take this as a challenge, and will bet the fastest way to shuffle your data between Excel and C# is to use Excel-DNA - http://excel-dna.net. (Disclaimer: I develop Excel-DNA. But it's still true...)

Because it uses the native .xll interface it skips all the COM integration overhead that you'd have with VSTO or another COM-based add-in approach. With Excel-DNA you could make a macro that is hooked up to a menu or ribbon button which reads a range, processes it, and writes it back to a range in Excel. All using the native Excel interface from C# - not a COM object in sight.

I've made a small test function that takes the current selection into an array, squares every number in the array, and writes the result into Sheet 2 starting from cell A1. You just need to add the (free) Excel-DNA runtime which you can download from http://excel-dna.net.

I read into C#, process and write back to Excel a million-cell range in under a second. Is this fast enough for you?

My function looks like this:

using ExcelDna.Integration;
public static class RangeTools {

[ExcelCommand(MenuName="Range Tools", MenuText="Square Selection")]
public static void SquareRange()
{
    object[,] result;
    
    // Get a reference to the current selection
    ExcelReference selection = (ExcelReference)XlCall.Excel(XlCall.xlfSelection);
    // Get the value of the selection
    object selectionContent = selection.GetValue();
    if (selectionContent is object[,])
    {
        object[,] values = (object[,])selectionContent;
        int rows = values.GetLength(0);
        int cols = values.GetLength(1);
        result = new object[rows,cols];
        
        // Process the values
        for (int i = 0; i < rows; i++)
        {
            for (int j = 0; j < cols; j++)
            {
                if (values[i,j] is double)
                {
                    double val = (double)values[i,j];
                    result[i,j] = val * val;
                }
                else
                {
                    result[i,j] = values[i,j];
                }
            }
        }
    }
    else if (selectionContent is double)
    {
        double value = (double)selectionContent;
        result = new object[,] {{value * value}}; 
    }
    else
    {
        result = new object[,] {{"Selection was not a range or a number, but " + selectionContent.ToString()}};
    }
    
    // Now create the target reference that will refer to Sheet 2, getting a reference that contains the SheetId first
    ExcelReference sheet2 = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, "Sheet2"); // Throws exception if no Sheet2 exists
    // ... then creating the reference with the right size as new ExcelReference(RowFirst, RowLast, ColFirst, ColLast, SheetId)
    int resultRows = result.GetLength(0);
    int resultCols = result.GetLength(1);
    ExcelReference target = new ExcelReference(0, resultRows-1, 0, resultCols-1, sheet2.SheetId);
    // Finally setting the result into the target range.
    target.SetValue(result);
}
}