PInvoke Object Extender

If you have done work in Office (especially Excel) and performed operations that look across all the cells in the UsedRange, you might note – if you do a performance analysis of your code – that you will see up to 100ms per Object Model touch, especially for properties and methods from the Cell object. The issue here are all the checks and balances in the Primary Interops as well as the weight of .NET through COM. To avoid it you can Private Invoke (PInvoke) the Excel application directly and .NET does two things for you:

  1. It keeps the function table for the private interface cached so it does not always have to keep going and getting it for you.
  2. It allows you to interface with the EXE directly, bypassing several layers.

The end result, is usually something like a 100ms to 3ms performance improvement.

To help with PInvoke operations, I developed the following class that EXTENDS the OBJECT class type:

/// <summary>
/// This class extends object with a set of methods to help
/// PInvoke into COM objects much faster.
/// </summary>
public static class ObjectExtender
{
public static object InvokeMethod(this object PobjItem, string PstrMethod, object[] PobjMethodParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrMethod, BindingFlags.InvokeMethod, null, PobjItem, PobjMethodParams, null);
}
catch
{
return null;
}

}

public static object InvokeMethod(this object PobjItem, string PstrMethod)
{
return InvokeMethod(PobjItem, PstrMethod, null);
}

public static object GetProperty(this object PobjItem, string PstrProperty, object[] PobjParams)
{
try
{
return PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.GetProperty, null, PobjItem, PobjParams, null);
}
catch
{
return null;
}
}

public static object GetProperty(this object PobjItem, string PstrProperty)
{
return GetProperty(PobjItem, PstrProperty, null);
}

public static void SetProperty(this object PobjItem, string PstrProperty, object PobjValue)
{
try
{
object[] LobjVal = { PobjValue };
PobjItem.GetType().InvokeMember(PstrProperty, BindingFlags.SetProperty, null, PobjItem, LobjVal, null);
}
catch
{
return;
}
}

To use this code, you would do something like this:

LobjCell.InvokeMethod("Calculate");
object LobjVal = LobjCell.GetProperty("Validation");
object LobjFormula = LobjVal.GetProperty("Formula1");

Iterating through Names list Slow

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();