Add “Open in Excel” feature to a new custom form D365FO

In Dynamics 365 Finance and Operation, Microsoft provides an Open in Excel feature to all the forms where details masters and simple list patterns are applied. It is an out-of-the-box functionality that checks If any data entity exists which have the same root data source as the current form.

Reference: In the All Customers form, the root data source is Custtable and CustCustomerV2Entity also has the same root data source, So the Open in Excel feature exists on this form which opens the data in Excel where you can modify, delete or add new data using Excel Add-in.

You can also add/remove options in this dialog by setting the visibility and creating more data entities having the same root data source as the form

Problem Statement:

Need to add a custom menu option that exports filtered records that are selected

Resolution:

You need to perform the following steps,

• Implement the OfficeIGeneratedWorkbookCustomExporter interface.
• Write your code in the getDataEntityContext(OfficeGeneratedExportMenuItem _menuItem) method
• Get the ExportToExcelDataEntityContext object for your entity
• Use its filter method to apply your filter

public class OpenSalesOrderForm extends FormRun implements OfficeIGeneratedWorkbookCustomExporter
{
    OfficeGeneratedExportMenuItem   filteredEntityMenuItem;
	
    public void customizeMenuOptions(OfficeMenuOptions _menuOptions)
    {
        filteredEntityMenuItem = OfficeGeneratedExportMenuItem::construct(tableStr(FilteredSOEntity), 'FilteredSOTable');
        filteredSOEntityMenuItem.displayName(strFmt('Filtered SO Table (%1)', curExt()));

        var dataEntityEnumerator = _menuOptions.dataEntityOptions().getEnumerator();

        while (dataEntityEnumerator.moveNext())
        {
            OfficeMenuDataEntityOptions dataEntityOptions = dataEntityEnumerator.current();

            if (dataEntityOptions.dataEntityName() == tableStr(FilteredSOEntity) )
            {
                dataEntityOptions.includeDefault(false);
            }
        }

        _menuOptions.customMenuItems().addEnd(filteredEntityMenuItem);
		
        filteredEntityMenuItem.visible(true);
    }

	ExportToExcelDataEntityContext getDataEntityContext(OfficeGeneratedExportMenuItem _menuItem)
    {
		SalesTable salesTableLocal = salesTable_ds.cursor();
        
		ExportToExcelDataEntityContext context = ExportToExcelDataEntityContext::constructDefault(literalStr(FilteredSOEntity));


		ExportToExcelFilterTreeBuilder filterBuilder = new ExportToExcelFilterTreeBuilder(_menuItem.dataEntityName());

		ListEnumerator entities = context.entities().getEnumerator();
		while (entities.moveNext())
		{
			if (entities.current().entityName() == _menuItem.dataEntityName())
			{
				entities.current().filter(filterBuilder.and(filterBuilder.companyFilter(),
				filterBuilder.areEqual(fieldStr(FilteredSOEntity, SalesId), salesTableLocal.SalesId)));
				break;
			}
		}
        return context;
    }
}
                                

Build Solution:

After building/re-building your solution in Visual Studio, you will be able to see the custom option in Open-In Excel and the out of the box option will be unavailable.

After clicking this option, you will be asked to download the Excel, and after opening the downloaded excel you will only see the filtered records in the Excel Sheet.