I received an email from a customer the other day asking how to set up an Excel pivot table programmatically. The Excel OM exposes a number of objects and methods that you can use to create pivot tables, some more sophisticated than others. I wanted the simplest possible way to set up a pivot table from an external data source, and this is what I ended up with...
I used the AdventureWorks SQL database, which you can download from here. In my solution, I first set up a data connection to the database, with a SQL select statement to fetch all the SalesPerson sales records:
string connection =
@"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYSERVER\SQLEXPRESS;Initial Catalog=AdventureWorks";
string command =
"SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";
Next, I added a new PivotCache to the PivotCaches collection in the active workbook, and set its data connection and SQL command properties:
Excel.PivotCache pivotCache =
this.Application.ActiveWorkbook.PivotCaches().Add(
Excel.XlPivotTableSourceType.xlExternal, missing);
pivotCache.Connection = connection;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = command;
pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
I can then add a new PivotTable to the worksheet, based on the PivotCache I’ve just configured:
Excel.Worksheet sheet = (Excel.Worksheet)this.Application.ActiveSheet;
Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(missing);
Excel.PivotTable pivotTable = pivotTables.Add(
pivotCache, this.Application.ActiveCell, "PivotTable1",
missing, missing);
Then, set the PivotTable to use the pivot table stencil outline instead of the default 2x2 cell grid, and format it with grey alternating row shading:
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
Set up the SalesTerritory field as the page field, and FullName as the row field:
Excel.PivotField pageField =
(Excel.PivotField)pivotTable.PivotFields("SalesTerritory");
pageField.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
Excel.PivotField rowField =
(Excel.PivotField)pivotTable.PivotFields("FullName");
rowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Add a data field for the sales for 2004:
pivotTable.AddDataField(
pivotTable.PivotFields("2004"), "Sum of 2004", Excel.XlConsolidationFunction.xlSum);
Done. The end result looks like this:
In an earlier post, I talked about how you could delay (or prevent) the loading of managed code using a native add-in. In that post I also listed the standard LoadBehavior settings, and I was assuming that everyone knows how these apply, but I got a couple of follow-up questions that prompted me to clarify the normal (LoadBehavior-based) demand-loading mechanism, and how it applies to VSTO add-ins.
It’s pretty simple: you can set up any Office COM add-in (native, managed, VSTO) for demand-loading in a standard way, that is by setting the LoadBehavior in the registry to 0x10 (16 dec). For versions of Office prior to 2007 (and for those Office apps that don’t yet support the Ribbon), the typical pattern is to create some custom CommandBar control in your add-in at startup. Office will load the add-in the first time it boots after the add-in is registered, call your add-in code to set up the custom UI, and cache this information so that when the user clicks the control, this will load the add-in.
In the case of add-ins that implement custom Ribbons, the behavior is essentially the same: Office will load the add-in the first time it boots after the add-in is registered, call GetCustomUI and cache the ribbon XML. Then, Office resets the LoadBehavior value to 8. Subsequently, when the user clicks one of the ribbon controls, this will load the add-in and set LoadBehavior to 9. This works with both a low-level IRibbonExtensibility implementation and with the VSTO designer-generated ribbon wrappers.
If you want to set LoadBehavior in your project for testing during development, you could create a regedit script, eg:
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\MyAddIn]
"Description"="MyAddIn"
"FriendlyName"="MyAddIn"
"LoadBehavior"=dword:00000010
"Manifest"="c:\\Temp\\MyAddIn\\bin\\Debug\\MyAddIn.vsto|vstolocal"
...and then add this to the project properties as a post-build step:
regedit /s "$(SolutionDir)$(SolutionName).reg"
When it comes time to deploy the solution, if you’re using a setup project, you’ll have to set the LoadBehavior by setting the value in the registry editor in your setup project.
If you’re using ClickOnce publishing, you’ll need to edit the loadBehavior attribute value manually in the application manifest (and then re-sign the manifest).
That’s it. There’s nothing else you need to do. You create your custom Ribbon either using a low-level implementation or using the VSTO designer wrappers – all in the normal way.
In my last post, I discussed how you could avoid any dependency on the Office PIAs by using ComImport to redefine the host application’s OM interfaces. Someone (A Developer) pointed out that I had actually omitted the trailing 2 members of the IRibbonControl interface – and I mentioned that this wouldn’t stop the code working. I also mentioned that omitting interface members is a valid technique, so today I’ll explain what I mean by this.
I’m attaching the sample solution to this post – and you can compare it with the sample solution I attached to the previous post – you’ll see that the solutions are identical except that in this one I’m omitting selective interface members in my ComImport declarations. The idea is that I don’t want to include definitions of interface members that I’m not using. You might think that I could simply omit these members altogether – and this is true for trailing members, but it’s not true for non-trailing members. Strictly speaking, even for trailing members, it’s not good practice.
The reason is that these are COM interfaces, and the number and position of methods defined in a COM interface is paramount. At runtime, the methods are represented by a virtual function table (vtable), which contains slots that correspond to the methods, in the order of their declaration in the interface. Because calls into COM interfaces are implemented as calls to offsets from the start of the interface’s vtable (or the vtable for the object that implements the interface), it means that the order/position and number of the members is significant.
This ordering and numbering must be maintained when you redefine a COM interface using ComImport, and the .NET Framework provides member syntax to support this. In order to preserve vtable order, you use the special _VtblGapXX_YY method syntax to indicate missing members, where XX signifies the position in the vtable, and YY signifies the number of vtable members to be omitted from this position. For example, in the _CustomTaskPane interface, from position 1, you can omit 2 vtable members using this declaration:
void _VtblGap1_2();
This syntax allows me to preserve vtable slots for members I’m not defining, such that the subsequent members are still correctly positioned. Here’s the set of selectively ComImport-ed interfaces for custom task panes. Note that by omitting the DockPosition and DockPositionRestrict members, I can also avoid having to declare the MsoCTPDockPosition and MsoCTPDockPositionRestrict enums:
//public enum MsoCTPDockPosition
//{
// msoCTPDockPositionLeft,
// msoCTPDockPositionTop,
// msoCTPDockPositionRight,
// msoCTPDockPositionBottom,
// msoCTPDockPositionFloating
//}
//public enum MsoCTPDockPositionRestrict
//{
// msoCTPDockPositionRestrictNone,
// msoCTPDockPositionRestrictNoChange,
// msoCTPDockPositionRestrictNoHorizontal,
// msoCTPDockPositionRestrictNoVertical
//}
[ComImport, Guid("000C033B-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0), DefaultMember("Title")]
public interface _CustomTaskPane
{
[DispId(0)]
string Title { [return: MarshalAs(UnmanagedType.BStr)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(0)] get; }
// From position 1, we omit 2 vtbl members.
void _VtblGap1_2();
//[DispId(1)]
//object Application { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)] get; }
//[DispId(2)]
//object Window { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(2)] get; }
[DispId(3)]
bool Visible { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(3)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(3)] set; }
[DispId(4)]
object ContentControl { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(4)] get; }
// From position 5, we omit 1 vtbl member.
//void _VtblGap5_1();
void _VtblGap_1();
//[DispId(5)]
//int Height { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(5)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(5)] set; }
[DispId(6)]
int Width { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(6)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(6)] set; }
// From position 7, we omit 3 vtbl members.
void _VtblGap7_3();
//[DispId(7)]
//MsoCTPDockPosition DockPosition { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(7)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(7)] set; }
//[DispId(8)]
//MsoCTPDockPositionRestrict DockPositionRestrict { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(8)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(8)] set; }
//[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(9)]
//void Delete();
}
[ComImport, Guid("000C033B-0000-0000-C000-000000000046")]
public interface CustomTaskPane : _CustomTaskPane
{
}
[ComImport, Guid("000C033D-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0)]
public interface ICTPFactory
{
[return: MarshalAs(UnmanagedType.Interface)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
CustomTaskPane CreateCTP([In, MarshalAs(UnmanagedType.BStr)] string CTPAxID, [In, MarshalAs(UnmanagedType.BStr)] string CTPTitle, [In, Optional, MarshalAs(UnmanagedType.Struct)] object CTPParentWindow);
}
[ComImport, Guid("000C033E-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0)]
public interface ICustomTaskPaneConsumer
{
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
void CTPFactoryAvailable([In, MarshalAs(UnmanagedType.Interface)] ICTPFactory CTPFactoryInst);
}
As you can see from the listing above, from position 5, I used a simpler form of the _VtblGap syntax to omit one vtable member. Note that it's enough to specify the number of vtable slots to reserve - it's not necessary to specify the position, because the position is taken from the position of the _VtblGap_YY declaration itself.
//void _VtblGap5_1();
void _VtblGap_1();
Note also that just because I can specify the position in the XX component, this does not allow me to rearrange the position of the _VtblGap declarations themselves. Given this, you might wonder why I bother with the XX in _VtblGapXX_YY, since it’s not necessary for the purposes of reserving correctly ordered vtable slots. The answer is that these _VtblGap entries all count as method declarations, and if I only use the YY component, there’s a good chance I’ll end up with a name conflict and the code will fail to compile. Using the XX component is just a convenience to avoid this problem. The _VtblGap syntax is documented in the Common Language Infrastructure Annotated Standard.
The pattern is similar for the Ribbon interfaces. Note that (as in the example in my previous post), I could simply omit the trailing members, but in this example I’ve chosen to explicitly reserve vtable space even though I’m not using these members and there are no members after these ones in the interface. The reason that preserving overall vtable size is encouraged is because there could theoretically be a consumer of the interface written in such a way that is dependent on the overall size of the vtable. This is pretty far-fetched these days – and in this specific example I’m ComImport-ing these interfaces in my own project which is not designed to be re-used by any other consumer. Nonetheless, it’s probably good practice, and does no harm.
[ComImport, Guid("000C0396-0000-0000-C000-000000000046"), TypeLibType((short)0x1040)]
public interface IRibbonExtensibility
{
[return: MarshalAs(UnmanagedType.BStr)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
string GetCustomUI([In, MarshalAs(UnmanagedType.BStr)] string RibbonID);
}
[ComImport, Guid("000C0395-0000-0000-C000-000000000046"), TypeLibType((short)0x1040)]
public interface IRibbonControl
{
[DispId(1)]
string Id { [return: MarshalAs(UnmanagedType.BStr)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)] get; }
// From position 2, we omit 2 vtbl members.
void _VtblGap2_2();
//[DispId(2)]
//object Context { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(2)] get; }
//[DispId(3)]
//string Tag { [return: MarshalAs(UnmanagedType.BStr)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(3)] get; }
}
The end-result of all this member omission is smaller code, which means a smaller working set, and likely better performance as a result. It also means less chance for bugs to be introduced and less code maintenance going forward.
In a previous post, I discussed how you could build an add-in for multiple versions of Office, and explained the problems in this approach (and why it is not officially supported). One of the reasons this is not supported is because you end up building an add-in which has dependencies on a later version of the Office PIAs, even though your add-in is sometimes deployed to a machine with an earlier version of Office. The canonical example is where you build an add-in that conditionally uses both Office 2003 (and earlier) CommandBar technology and Office 2007 (and later) Ribbon and task pane technologies, as described in my earlier post. This add-in would normally have a dependency on the Office 2007 PIAs (where the IRibbonExtensibility and ICustomTaskPaneConsumer types are declared). When deployed to an Office 2007 machine, all is good, because the Office 2007 PIAs are present. However, when deployed to an Office 2003 machine – even though the Ribbon and task pane functionality is not used, it is still in the add-in code, and therefore still requires the Office 2007 PIAs. Is there a way around this problem?
Of course, one solution is to deploy the Office 2007 PIAs to the machine with Office 2003, but you then have the follow-on problems of registering multiple versions of the PIAs, and of loading the Office 2007 PIAs into an Office 2003 process. Not good.
Another way around this problem is to remove the dependency on the later PIAs. Because of the high degree of backwards compatibility in Office, you can safely assume that if your add-in works on Office 2003 (with the Office 2003 PIAs), then it should also work on Office 2007 (with the Office 2007 PIAs). So, the only issue is how to get it to work on an Office 2003 machine with only the Office 2003 PIAs present, even though your code uses types such as IRibbonExtensibility that are not present in Office 2003 or the Office 2003 PIAs. So, the question becomes, How can you write a solution which uses types defined in an assembly (the Office 2007 PIA) that is not referenced by the solution?
The answer, of course, is ComImport. For documentation on ComImport, see here and here. PIAs, and interop assemblies generally, can be created by using the Tlbimp.exe utility, which reads a type library and outputs an interop assembly, containing metadata that is the managed equivalent of the COM typelib. ComImport is a pseudo-custom attribute that indicates that a type has been defined in a previously published type library. You can apply this attribute when you want to generate interop metadata manually in source code that simulates the metadata produced by Tlbimp.exe.
Here’s an example. Note that I’m declaring an inner namespace “Office”, so that I can refer to the task pane and Ribbon types as if they were declared in the same namespace as the real Office types – that is, assuming the standard using statement with an alias, eg: using Office = Microsoft.Office.Core;.
Taking custom task panes first, note that ICustomTaskPaneConsumer has a member that takes an ICTPFactory object as a parameter. I therefore have to define ICTPFactory as well. ICTPFactory in turn has a member that takes a CustomTaskPane object, and this derives from _CustomTaskPane, so I need to define these two interfaces also. With Ribbons, the IRibbonExtensibility interface is straightforward, but the signatures of the callback methods that I must define for Office to use for my Ribbon controls tend to take IRibbonControl objects as parameters, so I need to define this interface also.
namespace MyOffice2003AddIn
{
namespace Office
{
#region Custom Task Pane
public enum MsoCTPDockPosition
{
msoCTPDockPositionLeft,
msoCTPDockPositionTop,
msoCTPDockPositionRight,
msoCTPDockPositionBottom,
msoCTPDockPositionFloating
}
public enum MsoCTPDockPositionRestrict
{
msoCTPDockPositionRestrictNone,
msoCTPDockPositionRestrictNoChange,
msoCTPDockPositionRestrictNoHorizontal,
msoCTPDockPositionRestrictNoVertical
}
[ComImport, Guid("000C033B-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0), DefaultMember("Title")]
public interface _CustomTaskPane
{
[DispId(0)]
string Title { [return: MarshalAs(UnmanagedType.BStr)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(0)] get; }
[DispId(1)]
object Application { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)] get; }
[DispId(2)]
object Window { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(2)] get; }
[DispId(3)]
bool Visible { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(3)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(3)] set; }
[DispId(4)]
object ContentControl { [return: MarshalAs(UnmanagedType.IDispatch)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(4)] get; }
[DispId(5)]
int Height { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(5)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(5)] set; }
[DispId(6)]
int Width { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(6)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(6)] set; }
[DispId(7)]
MsoCTPDockPosition DockPosition { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(7)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(7)] set; }
[DispId(8)]
MsoCTPDockPositionRestrict DockPositionRestrict { [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(8)] get; [param: In] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(8)] set; }
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(9)]
void Delete();
}
[ComImport, Guid("000C033B-0000-0000-C000-000000000046")]
public interface CustomTaskPane : _CustomTaskPane
{
}
[ComImport, Guid("000C033D-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0)]
public interface ICTPFactory
{
[return: MarshalAs(UnmanagedType.Interface)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
CustomTaskPane CreateCTP([In, MarshalAs(UnmanagedType.BStr)] string CTPAxID, [In, MarshalAs(UnmanagedType.BStr)] string CTPTitle, [In, Optional, MarshalAs(UnmanagedType.Struct)] object CTPParentWindow);
}
[ComImport, Guid("000C033E-0000-0000-C000-000000000046"), TypeLibType((short)0x10c0)]
public interface ICustomTaskPaneConsumer
{
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
void CTPFactoryAvailable([In, MarshalAs(UnmanagedType.Interface)] ICTPFactory CTPFactoryInst);
}
#endregion
#region Ribbon
[ComImport, Guid("000C0396-0000-0000-C000-000000000046"), TypeLibType((short)0x1040)]
public interface IRibbonExtensibility
{
[return: MarshalAs(UnmanagedType.BStr)]
[MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)]
string GetCustomUI([In, MarshalAs(UnmanagedType.BStr)] string RibbonID);
}
[ComImport, Guid("000C0395-0000-0000-C000-000000000046"), TypeLibType((short)0x1040)]
public interface IRibbonControl
{
[DispId(1)]
string Id { [return: MarshalAs(UnmanagedType.BStr)] [MethodImpl(MethodImplOptions.InternalCall, MethodCodeType = MethodCodeType.Runtime), DispId(1)] get; }
}
#endregion
}
}
How do I arrive at these ComImport declarations? One way is to use the ildasm.exe utility to read the Office 2007 PIA (that is, Office.dll) to get the metadata, although this approach requires you to do further work to massage the metadata into the appropriate code definitions. Another way is to use