Bug 419181 - Testcase for OpenOffice.org Calc/Excel
Summary: Testcase for OpenOffice.org Calc/Excel
Status: CLOSED UPSTREAM
Alias: None
Product: Fedora
Classification: Fedora
Component: openoffice.org (Show other bugs)
(Show other bugs)
Version: rawhide
Hardware: All All
low
medium
Target Milestone: ---
Assignee: Caolan McNamara
QA Contact: Fedora Extras Quality Assurance
URL: http://www.ambience.sk/open-office-te...
Whiteboard:
Keywords:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2007-12-11 03:32 UTC by Răzvan Sandu
Modified: 2008-01-14 17:16 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-01-14 17:16:10 UTC
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
Microsoft Excel file to test. (143.50 KB, application/octet-stream)
2007-12-11 03:35 UTC, Răzvan Sandu
no flags Details


External Trackers
Tracker ID Priority Status Summary Last Updated
OpenOffice.org 85242 None None None Never

Description Răzvan Sandu 2007-12-11 03:32:57 UTC
Hello,

Description of problem:

The attached spreadsheet file (.xls) behaves differently if it is open in
Microsoft Excel 2003 or in OpenOffice.org Calc 2.3.1.

(At least) totals at the bottom of column I are different.

Version-Release number of selected component (if applicable):
openoffice.org-calc-2.3.0-6.7.fc8

How reproducible:
Always.

Steps to Reproduce:
1. Please open the attached .xls file in Excel 2003 and note the bottom totals
in column I.
2.Please open the attached .xls file in OpenOffice.org Calc and note the (now
different) bottom totals in column I.
  
Actual results:
Recalculated formulas give different mathematical answers.

Expected results:
Recalculated formulas should give the same mathematical answer.

Additional info:
Please don't shoot me ;-) for the corectness of this file; it was not created by
me. The file is provided „as it is”, without any assumption of its corectness,
just to serve as a testcase. It is one of the many files an average user creates
in his/hers day-by-day work in a large office.

Comment 1 Răzvan Sandu 2007-12-11 03:35:26 UTC
Created attachment 283591 [details]
Microsoft Excel file to test.

Comment 2 Răzvan Sandu 2007-12-11 04:55:43 UTC
Hello again,

As far as I can figure it out, if one tries to export this Excel file as .ods
(either via the Sun plugin for Microsoft Office or by directly opening the file
in Calc), numbers get converted to text: an apostrophe is added in front of each
digit in columns E and F. Of course, further calculations cannot be performed.

I don't know if the above is the only issue that gets an incorrect mathematical
calculation.

In the mean time, I found no way to (automatically) remove the apostophes or to
format the cells in columns E and F as numbers. Can you please provide a way to
do this or point to the relevant documentation ?

Such an issue is a *serious showstopper* for a less-skilled IT person, let's say
an accountant.


Regards,
Răzvan


Comment 3 Răzvan Sandu 2007-12-13 11:34:04 UTC
I think the correct behaviour of Calc should be to issue a warning when some
formula tries to perform calculations with numerals that are treated as text.

In Excel 2003's help I've identified a quick procedure for converting a range of
cells that are treated as text back to numerals (by multiplying them with 1,
using Paste Special from the Edit menu).

Regards,
Răzvan

Comment 4 Răzvan Sandu 2007-12-17 02:12:33 UTC
Hello,


This seems to be a solution, but the problem remains *very* annoying:

http://www.ambience.sk/open-office-text-number-format-conversion

Besides the steps given at the link above, it requires selecting the affected
cells and doing a Format -> Default Formatting; if one doesn't do that, the
regular expression replacement won't work.


Regards,
Răzvan

Comment 5 Caolan McNamara 2008-01-14 17:16:10 UTC
Indeed (long delay for me to find a copy of excel), excel itself warns about
this with little warnings symbols, but it does work. What's strange to me is
that it works with text formatted cells of more than one character, but not with
text formatted cells of one character. Let's check with upstream, there's more
expertise there wrt. calc issues and it affects all versions, not just the
fedora one. http://www.openoffice.org/issues/show_bug.cgi?id=85242


Note You need to log in before you can comment on or make changes to this bug.