C + + developing COM add-ins for Excel (i)

The current project is to develop an add-on for Excel to achieve financial-related business, and after a lot of factors are considered, decide to abandon C # and develop it in C + +. There are two ways to develop Excel add-ins in C + +, one is an Excel add-in XLL, and the other is to use ATL to make COM add-ins. The advantage of the XLL approach is that it is close to the bottom of Excel, executes quickly, and does not need to modify the registry, but uses it with a high degree of complexity and needs to learn about its proprietary data structures and API invocation methods. I was going to develop it completely with an XLL, but unfortunately the last step was to not find a way to implement the Ribbon menu using the XLL API. COM add-ons are easier to develop and provide an interface for many features, just a simple implementation. The final decision is to develop in the form of COM add-ons and, if necessary, consider the way they are mixed with XLL. The following are just some of the things that I've encountered in the development process, and it doesn't mean that it's the best approach, and that it will be revised in the future as work and learning go deeper.

The way to create COM add-ins for Excel is easy in VS2008 and in the past, simply select the shared Add-in in extensibility when you create a new project, and then follow the navigation action. The VS2012 I am currently using does not have this template, so you can only create a new ATL project and add the implementation interface yourself. An article on MSDN that builds C + + add-ons for Outlook 2010 explains this process in great detail, with only a brief excerpt and a description of the problems that may be encountered in some steps and how I can solve them.

First, create the project

1. Create a new ATL project. Since I want to use MFC controls, I have checked support for MFC, and others are using the default settings.

2. Add a class for the project and select the ATL Simple object from the ATL bar. Here is a convention, which is generally named CConnect. Fill in the ProgID, which is required when registering this add-in in the registry.

3, for the CConnect class implementation interface. Select the _idtextensibility2 interface in the Microsoft Add-in designer<1.0> type library, which is the interface that all Office add-ins must implement. Change the &libid_addindesignerobjects in the inheritance declaration to &__uuidof (__addindesignerobjects), and change the return value of the 5 methods of this interface to S_OK.

