Fastest way to interface between live (unsaved) Excel data and C# objects
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);
}
}
If the C# application is a stand-alone application, then you will always have cross-process marshaling involved that will overwhelm any optimizations you can do by switching languages from, say, C# to C++. Stick to your most preferred language in this situation, which sounds like is C#.
If you are willing to make an add-in that runs within Excel, however, then your operations will avoid cross-process calls and run about 50x faster.
If you run within Excel as an add-in, then VBA is among the fastest options, but it does still involve COM and so C++ calls using an XLL add-in would be fastest. But VBA is still quite fast in terms of calls to the Excel object model. As for actual calculation speed, however, VBA runs as pcode, not as fully compiled code, and so executes about 2-3x slower than native code. This sounds very bad, but it isn't because the vast majority of the execution time taken with a typical Excel add-in or application involves calls to the Excel object model, so VBA vs. a fully compiled COM add-in, say using natively compiled VB 6.0, would only be about 5-15% slower, which is not noticeable.
VB 6.0 is a compiled COM approach, and runs 2-3x faster than VBA for non-Excel related calls, but VB 6.0 is about 12 years old at this point and won't run in 64 bit mode, say if installing Office 2010, which can be installed to run 32 bit or 64 bit. Usage of 64 bit Excel is tiny at the moment, but will grow in usage, and so I would avoid VB 6.0 for this reason.
C#, if running in-process as an Excel add-in would execute calls to the Excel object model as fast as VBA, and execute non-Excel calls 2-3x faster than VBA -- if running unshimmed. The approach recommended by Microsoft, however, is to run fully shimmed, for example, by making use of the COM Shim Wizard. By being shimmed, Excel is protected from your code (if it's faulty) and your code is fully protected from other 3rd party add-ins that could otherwise potentially cause problems. The down-side to this, however, is that a shimmed solution runs within a separate AppDomain, which requires cross-AppDomain marshaling that incurrs an execution speed penalty of about 40x -- which is very noticeable in many contexts.
Add-ins using Visual Studio Tools for Office (VSTO) are automatically loaded within a shim and executes within a separate AppDomain. There is no avoiding this if using VSTO. Therefore, calls to the Excel object model would also incur an approximately 40x execution speed degradation. VSTO is a gorgeous system for making very rich Excel add-ins, but execution speed is its weakness for applications such as yours.
ExcelDna is a free, open source project that allows you to use C# code, which is then converted for you to an XLL add-in that uses C++ code. That is, ExcelDna parses your C# code and creates the required C++ code for you. I've not used it myself, but I am familiar with the process and it's very impressive. ExcelDna gets very good reviews from those that use it. [Edit: Note the following correction as per Govert's comments below: "Hi Mike - I want add a small correction to clarify the Excel-Dna implementation: all the managed-to-Excel glue works at runtime from your managed assembly using reflection - there is no extra pre-compilation step or C++ code generation. Also, even though Excel-Dna uses .NET, there need not be any COM interop involved when talking to Excel - as an .xll the native interface can be used directly from .NET (though you can also use COM if you want). This makes high-performance UDFs and macros possible." – Govert]
You also might want to look at Add-in Express. It's not free, but it would allow you to code in C# and although it shims your solution into a separate AppDomain, I believe that it's execution speed is outstanding. If I am understanding its execution speed correctly, then I'm not sure how Add-in Express doing this, but it might be taking advantage of something called FastPath AppDomain marshaling. Don't quote me on any of this, however, as I'm not very familiar with Add-in Express. You should check it out though and do your own research. [Edit: Reading Charles Williams' answer, it looks like Add-in Express enables both COM and C API access. And Govert states that Excel DNA also enables both COM and the fastrer C API access. So you'd probably want to check out both and compare them to ExcelDna.]
My advice would be to research Add-in Express and ExcelDna. Both approaches would allow you to code using C#, which you seem most familiar with.
The other main issue is how you make your calls. For example, Excel is very fast when handling an entire range of data passed back-and-forth as an array. This is vastly more efficient than looping through the cells individually. For example, the following code makes use of the Excel.Range.set_Value accessor method to assign a 10 x 10 array of values to a 10 x 10 range of cells in one shot:
void AssignArrayToRange()
{
// Create the array.
object[,] myArray = new object[10, 10];
// Initialize the array.
for (int i = 0; i < myArray.GetLength(0); i++)
{
for (int j = 0; j < myArray.GetLength(1); j++)
{
myArray[i, j] = i + j;
}
}
// Create a Range of the correct size:
int rows = myArray.GetLength(0);
int columns = myArray.GetLength(1);
Excel.Range range = myWorksheet.get_Range("A1", Type.Missing);
range = range.get_Resize(rows, columns);
// Assign the Array to the Range in one shot:
range.set_Value(Type.Missing, myArray);
}
One can similarly make use of the Excel.Range.get_Value accessor method to read an array of values from a range in one step. Doing this and then looping through the values within the array is vastly faster than looping trough the values within the cells of the range individually.
Further to Mike Rosenblum's comments on the use of arrays, I'd like to add that I've been using the very approach (VSTO + arrays) and when I measured it, the actual read speed itself was within milliseconds. Just remember to disable event handling and screen updating prior to the read/write, and remember to re-enable after the operation is complete.
Using C#, you can create 1-based arrays exactly the same as Excel VBA itself does. This is pretty useful, especially because even in VSTO, when you extract the array from an Excel.Range object, the array is 1-based, so keeping the Excel-oriented arrays 1-based helps you avoid needing to always check for whether the array is one-based or zero-based. (If the column position in the array has significance to you, having to deal with 0-based and 1-based arrays can be a real pain).
Generally reading the Excel.Range into an array would look something like this:
var myArray = (object[,])range.Value2;
My variation of Mike Rosenblum's array-write uses a 1-based array like this:
int[] lowerBounds = new int[]{ 1, 1 };
int[] lengths = new int[] { rowCount, columnCount };
var myArray =
(object[,])Array.CreateInstance(typeof(object), lengths, lowerBounds);
var dataRange = GetRangeFromMySources();
// this example is a bit too atomic; you probably want to disable
// screen updates and events a bit higher up in the call stack...
dataRange.Application.ScreenUpdating = false;
dataRange.Application.EnableEvents = false;
dataRange = dataRange.get_Resize(rowCount, columnCount);
dataRange.set_Value(Excel.XlRangeValueDataType.xlRangeValueDefault, myArray);
dataRange.Application.ScreenUpdating = true;
dataRange.Application.EnableEvents = true;