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

How to determine if an Excel Workbook is Embedded… and more…

When working with a Visual Studio Tools for Office (VSTO) project, you may want to have your add-in behave differently when the Active Workbook is inside an embedding. More specifically, the user has inserted an Excel sheet into a Word Document, for example, and has just double-clicked on it and you find that you need to detect this scenario. The trick is to know when you are in an embedded scenario and as it turns out, this is not easy to do. If you are an OLE guru, you might check the Container property on the Workbook Object:

However, this property always throws an Exception for Office Document types. It specifically calls out Internet Explorer, for a reason. This property does not work with OLE Embeddings in other Office documents. You can check the path length and as I have seen in many solutions hardcoding for “Workbook in…” but this fails for multi-language solutions.

What I found you have to do is access a series of OLE properties through C# to get the information you need. And as an added bonus, not only can you determine if Excel is embedded, but you can also get the Name of the class object it is embedded inside of and the Moniker / Filename of the file it is embedded in. I created the following Extension methods for Excel:

/// <summary>
/// EXTENSION METHOD CLASS FOR EXCEL
/// </summary>
public static class ExcelExtensionMethods
{
    [DllImport("ole32.dll")]
    static extern int CreateBindCtx(uint reserved, out IBindCtx ppbc);

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Checks to see if the Workbook is embeeded inside of 
    /// another ActiveX Document type, sy=uch as Word or Excel.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static bool IsEmbedded(this Excel.Workbook PobjWb)
    {
        if (PobjWb.Path == null || PobjWb.Path.Length == 0)
        {
            try
            {
                // requires using Microsoft.VisualStudio.OLE.Interop;
                // and you have to manually add this to reference from here:
                // C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.VisualStudio.OLE.Interop.dll
                IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
                IOleClientSite LobjPpClientSite;
                // get the client site
                LobjOleObject.GetClientSite(out LobjPpClientSite);
                // if there is one - we are embedded
                if (LobjPpClientSite != null)
                {
                    return true;
                }
                else
                {
                    // not embedded
                    return false;
                }
            }
            catch (Exception ex)
            {
                // exception
                Debug.Print(ex.ToString());
                return false;
            }
            finally { }
        }
        else
        {
            // not embedded
            return false;
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// This method return the name of the class that we
    /// are embedded inside of.
    /// If we are not embedded it return null.
    /// If there is any exception it return null.
    /// If the container cannot be accessed it returns UNKNOWN.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedClassName(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    return LobjPpContainer.GetType().Name;
                }
                else
                {
                    // something wrong - container is not valid
                    return "UNKNOWN";
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }

    /// <summary>
    /// WORKBOOK EXTENSION METHOD
    /// Get the full path to the file that the workbook is embedded 
    /// inside of. 
    /// If we are not embeeded then this will return null.
    /// If we are embedded but there are issues with the container
    /// or an exception occurs, it will return null.
    /// Otherwise we get the full path and filename.
    /// </summary>
    /// <param name="PobjWb"></param>
    /// <returns></returns>
    public static string EmbedMoniker(this Excel.Workbook PobjWb)
    {
        try
        {
            IOleObject LobjOleObject = ((object)PobjWb) as IOleObject;
            IOleClientSite LobjPpClientSite;
            // get the client site
            LobjOleObject.GetClientSite(out LobjPpClientSite);
            if (LobjPpClientSite != null)
            {
                IOleContainer LobjPpContainer;
                LobjPpClientSite.GetContainer(out LobjPpContainer);
                if (LobjPpContainer != null)
                {
                    // get the moniker
                    IMoniker LobjMoniker;
                    LobjPpClientSite.GetMoniker((uint)OLEGETMONIKER.OLEGETMONIKER_FORCEASSIGN,
                                                (uint)OLEWHICHMK.OLEWHICHMK_OBJFULL,
                                                out LobjMoniker);
                    if (LobjMoniker != null)
                    {
                        // now pull the moniker display name
                        // this will be in the form of PATH!Context
                        string LstrDisplayName;
                        IBindCtx LobjCtx = null;
                        CreateBindCtx(0, out LobjCtx); // required (imported function)
                        LobjMoniker.GetDisplayName(LobjCtx, null, out LstrDisplayName);
                        // remove context is exists
                        if (LstrDisplayName.Contains("!"))
                        {
                            string[] LobjMonikerArray = LstrDisplayName.Split('!');
                            // return the first part - which should be the path
                            return LobjMonikerArray[0];
                        }
                        else
                        {
                            // return full display name
                            return LstrDisplayName;
                        }
                    }
                    else
                    {
                        // no moniker value
                        return null;
                    }
                }
                else
                {
                    // something wrong - container is not valid
                    return null;
                }
            }
            else
            {
                // not embedded
                return null;
            }
        }
        catch (Exception ex)
        {
            Debug.Print(ex.ToString());
            return null; // failed
        }
    }
}

Pay close attention to the comments, because you will not find the OLE reference you need in the References window. You will have to browse to the path given and manually select it.

Now, to use it, I hook to the WorkbookActivate event, like this:

/// <summary>
/// STARTUP
/// </summary>
/// <param name="PobjSender"></param>
/// <param name="pObjEventArgs"></param>
private void ThisAddIn_Startup(object PobjSender, System.EventArgs pObjEventArgs)
{
    Application.WorkbookActivate += new Excel.AppEvents_WorkbookActivateEventHandler(Application_WorkbookActivate);
}

/// <summary>
/// Workbook Activate event fires when any workbook is activated.
/// </summary>
/// <param name="PobjWb"></param>
void Application_WorkbookActivate(Excel.Workbook PobjWb)
{
    // call the extension method created below
    if (PobjWb.IsEmbedded())
    {
        string LstrClass = PobjWb.EmbedClassName();
        string LstrPath = PobjWb.EmbedMoniker();
        MessageBox.Show("This workbook is embedded in a [" + LstrClass + "] " +
                        "with a path of: \n\n\t" + LstrPath);
    }
}

What you can see from the code above is that on WorkbookActivate, I check to see if the workbook is embedded and if it is, then I get the Class Name by calling my other Extension Method – EmbedClassName() – and then I get the path to the file it is embedded inside of (for example, the Word document path that contains the embedded Excel sheet) by calling yet another Extension Method – EmbedMoniker().

Creating a Loader Add-in (Master Add-in)

I have now helped about half a dozen customers over the last 3 years to perform this very same task. In each case the scenario is the same:

