search

audreyt / ethercalc

Node.js port of Multi-user SocialCalc
https://ethercalc.net
Other
2.97k stars 538 forks source link

export to ods or excel creates error in sum formula #717

Open RdR1024 opened 4 years ago

RdR1024 commented 4 years ago

Export to ods or excel format creates an error in a sum formula that uses a cell range. It seems the colon in a cell range gets replaced with \c To reproduce: in cells A1, A2 and A3 enter the values 1, 2 and 3, respectively. In cell A4 enter the formula =sum(A1:A3) Then click the export button and choose the ODS format (or Excel). Save the file and then open the file with Excel, or Gnumeric, or another spreadsheet application that can read ODS. You'll get an error message. To see what's happening, unzip the ODS file and inspect the xml contents -- it seems that the formula isn't saved correctly.

eddyparkinson commented 4 years ago

Agree - it is a bug https://ethercalc.org/export_range

Files needed to fix

Related to https://github.com/SheetJS these files control import and export. The convert from/to social calc format

Maybe in https://github.com/SheetJS/j something is wrong with support for social calc. see things like "to_socialcalc"

Edit: the file was - https://github.com/SheetJS/js-harb but changes have been merged into js-xlsx.

related changes

https://github.com/quilt-js/js-harb/commit/5d25d3bbbfa3333eaaf34c74b7f89ab869873efe#diff-714ff6788ac70479b4f2b0514425f0b3

RdR1024 commented 4 years ago

@eddyparkinson thanks for looking into this. I'm wondering if it is SheetJS or some code that calls SheetJS. The reason I ask, is that when I try converting a spreadsheet with sheetjs (js-xlsx) it seems to preserve the formula. For example, using the node version: 

./xlsx.njs -o test1.ods --ods test1.xlsx

The resulting .ods spreadsheet has the correct formula. However, when you try that with ethercalc (by dropping the .xlsx file and then saving the .ods file), the formula gets corrupted.

That makes me think it is not the sheetjs library, but some other code that gets the internal socialcalc datastructure and maps it to sheetjs.

eddyparkinson commented 4 years ago

Maybe, I am not sure what is adding the \c ... wants a debugger.

From memory most of the code that converts between social calc (ethercalc) format and ODS/xlsx was in js-harb.

I expect I did have formulas exporting correctly at some point, as I changed js-harb so it exported them. But at that stage https://github.com/SheetJS people were not active and so did not merge in pull requests. This has changed, and they are merging in.