0

I have a strange behavior when reading an excel file. When I open the file and save it with my version of Excel, the file is properly read with xlsx. However, when I do not touch it and read it with xlsx, some formulas are offseted. Here is an example: I read my excel file and focus on cell O124. Before opening it and saving it (without doing any modification), here is what I get in cell O124:

    { t: 'n', v: 18202.970297029704, f: 'O124*(1+P144)*(1+P$146)', w: '18,203',

Of course, there is here a circular reference (on O124). The P144 and P146 are also shifted. However, when I open it and save it with Excel (I am using Excel 2019), I can see the formula is N124 and not O124 (so no circular reference). Here is he result:

    { t: 'n', v: 18202.970297029704, f: 'N124*(1+O144)*(1+O$146)', w: '18,203',

I have correctly the N124, O144 and O146.

I really do not understand why there is a shift when I do not "resave" my file first. Would you have any ideas?

[EDIT] I am using the xlsx package with node js and then I use the xlsx-calc package to make calculation on the sheet. This is where I got the circular ref error first.

Thanks a lot for your help!

StaP
  • 107
  • 3
  • 13

1 Answers1

0

For those interested, it is solved when using the latestest version of the package xlsx (0.14.4). Indeed, as answered by dev of the package, it is due to shared formulas in excel. In the xml of the file we have:

    <c r="N123" s="20">
        <f t="shared" ref="M123:O125" si="201">M123*(1+N143)*(1+N$146)</f>
        <v>12208.016586040083</v>
      </c>


      <c r="O124" s="20">
        <f t="shared" si="201"/>
        <v>18202.970297029704</v>

That si is an index that references the shared formula. The issue is solved with the latest version of the package.

StaP
  • 107
  • 3
  • 13