MS Office

The Excel Adapter allows apps to use Excel as a local data editor. The app uploads tabular data into Excel, so that the user may view it and edit it in a familiar environment. Any changes can then be transmitted back to the app for validation, processing, auditing and/or long-term storage.

As shown in the Setup section, you need to initialize the @glue42/office library and set the excel property of the configuration object to true:

const config = {
    // ...,
    excel: true // enable Excel integration
}

After that, get a reference to the Excel Adapter API:

Glue4Office(config)
    .then(office => {
        const excel = office.excel
        // interact with Excel
    })
    .catch(console.error)

When io.Connect Desktop is initialized, you can check whether Excel is running and the Excel Adapter is loaded:

console.log(`Excel Adapter is ${excel.addinStatus ? "available" : "unavailable"}`);

You can use the onAddinStatusChanged() method to track the availability of the Excel Adapter. You may find this useful if you need to track when to enable or disable certain elements of your app user interface.

const unsubscribe = excel.onAddinStatusChanged(available => {
    console.log(`Excel Adapter is ${available ? "available" : "not available"}`)
});

The available argument passed to your callback will be true if and only if:

• Excel is running.
• The Excel Adapter is installed and enabled in Excel.
• The Excel Adapter and your app are using the same connectivity configuration and are connected to the same io.Connect Gateway.

In any other case the available flag will be false.

To stop listening for connection status changes, simply call the returned function:

unsubscribe();

To send a table to Excel, you need to call the openSheet() method.

You must specify the shape of your app data, optionally pass the row data and certain customization options. All of these need to be packaged in a single OpenSheetConfig object, passed to the openSheet() call.

The example below assumes your app is displaying a financial portfolio (e.g., a list of stocks a person owns). Here is how to push the data in Excel:

const config = {
    columnConfig: [
        { header: "Symbol", fieldName: "symbol" },
        { header: "Quantity", fieldName: "quantity" }
    ],
    data: [
        { quantity: 100, symbol: "AAPL" },
        { quantity: 200, symbol: "GOOG" }
    ]
}

excel.openSheet(config)
    .then(sheet => console.log("Sent data to Excel"))

The example above will open a new Excel workbook, create an empty sheet and will create the following table and place it at position A1 (row 1, column A):

The header in the ColumnConfig is optional and specifies the caption of the column in Excel. If omitted, the field name will be used.

If you don't need to send any data but just set up a table in Excel for the user to populate, then you can omit the data property. You can still populate the table later using the update() method on the Sheet object.

Note that the Promise returned by the openSheet() method resolves with a reference to a Sheet object.

By default, when your app sends data to Excel, the Application Adapter will create a new workbook, a new spreadsheet in the workbook, and place the unformatted data in the 1st row and column (A1).

There are certain customizations you can apply by specifying the options property in the OpenSheetConfig object.

Column Customization

If you are planning to take updates from the user into your app, you shouldn't rely on the ordering of the rows when accepting the data because the user might have filtered or sorted the data before returning it to your app. This means that:

• all your rows must be keyed somehow, so you can track what has changed
• you must not accept changes to columns containing keys

You can also specify a background and/or a foreground color and a column width:

const columns = [
    {
        header: "Symbol",
        fieldName: "symbol",
        width: 80
    }
    // ...
]

Preventing Saving Temporary Workbooks

If your users need to message data to Excel, but aren't allowed to save it locally, and should instead return the data to be saved in your app, you can set the inhibitLocalSave flag to true to prevent the users from saving temporary workbooks.

Custom Workbook, Sheet and Range

If your app needs to create (or re-open and re-use) a specific workbook, or place data in a specific spreadsheet and location in the spreadsheet, you can use the workbook, worksheet and topLeft options:

const config = {
    columnConfig,
    data,
    options: {
        workbook: "ClientData.xls",
        worksheet: "John Doe",
        topLeft: "B2",
        dataRangeName: "ClientData",
        clearGrid: true
    }
}

Note that all settings except the columnConfig property are optional. If the workbook doesn't exist, it is going to be created, otherwise re-opened. If there is data in the specified spreadsheet, it's going to be wiped, unless you've set clearGrid to false. In the example above the data will be placed in the John Doe spreadsheet, starting on the 2nd row and column (B2). You can also use Row/Column references, e.g., R2C2 (row 2 column 2). Specifying dataRangeName names the range of cells which starts at B2 and spans your data to ClientData.

Using Templates

Excel Templates are workbooks that can be used to create a framework that the Excel Adapter should use when displaying a new set of data to the user. When the app invokes an io.Connect Excel API method, it may request that the data be added to a copy of an existing workbook (the template) that has been formatted to present the data correctly for the user. The Excel Adapter will then make a copy of the workbook and paste the data into it, instead of using a new blank workbook.

If you want to send formatted data to Excel or you want to include more than just the data (e.g. headers, footers, charts and maps), you can use templateWorkbook and templateSheet:

const config = {
    columnConfig,
    data,
    options: {
        templateWorkbook: "ReportTemplate.xls",
        templateWorksheet: "Data with Chart"
    }
}

