Getting the Excel Range object from the Clipboard through the IStream interface

• Download sample - 60.8 KB

Introduction

When you copy selected cells from Excel to Clipboard, you have a couple of data formats in it. If you need to paste just data, then even CF_CSV may satisfy. But what to do if you want to access the Range object representing the copied cells? Use CF_LINKSOURCE!

Background

In our company, we're now working on a project named Converter that imports data from Excel to our applications. The user has to drag or copy/paste some cell range from Excel to Converter and setup a data transfer scheme (from which cell to which destination data will be transferred; whether the first data row contains fieldnames, etc.). Finally, the user gets the scheme that could be used to transfer data from as many Excel Workbooks as needed (of course, Workbooks must have the same layout). Creating such a scheme requires some info about the Range layout, cell names, etc., so you need the Range object to get it. This article describes how we can do this by using the IStream interface from the CF_LINKSOURCE Clipboard format.

Main algorithm

The main algorithm is as simple as the GetRange method from the sample code:

public static Range GetRange(IDataObject dataObject)
{ 
    IStream iStream = IStreamFromDataObject(dataObject); 
    IMoniker compositeMoniker = IMonikerFromIStream(iStream); 
    return RangeFromCompositeMoniker(compositeMoniker); 
}

Don't get confused with IDataObject - it's a System.Runtime.InteropServices.ComTypes.IDataObject rather than System.Windows.Forms.IDataObject.

Inside look

Getting IMoniker

IStreamFromDataObject is simple and I'll skip it. To get IMoniker from IStream, we need the P/Invoked function OleLoadFromStream from ole32.dll.

[DllImport("ole32.dll", CharSet = CharSet.Unicode, ExactSpelling = true)]
public static extern HRESULT OleLoadFromStream( 
                        IStream pStm, 
                        [In] ref Guid riid, 
                        [MarshalAs(UnmanagedType.IUnknown)] out object ppvObj);

HRESULT is a struct that had been taken from pinvoke.net.

You pass the stream from the previous step to OleLoadFromStream and... get an error! It takes some time to figure out that you need to rewind a stream! OK, let's do that.

iStream.Seek(0, 0, IntPtr.Zero);

And voila! We get the object from OleLoadFromStream that successfully casts to IMoniker.

Getting Range

If you take a look at the CLSID of the moniker, you'll see that it's a composite moniker. If we lived in a perfect world, moniker.BindToObject() would give us the Range object. But in real life, monikers from Microsoft Office can be bound only to the file object (Workbook in the case of Excel), so we need to split the composite moniker and do some work for Excel.

private static List<IMoniker> SplitCompositeMoniker(IMoniker compositeMoniker)
{
    if (compositeMoniker == null)
        throw new ArgumentNullException("compositeMoniker", 
                  "compositeMoniker is null.");

    List<IMoniker> monikerList = new List<IMoniker>();

    IEnumMoniker enumMoniker;
    compositeMoniker.Enum(true, out enumMoniker);
    if (enumMoniker != null)
    {
        IMoniker[] monikerArray = new IMoniker[1];
        IntPtr fetched = new IntPtr();
        HRESULT res;
        while (res = enumMoniker.Next(1, monikerArray, fetched))
        {
            monikerList.Add(monikerArray[0]);
        }
        return monikerList;
    }
    else
        throw new ApplicationException("IMoniker is not composite");
}

Now we get the List with the file moniker and item moniker. To bind to the Workbook, we need to just call IMoniker.BindToObject:

IBindCtx bindctx;
if (!ole32.CreateBindCtx(0, out bindctx) || bindctx == null)
    throw new ApplicationException("Can't create bindctx");
object obj;
Guid workbookGuid = Marshal.GenerateGuidForType(typeof(Workbook));
monikers[0].BindToObject(bindctx, null, ref workbookGuid, out obj);
Workbook workbook = obj as Workbook;

But a call to the item moniker's BindToObject gets us an error (practically, we can do a successful call to BindToObject with the IID of IUnknown but the returning object would actually be the Workbook object, sad but true.) Game over? Not so fast. We still can get the display name from the item moniker with IMoniker.GetDisplayName(). For Excel Range, it will be something like "!blahblahblah!R1C1:R3C3" where blahblahblah is the sheet name and R1C1:R3C3 identifies the range inside the sheet. I wrote a helper class that parses DisplayName and gets the Range from the Workbook object. The only interesting moments are:

1. The user can copy whole rows or whole columns that are identified as "Rx:Ry" and "Cx:Cy", respectively.
2. You must convert R1C1 names to the current Excel Reference Style (mostly A1) to get the Range.

The helper can be used as follows:

ExcelItemMonikerHelper helper = new ExcelItemMonikerHelper(monikers[1], bindctx);
Range range = helper.GetRange(workbook);

So finally, we get Range as if the item moniker's BindToObject really works :)