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

Open
opened 2019-12-19 23:02:37 +00:00 by doms3 · 2 comments
doms3 commented 2019-12-19 23:02:37 +00:00 (Migrated from github.com)

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.

7ef3f3e531/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.

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 2019-12-20 21:47:04 +00:00 (Migrated from github.com)

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.

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 2021-06-10 10:56:21 +00:00 (Migrated from github.com)

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.

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.
Sign in to join this conversation.
No Milestone
No Assignees
1 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: sheetjs/sheetjs#1707
No description provided.