#
Lack of shared formula creation support #2938

**Labels**

No Label

DBF

Dates

Defined Names

Features

Formula

HTML

Images

Infrastructure

Integration

International

ODS

Operations

Performance

PivotTables

Pro

Protection

Read Bug

SSF

SYLK

Style

Write Bug

good first issue

No Milestone

No Assignees

**2 Participants**

**Notifications**

**Due Date**

No due date set.

**Dependencies**

No dependencies set.

Reference: sheetjs/sheetjs#2938

Loading…

Reference in New Issue

No description provided.

Delete Branch "%!s(<nil>)"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?

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:

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

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.

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:

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