format-formula

This project is a partial port of the excel-formula library to ES6.
It contains a set of functions that can be used to pretty print Excel formulas.

Key Differences from excel-formula:

Removed external dependencies (Bootstrap, jQuery)
Removed methods not related to formatting
Removed jQuery methods and replaced them with ES6 equivalents
Modularized the library with support for tree-shaking
Does not expose a global (window) variable
Added isEu as an option to the getTokens, formatFormula and formatFormulaHTML methods
Provides ES, CJS, and UMD module formats

Install

npm install @pequity/format-formula

Usage

Module bundler

import { formatFormula } from '@pequity/format-formula';

const formattedFormula = formatFormula('SUM(A1:A2)');

Browser

<script src="https://unpkg.com/@pequity/format-formula"></script>
<script>
  const formattedFormula = FormatFormula.formatFormula('SUM(A1:A2)');
</script>

Available methods

formatFormula

Formats an excel formula.

Signature:
formatFormula(formula: string, options): string

formula - The excel formula to format
options - An optional object with the following properties:

Name	Description	Default
tmplFunctionStart	Template for the start of a function, the `{{token}}` will contain the name of the function.	`'{{autoindent}}{{token}}(\n'`
tmplFunctionStop	Template for when the end of a function has been reached.	`'\n{{autoindent}}{{token}})'`
tmplOperandError	Template for errors.	`' {{token}}'`
tmplOperandRange	Template for ranges and variable names.	`'{{autoindent}}{{token}}'`
tmplLogical	Template for logical operators	`'{{token}}{{autolinebreak}}'`
tmplOperandLogical	Template for logical operators such as `+ - = ...`	`'{{autoindent}}{{token}}'`
tmplOperandNumber	Template for numbers.	`'{{autoindent}}{{token}}'`
tmplOperandText	Template for text/strings.	`'{{autoindent}}"{{token}}"'`
tmplArgument	Template for argument separators such as `,.`	`'{{token}}\n'`
tmplOperandOperatorInfix	-	`' {{token}}{{autolinebreak}}'`
tmplFunctionStartArray	Template for the start of an array.	`''`
tmplFunctionStartArrayRow	Template for the start of an array row.	`'{'`
tmplFunctionStopArrayRow	Template for the end of an array row.	`'}'`
tmplFunctionStopArray	Template for the end of an array.	`''`
tmplSubexpressionStart	Template for the sub expression start.	`'{{autoindent}}(\n'`
tmplSubexpressionStop	Template for the sub expression stop.	`'\n)'`
tmplIndentTab	Template for the tab char.	`'\t'`
tmplIndentSpace	Template for space char.	`' '`
autoLineBreak	When rendering line breaks automatically which types should it break on.	`'TOK_TYPE_FUNCTION \| TOK_TYPE_ARGUMENT \| TOK_SUBTYPE_LOGICAL \| TOK_TYPE_OP_IN'`
newLine	Used for the `{{autolinebreak}}` replacement as well as some string parsing.	`'\n'`
trim	Trim the output.	`true`
customTokenRender	This is a call back to a custom token function.	`null`
prefix	Add a prefix to the formula.	`''`
postfix	Add a suffix to the formula.	`''`
isEu	If `true`then `;` is treated as list separator, if `false` then `;` is treated as array row separator	`false`

Template Values

{{autoindent}} - apply auto indent based on current tree level
{{token}} - the named token such as FUNCTION_NAME or "string"
{{autolinebreak}} - apply line break automatically. tests for next element only at this point

customTokenRender Example

function (tokenString, token, indent, lineBreak) {
  const outStr = token
  const useTemplate = true

  // In the return object "useTemplate" tells formatFormula()
  // weather or not to apply the template to what your return from the "tokenString".
  return { tokenString: outStr, useTemplate }
}

formatFormulaHTML

Formats an excel formula into HTML.

Signature:
formatFormulaHTML(formula: string, options): string

formula - The excel formula to format
options - An optional object with the following properties (inherits defaults from formatFormula):

Name	Description	Default
tmplFunctionStart	Template for the start of a function, the `{{token}}` will contain the name of the function.	`'{{autoindent}}<span class="function">{{token}}</span><span class="function_start">(</span><br />'`
tmplFunctionStop	Template for when the end of a function has been reached.	`'<br />{{autoindent}}{{token}}<span class="function_stop">)</span>'`
tmplOperandError	Template for errors.	`' {{token}}'`
tmplOperandRange	Template for ranges and variable names.	`'{{autoindent}}{{token}}'`
tmplLogical	Template for logical operators	`'{{token}}{{autolinebreak}}'`
tmplOperandLogical	Template for logical operators such as `+ - = ...`	`'{{autoindent}}{{token}}'`
tmplOperandNumber	Template for numbers.	`'{{autoindent}}{{token}}'`
tmplOperandText	Template for text/strings.	`'{{autoindent}}<span class="quote_mark">"</span><span class="text">{{token}}</span><span class="quote_mark">"</span>'`
tmplArgument	Template for argument separators such as `,.`	`'{{token}}<br />'`
tmplOperandOperatorInfix	-	`' {{token}}{{autolinebreak}}'`
tmplFunctionStartArray	Template for the start of an array.	`''`
tmplFunctionStartArrayRow	Template for the start of an array row.	`'{'`
tmplFunctionStopArrayRow	Template for the end of an array row.	`'}'`
tmplFunctionStopArray	Template for the end of an array.	`''`
tmplSubexpressionStart	Template for the sub expression start.	`'{{autoindent}}('`
tmplSubexpressionStop	Template for the sub expression stop.	`' )'`
tmplIndentTab	Template for the tab char.	`'<span class="tabbed">    </span>'`
tmplIndentSpace	Template for space char.	`' '`
autoLineBreak	When rendering line breaks automatically which types should it break on.	`'TOK_TYPE_FUNCTION \| TOK_TYPE_ARGUMENT \| TOK_SUBTYPE_LOGICAL \| TOK_TYPE_OP_IN '`
newLine	Used for the `{{autolinebreak}}` replacement as well as some string parsing.	`'<br />'`
trim	Trim the output.	`true`
customTokenRender	This is a call back to a custom token function.	Custom function for formatFormulaHTML
prefix	Add a prefix to the formula.	`'='`
postfix	Add a suffix to the formula.	`''`

getTokens

Tokenizes an excel formula.

Signature: getTokens(formula: string isEu: boolean): F_token[]

formula - The excel formula to format
isEu - If truethen ; is treated as list separator, if false then ; is treated as array row separator

Returns an array of tokens, e.g. given the formula A1+1000 the output would be:

[
  {
    "subtype": "range",
    "type": "operand",
    "value": "A1"
  },
  {
    "subtype": "math",
    "type": "operator-infix",
    "value": "+"
  },
  {
    "subtype": "number",
    "type": "operand",
    "value": "1000"
  }
]