This is an automated email from the ASF dual-hosted git repository. damjan pushed a commit to branch AOO41X in repository https://gitbox.apache.org/repos/asf/openoffice.git
commit 0285e67f3b672422a011fcc082697d769fcf7d81 Author: Damjan Jovanovic <[email protected]> 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 (cherry picked from commit 577fe17932e0dec38662067d1a86e7fd6ae525b6) --- .../import/spreadsheetml/spreadsheetml2ooo.xsl | 25 ++++- .../data/uno/sc/fvt/Bug81233ColumnZReference.xml | 121 +++++++++++++++++++++ 2 files changed, 142 insertions(+), 4 deletions(-) diff --git a/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl b/main/filter/source/xslt/import/spreadsheetml/spreadsheetml2ooo.xsl index 934f0fa662..3f93f105ae 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>
