Created attachment 43492 [details] Conditional-Format.xls I have found a bug in the conditional format. If a table has different fonts and the file has been imported or exported into or from MS Excel 2000 the special font setting of this field get lost and the font of the default format is taken instead. In the attachment i've uploaded some screenshots and the .ods file of the excample shown at the pictures. Testsystem is a WindowsXP-PC with MS Office 2000 and LibreOffice 3.3.1 RC1 installed (same behavior as in 3.3.0 final). Explanation of the conditional format of the xls-file (you can see it in picture 1) You can see two rows. The conitional format works in this way: If cell $A1 is NOT empty cell $B1 sets the colour of the "B" to red if cell $A1 is NOT empty call $C1 sets the colour of the "d" to green and a single strikethrough if cell $A1 is NOT empty call $D1 sets the colour of the "D" to blue and a single strikethrough If cell $A1 empty the "E" of cell $E1 is RED and big If cell $A2 is NOT empty cell $B2 sets the colour of the "B" to red if cell $A2 is NOT empty call $C2 sets the colour of the "d" to green and a single strikethrough if cell $A2 is NOT empty call $D2 sets the colour of the "D" to blue and a single strikethrough If cell $A2 empty the "E" of cell $E2 is RED and big All cells are set to the font "Arial" but $D1 and $D2 are set to "DejaVu Serif" Now look in coloum D of MS Excel: The font does not change in booth possible cases (coloum A empty or not). Same file imported in LibreOffice: In row 1 the font is now changed in "Arial" !!! Converting has not set the correct font in the "style and format". The difference between the two programs is, that in excel you cannot change the font inside a format-definition, it is given my the cell itself. In LibreOffice you can change the font within a format-definition. The MS Office Importfilter disregard this. Sorry for my english, i hope you can understand it and my contribution is helpfull for you.
Created attachment 43493 [details] Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts
Created attachment 43494 [details] ROW2 - Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts
Created attachment 43495 [details] The formula in excel of cell $D1, fonts cannot chosen upper right
Created attachment 43496 [details] The format-definition for cell $D1 imported from excel is "Excel_CondFormat_1_3_1"
Created attachment 43497 [details] The font of $D1 in "Excel_CondFormat_1_3_1" is Arial, but should be "DejaVu Serif"
Created attachment 43498 [details] The font of $D1 in "Excel_CondFormat_1_3_1" is "Arial", but should be "DejaVu Serif"
Same issue in LibO 3.3.1 RC2.
Same issue in Libreoffice 3.4.0!
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Same behavior in Libre Office 3.5 beta3 ! Export the ods file in msexcel 2000 and import it back to LibO3.5beta3 or MS Excel 2000 causes lost of font information. You can easily test it yourself.
Created attachment 55996 [details] This is created in LibreOffice with different fonts and styles in the cells The fonts in the cell with the D is DejaVu. This is the original file.
Same in LibO 3.5.0rc1! How to reproduce it: 1. Open the file in the attachment above (2012-01-22). The cells have different fonts 2. Save it as .XLS Microsoft Office 2000 Exel 3. Reopen the XLS-File in Libreoffice All cells have the same fonttype!!! The Import and/or Exportfilter does not convert the templates in cells with conditional format correctly.
After open a file in LibreOffice Calc V3.5.1.2, conditional format definitions are not converted completly and does not correctly take care of the fonts in the cell and the conditional format definitions.
Created attachment 60072 [details] ConditionalFormat_Test_B.ods (The original odt-file for the testexample B) I create a new file with LibreOffice V3.5.2.2 and try to make it much more simpler for testing this bug and for better understanding. This file works like this: If you enter a lower "x" in the cell B1 the cell A1 change the font and colour The same happens with the second line and B2 and A2. If you save this file as "Mircosoft Excel 97/2000/XP/2003 (.xls)" and reopen this xls-Excel-file the fontsetting of the cell A1 is always Arial, even if x is set in B1 or not! The problem seems to be the import of an xls file into libreoffice, exporting the file and open it into Excel is ok. odf -> xls = ok xls -> odf = not ok Can someone confirm this behavior?
Created attachment 60073 [details] ConditionalFormat_Test_B.xls (the odt file saved into xls)
Created attachment 60075 [details] ConditionalFormat_Test_B.png (Shows the difference between the odt and the reimported xls
Please don't play with the importance of bug reports. This is just a normal bug report. Normally not even normal because it only affects the chosen font which is not a hard setting.
Additionally this should be better with the export of conditional formatting to xlsx that will be in 3.6
(In reply to comment #17) > Please don't play with the importance of bug reports. This is just a normal bug > report. Normally not even normal because it only affects the chosen font which > is not a hard setting. This bug "only" prevents me to use LibreOffice at work, because it is incompatible with Microsoft Office and will break documents. What is more important as to make LibreOffice compatible for the enterprise? This is a k.o. criteria! Request to set the importance to High. (For me it is essential!)
Just started using LibreOffice 3.4.6 here at our workplace and immediately ran into this awful compatibility bug. It is critical that we are able to view spreadsheets which make use of conditional formatting. This is a showstopper bug.
I confirm that the same issue is present in LibreOffice V3.5.6.2 and in V3.6.1.1. Saving the file as xlsx and reimport it into LibO the conditional formating is completely destroyed. @Markus Mohrhardt: The situation in LibO 3.6 seems to be not better as before. We run in big compatibility issues. Our IT-Team describes LibO as incompatible crap. Today it is forbidden for us users to use LibreOffice at work! In my own opinion: The conditional format converter needs a complete rewrite.
Created attachment 66032 [details] The results of in ods created conditional format saved as xls(x) and reimport into LibO
I provoke a little and set the importance to highest... This problem is still in LibO 3.5.7.1 and in 3.6.2.1 ! Is it possible to fix the import filter, instead of implementing new features into LibO? There are still several compatibility issues in LibO. Thank you.
(In reply to comment #23) > I provoke a little and set the importance to highest... > > This problem is still in LibO 3.5.7.1 and in 3.6.2.1 ! > > Is it possible to fix the import filter, instead of implementing new > features into LibO? There are still several compatibility issues in LibO. Sure, if you implement the fix. The source code for the conditional format import from xls is in sc/source/filter/excel/xicontent.cxx and sc/source/filter/excel/xistyle.cxx
Created attachment 68216 [details] Excel files exported from LibreOffice to Excel conditional format formula bug Opening a conditinally formatted Microsoft Excel (2003) workbook with LibreOffice, when I do not edit conditional formatting, formulas {ie. in sheet Név1 cell D38 condformat 1 C38(:C68)} are shifted to the first row {in this example C1(:C31)} when I open it with Microsoft Excel 2003, but containes the original {here C38(:C68)} formula when I open it with LibreOffice. It is valid in all conditionally formatted cells and all sheets. (see Excel_Condformat_Example1.xls - extractable from the zip file) If the first row containes first conditionally formatted cell of the range, LibreOffice may shift the formula toward a farther column (ie. Excel_Condformat_Example6.xls and ...7.xls from column A to column IV). It does not depend if I edited the content of the conditionally formatted cell with LibreOffice (see sheet Név1 cell I42 in Excel_Condformat_Example2.xls). Reopening the exported Excel xls file in LibreOffice will show the original correct Excel formula again. It means there can be bug(s) in the import filter, too. However, if I edited the conditional formatting of a cell or just opening the conditional formatting dialog and clicking OK (pressing Enter), many but not all of the conditional formatting of other cells of the sheet will disappear (see sheet Név1 around cell I42 in Excel_Condformat_Example3.xls and ...4.xls). Other sheets are not affected in this case. Conditional formatting referring to the same cell is all right (Excel_Condformat_Example5.xls). I hope it will help finding the bug in the import/export filter of Microsoft Excel in case of conditional formatting formula of cells.
Still in LibO 3.6.3.2. Hopefully someone could solve this issues.
Nor does colour get saved. Something quite odd happening. If you set up conditional formatting with LO eg background colours set by the contents of a cell, it works fine in LO working in XLS or ODS format. Save as XLS and open in Excel - no colour formatting. Save from Excel, reopen that saved file in LO - the conditional formatting works again. So, something is being saved and it's retained by Excel, but it's not in the right form for Excel to use. Please fix!
I've analysed what is going on in more detail. Here are some simple steps to take to show what a mess we have here: 1 Create 4 lines of data and 4 new styles with different background colours 2 save as .ods 3 close 4 open 5 create conditional formatting separately on A1 and B1 with formulas like left(b1)=”s” - four conditions 6 Save, close 7 open - The 4 new styles remain – everything is still working ok 8 save as .xlsx (XML 2007) - The 4 new styles remain – formulas remain correct 9 close 10 open New styles have gone. There are 8 generated ones. Conditional formatting is garbage 11 replace step 8 by save as .xlsx (XML 2003) Gives error message that conditional formatting can’t be saved 12 replace step 8 by save as .xlsx (LO XML) The 4 new dtyles remain – formulas remain correct 13 close 14 open New styles have gone. There are 8 generated ones. Conditional formatting is garbage – all formulas refer to B1 15 replace step 8 by save as .xls (97/2000/XP/2003/) The 4 new styles remain – formulas remain correct 16 close This shouldn’t work. .xls does not support more than 3 conditions per cell 17 open New styles remain. There are 32 generated styles (4 tests x 8 cells). Conditional formatting retained, using the new styles NB If you create conditional formatting on A1:B1 using formulas like LEFT($B1)=”s” the result of copy and special paste of formats gives garbage values for the tests Will add the .ods spreadsheet so above can be tested
Created attachment 72338 [details] .ods with conditional formatting to demo above
Hi, I have just installed LibreOffice 4.3.1.2 as a try and there is the issue that when opening an Excel document (*.xlsx) edited with MS Office, conditional formatting prevents/over-writes the font format of the cells, so I'm bringing back this issue to the front. It would be great if anyone could help with this bug... Thanks! Alex
Change Version back to 3.3.0 according to: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version
*** Bug 83666 has been marked as a duplicate of this bug. ***
** 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
Created attachment 149116 [details] result from behavior of file, saved as xls in 63-master, in 63-master and in Excel 2010
looking at the original situation with report and the result in Version: 6.3.0.0.alpha0+ Build ID: b45289e48e0f354b9996e2846dd041db4a9947ce CPU threads: 4; OS: Linux 4.15; UI render: GL; VCL: gtk3; TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-02-08_12:51:03 Locale: nl-NL (nl_NL.UTF-8); UI-Language: en-US Calc: threaded - the situation is greatly improved (see https://bugs.documentfoundation.org/attachment.cgi?id=149116 ) - the bug report collects multiple issues. So the situation is best served by: - closing this as resolved / WorksForMe (not Fixed, since we don't exactly knwo which code commits did it) with great thanks to the devs having done the hard work - creating either new issues for remaining stuff, as simple/single reports as possible; or - cleaning up this list (*) to get a similar result; with thanks to all people doing reports, triage and other QA-stuff for the hard work! https://bugs.documentfoundation.org/buglist.cgi?bug_status=UNCONFIRMED&bug_status=NEW&bug_status=ASSIGNED&bug_status=REOPENED&component=Calc&f1=short_desc&f2=short_desc&j_top=OR&list_id=909699&product=LibreOffice&query_format=advanced&short_desc=conditiona%20xls&short_desc_type=allwordssubstr