Microsoft Office

The Excel Adapter can be configured via the following files:

By default, all JSON configuration files are located in the %LocalAppData%/interop.io/io.Connect Desktop/UserData/<ENV>-<REGION> folder, where <ENV>-<REG> represents the environment and region of io.Connect Desktop (e.g., DEMO-INTEROP.IO).

The paths to the configuration files can be absolute paths, can contain environment variables (e.g., %LocalAppData%), or can be URLs. If a file isn't found at the specified location, the Excel Adapter will search for a file with the same name in its installation folder.

To specify the location of the ioxl2_config.json file, use the addinConfigPath key in the <appSettings> section of the GlueXl-AddIn64.xll.config file:

<appSettings>
    <add key="addinConfigPath" value="%localappdata%/interop.io/io.Connect Desktop/UserData/DEMO-INTEROP.IO/ioxl2_config.json" />
</appSettings>

The ioxl2_config.json file has the following properties:

Example ioxl2_config.json file:

{
    "auto-init-glue": "",
    "IGlueExcelService-ns": "IO.XL.",
    "volatile-udfs": ["IOXL_Invoke", "IOXL_SendData", "IOXL_RaiseNotification"],
    "custom-ribbon-path": "%localappdata%/interop.io/io.Connect Desktop/UserData/DEMO-INTEROP.IO/custom-ioxl-ribbon.json",
    "excel-service-configuration": "%localappdata%/interop.io/io.Connect Desktop/UserData/DEMO-INTEROP.IO/xl-service-configuration.json"
}

The Excel service configuration file allows you to define custom UDFs, keyboard shortcuts, context menus, ribbon menus, and custom task panes which the Excel Adapter will create when Excel starts. You can achieve the same at runtime by using the Excel API.

To specify the location of the file, use the "excel-service-configuration" property of the Excel Adapter settings. Alternatively, you can store the Excel service configuration in the app preferences of an interop-enabled app and specify the name of the app in the "excel-service-preference-app" property.

The Excel service configuration file has the following properties:

Custom UDFs

To define custom UDFs, use the "RegisterUDF" property. The code of the UDF must be written in C#. Use the Args array to access the arguments passed to the UDF (e.g., Args[0]) and a return statement to return the result to Excel:

{
    "RegisterUDF": [
        {
            "UdfName": "IO_XL_DiscountedValue",
            "Imports": ["System.Math"],
            "Helpers": "double DiscountFactor(double rate, double periods) => Pow(1 + rate, -periods);",
            "Script": "return (double)Args[0] * DiscountFactor((double)Args[1] / 100.0, (double)Args[2]);",
            "Parameters": [
                { "Name": "futureValue", "Description": "Future cash flow amount." },
                { "Name": "ratePercent", "Description": "Annual discount rate (%)." },
                { "Name": "periods", "Description": "Number of periods." }
            ]
        }
    ]
}

The UDF can then be used in Excel like any other function:

=IO_XL_DiscountedValue(1000, 5, 3)

Each object in the "RegisterUDF" array has the following properties:

⚠️ Note that custom UDFs can't be modified or removed while Excel is running.

Keyboard Shortcuts

To define keyboard shortcuts which will invoke an Interop method with the values of a specified range, use the "RegisterCallbackShortcut" property:

{
    "RegisterCallbackShortcut": [
        {
            "range": { "range": "A1:B2" },
            "shortcut": "^+k",
            "subscriptionInfo": {
                "callbackEndpoint": "MyShortcutHandler",
                "callbackId": "shortcut1"
            }
        }
    ]
}

Each object in the "RegisterCallbackShortcut" array has the following properties:

Besides the common callback properties, the Interop method will receive an rng property containing the values of the specified range.

⚠️ Note that you can register up to 20 keyboard shortcuts.

Context Menus

To add custom items to the Excel cell context menu, use the "CreateContextMenu" property. When the user clicks the menu item, the specified Interop method will be invoked:

{
    "CreateContextMenu": [
        {
            "caption": "Send to CRM",
            "toolTip": "Send the selected client to the CRM app",
            "menuPath": ["interop.io", "Actions"],
            "range": { "worksheet": "Clients", "range": "A2:A100" },
            "subscriptionInfo": {
                "callbackEndpoint": "SendToCRM",
                "selectionLimit": 10
            }
        }
    ]
}

Each object in the "CreateContextMenu" array has the following properties:

Ribbon Menus

To add custom items to the "Dynamics" dropdown in the interop.io ribbon tab, use the "CreateDynamicRibbonMenu" property. When the user selects the item and clicks the "Execute Dynamic" button, the specified Interop method will be invoked:

{
    "CreateDynamicRibbonMenu": [
        {
            "caption": "Refresh Prices",
            "toolTip": "Request the latest prices",
            "subscriptionInfo": {
                "callbackEndpoint": "RefreshPrices"
            }
        }
    ]
}

Each object in the "CreateDynamicRibbonMenu" array has the following properties:

Range Settings

The "range" object has the following properties:

Callbacks

The "subscriptionInfo" object describes the Interop method to invoke when the user interacts with an Excel element created by the Excel Adapter. It has the following properties:

