Microsoft Office

The Excel Adapter exposes a client API which your web apps can use to interoperate with Excel. The io.Connect Excel API provides convenient methods for manipulating Excel features (workbooks, tables, ranges, menus) and enables you to execute various operations in Excel - reading and writing data, subscribing for updates, creating menus dynamically, and more.

⚠️ Note that as version 2 of the Excel Adapter is still an experimental feature, the io.Connect Excel API is currently distributed together with the Excel Adapter bundle in the form of a standalone TypeScript file.

⚠️ Note that for your interop-enabled apps to be able to use the io.Connect Excel API, the Excel Adapter must be installed and Excel must be running.

You can use the Excel API testing app hosted at https://excel-playground.interop.io/ to explore the io.Connect Excel API. It provides a convenient way to invoke the exposed methods and get to know the data structures with which they work. It's possible to modify the arguments passed to the methods from the UI of the app and copy entire code snippets which you can then modify and test in the console of the app or in your own interop-enabled app:

The fastest way to start using the Excel API testing app is to:

1. Copy the app URL - https://excel-playground.interop.io/.
2. Open the io.Connect Desktop tray menu by right-clicking on the io.Connect Desktop tray icon.
3. Click on the "Open" menu item.
4. Paste the URL into the text input field.
5. Click the "Open" button to start the app in an io.Connect Window.

Another option is to create an app definition for the app and start it from the io.Connect launcher.

The Excel Service API enables you to interoperate with Excel from your interop-enabled web app. The following sections provide examples of using the Excel Service API methods to interact with various Excel features from your app.

ℹ️ For more details about the Excel Service API methods and data structures, see the API Reference section.

Initialization

To initialize the Excel Service API, instantiate the IOConnectXLService class and pass the initialized API object of the @interopio/desktop library as a required argument:

import IODesktop from "@interopio/desktop";
import { IOConnectXLService } from "./io-excel-service";

// Initializing the `@interopio/desktop` library.
const io = await IODesktop();

// Initializing the Excel Service API.
const xl = new IOConnectXLService(io);

// Now the Excel Service API will be available via the `xl` object.
await xl.openWorkbook("my-workbook.xlsx");

By default, the Excel Service API uses the "IO.XL." prefix for the Interop methods registered by the Excel Adapter. If you have changed the prefix via the "IGlueExcelService-ns" property of the Excel Adapter settings, pass the new prefix as a second argument:

const xl = new IOConnectXLService(io, "MyCompany.XL.");

Most methods accept a RangeInfo object specifying the workbook, worksheet, and range on which to perform the operation. If you don't specify a workbook or a worksheet, the active ones will be used.

Workbooks

The following sections describe the available operations related to manipulating Excel workbooks.

Create

To create an Excel workbook, use the createWorkbook() method. It accepts a name for the workbook file and a name for the worksheet to create as required arguments. Optionally, you can specify how save conflict resolutions should be handled by using the XLSaveConflictResolution enumeration:

import { XLSaveConflictResolution } from "./io-excel-service";

const workbookName = "My Workbook";
const sheetName = "My Sheet";
const conflictResolution = XLSaveConflictResolution.LocalSessionChanges;

await xl.createWorkbook(workbookName, sheetName, conflictResolution);

Open

To open an Excel workbook, use the openWorkbook() method and pass as a required argument a file name or a path to the Excel file to open:

const fileName = "my-workbook.xlsx";

await xl.openWorkbook(fileName);

Save

To save an Excel workbook, use the saveAs() method and pass as required arguments a RangeInfo object and a name with which to save the Excel file:

const range = { workbook: "Book1" };
const fileName = "my-workbook.xlsx";

await xl.saveAs(range, fileName);

List

To list all open workbooks, use the listWorkbooks() method. The data property of the result contains an array of objects describing the open workbooks:

const { data } = await xl.listWorkbooks();

data.forEach(({ name, path }) => console.log(`${name}: ${path}`));

ℹ️ For details on the properties of the returned data object, see the listWorkbooks() method.

Worksheets

The following sections describe the available operations related to manipulating Excel worksheets.

Create

To create a worksheet, use the createWorksheet() method. Optionally, pass the name of the workbook in which to create the worksheet and a name for the worksheet. If you don't specify a workbook, the worksheet will be created in the active workbook:

await xl.createWorksheet("Book1", "Prices");

Rename

To rename a worksheet, use the renameWorksheet() method and pass as required arguments a RangeInfo object specifying the worksheet and a new name for it:

const range = { workbook: "Book1", worksheet: "Sheet1" };

await xl.renameWorksheet(range, "Summary");

