Created attachment 47930 [details]
simple example

Not a P1 Issue lowered to P4

Hi Swante,

seems to be yours.

Frank

Thanks for the issue, rolik.

Nevertheless I really need help on this Excel 2003 XML / spreadsheetml filter,
as there are I am distracted by many other projects and see no possibility to
fix this in the short distance..

Is there any resolution for this issue.

In http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=33060 we 
found a possible solution for OOo version 3.2.1 (OOO320m18, build 9502) by 
applying two changes in
"C:\Program Files\OpenOffice.org 3
\Basis\share\xslt\import\spreadsheetml\spreadsheetml2ooo.xsl"

Replace "floor( $column-number div 26 )" by "floor( ($column-number - 1) div 
26 )"
and "$column-number mod 26" by "($column-number - 1) mod 26 + 1"

With these changes, the problem seems to have disappeared.

Hi That post was raised by me.

The solution is fine and as suggested would work uptill 702 column number only.

would this be taken up in the next release or a next patch ?

The original code has the same limitation to 702 columns. But the MS Excel 2003 
XML format is limited to 256 columns so that should be no problem.

But i did not find that limitation. Used MS office 2007 to open the xml files 
did not find any limitation

Fixed by the below commit, resolving FIXED.

Thank you alex6684 for your brilliant investigation, and thank you everyone for your bug report, sample file, testing and comments :-).


commit 577fe17932e0dec38662067d1a86e7fd6ae525b6
Author: Damjan Jovanovic
Date:   Wed Jan 11 19:47:12 2023 +0200

Our XSLT-based MS Office 2003 SpreadsheetML format import filter, when doing
conversion from R1C1 style column references to our A1 style references, had a
bug where it was treating the column value as 0-based, and dividing by 26 to
find the 1st letter and taking the remainder when divided by 26 for the second
letter. Those numbers are then each converted to a letter [0 = nothing,
1 = "A", 2 = "B", ..., 26 = "Z"].

However since R1C1 is 1-based, and not 0-based, this breaks for column numbers
which are multiples of 26, as 26 mod 26 = 0, so the least significant digit is
converted to nothing while the most significant digit gets incremented too
early.

Fix this by converting the column number to 0-based by subtracting 1 before
calculation, then adding 1 to the least significant digit afterwards.

Also the fact we have 2 letters limited us to a maximum of 26^2 = 676 columns,
after which column references would wrap around. Fix this too, by adding a 3rd
letter, which lets us address a maximum of 17576 columns.

Add a sample file to our unit tests.

Found by: alex dot plantema at xs4all dot nl
Patch by: me

M       main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl
A       test/testuno/data/uno/sc/fvt/Bug81233ColumnZReference.xml
M       test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java

*** Issue 90108 has been marked as a duplicate of this issue. ***

*** Issue 86486 has been marked as a duplicate of this issue. ***

Cherry-picking for AOO41X in commit 0285e67f3b672422a011fcc082697d769fcf7d81.

Has this already been cherry-picked for AOO42X?

(In reply to Matthias Seidel from comment #14)
> Has this already been cherry-picked for AOO42X?

No it hasn't.

What is meant to be process for using "Target Milestone" vs "Flags" vs the "release_blocker" Keyword?

As I understand it the "Target Milestone" is the version for which the issue will be closed.

A "release blocker" is an issue that blocks the release of a certain version if not fixed.

Normally, I would cherry-pick for AOO42X and then if it fits to AOO41X, since that is the version we will release next.

(In reply to Matthias Seidel from comment #16)
> As I understand it the "Target Milestone" is the version for which the issue
> will be closed.
> 
> A "release blocker" is an issue that blocks the release of a certain version
> if not fixed.
> 
> Normally, I would cherry-pick for AOO42X and then if it fits to AOO41X,
> since that is the version we will release next.

Alright thank you.

And we track which branches the commit has been cherry-picked to by means of comments like "Cherry-picked for <branch> with <commit/URL>"?

At least that's the way I do it.

(In reply to damjan from comment #17)
> And we track which branches the commit has been cherry-picked to by means of
> comments like "Cherry-picked for <branch> with <commit/URL>"?

Back then, when we used Subversion, we had a mechanism, that did this automatically when you mentioned the issue number in your commit.

An addition:

You can set the flag "release_blocker" with a "?" as value when you think that the fix is OK  to be part of the respective release, e.g., when the changed code is not much like a few lines and / or it's easy to test.

However, at the end the release manager decides if so.

So this is fixed in AOO41X but not in AOO42X.

We must not forget to cherry-pick it.

For the moment, I will set it as "Target Milestone" 4.1.14.

Cherry-picked for AOO42X with 0f570a5492b9ec8bf48d122b191318403f88eed0.

In the new version references to columns 677 to 702 don't work.
What's the purpose of applying FLOOR to the result of a MOD operation?
Please delete my e-mail address from comment 10.

Here's a way to obtain the column characters, not limited to 3, in pseudocode:

columncharacters := "";
while columnnumber > 0 do
  columncharacters := concat (chr ((columnnumber - 1) mod 26) + 65; columncharacters);
  columnnumber := floor ((columnnumber - 1) div 26)
end;

(Then add $ for absolute references.)

(In reply to alex6684 from comment #23)

Here's the correct version:

columncharacters := "";
while columnnumber > 0 do
  columncharacters := concat (chr ((columnnumber - 1) mod 26 + 65); columncharacters);
  columnnumber := floor ((columnnumber - 1) div 26)
end;