Shared formulas incorrectly parsed. #1388

New Issue

2018-12-18T15:11:40Z

jack-williams commented

2018-12-18 15:11:40 +00:00

(Migrated from github.com)

Not really sure what is going on here, but something seems wacky with shared formulas. I hope these steps reproduce the issues.

Steps to reproduce:

Open blank workbook.
Enter =A1 into cell B1.
Drag fill B1 down to B10.
Select B1:B5 and clear contents.
Parse workbook.

Expected:

{ t: 'n', v: 0, f: 'A6', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A7', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A8', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A9', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A10', z: 'General', w: '0' }

Received:

{ t: 'n', v: 0, f: 'A6', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A11', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A12', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A13', z: 'General', w: '0' }
{ t: 'n', v: 0, f: 'A14', z: 'General', w: '0' }

Not really sure what is going on here, but something seems wacky with shared formulas. I hope these steps reproduce the issues. Steps to reproduce: 1. Open blank workbook. 2. Enter =A1 into cell B1. 3. Drag fill B1 down to B10. 4. Select B1:B5 and clear contents. 5. Parse workbook. **Expected**: ``` { t: 'n', v: 0, f: 'A6', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A7', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A8', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A9', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A10', z: 'General', w: '0' } ``` **Received**: ``` { t: 'n', v: 0, f: 'A6', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A11', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A12', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A13', z: 'General', w: '0' } { t: 'n', v: 0, f: 'A14', z: 'General', w: '0' } ```

DanielFabian commented

2019-04-29 13:53:40 +00:00

(Migrated from github.com)

The second argument to shift_formula_xlsx needs to be the original formula's tag.r not its ftag.ref and it needs to use decode_cell not decode_range. Something like this:

  function shift_formula_xlsx(f, range, cell) {
    var r = decode_cell(range),
      c = decode_cell(cell);
    var delta = { r: c.r - r.r, c: c.c - r.c };
    return shift_formula_str(f, delta);
  }

According, https://github.com/SheetJS/js-xlsx/blob/0c36667c0c1c50cabec680387d06df4245ec6876/bits/67_wsxml.js#L346 this line needs to be changed to something like:

                sharedf[parseInt(ftag.si, 10)] = [tag.r, _xlfn(unescapexml(utf8read(cref[1])))];

and https://github.com/SheetJS/js-xlsx/blob/0c36667c0c1c50cabec680387d06df4245ec6876/bits/67_wsxml.js#L350 to something like:

              if (sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][0], tag.r);

The second argument to `shift_formula_xlsx` needs to be the original formula's `tag.r` not its `ftag.ref` and it needs to use `decode_cell` not `decode_range`. Something like this: ``` function shift_formula_xlsx(f, range, cell) { var r = decode_cell(range), c = decode_cell(cell); var delta = { r: c.r - r.r, c: c.c - r.c }; return shift_formula_str(f, delta); } ``` According, `https://github.com/SheetJS/js-xlsx/blob/0c36667c0c1c50cabec680387d06df4245ec6876/bits/67_wsxml.js#L346` this line needs to be changed to something like: ``` sharedf[parseInt(ftag.si, 10)] = [tag.r, _xlfn(unescapexml(utf8read(cref[1])))]; ``` and `https://github.com/SheetJS/js-xlsx/blob/0c36667c0c1c50cabec680387d06df4245ec6876/bits/67_wsxml.js#L350` to something like: ``` if (sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][0], tag.r); ```

SheetJSDev commented

2019-04-29 14:47:15 +00:00

(Migrated from github.com)

Our Pro version has a slightly different diff but has the same effect:

< 						sharedf[parseInt(ftag.si, 10)] = [ftag, _xlfn(unescapexml(utf8read(cref[1])))];
---
> 						sharedf[parseInt(ftag.si, 10)] = [ftag, _xlfn(unescapexml(utf8read(cref[1]))), tag.r];

< 					if(sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][0].ref, tag.r);
---
> 					if(sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][2], tag.r);

We've been slow on the update to the open source build but will try to roll out a patch with it and more formula-related fixes soon

Our Pro version has a slightly different diff but has the same effect: ```js < sharedf[parseInt(ftag.si, 10)] = [ftag, _xlfn(unescapexml(utf8read(cref[1])))]; --- > sharedf[parseInt(ftag.si, 10)] = [ftag, _xlfn(unescapexml(utf8read(cref[1]))), tag.r]; < if(sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][0].ref, tag.r); --- > if(sharedf[ftag.si]) p.f = shift_formula_xlsx(sharedf[ftag.si][1], sharedf[ftag.si][2], tag.r); ``` We've been slow on the update to the open source build but will try to roll out a patch with it and more formula-related fixes soon

jack-williams commented

2019-04-30 10:07:29 +00:00

(Migrated from github.com)

Thanks!

Sign in to join this conversation.