Lack of shared formula creation support #2938

New Issue

TL-SWFT · 2023-05-10T06:53:34Z

TL-SWFT commented

2023-05-10 06:53:34 +00:00

sheetjs currently can correctly read spreadsheets with shared formulas, but cannot create new spreadsheets with shared formulas. I would like to see shared formula creation implemented in sheetjs.

For example, if I have a spreadsheet with shared formulas like this:

A	B	C
1	=A1*2	=B1*3
2		=C1
3		=C2

And I make changes to the spreadsheet in memory using sheetjs and want to save it, sheetjs will save it as:

A	B	C
1	=A1*2	=B1*3
2		=B2*3
3		=B3*3

It loses the shared formula and duplicates it in each cell. For full spreadsheet compatibility and file efficiency, I would recommend sheetjs implement creating spreadsheets with shared formulas on save.

Thank you for your consideration! Let me know if you have any other questions.

sheetjs currently can correctly read spreadsheets with shared formulas, but cannot create new spreadsheets with shared formulas. I would like to see shared formula creation implemented in sheetjs. For example, if I have a spreadsheet with shared formulas like this: | A | B | C | |:-:|:-:|:-:| | 1 | =A1*2 | =B1*3 | | 2 | | =C1 | | 3 | | =C2 | And I make changes to the spreadsheet in memory using sheetjs and want to save it, sheetjs will save it as: | A | B | C | |:-:|:-:|:-:| | 1 | =A1*2 | =B1*3 | | 2 | | =B2*3 | | 3 | | =B3*3 | It loses the shared formula and duplicates it in each cell. For full spreadsheet compatibility and file efficiency, I would recommend sheetjs implement creating spreadsheets with shared formulas on save. Thank you for your consideration! Let me know if you have any other questions.

sheetjs commented

2023-05-10 07:24:37 +00:00

The difference between the Excel case and the SheetJS case is that Excel uses contextual knowledge that SheetJS does not have.

The way to create a shared formula in Excel is to fill down. Suppose you set B1=A1*2, B2=A2*2, B3=A3*3 manually. Then set C1=A1*2 and fill down to C3.

That file is attached (Issue2938.xlsx) B3 is stored as a normal formula and C3 is stored as a shared formula:

      <c r="B3">
        <f>A3*2</f>
        <v>6</v>
      </c>

      <c r="C3">
        <f t="shared" si="0"/>
        <v>6</v>
      </c>

Excel only uses the shared formula optimization for C3 because the application is aware of what actions were performed (in other words, "path-dependent"). Since the formulae in column B were separately assigned, Excel is not aware of the shared expression.

.

For the XLSX writer (and eventually XLSB / XLS) to use shared formulae, the similar formulae would have to be determined by comparing against shifted equivalents.

This operation can be made more performant by changing the formula representation (using RC-style formulae instead of A1-style) but that would be a breaking change and most users are not familiar with RC-style.

.

It is possible to emulate Excel's behavior by introducing a special cell flag to note the parent cell of a shared formula expression, but we'd need to work out some details. For example, do you break the entire shared formula if the parent cell formula changes?

The difference between the Excel case and the SheetJS case is that Excel uses contextual knowledge that SheetJS does not have. The way to create a shared formula in Excel is to fill down. Suppose you set `B1=A1*2`, `B2=A2*2`, `B3=A3*3` manually. Then set `C1=A1*2` and fill down to C3. That file is attached (Issue2938.xlsx) B3 is stored as a normal formula and C3 is stored as a shared formula: ```xml <c r="B3"> <f>A3*2</f> <v>6</v> </c> <c r="C3"> <f t="shared" si="0"/> <v>6</v> </c> ``` Excel only uses the shared formula optimization for C3 because the application is aware of what actions were performed (in other words, "path-dependent"). Since the formulae in column B were separately assigned, Excel is not aware of the shared expression. . For the XLSX writer (and eventually XLSB / XLS) to use shared formulae, the similar formulae would have to be determined by comparing against shifted equivalents. This operation can be made more performant by changing the formula representation (using RC-style formulae instead of A1-style) but that would be a breaking change and most users are not familiar with RC-style. . It is possible to emulate Excel's behavior by introducing a special cell flag to note the parent cell of a shared formula expression, but we'd need to work out some details. For example, do you break the entire shared formula if the parent cell formula changes?

Issue2938.xlsx

8.3 KiB

Sign in to join this conversation.