Serialize formula if present #112

2014-09-13T12:01:05Z

johnyesberg commented

2014-09-13 12:01:05 +00:00

(Migrated from github.com)

It seems to be important that the .. comes before the ..

This seems to work in a round-trip on a couple of formulae I tried.

It seems to be important that the <f>..</f> comes before the <v>..</v> This seems to work in a round-trip on a couple of formulae I tried.

coveralls commented

2014-09-13 12:09:07 +00:00

(Migrated from github.com)

Coverage increased (+0.0%) when pulling fffc958a9e on johnyesberg:master into 19f3a6ae76 on SheetJS:master.

[![Coverage Status](https://coveralls.io/builds/1205346/badge)](https://coveralls.io/builds/1205346) Coverage increased (+0.0%) when pulling **fffc958a9e495b6afa3842a20639977d6f61c833 on johnyesberg:master** into **19f3a6ae762640ee22d36710878f44e64afca884 on SheetJS:master**.

SheetJSDev commented

2014-09-13 14:46:51 +00:00

(Migrated from github.com)

What happens if the formula is set but the value is not (as is commonly the case with third-party writers)?

See the first part of the patch in https://github.com/SheetJS/js-xlsx/pull/103 (h/t @christocracy ).

What happens if the formula is set but the value is not (as is commonly the case with third-party writers)? See the first part of the patch in https://github.com/SheetJS/js-xlsx/pull/103 (h/t @christocracy ).

johnyesberg commented

2014-09-14 03:21:17 +00:00

(Migrated from github.com)

Excel 2010 copes without a value. It seems to calculate the value when the workbook is opened, which is nice.

Some notes related to formulae and values:

If you edit a .xlsx outside Excel and you change one of the sheet.xml files to remove a formula and just leave a value, then it's important to remove the /xl/calcChain.xml file (or even just the entry within it) - otherwise it will probably refer to a formula which no longer exists. Excel will then complain when opening the workbook.
I found it hard to convince Excel to recalculate. For example, consider a cell with a formula "2+3", and a value "1". Even choosing Recalculate from the menu doesn't cause Excel to evaluate the formula. I found that I could edit (F2) the cell and press Enter, and the new value will be calculated, but that doesn't really work for a large number of cells.
I have a formula on Sheet1 "=Sheet2!A1". I plan to modify Sheet2 outside Excel. It was difficult to make Excel re-evaluate the formula on Sheet1 with the new value in Sheet2!A1. The rather ugly workaround that I came up with was to make the formula "=Sheet2!A1+0*RAND()". Excel knows that formulae containing RAND() have to be re-evaluated all the time. (They are stored with <f ca="1">...</f> to signify that CAlculation is required.)

Excel 2010 copes without a value. It seems to calculate the value when the workbook is opened, which is nice. Some notes related to formulae and values: - If you edit a .xlsx outside Excel and you change one of the sheet.xml files to remove a formula and just leave a value, then it's important to remove the /xl/calcChain.xml file (or even just the entry within it) - otherwise it will probably refer to a formula which no longer exists. Excel will then complain when opening the workbook. - I found it hard to convince Excel to recalculate. For example, consider a cell with a formula "2+3", and a value "1". Even choosing Recalculate from the menu doesn't cause Excel to evaluate the formula. I found that I could edit (F2) the cell and press Enter, and the new value will be calculated, but that doesn't really work for a large number of cells. - I have a formula on Sheet1 "=Sheet2!A1". I plan to modify Sheet2 outside Excel. It was difficult to make Excel re-evaluate the formula on Sheet1 with the new value in Sheet2!A1. The rather ugly workaround that I came up with was to make the formula "=Sheet2!A1+0*RAND()". Excel knows that formulae containing RAND() have to be re-evaluated all the time. (They are stored with `<f ca="1">...</f>` to signify that CAlculation is required.)