This is the last part in writing Delphi Excel COM Add-Ins. We just need to be a bit more customized with individual icons passed as IPictureDisp to the UI. And we need to integrate our Delphi COM Add-In to Excel’s Ribbon by implementing the IRibbonExtensibility interface.
We start a new Delphi project with the same things we’ve done before. The Microsoft Office Fluent UI can be modified either by using the XML file formats introduced with Office 2007 or by using a (Delphi ;) COM Add-in which implements IRibbonExtensibility. We want to do this and want to differentiate between Excel 12+ and versions before when the Add-In is customizing the Microsoft Office user interface.
The IRibbonExtensibility Interface is defined in the Microsoft Office 12+ Object Library (MSO.DLL) and we extract the following definition from a created TLB file or from a Delphi Office20xx import unit to our project.
type
// *********************************************************************//
// Interface: IRibbonExtensibility
// Flags: (4416) Dual OleAutomation Dispatchable
// GUID: {000C0396-0000-0000-C000-000000000046}
// *********************************************************************//
IRibbonExtensibility = interface(IDispatch)
['{000C0396-0000-0000-C000-000000000046}']
function GetCustomUI(const RibbonID: WideString): WideString; safecall;
end;
// *********************************************************************//
// DispIntf: IRibbonExtensibilityDisp
// Flags: (4416) Dual OleAutomation Dispatchable
// GUID: {000C0396-0000-0000-C000-000000000046}
// *********************************************************************//
IRibbonExtensibilityDisp = dispinterface
['{000C0396-0000-0000-C000-000000000046}']
function GetCustomUI(const RibbonID: WideString): WideString; dispid 1;
end;
There is only one method to implement. We add the IRibbonExtensibility interface to the list of implemented interfaces and expand the class definition with GetCustomUI.
The previous project did provide a CommandBar with buttons. These buttons shall be available in a custom Tab of the Excel Ribbon. We need a Callback method to handle the clicks. You can find a list of the Callback methods with signatures here. We add a method to the Add-In’s Interface which will work as callback procedure. The Control parameter will be an IRibbonControl which is also defined in the Microsoft Office Object Library.
interface IDelphiAddin3: IDispatch
{
[id(0x00000001)]
HRESULT _stdcall RibbonClick([in] IDispatch* Control);
};
A DoSomething method will deal with the clicks to the Ribbon’s and Command Bar’s buttons. The Delphi Add-In’s class definition should look as follows.
type
TDelphiAddin3 = class(TAutoObject, IDelphiAddin3,
IDTExtensibility2, IRibbonExtensibility)
private
{ ... }
procedure DoSomething(const IdTag: Widestring);
{ IRibbonExtensibility }
function GetCustomUI(const RibbonID: WideString): WideString; safecall;
protected
procedure RibbonClick(const Control: IDispatch); safecall;
{ ... }
end;
We start with the implementation of the GetCustomUI method. The RibbonID parameter can be ignored. There’s only one value (“Microsoft.Excel.Workbook”) that will be passed by Excel to GetCustomUI. A list of RibbonID values for different Microsoft Office applications can be found here.
function TDelphiAddin3.GetCustomUI(const RibbonID: WideString): WideString;
begin
result:=
'<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">'#13#10 +
' <ribbon>'#13#10 +
' <tabs>'#13#10 +
' <tab id="myglaTab" label="Mygla">'#13#10 +
' <group id="myglaGroup1" label="Mygla''s">'#13#10 +
' <button id="MyglaButton11" size="large" imageMso="M" onAction="RibbonClick"/>'#13#10 +
{ ... }
' </group>'#13#10 +
' </tab>'#13#10 +
' </tabs>'#13#10 +
' </ribbon>'#13#10 +
'</customUI>';
end;
I won’t provide information about the XML format. There is a series of articles about customizing the Office Fluent Ribbon UI you can find on Microsoft’s website that is worth reading. Another source of information is Ron de Bruin’s website. This information really helps. The XML return value of GetCustomUI creates a new Tab in the Ribbon and adds some buttons. The onAction Callback attribute is set to our RibbonClick method.
Both, clicks to a Command Bar Button and to a Ribbon Button should be handled from one place. This is our DoSomething method. From BtnClick method, we pass the value of the Tag property of the ComandBarButton to DoSomething. From RibbonClick method, we pass the Id property of the RibbonControl. In this way, DoSomething can identify the control which has invoked a callback. A simple message box will be displayed for illustration.
procedure TDelphiAddin3.BtnClick(const Ctrl: CommandBarButton; var CancelDefault: WordBool); begin // tag used to identify buttons DoSomething(Ctrl.Tag); end; procedure TDelphiAddin3.RibbonClick(const Control: IDispatch); begin // id used to identify buttons DoSomething((Control as IRibbonControl).Id); end; procedure TDelphiAddin3.DoSomething(const IdTag: Widestring); var s: WideString; begin s:='Hello World! ' + IdTag + ' clicked'; MessageBoxW(0, PWideChar(s), '', MB_OK); end;
Now, Excel shows the custom Ribbon Tab in Excel 12+ but also the Command Bar in the Add-In Tab. We add a small piece of code to the InitButtons method we’ve taken from previous project.
procedure TDelphiAddin3.InitButtons;
begin
{ ... }
// only for Excel 11 and before
llcid:= GetUserDefaultLCID;
Val(Excel.Version[llcid], v, i);
if (i <> 0) or (v > 11) then
Exit;
{ ... }
Done this, we have reached the objective. Excel 11- will show a Command Bar and Excel 12+ a custom Ribbon Tab. For the last step, we will use a new Delphi project. Take over all the things from previous project. I will explain the necessary modifications.
Custom icons will be passed to the Ribbon UI with a callback. CommandBar controls have a Picture and Mask property for individual icons. There’s an article about the challenges with custom images. A Microsoft Knowledge Base article describes .NET ways to custom pictures. It explains what we want to do in an unmanaged Delphi way.
Let’s start with the callback for Ribbon UI controls. There’s a description of several callback signatures with with the following signature for a GetImage method.
HRESULT GetImage([in] IRibbonControl *pControl, [out, retval] IPictureDisp ** ppdispImage)
So, we add a method to the Delphi COM Add-In’s interface definition as follows.
interface IDelphiAddin4: IDispatch
{
[id(0x00000001)]
HRESULT _stdcall RibbonClick([in] IDispatch* Control);
[id(0x00000002)]
HRESULT _stdcall GetImage([in] BSTR ImageID, [out, retval] Picture** retval);
};
The Picture type is translated to IPictureDisp. A second method named GetResPic is added to the class definition. It has a second parameter for different methods of loading a picture. All methods will load pictures from resources linked to the Add-In’s DLL.
type
TDelphiAddin4 = class(TAutoObject, IDelphiAddin4,
IDTExtensibility2, IRibbonExtensibility)
private
function GetResPic(const ImgName: String;
const Res: integer = 0): IPictureDisp;
{ ... }
protected
function GetImage(const ImageID: WideString): IPictureDisp; safecall;
{ ... }
end;
We modify the return value of GetCustomUI. The loadImage attribute is set to GetImage. And the image attribute for buttons is set instead of setting imageMSO.
function TDelphiAddin4.GetCustomUI(const RibbonID: WideString): WideString;
begin
result:=
'<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" loadImage="GetImage">'#13#10 +
' <ribbon>'#13#10 +
' <tabs>'#13#10 +
' <tab id="myglaTab" label="Mygla">'#13#10 +
' <group id="myglaGroup1" label="Mygla''s">'#13#10 +
{ ... }
' <button id="MyglaButton12" size="large" image="Mygla.png" onAction="RibbonClick"/>'#13#10 +
{ ... }
' </group>'#13#10 +
' </tab>'#13#10 +
' </tabs>'#13#10 +
' </ribbon>'#13#10 +
'</customUI>';
end;
The GetImage method passes the resource name to the GetResPic function. I use the underscore for dots in resource names and GetImage exchanges the dot of the ImageID parameter with an underscore.
function TDelphiAddin4.GetImage(const ImageID: WideString): IPictureDisp;
begin
{ ... }
Result:= GetResPic(s);
end;
Next step is changing the InitButtons method to show custom images on CommandBarControls. We set the Picture property to a desired image and the FaceID property to value 0. The sample project makes no use of the Mask property.
procedure TDelphiAddin4.InitButtons;
begin
{ ... }
Button.DefaultInterface.Set_FaceId(0);
Button.DefaultInterface.Set_Picture(
GetResPic('Letter'+ FIDs[i]+'_bmp', 2));
{ ... }
Last but not least, we need to implement the GetResPic function. The Res parameter is used for a Case statement. Every case makes use of the OleCreatePictureIndirect function. It creates an OLE standard picture object which provides both the IPicture and IPictureDisp interfaces. The picture object is created from an initialized PICTDESC structure.
function TDelphiAddin4.GetResPic(const ImgName: String;
const Res: integer = 0): ActiveX.IPictureDisp;
// Res: 0 GDI+ Bitmap RCDATA
// 1 LoadResource ICON
// 2 LoadResource BITMAP
var
PictureDesc: TPictDesc;
{ ... }
begin
The principle for the second and third case is described in the KB article mentioned above. So we start from bottom up. The cases explain loading different resource types. The first example loads a bitmap resource to HBITMAP with the LoadBitmap function.
{ ... }
FillChar(PictureDesc, SizeOf(PictureDesc), 0);
PictureDesc.cbSizeOfStruct:= SizeOf(PictureDesc);
PictureDesc.picType := PICTYPE_BITMAP;
PictureDesc.hbitmap := LoadBitmap(HInstance, PChar(ImgName));
OleCheck(OleCreatePictureIndirect(PictureDesc,
ActiveX.IPicture, true, Result));
{ ... }
The second example loads an icon resource to HICON with the LoadIcon function.
{ ... }
FillChar(PictureDesc, SizeOf(PictureDesc), 0);
PictureDesc.cbSizeOfStruct:= SizeOf(PictureDesc);
PictureDesc.picType := PICTYPE_ICON;
PictureDesc.hIcon := LoadIcon(HInstance, PChar(ImgName));
OleCheck(OleCreatePictureIndirect(PictureDesc,
ActiveX.IPicture, true, Result));
{ ... }
LoadBitmap and LoadIcon are GDI functions and do not support PNG files with alpha channel. This is a strong limitation. GDI+ supports various file formats including the PNG files with transparency we want to use. It provides class wrappers for its functions but you can use the GDI+ Flat API directly. There are Delphi translations of the entire GDI+ API. But the included Demos make use of the class wrappers. I found a VBA sample from Stephen Bullen but no Delphi solution on the Web. Thus, I show here my Delphi version for loading images. A limited set of functions and structures of the Flat API is needed. They are included in the sample unit.
function TDelphiAddin4.GetResPic(const ImgName: String;
const Res: integer = 0): ActiveX.IPictureDisp;
var
PictureDesc: TPictDesc;
GPInput: GdiplusStartupInput;
Status: GpStatus;
Token: PULONG;
ResStream: TResourceStream;
ResStreamI: IStream;
GPBM: GpBitmap;
HBM: HBITMAP;
begin
{ ... }
FillChar(GPInput, SizeOf(GPInput), 0);
GPInput.GdiplusVersion:= 1;
Status:= GdiplusStartup(Token, @GPInput, nil);
if Status = 0 then begin
try
ResStream:= TResourceStream.Create(HInstance, ImgName, RT_RCDATA);
try
ResStreamI:= TStreamAdapter.Create(ResStream);
Status:= GdipCreateBitmapFromStream(ResStreamI, GPBM);
if Status = 0 then begin
try
Status:= GdipCreateHBITMAPFromBitmap(GPBM, HBM, $00FFFFFF);
if Status = 0 then begin
FillChar(PictureDesc,SizeOf(PictureDesc),0);
PictureDesc.cbSizeOfStruct:= SizeOf(PictureDesc);
PictureDesc.picType:= PICTYPE_BITMAP;
PictureDesc.hbitmap:= HBM;
OleCheck(OleCreatePictureIndirect(PictureDesc,
ActiveX.IPicture, true, Result));
end;
finally
GdipDisposeImage(GPBM);
end;
end;
finally
ResStream.Free;
ResStreamI:= nil;
end;
finally
GdiplusShutdown(Token);
end;
end;
{ ... }
PNG files are stored as raw data (RCDATA) resources. I use the GdipCreateBitmapFromStream function to load the image resources. The TStreamAdapter is required because the function expects an IStream interface as source. When calling the GdipCreateHBITMAPFromBitmap function, I use White ($00FFFFFF) as colorBackground parameter for best results. Both functions are part of the GDI+ Bitmap class.
I won’t win a designer award for the icons I have included to the sample project. You’ll do it better. However, you’re invited to leave a comment.
Excellent work!
thanks