Compute formula value after change #105

New Issue

2014-09-02T13:30:13Z

BrunoMarchesson commented

2014-09-02 13:30:13 +00:00

(Migrated from github.com)

Hello,

Not sure that it is an issue or a known limitation, but is it possible to refresh/recompute a formula value after changing a cell one ?
(for example, C1 has formula A1+B1, and I would like to get the C1 value updated after changing A1 value).

Hello, Not sure that it is an issue or a known limitation, but is it possible to refresh/recompute a formula value after changing a cell one ? (for example, C1 has formula A1+B1, and I would like to get the C1 value updated after changing A1 value).

SheetJSDev commented

2014-09-02 14:28:16 +00:00

(Migrated from github.com)

@BrunoMarchesson The goal of this library is to convert between Excel files and JS representations (the form is described in the README ). The xls library also produces the same type of structure.

Since there is no XLSX or XLSB-specific code involved, I think it makes the most sense to build a separate library for this. The key components are:

A) Formula parser: IMHO the best starting point is [MS-XLSB] 2.5.97.88. XLSB stores formulae as a series of Ptgs ("parsed things", a series of tokens that a parser would produce), so the ABNF is theoretically complete.

B) Math engine: It isn't difficult to get basic things like SUM right, but other functions are trickier. Lots of tests involved.

C) Dependency management: the trickiest part is keeping track of non-obvious dependencies: functions like OFFSET create dependencies.

I'll leave this issue open in case you or someone else is interested in tackling this problem

@BrunoMarchesson The goal of this library is to convert between Excel files and JS representations (the form is described [in the README](https://github.com/SheetJS/js-xlsx#workbook--worksheet--cell-object-description) ). The [xls library](http://git.io/xls) also produces the same type of structure. Since there is no XLSX or XLSB-specific code involved, I think it makes the most sense to build a separate library for this. The key components are: A) Formula parser: IMHO the best starting point is [MS-XLSB] 2.5.97.88. XLSB stores formulae as a series of Ptgs ("parsed things", a series of tokens that a parser would produce), so the ABNF is theoretically complete. B) Math engine: It isn't difficult to get basic things like SUM right, but other functions are trickier. Lots of tests involved. C) Dependency management: the trickiest part is keeping track of non-obvious dependencies: functions like OFFSET create dependencies. I'll leave this issue open in case you or someone else is interested in tackling this problem

BrunoMarchesson commented

2014-09-03 08:21:13 +00:00

(Migrated from github.com)

Many thanks for the quick answer.
I will try to write a piece of code for supporting some basic formulas and keep you informed !

Regards

Many thanks for the quick answer. I will try to write a piece of code for supporting some basic formulas and keep you informed ! Regards

d00001 commented

2014-12-08 20:28:56 +00:00

(Migrated from github.com)

@BrunoMarchesson Any progress on this? I might be able to get involved.

greyarch commented

2015-10-01 08:08:48 +00:00

(Migrated from github.com)

Is there any progress on the basic formula support? Would be much appreciated.

reviewher commented

2017-03-25 05:13:14 +00:00

(Migrated from github.com)

There are packages that do this exact thing, like https://github.com/fabiooshiro/xlsx-calc,

Harshitg10 commented

2018-02-15 12:21:59 +00:00

(Migrated from github.com)

ws['A1']['f'] = "=SUM(100,200)"
This 1 worked for me

`ws['A1']['f'] = "=SUM(100,200)" ` This 1 worked for me

SheetJSDev commented

2018-03-17 16:20:54 +00:00

(Migrated from github.com)

The formula bodies are currently round-tripped (so if you read a file, edit a value, write a new file and open the new file in Excel, you will see the new values). Formula recalculation is supported in the Pro version and we may eventually make it open source.

Sign in to join this conversation.