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://interopio.github.io/excel-playground/ 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 them 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://interopio.github.io/excel-playground/.
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 your 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 detailed information 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");

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);

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, 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",
        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);

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);

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);

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.

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

describeTableColumns()

Retrieves details about the columns of an Excel table.

Signature:

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

Parameters:

Return value: XLServiceResult

destroySubscription()

Destroys an existing subscription.

Signature:

destroySubscription(subscriptionId: string): Promise<XLServiceResult>

Parameters:

Return value: XLServiceResult

openWorkbook()

Opens an existing Excel workbook file.

Signature:

openWorkbook(fileName: 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

saveAs()

Saves an Excel workbook file.

Signature:

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

Parameters:

Return value: XLServiceResult

subscribe()

Subscribes for updates of an Excel range.

Signature:

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

Parameters:

updateTableColumns()

Updates columns in an Excel table.

Signature:

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

Parameters:

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

Settings for the subscription callbacks attached to the custom task pane elements.

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:

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:

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.

⚠️ Note that the XLServiceResult object will always contain all described properties, but only the ones relevant to the specific API operation will be populated. All other properties will be set to null.

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:

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.

Signature:

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

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 deltas array has the following properties:

Custom task pane subscription properties:

Return value: void