The Interop method will receive an object with the following common properties:

The Excel Adapter adds an interop.io tab to the Excel ribbon. You can add your own tabs to the Excel ribbon and modify the attributes of the existing ribbon controls.

Custom Tabs

To define custom ribbon tabs, create a JSON file containing an array of tab objects and specify its location in the "custom-ribbon-path" property of the Excel Adapter settings. Alternatively, use the "custom-ribbon" property to define the tabs directly in the ioxl2_config.json file.

Each tab contains groups, and each group contains controls (buttons, boxes, button groups, and separators). When the user clicks a button, the Interop method specified in its "callback" property will be invoked:

[
    {
        "type": "tab",
        "label": "My Tab",
        "controls": [
            {
                "type": "group",
                "label": "Actions",
                "controls": [
                    {
                        "type": "button",
                        "label": "Send Data",
                        "size": "large",
                        "imageMso": "HappyFace",
                        "callback": {
                            "callbackEndpoint": "SendData",
                            "callbackId": "send-data"
                        }
                    },
                    {
                        "type": "separator"
                    },
                    {
                        "type": "box",
                        "horizontal": false,
                        "controls": [
                            {
                                "type": "button",
                                "label": "Buy",
                                "callback": { "callbackEndpoint": "Buy" }
                            },
                            {
                                "type": "button",
                                "label": "Sell",
                                "callback": { "callbackEndpoint": "Sell" }
                            }
                        ]
                    }
                ]
            }
        ]
    }
]

Each ribbon object has the following properties:

Attribute Overrides

To override the attributes of the ribbon controls (including the controls of the interop.io tab), create a JSON file and specify its location in the "ribbon-attributes-overrides-path" property of the Excel Adapter settings.

The keys of the JSON object are control IDs. Their values are objects whose keys are names of Office ribbon XML attributes (e.g., "label", "visible", "screentip"). To remove a control from the ribbon, set its value to null:

{
    "IOGlueXlTabId": {
        "label": "My Company"
    },
    "IOFeedbackBtn": null
}

To set the label, visibility, or enabled state of a control dynamically, use the "OnGetLabel", "OnGetVisible", or "OnGetEnabled" keys and provide the name of an Interop method as a value. The Interop method will receive the control ID in the ribbonObject property and the key name in the onGet property. It must return an object with a property with the same name as the key (e.g., { OnGetVisible: false }) within 800 milliseconds:

{
    "IOStatusBtn": {
        "OnGetVisible": "MyRibbonVisibilityHandler"
    }
}

The following table lists the IDs of the controls in the interop.io tab:

To create custom task panes in Excel when Excel starts, use the "CreateCTP" property of the Excel service configuration. To create custom task panes at runtime, use the Excel API.

A custom task pane contains UI elements described by the "ui" property. When the user clicks a button in the task pane, the Interop method specified in the button's "callback" property will be invoked. If the button doesn't have a callback, the Interop method specified in the "subscriptionInfo" property of the task pane will be invoked:

{
    "CreateCTP": [
        {
            "subscriptionInfo": {
                "callbackEndpoint": "PricingPanelHandler"
            },
            "ctpDescriptor": {
                "id": "pricingPanel",
                "title": "Pricing",
                "dockPosition": "msoCTPDockPositionLeft",
                "ui": {
                    "type": "Panel",
                    "margin": { "top": 10, "left": 10, "bottom": 10, "right": 10 },
                    "children": [
                        {
                            "type": "Label",
                            "text": "Select a pricing source:"
                        },
                        {
                            "type": "Button",
                            "id": "btnBloomberg",
                            "text": "Bloomberg Pricing",
                            "callback": { "callbackEndpoint": "PricingBloomberg" }
                        },
                        {
                            "type": "Border"
                        },
                        {
                            "type": "Button",
                            "id": "btnFidessa",
                            "text": "Fidessa Pricing",
                            "backColor": "#E0FFE0"
                        }
                    ]
                }
            }
        }
    ]
}

Each object in the "CreateCTP" array has the following properties:

The "ctpDescriptor" object has the following properties:

The following UI elements are available:

Each UI element has the following properties:

Besides the common callback properties, the Interop method will receive a descriptor property containing the ID of the clicked button.

The Excel Adapter writes its log to the %LocalAppData%/interop.io/io.Connect Desktop/UserData/ApplicationAdapters/Excel/GlueXLv2.log file. To view the latest log entries in Excel, click the "Logs" button in the interop.io ribbon tab.

To change the log level or the log file location, edit the <log4net> section of the GlueXl-AddIn64.xll.config file:

<log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
        <file type="log4net.Util.PatternString" value="%env{localappdata}\interop.io\io.Connect Desktop\UserData\ApplicationAdapters\Excel\GlueXLv2.log" />
        <!-- Other appender settings. -->
    </appender>
    <root>
        <!-- Possible values are "DEBUG", "INFO", "WARN", "ERROR", "FATAL". -->
        <level value="INFO" />
        <appender-ref ref="RollingFileAppender" />
    </root>
</log4net>