Created attachment 87778 [details] Source XLSX file I have XLSX files generated by SAP BI software. They open correctly formatted in Excel 2007, but in LibreOffice they open without any number or date formatting. Further, if I save file with LO it looses it's date/number formatting in other applications also (Excel 2007). Attached you can find test.xlsx which is source file generated by SAP BI, and picture of same file opened in Excel 2007 on Win 7 64bit.
Created attachment 87779 [details] Picture of file formatting in Excel 2007
reproducibile under Win7x64 using LibO 4.3.2.2 status NEW
Added sponsoring. https://freedomsponsors.org/issue/673/formatting-loss-of-number-and-date-formatting-in-xlsx-files
** 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.0.5 or 5.1.2 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 your help! -- The LibreOffice QA Team This NEW Message was generated on: 2016-04-16
I made some investigation about this problem, and it seems that importing "Custom Formatting" from xlsx is not working properly.
Created attachment 125499 [details] Property windows after import test.xlsx Property windows after import test.xlsx, shows that user defined custom formatting is properly imported into LibreOffice. To workaround this issue you could select impacted cells, click Right Mouse Button, select "Format Cells" and then apply User Defined formatting manually. Could you please reproduce this issue with single cell (with custom formatting)?
Created attachment 125505 [details] One cell test document Here is one cell test document. You will notice background is white for first 2 rows, but I didn't find a way to remove that from program. A1 cell has todays date, 6.6.2016 and it should be shows formatted like that. I user font Arial, size 15, bold, centered both horizontally and vertically. If I just right click A1 cell in LO 5.1.4.1 (win 8.1), and go to Format Cell, formatting shown is NUMBER / GENERAL, not DATE. When I generate document as XLS (from original program), custom formatting is shown correctly.
It seems that described behaviour could be split into several two bugs. I have created bug which is needed to properly resolve this bug: https://bugs.documentfoundation.org/show_bug.cgi?id=100464 My plan is first resolve issue with missing custom cell formatting and after that resolve that bug.
To provide proper fix I need to test it in different user scenario. It will help in avoiding potential regression. Could you please provide instruction, how to create .xlsx document with MS Excel, in which this bug will appear. Does this issue is visible only in .xlsx documents created by SAP BI software?
This bug is related to this one: https://bugs.documentfoundation.org/show_bug.cgi?id=81350 I will check if the same solution works for it.
Created attachment 126088 [details] Wrong bools values (0,1 instead of TRUE, FALSE) created by Excel 2010
(In reply to Bartosz Kosiorek from comment #9) > Could you please provide instruction, how to create .xlsx document with MS > Excel, in which this bug will appear. > > Does this issue is visible only in .xlsx documents created by SAP BI > software? I don't have Excel, so I can't reproduce it. Yes, it's visible only in .xlsx files. Old binary .xls files created by this software are not affected. I think backend for that software is Excel 2010 retail installation trough which it creates documents.
When I open my xlsx files, it says in workbook.xml this: http://schemas.openxmlformats.org/spreadsheetml/2006/main Does that means it's created by Excel 2007? Not really sure about versions, but I'll see if I can check somehow tomorrow.
Thanks to Markus which point that root cause of problem was default value for "applyNumberFormat", I think the root cause of problem is wrong "xfId". After testing different .xlsx content, I think the default value of "applyNumberFormat" is ok ("false" in this case). The problem could be with default value of "xfId", which in my case should be "0" for all fields in "cellXfs" After changing from (test_LO_original.xlsx file in attachment): <cellXfs count="3"> <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/> <xf numFmtId="0" fontId="1" fillId="2" borderId="0" applyFont="1" applyFill="1" applyAlignment="1"> <alignment horizontal="left" vertical="bottom"/> </xf> <xf numFmtId="172" fontId="2" fillId="2" borderId="0" applyFont="1" applyFill="1" applyAlignment="1"> <alignment horizontal="center" vertical="center"/> </xf> </cellXfs> to (test_LO_hacked.xlsx file in attachment): <cellXfs count="3"> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/> <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"> <alignment horizontal="left" vertical="bottom"/> </xf> <xf numFmtId="172" fontId="2" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1" applyNumberFormat="0"> <alignment horizontal="center" vertical="center"/> </xf> </cellXfs> everything is working correctly, even if I export it via LibreOffice to .xlsx format After save test_LO_original.xlsx file by MS Excel, it is adding xfId="0" and applyNumberFormat="1" (test_LO_saved_by_Excel.xlsx file in attachment). Now as I know more about root cause of problem, I'm starting implementation of solution to propose.
Created attachment 126129 [details] One cell test document with manual hacking content
Wow, good job finding root cause! Looking forward to fix!
Can I ask you, if it's not too much trouble, to apply fix to 5.1 and 5.2 branch, or at least 5.2 which will be published in about 1 month? Thank you.
First patch needs to be pushed into master (5.3) and then it could be backported into 5.0, 5.1 and 5.2 Unfortunalety I am not experienced in that matter. I started contributing to two moths ago. Could you please attach both .xls and .xlsx files (complex one). I would like to compare how it is imported.
Created attachment 126135 [details] LO_TEST - XLS This is XLS version of more complicated file. It includes date, formated numbers (1.000.000,00 with negatives in red) and percentage (10,00%). Also, when I was saving file from program and choosed xlsx file, it said Excel 2007 version. Next file will be XLSX.
Created attachment 126136 [details] LO_TEST - XLSX XLSX file, same as above.
Created attachment 126186 [details] LO_TEST - XLSX - saved by MS Excel 2010
Created attachment 126187 [details] Source XLSX file - saved by MS Excel 2010
Following parameters needs to be set to "0" by default, if it is not explicetely set in .xlsx file: - numFmtId="0" - fontId="0" - fillId="0" - borderId="0" - xfId="0" I checked it with MS Excel 2010 and Excel 2007 and it is working in that way. Currently in LibreOffice these parameters are set to "-1" I have created fix for that, and now I'm preparing tests.
The problem with user formatting could be divided into two issues: 1. Wrong default xfId value during .xlsx import 2. default applyNumberFormat is calculated property during .xlsx import For the first issue I have prepared review: https://gerrit.libreoffice.org/#/c/27198/ I think most of the issues will be solved with that patch. After apply that fix, users could download latest LibreOffice image, test it and give remarks/examples of documents in which there are still problems.
To resolve issue no. 2: "default applyNumberFormat is calculated property during .xlsx import" we need to enable applyNumberFormat="1" in <cellXfs> always when style is used in <c> (sheet1.xml) For example: <row r="3" s="1" customFormat="1" ht="9.5994" customHeight="1"> <c r="A3" s="3"/> means that style number s="3" must have enabled "applyNumberFormat".
I see your patch got verified. Can you please try to apply it to 5.2 branch also? Thank you!
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=9b9bcef5ef1858c63c8708bfae2ecea3d398eeb8 tdf#70565 Set correct default values to "0" of xfId attribute It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Just tested it with latest daily and it's working! Used few personal files and everything is shown correctly. Only dates in first XLSX file attached to this bug aren't shown, but dates from my personal files are shown correctly. I wouldn't try to fix that, if I notice new issues I will report in a new bug. Please try to backport it to 5.2 so we can start using your fix as soon as possible. :) Also, please claim bounty for this bug on https://freedomsponsors.org/issue/673/formatting-loss-of-number-and-date-formatting-in-xlsx-files
I would like to confirm that https://freedomsponsors.org/user/gang65/ is my account. Thanks to you Mikeyy - L10n HR, I was able to resolve many import/export xlsx issues. More details are available at: https://wiki.documentfoundation.org/User:Gang65
Review for LibreOffice-5-2 is in progress: https://gerrit.libreoffice.org/#/c/28266/
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fb37ba7df28ee9eb81c1dc21c55baabe56c474b3&h=libreoffice-5-2 tdf#70565 Set correct default values to "0" of xfId attribute It will be available in 5.2.2. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
The new version of LibreOffice was released (5.2.2): https://www.libreoffice.org/download/libreoffice-fresh/ Could you please check how it is working for you? What else problem are you facing during import/export xlsx documents? I have some spare time, and I could spend it for some improvements into .xlsx handling.
Bartosz Kosiorek committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c88c3f66984df5ac4ca65431e7eacbeb93cb2217&h=libreoffice-5-2 tdf#70565 Set correct default values to "0" of xfId attribute It will be available in 5.2.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Mikeyy - L10n HR from comment #28) > Only dates in first XLSX file attached to this bug aren't shown, but dates > from my personal files are shown correctly. I wouldn't try to fix that, if I > notice new issues I will report in a new bug. Dates not shown from attachment 87778 [details] but shown if re-saved with MSO. Sure it would be better if it was also fixed, but since not requested, let Bartosz decide.
Of course Timur, but I didn't want to bug Bartosz further and I couldn't reproduce it on new documents, only on attached one. If he has time, it would be great if he could fix it. This last commit for LO 5.2.5, what is it for? This bug landed in 5.2.3.