AboutSupport
Docs

Microsoft Excel integration

Integrate your OpenFin solution with the Microsoft Excel desktop application.

Suggest Edits

OpenFin’s Microsoft Excel integration enables application providers to integrate their applications with Microsoft Excel running on the same machine, providing two-way communication between Excel and your OpenFin applications.

The integration utilizes Microsoft’s Excel Interop API which provides automation and control of the Excel desktop application, without requiring installation of any custom dependencies on target desktops.

With the Excel integration, you can do things like:

  • Create, open and save workbooks and worksheets from your OpenFin app.

  • Push and receive data between your OpenFin app and a worksheet.

  • Programmatically format cells in worksheets.

Prerequisites

  • OpenFin

    • Minimum runtime version 15.80.49.30

  • Microsoft Excel 2013 or later

  • .NET Framework 4.7.2

Installation

Add the @openfin/excel NPM package as a dependency for your app:

npm install @openfin/excel

Configuration

On initialization, the client-side API downloads and launches a .NET adapter process in the background which functions as an intermediary between the client-side API and Microsoft’s Excel Interop API.
Some app configuration is required in order to facilitate this process.

Configure API security

The Excel integration API requires permission to use the System.downloadAsset and System.launchExternalProcess secured APIs, so be sure to declare the following permissions in your app’s manifest:

"permissions": { 
  "System": {
    "downloadAsset": true,
    "launchExternalProcess": true
  }
},

📘

Note

The location of the permissions object depends on whether your app uses Platform API.
See Declaring APIs in an application manifest file for more information.

