You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@openoffice.apache.org by bu...@apache.org on 2012/05/30 08:23:37 UTC

[Bug 119565] New: [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

https://issues.apache.org/ooo/show_bug.cgi?id=119565

          Priority: P3
            Bug ID: 119565
          Assignee: ooo-issues@incubator.apache.org
           Summary: [From Symphony] The SUM formula result changed
                    incorrectly if save the .xls doc to .ods one.
          Severity: normal
        Issue Type: DEFECT
    Classification: Application
                OS: All
          Reporter: binbjguo@gmail.com
          Hardware: PC
            Status: UNCONFIRMED
           Version: OOo 3.4 Beta
         Component: code
           Product: spreadsheet

Created attachment 77784
  --> https://issues.apache.org/ooo/attachment.cgi?id=77784&action=edit
sample file can repro this bug

Steps:
1.Launch AOO3.4, open the attached .xls sample file.
2.Save it as a .ods document.
3.Reopen the saved .ods doc, check the formula results in A32008, A32009.

Defect: 
The formula result changed to "#NAME?", actually, it should be "3676".

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

ooo@erack.de changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |ooo@erack.de

--- Comment #9 from ooo@erack.de ---
(In reply to comment #8)

There seems to be some confusion about the union (or range sequence)
operator and named expressions I'd like to clarify.

Note: I'll use the ';' semicolon parameter separator for clarity, in
      Excel that may be different depending on locale, e.g. ',' comma
      instead.

* in ODF the union operator is defined as '~' tilde
* OOo, AOOi and LibO use '~' in the UI as well
* in Excel, a union is entered as (range1;range2) including the
  parentheses, without parentheses it is not a union
* the Excel form is also accepted by AOOi,... for usability, only that
  it is transformed to (range1~range2)
* old OOo accepted (range1;range2) only (AFAIR) for INDEX() function and
  treated it specially there, the plain range1;range2 in named expressions
  passed to functions may have worked by accident
* the SUM() function is a bit unlucky example because SUM(range1;range2) and
  SUM((range1;range2)) and SUM(range1~range2) all yield identical results due
  to the SUM() function accepting one or more range parameters anyway, which
  may have led to the assumption that in Excel the union operator would be
  a simple ';' semicolon
* the difference can be seen for example with INDEX((range1;range2);1;1;2)
  where if the parentheses are omitted the function returns an error because
  it would get 5 parameters passed instead of 4
* named expressions (AKA defined names) are exactly that, they must define an
  expression that can be compiled
  * range1;range2 is NOT an expression
    * in fact you can't enter that as a named expression, you'd get an error
      and you also get an error in the imported document if you open the names
      dialog, select that definition and click the Modify button
  * range1~range2 or (range1;range2) would be valid expressions
* in the imported Excel document the faulty named expression works
  nevertheless because it is imported as already compiled, as soon as the
  expression would need to be recompiled it would fail, which is the reason
  when saved/reloaded as .ods it fails

As a solution the Excel import needs to handle that and in named
expressions generate a proper union from what looks like a list of
ranges instead of passing these as is.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

Du Jing <bj...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
             Status|UNCONFIRMED                 |CONFIRMED
                 CC|                            |bjdujing@gmail.com
     Ever confirmed|0                           |1

--- Comment #7 from Du Jing <bj...@gmail.com> ---
confirmed,change its status

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

--- Comment #4 from lijianyuan1983@gmail.com ---
(In reply to comment #3)
> Please have a look at issue 25769. It seems, that it describes the same
> problem.
> 
> (In reply to comment #2)
> > Chart series formula still uses ";" as union operator.
> 
> The accepted syntax in the fields in the chart data dialog is different from
> this problem. When improving the Calc DataProvider the issue 64604 should be
> considered too.
> 
> Please do not confuse the union operator with a sequence of ranges.

Issue 25769 and 64604 focused on core function. Here my focus is MS Office 2003
filter. Below is my root cause analysis and resolution. Please take a look.
Root cause:
Per ODF version 1.2, union operator has been changed from ";" to "~". But
current AOO 2003 import filter for formula still uses ";". This defect caused
by MS Office 2003 import. For the "SUM" function in attachment, it seems that
the formula is imported correctly in the first step but actually it is not.
Because for "SUM" function, the same result will be got by SUM(A1:A2;A3:A4) and
SUM(A1:A2~A3:A4). The first one is two parameters. The second one is one
parameter with union.
Resolution:
For range name, cell formula and array formula, use "~" for union when import.
For chart series formula, use ";" for union when import. From filter point,
chart series formula is the same as other formulas. Here is a chart series
formula in MS Office Excel 2003
"=SERIES(,(Sheet1!$A$3:$B$3,Sheet1!$D$3:$E$3),(Sheet1!$A$4:$B$4,Sheet1!$D$4:$E$4),1)".
"Sheet1!$A$3:$B$3,Sheet1!$D$3:$E$3" is a union reference.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

--- Comment #2 from lijianyuan1983@gmail.com ---
Chart series formula still uses ";" as union operator.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

--- Comment #6 from lijianyuan1983@gmail.com ---
(In reply to comment #5)
> Created attachment 78077 [details]
> named sequence of ranges in document from OOo2.4.3
> 
> I do not think, that it is a problem of Excel filter. If you want to name a
> sequence of ranges, that is not possible in AOO3.4. It has been possible in
> OOo2.4.3 and is still possible in LO3.5. I have not searched yet, when this
> changed for OpenOffice.
> 
> You get the same problem, when you open a spreadsheet, which was created
> with OOo2.4.3 or with LO3.5. Therefore a fix should not be in the Excel
> filter, but in the core for insert names.

Now I think your solution is more reasonable. Thanks.
Just keep excel filter code. Handle the range name union operator in core
function. And this fix has nothing to do with chart sequence of ranges.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

Regina Henschel <rb...@t-online.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |rb.henschel@t-online.de

--- Comment #3 from Regina Henschel <rb...@t-online.de> ---
Please have a look at issue 25769. It seems, that it describes the same
problem.

(In reply to comment #2)
> Chart series formula still uses ";" as union operator.

The accepted syntax in the fields in the chart data dialog is different from
this problem. When improving the Calc DataProvider the issue 64604 should be
considered too.

Please do not confuse the union operator with a sequence of ranges.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

--- Comment #8 from Jianyuan Li <li...@gmail.com> ---
Issue clarification:
In Excel 2003 define a defined name as "=Sheet1!$D$3:$D$5,Sheet1!$B$3:$B$5". In
this defined name, a union operator(Excel uses "," and  AOO uses "~") is used
to form a union area. This defined name will be passed into a formula SUM.
Import them in AOO, by Excel 2003 filter the union operator will be translated
into ";" which is an old union operator. And SUM will take it as a parameter
separator. So the SUM result seems correct while it is not.
In AOO, union operator should be set as "~" in defined name. But fix in Excel
2003 filter is not reasonable. Because:
1. Defined name with union(OO uses ";") created in lower version OO(OOo2.4.3)
still cannot be imported correctly even if a fix is patched in Excel 2003
filter.
2. I have checked this formula "=SUM((B3:B5,D3:D5))"(union directly used)
created in Excel 2003. It will be imported as "=SUM((B3:B5~D3:D5))" which is
correct. While Excel 2003 filter still imports the union as ";". But as last it
is "~". Core function must does something which a hint to fix union issue in
defined name.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

lijianyuan1983@gmail.com changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |lijianyuan1983@gmail.com

--- Comment #1 from lijianyuan1983@gmail.com ---
I am working on this defect. Root cause has been found. It is related to the
union operator. AOO use "~" other than ";" as a union operator.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Bug 119565] [From Symphony] The SUM formula result changed incorrectly if save the .xls doc to .ods one.

Posted by bu...@apache.org.
https://issues.apache.org/ooo/show_bug.cgi?id=119565

--- Comment #5 from Regina Henschel <rb...@t-online.de> ---
Created attachment 78077
  --> https://issues.apache.org/ooo/attachment.cgi?id=78077&action=edit
named sequence of ranges in document from OOo2.4.3

I do not think, that it is a problem of Excel filter. If you want to name a
sequence of ranges, that is not possible in AOO3.4. It has been possible in
OOo2.4.3 and is still possible in LO3.5. I have not searched yet, when this
changed for OpenOffice.

You get the same problem, when you open a spreadsheet, which was created with
OOo2.4.3 or with LO3.5. Therefore a fix should not be in the Excel filter, but
in the core for insert names.

-- 
You are receiving this mail because:
You are the assignee for the bug.