You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openoffice.apache.org by da...@apache.org on 2023/01/11 18:25:21 UTC
[openoffice] branch trunk updated: 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"].
This is an automated email from the ASF dual-hosted git repository.
damjan pushed a commit to branch trunk
in repository https://gitbox.apache.org/repos/asf/openoffice.git
The following commit(s) were added to refs/heads/trunk by this push:
new 577fe17932 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"].
577fe17932 is described below
commit 577fe17932e0dec38662067d1a86e7fd6ae525b6
Author: Damjan Jovanovic <da...@apache.org>
AuthorDate: 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
---
.../import/spreadsheetml/spreadsheetml2ooo.xsl | 25 ++++-
.../data/uno/sc/fvt/Bug81233ColumnZReference.xml | 121 +++++++++++++++++++++
.../source/fvt/uno/sc/formula/TestFormulaDocs.java | 3 +-
3 files changed, 144 insertions(+), 5 deletions(-)
diff --git a/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl b/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl
index e6e19d0900..33b6317c2b 100644
--- a/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl
+++ b/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl
@@ -8225,11 +8225,23 @@
<xsl:param name="row-number"/>
<xsl:param name="column-pos-style"/>
<xsl:param name="row-pos-style"/>
+ <xsl:variable name="zero-based-column-number">
+ <xsl:value-of select="$column-number - 1"/>
+ </xsl:variable>
<xsl:variable name="column-number1">
- <xsl:value-of select="floor( $column-number div 26 )"/>
+ <xsl:value-of select="floor( $zero-based-column-number div 676 )"/>
+ </xsl:variable>
+ <xsl:variable name="column-remainder1">
+ <xsl:value-of select="floor( $zero-based-column-number mod 676 )"/>
</xsl:variable>
<xsl:variable name="column-number2">
- <xsl:value-of select="$column-number mod 26"/>
+ <xsl:value-of select="floor( $column-remainder1 div 26 )"/>
+ </xsl:variable>
+ <xsl:variable name="column-remainder2">
+ <xsl:value-of select="floor( $column-remainder1 mod 26 )"/>
+ </xsl:variable>
+ <xsl:variable name="column-number3">
+ <xsl:value-of select="( $column-remainder2 mod 26 ) + 1"/>
</xsl:variable>
<xsl:variable name="column-character1">
<xsl:call-template name="number-to-character">
@@ -8241,13 +8253,18 @@
<xsl:with-param name="number" select="$column-number2"/>
</xsl:call-template>
</xsl:variable>
+ <xsl:variable name="column-character3">
+ <xsl:call-template name="number-to-character">
+ <xsl:with-param name="number" select="$column-number3"/>
+ </xsl:call-template>
+ </xsl:variable>
<!-- position styles are 'absolute' or 'relative', -->
<xsl:choose>
<xsl:when test="$column-pos-style = 'absolute'">
- <xsl:value-of select="concat( '$', $column-character1, $column-character2)"/>
+ <xsl:value-of select="concat( '$', $column-character1, $column-character2, $column-character3)"/>
</xsl:when>
<xsl:otherwise>
- <xsl:value-of select="concat( $column-character1, $column-character2)"/>
+ <xsl:value-of select="concat( $column-character1, $column-character2, $column-character3)"/>
</xsl:otherwise>
</xsl:choose>
<xsl:choose>
diff --git a/test/testuno/data/uno/sc/fvt/Bug81233ColumnZReference.xml b/test/testuno/data/uno/sc/fvt/Bug81233ColumnZReference.xml
new file mode 100644
index 0000000000..f37f9da884
--- /dev/null
+++ b/test/testuno/data/uno/sc/fvt/Bug81233ColumnZReference.xml
@@ -0,0 +1,121 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<?mso-application progid="Excel.Sheet"?>
+<Workbook xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel">
+ <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
+ <Colors>
+ <Color>
+ <Index>3</Index>
+ <RGB>#c0c0c0</RGB>
+ </Color>
+ </Colors>
+ </OfficeDocumentSettings>
+ <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
+ <WindowHeight>9000</WindowHeight>
+ <WindowWidth>13860</WindowWidth>
+ <WindowTopX>240</WindowTopX>
+ <WindowTopY>75</WindowTopY>
+ <ProtectStructure>False</ProtectStructure>
+ <ProtectWindows>False</ProtectWindows>
+ </ExcelWorkbook>
+ <Styles>
+ <Style ss:ID="Default" ss:Name="Default"/>
+ <Style ss:ID="Result" ss:Name="Result">
+ <Font ss:Bold="1" ss:Italic="1" ss:Underline="Single"/>
+ </Style>
+ <Style ss:ID="Result2" ss:Name="Result2">
+ <Font ss:Bold="1" ss:Italic="1" ss:Underline="Single"/>
+ </Style>
+ <Style ss:ID="Heading" ss:Name="Heading">
+ <Alignment ss:Horizontal="Center"/>
+ <Font ss:Bold="1" ss:Italic="1" ss:Size="16"/>
+ </Style>
+ <Style ss:ID="Heading1" ss:Name="Heading1">
+ <Alignment ss:Horizontal="Center" ss:Rotate="90"/>
+ <Font ss:Bold="1" ss:Italic="1" ss:Size="16"/>
+ </Style>
+ <Style ss:ID="co1"/>
+ <Style ss:ID="co2"/>
+ <Style ss:ID="ta1"/>
+ <Style ss:ID="ce1"/>
+ <Style ss:ID="T1">
+ <Font ss:VerticalAlign="Subscript"/>
+ </Style>
+ </Styles>
+ <ss:Worksheet ss:Name="Sheet1">
+ <Table ss:StyleID="ta1">
+ <Column ss:Width="218.4408"/>
+ <Column ss:Span="1022" ss:Width="64.26"/>
+ <Row ss:Height="12.1032">
+ <Cell ss:StyleID="ce1">
+ <Data ss:Type="String">TestID</Data>
+ </Cell>
+ <Cell ss:StyleID="ce1">
+ <Data ss:Type="String">TestOK</Data>
+ </Cell>
+ <Cell ss:Index="10">
+ <Data ss:Type="String">wrapped!</Data>
+ </Cell>
+ <Cell ss:Index="25">
+ <Data ss:Type="String">Y test</Data>
+ </Cell>
+ <Cell>
+ <Data ss:Type="String">Z test</Data>
+ </Cell>
+ <Cell>
+ <Data ss:Type="String">AA test</Data>
+ </Cell>
+ <Cell ss:Index="1024">
+ <Data ss:Type="String">AMJ test</Data>
+ </Cell>
+ </Row>
+ <Row ss:Height="12.1032">
+ <Cell>
+ <Data ss:Type="String">Y2=Y1?</Data>
+ </Cell>
+ <Cell ss:Formula="=R2C25=R1C25">
+ <Data ss:Type="Boolean">1</Data>
+ </Cell>
+ <Cell ss:Index="25" ss:Formula="=R[-1]C">
+ <Data ss:Type="String">Y test</Data>
+ </Cell>
+ <Cell ss:Formula="=R[-1]C">
+ <Data ss:Type="String">Z test</Data>
+ </Cell>
+ <Cell ss:Formula="=R[-1]C">
+ <Data ss:Type="String">AA test</Data>
+ </Cell>
+ <Cell ss:Index="1024" ss:Formula="=R[-1]C">
+ <Data ss:Type="String">AMJ test</Data>
+ </Cell>
+ </Row>
+ <Row ss:Height="13.4064">
+ <Cell>
+ <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">Z2=Z1? (26th column.)</ss:Data>
+ </Cell>
+ <Cell ss:Formula="=R2C26=R1C26">
+ <Data ss:Type="Boolean">1</Data>
+ </Cell>
+ <Cell ss:Index="1024"/>
+ </Row>
+ <Row ss:Height="12.1032">
+ <Cell>
+ <Data ss:Type="String">AA2=AA1?</Data>
+ </Cell>
+ <Cell ss:Formula="=R2C27=R1C27">
+ <Data ss:Type="Boolean">1</Data>
+ </Cell>
+ <Cell ss:Index="1024"/>
+ </Row>
+ <Row ss:Height="13.4064">
+ <Cell>
+ <ss:Data xmlns="http://www.w3.org/TR/REC-html40" ss:Type="String">AMJ2=AMJ1? (1024th column.)</ss:Data>
+ </Cell>
+ <Cell ss:Formula="=R2C1024=R1C1024">
+ <Data ss:Type="Boolean">1</Data>
+ </Cell>
+ <Cell ss:Index="1024"/>
+ </Row>
+ </Table>
+ <x:WorksheetOptions/>
+ </ss:Worksheet>
+</Workbook>
diff --git a/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java b/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java
index dea8ec98a3..c8d2b7ad0e 100644
--- a/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java
+++ b/test/testuno/source/fvt/uno/sc/formula/TestFormulaDocs.java
@@ -73,7 +73,8 @@ public class TestFormulaDocs {
{"uno/sc/fvt/StarBasicTab.ods", "Basic Tab Function Test"},
{"uno/sc/fvt/DGET on formulas.ods", "DGET on formulas Test"},
{"uno/sc/fvt/Basic Line as variable and Line Input.ods", "Basic Line as variable and Line Input Test"},
- {"uno/sc/fvt/comment-in-single-line-if-then-else.ods", "Basic comment after single line if statement Test"}
+ {"uno/sc/fvt/comment-in-single-line-if-then-else.ods", "Basic comment after single line if statement Test"},
+ {"uno/sc/fvt/Bug81233ColumnZReference.xml", "Bug 81233 column Z reference wrongly converts to column A"}
});
}