In addition, when running on v20 or later of the OpenFin runtime, the desktop owner must also allow your application to use this secured API in desktop owner settings. However, this is not required during development if your application is running from `localhost``, in which case there is no need to configure desktop owner settings.

Self-hosting the adapter

Corporate firewalls might prevent access to OpenFin’s CDN, in which case, the Excel integration cannot download the required adapter package.
To remedy this, you can self-host the adapter package by downloading the relevant package and hosting it within the corporate network.

Adapter packages for the Excel integration can be downloaded from OpenFin’s Versions page (check the Excel Integration Versions table on the Integrations tab).

Secondly, you must make any app that uses the Excel integration aware of the alternate adapter package location by declaring it as an appAsset in your app’s manifest:

"appAssets": [{
  "alias": "excel-adapter",
  "src": "ADAPTER_PACKAGE_URL",
  "target": "OpenFin.Excel.exe",
  "version": "ADAPTER_PACKAGE_VERSION"
  }]

Note that you should:

  • Ensure that the alias and target values match those in the example above.

  • Replace "ADAPTER_PACKAGE_URL" for the src property with the URL where your app can download the adapter package from. It is recommended to include the package version in the URL to avoid version conflicts.

  • Replace "ADAPTER_PACKAGE_VERSION" for the version property with the NPM package version being used by your app.

🚧

Warning

Do not mix package versions!

The version of the self-hosted adapter package declared in appAssets must match the NPM package version used by the app. If it does not, an error is thrown when the app attempts to use the Excel integration.

To avoid conflicts, ensure that the hosted URL contains the version of the adapter package and take care when upgrading NPM dependencies — if the @openfin/excel package is upgraded you must also update appAssets to match.

Usage

📘

Note

Take a look at the starter project for the Excel integration for a working code example.

The client-side API contains the getExcelApplication function.
Import and call this function to start controlling Excel:

import { getExcelApplication } from '@openfin/excel';

const excel = await getExcelApplication();

When you call this function for the first time, it downloads and starts a .NET adapter process for your application to use.

The getExcelApplication function returns an object that enables you to begin interacting with Excel at the workbook level, and also to quit Excel closing it on the desktop:

// Quit Excel closing all open workbooks in the process (user will be prompted to save any unsaved changes)
await excel.quit();

// Quit Excel closing all open workbooks in the process (user will not be prompted and any unsaved changes will be lost)
await excel.quit(false);

Working with workbooks

You can discover open workbooks by calling getWorkbooks:

// Output the names of all open workbooks to the console
const openWorkbooks = await excel.getWorkbooks();
openWorkbooks.forEach(async (workbook) => {
  const workbookName = await workbook.getName();
  console.log(workbookName);
});

Alternatively, you can open an existing workbook file or create a new workbook based on the default "Blank workbook" template:

// Open workbook file from C:\\temp\\MyWorkbook.xlsx
const existingWorkbook = await excel.openWorkbook('C:\\\\temp\\\\MyWorkbook.xlsx');

// Open a new workbook
const workbook = await excel.createWorkbook();

Once you have a workbook object, you can do things like get the name, give it focus, save, or close:

// Get the name of the workbook
const workbookName = await workbook.getName();

// Activate the first window associated with the workbook, bringing it in to focus
await workbook.activate();

// Get the current calculation mode which determines when the workbook (or worksheet) is re-calculated
await workbook.getCalculationMode();

// Trigger calculation for all worksheets in the workbook (only necessary if getCalculationMode does not return "Automatic")
await workbook.calculate();

// Save changes to disk
const savePath = await workbook.save();

// Save the workbook file to C:\\temp\\MyOtherWorkbook.xlsx
const saveAsPath = await workbook.saveAs('C:\\\\temp\\\\MyOtherWorkbook.xlsx');

// Close the workbook
await workbook.close();

📘

Note

Attempting to call any function on the workbook object after it has been closed (via the API or the Excel desktop application) results in an AdapterError being thrown.
However, if the same workbook is then reopened, the existing object can be used again as normal.

Working with worksheets

Using a workbook object, you can discover the existing worksheets in the workbook and create new ones:

// Output the names of all worksheets in the workbook to the console
const worksheets = await workbook.getWorksheets();
worksheets.forEach(async (worksheet) => {
  const worksheetName = await worksheet.getName();
  console.log(worksheetName);
});
  
// Get the worksheet named "Sheet 1"
const sheet1 = await workbook.getWorksheetByName('Sheet 1');

// Get the current active worksheet
const activeWorksheet = await workbook.getActiveWorksheet();
  
// Create a new worksheet
const worksheet = await workbook.addWorksheet();

Once you have a worksheet object:

  • You can control the worksheet by giving it focus, triggering calculations and marking the worksheet as protected:
// Get the name of the worksheet
const worksheetName = await worksheet.getName();

// Activate the worksheet and bring it in to focus (equivalent to clicking the sheet's tab in Excel)
await worksheet.activate();

// Trigger calculation for the worksheet (only necessary if getCalculationMode does not return "Automatic")
await worksheet.calculate();

// Protect the worksheet so that it cannot be modified
await worksheet.protect();
  • You can clear all values and formatting across the entire worksheet:
// Clear the values and formatting for all cells in the worksheet
await worksheet.clearAllCellFormatting();
await worksheet.clearAllCellValues();

// Calling the above two statements can be simplified by calling clearAllCells
await worksheet.clearAllCells();
  • You can get a reference to a range of cells in the worksheet, consisting of one or more cells:
// Get cells by range
const cellRange1 = await worksheet.getCellRange('A1');
const cellRange2 = await worksheet.getCells('A2:E6');

// Get cells by name
const cellRange3 = await worksheet.getCellRange('DAILY_TRADES');

// Get multiple groups using comma-separated names and ranges
const cellRange4 = await worksheet.getCells('B10,G5:H6,ACCOUNT_MARGIN');

Working with cell ranges

Once you have a reference to a cell range:

  • You can set the values of the cells in the range:
// Calculate Pythagorean triples and output computed values to console
const pythagoreanTableCellRange = await worksheet.getCellRange('A1:C4');
await pythagoreanTableCellRange.setValues([
  ['A', 'B', 'C'],
  [3, 4, '=SQRT(A2^2+B2^2)'],
  [5, 12, '=SQRT(A3^2+B3^2)'],
  [8, 15, '=SQRT(A4^2+B4^2)'],
]);
const computedCellRange = await worksheet.getCellRange('C2:C4');
const computedCells = await computedCellRange.getCells();
computedCells.forEach((cell) => console.log(cell.value));
  • You can set the formatting of the cells in the range:
// This example requires an additional import
import { ExcelCellHorizontalAlignment } from '@openfin/excel';

// Create and format a table
const tableCellRange = await worksheet.getCellRange('A1:C4');
await tableCellRange.setValues([
  ['Number 1', 'Number 2', 'Sum Total'],
  [1000, 2000, '=SUM(A2:B2)'],
  [3000, 4000, '=SUM(A3:B3)'],
  [5000, 6000, '=SUM(A4:B4)'],
]);
await (await worksheet.getCellRange('A2:C4')).setFormatting({ numberFormat: '#,##0' }); // number formats
await (await worksheet.getCellRange('A1:C1')).setFormatting({
  alignment: { horizontal: ExcelCellHorizontalAlignment.Center },
  background: { color: '180,198,231' },
  font: { bold: true },
}); // header
await (await worksheet.getCellRange('C2:C4')).setFormatting({ background: { color: '210,210,210' } }); // totals
await (await worksheet.getCellRange('A1:C4')).setFormatting({ border: { all: { color: '100,100,100' } } }); // border

The following table lists the available cell formatting options:

NameDescriptionExample
alignmentSets horizontal and vertical text alignment{ alignment: { horizontal: ExcelCellHorizontalAlignment.Center, vertical: ExcelCellVerticalAlignment.Justify } }
backgroundSets background color and pattern{ background: { color: '100,100,100', pattern: ExcelCellPattern.Grid } }
borderSets border color and styles{ border: { all: { color: '255,50,0', lineStyle: ExcelCellBorderLineStyle.Dash } }
fontSets font styling.{ font: { bold: true, color: '0,180,80', italic: false, name: 'Consolas', size: 9 } }
lockedLocks or unlocks the cell range for when the worksheet is protected{ locked: true }
mergeCellsMerges the cells in the range keeping the content of the leftmost or topmost cell{ mergeCells: true }
numberFormatFormats the appearance of numbers for things like currency, percentages, decimals, dates, phone numbers, etc. See here for more info on custom number formats{ numberFormat: '#,##0' }
shrinkToFitReduces the font size of the cell text so that the text fits in the current size of the cell without wrapping{ shrinkToFit: true }
  • You can clear values and formatting for ranges of cells or the entire worksheet:
// Clear the values and formatting for cells A1:C4
await (await worksheet.getCellRange('A1:C4')).clearValues();
await (await worksheet.getCellRange('A1:C4')).clearFormatting();

// The above two statements can be simplified by just calling clear
await (await worksheet.getCellRange('A1:C4')).clear();

// Clear the values and formatting for the entire worksheet
await worksheet.clearAllCellValues();
await worksheet.clearAllCellFormatting();

// The above two statements can be simplified by just calling clearAllCells
await worksheet.clearAllCells();
  • You can define names for cell ranges for future reference:
// Define a name for cells A1:D4 and use it to get the cell range
await (await worksheet.getCellRange('A1:D4')).setName('ACCOUNTS_TABLE');
const accountsTableCellRange = await worksheet.getCellRange('ACCOUNTS_TABLE');
  • You can create filters in tabular data to only show rows that meet certain criteria:
// This example requires an additional import
import { ExcelFilterOperator } from '@openfin/excel';

// Calculate Pythagorean triples but only show rows where "C" is greater than 10
await (await worksheet.getCellRange('A1:C4')).setValues([
  ['A', 'B', 'C'],
  [3, 4, '=SQRT(A2^2+B2^2)'],
  [5, 12, '=SQRT(A3^2+B3^2)'],
  [8, 15, '=SQRT(A4^2+B4^2)'],
]);
(await worksheet.getCellRange('A1:C1')).setFilter(3, ExcelFilterOperator.Or, '>10');

Subscribing to events

The client-side API exposes a number of events at the workbook, worksheet and cell range level.

Use the addEventHandler function to register a function to be called when a given event occurs:

// This example requires additional imports
import { Cell, CellRangeChangedEventListener, WorkbookActivatedEventListener, WorksheetActivatedEventListener } from '@openfin/excel';

// Log a message to console when the workbook is given focus
const workbookActivatedListener: WorkbookActivatedEventListener = async () => {
  const workbookName = await workbook.getName();
  console.log(`Workbook ${workbookName} activated!`);
};
await workbook.addEventListener('activate', workbookActivatedListener);

// Log a message to console when the worksheet is given focus
const worksheetActivatedListener: WorksheetActivatedEventListener = async () => {
  const worksheetName = await worksheet.getName();
  console.log(`Worksheet ${worksheetName} activated!`);
};
await worksheet.addEventListener('activate', worksheetActivatedListener);

// Log a message to console when changes are made to cell A1
const cellRangeChangedListener: CellRangeChangedEventListener = async (changedCells: Cell[]) => {
  const changedCell = changedCells[0];
  console.log(`Cell ${changedCell.address} value changed to ${changedCell.value}`);
};
await (await worksheet.getCellRange('A1')).addEventListener('change', cellRangeChangedListener);

Always clean up registered handlers to avoid memory leaks by calling removeEventListener:

await workbook.removeEventListener(workbookActivatedListener);
await worksheet.removeEventListener(worksheetActivatedListener);
await (await worksheet.getCellRange('A1')).removeEventListener(cellRangeChangedListener);

Working with data streams

Data streams enable the streaming of real time data into an Excel worksheet.

Data streams are enabled purely by JavaScript and do not require any other server component to be installed, unlike Excel’s RTD (Real Time Data) functionality.

The createDataStream function takes a target cell range, a function, and an optional update interval (defaults to one second). This function is called after each interval and returns a value that is used to update the target cell range.

// This example requires an additional import
import { CellValue } from '@openfin/excel';

// Create a data stream to update cell A1 every half second with data from a REST API endpoint
const interval = 0.5e3; // 0.5 seconds
const apiDataEndpointUrl =
  'https://www.random.org/integers/?num=1&min=1&max=100&col=1&base=10&format=plain&rnd=new';
const restApiDataStreamTargetCellRange = await worksheet.getCellRange('A1');
const restApiDataStream = restApiDataStreamTargetCellRange.createDataStream(async (): Promise<CellValue> => {
  // Retrieve data from API endpoint and update the target cell range with the response data
  const response = await fetch(apiDataEndpointUrl);
  if (!response.ok) {
    throw new Error('Request failed');
  }
  return response.text();
}, interval);

// Start streaming
restApiDataStream.start();

When making numerous remote requests it’s far more efficient to use the Websockets API rather than HTTP, where possible. Also, the Websocket API supports two-way communication which is preferable to long polling.

// Initialize the websocket connection and listen for messages
let updateValue: number;
const webSocketServerUrl = 'wss://mywebsocketserver.com';
const socket = new WebSocket(webSocketServerUrl);
socket.addEventListener('message', (event) => {
  updateValue = event.data.value;
});

// Create the data stream and start streaming to output to a named cell range at the default interval of 1 second
const wsDataStreamTargetCellRange = await worksheet.getCellRange('WS_OUTPUT');
const wsDataStream = wsDataStreamTargetCellRange.createDataStream(async (): Promise<CellValue> => updateValue);
wsDataStream.start();

📘

Note

Use named cell ranges where possible.

Using a named cell range with a data stream enables workbook authors to control which specific cells will be updated with the data stream output, without requiring further code changes.

Clean up data streams by calling close.

// Stop and clean up the data streams
restApiDataStream.stop();
restApiDataStream.close();
wsDataStream.stop();
wsDataStream.close();

📘

Note

Calling start or stop on a closed data stream results in an error being thrown.

Enabling API logging

If you encounter errors during the course of using the Excel Integration, it can be helpful to enable API logging, which outputs more information to the application’s dev tools console.

API logging is turned off by default.

To enable API logging:

  • Import enableLogging and call it from your application:
import { enableLogging } from '@openfin/excel';

enableLogging();
  • Alternatively, the enableLogging function is also registered globally:
window.fin.Integrations.Excel.enableLogging();

To disable API logging:

  • Import disableLogging and call it from your application:
import { disableLogging } from '@openfin/excel';

disableLogging();
  • Alternatively, the disableLogging function is also registered globally:
window.fin.Integrations.Excel.disableLogging();

📘

Note

The Excel Integration API logging is a separate feature from the OpenFin Application logs logging tool.

Updated 12 months ago

Related topics