Using Custom Data Parts in Excel 2010

Summary: This article describes how to build an OLE DB data provider that supports Custom Data Parts in Microsoft Excel 2010. (10 printed pages)

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

In this article
Introduction to Custom Data Parts in Excel 2010
Architectural Overview of Custom Data Parts in Excel 2010
Creating an ATL OLE DB Provider That Supports Custom Data Parts
Using a Provider in Excel 2010
Additional Resources

Published:  April 2010

Provided by:    Mark Parris, Microsoft Corporation

Contents

• Introduction to Custom Data Parts in Excel 2010

• Architectural Overview of Custom Data Parts in Excel 2010

• Creating an ATL OLE DB Provider That Supports Custom Data Parts

• Using a Provider in Excel 2010

Introduction to Custom Data Parts in Excel 2010

This article describes how to build an ATL OLE DB data provider that supports the new Custom Data Part (CDP) functionality in Excel 2010. A custom data part is a section of the workbook file where providers can store custom data. Custom data parts resemble custom XML parts except the data can be stored in any format.

Architectural Overview of Custom Data Parts in Excel 2010

A CDP is a mechanism that allows an OLE DB provider that implements the IMDEmbeddedDatainterface to indicate to Excel that it supports embedded storage in the form of a CDP. During the instantiation of the data connection, if the provider indicates that it supports IMDEmbeddedData, Excel asks the provider for a unique ID. When the workbook is saved, Excel gives the provider an IStream pointer. The provider can write data into this stream, which is then saved with the workbook. When the workbook loads and updates the data connection, Excel gives the provider an IStream pointer to the saved data. The provider then reads in the data from the stream. This process is shown in the following diagram.

Figure 1. Architectural Overview of Custom Data Parts in Excel 2010

Creating an ATL OLE DB Provider That Supports Custom Data Parts

The following steps show how to create an OLE DB provider that supports Custom Data Parts.

Create an ATL OLE DB Provider and Implement IMDEmbeddedData

Follow the steps in Creating an OLE DB Provider to create an ATL OLE DB provider and generate the provider files. In MyProviderDS.h, add the following #include statements.

// main symbols
#include "resource.h"
#include "MyProviderRS.h"
#include "MyProviderSess.h"

// add the following statements
#include <initguid.h>#include "msmd.h"

Extend CMyProviderSource to Implement the Necessary Interfaces

In MyProviderDS.h, extend CMyProviderSource to implement IMDEmbeddedData by removing the direct implementation of IPersistImpl<>, because IMDEmbeddedData extends IPersistStream.

class ATL_NO_VTABLE CMyProviderSource : 
public CComObjectRootEx<CComSingleThreadModel>,
public CComCoClass<CMyProviderSource, &CLSID_MyProvider>,
public IDBCreateSessionImpl<CMyProviderSource, CMyProviderSession>,
public IDBInitializeImpl<CMyProviderSource>,
public IDBPropertiesImpl<CMyProviderSource>,
public IInternalConnectionImpl<CMyProviderSource>, public IMDEmbeddedData

In CMyProviderSource, Add Implementations for the Methods Defined in IMDEmbeddedData

Implement SetHosted. This method indicates if Excel Services is hosting the provider, instead of Excel. Excel Services does not support the full IMDEmbeddedData interface. If the provider receives a call to SetHosted with a value of TRUE, it should expect to receive a SetContainerURL call indicating the hosting path of the workbook next.

STDMETHOD(SetHosted)(BOOL in_fIsHosted)
{
    HRESULT hr = S_OK;

    return hr;
}

Implement SetContainuerURL. This method tells the provider the path of the workbook to which the provider is returning data, whether the provider is hosted on a server or on a local hard disk drive. The provider can use this method to differentiate between workbooks. In addition, this method is called after a workbook is saved to a new name or location.

STDMETHOD(SetContainerURL)(BSTR in_bstrURL)
{
    HRESULT hr = S_OK;
    return hr;
}

Implement GetStreamIdentifier. This method instructs Excel how to determine the provider by returning a provider-specific string. This method is also called during refresh to give the provider an opportunity to claim ownership of the ID string. If the stream identifier Excel passes to this method is not recognized by the provider, it should return S_FALSE. The stream identifier is used by Excel to uniquely determine the CDP.