List

To list the worksheets in a workbook, use the listWorksheets() method. If you don't specify a workbook, the worksheets of the active workbook will be listed. The data property of the result contains an array of objects describing the worksheets:

const { data } = await xl.listWorksheets("Book1");

ℹ️ For details on the properties of the returned data object, see the listWorksheets() method.

Ranges

The following sections describe the available operations related to manipulating Excel ranges.

Activate

To select a range, use the activate() method. If you don't pass a RangeInfo object or don't specify a workbook in it, the Excel window will only be brought to the front:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

await xl.activate(range);

Read

To read the values of a range, use the read() method. The data property of the result contains a single value if the range is a single cell, or a 2D array of values otherwise:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

const { data } = await xl.read(range);

Write

To write values in a range, use the write() method. If you pass a single value, it will be written in each cell of the range. If you pass an array, its values will be written in the respective cells. Shorter rows of a 2D array are padded with empty cells:

// Writing the same value in all cells of the range.
await xl.write({ range: "A1:C3" }, 1);

// Writing a 2D array.
await xl.write({ range: "A5:B6" }, [[1, 2], [3, 4]]);

To resize the range automatically so that it fits the data, use the resizeOrientation property of the RangeInfo object. In this case, it's enough to specify only the top left cell of the range:

const rows = Array.from({ length: 50 }, (_, i) => [`Row ${i + 1}`, i]);

await xl.write({ range: "A1", resizeOrientation: "Vertical" }, rows);

Read & Write References

To read or write a range specified with an Excel reference (e.g., "Sheet1!A1:B5"), use the readRef() and writeRef() methods. The size of the range must match the size of the data you write:

const { data } = await xl.readRef("Sheet1!A1:B2");

await xl.writeRef("Sheet1!D1:E2", data);

Format

To apply a number format to a range, use the setRangeFormat() method and specify the format in the numberFormat property of the RangeInfo object:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "B2:B10", numberFormat: "#,##0.00" };

await xl.setRangeFormat(range);

Styles

To set the background and text colors of a range, use the applyStyles() method. Colors can be specified as HTML color names (e.g., "red") or hexadecimal values (e.g., "#FF0000"). Use "none" to remove the background color:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C1" };

await xl.applyStyles(range, "#1F4E79", "white");

Merge

To merge the cells of a range, use the merge() method. To merge the cells in each row of the range separately, pass true as a second argument. To unmerge cells, use the unmerge() method:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C1" };

await xl.merge(range);
await xl.unmerge(range);

Sort

To sort a range, use the sortRange() method. Pass the position (1-based) of the column within the range by which to sort. Optionally, specify whether to sort in descending order and whether the range has a header row:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

// Sorting by the second column in descending order.
await xl.sortRange(range, 2, true, true);

Find & Replace

To find the first cell containing a value, use the find() method. The address and data properties of the result contain the address and the value of the found cell:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

const { address } = await xl.find(range, "MSFT");

To replace values, use the findReplace() method. If you don't pass a replacement value, the data property of the result will contain an array of objects describing all found cells:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

// Replacing all occurrences of a value.
await xl.findReplace(range, "MSFT", "AAPL");

// Finding all occurrences of a value.
const { data } = await xl.findReplace(range, "AAPL");

ℹ️ For details on the properties of the returned data object, see the findReplace() method.

Clear Contents

To remove the values of a range while preserving its formatting, use the clearContents() method:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };

await xl.clearContents(range);

Used Range

To find the range of a worksheet that contains data, use the getUsedRange() method. The data property of the result contains an object describing the used range:

const range = { workbook: "Book1", worksheet: "Sheet1" };

const { data } = await xl.getUsedRange(range);

ℹ️ For details on the properties of the returned data object, see the getUsedRange() method.

Link to CSV

To import the data from a CSV file into a worksheet, use the linkToCSV() method. Pass a RangeInfo object specifying the top left cell where to insert the data and a path to the CSV file. The data will be refreshed automatically each time the workbook is opened:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1" };

await xl.linkToCSV(range, "C:/Data/prices.csv");

Comments

To add a comment to each cell of a range, use the writeComment() method. To remove the comments from a range, use the clearComments() method:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "B2" };

await xl.writeComment(range, "Price updated from Bloomberg.");
await xl.clearComments(range);

Tables

The following sections describe the available operations related to manipulating Excel tables.

Create

To create a table, use the createTable() method. Pass as required arguments a RangeInfo object, name for the table, style for the table, names for the columns, data for the columns, and a callback to be invoked when the table has been updated:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";
const tableStyle = "TableStyleDark1";
const columns = ["ID", "Name", "Email"];
const data = [
    ["1", "John Doe", "john@example.com"],
    ["2", "Jane Smith", "jane@example.com"]
];
const callback = origin => console.log(origin);

