Hi @muscapades - I see where you're going with this branch but I have some concerns:

1. it's intentionally losing information about the sharedness of the linked cells. It's hard to tell whether Excel itself offers any UI to manipulate this but I can see that it is very enthusiastic to share formulas wherever it can - if it recognises that a range is using the same (but shifted) formula, it will reduce it to shared cells, even if each cell's formula was individually typed (I tried it out).

2. If the formula is large and/or there are many copies of that formula, expanding the formulas for each cell will result in a much larger xlsx file size. Since the formula must be translated into each cell, the zip compression cannot efficiently compress them as the cell refs will be different for each cell

3. We also lose out on the opportunity to mass-paste a formula into a sheet. Imagine I want a table of N columns with some calculated formula based columns on each row. It's a pain to have to re-format the formula for each row - whereas if we add a shared formulas to the ExcelJS API, then it's easy...

4. The developers of Excel seem to take pleasure in making some things about as complicated as possible and I also worry that this might turn into bugs in the formula translation - either now or in the future for example if a cell reference is contained inside a text string, it probably shouldn't be translated, etc.

Right now, my vote is tending towards your previous PR - what are your thoughts?

Hi @guyonroche,

All good points...

Ad 1) I don't think the information is lost, since model.sharedFormula still points to the cell where the formula was shared from. So as the branch is now, I think the user has all the information - that the formula was shared, where it was shared from, and what (I think) the formula converts to. This is one reason why I kept the SharedFormulaValue in the branch instead of making the cell a FormulaValue (and loosing the information about where the original formula is).

Ad 3) Do you plan to add features like this? Making shared formulas a "first class citizen" in exceljs?

Ad 4) Quite right, there may be examples where we need to parse the formula instead of regexing. My inclination would be to wait for those examples to surface before worrying about them. Is there another place in the code where exceljs are handling parsed formulas, and where the conversion could be cleaner?

What I did not like about the first PR is that it left the problem to the user of exceljs, and leaving it to everybody to come up with their own handling of it. As a user of exceljs, I would welcome any help it could give me in handling shared formulas. So as a minimum I would propose that SharedFormula.slideFormula is offered to the users as a way out.

Yeah I intended to make shared formulas first class citizens - with a value of the form { sharedFormula: 'A2', result: 5 }
I was also thinking to add a convenience function like sheet.fillFunction('A1+B1', 'A1:D10', values) - which would set the range A1:D10 to shared based on the formula in A1.
I'm part way through that already on the other branch.

That's convenient for constructing excel sheets in exceljs, I can see that.

My current use case is to read and analyze excel files, constructed in Excel, and I need to know the formula in each cell, same as what Excel would show when the cell is selected, so I need the converted formula. I don't know what everybody else will be using exceljs for, but for me this is a key feature.

The idea behind making two separate branches was precisely to leave it to you to decide what the right way for exceljs to handle shared formulas is. Knowing what the shared formula translates to for a particular cell is useful to me, and I think it might be for others too.

Cool - I can see the value of that use-case, what we could do is add something like a 'translatedFormula' property on the cell that will run that transformation (lazy style) but still keep the shared link as primary data.

Hey @muscapades - I'm nearing publish on the other branch. I took the translation formula from this branch and added a convenience function on Cell to return either the real formula or the translated one.
Just final red-tape, tests, documentation, etc and it's ready to go