@borgar/fx

Utilities for working with Excel formulas

Usage no npm install needed!

<script type="module">
  import borgarFx from 'https://cdn.skypack.dev/@borgar/fx';
</script>

README

fx

This is a collection utilities to work with Excel formula code, specifically syntax highlighting.

This utility is developed as tooling for GRID – The new face of spreadsheets, to which it owes a debt of gratitude.

Installing

The library is also provided as an ES6 module in an NPM package:

$ npm install @borgar/fx

API

# tokenize( formula [, options] )

  • formula should be a string (an Excel formula).

  • options are set as an object of keys: convert(filename, { option: true }). Supported options are:

    | name | default | effect | |- | - | - | emitRanges | false | Adds offset ranges on the tokens: { range: [ start, end ] } | mergeRanges | true | Should ranges be returned as whole references (Sheet1!A1:B2) or as separate tokens for each part: (Sheet1,!,A1,:,B2). | r1c1 | false | Ranges are expected to be in the R1C1 style format rather than the more popular A1 style.

The returned output will be an array of objects representing the tokens:

[
  { type: FX_PREFIX, value: '=' },
  { type: FUNCTION, value: 'SUM' },
  { type: OPERATOR, value: '(' },
  { type: RANGE, value: 'A1:B2' },
  { type: OPERATOR, value: ')' }
]

Token types may be found as an Object as the tokenTypes export on the package (import {tokenTypes} from '@borgar/fx';):

tokenTypes = {
  OPERATOR: "operator",
  BOOLEAN: "bool",
  ERROR: "error",
  NUMBER: "number",
  FUNCTION: "function",
  NEWLINE: "newline",
  WHITESPACE: "whitespace",
  STRING: "string",
  PATH_QUOTE: "path-quote",
  PATH_BRACE: "path-brace",
  PATH_PREFIX: "path-prefix",
  RANGE: "range",
  RANGE_BEAM: "range-beam",
  RANGE_NAMED: "range-named",
  FX_PREFIX: "fx-prefix",
  UNKNOWN: "unknown"
}

# translateToA1( formula, anchorCell )

Translates ranges in a formula from relative R1C1 syntax to absolute A1 syntax.

  • formula should be a string (an Excel formula) or a token list.

  • anchorCell should be a simple string reference to an A1 cell (AF123 or $C$5).

Returns the same formula with the ranges translated. If an array of tokens was supplied, then the same array is returned (be careful that mergeRanges must be false).

translateToA1("=SUM(RC[1],R2C5,Sheet!R3C5)", "D10");
// => "=SUM(E10,$E$2,Sheet!$E$3)");

# translateToRC( formula, anchorCell )

Translates ranges in a formula from absolute A1 syntax to relative R1C1 syntax.

  • formula should be a string (an Excel formula) or a token list.

  • anchorCell should be a simple string reference to an A1 cell (AF123 or $C$5).

Returns the same formula with the ranges translated. If an array of tokens was supplied, then the same array is returned (be careful that mergeRanges must be false).

translateToRC("=SUM(E10,$E$2,Sheet!$E$3)", "D10");
// => "=SUM(RC[1],R2C5,Sheet!R3C5)");

# addMeta( tokenlist [, context] )

Runs through a list of tokens and adds extra attributes such as matching parens and ranges.

  • tokenlist should be a token list (from tokenize()).

  • context should be an object containing default reference attributes: { workbookName: 'report.xlsx', sheetName: 'Sheet1' }). If supplied, these are used to match A1 to "Sheet1A1)

The returned output will be the same array of tokens but the following properties will added to tokens (as applicable):

Parentheses ( )

Matching parens will be tagged with .groupId string identifier as well as a .depth number value (indicating the level of nesting).

Parens without a counterpart will be tagged with .error (boolean true).

Curly brackets { }

Matching curly brackets will be tagged with .groupId string identifier. These may not be nested in Excel.

Curly brackets without a counterpart will be tagged with .error (boolean true).

Ranges (RANGE or RANGE_BEAM type tokens)

All ranges will be tagged with .groupId string identifier regardless of the number of times they occur.

Tokens of type UNKNOWN

All will be tagged with .error (boolean true).

.a1:

An object of methods to interpret and manipulate A1 style references.

# .parse( refString[, allow_named = true ] )

Parse a string reference into an object representing it.

import { a1 } from '@borgar/fx';
a1.parse('Sheet1!A$1:$B2');
// => {
//   workbookName: '',
//   sheetName: 'Sheet1',
//   range: {
//     top: 0,
//     left: 0,
//     bottom: 1,
//     right: 1
//     $top: true,
//     $left: false,
//     $bottom: false,
//     $right: true
//   }
// }

# .to( columnString )

Parse a simple string reference to an A1 range into a range object (see above). Will accept A1, A2, A:A, or 1:1.

# .from( rangeObject )

Stringify a range object (see above) into A1 syntax.

# .fromCol( columnString )

Convert a column string representation to a 0 based offset number ("C" = 2).

# .toCol( columnNumber )

Convert a 0 based offset number to a column string representation (2 = "C").

.rc:

An object of methods to interpret and manipulate R1C1 style references.

# .parse( refString[, allow_named = true ] )

Parse a string reference into an object representing it.

import { rc } from '@borgar/fx';
rc.parse('Sheet1!R[9]C9:R[9]C9');
// => {
//   workbookName: '',
//   sheetName: 'Sheet1',
//   range: {
//     r0: 9,
//     c0: 8,
//     r1: 9,
//     c1: 8,
//     $c0: true,
//     $c1: true
//     $r0: false,
//     $r1: false
//   }
// }

# .from( rangeObject )

Stringify a range object (see above) into R1C1 syntax.

# .to( columnString )

Parse a simple string reference to an R1C1 range into a range object (see above).