Excel Interop - Efficiency and Performance

Faster way to access Range using Excel.Interop

As far as I understand your question there is no "FAST" way when choosing .Range[_xlWorksheet.Cells[1, 1], _xlWorksheet.Cells[10, 10]] or Range["A1", "J10"] They are the same.

In Excel, you can refer to a range, say A1:A10, in some of these ways

Debug.Print Sheets("Sheet1").Range("A1:A10").Address
Debug.Print Sheets("Sheet1").Range(Sheets("Sheet1").Range("A1"), Sheets("Sheet1").Range("A10")).Address
Debug.Print Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), Sheets("Sheet1").Cells(10, 1)).Address

Choosing one of the above will NOT determine the performance. What WILL determine the performance is HOW you read/write to them

C# Excel Interop Slow when looping through cells

Excel and C# run in different environments completely. C# runs in the .NET framework using managed memory while Excel is a native C++ application and runs in unmanaged memory. Translating data between these two (a process called "marshaling") is extremely expensive in terms of performance.

Tweaking your code isn't going to help. For loops, string construction, etc. are all blazingly fast compared to the marshaling process. The only way you are going to get significantly better performance is to reduce the number of trips that have to cross the interprocess boundary. Extracting data cell by cell is never going to get you the performance you want.

Here are a couple options:

1. Write a sub or function in VBA that does everything you want, then call that sub or function via interop. Walkthrough.

2. Use interop to save the worksheet to a temporary file in CSV format, then open the file using C#. You will need to loop through and parse the file to get it into a useful data structure, but this loop will go much faster.

3. Use interop to save a range of cells to the clipboard, then use C# to read the clipboard directly.

Efficient way to write excel from C#

If you're using Excel 2007 or higher, the best option is Open XML SDK 2.0

You can also modify the current method. Instead of writing each value individually, create a two-dimensional array to hold all the values. Then get a range of the same size and set the value for the range to be the two-dimensional array. That way you only suffer the cost of one marshalled COM call instead of the many, many you were dealing with.

Excel Interop Efficiency get row number with value in a column

Here's how I'd do it:

• Get a Range corresponding to the column you're interested in
• Get the UsedRange of the sheet you're interested in
• Get a Range that is the intersection of the above two ranges
• Get the value of this Range, which will be an array of values from the column you're interested in

You can then iterate through this array to find the value you want, then use its index to derive the row number.

The number of calls to the Excel is O(1) with the above method, as opposed to O(n) in your version.

How can I speed-up creating excel Excel spreadsheets from C#?

You can get rid of COM Interop altogether, by using EPPlus (here). This library creates Excel files by using the 2007 XML format, not the Excel COM interface, and is a lot quicker as a result.

Excel Interop : Can you listen for a change in range value?

I don't think Range object has a Change event. You can listen to the Worksheet Change event and probably do a bit more checking to figure out if the change is the one you want to catch.

---

Related Topics