Excel .xlsx (conditional) color formatting is being corrupt after loaded and saved with LibreOffice Calc. This prohibits to use the Excel/Windows created .xlsx file as a team shared file with LibreOffice Calc users on Linux. At least the conditional formatted colors disappear in LibreOffice and is being corrupted after saving and then next loaded with Excel again. The attached pdf shows first the screen shot of the original .xlsx file opened with Excel, and second the same file with Excel after it has been loaded and saved once with libreOffice Calc. The attached .xlsx is the original Excel file.
Created attachment 119127 [details] The original Excel created .xlsx file
Terje, Thank you for helping us to improve LibreOffice. You promised us screen shots from Excel and LibreOffice. They would be helpful, especially the one from Excel. Nor would it hurt at all to tell us where to look in the spreadsheet. Please set the bug status back to UNCONFIRMED when you have attached this information. By the way, what version of Excel created the file? Meanwhile, I offer a couple of observations ... officetron diagnoses one error in the file: Entry with MIME type "application/vnd.openxmlformats-package.core-properties+xml" has unrecognized relationship type "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" (see ISO/IEC 29500-1:2008, Clause 15.2.12.1) MS Excel 2003 SP3 warns firstly: This file was created in a newer of version of Microsof Excel. The file has been converted to a format you can work with, but the following issues were encountered. The file has been opened in read-only mode to protect the original file. - Some cells have overlapping conditional formatting ranges. This version of Excel will not evaluate all the conditional formatting rules on the overlapping cells. - Some cells contain types of conditional formatting that are not supported in the version of Excel. - Some cells contain multiple conditional formatting rules that should all be evaluated and shown. this version of Excel does not have this option, and will stop evaluation after the first true condition. and then warns: Microsoft Office Excel is waiting for another application to cxomplete and OLE action. ( I have retyped both these messages, so beware of typos. ) The messages from Excel do not suggest that the right display is anything other than well defined. Is that true, I wonder? I am setting the bug status priority back to normal; the triage process will assign a priority. Terry.
Created attachment 119136 [details] pdf with two screen shots using e The pdf first page shows the screen shot of the original .xlsx file opened with Excel. The second page shows the the same file opened with Excel after it has been loaded and saved once with libreOffice Calc. Look in the area around line 121 where the graded colors have been all read. Above this line also more green background has been added.
I think it is Excel 2013 that is used on Windows, but I will get that verified.
Thank you, Terje. I am setting bug status to UNCONFIRMED.
Terje, Is you problem the same as bug 91654 "FILEOPEN: XLSX - Conditional Formatting rules not imported correctly"? As before, please se the bug status back to UNCONFIRMED when you reply. Terry.
(In reply to Terrence Enger from comment #6) > Terje, > > Is you problem the same as bug 91654 "FILEOPEN: XLSX - Conditional > Formatting rules not imported correctly"? > > As before, please se the bug status back to UNCONFIRMED when you reply. > > Terry. I'm not sure. In my attached pdf screenshots the "Edit formatting rule" popup window show that the original xlsx cell content (first page) midpoint =$D$121 maximum =$E$121 become changed (removed) by Calc to (second page): midpoint 0 maximum 0 And additional, inside Calc the original xlsx file is displayed with WHITE instead of colors in this area.
Thank you, Terje, for getting back to us. I fear that I do not know enough about conditional formatting to do much more. Meanwhile ... (*) I see white background around cell AW121 in both daily dbgutil version 2015-09-30 and version 3.5.4.2 as delivered with debian-wheezy and on Windows Vista with Version: 5.1.0.0.alpha1+ Build ID: 56050b75941355285911c0293a3c02fc403d0c89 TinderBox: Win-x86@39, Branch:master, Time: 2015-08-13_05:36:16 Locale: en-CA (en_CA) So I am setting version 3.5.4 release and o/s All in the bug report. (*) I am changing the summary to "FILEOPEN: .xlsx (conditional) color formatting corrupted". This is narrower than the reported problem, but if the corruption in the output file persists after the input problem is fixed, it deserves a separate bug report anyway. (*) I am setting see-also bug 91654 as per comment 7, and I am setting filter:xlsx. (*) I am setting blocks 87351 Meta: "Conditional formatting bugs and enhancements".
Setting whiteboard needQAAdvice.
Migrating Whiteboard tags to Keywords: (filter:xlsx) [NinjaEdit]
This file breaks all the assumptions that I made when I wrote the import code. The type is set to num but the string is not a value. I need to think about this a bit.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b1ba84efc38821d5d272448cb34af7519306b8ce handle entries with num type and formula during OOXML import, tdf#94626 It will be available in 5.2.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.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b125635851d3c96b0d27d06d45475774de23e8df add test for tdf#94626 It will be available in 5.2.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.
Thank you, Markus. With daily dbgutil bibisect version 2016-01-27, I see colors like page 2 of Terje's attached .pdf. Terje, When you see good results for yourself, please set the bug status to VERIFIED FIXED. I am removing whiteboard "needQAAdvice".
Thanks for the patch. I'm currently running libreoffice 5.0.2.2-1.2 x86_64 on openSUSE Leap 42.1, and the newest package in Factory for 42.1 is currently version 5.0.4 I'm sorry I don't know if it is possibly and if so how to patch up this before the official 42.1 package contain the new patch. Terje J. H
Terje, Release 5.2, with your fix, should be available in August. If you do not want to wait, you can wait for a build later than 2016-01-27 to appear in <http://dev-builds.libreoffice.org/daily/master/Win-x86@62-merge-TDF/current/> and "install" it following the instructions at <https://wiki.documentfoundation.org/Installing_in_parallel/Windows>. Terry.