Download it now!
Bug 87773 - FILESAVE: Numbers not saved properly
Summary: FILESAVE: Numbers not saved properly
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.6.2 release
Hardware: x86 (IA32) Linux (All)
: high normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-12-27 20:44 UTC by Adrian Klaver
Modified: 2018-10-03 02:54 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Spreadsheet with single column UPC numbers. (26.00 KB, application/vnd.ms-excel)
2014-12-28 20:40 UTC, Adrian Klaver
Details
Example XLS spreadsheet and perl script showing inconsistencies (68.45 KB, application/x-compress)
2016-01-29 15:25 UTC, Henning Spruth
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Adrian Klaver 2014-12-27 20:44:58 UTC

    
Comment 1 Adrian Klaver 2014-12-27 20:54:12 UTC
Given a *.xls file with a column of UPC codes:

744835984608
744835984608
744835985988
744835990012
744835985988

Open the file in LibreOffice(LO), make any change and then save as *.xls. 

In LO the numbers remain as above. When I open the file with xlrd and read the numbers they come out as 0.0. 

If I take the changed file and Save As *.ods and then Save As *.xls then I see the numbers correctly in both LO and when opened in xlrd.

If I take the *.xls file and Save As *.xlsx and make changes in the *.xlsx file I have no problems either.

In summary there seems to be some issue with saving a *.xls file in place.
Comment 2 Adrian Klaver 2014-12-27 21:26:17 UTC
Confirm the same behavior with:

Version: 4.3.5.2.0+
Build ID: 430m0(Build:2)

from openSUSE build service.
Comment 3 raal 2014-12-28 19:02:13 UTC Comment hidden (obsolete)
Comment 4 Adrian Klaver 2014-12-28 20:40:23 UTC
Created attachment 111438 [details]
Spreadsheet with single column UPC numbers.

Attached is spreadsheet with a single column of UPC numbers. I tested it here and if I read it as sent I see the same values when I open it using xlrd as I see in LibreOffice. If I make a change, say delete the '#' in the column header and save as *.xls, I see 0.0 for values when I open in xlrd. The values are still correct in the LibreOffice view.
Comment 5 raal 2014-12-28 21:36:24 UTC
LO: 4.3.3.2, Linux

python /usr/local/bin/runxlrd.py 3rows /tmp/upc_number_sample.xls

=== File: /tmp/upc_number_sample.xls ===
Open took 0.01 seconds

BIFF version: 8; datemode: 0
codepage: 1200 (encoding: utf_16_le); countries: (1, 1)
Last saved by: u'Calc'
Number of data sheets: 1
Use mmap: 1; Formatting: 0; On demand: 0
Ragged rows: 0
Load time: 0.00 seconds (stage 1) 0.01 seconds (stage 2)

sheet 0: name = u'Sheet1'; nrows = 161; ncols = 1

cell A1: type=1, data: u'UPC#'

cell A2: type=1, data: u'744835962453'

cell A161: type=1, data: u'744835400016'


command took 0.00 seconds


after deleting # from UPC#

/tmp/xlrd-0.9.3$ python /usr/local/bin/runxlrd.py 3rows /tmp/upc_number_sample.xls

=== File: /tmp/upc_number_sample.xls ===
Open took 0.00 seconds

BIFF version: 8; datemode: 0
codepage: 1200 (encoding: utf_16_le); countries: (420, 420)
Last saved by: u'Calc'
Number of data sheets: 1
Use mmap: 1; Formatting: 0; On demand: 0
Ragged rows: 0
Load time: 0.00 seconds (stage 1) 0.00 seconds (stage 2)

sheet 0: name = u'Sheet1'; nrows = 161; ncols = 1

cell A1: type=1, data: u'UPC'

cell A2: type=2, data: 0.0

cell A161: type=2, data: 0.0


command took 0.00 seconds

I changed first number (A2 cell) from ="744835962453" to numeric value:
python /usr/local/bin/runxlrd.py 3rows /tmp/upc_number_sample.xls

=== File: /tmp/upc_number_sample.xls ===
Open took 0.00 seconds

BIFF version: 8; datemode: 0
codepage: 1200 (encoding: utf_16_le); countries: (420, 420)
Last saved by: u'Calc'
Number of data sheets: 1
Use mmap: 1; Formatting: 0; On demand: 0
Ragged rows: 0
Load time: 0.00 seconds (stage 1) 0.00 seconds (stage 2)

sheet 0: name = u'Sheet1'; nrows = 161; ncols = 1

cell A1: type=1, data: u'UPC'

cell A2: type=2, data: 744835962453.0

cell A161: type=2, data: 0.0


command took 0.00 seconds
 
Open file, save as .ods, then save as .xls