STDMETHOD(GetStreamIdentifier)(BSTR *out_pbstrStreamId)
{
    HRESULT hr;
    VARIANT vValue;

    hr = S_OK;

    // Check the connection string first.
    VariantInit(&vValue);        
    hr = this->GetPropValue(&DBPROPSET_DBINIT, DBPROP_INIT_CATALOG, &vValue);

    if (SUCCEEDED(hr))
    {
        if (!vValue.bstrVal || !wcscmp(vValue.bstrVal, OLESTR("(Default)")))
        {
            *out_pbstrStreamId = SysAllocString(OLESTR("MyProvider"));
            vValue.vt = VT_BSTR;
            vValue.bstrVal = *out_pbstrStreamId;
            this->SetPropValue(&DBPROPSET_DBINIT, DBPROP_INIT_CATALOG, &vValue);
        }
        else  // Excel verifies that the stream is from MyProvider
        {
            int pos = wcscmp(vValue.bstrVal, OLESTR("MyProvider"));
            if (pos != 0)
            {
                return S_FALSE;
            }
        }
    }
    return hr;
}

Implement SetTempDirPath. This method instructs the provider where it can store temporary data.

STDMETHOD(SetTempDirPath)(BSTR in_bstrPath)
{
    HRESULT hr = S_OK;

    // Cache value of in_bstrPath, if required

    return hr;
}

Implement IsDirty. This method instructs Excel whether the provider should save its data. Excel calls IsDirty before it calls Save. However, if the provider indicates its data was modified and needs to be saved in the call to IsDirty, and the Save operation fails due to a condition such as an unavailable network location, Excel can then call Save without calling IsDirty again.

STDMETHOD(IsDirty)()
{
    HRESULT hr;

    // S_FALSE indicates that the data was not modified and does not need to be saved
    // S_OK indicates that the data was modified and needs to be saved
    hr = S_OK;

    return hr;
}

Implement Cancel. This method is called when Excel cancels the save operation. The provider should perform any necessary cleanup here. If cleanup is successful, return S_OK. Otherwise, return E_FAIL.

STDMETHOD(Cancel)(void)
{
    HRESULT hr;

    // Perform necessary cleanup here
    // Return S_OK if cleanup succeeded
    // Return E_FAIL if cleanup failed
    hr = S_OK;

    return hr;
}

Implement Load. This method is used during the refresh of the associated workbook connection. The provider loads its data from the provided IStream pointer.

STDMETHOD(Load)(IStream *pStm)
{
    HRESULT hr = S_OK;

    // Load data from pStm

    return hr;
}

Implement Save. This method is where the provider writes its embedded data to be stored in the Excel workbook.

STDMETHOD(Save)(IStream *pStm, BOOL fClearDirty)
{
    wchar_t* pwszText = NULL; 
    ULONG uWritten = 0, uLen;
    HRESULT hr = S_OK;

    pwszText = L"Hello";
    uLen = (ULONG)wcslen(pwszText);
    hr = pStm->Write((void*)pwszText, (ULONG)(sizeof(wchar_t) * uLen), &uWritten);

    if (fClearDirty)
    {
        // Clear the flag that indicates if the data was modified
    }

    return hr;
}

In CMyProviderSource, Implement IPersistStream Methods GetClassID, GetSizeMax

Implement GetClassID. This method is called to get the CLSID of the provider. This method must be implemented because IMDEmbeddedData is derived from IPersistStream.

STDMETHOD(GetClassID)(CLSID *pClassID)
{
    if (pClassID == NULL)
        return E_FAIL;
    *pClassID = GetObjectCLSID();
    return S_OK;
}

Implement GetSizeMax. This method is called to determine how much storage space the provider needs. This method must be implemented because IMDEmbeddedData is derived from IPersistStream.

STDMETHOD(GetSizeMax)(ULARGE_INTEGER *pcbSize)
{
    // Set pcbSize to size needed to save the data, in bytes
    return S_OK;
}

Add IMDEmbeddedData to the Provider COM_MAP

In CMyProviderSource, add an entry to the COM_MAP for IMDEmbeddedData.

BEGIN_COM_MAP(CMyProviderSource)
    COM_INTERFACE_ENTRY(IDBCreateSession)
    COM_INTERFACE_ENTRY(IDBInitialize)
    COM_INTERFACE_ENTRY(IDBProperties)
    COM_INTERFACE_ENTRY(IPersist)
    COM_INTERFACE_ENTRY(IInternalConnection)
    COM_INTERFACE_ENTRY(IMDEmbeddedData)