// Creating a named table with 3 named columns and 2 rows of data.
await xl.createTable(range, tableName, tableStyle, columns, data, callback);

Refresh

To refresh a table, use the refreshTable() method and pass as required arguments a RangeInfo object and the table name:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";

await xl.refreshTable(range, tableName);

Read Rows

To read table rows, use the readTableRows() method. Pass as required arguments a RangeInfo object, the table name, and the number of the row within the table from which to start the reading operation. Optionally, specify the number of table rows to read:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";
const fromRow = 1;
const rowsToRead = 10;

// Reading a specified number of rows from a table.
const { data } = await xl.readTableRows(range, tableName, fromRow, rowsToRead);

console.log(`Table columns data: ${JSON.stringify(data)}`);

Write Rows

To write table rows, use the writeTableRows() method. The rows will be inserted at the specified position. Pass as required arguments a RangeInfo object, the table name, the number of the row within the table where to insert the new rows (or null to add them after the last row), and a 2D array with the values for each cell of the rows:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";
const rowPosition = 1;
const values = [
    ["1", "John Doe", "john@example.com"]
];

// Writing (inserting) a single row at the specified position in the table.
await xl.writeTableRows(range, tableName, rowPosition, values);

Update Columns

To update table columns, use the updateTableColumns() method. Pass as required arguments a RangeInfo object, the table name, and an array of TableColumnOperationDescriptor objects describing the column operations:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";
const columnOperations = [
    {
        oldName: "Email",
        name: "Contact Details",
        position: null,
        op: "Rename"
    }
];

// Renaming a table column.
await xl.updateTableColumns(range, tableName, columnOperations);

Describe Columns

To retrieve details about table columns, use the describeTableColumns() method and pass as required arguments a RangeInfo object and the table name:

const range = { workbook: "Book1", worksheet: "Sheet1" };
const tableName = "My Table";

// Retrieving details about the table columns.
const { columns } = await xl.describeTableColumns(range, tableName);

List

To list the tables in a worksheet, use the listTables() method. The data property of the result contains an array of objects describing the tables:

const range = { workbook: "Book1", worksheet: "Sheet1" };

const { data } = await xl.listTables(range);

ℹ️ For details on the properties of the returned data object, see the listTables() method.

Delete Rows

To delete table rows, use the deleteTableRows() method. Pass as required arguments a RangeInfo object, the table name, and the position (1-based) of the first row to delete. Optionally, specify the number of rows to delete:

const range = { workbook: "Book1", worksheet: "Sheet1" };

// Deleting 3 rows starting from the second row.
await xl.deleteTableRows(range, "My Table", 2, 3);

Delete

To delete a table, use the deleteTable() method. To keep the table data as a regular range, pass true as a third argument:

const range = { workbook: "Book1", worksheet: "Sheet1" };

await xl.deleteTable(range, "My Table", true);

Pivot Tables

To create a pivot table, use the createPivotTable() method. Pass as required arguments a RangeInfo object specifying the source data, a RangeInfo object specifying where to place the pivot table, and a name for the pivot table. The destination worksheet must be in the same workbook as the source data. If you don't specify a destination worksheet, the pivot table will be placed in the source worksheet:

const sourceRange = { workbook: "Book1", worksheet: "Sales", range: "A1:D100" };
const destinationRange = { worksheet: "Summary", range: "A3" };

await xl.createPivotTable(sourceRange, destinationRange, "Sales Pivot");

⚠️ Note that the pivot table is created without any fields. The user must add fields to it from the Excel "PivotTable Fields" pane.

Subscriptions

The following sections describe the available operations related to subscribing for updates from Excel.

Range Updates

To subscribe for range updates, use the subscribe() method and pass as required arguments a RangeInfo object and an XLCallback for handling the updates:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };
const callback = ({ intersect, address }) => console.log(`Updated cell address: ${address}, updated cell value: ${intersect}`);

await xl.subscribe(range, callback);

Range Deltas

To receive details about the changed rows within a range, use the subscribeDeltas() method. The delta property of the object passed to the callback contains an array of objects describing the inserted, modified, and deleted rows:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C100" };
const callback = ({ delta }) => delta?.forEach(({ action, row }) => console.log(action, row));

await xl.subscribeDeltas(range, callback);

When the workbook is saved, the callback is invoked with an object whose action property is set to "saved".

ℹ️ For more details on the objects passed to the callback, see the XLCallback section.

