Microsoft Excel integration

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 recommended runtime version 9.61.35.22
• 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

When running on v12 or later of the OpenFin runtime, your app will require permission to use the System.downloadAsset and System.launchExternalProcess secured APIs. 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 will depend on whether or not 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

In some cases, corporate firewalls may not allow access to OpenFin’s CDN which will prevent the Excel integration from downloading 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 different adapter package location by declaring it 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.

🚧

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, or an error will be 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

📘

Starter Project

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

// Create a new empty workbook
const newWorkbook = 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.calculateFull();

// 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) will result 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');

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

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

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

// Protects 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 individual cells or ranges of cells in the worksheet and their properties:

// Get the name and formula for cell A1
const cellA1 = (await worksheet.getCells('A1'))[0];
const cellName = cellA1.name;
const cellFormula = cellA1.formula;

// Output the values of all cells in the range A2:E6 to the console
const cellRange = await worksheet.getCells('A2:E6');
cellRange.forEach((cell) => {
  console.log(`${cell.address}: ${cell.value}`);
});

// Get individual cells or ranges by name
const dailyTradesTableCells = await worksheet.getCells('DAILY_TRADES');
const dailyTradesTableTotal = (await worksheet.getCells('DAILY_TRADES_TOTAL'))[0];

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

• You can set values for individual cells or ranges of cells across the worksheet:

// Calculate Pythagorean triples and output computed values to console
await worksheet.setCellValues('A1:C4', [
  ['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 computedCells = await worksheet.getCells('C2:C4');
computedCells.forEach((cell) => console.log(cell.value));

• You can set formatting for individual or ranges of cells across the worksheet:

// This example requires an extra import
import { ExcelCellHorizontalAlignment } from '@openfin/excel';

// Format the table
worksheet.setCellFormatting('A2:C4', { numberFormat: '#,##0' }); // number formats
worksheet.setCellFormatting('A1:C1', { alignment: { horizontal: ExcelCellHorizontalAlignment.Center }, background: { color: '180,198,231' }, font: { bold: true } }); // header
worksheet.setCellFormatting('C2:C4', { background: { color: '210,210,210' } }); // totals
worksheet.setCellFormatting('A1:C4', { border: { all: { color: '100,100,100' } } }); // border

The following table lists the available cell formatting options:

• You can clear values and formatting for ranges of cells or the entire worksheet:

// Clear the values and formatting for cells A1:C4
await worksheet.clearCellValues('A1:C4');
await worksheet.clearCellFormatting('A1:C4');

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

// 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 individual cells or ranges of cells across the worksheet:

// Define a name for cells A1:D4 and use it to get the cells
await worksheet.setCellName('A1:D4', 'ACCOUNTS_TABLE');
const accountsTableCells = await worksheet.getCells('ACCOUNTS_TABLE');

• You can create filters in tabular data to only show rows that meet certain criteria:

// This example requires an extra import
import { ExcelFilterOperator } from '@openfin/excel';

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

Subscribing to events

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

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

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

// Log a message to console when changes are made to the worksheet
const worksheetChangedListener = async (changedCells: Cell[]) => {
  const changedCellsAddresses = changedCells.map((cell) => cell.address).join(', ');
  const worksheetName = await worksheet.getName();
  console.log(`Cell(s) ${changedCellsAddresses} in worksheet ${worksheetName} changed`);
};
await worksheet.addEventListener('change', worksheetChangedListener);

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

await workbook.removeEventListener(workbookActivatedListener);
await worksheet.removeEventListener(worksheetChangedListener);

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.

// Create a data stream to update the “API_OUTPUT” named cell range every half second with data from a REST API endpoint
const dataStream = worksheet.createDataStream('API_OUTPUT', async (): Promise<CellValue> => {
  // Retrieve data from API endpoint
  const response = await fetch(apiDataEndpointUrl, {
    headers: { Accept: 'application/json' },
  });
  if (!response.ok) {
    throw new Error('Request failed');
  }

  // Update the target cell range with the “value” property from the response data
  const data = await response.json();
  return data.value;
}, 500);

// Start streaming
dataStream.start();

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

// Create a data stream to update the “WS_OUTPUT” named cell range every second with incoming data from a websocket

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

// Create the data stream and start streaming
const dataStream = worksheet.createDataStream('WS_OUTPUT', async (): Promise<CellValue> => updateValue);
dataStream.start();

📘

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.

Modify the target cell range or update interval of an existing data stream, then call start for the changes to take effect.

// Update the data stream target cell range and decrease the update interval
dataStream.cellRange = 'NEW_NAMED_RANGE';
dataStream.updateInterval = 2000;
dataStream.start();

Data streams should be cleaned up by calling close.

Note: Calling start or stop on a closed data stream will result in an error being thrown.

// Clean up the data stream
dataStream.close();

Enabling API logging

If you encounter errors during the course of using the Excel Integration, it can be helpful to enable API logging, which will output 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.

Migrating from the legacy Excel service

The @openfin/excel NPM package supersedes the previous Excel service which is now deprecated.

The NPM package has a number of advantages over the service:

The new client-side API is not a one-to-one match with the API provided by the service. There are implementation as well as functional differences, some of which will be addressed in future releases.

Migration steps

If you are using the existing Excel service and wish to migrate your app to use the new NPM package, follow these steps:

1. Review the API documentation and ensure that it meets the functional requirements of your app.

2. Make the required changes to your app:

   1. Add the @openfin/excel NPM package as a dependency.

   2. Remove any script tags referencing the Excel service API JavaScript file, fin.desktop.Excel.js or ExcelAPI.js.

   3. Update your app's source code where it calls deprecated API statements and replace them as per the API documentation.

      Note: The Excel RTD functionality has been replaced by data streams in the new Excel integration. Remember to factor in removing references to the OpenFinRTD function in all affected Excel workbooks, and replacing with the named cell ranges used by the data streams implemented in your app.

   4. If using a bundler (such as webpack), update your app’s bundling process to remove the Excel service API JavaScript file (if being included in the bundle) and ensure required files from the NPM package are included.

   5. Update your app’s manifest to remove references to the "excel" service:

   JSON

   {...
     "services": 
     [
       {"name": "excel"}
     ]
   }
   

   1. If required, update your app’s manifest to include the necessary API permissions (see Configure API security above).

   2. If planning on self-hosting the adapter, update your app’s manifest to include the required appAssets entry (see Self-hosting the adapter above).

3. Optional: Remove the deprecated Excel service Add-In from any desktop that ran the Excel service.

   We recommend this step only if you know that no applications are targeting version 14.78.48.16 of the OpenFinRuntime. If you’re confident that is the case, please proceed with the following optional steps:

   1. Locate where the Add-in file resides and delete it. If you do not know its location, in Excel, click File > Options > Add-ins and find "OpenFin Excel API Add-In". Typically the location will be %LocalAppData%\OpenFin\shared\assets\excel-api-addin.
   2. Close down Excel and then delete the entire "excel-api-addin" folder.
   3. Start Excel and you should see an alert stating that the OpenFin.ExcelApi-AddIn.xll Add-in could not be found, click on OK to continue.
   4. In Excel, click File > Options > Add-ins, then select "Excel Add-ins" next to "Manage" and click on Go. Uncheck the "Openfin.Excelapi-Addin" option then when prompted to delete it from the list click Yes.
   5. Click on OK to close the Add-ins window
   6. Delete this folder %LocalAppData%\OpenFin\cache\14.78.48.16 (substitute your OpenFin installation folder path if different than this one).
   7. Delete all folders that start with Excel-Service-Manager in the following folder %LocalAppData%\OpenFin\apps\ .