Angular Spreadsheet Action Bar

The Spreadsheet action bar consist of tools that allow you to control the cell selection and calculate cell values by entering formulas into the dedicated formula input. By using the action bar tools, you can save time and improve your productivity when working with the Spreadsheet data.

The action bar contains two main tools:

• Cell Selector
• Formula Input

Cell Selector

The cell selector ComboBox tool displays the range selection in the same format used by MS Excel—for example, A1:B5. To highlight a specific range you can:

• Drag and select the desired cells.
• Manually type the necessary range selection in the component.

You can also save the current range selection and apply it later when needed. To save a specific range of cells:

1. Select the desired cells.
2. Change the default range selection to any custom name.
3. Click Enter to save the custom selection.

Formula Input

The formula input of the Spreadsheet is a powerful tool that allows you to calculate cell data in sheets, just like in MS Excel. Use the formula input to enter any mathematical formulas (for example, =SUM(A1:A3)) or logical formulas (for example, =AND(A2>=2, A3<5)).

To calculate a value in the Spreadsheet:

1. Click on the cell where you want to display the calculated value.
2. In the formula input field, insert the = sign. The Spreadsheet suggests relevant formulas depending on the typed letter.
3. Define the cell range whose values must be calculated either manually or by dragging through the cells.

The following example demonstrates the SUM formula in action.

Custom Formulas

The Spreadsheet component allows you to create your own custom JavaScript formulas (primitives) by calling the defineFunction(name, func) method. The first argument of the method (name) is the name for the custom formula, while the second one (func) is the implementation of the formula in JavaScript code.

Copy CodedefineFunction(
  'customFunction',
  function (x1: number, y1: number) {
    //Implementation of the function.
  }
).args([
    ['x1', 'number'],
    ['y1', 'number'],
  ]
  , null
);

The args method lets you specify the expected types of the arguments. When the custom formula is called with mismatching argument types, the args method lets you throw errors automatically. This way, you don't need to create custom code to verify the argument types at runtime.

Synchronous Primitives

To implement a custom function that calculates the distance between two points, use the built-in defineFunction and args methods.

Copy CodedefineFunction(
  'distance',
  function (x1: number, y1: number, x2: number, y2: number) {
    const dx = Math.abs(x1 - x2);
    const dy = Math.abs(y1 - y2);
    const distance = Math.sqrt(dx * dx + dy * dy);
    return distance;
  }
).args([
    ['x1', 'number'],
    ['y1', 'number'],
    ['x2', 'number'],
    ['y2', 'number'],
  ]
  , null
);

The following example demonstrates how to calculate the distance between two points with coordinates (2, 2) and (5, 6).

Asynchronous Primitives

If a custom formula needs to retrieve information from a remote server, use the built-in defineFunction() and argsAsync() methods. You can pass a callback that returns the remote data as the first argument to the implementation function. As a result, the callback remains invisible in the custom formula itself.

Copy CodedefineFunction('currency', function (callback, eur, usd) {
  // Custom function that retrieves currency exchange rates from a remote server:
  fetchCurrency(eur, usd, function (value) {
    callback(value);
  });
  return 5;
}).argsAsync(
  [
    ['base', 'string'],
    ['curr', 'string'],
  ],
  null
);

Suggested Links

• API Reference of the Spreadsheet
• API Reference of the SpreadsheetComponent
• Sheets Bar