Once you have obtained a reference to the opened spreadsheet, you can subscribe for and start tracking updates made by the user, receive and validate them in your app, using the onChanged() method on the Sheet object:

const config = {
    columnConfig: [
        { header: "Symbol", fieldName: "symbol" },
        { header: "Quantity", fieldName: "quantity" }
    ],
    data: [
        { quantity: 100, symbol: "AAPL" },
        { quantity: 200, symbol: "GOOG" },
    ],
    options: {
        // Configure Excel to trigger sheet change events when the user changes
        // one or more cells in a given row and then selects a cell in a different row.
        // By default, sheet change events will be triggered when the user clicks a predefined button in Excel.
        updateTrigger: ["row"]
    }
};

excel.openSheet(config)
    .then(sheet => {
        console.log("Sent data to Excel", sheet);

        sheet.onChanged((data, errorCallback, doneCallback, delta) => {
            console.log("data: ", data);
            console.log("delta: ", delta);

            doneCallback();
        });
    });

The function passed to onChanged() will be called with the data sent from Excel. The data parameter holds an array of objects where each object corresponds to a row in Excel. Each object will have a number of properties populated with data, where each property will correspond to the fieldName property you have passed in the respective column definition when calling openSheet().

You can optionally validate the data by calling the errorCallback function or accept the changes by calling the doneCallback function. Declarative and imperative validation is explained in details in the Data Validation section below.

The delta argument is a DeltaItem object which holds the changes made to the row(s).

The onChanged() method call creates a subscription for changes done by the user. To unsubscribe, simply call the returned unsubscribe() function:

const unsubscribe = sheet.onChanged(...)
unsubscribe()   // sheet no longer tracked for changes

There are 2 types of validation you can perform before you accept data from Excel:

• preventing users from typing incompatible data (e.g., column is numeric but the user types in some text), which you can do using Declarative Validation;
• preventing users from breaking the integrity of your data (which can span all data), which you can do using Programmatic Validation;

Declarative Validation

You can specify what kind of data the user is allowed to type in a given column using the validation property from the ColumnConfig:

const quantityColumn = {
    header: "Quantity",
    fieldName: "quantity",
    // whole numbers only
    validation: {
        alert: "Stop",
        type: "WholeNumber"
    }
}

The possible alert types are:

The possible values for the type property are:

The list validation type requires you to pass one more property holding the allowed set of values:

const stateColumn = {
    header: "State",
    fieldName: "state",
    validation: {
        alert: "Stop",
        type: "List",
        list: ["Arizona", "California", ...]
    }
}

When your app has set the validation property and the alert is Stop, Excel won't even attempt to return data back to your app if the user input is invalid.

Programmatic Validation

When your app needs a more sophisticated validation than what the validation property can offer, you can write code to completely control the validation of the data sent from Excel to your app.

Here is again how subscribing for user updates looks like:

sheet.onChanged((data, errorCallback, doneCallback, delta) => {
    // add your custom validation here
})

When your app receives an update from Excel, you can signal Excel back by calling the errorCallback and let Excel know that there were errors in the user's input which need to be corrected in Excel before the data is accepted by your web app.

The errorCallback accepts a list of validation errors, where a validation error specifies which cell (row and column) is in error and what the problem is. Here is an example of handling multiple validation errors:

sheet.onChanged((data, errorCallback, doneCallback, delta) => {
    // ...
    const errors = data.reduce(
        (errors, rowData, rowIndex) => {
            if (!rowData["firstName"]) {
                errors.push({
                    row: rowIndex + 1,
                    column: 0,
                    description: "First name is mandatory"
                })
            }
            if (Number(rowData["subscriptionMonths"]) < 6) {
                errors.push({
                    row: rowIndex + 1,
                    column: 2,
                    description: "Subscription period must be at least 6 months",
                    text: "6"   // replacing what the user typed
                })
            }
        }, [])

    // if during the validation there are any errors accumulated
    // you need to call the errorCallback, otherwise the doneCallback
    if (errors.length > 0) {
        errorCallback(errors)
    }
    else {
        doneCallback()
    }
})

The column property in the validation error can either be a Number (the 0-based column index) or a String (the fieldName specified in the column config).

You can also customize the way Excel displays the errors by setting the foregroundColor, backgroundColor and some other properties of the validationError object.

Controlling Update Frequency

If you are expecting updates from Excel, you can specify how often your app should get updates by setting a value for the updateTrigger property in the OpenSheetOptions object.

The possible values are:

The row options is the most interactive, since your app gets updated as the user moves through the spreadsheet.

When using the button option, you can also customize the caption of the button and where it is placed by using the buttonText and buttonRange properties:

const config = {
    columnConfig,
    data,
    {   // options follow
        updateTrigger: "Button",
        buttonText: "Send back data",
        buttonRange: "A1:C1"    // button spans 3 cells
    }
}

excel.openSheet(config).then(sheet => ...)

Upon launching, the Excel Adapter registers the following Interop methods and streams:

For a complete list of the available Excel Adapter API methods and properties, see the Excel Adapter Reference Documentation.