Destroy Subscription

To destroy an existing subscription, use the destroySubscription() method and pass as a required argument the subscription ID:

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };
const callback = ({ intersect, address }) => console.log(`Updated cell address: ${address}, updated cell value: ${intersect}`);

// Creating a subscription.
const { subscriptionId } = await xl.subscribe(range, callback);

// Destroying an existing subscription.
const { message } = await xl.destroySubscription(subscriptionId);

console.log(message);

Raw Methods

Methods ending with Raw (e.g., subscribeRaw()) accept a SubscriptionInfo object instead of a callback. Instead of invoking a callback in your app, the Excel Adapter will invoke the Interop method specified in the callbackEndpoint property. This allows you to handle the updates in a different app or to target specific app instances:

// Registering an Interop method for handling the updates.
await io.interop.register("MyRangeHandler", ({ address, intersect }) => console.log(address, intersect));

const range = { workbook: "Book1", worksheet: "Sheet1", range: "A1:C10" };
const subscriptionInfo = { callbackEndpoint: "MyRangeHandler" };

await xl.subscribeRaw(range, subscriptionInfo);

Menus

The following sections describe the available operations related to manipulating Excel context and ribbon menus.

Context Menus

To add an item to the Excel cell context menu, use the createContextMenu() method. Pass as required arguments a caption for the menu item, an array with the captions of the parent submenus, a RangeInfo object specifying where the menu item will be available, and an XLCallback to invoke when the menu item is clicked. If you don't specify a workbook, worksheet, or range, the menu item will be available in all of them:

const range = { worksheet: "Clients", range: "A2:A100" };
const callback = ({ activeCell }) => console.log(`Selected client: ${activeCell}`);

const { menuId } = await xl.createContextMenu("Send to CRM", ["interop.io", "Actions"], range, callback);

To remove the menu item, use the destroyContextMenu() method and pass the menu ID:

await xl.destroyContextMenu(menuId);

Ribbon Menus

To add an item to the "Dynamics" dropdown in the interop.io ribbon tab, use the createDynamicRibbonMenu() method. When the user selects the item and clicks the "Execute Dynamic" button, the specified XLCallback will be invoked. The workbook and worksheet properties of the RangeInfo object specify where the menu item will be enabled:

const range = { workbook: "Book1" };
const callback = () => console.log("Refreshing prices...");

const { menuId } = await xl.createDynamicRibbonMenu("Refresh Prices", range, callback);

To remove the menu item, use the destroyRibbonMenu() method and pass the menu ID:

await xl.destroyRibbonMenu(menuId);

Custom Task Panes

To create a custom task pane in the active Excel window, use the createOrUpdateCTP() method. Pass as required arguments a RangeInfo object, a CTPDescriptor object describing the task pane, and an XLCallback to invoke when the user clicks a button in the task pane. The descriptor property of the object passed to the callback contains the ID of the clicked button:

const ctpDescriptor = {
    id: "pricing-panel",
    title: "Pricing",
    ui: {
        type: "Panel",
        children: [
            { type: "Label", text: "Select a pricing source:" },
            { type: "Button", id: "bloomberg", text: "Bloomberg" },
            { type: "Button", id: "fidessa", text: "Fidessa" }
        ]
    }
};
const callback = ({ descriptor }) => console.log(`Clicked button: ${descriptor}`);

await xl.createOrUpdateCTP({}, ctpDescriptor, callback);

To update an existing custom task pane, call createOrUpdateCTP() again with the same task pane ID. To remove a custom task pane, use the destroyCTP() method:

await xl.destroyCTP({ id: "pricing-panel" });

ℹ️ For details on the available UI elements, see the Configuration > Custom Task Panes section.

Keyboard Shortcuts

To invoke an Interop method with the values of a range when the user presses a keyboard shortcut, use the registerCallbackShortcut() method. Specify the shortcut in the format used by the Excel Application.OnKey method (e.g., "^+k" for CTRL + SHIFT + K). The rng property of the object passed to the Interop method contains the values of the range:

await io.interop.register("MyShortcutHandler", ({ rng }) => console.log(rng));

const range = { worksheet: "Sheet1", range: "A1:B2" };
const subscriptionInfo = { callbackEndpoint: "MyShortcutHandler" };

await xl.registerCallbackShortcut(range, "^+k", subscriptionInfo);

To remove the shortcut, use the unregisterCallbackShortcut() method:

await xl.unregisterCallbackShortcut("^+k");

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

Search Providers

To make the values of a range available in the io.Connect search, use the registerSearchProvider() method. Pass as required arguments a RangeInfo object and a SearchProviderDescriptor object. By default, the first column of the range is used as a result ID, the second as a display name, and the third as a description:

const range = { workbook: "Book1", worksheet: "Instruments", range: "A2:C100" };
const descriptor = { name: "ExcelInstruments", types: ["instrument"] };

await xl.registerSearchProvider(range, descriptor);

To remove the search provider, use the unregisterSearchProvider() method:

await xl.unregisterSearchProvider({ name: "ExcelInstruments" });

Macros

To run a VBA macro, use the runMacro() method. Pass the name of the macro and, optionally, an array of arguments for it:

await xl.runMacro("Module1.RecalculatePrices", ["EUR", 2]);

To list the available VBA macros, use the listMacros() method. Optionally, pass a workbook name to list only its macros. The data property of the result contains an array of objects describing the macros:

const { data } = await xl.listMacros("Book1.xlsm");

ℹ️ For details on the properties of the returned data object, see the listMacros() method.

⚠️ Note that listing macros requires the "Trust access to the VBA project object model" option to be enabled in the Excel Trust Center settings.

OLE Objects & Shapes

To retrieve details about the OLE objects (e.g., ActiveX controls, embedded documents) in a worksheet, use the getOleObjectsInfo() method. The oleObjects property of the result contains an array of objects describing the OLE objects:

const range = { workbook: "Book1", worksheet: "Sheet1" };

const { oleObjects } = await xl.getOleObjectsInfo(range);

To activate an OLE object, execute an OLE verb on it, or set the value of an ActiveX control, use the updateOleObject() method. Pass as required arguments a RangeInfo object, the name of the OLE object, the operation to perform ("activate", "verb", or "value"), and a value for the operation:

const range = { workbook: "Book1", worksheet: "Sheet1" };

// Setting the value of an ActiveX check box.
await xl.updateOleObject(range, "CheckBox1", "value", true);

// Opening an embedded document.
await xl.updateOleObject(range, "Object 1", "verb", "xlVerbOpen");

To retrieve details about the shapes (including form controls) in a worksheet, use the getShapesInfo() method. The shapeObjects property of the result contains an array of objects describing the shapes:

const range = { workbook: "Book1", worksheet: "Sheet1" };

const { shapeObjects } = await xl.getShapesInfo(range);

Custom UDFs

To register a custom UDF at runtime, use the registerUDF() method. The code of the UDF must be written in C#. Use the Args array to access the arguments passed to the UDF:

const parameters = [{ name: "value", description: "Value to double." }];

await xl.registerUDF("MY_DOUBLE", "return Convert.ToDouble(Args[0]) * 2;", [], [], "", parameters);

The UDF can then be used in Excel like any other function (e.g., =MY_DOUBLE(21)).

ℹ️ For more details on custom UDFs, see the Configuration > Custom UDFs section.

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

Active Context

To retrieve details about the active workbook, worksheet, cell, and selection, use the getActiveContext() method. Optionally, pass the maximum number of selected cells whose values to return (defaults to 1). If the user has selected more cells, no values will be returned:

const { data } = await xl.getActiveContext(10);

console.log(data.selectionAddress, data.selection);

ℹ️ For details on the properties of the returned data object, see the getActiveContext() method.

The following API reference sections describe the data structures and the methods provided by the io.Connect Excel API.

IOConnectXLService

The IOConnectXLService is the entry point for the Excel Service API. Instantiate this class to initialize the Excel Service API.

Parameters:

Methods:

The following sections describe the methods provided by the IOConnectXLService class.

activate()

Selects a range.

Signature:

