VSTO Speed Issues

Question

I've written a VSTO DLL to perform a transform on a user-specified range of cells. I'm doing this in the following manner:

Globals.ThisAddIn.Application.EnableEvents = false;

int totRows=inputRange.Rows.Count;
int totCols=inputRange.Columns.Count;

for (int i = 1; i <= totRows; i++) {
  for (int j = 1; j <= totCols; j++) {
    if (((Range)inputRange.Cells[i, j]).Value2 != null) {
      ((Range)outputSheet.Cells[i, j]).Value2 = MyTransform(((Range)inputRange.Cells[i, j]).Value2);
    }
  }
}

Globals.ThisAddIn.Application.EnableEvents = true;

This method allows me to modify approximately 1.5 million cells/hour (I'm not counting time consumed by MyTransform()), which seems to me to be very slow.

Is there a more efficient way to read/write cells in VSTO?

If not, would converting this to an XLL (likely using Excel-DNA) be any faster? Any guess as to how much?

I'd have to guess you are running this code on a thread to prevent the UI from freezing. In which case you cross an apartment boundary to access the cells and that's slow. Not disabling automatic cell calculations makes it very slow too. — Hans Passant, Jul 23 '12 at 14:57
No thread, the interface is more or less freezing for long runs. I'll try disabling auto-cell calculations, although there are no calculated cells (it is a list of names and addresses). — Marc Bernier, Jul 23 '12 at 15:32

score 4 · Accepted Answer · edited May 23 '17 at 10:09

You are making multiple COM calls for every cell that you are reading and setting. It is much more efficient to process whole ranges as arrays at once.

This question has an extensive discussion of the options: Fastest way to interface between live (unsaved) Excel data and C# objects

With Excel-DNA and the Excel C API you can read and write a million cells per second, but I'm guessing even via COM if you read and write as a large array the performance would be quite good.

VSTO Speed Issues

1 Answers1