python /usr/local/bin/runxlrd.py 3rows /tmp/upc_number_sample2.xls

=== File: /tmp/upc_number_sample2.xls ===
Open took 0.00 seconds

BIFF version: 8; datemode: 0
codepage: 1200 (encoding: utf_16_le); countries: (420, 420)
Last saved by: u'Calc'
Number of data sheets: 1
Use mmap: 1; Formatting: 0; On demand: 0
Ragged rows: 0
Load time: 0.00 seconds (stage 1) 0.00 seconds (stage 2)

sheet 0: name = u'Sheet1'; nrows = 161; ncols = 1

cell A1: type=1, data: u'UPC'

cell A2: type=2, data: 0.0

cell A161: type=2, data: 0.0


command took 0.00 seconds


xlrd read values as numeric values but these values are strings. I don't know if it is bug in LO or in xlrd.
Comment 6 Adrian Klaver 2014-12-28 22:17:35 UTC
Thanks for taking a look at this. I will send bug report link to the python-excel list, for view by folks that know more than I do about xlrd.

Should have added previously, that when I did the ods shuffle it went like this:
    
1) Start with xls file, Save As ods file
2) In ods file, make change, do not save as ods, instead Save As xls.
3) Read saved xls file with xlrd.

Then there is still the issue of why xlsx works?
Comment 7 Robinson Tryon (qubit) 2014-12-29 05:28:38 UTC
(In reply to raal from comment #5)
> ...
> xlrd read values as numeric values but these values are strings. I don't
> know if it is bug in LO or in xlrd.

Thanks for the extensive work here!

This sounds like a legitimate issue, and it looks like work is progressing here, so I'll change status -> NEW. If we discover that the problem is wholly in xlrd, feel free to change status to RESOLVED NOTOURBUG.
Comment 8 Adrian Klaver 2014-12-30 00:27:10 UTC
To update John Machin one of the developers of xlrd is taking a look at the before and after spreadsheets for a clue as what is going on. If you want to follow the thread on the python-excel list it is here:

https://groups.google.com/forum/#!topic/python-excel/7N45ysX2f8c
Comment 9 Adrian Klaver 2014-12-31 00:16:15 UTC
From the message below at the python-excel mailing list:

https://groups.google.com/d/msg/python-excel/7N45ysX2f8c/VJC1ClBW4QEJ

The issue seems to be that the values are actually formulas and that on saving LO does not evaluate the formulas, but instead saves 0.0. I looked for a setting to change this behavior, however I could not find one. 

Does such a setting exist?

If not is this something that can be fixed?
Comment 10 Henning Spruth 2015-10-16 16:50:08 UTC
I came here searching for a solution to a problem that might have the same root cause:

I have an XLS spreadsheet with a simple CONCATENATE function. It is parsed with the perl Spreadsheet::ParseExcel library. When the spreadsheet was saved with Libreoffice 5.0.2.2, the perl parser incorrectly returns "0" for the cell containing the concatenation.

When saving the same spreadsheet with Openoffice 4.1.1 or Excel 2007, the perl parser returns the correct value.

I can provide proof-of-concept perl code if needed.
Comment 11 Henning Spruth 2016-01-29 15:25:32 UTC
Created attachment 122276 [details]
Example XLS spreadsheet and perl script showing inconsistencies
Comment 12 Henning Spruth 2016-01-29 15:30:57 UTC
I attached an example testcase containing an example.xls saved by libreoffice Calc version 5.0.4.2 and a trivial perl script using the Spreadsheet::ParseExcel package. There are two identical formuluas concatenating a pair of cells, but are read differently by the perl script.
Comment 13 Henning Spruth 2016-02-10 16:39:44 UTC Comment hidden (obsolete)
Comment 14 Henning Spruth 2016-08-03 15:54:38 UTC Comment hidden (obsolete)
Comment 15 QA Administrators 2017-09-01 11:18:24 UTC Comment hidden (obsolete)
Comment 16 Adrian Klaver 2017-09-03 15:39:24 UTC
I am using openSUSE Leap 42.2 and the latest version available is 5.3.3.2. Testing with that shows that the bug is still there. The behavior is the same as before.
Comment 17 Henning Spruth 2017-10-02 23:17:24 UTC
The issue still exists in the 5.4.1.2 release.

Refer to the 2nd attachment (Example XLS spreadsheet and perl script): when opening the original XLS in Libreoffice and saving it, the perl script (using the Spreadsheet::ParseExcel package) displays "0" instead of the expected result of the =CONCATENATE formula, which is "foom0bazbar".
Comment 18 QA Administrators 2018-10-03 02:54:08 UTC
** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information from Help - About LibreOffice.
 
If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice.

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not 
appropriate in this case)


If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug