[Issue] Z column formula incorrect while opening XML from Excel

[krishna_36]

I am trying to open a xml file in open office spreadsheet which has a some columns of data and a sum of each columns... formula is in built in using xml , while everything is fine.. for the columns with 'Z' like Z, AZ ... the formula is not adding the z column sum, it is adding the sum of A. Like 'Z' column adds A column sum, AZ column adds 'B' column. This happens for z column only that too when a xml is opened .

below is the xml sample.

create a xml file using that then check out the z column

[squenson]

Could you upload an xml file as example. It looks like a bug in translating the column reference!

[krishna_36]

upload was not allowed. so i copied the code in the post itself

[acknak]

Looks like this one: Issue 81233: spreadsheetml: Trouble with import from XML (format MS Excel 2003) to OpenOffice Calc
You can register there and add your vote (up to two) or comment.

[krishna_36]

The issues is a little different that Issue 81233. In there the open office skipped the z column. But in here the open office is taking the wrong reference. It is taking the A column sum instead of the Z column.

[squenson]

For me, it looks the same. Here is a quote from this issue:

If I open its by MS Excel, I see that formulas have absolute positions: Y5 and Z5 - It’s correct. If I open its by OpenOffice, I see that formulas have absolute positions: Y5
and A5 - It’s WRONG! (OpenOffie skipped column Z when translating relative position to absolute position).

It's already three year old, so don't hold your breath until a patch is posted...

[krishna_36]

Thank you all for the reply.

When can we expect a patch for the same.
I checked in 3.2.1 the same issue is also present..

[Charlie Young]

If on looks at the XML import filter file in

C:\Program Files\OpenOffice.org 3\Basis\share\xslt\import\spreadsheetml\spreadsheetml2ooo.xsl

One finds, starting at line 8226:

Code: Select all

<xsl:template name="translate-unit">
		<!-- convert cell position expression unit, R1C1, R3, C4 -->
		<xsl:param name="column-number"/>
		<xsl:param name="row-number"/>
		<xsl:param name="column-pos-style"/>
		<xsl:param name="row-pos-style"/>
		<xsl:variable name="column-number1">
			<xsl:value-of select="floor( $column-number div 26 )"/>
		</xsl:variable>
		<xsl:variable name="column-number2">
			<xsl:value-of select="$column-number mod 26"/>
		</xsl:variable>
		<xsl:variable name="column-character1">
			<xsl:call-template name="number-to-character">
				<xsl:with-param name="number" select="$column-number1"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:variable name="column-character2">
			<xsl:call-template name="number-to-character">
				<xsl:with-param name="number" select="$column-number2"/>
			</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:when>
			<xsl:otherwise>
				<xsl:value-of select="concat( $column-character1, $column-character2)"/>
			</xsl:otherwise>
		</xsl:choose>
		<xsl:choose>
			<xsl:when test="$row-pos-style ='absolute'">
				<xsl:value-of select="concat( '$', $row-number)"/>
			</xsl:when>
			<xsl:otherwise>
				<xsl:value-of select="$row-number"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>
	<xsl:template name="number-to-character">
		<xsl:param name="number"/>
		<xsl:choose>
			<xsl:when test="$number = 0"/>
			<xsl:when test="$number = 1">A</xsl:when>
			<xsl:when test="$number = 2">B</xsl:when>
			<xsl:when test="$number = 3">C</xsl:when>
			<xsl:when test="$number = 4">D</xsl:when>
			<xsl:when test="$number = 5">E</xsl:when>
			<xsl:when test="$number = 6">F</xsl:when>
			<xsl:when test="$number = 7">G</xsl:when>
			<xsl:when test="$number = 8">H</xsl:when>
			<xsl:when test="$number = 9">I</xsl:when>
			<xsl:when test="$number = 10">J</xsl:when>
			<xsl:when test="$number = 11">K</xsl:when>
			<xsl:when test="$number = 12">L</xsl:when>
			<xsl:when test="$number = 13">M</xsl:when>
			<xsl:when test="$number = 14">N</xsl:when>
			<xsl:when test="$number = 15">O</xsl:when>
			<xsl:when test="$number = 16">P</xsl:when>
			<xsl:when test="$number = 17">Q</xsl:when>
			<xsl:when test="$number = 18">R</xsl:when>
			<xsl:when test="$number = 19">S</xsl:when>
			<xsl:when test="$number = 20">T</xsl:when>
			<xsl:when test="$number = 21">U</xsl:when>
			<xsl:when test="$number = 22">V</xsl:when>
			<xsl:when test="$number = 23">W</xsl:when>
			<xsl:when test="$number = 24">X</xsl:when>
			<xsl:when test="$number = 25">Y</xsl:when>
			<xsl:when test="$number = 26">Z</xsl:when>
			<xsl:otherwise/>
		</xsl:choose>
	</xsl:template>

Note well the line

<xsl:value-of select="$column-number mod 26"/>

which is an assignment to the variable $column-number2

Now, that assignment will produce a number from 0-25, of course, so in the template "number-to-character," Z will never be assigned by

<xsl:when test="$number = 26">Z</xsl:when>

It does not work, however, to just assign Z to 0. If I make that change, and then import the XML into Calc, instead of getting =SUM(A3;A4;A5;A6) in column A, I get =SUM(ZA3;ZA4;ZA5;ZA6).

So while I think I'm closing in on the source of the problem, I haven't figured out how to fix it yet

Edit: The problem still exists in the 3.3 build

[acknak]

I checked in 3.2.1 the same issue is also present... The problem still exists in the 3.3 build

I'm not sure it's worth your time testing releases looking for bug fixes; just look at the issue status.

Any progress toward fixing an issue will be recorded in the issue status and/or comments. If the issue has not reached state=VERIFIED, then no fix is available in any version. If a fix is VERIFIED, then the fix will be included in whatever release is shown as the "Target milestone" field.

You can see one simple case, here: http://qa.openoffice.org/issues/show_bug.cgi?id=113400

The only ways to speed up a fix are:
1) Diagnose the exact cause or situation that triggers the problem; the easier it is to find/fix the problem, the more likely it is to get fixed.
2) Contribute a patch that fixes the problem; however, there's no guarantee that the developers will accept the patch.
3) Take a patch someone else has contributed and apply it to a custom build of OOo on your system.

If a patch exists for an issue, it should be attached there, and the issue should be marked as Type=PATCH.

[Alex1]

In the part of spreadsheetml2ooo.xsl quoted by Charlie I replaced "floor( $column-number div 26 )" by "floor( ($column-number - 1) div 26 )" and "$column-number mod 26" by "($column-number - 1) mod 26 + 1" and now it seems to work like it should.

[Charlie Young]

In the part of spreadsheetml2ooo.xsl quoted by Charlie I replaced "floor( $column-number div 26 )" by "floor( ($column-number - 1) div 26 )" and "$column-number mod 26" by "($column-number - 1) mod 26 + 1" and now it seems to work like it should.

Works for me too, at least on the test file here. I think I had tried the change to the mod alone, but not with the change to the floor.

[squenson]

It should be noted that the current code cannot go above 26^2+26=702 columns as the xml code transforms the column reference on two characters only.

[krishna_36]

Thanks a lot guys..!!

This is a nice solution. The thing is that will this come as patch or in the next release...!

what can i expect ?

[Alex1]

Nothing has changed in version 3.3. Who is supposed to take action now that the solution has been reported on http://qa.openoffice.org/issues/show_bug.cgi?id=81233 ?

[Alex1]

15 years after the first error report someone has tried to fix the bug, but now references to columns 677 to 702 are wrong.
I added a comment to issue 81233 but I don't know if it will be noticed as the issue is tagged as resolved.

[Lupp]

Why are you hijacking this ancient thread, and that without offering an example file for the claimed problems with certain column numbers?

BTW: AOO won't develop features or fix bugs rapidly. In specific if you need to work with XML-based files from MS Office, you should shift to LibreOffice. They did a lot concerning compatibility.

[Hagar Delest]

Why are you hijacking this ancient thread

Because there has been some recent updates in the bug report I guess. And it is not a hijack since Alex1 had posted in the roughly same timeframe than the last posts.
Thus it is quite nice to have an update here. Even if it is old, it may be of some interest for the few who could have subscribed to the topic.

[Lupp]

Well, as I saw it, the last post before "Alex1" posted recently was

