search

SheetJS / sheetjs

📗 SheetJS Spreadsheet Data Toolkit -- New home https://git.sheetjs.com/SheetJS/sheetjs
https://sheetjs.com/
Apache License 2.0
35.13k stars 8k forks source link

Column ranges not converted from A1 to RC when exporting in xlml format #1707

Open doms3 opened 4 years ago

doms3 commented 4 years ago

When exporting to the .xlml format, formulas have their cell references in A1 format converted into RC format (i.e. $A$1 becomes R1C1) using the a1_to_rc function.

https://github.com/SheetJS/sheetjs/blob/7ef3f3e531c63fd54939587d52980695c4ec8481/bits/61_fcommon.js#L26-L37

The function correctly turns references with a letter followed by a number into RC format but does not convert column ranges. As a result the exported sheet either does not have these formulas or they are corrupted.

For example, SUM($A:$A) should become SUM(C1) and SUM($A:$C) should become SUM(C1:C3).

Similarly, SUM($1:$3) should become SUM(R1:R3) in the exported .xlml format.

doms3 commented 4 years ago

Also noticing now that it will change A1 references in strings into RC format so ="$A$1" gets changed to ="R1C1" which shouldn't happen.

tyler-gloski commented 3 years ago

Bumping this issue as I'm also seeing the behavior with column ranges. a1_to_rc("SUM($A:$A)", {r:R, c:C}) yields "SUM($A:$A)", so it looks like this may be a case that's simply not handled yet.