Using the Office Add-ins API

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

The Microsoft Office Add-ins for Microsoft Dynamics AX enables you to integrate data from the Microsoft Dynamics AX client together with Microsoft Excel or Microsoft Word. When you install Office Add-ins, a tab labeled Dynamics AX is added to the ribbon in Excel and Word. You use the controls on this tab to add, remove, or update Microsoft Dynamics AX data that appears in an Excel spreadsheet or Word document.

The Microsoft Dynamics AX 2012 R2 Office Add-ins include an application programming interface (API) that you can use to change filter and other criteria in an Excel spreadsheet or Word document. You use the API when you want to programmatically customize a spreadsheet or document.

For example, you use the Export to Excel button on the Action Pane of a form to create a spreadsheet that is used by several people. You now want to create a version of that spreadsheet file for one person who works with a smaller set of records. To show a subset of records in the spreadsheet, you create an application that uses the API to filter records from the spreadsheet. You then save that file as a separate version of the original spreadsheet.

Working with Spreadsheet and Document Files

Excel 2007, Word 2007, and later versions use the Office Open XML format for spreadsheet and document files. To programmatically modify these files, you use the Open method of the SpreadsheetDocument class or the WordprocessingDocument class of the DocumentFormat.OpenXml.Packaging namespace. The namespace is found in the DocumentFormat.OpenXml assembly.

XML Metadata for Office Add-ins

The Office Add-ins embeds a custom XML part in every spreadsheet or document. The XML part includes metadata that describes the connections, tables, fields, filters, and other information that helps show Microsoft Dynamics AX data in the spreadsheet or document.

The following illustration shows how the metadata in the custom XML part is organized for an Excel spreadsheet. In the diagram, the Office Add-ins XML is the embedded XML part. The diagram also shows that the part includes nodes for the workbook options and data sources.

To programmatically add, remove, or modify the XML that Office Add-ins embeds in a file, you use the Serializer class. The class is in the Microsoft.Dynamics.AX.Framework.OfficeAddin.XmlParts namespace of the Microsoft.Dynamics.AX.Framework.OfficeAddin.XmlParts.dll .NET assembly. When you install the Office Add-ins, the assembly is put in the C:\Program Files (x86)\Microsoft Dynamics AX\60\OfficeAddins folder. The Serializer class includes the following methods:

In addition, the DocumentFormat.OpenXml and Microsoft.Dynamics.AX.Framework.OfficeAddin.XmlParts assemblies are added to the list of References in the AOT. As a result, you can use the classes in these assemblies from your X++ code to programmatically add, remove, or modify the XML in a spreadsheet or document. For more information about how to use a .NET assembly from X++, see How to: Compile and Run X++ that Calls CLR Managed Assemblies.

Workbook Options

The workbook options node is a collection of properties you use to control the behavior of an Excel spreadsheet. You can use the Office Add-ins API to programmatically access the following properties of the workbook options node:

Data Sources

The data sources node is the XML metadata that describes one or more data connections that a spreadsheet or document uses to obtain data from Microsoft Dynamics AX. Typically, the data sources are added when you export data from a form or you use the Office Add-ins to retrieve data.

The following illustration shows the structure of the XML metadata for the data sources node. Notice how a data source can include filters and matrix fields.

Using the Office Add-ins API with XML Metadata Nodes

To programmatically add, remove, and modify Office Add-ins XML nodes, you use several classes in the Microsoft.Dynamics.AX.Framework.OfficeAddin.XmlParts namespace. The namespace is found in the Microsoft.Dynamics.AX.Framework.OfficeAddin.XmlParts.dll .NET assembly. The classes include properties and methods that you use to modify the metadata values in the Office Add-ins XML part. To see how you can use API classes to add a filter to a spreadsheet, see How to: Add an Office Add-in Filter to a Spreadsheet.

The following table shows the class you use with each XML node you find in the custom XML part that Office Add-ins embed in a spreadsheet or document file.

Filtering Records in a Data Source

The earlier diagram shows that a data source can include filters. The diagram also shows that the Filters node can include one or more individual filter nodes. A filter node includes the criteria that specify the data records that appear in a spreadsheet or document. To customize the spreadsheet or document, you can use the Office Add-ins API to add, remove, or modify filter nodes.

Filter Nodes

A filter is an XML metadata node that specifies a field, a table, and criteria. The data source uses the properties of the filter to determine what records appear in a spreadsheet or document. A filter node is like a Range in an AX query. For information about how to modify a filter, see How to: Add an Office Add-in Filter to a Spreadsheet. To learn about AX queries and ranges, see Query Elements in the AOT.

To populate the properties, you use the same values that you would use with Add Condition in the Filter results dialog window. The following table shows the properties for a filter node.

Modifying a Matrix Field in a Data Source

The earlier diagram shows a data source can include a collection of matrix field nodes. A matrix field is an XML metadata node you use to show a total for a group of related records on a spreadsheet. To add a matrix field, you use the Create New Matrix Field dialog window in the Dynamics AX tab. A matrix field includes the following components:

• A field that contains the values that you use to put related records into groups. For example, you could group customer records by Credit rating.

• A measure field that contains the numeric values that you want to total for each group. For example, you could total the Credit limit field to show the total credit available to each Credit rating group.

• An expression that specifies whether to include a record in one of the groups that appears in the spreadsheet. The total for the measure field will not include the value of any records that are excluded by the expression. For example, you could use the expression to show credit rating groups for customers from a specified country or region.

The following table shows the properties for a matrix field node. You use the read-only properties to identify the matrix field you want to work with. You can then use the Office Add-ins API to modify the expression property for that matrix field. If you change the value of a read-only property, the value that you set will be ignored when you save the XML to the spreadsheet.

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.