4. Add the registry file. In the Connect.rgs file, append the registry information that you can copy directly from MSDN, but be aware that you want to change Outlook to Excel and change the Nativeaddin.connect to your own named ProgID.

 hkcu{noremove software {noremove Microsoft {noremove                    Office {noremove Outlook {noremove Addins  {nativeaddin.connect {val Description  = s   sample Addin     val friendlyname  = s " sample Addin    val LoadBehavior  = d 3}}}}} }

5, set up debugging. When you set the Debug command property to pre-boot Excel, Excel automatically starts and loads the add-in when you debug the project.

When the above steps are complete, project creation can be completed. We can add a popup window to the OnConnection function to prove that the add-in has indeed been successfully loaded. When you debug a program, registry write failures may occur because the WIN7 permission setting problem requires that you add a line of code at the top of the DllRegisterServer and DllUnregisterServer functions ATL:: Atlsetperuserregistration (TRUE);

II. Customizing the Ribbon Menu

Customizing the Ribbon menu requires implementing the IRibbonExtensibility interface, which is found in the library Microsoft Office 12.0 Object library<2.4> ( The version number of the name differs slightly depending on the version of Office installation.

1, realize the interface. Change the &libid_office in the inheritance declaration to &__uuidof (__office) and rename the namespaces and methods in stdafx.h to avoid collisions. As shown below

#import"C:\Program Files (x86) \common Files\designer\msaddndr. DLL"Auto_rename auto_search raw_interfaces_only Rename_namespace ("addindesign") #import"C:\Program Files (x86) \common Files\Microsoft Shared\office12\mso. DLL"Auto_rename auto_search raw_interfaces_only Rename_namespace ("Office") Rename ("RGB","Msorgb") Rename ("SearchPath","Msosearchpath")using namespaceaddindesign;using namespaceOffice;

2. Add Ribbon description XML. Can be modified using the example in MSDN.

3, realize GetCustomUI interface. This is done directly with the source code in MSDN.

STDMETHOD (GetCustomUI) (BSTR RibbonID, BSTR *ribbonxml) {    if(!ribbonxml)returnE_pointer; *ribbonxml =Getxmlresource (IDR_XML1); returnS_OK;} HRESULT Cconnect::hrgetresource (intNId, LPCTSTR Lptype, lpvoid* ppvresourcedata, dword*pdwsizeinbytes) {hmodule hmodule=_atlbasemodule.getmoduleinstance (); if(!hmodule)returne_unexpected; HRSRC hrsrc=FindResource (hmodule, Makeintresource (nId), lptype); if(!hrsrc)returnHRESULT_FROM_WIN32 (GetLastError ()); Hglobal Hglobal=LoadResource (hmodule, HRSRC); if(!hglobal)returnHRESULT_FROM_WIN32 (GetLastError ()); *pdwsizeinbytes =Sizeofresource (hmodule, HRSRC); *ppvresourcedata =Lockresource (HGLOBAL); returnS_OK;} BSTR Cconnect::getxmlresource (intnId) {LPVOID PreSourceData=NULL; DWORD dwsizeinbytes=0; HRESULT HR= Hrgetresource (NId, _t ("XML"),         &presourcedata, &dwsizeinbytes); if(FAILED (HR))returnNULL; CComBSTR cbstr (dwsizeinbytes, reinterpret_cast<LPCSTR>(PreSourceData)); returnCbstr. Detach ();}

4. Custom button images and button events. First, set the iconnect as the default response interface. For custom images, you need to use the Image property flag on the button to customize the picture, and use the LoadImage callback on the root node to load the custom picture. For button events, a OnAction callback is required on the button. The signature of all callback functions can be found in customizing the Office Fluent Ribbon for Developers (Part 3 of 3), adding a callback function interface declaration to the project's IDL file. and implement it in Connect.h

Begin_com_map (CConnect)    com_interface_entry (iconnect)    com_interface_entry2 (IDispatch, iconnect)    COM _interface_entry (_idtextensibility2)    com_interface_entry (iribbonextensibility) End_com_map ()

Interface iconnect:idispatch{    HRESULT historybuttonclicked ([in]idispatch* ribbon);    HRESULT GetImage ([in] BSTR *pbstrimageid, [out, retval] ipicturedisp * * ppdispimage);};

STDMETHOD (historybuttonclicked) (idispatch* ribbon);
STDMETHOD (GetImage) (BSTR *pbstrimageid,ipicturedisp * * ppdispimage);

The concrete implementation of the button event code is very simple, here is the implementation of the interface to load the picture. The first parameter represents the name of the picture, the value of the Image property, and the second parameter prints the information for the picture to Excel. Here you can put the picture into a resource, use GDI + to convert it to bitmap and save it to Ppdispimage by Olecreatepictureindirect.

STDMETHODIMP Cconnect::getimage (BSTR *pbstrimageid,ipicturedisp * *ppdispimage) {    intPngid (0); Try{Pngid= lexical_cast<int> (*Pbstrimageid); }    Catch(...) {        returne_unexpected; }    using namespaceGdiplus; LPVOID PreSourceData=NULL; DWORD Len=0; HRESULT HR= Hrgetresource (pngid,_t ("PNG"), &presourcedata, &Len); BYTE* LPRSRC = reinterpret_cast<byte*>(PreSourceData); if(!lprsrc) {        returne_unexpected; } hglobal M_hmem=GlobalAlloc (gmem_fixed, Len); BYTE* Pmem = (byte*) GlobalLock (M_HMEM);    memcpy (Pmem,lprsrc,len);    GlobalUnlock (M_HMEM); IStream*pstm; CreateStreamOnHGlobal (M_hmem,false,&pstm);    PICTDESC pic; memset (&pic,0,sizeofpic); Bitmap*png =Bitmap::fromstream (pstm); Hbitmap Hmap=NULL; PNG->gethbitmap (Color (),&hmap); Pic.pictype=Pictype_bitmap; Pic.bmp.hbitmap=Hmap; Olecreatepictureindirect (&pic,iid_ipicturedisp,true, (lpvoid*) ppdispimage); returnS_OK;}

Because the ID of a picture in a resource is a number, and the attribute value of an image in XML is a string, all of the image's ID is placed in the image as a string, and then converted to a number in the callback to find the resource, the method is rather stupid, and later found a better way to do the modification.

You can complete the process of customizing the Ribbon menu by following the steps above.

Third, operate Excel

To access and manipulate excel in the add-in, you need to add the following statement to the stdafx.h, noting that the actual path is related to the path of the native installation office

#import"C:\\Program Files (x86) \\Common Files\\Microsoft Shared\\vba\\vba6\\vbe6ext. OLB"#import"D:\\Program Files (x86) \\Microsoft Office\\office12\\excel. EXE"Rename"DialogBox","Exceldialogbox") Rename ("RGB","Excelrgb") Rename ("CopyFile","Excelcopyfile") Rename ("ReplaceText","Excelreplacetext") Exclude ("IFont","IPicture") No_dual_interfacesusing namespaceExcel;

You can then add initialization code to the OnConnection to access Excel.

* * Custom) {    pexcel.getactiveobject ("excel.application");     = Create (HWND) pexcel->GetHwnd ());     return S_OK;}

By doing this, you basically complete the initialization of a COM add-in, and you can begin to develop the actual business requirements

C + + developing COM add-ins for Excel (i)