Code: Select all

by krishna_36 » Sat, 2010-08-14 20:20

.

Anyway, it's not a hijack, because the topic is clearly unchanged. Sorry.

(In LibO there was explicitly shown an error 522 if this problem occurred. Since V 6.1.2.1 of 2018-09-21 the problem is fixed.)

[Alex1]

I'm writing a new version of the column conversion routine. I don't have an xml example containing absolute references to test with. Can someone upload such an example?

[Lupp]

@Alex1

Code: Select all

I'm writing a new version of the column conversion routine.

I don't see the context clearly. Are you working on the core code of AOO or is this about a routine "for private use"?
You surely know that the core of the issue is an incorrect handling of the base-26-without-zero system, for unsigned integers used for column designators. (For TextTables a base-52 system of the same kind is used.)
If you are interested in the "fundamental aspects" of the case, you can get my 'Star' Basic routines for the purpose, of course.

[Alex1]

The "fix" in version 4.1.14 doesn't make it much better. In fact the method is fundamentally wrong, so a new approach was necessary.

The method is simple: repeatedly divide the column number minus one by 26. The remainder gives the last letter, then continue with the quotient until it equals zero. This suggests using a loop, but I couldn't get it working in xslt. It appeared that none of the 74 .xsl files in OpenOffice contains a loop, and the xslt version used in OO doesn't even support it: https://xalan.apache.org/xalan-j/

Using recursion is an alternative, and gives the correct results. The code below can be used to replace templates translate-unit and number-to-character. It should work for any number of columns.
I didn't have an xml file containing absolute references available for testing, but by reversing the test for absolute references I could verify the result.

Three .xsl files contain the same wrong template converting the column number:
OpenOffice 4\share\xslt\import\spreadsheetml\spreadsheetml2ooo.xsl
OpenOffice 4\share\xslt\import\uof2\uof2odf.xsl
OpenOffice 4\share\xslt\import\uof\uof2odf_spreadsheet.xsl
The template was only changed in spreadsheetml2ooo.xsl in version 4.1.14. The names of the other files suggest that they are used to read unified office format text: https://wiki.openoffice.org/wiki/Docume ... le_formats

LibreOffice contains the template twice but at least for importing xml files it doesn't seem to be used:
LibreOffice\share\xslt\import\uof\uof2odf_spreadsheet.xsl
LibreOffice\share\xslt\import\spreadsheetml\spreadsheetml2ooo.xsl

Code: Select all

	<xsl:template name="translate-unit">
		<!-- convert cell position expression unit, R1C1, R3, C4 -->
		<xsl:param name="column-number"/>
		<xsl:param name="row-number"/>
		<xsl:param name="column-pos-style"/>
		<xsl:param name="row-pos-style"/>

		<xsl:variable name="ColumnLetters">
			<xsl:call-template name="ConvertColumnNumber">
				<xsl:with-param name="ColumnNumber" select="$column-number"/>
			</xsl:call-template>
		</xsl:variable>

		<!-- position styles are 'absolute' or 'relative', -->
		<xsl:choose>
			<xsl:when test="$column-pos-style = 'absolute'">
				<xsl:text>$</xsl:text>
			</xsl:when>
		</xsl:choose>
		<xsl:value-of select="$ColumnLetters"/>

		<xsl:choose>
			<xsl:when test="$row-pos-style ='absolute'">
				<xsl:text>$</xsl:text>
			</xsl:when>
		</xsl:choose>
		<xsl:value-of select="$row-number"/>
	</xsl:template>

	<xsl:template name="ConvertColumnNumber">
		<xsl:param name="ColumnNumber"/>
		<xsl:variable name="letters" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"/>
		<xsl:choose>
			<xsl:when test="$ColumnNumber &lt;= 26">
				<xsl:value-of select="substring($letters, $ColumnNumber, 1)"/>
			</xsl:when>
			<xsl:otherwise>
				<xsl:call-template name="ConvertColumnNumber">
					<xsl:with-param name="ColumnNumber" select="floor(($ColumnNumber - 1) div 26)"/>
				</xsl:call-template>
				<xsl:value-of select="substring($letters, ($ColumnNumber - 1) mod 26 + 1, 1)"/>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:template>