Bug 94626 - FILEOPEN: .xlsx (conditional) color formatting corrupted
Summary: FILEOPEN: .xlsx (conditional) color formatting corrupted
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.4 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.2.0
Keywords: filter:xlsx
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2015-09-29 21:51 UTC by Terje
Modified: 2016-10-25 19:11 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
The original Excel created .xlsx file (46.66 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-09-29 21:53 UTC, Terje
Details
pdf with two screen shots using e (758.28 KB, application/pdf)
2015-09-30 10:14 UTC, Terje
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Terje 2015-09-29 21:51:56 UTC
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.
Comment 1 Terje 2015-09-29 21:53:46 UTC
Created attachment 119127 [details]
The original Excel created .xlsx file
Comment 2 Terrence Enger 2015-09-30 01:10:13 UTC
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.
Comment 3 Terje 2015-09-30 10:14:13 UTC
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.
Comment 4 Terje 2015-09-30 10:17:11 UTC
I think it is Excel 2013 that is used on Windows, but I will get that verified.
Comment 5 Terrence Enger 2015-09-30 12:50:32 UTC
Thank you, Terje.  I am setting bug status to UNCONFIRMED.
Comment 6 Terrence Enger 2015-09-30 15:07:40 UTC
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.
Comment 7 Terje 2015-09-30 16:10:00 UTC
(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.
Comment 8 Terrence Enger 2015-09-30 20:21:41 UTC
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".
Comment 9 Terrence Enger 2015-09-30 20:59:05 UTC
Setting whiteboard needQAAdvice.
Comment 10 Robinson Tryon (qubit) 2015-12-17 13:06:25 UTC Comment hidden (obsolete)
Comment 11 Markus Mohrhard 2016-01-26 17:30:37 UTC
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.
Comment 12 Commit Notification 2016-01-26 21:20:59 UTC
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.
Comment 13 Commit Notification 2016-01-27 05:28:10 UTC
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.
Comment 14 Terrence Enger 2016-01-27 16:13:37 UTC
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".
Comment 15 Terje 2016-01-27 22:38:15 UTC
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
Comment 16 Terrence Enger 2016-01-27 23:45:43 UTC
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.