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.
Confirm the same behavior with: Version: 4.3.5.2.0+ Build ID: 430m0(Build:2) from openSUSE build service.
Hello, Thank you for filing the bug. Please attach a *.xls file with a column of UPC codes. I have set the bug's status to 'NEEDINFO', so please do change it back to 'UNCONFIRMED' once you have attached a document. (Please note that the attachment will be public, remove any sensitive information before attaching it.) How can I eliminate confidential data from a sample document? https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F Thank you
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.
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.
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?
(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.
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
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?
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.
Created attachment 122276 [details] Example XLS spreadsheet and perl script showing inconsistencies
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.
The issue still exists in the 5.1.0.3 release.
The issue still exists in the 5.2.0.4 release.
** 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 on a currently supported version of LibreOffice (5.4.1 or 5.3.6 https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System 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) 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: http://webchat.freenode.net/?channels=libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug-20170901
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.
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".
** 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
Dear Adrian Klaver, 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 https://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
I'm still seeing this in LO 6.4.5.2.
Dear Adrian Klaver, 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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
Version: 7.4.2.3 / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5 (cairo+xcb) Locale: en-US (en_US.UTF-8); UI: en-US Calc: threaded I still see this issue with one exception: 1) Make change in xls file and save as xls and all UPC numbers become 0(actually 0.0) in xlrd. 2) Open xls file save as ods then change the file and save as xls now also results in the UPC's showing up as 0.0 when read by xlrd whereas before they did not change. 3) Opens as xls save as xlsx still works in that the UPC's are preserved when read by xlrd.