grammar/hooks.js

  1. const TextFunctions = require('../formulas/functions/text');
  2. const MathFunctions = require('../formulas/functions/math');
  3. const TrigFunctions = require('../formulas/functions/trigonometry');
  4. const LogicalFunctions = require('../formulas/functions/logical');
  5. const EngFunctions = require('../formulas/functions/engineering');
  6. const ReferenceFunctions = require('../formulas/functions/reference');
  7. const InformationFunctions = require('../formulas/functions/information');
  8. const StatisticalFunctions = require('../formulas/functions/statistical');
  9. const DateFunctions = require('../formulas/functions/date');
  10. const WebFunctions = require('../formulas/functions/web');
  11. const FormulaError = require('../formulas/error');
  12. const {FormulaHelpers} = require('../formulas/helpers');
  13. const {Parser, allTokens} = require('./parsing');
  14. const lexer = require('./lexing');
  15. const Utils = require('./utils');
  17. /**
  18.  * A Excel Formula Parser & Evaluator
  19.  */
  20. class FormulaParser {
  22.     /**
  23.      * @param {{functions: {}, functionsNeedContext: {}, onVariable: function, onCell: function, onRange: function}} [config]
  24.      * @param isTest - is in testing environment
  25.      */
  26.     constructor(config, isTest = false) {
  27.         this.logs = [];
  28.         this.isTest = isTest;
  29.         this.utils = new Utils(this);
  30.         config = Object.assign({
  31.             functions: {},
  32.             functionsNeedContext: {},
  33.             onVariable: () => null,
  34.             onCell: () => 0,
  35.             onRange: () => [[0]],
  36.         }, config);
  38.         this.onVariable = config.onVariable;
  39.         this.functions = Object.assign({}, DateFunctions, StatisticalFunctions, InformationFunctions, ReferenceFunctions,
  40.             EngFunctions, LogicalFunctions, TextFunctions, MathFunctions, TrigFunctions, WebFunctions,
  41.             config.functions, config.functionsNeedContext);
  42.         this.onRange = config.onRange;
  43.         this.onCell = config.onCell;
  45.         // functions treat null as 0, other functions treats null as ""
  46.         this.funsNullAs0 = Object.keys(MathFunctions)
  47.             .concat(Object.keys(TrigFunctions))
  48.             .concat(Object.keys(LogicalFunctions))
  49.             .concat(Object.keys(EngFunctions))
  50.             .concat(Object.keys(ReferenceFunctions))
  51.             .concat(Object.keys(StatisticalFunctions))
  52.             .concat(Object.keys(DateFunctions));
  54.         // functions need context and don't need to retrieve references
  55.         this.funsNeedContextAndNoDataRetrieve = ['ROW', 'ROWS', 'COLUMN', 'COLUMNS', 'SUMIF', 'INDEX', 'AVERAGEIF', 'IF'];
  57.         // functions need parser context
  58.         this.funsNeedContext = [...Object.keys(config.functionsNeedContext), ...this.funsNeedContextAndNoDataRetrieve,
  59.             'INDEX', 'OFFSET', 'INDIRECT', 'IF', 'CHOOSE', 'WEBSERVICE'];
  61.         // functions preserve reference in arguments
  62.         this.funsPreserveRef = Object.keys(InformationFunctions);
  64.         this.parser = new Parser(this, this.utils);
  65.     }
  67.     /**
  68.      * Get all lexing token names. Webpack needs this.
  69.      * @return {Array.<string>} - All token names that should not be minimized.
  70.      */
  71.     static get allTokens() {
  72.         return allTokens;
  73.     }
  75.     /**
  76.      * Get value from the cell reference
  77.      * @param ref
  78.      * @return {*}
  79.      */
  80.     getCell(ref) {
  81.         // console.log('get cell', JSON.stringify(ref));
  82.         if (ref.sheet == null)
  83.             ref.sheet = this.position ? this.position.sheet : undefined;
  84.         return this.onCell(ref);
  85.     }
  87.     /**
  88.      * Get values from the range reference.
  89.      * @param ref
  90.      * @return {*}
  91.      */
  92.     getRange(ref) {
  93.         // console.log('get range', JSON.stringify(ref));
  94.         if (ref.sheet == null)
  95.             ref.sheet = this.position ? this.position.sheet : undefined;
  96.         return this.onRange(ref)
  97.     }
  99.     /**
  100.      * TODO:
  101.      * Get references or values from a user defined variable.
  102.      * @param name
  103.      * @return {*}
  104.      */
  105.     getVariable(name) {
  106.         // console.log('get variable', name);
  107.         const res = {ref: this.onVariable(name, this.position.sheet, this.position)};
  108.         if (res.ref == null)
  109.             return FormulaError.NAME;
  110.         return res;
  111.     }
  113.     /**
  114.      * Retrieve values from the given reference.
  115.      * @param valueOrRef
  116.      * @return {*}
  117.      */
  118.     retrieveRef(valueOrRef) {
  119.         if (FormulaHelpers.isRangeRef(valueOrRef)) {
  120.             return this.getRange(valueOrRef.ref);
  121.         }
  122.         if (FormulaHelpers.isCellRef(valueOrRef)) {
  123.             return this.getCell(valueOrRef.ref)
  124.         }
  125.         return valueOrRef;
  126.     }
  128.     /**
  129.      * Call an excel function.
  130.      * @param name - Function name.
  131.      * @param args - Arguments that pass to the function.
  132.      * @return {*}
  133.      */
  134.     _callFunction(name, args) {
  135.         if (name.indexOf('_xlfn.') === 0)
  136.             name = name.slice(6);
  137.         name = name.toUpperCase();
  138.         // if one arg is null, it means 0 or "" depends on the function it calls
  139.         const nullValue = this.funsNullAs0.includes(name) ? 0 : '';
  141.         if (!this.funsNeedContextAndNoDataRetrieve.includes(name)) {
  142.             // retrieve reference
  143.             args = args.map(arg => {
  144.                 if (arg === null)
  145.                     return {value: nullValue, isArray: false, omitted: true};
  146.                 const res = this.utils.extractRefValue(arg);
  148.                 if (this.funsPreserveRef.includes(name)) {
  149.                     return {value: res.val, isArray: res.isArray, ref: arg.ref};
  150.                 }
  151.                 return {
  152.                     value: res.val,
  153.                     isArray: res.isArray,
  154.                     isRangeRef: !!FormulaHelpers.isRangeRef(arg),
  155.                     isCellRef: !!FormulaHelpers.isCellRef(arg)
  156.                 };
  157.             });
  158.         }
  159.         // console.log('callFunction', name, args)
  161.         if (this.functions[name]) {
  162.             let res;
  163.             try {
  164.                 if (!this.funsNeedContextAndNoDataRetrieve.includes(name) && !this.funsNeedContext.includes(name))
  165.                     res = (this.functions[name](...args));
  166.                 else
  167.                     res = (this.functions[name](this, ...args));
  168.             } catch (e) {
  169.                 // allow functions throw FormulaError, this make functions easier to implement!
  170.                 if (e instanceof FormulaError) {
  171.                     return e;
  172.                 } else {
  173.                     throw e;
  174.                 }
  175.             }
  176.             if (res === undefined) {
  177.                 // console.log(`Function ${name} may be not implemented.`);
  178.                 if (this.isTest) {
  179.                     if (!this.logs.includes(name)) this.logs.push(name);
  180.                     return {value: 0, ref: {}};
  181.                 }
  182.                 throw FormulaError.NOT_IMPLEMENTED(name);
  183.             }
  184.             return res;
  185.         } else {
  186.             // console.log(`Function ${name} is not implemented`);
  187.             if (this.isTest) {
  188.                 if (!this.logs.includes(name)) this.logs.push(name);
  189.                 return {value: 0, ref: {}};
  190.             }
  191.             throw FormulaError.NOT_IMPLEMENTED(name);
  192.         }
  193.     }
  195.     async callFunctionAsync(name, args) {
  196.         const awaitedArgs = [];
  197.         for (const arg of args) {
  198.             awaitedArgs.push(await arg);
  199.         }
  200.         const res = await this._callFunction(name, awaitedArgs);
  201.         return FormulaHelpers.checkFunctionResult(res)
  202.     }
  204.     callFunction(name, args) {
  205.         if (this.async) {
  206.             return this.callFunctionAsync(name, args);
  207.         } else {
  208.             const res = this._callFunction(name, args);
  209.             return FormulaHelpers.checkFunctionResult(res);
  210.         }
  211.     }
  213.     /**
  214.      * Return currently supported functions.
  215.      * @return {this}
  216.      */
  217.     supportedFunctions() {
  218.         const supported = [];
  219.         const functions = Object.keys(this.functions);
  220.         functions.forEach(fun => {
  221.             try {
  222.                 const res = this.functions[fun](0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
  223.                 if (res === undefined) return;
  224.                 supported.push(fun);
  225.             } catch (e) {
  226.                 if (e instanceof Error)
  227.                     supported.push(fun);
  228.             }
  229.         });
  230.         return supported.sort();
  231.     }
  233.     /**
  234.      * Check and return the appropriate formula result.
  235.      * @param result
  236.      * @param {boolean} [allowReturnArray] - If the formula can return an array
  237.      * @return {*}
  238.      */
  239.     checkFormulaResult(result, allowReturnArray = false) {
  240.         const type = typeof result;
  241.         // number
  242.         if (type === 'number') {
  243.             if (isNaN(result)) {
  244.                 return FormulaError.VALUE;
  245.             } else if (!isFinite(result)) {
  246.                 return FormulaError.NUM;
  247.             }
  248.             result += 0; // make -0 to 0
  249.         } else if (type === 'object') {
  250.             if (result instanceof FormulaError)
  251.                 return result;
  252.             if (allowReturnArray) {
  253.                 if (result.ref) {
  254.                     result = this.retrieveRef(result);
  255.                 }
  256.                 // Disallow union, and other unknown data types.
  257.                 // e.g. `=(A1:C1, A2:E9)` -> #VALUE!
  258.                 if (typeof result === 'object' && !Array.isArray(result) && result != null) {
  259.                     return FormulaError.VALUE;
  260.                 }
  262.             } else {
  263.                 if (result.ref && result.ref.row && !result.ref.from) {
  264.                     // single cell reference
  265.                     result = this.retrieveRef(result);
  266.                 } else if (result.ref && result.ref.from && result.ref.from.col === result.ref.to.col) {
  267.                     // single Column reference
  268.                     result = this.retrieveRef({
  269.                         ref: {
  270.                             row: result.ref.from.row, col: result.ref.from.col
  271.                         }
  272.                     });
  273.                 } else if (Array.isArray(result)) {
  274.                     result = result[0][0]
  275.                 } else {
  276.                     // array, range reference, union collections
  277.                     return FormulaError.VALUE;
  278.                 }
  279.             }
  280.         }
  281.         return result;
  282.     }
  284.     /**
  285.      * Parse an excel formula.
  286.      * @param {string} inputText
  287.      * @param {{row: number, col: number}} [position] - The position of the parsed formula
  288.      *              e.g. {row: 1, col: 1}
  289.      * @param {boolean} [allowReturnArray] - If the formula can return an array. Useful when parsing array formulas,
  290.      *                                      or data validation formulas.
  291.      * @returns {*}
  292.      */
  293.     parse(inputText, position, allowReturnArray = false) {
  294.         if (inputText.length === 0) throw Error('Input must not be empty.');
  295.         this.position = position;
  296.         this.async = false;
  297.         const lexResult = lexer.lex(inputText);
  298.         this.parser.input = lexResult.tokens;
  299.         let res;
  300.         try {
  301.             res = this.parser.formulaWithBinaryOp();
  302.             res = this.checkFormulaResult(res, allowReturnArray);
  303.             if (res instanceof FormulaError) {
  304.                 return res;
  305.             }
  306.         } catch (e) {
  307.             throw FormulaError.ERROR(e.message, e);
  308.         }
  309.         if (this.parser.errors.length > 0) {
  310.             const error = this.parser.errors[0];
  311.             throw Utils.formatChevrotainError(error, inputText);
  312.         }
  313.         return res;
  314.     }
  316.     /**
  317.      * Parse an excel formula asynchronously.
  318.      * Use when providing custom async functions.
  319.      * @param {string} inputText
  320.      * @param {{row: number, col: number}} [position] - The position of the parsed formula
  321.      *              e.g. {row: 1, col: 1}
  322.      * @param {boolean} [allowReturnArray] - If the formula can return an array. Useful when parsing array formulas,
  323.      *                                      or data validation formulas.
  324.      * @returns {*}
  325.      */
  326.     async parseAsync(inputText, position, allowReturnArray = false) {
  327.         if (inputText.length === 0) throw Error('Input must not be empty.');
  328.         this.position = position;
  329.         this.async = true;
  330.         const lexResult = lexer.lex(inputText);
  331.         this.parser.input = lexResult.tokens;
  332.         let res;
  333.         try {
  334.             res = await this.parser.formulaWithBinaryOp();
  335.             res = this.checkFormulaResult(res, allowReturnArray);
  336.             if (res instanceof FormulaError) {
  337.                 return res;
  338.             }
  339.         } catch (e) {
  340.             throw FormulaError.ERROR(e.message, e);
  341.         }
  342.         if (this.parser.errors.length > 0) {
  343.             const error = this.parser.errors[0];
  344.             throw Utils.formatChevrotainError(error, inputText);
  345.         }
  346.         return res;
  347.     }
  348. }
  350. module.exports = {
  351.     FormulaParser,
  352.     FormulaHelpers,
  353. };