  • They have documents that are very specific to their system
  • They want to create an add-in to assist with the management of these documents.
  • They only want the add-in to load when one of their document is opened.
  • BONUS: They also want to remove other add-ins
  • BONUS: They would like a pristine instance of Word or Excel.

There are several methods to accomplish each of these tasks. But the one I find is easiest to maintain is what I call the “Master Add-in” approach.

In the “Master Add-in” you essentially attach one event: DocumentOpen or WorkbookOpen. You place code in the event to detect whether it is a document you care about. This can be done in several ways:

  • You can look at the path where the file came from.
  • You can look at the name of the file, if there is a specific naming convention you follow.
  • Or, you can tag he document with Document Properties or Custom Xml Parts.

Once you have identified that it is a document you care about you follow this basic process:

  • Get the path to the file being open and store it.
  • Close the workbook or document being passed into the Open event.
  • Create a new Instance of Word or Excel.
  • Iterate through the COM Add-ins collection and the Add-ins collection and disable everything you do not want running. This includes disabling the Master Add-in as you do not want it running for the next parts.
  • Locate your COM Add-in in the collection and set Connect = true.
  • Open the document or workbook from the application object.

You will now have two instances of Excel or Word open. One that the user was originally working with which has the Master Add-in loaded and then the new one that is customized with your specific add-in and only your add-in.

This is useful for when you have multiple versions of your system and you can update the Master to recognize which version to launch.

Getting this to work just right is sometimes a challenge and you have to be careful not to disable all the add-ins for all instances. I have a few customer examples that I have built and will work on cleaning them up. I will combine the best parts and will write a new entry in the future which will walk you through creating one.

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