Bug Hunting Session
Bug 34412 - FILEOPEN: Import and export from and to MS Excel (xls/xlsx)does not take care of the font of the cell in conditional format definitions
Summary: FILEOPEN: Import and export from and to MS Excel (xls/xlsx)does not take care...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: interoperability
Keywords: filter:xls
: 83666 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2011-02-17 13:05 UTC by starmatz71
Modified: 2019-02-11 12:11 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Conditional-Format.xls (10.50 KB, application/vnd.ms-excel)
2011-02-17 13:05 UTC, starmatz71
Details
Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts (58.12 KB, image/png)
2011-02-17 13:05 UTC, starmatz71
Details
ROW2 - Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts (56.22 KB, image/png)
2011-02-17 13:07 UTC, starmatz71
Details
The formula in excel of cell $D1, fonts cannot chosen upper right (93.01 KB, image/png)
2011-02-17 13:09 UTC, starmatz71
Details
The format-definition for cell $D1 imported from excel is "Excel_CondFormat_1_3_1" (39.27 KB, image/png)
2011-02-17 13:13 UTC, starmatz71
Details
The font of $D1 in "Excel_CondFormat_1_3_1" is Arial, but should be "DejaVu Serif" (39.27 KB, image/png)
2011-02-17 13:15 UTC, starmatz71
Details
The font of $D1 in "Excel_CondFormat_1_3_1" is "Arial", but should be "DejaVu Serif" (100.77 KB, image/png)
2011-02-17 13:20 UTC, starmatz71
Details
This is created in LibreOffice with different fonts and styles in the cells (9.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-01-22 13:14 UTC, starmatz71
Details
ConditionalFormat_Test_B.ods (The original odt-file for the testexample B) (9.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-04-16 10:16 UTC, starmatz71
Details
ConditionalFormat_Test_B.xls (the odt file saved into xls) (6.50 KB, application/vnd.ms-excel)
2012-04-16 10:18 UTC, starmatz71
Details
ConditionalFormat_Test_B.png (Shows the difference between the odt and the reimported xls (63.87 KB, image/png)
2012-04-16 10:22 UTC, starmatz71
Details
The results of in ods created conditional format saved as xls(x) and reimport into LibO (98.79 KB, image/png)
2012-08-23 20:58 UTC, starmatz71
Details
Excel files exported from LibreOffice to Excel conditional format formula bug (90.00 KB, application/zip)
2012-10-07 16:10 UTC, Péter, Lőrincz
Details
.ods with conditional formatting to demo above (14.70 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-12-31 11:55 UTC, mike.hall
Details
result from behavior of file, saved as xls in 63-master, in 63-master and in Excel 2010 (191.92 KB, image/png)
2019-02-11 12:06 UTC, Cor Nouws
Details

Note You need to log in before you can comment on or make changes to this bug.
Description starmatz71 2011-02-17 13:05:05 UTC
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.
Comment 1 starmatz71 2011-02-17 13:05:49 UTC
Created attachment 43493 [details]
Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts
Comment 2 starmatz71 2011-02-17 13:07:25 UTC
Created attachment 43494 [details]
ROW2 - Conditional Format: Top MS Excel coloum D same font, imported in LibO coloum D has different fonts
Comment 3 starmatz71 2011-02-17 13:09:52 UTC
Created attachment 43495 [details]
The formula in excel of cell $D1, fonts cannot chosen upper right
Comment 4 starmatz71 2011-02-17 13:13:14 UTC
Created attachment 43496 [details]
The format-definition for cell $D1 imported from excel is "Excel_CondFormat_1_3_1"
Comment 5 starmatz71 2011-02-17 13:15:33 UTC
Created attachment 43497 [details]
The font of $D1 in "Excel_CondFormat_1_3_1" is Arial, but should be "DejaVu Serif"
Comment 6 starmatz71 2011-02-17 13:20:30 UTC
Created attachment 43498 [details]
The font of $D1 in "Excel_CondFormat_1_3_1" is "Arial", but should be "DejaVu Serif"
Comment 7 starmatz71 2011-02-18 08:21:08 UTC
Same issue in LibO 3.3.1 RC2.
Comment 8 starmatz71 2011-06-17 13:51:13 UTC
Same issue in Libreoffice 3.4.0!
Comment 9 Björn Michaelsen 2011-12-23 11:53:18 UTC Comment hidden (obsolete)
Comment 10 starmatz71 2012-01-19 13:40:23 UTC
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.
Comment 11 starmatz71 2012-01-22 13:14:41 UTC
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.
Comment 12 starmatz71 2012-01-22 13:21:00 UTC
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.
Comment 13 starmatz71 2012-03-30 13:03:26 UTC
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.
Comment 14 starmatz71 2012-04-16 10:16:30 UTC
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?
Comment 15 starmatz71 2012-04-16 10:18:07 UTC
Created attachment 60073 [details]
ConditionalFormat_Test_B.xls (the odt file saved into xls)
Comment 16 starmatz71 2012-04-16 10:22:02 UTC
Created attachment 60075 [details]
ConditionalFormat_Test_B.png (Shows the difference between the odt and the reimported xls
Comment 17 Markus Mohrhard 2012-04-19 17:42:35 UTC
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.
Comment 18 Markus Mohrhard 2012-04-19 17:43:10 UTC
Additionally this should be better with the export of conditional formatting to xlsx that will be in 3.6
Comment 19 starmatz71 2012-04-20 11:28:38 UTC
(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!)
Comment 20 Marc Meledandri 2012-05-02 12:23:43 UTC
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.
Comment 21 starmatz71 2012-08-23 20:57:14 UTC
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.
Comment 22 starmatz71 2012-08-23 20:58:52 UTC
Created attachment 66032 [details]
The results of in ods created conditional format saved as xls(x) and reimport into LibO
Comment 23 starmatz71 2012-09-24 19:46:19 UTC
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.
Comment 24 Markus Mohrhard 2012-10-06 00:28:16 UTC
(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
Comment 25 Péter, Lőrincz 2012-10-07 16:10:12 UTC
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.
Comment 26 starmatz71 2012-11-13 22:40:49 UTC
Still in LibO 3.6.3.2.

Hopefully someone could solve this issues.
Comment 27 mike.hall 2012-12-06 19:08:11 UTC
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!
Comment 28 mike.hall 2012-12-31 11:52:50 UTC
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
Comment 29 mike.hall 2012-12-31 11:55:16 UTC
Created attachment 72338 [details]
.ods with conditional formatting to demo above
Comment 30 alex 2014-08-31 20:00:37 UTC
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
Comment 31 ign_christian 2014-09-01 09:51:18 UTC
Change Version back to 3.3.0 according to: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Version
Comment 32 raal 2015-01-20 22:09:47 UTC
*** Bug 83666 has been marked as a duplicate of this bug. ***
Comment 33 QA Administrators 2018-03-25 02:29:33 UTC Comment hidden (obsolete)
Comment 34 Cor Nouws 2019-02-11 12:06:18 UTC
Created attachment 149116 [details]
result from behavior of file, saved as xls in 63-master, in 63-master and in Excel 2010
Comment 35 Cor Nouws 2019-02-11 12:11:48 UTC
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