Formulae not retained on export #152
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
1 Participants
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: sheetjs/sheetjs#152
Loading…
Reference in New Issue
Block a user
No description provided.
Delete Branch "%!s()"
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?
When a worksheet has cells with valid
.f
properties (formula), the value is not retained during exporting to XLSX format via:I need to support such functionality for the modification of templated workbooks. For example, the template has a cell, Q16 with the formula
=IF(ISERROR((N16*M16*R16)/101.8),0,((N16*M16*R16)/101.8))
. When output via the above code, the value "0" is produced instead.No formula information is retained, and the cell is a "General" cell of value "0". Is there a workaround or solution to this?
@florf
Sorry for the bump, but I just wanted to know if you ever found a solution to this problem. I have cells with valid .f properties that won't write, rather they just show up as empty boxes. If you found a work-around or solution, I'd really appreciate it if you could share.
Thanks!
Bumping this issue.
According to the docs: "The raw data is the only thing guaranteed to be saved. Formulae, formatting, and other niceties may not be serialized (pending CSF standardization)"
Is there any word on progress for this topic? Has anyone found a workaround?
The formula situation is not as simple as it seems. In general, when considering any feature, we should always try to understand the full scope of the problem first.
A1-style versus RC-style references
Most people are familiar with the A-1 style, but the RC style has a few cool benefits. Let's say you want to keep a running total in a separate column. One common approach is to write formulae like
B2=A2+B1, B3=A3+B2, ...
. Now you are forced to write different things in different cells. In the RC form, they are all the same formula:=RC[-1]+R[-1]C
. This gives rise to the so-called "shared formula" storage (see [MS-XLS] 2.4.260 ShrFmla)Array formula
Unlike shared formulae, array formulae change the formula semantics and oftentimes apply to ranges of cells. In a context where a scalar is expected (e.g. COS), non-array formulae will implicitly slice a column to find the corresponding cell.
For example, consider a worksheet with
A1=1, A2=2, A3=3, A4=4, A5=5
. Thanks to the intersection rule, setting C2 to the formula=SUM(A1:A5*A1:A5)
will give you 4 (A2*A2=4
) and setting B4 to that formula will give you 16 (A4*A4 = 16
). If you enter the formula and hit control-shift-enter, you will see the formula as{=SUM(A1:A5*A1:A5)}
and the value is 55.File representation
This process would be much easier if there were one representation shared by the various Excel formats. That is not the case:
Fortunately, the test suite has files in various formats so we can compare the representations.
Requirements
A "standardized solution" consists of:
A) A standard formula representation for simple single-cell formula
This boils down to deciding between A1-style and RC-style formula references.
B) A standard formula representation for multi-cell array formulae
This is not as straightforward since it requires some sort of worksheet-level storage. Slicing the formula is problematic as it loses information that you will need to perform certain operations.
C) A set of utility functions for converting back and forth between various representations
@tinotruppel and others have looked into this problem. The formula stress test has some fun corner cases to consider. Note that once this is done, actually writing the formulae in XLSX is super simple.
D) A set of utility functions for linting worksheets (for example, verifying that a simple formula does not intersect an array formula)
This is pretty straightforward once we decide on the representation.
@SheetJSDev Thank you for the background info. Is there a forum for discussing the representation options/timeline for making the decision?
@SheetJSDev @tinotruppel Since the .xlsx format seems to be the simplest to implement, I vote adding that functionality first. js-xlsx is the name of the library after all. I think most developers would understand if other formats aren't supported right away.
Can we specify which representation to use in the options object passed to the XLSX.write method? It looks like line 350 of https://github.com/SheetJS/js-xlsx/blob/master/bits/68_wsbin.js is what I'm looking for to do the job but I'm not sure. Please let me know if I can help code for the repo or if there is something I can add in a personal fork to get basic string formulae writing out.
Thank you!
@ryangallen @SheetJSDev A workaround for writing functions to xslx would be great! Should be the big deal with your knowledge, it would be very kind of you, if you could give us some hint.
Thank you!
@florf @aravindkoneru @ryangallen if, like me, you still need this feature, I have a patch that works for me:
d43964564d
@mcsf Thanks for the patch! It works great for me.
@mcsf thanks, it works!
@mcsf would the below formula work with the changes you made?
I was looking through the code it formats the above like so
Not sure what the format should be for the xml or where the documentation on that might be.
When I create excel document and save it as xml I get
7820864.7891291287
Again not certain what format it should be.
Hey @mdumouchel — I'm not sure what the question is. Special characters have to be escaped with predefined XML entities whenever any bits are added to the document.
As for the output discrepancies between xlsx and Excel, you'll notice that Excel is preferring RC-style references over A1-style. As to why Excel is seemingly not escaping things like ampersands, I have no answer.
The important question is really whether
<f>=SUMIF(B2:B2, ">="&B2,D2:D2)</f>
actually works for you once you open your document with Libre/Excel.After reviewing it again and playing around with formatting found it was error in !ref and the cell was out of range. Since that was only thing I wasn't seeing I thought had something to do with the formula.
Thanks for the fast reply and the patch!
Thanks for the patch @mcsf 👍
Has this made it back into the repo? Is still the plan to get this back into the main repo is this forever forked?
Could someone merge this change back into the main branch since it's required to save formulas?
any update on when formulas will be supported or at least the xlsx patch from @mcsf will be merged?
We finally settled on a convention: for non-array formulae just setting the
f
key will export the formula. For array formulae, set the topleft corner cell'sF
key to the array formula range. This works for range as well as point array formulae.We updated the write test with an example