Click here to Skip to main content
15,868,016 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel

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

Rate me:
Please Sign up or sign in to vote.
4.83/5 (5 votes)
25 Jan 2011CPOL3 min read 37.3K   650   10   12
This article demonstrates how to get the Excel Range object from the Clipboard using the CF_LINKSOURCE Clipboard format.

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:

C#
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.

C#
[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.

C#
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.

C#
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:

C#
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:

C#
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 :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
Russian Federation Russian Federation
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionNon-contiguous range detection? Pin
Member 1197140421-Aug-18 6:18
Member 1197140421-Aug-18 6:18 
AnswerRe: Non-contiguous range detection? Pin
hymced21-Feb-19 0:16
hymced21-Feb-19 0:16 
GeneralRe: Non-contiguous range detection? Pin
hymced1-Dec-20 8:07
hymced1-Dec-20 8:07 
AnswerDuplicate Pin
Clifford Nelson3-Jul-13 12:02
Clifford Nelson3-Jul-13 12:02 
GeneralRe: Duplicate Pin
Alexey Merson3-Jul-13 19:54
Alexey Merson3-Jul-13 19:54 
AnswerRe: Duplicate Pin
Clifford Nelson5-Jul-13 9:22
Clifford Nelson5-Jul-13 9:22 
GeneralRe: Duplicate Pin
Alexey Merson5-Jul-13 9:54
Alexey Merson5-Jul-13 9:54 
I can't get what you trying to tell me. Do you think I posted this article on cyqdata.com after codeproject.com? Or do you think I copypasted someone's article? Or what? Did you ever noticed article date here and there?
AnswerRe: Duplicate Pin
Clifford Nelson5-Jul-13 10:16
Clifford Nelson5-Jul-13 10:16 
QuestionLanguage independent item moniker Pin
gaditya22-Mar-13 16:35
gaditya22-Mar-13 16:35 
AnswerRe: Language independent item moniker Pin
Alexey Merson25-Mar-13 7:44
Alexey Merson25-Mar-13 7:44 
GeneralRe: Language independent item moniker Pin
gaditya26-Mar-13 10:33
gaditya26-Mar-13 10:33 
GeneralShould be filed as a Tip/Trick Pin
Slacker00725-Jan-11 22:09
professionalSlacker00725-Jan-11 22:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.