search

weshatheleopard / rubyXL

Ruby lib for reading/writing/modifying .xlsx and .xlsm files
MIT License
1.27k stars 253 forks source link

Formulas of t="shared" are processed as empty string #384

Open rPetas opened 3 years ago

rPetas commented 3 years ago

The following file is a simple table with many formulas in the same row(basically a sum of each column in the table) When the file is edited it automatically changes these formula to a "shared" type of formula to optimize space usage.

Currently when trying to read the formula on these cells with the "shared" type, the expression comes as an empty string, probably because the expression the in inline text in the tag is being read (and it does not exist), but in this case it should be somehow calculated from its reference (si).

This was detected because I am trying to read the formulas to update them after inserting some lines on the table.

To circumvent the problem ill try in my code to process how the formula should be by myself. I just though it should be known. I could try to help if you point me in the right direction. example.xlsx

weshatheleopard commented 3 years ago

I will need some usage questions answered.

Specifically: the goal of rubyXL is not to give you an Excel experience. The goal is to give you access to the data, so you can make your own experience in whatever way you deem suitable.

From that standpoint, cell.formula gives you access to formula tag of the XML encoding that cell, not "gives you formula that Excel shows in that cell".

It is possible to make an convenience method that will give you "formula that Excel shows in that cell". However, you will have to answer at least a few questions for that situation.

  1. What would you like that accessor to be called? Note that the name formula is already taken.
  2. If a cell has a shared formula (t="shared" si="...") in it, then what should we do when a new formula is attempted to be written into it? a) Remove the original group formula from the cell and insert a new "singular" formula, or b) replace the formula value in that cell, which means the formula will magically change in the entire group?
  3. If you choose a) then note that when a "group" formula in a cell is replaced by a "singular" formula then the @-value and ref value may need to move to a different cell (that still remains a part of the formula group) as well.
  4. Note that the cell addresses in the formula that are not prepended by $ are relative to the current cell. So assume you have a group formula that is located in the cell A1 and says "=B2". Which, given that it's relative to A1, means "={+1,+1}". Then if cell F23 is also in that group, in the cell A1 that formula will mean "=B2", and in the cell "F23", it will mean "=G24" -- while formula expression that the accessor returns remains exactly the same. So if you are using that special accessor we are planning to write, given that the formula parser hasn't been written yet, such conversion can't be done in rubyXL and then people will complain that "formula that they get using that accessor is is not what Excel gives them". Are you prepared for that?

In short, like with just about everything else, the idea looks great on the surface -- but as soon as you start actually digging deeper, the number of things that need to be done, balloons rapidly.