When you try to iterate through many of the collections provided by Excel through VSTO, you may experience a significant performance hit, depending on your task.
In one recent case, a customer was iterating through the Names collection of an Excel Workbook. The workbook in question had 10,000 Named Range entries. They were looping through the collection and accessing the Name and RefersTo properties of each Name in the list. Because each call (property request or method) goes back through the VSTO and COM Wrapper layer, you may encounter a tremendous amount overhead.
In this particular case, they needed to iterate through the Names collection each time they performed a right-click and the result was a 14 second hang. Now imagine a scenario when you need to perform this every time the selection changes.
So, the best option I found, to improve performance is to PInvoke and go around VSTO and access the object directly and build a Dictionary of these two properties (Name/RefersTo). In the sample provided below, I was able trim 14 seconds to 0.6 seconds for 10,000 Named Ranges.
Here is the code which does this by implementing a Workbook Extension Method:
public static Dictionary<string,string> GetNamesAsDictionary(this Excel.Workbook wb)
{
try
{
// our return collection
Dictionary<string, string> myNames = new Dictionary<string, string>();
// use PInvoke to get the Names collection from the Workbook
object oNames = wb.GetType().InvokeMember("Names", BindingFlags.GetProperty, null, wb, null);
// get the total count of names
int iCnt = int.Parse(oNames.GetType().InvokeMember("Count", BindingFlags.GetProperty, null, oNames, null).ToString());
// loop through all the names by index...
for (int i = 1; i <= iCnt; i++)
{
object[] oParams = { i }; // parameter call for PInvoke
// get the specific name object at index (i)
object oName = oNames.GetType().InvokeMember("Item", BindingFlags.InvokeMethod, null, oNames, oParams);
// grab the name and range address...
string rangeName = oName.GetType().InvokeMember("Name", BindingFlags.GetProperty, null, oName, null).ToString();
string rangeAddress = oName.GetType().InvokeMember("RefersTo", BindingFlags.GetProperty, null, oName, null).ToString();
// add to our collection
myNames.Add(rangeName, rangeAddress);
}
// done - return
return myNames;
}
catch
{
// something bad happened - return null
return null;
}
}
To use this, you simple access it off the Workbook object, like this:
Dictionary<string, string> myNames = xlApp.ActiveWorkbook.GetNamesAsDictionary();