Microsoft Excel integration

Overview

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.

With Excel Integration, you can:

  • Create, open and save workbooks and worksheets from your OpenFin app.
  • Push and receive data between your OpenFin app and a worksheet.
  • Select items in either your OpenFin app or a worksheet, based on events in the other.
  • Programmatically format cells in worksheets.

Prerequisites

There are three prerequisites for the Excel integration:

  • OpenFin
    • The Excel integration can be used with version 9.61.35.22 or later of the OpenFin runtime.
  • Microsoft Excel 2013 or later
    • Any machine where the integration will be used must have Microsoft Excel 2013 or later installed.
  • .NET Framework 4.7.2
    • Any machine where the integration will be used must have the .NET Framework 4.7.2 runtime installed.

How Microsoft Excel integration works

Microsoft allows programmatic control over Excel by using the Microsoft.Office.Interop.Excel namespace. This namespace enables a .NET program to control the Excel program itself, and access workbooks, worksheets, and cells in Excel. OpenFin created a client-side API and a .NET adapter which work together to allow OpenFin apps to control Microsoft Excel.

Specifically:

  • An NPM package, @openfin/excel, contains the client-side API.

  • A .NET adapter works as an intermediary between the client-side API and the Microsoft Excel Interop API.

On initialization, the client-side API downloads from OpenFin’s CDN and launches the .NET adapter in the background. This is the default behavior, and is recommended as it downloads the appropriate version of the .NET adapter. You can self-host the adapter, but you should have a process to make sure you have a correct version of the adapter and update it when necessary.

How to install and configure Microsoft Excel integration

Installation

Add the @openfin/excel NPM package as a dependency for your app using the relevant package manager, e.g.:

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.

Working with Microsoft Excel Integration

Self-hosting the adapter

If you do not want the client-side API to automatically download the adapter from OpenFin’s CDN, you can override the default behavior by declaring the adapter as an appAsset in your app’s manifest:

"appAssets": [{
  "alias": "excel-adapter",
  "src": "https://yourdomain.com/OpenFin.Excel.zip",
  "target": "OpenFin.Excel.exe",
  "version": "1.0.3"
}]

The alias and target values must match those in the example above. The version value should correspond to the NPM package version being used by your app. The src value should be the URL where your app can download the adapter from.

The adapter package for any given version can be downloaded from OpenFin’s CDN at the following location:

https://cdn.openfin.co/release/integrations/excel/{API_VERSION}/OpenFin.Excel.zip

where {API_VERSION} is the version to download. For example, to download version 1.0.3:

https://cdn.openfin.co/release/integrations/excel/1.0.3/OpenFin.Excel.zip

Controlling the Excel application

The @openfin/excel NPM package contains an ES module that exports the getExcelApplication function. This is the client-side API. Import 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 containing a number of functions that control Excel at the application-level:

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

// Trigger calculation for all open workbooks (only necessary if getCalculationMode does not return "Automatic")
await excel.calculateFull();

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

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

// Save the workbook file to C:\temp\MyOtherWorkbook.xlsx
await workbook.saveAs('C:\\temp\\MyOtherWorkbook.xlsx');
    
// Close the workbook
await workbook.close();

You can also get the value of a property from the Excel primary interop assemblies (PIA) Workbook interface.

Refer to the Workbook interface properties documentation for a list of valid property names.

Only properties that are a value type or a string are supported and can be retrieved using this function. If you attempt to retrieve the value of a property that is not a value type or a string, an error is thrown:

// Get the name of the workbook author
const workbookAuthor = await workbook.getProperty<string>('Author');

// Check whether the workbook has a protection password set
const isPasswordProtected = await workbook.getProperty<boolean>('HasPassword');

// Attempting to retrieve the value of a non-existent property returns "null"
const nonExistentPropValue = await workbook.getProperty('nonExistentProp');
console.log(nonExistentPropValue); // Outputs "null" to console

// Attempting to retrieve the value of a property that is not a value type or string throws an error
const unsupportedPropValue = await workbook.getProperty('ActiveSheet'); // throws error

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();
  • Similar to workbooks, you can get the value of a value type or string property from the Excel PIA Worksheet interface (refer to the Worksheet interface properties documentation for a list of valid property names):
// Check if user-interface-only protection is turned on for the worksheet
const isProtected = await worksheet.getProperty<boolean>('ProtectionMode');
  • 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, formulas and formatting 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));

// Format the table
worksheet.setCellFormatting('A1:C1', { alignment: { horizontal: '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

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

// Calling the above two statements can be simplified by calling clearCells
await worksheet.clearCells('A1:C4');
  • 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 application, 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 a new workbook is created
const workbookCreatedListener = async (createdWorkbook) => {
  const workbookName = await createdWorkbook.getName();
  console.log(`Workbook ${workbookName} created!`);
};
await excel.addEventListener('createWorkbook', workbookCreatedListener);

// 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 excel.removeEventListener(workbookCreatedListener);
await workbook.removeEventListener(workbookActivatedListener);
await worksheet.removeEventListener(worksheetChangedListener);

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 Excel service

Overview

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:

AdvantageDescription
Lighter footprintThere is no "Add-in" component that requires installation in Excel.
Runtime version consistencyThe adapter process uses the same runtime version that the parent app uses.
Improved isolationMultiple apps that use different versions of the Excel Integration can be run at once.
Simple configurationBy default no additional configuration is required unless you choose to self-host the adapter package.
Standard upgrade pathUpgrading to newer versions is the same process as per any NPM dependency.
Typescript supportThe new client-side API is provided as an ES module including type definitions.

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.

    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:

    {...
      "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\ .

Related topics

Note

  • The adapter process is for the sole use of the parent app that launched it. If multiple apps are running that use the Excel Integration, they each run their own separate adapter process. This isolation allows different versions of the Excel Integration to be used by multiple apps at the same time

Did this page help you?