lesterlyu / fast-formula-parser Goto Github PK

Firstly Thank you for all your contributions - Its amazing utility !

I am trying to use this in our workflow but have found an issue:

Lets say Cell A1 has value "1"

SUM("1", "1") === 2
SUM(A1, A1) === 2
but
SUM(var1, var1) === 0 where var1 resolve to A1

This is because code treat literals different from variable references.
https://github.com/LesterLyu/fast-formula-parser/blob/master/formulas/functions/math.js#L599

Is there any way to be able to use variables but let them be treated similar to literals or cell references?

Thank you for all your contributions !
Makarand

Do you know why i receive parser error with double quotes?

import {
  lex,
} from "fast-formula-parser/grammar/lexing";

lex('=SUM("') => Throws parsing error (one double quote)

lex('=SUM('') => Works fine (one single quote)

To be consistent with Excel, Can boolean functions like ISNUMBER, ISEMPTY return uppercase TRUE|FALSE

Currently lowercase true is returned.

Hi,
we have the formula defined in a excel sheet that formula is reference to multiple cell values from another sheet in same excel file. we have to evalute that formula to get the value/result using node js. we have tried multiple npm packages but receiving formula instead of its value as the result.

Below are the packages we tried
1.exceljs
2. xlsx
3. 'xlsx-calc' and formulajs/formulajs'
4. hyperformula

const Excel = require('exceljs');
const workbook = new Excel.Workbook();
//Reading excel sheet here with passing path of sheet
workbook.xlsx.readFile("./SimpleExampleToTestExcelFormulaSettingInNodeJS.xlsx").then(() => {
var worksheet = workbook.getWorksheet("Sheet1");// In excel sheet1 is reading here
var templateSheet = workbook.getWorksheet("Sheet2");// In excel sheet2 is reading here

    //I want to read excel cell and formula value also here I have tried below scenario.

    //Scenario 1
    console.log("getValue", worksheet.getCell('A' + 15).value);
    //getValue { formula: 'B9', result: 'Action for Step 1' }
    //that time getting this result.

    //Scenario 2
    var cellFormula = 'B9';
    //Here i am try to set B9 value in A20 
    worksheet.getCell('A' + 20).formula = { formula: cellFormula };
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That getting result getValue null

    //Scenario 3
    var cellFormula = 'B9';
    worksheet.getCell('A' + 20).value = { formula: cellFormula };
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue { formula: 'B9' }

    //Scenario 5
    var cellFormula = '=B9';
    worksheet.getCell('A' + 20).value = cellFormula;
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue =B9

    //Scenario 6
    var cellFormula = '=B9';
    worksheet.getCell('A' + 20).formula = cellFormula;
    console.log("getValue", worksheet.getCell('A' + 20).value);
    //That time getting getValue =B9

    //But i want in A20 { formula: 'B9', result: 'Action for Step 1' }

    //Scenario 7
    //This one is trying to read another sheet2 and set value in A3 is Sheet1B9 value.
    var cellFormula = '=Sheet1!B9';
    worksheet1.getCell('A' + 3).formula = cellFormula;
    console.log("getValue", worksheet1.getCell('A' + 3).value);
    //That time getting getValue null

    //Scenario 8
    var cellFormula = '=Sheet1!B9';
    templateSheet.getCell('A' + 3).value = cellFormula;
    console.log("getValue", worksheet1.getCell('A' + 3).value);
    //That time getting getValue =Sheet1!B9

});

using 'xlsx-calc' and formulajs/formulajs' NPM
var XLSX_CALC = require('xlsx-calc');
var formulajs = require('@formulajs/formulajs');

//scenario 1
XLSX_CALC.import_functions(formulajs);
worksheet.C30 = { f: '=Template!H11'};
console.log("value", worksheet.C30.value);
//that time getting also same formula here

//scenario 2

XLSX_CALC.import_functions(formulajs);
worksheet.C30 = { f: '=Template!H11', v: "ghjhg" };
console.log("value", worksheet.C30.value);
//that time getting formula and value but value also set here

5.using xlsx npm

const reader = require('xlsx');
const workbook1 = reader.readFile(req.file.path);
var wb = workbook1.Sheets["FieldMapping"];
//scenario 1
var data = reader.utils.getCell(wb, 'A'+100 ,"=Template!E11",1)
//this one also not working proper

//scenario 2

reader.utils.sheet_set_array_formula(wb, "C1", "=Template!E11", 1);
//this time also get undefined value.

4 using 'hyperformula' Npm

const HyperFormula = require('hyperformula');
const data = ['=Template!E11'];
var setFormula = HyperFormula.buildFromArray(data ,’A’+100);
var getResult = hfInstance.getCellValue('A'+100);
console.log(getResult);
//This is also not working as expected.

When parsing an index formula like the following:

INDEX('Sheet1'!A1:AV90,'Sheet2'!C28,'Sheet2'!G2)

The result of new FormulaParser.DepParser().parse() is:

[{ from: { row: 1, col: 1 } }, sheet: "Sheet 1", to: { row: 90, col: 48 } ]

This would suggest there are thousands of dependencies, but really is are only 1 when the index is resolved, the row & column values given by arguments 2 & 3 are used to get the cell in question.

Maybe I am thinking about this wrong but this is how I see it, any thoughts are welcome

Why?

People need to know the format of the formula result to visulize the result.

Example 1:

DATE(2000, 1, 1) results in

• 36526 in General format
• 1/1/2000 in Date format.
  Internally, the value is being stored as a number 36526 (or so-called excel serial number), but there is a way to store and propagate the type of the value when evaluating the formula DATE(2000, 1, 1). Thus, we can tell users that the result is a Date.

Example 2:

DATE(CONCAT(200, 0), 1, 1) returns a date value/ serial number.
CONCAT(200, 0) always return a string (or error), DATE(CONCAT(200, 0), 1, 1) always return a date (or error).

Example 3:

CONCATE(DATE(2000, 1, 1)) returns a string.

Questions

Do we need to propagate the type internally?

It is unnecessary. We only need to know the out-most function name, since the out-most function determines the result type.

How many types are there?

So far, I discovered only Date and General. General can be JavaScript Number, String, Boolean.

How about some formulas returning a cell reference, range reference, array? Should we tell the user what we returning?

How about a simple reference =A1, where A1 has a formula type Date?

We should know the type of the given cell, but how?

Should we provide a format function in the result wrapper, which is basically SSF.format but only takes a format string?

Do we use a pure JS object to wrap the formula result, or a class?

class may use more memory. Generating more unnecessary data → Invoke Garbage Collector more frequently.

More questions?

Proposing

Currently I don't have a clear idea of the implementation, we must think of all the questions above carefully.

Instead of returning the value directly, we return an object that wraps the result and add information about the type, e.g., Date, or Excel Serial Number.

Currently =SUM(A2:A) computes the entire column as if the formula was =SUM(A:A).

While this would throw an error in Excel, google sheets parses this as the entire column, starting at row 2. Would it be possible to support these partial column and row references

Thanks!

Is there any plan/time table for Type definitions?

Hi Lester,

Aren't lines 101-106 of your readme example unnecessary and unreachable due to the first return statement? Or I could be missing some silly fact somewhere.

Thanks,

Daniel Gesua

https://github.com/LesterLyu/fast-formula-parser/blob/master/grammar/hooks.js#L107

Right now, onVariable callbacks has access to variable name and sheet, but not the cell position.

Can i propose to change

const res = {ref: this.onVariable(name, this.position.sheet)};

to

const res = {ref: this.onVariable(name, this.position)};

The reason is that lets say i have a formula =sales * 10, in a table. For each onVariable call, i want to access the cell position and return the correct position of sales column.

Eg:

Should this be extended to null and undefined values. I think thats how excel or google sheets does the check

value === null || value === void 0 || value === ''

ISBLANK: (value) => {
  if (!value.ref)
      return false;
  value = H.accept(value);
  return typeof value === 'string' && value.length === 0;
}

Hi Lester,

I have an issue with parseAsync :

If both arguments use not implemented function then we have an UnhandledPromiseRejectionWarning.

You can reproduce with this formula "SUM(NOTIMP1(2,1), NOTIMP2(3,4))"

I found a workaround by changing line 191 of /grammar/hooks.js from
'throw FormulaError.NOT_IMPLEMENTED(name);' to
'return FormulaError.NOT_IMPLEMENTED(name);' and it seems to work.

Best regards,

Jacques Loriot

2 issues

1. Functions supplied in functionsNeedContext does not work as it throws function not found error. This is because we are checking for custom functions in if (this.functions[name]) {

2. Right now only functionsNeedContext can access context position. Is there anyway we can inject context as the last argument to all functions ? Or the first arg ? Its quite useful when executing side-effects since we know the positions of the cell.

Hi Lester,

I just noticed some minor errors in your diagram and figured I'd give you a heads up:

Take a look at "functionCall". It's missing the opening parenthesis node.

Also, "postfixOp" does not get used anywhere in the diagram or the code.

Thanks for this awesome project.

Best,

Daniel Gesua

Some error codes are wrongs

1. Empty arguments - Expected FormulaError { _error: '#NA!' }

console.log(parser.parse('SUM()', {sheet: 'Sheet 1', row: 1, col: 1}));

Received - Error: Argument type 0 is missing.

2. Unknown function - Expected FormulaError { _error: '#NAME?' }

console.log(parser.parse('SUMABC()', {sheet: 'Sheet 1', row: 1, col: 1}));

Received Error: Function SUMS is not implemented.

Any idea how i can do this

=TODAY() + 1 => should return tomorrow

Right now, it converts date to serial number and adds 1

For example how to handle formula SUM(A1,B2) if A1 is also =B4-B3?

50*.85 throws an error while 50*0.85 works.

Is this an issue with the parser?

Hi, Right now if we invoke a custom function =HELLO(A1)

The first argument of the function contains an object with value in the cell as per

https://github.com/LesterLyu/fast-formula-parser/blob/master/grammar/hooks.js#L151

Is there anyway i can access the position of the cell. Any possibility of changing L151 to

{
  value: res.val,
  isArray: res.isArray,
  isRangeRef: !!FormulaHelpers.isRangeRef(arg),
  isCellRef: !!FormulaHelpers.isCellRef(arg),
  position: arg.ref // New property
}

I can't seem to find a way to return a valid date from inside a variable.

  onCell: ({ sheet, row, col }) => {
      return new Date();
      //or
      return [1999,9,9];
      //or
      return "1999-9-9";
      //or
      return "1999/9/9";
  }

None of the above works.

supportedFunctions does not have any functions from funsNeedContextAndNoDataRetrieve, Could you add it?

Please help, how I should work with cases like 0.1+0.2, I got invalid value, I fixed it by toFixed(15), but I have problem with case 0.2^-2, where result is 24.999999999996
Should I create fork and implement math ops with big.js?

SEARCH and COUNTIF seem not to support arrays in criteria argument. For both functions, only the first element is evaluated.

For example, SEARCH evaluates "outlook.com" but not "gmail.com":

IF(SUMPRODUCT( -- ISNUMBER(SEARCH({"outlook.com", "gmail.com"},A1)))>0,A1,RIGHT(A1,LEN(A1)-FIND("@",A1)))

A1: [email protected]

--> result: gmail.com

A1: [email protected]

--> result: [email protected]

Same in COUNTIF

Is this project still supported/maintained?

Excel supports IFS formulae in which pairs of (condition, value) are evaluated and the first matching one wins.
e.g.

=IFS(1=3,"Not me", 1=2, "Me neither", 1=1, "Yes me")

returns

Yes me

fast-formula-parser throws errors when I try and use these.

Hi Lester,

I tried parsing "SUM((SUM(A2)-48):(SUM(A2)-48))" in your demo and got a valid answer (the sum of the 2nd row). However, while this works on your parser this would give a formula error in Excel.

Is this desirable behavior for your parser?

Best,

Daniel Gesua

If you run

parser.parse(IF(20 < 0, "yep", "nope")) => nope

With parseAsync

parser.parseAsync(IF(20 < 0, "yep", "nope")) => yep

Any reason this is happening?

As the title says.