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:

Method	Description
GetWorkbookXmlPart	Get the custom XML part from a spreadsheet. The method returns a WorkbookXmlPart object.
SetWorkbookXmlPart	Saves changes to the custom XML part of a spreadsheet.
GetDocumentXmlPart	Get the custom XML part from a Word document. The method returns a DocumentXmlPart object.
SetDocumentXmlPart	Saves changes to the custom XML part of a document.

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:

Property	Type	Description
IsDesignLocked	bool	Specifies whether the spreadsheet or document is locked. To enable the lock, set the property to true. For information about how to programmatically access the workbook options node, see How to: Use Office Add-ins to Lock an Excel Spreadsheet. If the file is locked, you cannot use the controls in the Dynamics AX tab to modify the data source or fields that appear in the spreadsheet.
RefreshAllDataOnOpen	bool	Specifies whether the data in the spreadsheet is refreshed when the file is opened. To refresh the data, set the property to true. You can also use the Options for working with Microsoft Dynamics AX window from the Dynamic AX tab to set this property value.

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.

XML node	Class
Office Add-ins XML	You use the WorkbookXmlPart class to represent the Office Add-ins XML part from a spreadsheet. You use the DocumentXmlPart class to represent the Office Add-ins XML part from a document.
Workbook options	You use the WorkbookOptionsXmlPart class to access the workbook option properties for a spreadsheet. You use this class to change the IsDesignLocked and RefreshAllDataOnOpen properties for a spreadsheet.
Data sources	You use the DataSourcesXmlPartDictionary class to get the collection of data sources in the Office Add-ins XML.
Data source	You use the DataSourceXmlPart class to get a single data source node from the data source collection of a spreadsheet or document.
Filter collection	You use the FilterXmlPartCollection class to get the filters for a specified data source. You can use the Add method of this class to create a new filter node. You can also use the Remove method to delete an existing filter node.
Filter	You use the FilterXmlPart class to view and modify the properties of an individual filter node.
Matrix field collection	You use the MatrixFieldXmlPartCollection class to get the matrix fields for a specified data source.
Matrix field	You use the MatrixFieldXmlPart class to view the properties of an individual matrix field node and modify the expression property of that matrix field.

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.

Property	Type	Description
Criteria	string	Specifies the filter operation and value. For example, you could use > 0 to specify the criteria for a credit limit field.
FieldFullyQualifiedName	string	Specifies the fully qualified name of the field for the filter. The format for the name is <data source>.<table name>.<field name>.
TableFullyQualifiedName	string	Specifies the fully qualified name of the table where the field appears. The format for the name is <data source>.<table name>.

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.

Property	Type	Description
DataSourceName	string	The name of the data source. This property is read-only.
DataSourceQualifiedName	string	The full name for the data source. This property is read-only.
DisplayName	string	The name of the filter. This property is read-only.
ExpressionModel	string	The serialized form of the expression associated with the field. To modify a matrix field, you change the values in the expression To de-serialize and modify the expression, you can use the ExpressionInfo class and related classes in the Microsoft.Dynamics.AX.Framework.OfficeAddin assembly.
FieldName	string	The name of the field. This property is read-only.
FullyQualifiedFieldName	string	Specifies the fully qualified name of the field for the filter. This property is read-only.
Id	Guid	Specifies the ID of the MatrixField object associated with this filter. This property is read-only.
MeasureFieldFullyQualifiedName	string	Specifies the full name of the field that defines the measure for the matrix field. This property is read-only.

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