[Issue] Z column formula incorrect while opening a xml

Discuss the spreadsheet application

[Issue] Z column formula incorrect while opening a xml

Postby krishna_36 » Sat Aug 07, 2010 7:56 pm

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
Attachments
sample_excel.zip
(1.25 KiB) Downloaded 62 times
Last edited by krishna_36 on Sun Aug 08, 2010 7:33 am, edited 5 times in total.
OpenOffice 3.2 on Windows XP
krishna_36
 
Posts: 9
Joined: Sat Aug 07, 2010 7:50 pm

Re: Z column formula in correct while opening a xml in open

Postby squenson » Sat Aug 07, 2010 8:01 pm

Could you upload an xml file as example. It looks like a bug in translating the column reference!
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: Z column formula in correct while opening a xml in open

Postby krishna_36 » Sat Aug 07, 2010 8:03 pm

upload was not allowed. so i copied the code in the post itself
OpenOffice 3.2 on Windows XP
krishna_36
 
Posts: 9
Joined: Sat Aug 07, 2010 7:50 pm

Re: Z column formula in correct while opening a xml in open

Postby acknak » Sat Aug 07, 2010 8:38 pm

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Issue] Z column formula incorrect while opening a xml

Postby krishna_36 » Sun Aug 08, 2010 7:25 am

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.
OpenOffice 3.2 on Windows XP
krishna_36
 
Posts: 9
Joined: Sat Aug 07, 2010 7:50 pm

Re: [Issue] Z column formula incorrect while opening a xml

Postby squenson » Sun Aug 08, 2010 8:01 pm

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...
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: [Issue] Z column formula incorrect while opening a xml

Postby krishna_36 » Mon Aug 09, 2010 8:54 pm

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..
OpenOffice 3.2 on Windows XP
krishna_36
 
Posts: 9
Joined: Sat Aug 07, 2010 7:50 pm

Re: [Issue] Z column formula incorrect while opening a xml

Postby Charlie Young » Mon Aug 09, 2010 9:53 pm

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   Expand viewCollapse view
<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 :knock:

 Edit: The problem still exists in the 3.3 build 
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: [Issue] Z column formula incorrect while opening a xml

Postby acknak » Mon Aug 09, 2010 11:56 pm

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Issue] Z column formula incorrect while opening a xml

Postby Alex1 » Fri Aug 13, 2010 11:39 pm

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.
AOO 4.1.7 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 608
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands

Re: [Issue] Z column formula incorrect while opening a xml

Postby Charlie Young » Sat Aug 14, 2010 12:38 am

Alex1 wrote: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.
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
 
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: [Issue] Z column formula incorrect while opening a xml

Postby squenson » Sat Aug 14, 2010 3:40 am

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.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: [Issue] Z column formula incorrect while opening a xml

Postby krishna_36 » Sat Aug 14, 2010 8:20 pm

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 ?
OpenOffice 3.2 on Windows XP
krishna_36
 
Posts: 9
Joined: Sat Aug 07, 2010 7:50 pm

Re: [Issue] Z column formula incorrect while opening a xml

Postby Alex1 » Sun Jan 30, 2011 1:10 pm

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 ?
AOO 4.1.7 & LO 5.0.6 on Windows XP & 7
Alex1
Volunteer
 
Posts: 608
Joined: Fri Feb 26, 2010 1:00 pm
Location: Netherlands


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests