Formulae not retained on export

When a worksheet has cells with valid .f properties (formula), the value is not retained during exporting to XLSX format via:

var workbook = workbook = Xlsx.readFile('OnDemand.xlsx', { 
    cellFormula: true, 
    cellStyles: true, 
    sheetStubs: true, 
    cellNF: true, 
    bookDeps: true 
});
var out = Xlsx.write( workbook, { bookType: 'xlsx', /*bookSST: true,*/ type: 'binary' });

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?

[aravindkoneru]

@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!

[ryangallen]

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?

[SheetJSDev]

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:

• XLSX stores raw A1-reference formula strings
• XLSB stores a parsed representation (a set of tokens which roughly matches how Excel internally parses formulae)
• XLS stores a slightly different parsed representation
• SpreadsheetML stores the formulas using RC-reference notation -- there's a small regex operation involved in converting from RC to A1
• CSV has some special rules for determining whether a cell encodes an A1-reference formula or if it should be treated as literal data.

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.

[ryangallen]

@SheetJSDev Thank you for the background info. Is there a forum for discussing the representation options/timeline for making the decision?

[ryangallen]

@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!

[cara]

@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!

[mcsf]

@florf @aravindkoneru @ryangallen if, like me, you still need this feature, I have a patch that works for me:

mcsf@d439645

[aravindkoneru]

@mcsf Thanks for the patch! It works great for me.

[alekseyl1992]

@mcsf thanks, it works!

[mdumouchel]

@mcsf would the below formula work with the changes you made?

{ t: 'n', f: '=SUMIF(B2:B2, ">="&B2,D2:D2)' }

I was looking through the code it formats the above like so

<f>=SUMIF(B2:B2, &quot;&gt;=&quot;&amp;B2,D2:D2)</f>

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.

[mcsf]

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.

Again not certain what format it should be.

The important question is really whether <f>=SUMIF(B2:B2, &quot;&gt;=&quot;&amp;B2,D2:D2)</f> actually works for you once you open your document with Libre/Excel.

[mdumouchel]

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!

[AndrewFreemantle]

Thanks for the patch @mcsf 👍