activate(range?: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

applyStyles()

Sets the background and text colors of a range.

Signature:

applyStyles(range: RangeInfo, backgroundColor: string, foregroundColor: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

clearComments()

Removes the comments from a range.

Signature:

clearComments(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

clearContents()

Removes the values of a range while preserving its formatting.

Signature:

clearContents(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createContextMenu()

Adds an item to the Excel cell context menu.

Signature:

createContextMenu(
    caption: string,
    menuPath: string[],
    range: RangeInfo,
    callback: XLCallback
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createContextMenuRaw()

Raw version of createContextMenu() which accepts a SubscriptionInfo object instead of a callback.

Signature:

createContextMenuRaw(
    caption: string,
    menuPath: string[],
    toolTip: string | undefined,
    range: RangeInfo,
    subscriptionInfo: SubscriptionInfo
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createDynamicRibbonMenu()

Adds an item to the "Dynamics" dropdown of the interop.io ribbon tab. The callback is invoked when the user selects the item and clicks the "Execute Dynamic" button.

Signature:

createDynamicRibbonMenu(caption: string, range: RangeInfo, callback: XLCallback): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createDynamicRibbonMenuRaw()

Raw version of createDynamicRibbonMenu() which accepts a SubscriptionInfo object instead of a callback.

Signature:

createDynamicRibbonMenuRaw(
    caption: string,
    toolTip: string | undefined,
    range: RangeInfo,
    subscriptionInfo: SubscriptionInfo
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createOrUpdateCTP()

Creates a custom task pane in the active Excel window or updates an existing one with the same ID.

Signature:

createOrUpdateCTP(range: RangeInfo, ctpDescriptor: CTPDescriptor, callback: XLCallback): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createOrUpdateCTPRaw()

Raw version of createOrUpdateCTP() which accepts a SubscriptionInfo object instead of a callback.

Signature:

createOrUpdateCTPRaw(
    range: RangeInfo,
    ctpDescriptor: CTPDescriptor,
    subscriptionInfo: SubscriptionInfo
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createPivotTable()

Creates a pivot table without any fields. The tableName, worksheet, and address properties of the result contain the name of the pivot table, the name of the destination worksheet, and the address of the top left cell of the pivot table.

Signature:

createPivotTable(
    sourceRange: RangeInfo,
    destinationRange: RangeInfo,
    pivotName: string
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createTable()

Creates a table in an Excel workbook.

Signature:

createTable(
    range: RangeInfo,
    tableName: string,
    tableStyle: string,
    columns: string[],
    value: object[][],
    callback: XLCallback
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createWorkbook()

Creates a new Excel workbook with a specified worksheet.

Signature:

createWorkbook(
    workbookFile: string,
    worksheet: string,
    saveConflictResolution: XLSaveConflictResolution = XLSaveConflictResolution.UserResolution
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

createWorksheet()

Creates a worksheet.

Signature:

createWorksheet(workbook?: string, worksheetName?: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

deleteTable()

Deletes a table.

Signature:

deleteTable(range: RangeInfo, tableName: string, preserveData: boolean = false): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

deleteTableRows()

Deletes rows from a table.

Signature:

deleteTableRows(range: RangeInfo, tableName: string, fromRow: number, count?: number): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

describeTableColumns()

Retrieves details about the columns of an Excel table.

Signature:

describeTableColumns(range: RangeInfo, tableName: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroyContextMenu()

Removes an item from the Excel cell context menu.

Signature:

destroyContextMenu(menuId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroyContextMenuRaw()

Raw version of destroyContextMenu(). Use it to remove menu items created with createContextMenuRaw().

Signature:

destroyContextMenuRaw(menuId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroyCTP()

Removes a custom task pane.

Signature:

destroyCTP(ctpDescriptor: CTPDescriptor): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroyRibbonMenu()

Removes an item from the "Dynamics" dropdown of the interop.io ribbon tab.

Signature:

destroyRibbonMenu(menuId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroyRibbonMenuRaw()

Raw version of destroyRibbonMenu(). Use it to remove menu items created with createDynamicRibbonMenuRaw().

Signature:

destroyRibbonMenuRaw(menuId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

destroySubscription()

Destroys an existing subscription.

Signature:

destroySubscription(subscriptionId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

find()

Finds the first cell in a range containing a value. The address and data properties of the result contain the address and the value of the found cell.

Signature:

find(
    range: RangeInfo,
    what: string,
    after?: string,
    matchCase: boolean = false,
    matchEntireCell: boolean = false
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

findReplace()

Replaces values in a range. If a replacement value isn't specified, finds all cells containing the value and returns them in the data property of the result.

Signature:

findReplace(
    range: RangeInfo,
    find: string,
    replace?: string,
    matchCase: boolean = false,
    matchEntireCell: boolean = false
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

If a replacement value isn't specified, the data property of the result contains an array of objects, each with the following properties:

getActiveContext()

Returns details about the active workbook, worksheet, cell, and selection in the data property of the result.

Signature:

getActiveContext(selectionLimit: number = 1): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

The data property of the result contains an object with the following properties:

getOleObjectsInfo()

Returns details about the OLE objects (e.g., ActiveX controls, embedded documents) in a worksheet in the oleObjects property of the result. Each object in the oleObjects array describes an OLE object - its name, index, programmatic ID, type, position, size, top left and bottom right cells, placement, visibility, enabled state, linked cell, list fill range, and more.

Signature:

getOleObjectsInfo(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

getShapesInfo()

Returns details about the shapes (including form controls) in a worksheet in the shapeObjects property of the result. Each object in the shapeObjects array describes a shape - its name, ID, type, position, size, top left and bottom right cells, placement, visibility, alternative text, title, assigned macro, and text. For form controls, it also contains the control type, linked cell, list fill range, and value.

Signature:

getShapesInfo(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

getUsedRange()

Returns details about the range of a worksheet that contains data in the data property of the result.

Signature:

getUsedRange(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

The data property of the result contains an object with the following properties:

linkToCSV()

Imports the data from a CSV file into a worksheet. The data is refreshed automatically each time the workbook is opened.

Signature:

linkToCSV(range: RangeInfo, csv: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

listMacros()

Lists the available VBA macros in the data property of the result.

Signature:

listMacros(workbook?: string, includeGlue: boolean = false): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

The data property of the result contains an array of objects, each with the following properties:

listTables()

Lists the tables in a worksheet in the data property of the result.

Signature:

listTables(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

The data property of the result contains an array of objects, each with the following properties:

listWorkbooks()

Lists the open workbooks in the data property of the result.

Signature:

listWorkbooks(): Promise<XLServiceResult>

Return value: XLServiceResult

The data property of the result contains an array of objects, each with the following properties:

listWorksheets()

Lists the worksheets in a workbook in the data property of the result.

Signature:

listWorksheets(workbook?: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

The data property of the result contains an array of objects, each with the following properties:

merge()

Merges the cells of a range.

Signature:

merge(range: RangeInfo, across: boolean = false): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

openWorkbook()

Opens an existing Excel workbook file.

Signature:

openWorkbook(fileName: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

read()

Reads the values of a range. The data property of the result contains a single value if the range is a single cell, or a 2D array of values otherwise.

Signature:

read(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

readRef()

Reads the values of a range specified with an Excel reference. The data property of the result contains a single value if the range is a single cell, or a 2D array of values otherwise.

Signature:

readRef(reference: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

readTableRows()

Reads rows from an Excel table.

Signature:

readTableRows(
    range: RangeInfo,
    tableName: string,
    fromRow: number,
    rowsToRead?: number
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

refreshTable()

Refreshes an Excel table.

Signature:

refreshTable(range: RangeInfo, tableName: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

registerCallbackShortcut()

Registers a keyboard shortcut which invokes an Interop method with the values of a range. The rng property of the object passed to the Interop method contains the values of the range.

Signature:

registerCallbackShortcut(range: RangeInfo, shortcut: string, subscriptionInfo: SubscriptionInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

registerSearchProvider()

Registers a range as an io.Connect search provider.

Signature:

registerSearchProvider(range: RangeInfo, descriptor: SearchProviderDescriptor): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

registerUDF()

Registers a custom UDF written in C#.

Signature:

registerUDF(
    udfName: string,
    script: string,
    deps: string[],
    imports: string[],
    helpers: string,
    parameters: UdfParameterDescriptor[]
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

renameWorksheet()

Renames a worksheet.

Signature:

renameWorksheet(range: RangeInfo, newName: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

runMacro()

Runs a VBA macro. The return value of the macro isn't available.

Signature:

runMacro(macro: string, params?: any[]): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

saveAs()

Saves an Excel workbook file.

Signature:

saveAs(range: RangeInfo, fileName: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

setRangeFormat()

Applies the number format specified in the numberFormat property of the RangeInfo object to a range.

Signature:

setRangeFormat(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

sortRange()

Sorts a range.

Signature:

sortRange(
    range: RangeInfo,
    sortColumn: number,
    descending: boolean = false,
    hasHeader: boolean = true
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

subscribe()

Subscribes for updates of an Excel range.

Signature:

subscribe(rangeInfo: RangeInfo, callback: XLCallback): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

subscribeDeltas()

Subscribes for details about the inserted, modified, and deleted rows within a range.

Signature:

subscribeDeltas(rangeInfo: RangeInfo, callback: XLCallback): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

subscribeDeltasRaw()

Raw version of subscribeDeltas() which accepts a SubscriptionInfo object instead of a callback.

Signature:

subscribeDeltasRaw(range: RangeInfo, subscriptionInfo: SubscriptionInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

subscribeRaw()

Raw version of subscribe() which accepts a SubscriptionInfo object instead of a callback.

Signature:

subscribeRaw(range: RangeInfo, subscriptionInfo: SubscriptionInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

unmerge()

Unmerges the cells of a range.

Signature:

unmerge(range: RangeInfo): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

unregisterCallbackShortcut()

Removes a keyboard shortcut registered with registerCallbackShortcut() and restores its default Excel behavior.

Signature:

unregisterCallbackShortcut(shortcut: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

unregisterSearchProvider()

Removes a search provider registered with registerSearchProvider().

Signature:

unregisterSearchProvider(descriptor: SearchProviderDescriptor): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

updateOleObject()

Activates an OLE object, executes an OLE verb on it, or sets the value of an ActiveX control. The Excel window is brought to the front before the operation is performed.

Signature:

updateOleObject(
    range: RangeInfo,
    oleObjectName: string,
    target: string,
    value: any,
    modalInfo?: ModalInfo
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

updateTableColumns()

Updates columns in an Excel table.

Signature:

updateTableColumns(
    range: RangeInfo,
    tableName: string,
    columnOps: TableColumnOperationDescriptor[]
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

write()

Writes values in a range. A single value is written in each cell of the range. The values of an array are written in the respective cells.

Signature:

write(range: RangeInfo, value: object): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

writeComment()

Adds a comment to each cell of a range. Existing comments are replaced.

Signature:

writeComment(range: RangeInfo, comment: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

writeRef()

Writes values in a range specified with an Excel reference. The size of the range must match the size of the data.

Signature:

writeRef(reference: string, value: object): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

writeTableRows()

Writes (inserts) rows in an Excel table.

Signature:

writeTableRows(
    range: RangeInfo,
    tableName: string,
    rowPosition: number | null,
    value: object[][]
): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

CallbackInfo

Describes an Interop method to invoke as a callback (e.g., when a button in a custom task pane is clicked).

Properties:

CTPDescriptor

Describes a custom task pane.

Properties:

RangeInfo

Describes an Excel range associated with the API operation to execute in Excel (e.g., read, write, subscribe, save, and more) and various settings to apply to that operation. This object is a required argument for most methods of the Excel Service API and is used for locating the Excel file on which to perform the API operation, as well as for providing settings for the operation itself. All properties are optional and if you set any properties that aren't relevant to the current API operation, they will be ignored.

Properties:

SearchProviderDescriptor

Describes a search provider.

Properties:

SubscriptionInfo

Subscription settings when executing subscription operations (usually via raw methods such as subscribeRaw()).

Properties:

TableColumnInfo

Describes a table column.

Properties:

TableColumnOperationDescriptor

Describes a table column operation.

Properties:

UdfParameterDescriptor

Describes a parameter of a custom UDF. The parameter names and descriptions are displayed in the Excel Insert Function dialog.

Properties:

UIDescriptor

Describes a UI element in a custom task pane.

Properties:

XLServiceResult

Describes the result from executing an Excel Service API operation. Contains details about the result from executing the operation and additional details relevant to the specific API operation. Only the properties relevant to the specific API operation are populated.

The following tables describe all available XLServiceResult properties grouped by operation relevance.

Operation result properties:

Common properties:

Table operation properties:

Custom task pane operation properties:

Context and ribbon menu operation properties:

File operation properties:

Window operation properties:

OLE object and shape operation properties:

Data operation properties:

LifetimeType

Enumeration of the possible values for determining the lifetime of the subscription - i.e., the conditions under which the Excel Adapter should stop sending subscription updates to the client:

XLSaveConflictResolution

Enumeration of the possible values for handling save conflict resolutions:

DataOrientation

Determines the direction (horizontal or vertical) in which to expand any array data passed to the methods for executing writing operations in Excel.

Signature:

type DataOrientation = "Horizontal" | "Vertical";

TableColumnOperation

Indicates the table column operation to perform. The "Update" operation is reserved for future use.

Signature:

type TableColumnOperation = "Add" | "Delete" | "Rename" | "Update";

Thickness

Describes the margin around a UI element in a custom task pane in pixels.

Signature:

type Thickness = { left: number; top: number; right: number; bottom: number };

UIHorizontalAlignment

Horizontal alignment of a UI element in a custom task pane.

Signature:

type UIHorizontalAlignment = "Left" | "Center" | "Right" | "Stretch";

UIType

Type of a UI element in a custom task pane. The following values are supported:

UIVerticalAlignment

Vertical alignment of a UI element in a custom task pane.

Signature:

type UIVerticalAlignment = "Top" | "Center" | "Bottom" | "Stretch";

XLCallback

Callback to be invoked when subscription updates are available.

Signature:

type XLCallback = (origin: any, ...props: any[]) => void;

Parameters:

The origin parameter is an object whose shape varies depending on the particular subscription operation. It contains common properties valid for all subscription operations, as well as specific properties for some of the available subscription operations.

Common subscription properties:

Table subscription properties:

Range subscription properties:

Range delta subscription properties:

Each object in the delta array has the following properties:

Keyboard shortcut properties:

Custom task pane subscription properties:

Return value: void