END_COM_MAP()

Add the Necessary Properties

In the property set map for CMyProviderSource, add entries for the MSOLAPINIT and MDX_EXTENSION property sets. Add the following property entries for MSMD_EMBEDDED_DATA and MSMD_MDX_DDL_EXTENSIONS.

BEGIN_PROPERTY_SET(DBPROPSET_MDX_EXTENSIONS)
    PROPERTY_INFO_ENTRY_EX(MSMD_MDX_DDL_EXTENSIONS, VT_I4,
        DBPROPFLAGS_DBINIT, DBPROPVAL_MDX_DDL_CREATECUBE,
        DBPROPOPTIONS_REQUIRED)
END_PROPERTY_SET(DBPROPSET_MDX_EXTENSIONS)
BEGIN_PROPERTY_SET_EX(DBPROPSET_MSOLAPINIT, UPROPSET_USERINIT)
    PROPERTY_INFO_ENTRY_EX(MSMD_EMBEDDED_DATA, VT_I4,
        DBPROPFLAGS_DBINIT, DBPROPVAL_EMBED_EMBEDDED,
        DBPROPOPTIONS_REQUIRED)
END_PROPERTY_SET(DBPROPSET_MSOLAPINIT)

In the property set map for CMyProviderSource, add the INIT_CATALOG property to the DBPROPSET_DBINIT property set.

BEGIN_PROPERTY_SET(DBPROPSET_DBINIT)
    PROPERTY_INFO_ENTRY(INIT_CATALOG)
    PROPERTY_INFO_ENTRY(AUTH_PASSWORD)
    PROPERTY_INFO_ENTRY(AUTH_PERSIST_SENSITIVE_AUTHINFO)
    PROPERTY_INFO_ENTRY(AUTH_USERID)
    ...

Update the String Table

In the string table, add entries for IDS_DBPROP_MSMD_MDX_DDL_EXTENSIONS and IDS_DBPROP_MSMD_EMBEDDED_DATA.

To view the string table in Visual Studio, click the View menu, click Other Windows, and then click Resource View. In the Resource View pane, under the DemoProvider project, expand the DemoProvider.rc folder, expand the String Table folder, and double-click the String Table string node.

To add an entry to the string table, in the String Table, right-click and select New String.

Update the Registry Resource

In the registry resource IDR_MYPROVIDER, configure val OLEDB_SERVICES = d '4294967295' to OLEDB_SERVICES = d '4294967295' by removing the word val.

ForceRemove {39447E13-3B45-4930-9B1B-F020570F43E1} = s 'MyProvider Class'
{
    ProgID = s 'oledbtest.MyProvider.1'
    VersionIndependentProgID = s 'oledbtest.MyProvider'
    InprocServer32 = s '%MODULE%'
    {
        val ThreadingModel = s 'Apartment'
    }
    'OLE DB provider' = s 'MyProvider Class'
    val AppID = s '%APPID%'
    OLEDB_SERVICES = d '4294967295'
    'TypeLib' = s '{2FDAA868-6A14-4F55-8C81-151F629707FD}'
}

Build the Project

Build the OLE DB provider.

Using a Provider in Excel 2010

A provider that supports Custom Data Parts connects to Excel through a data connection like a standard data source provider.

To create a connection to your CDP data source

1. Start Excel.

2. On the Data tab, click From Other Sources and then click From Data Connection Wizard.

3. In the Data Connection Wizard, click Other/Advanced and then click Next.

4. In the list of providers that appears in the Data Link Properties dialog box, click MyProvider Class and then click OK.

5. In the next dialog box, click Database and Table, and then click Finish.

6. In the Import Data dialog, select the location for the data and select the Table, PivotTable or PivotChart format. Click OK.

7. Save the document. This calls IMDEmbeddedData::Save, and the provider can write data to the document by using the IStream pointer provided by Excel.

8. Close and reopen the document.

9. Refresh the data connection. This calls IMDEmbeddedData::Load, and the provider loads its data from the stream pointer provided by Excel.

Additional Resources

For more information about the subjects related to this article, see the following resources:

• Excel Developer Center

• Office Developer Center

• Excel MVPs at MSDN

• Excel MVPs at MVPs.org