Introduction

When you copy some 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 Range object representing copied cells? Use CF_LINKSOURCE!  

Background  

In our company we’re working now on the project named Converter that imports data from Excel to our applications. So user has to drag or copy/paste some cell range from Excel to Converter and setup data transfer scheme (from which cell to which destination data will be transferred; whether first data row contains fieldnames etc). Finally he 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 Range layout, cell names etc so you need Range object to get it. This article describes how one can do this by using IStream interface from CF_LINKSOURCE clipboard format.  

Main algorithm 

Main algorithm is as simple as the GetRange method from 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 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 previous step to OleLoadFromStream and...get 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 object from OleLoadFromStream that successfully casts to IMoniker.

Getting Range

If you take a look at 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 Range object. But in real life monikers from Microsoft Office can be bound only to the file object (Workbook in case of Excel) so we need to split 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 List with file moniker and item moniker. To bind to workbook we need 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 call to item moniker’s BindToObject gets us an error (practically we can do successful call to BindToObject with the IID of IUnknown but returning object would actually be the workbook object, sad but true.) Game Over? Not so fast. We still can get display name from item moniker with IMoniker.GetDisplayName(). For Excel Range it will be something like “!blahblahblah!R1C1:R3C3” where blahblahblah is a sheet name and R1C1:R3C3 identifies range inside a sheet. I wrote a helper class that parses DisplayName and gets the Range from Workbook object. The only interesting moments are:
1. 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 current Excel Reference Style (mostly A1) to get the Range. 

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 item moniker’s BindToObject really works :) 

Tag getting,excel,range,object,from,clipboard,through,istream,interface
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架