Bug 70565 - FORMATTING: User Defined Custom Formatting is not applied during importing XLSX documents
Summary: FORMATTING: User Defined Custom Formatting is not applied during importing XL...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Bartosz
QA Contact:
URL: https://freedomsponsors.org/issue/673...
Whiteboard: target:5.3.0 target:5.2.5
Keywords:
Depends on:
Blocks:
 
Reported: 2013-10-17 07:24 UTC by Mikeyy - L10n HR
Modified: 2016-12-19 09:37 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Source XLSX file (11.02 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-10-17 07:24 UTC, Mikeyy - L10n HR
Details
Picture of file formatting in Excel 2007 (79.44 KB, image/png)
2013-10-17 07:25 UTC, Mikeyy - L10n HR
Details
Property windows after import test.xlsx (68.18 KB, image/png)
2016-06-05 20:44 UTC, Bartosz
Details
One cell test document (4.59 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-06-06 07:08 UTC, Mikeyy - L10n HR
Details
Wrong bools values (0,1 instead of TRUE, FALSE) created by Excel 2010 (8.60 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-06 15:00 UTC, Bartosz
Details
One cell test document with manual hacking content (3.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-08 14:28 UTC, Bartosz
Details
LO_TEST - XLS (9.00 KB, application/vnd.ms-excel)
2016-07-09 17:54 UTC, Mikeyy - L10n HR
Details
LO_TEST - XLSX (6.05 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-09 17:55 UTC, Mikeyy - L10n HR
Details
LO_TEST - XLSX - saved by MS Excel 2010 (9.40 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-12 21:51 UTC, Bartosz
Details
Source XLSX file - saved by MS Excel 2010 (14.58 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-07-12 21:56 UTC, Bartosz
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mikeyy - L10n HR 2013-10-17 07:24:18 UTC
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.
Comment 1 Mikeyy - L10n HR 2013-10-17 07:25:07 UTC
Created attachment 87779 [details]
Picture of file formatting in Excel 2007
Comment 2 tommy27 2014-10-19 18:46:22 UTC
reproducibile under Win7x64 using LibO 4.3.2.2
status NEW
Comment 4 tommy27 2016-04-16 07:29:10 UTC Comment hidden (obsolete)
Comment 5 Bartosz 2016-06-05 20:37:41 UTC
I made some investigation about this problem, and it seems that importing "Custom Formatting" from xlsx is not working properly.
Comment 6 Bartosz 2016-06-05 20:44:45 UTC
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)?
Comment 7 Mikeyy - L10n HR 2016-06-06 07:08:20 UTC
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.
Comment 8 Bartosz 2016-06-17 22:24:41 UTC
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.
Comment 9 Bartosz 2016-07-05 00:14:48 UTC
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?
Comment 10 Bartosz 2016-07-06 14:43:29 UTC
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.
Comment 11 Bartosz 2016-07-06 15:00:34 UTC
Created attachment 126088 [details]
Wrong bools values (0,1 instead of TRUE, FALSE) created by Excel 2010
Comment 12 Mikeyy - L10n HR 2016-07-06 18:35:14 UTC
(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.
Comment 13 Mikeyy - L10n HR 2016-07-06 19:03:18 UTC
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.
Comment 14 Bartosz 2016-07-08 14:21:07 UTC
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.
Comment 15 Bartosz 2016-07-08 14:28:03 UTC
Created attachment 126129 [details]
One cell test document with manual hacking content
Comment 16 Mikeyy - L10n HR 2016-07-08 15:20:16 UTC
Wow, good job finding root cause!
Looking forward to fix!
Comment 17 Mikeyy - L10n HR 2016-07-08 20:48:50 UTC
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.
Comment 18 Bartosz 2016-07-09 08:20:29 UTC
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.
Comment 19 Mikeyy - L10n HR 2016-07-09 17:54:45 UTC
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.
Comment 20 Mikeyy - L10n HR 2016-07-09 17:55:24 UTC
Created attachment 126136 [details]
LO_TEST - XLSX

XLSX file, same as above.
Comment 21 Bartosz 2016-07-12 21:51:51 UTC
Created attachment 126186 [details]
LO_TEST - XLSX - saved by MS Excel 2010
Comment 22 Bartosz 2016-07-12 21:56:52 UTC
Created attachment 126187 [details]
Source XLSX file - saved by MS Excel 2010
Comment 23 Bartosz 2016-07-13 11:54:34 UTC
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.
Comment 24 Bartosz 2016-07-18 22:44:40 UTC
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.
Comment 25 Bartosz 2016-07-30 00:06:59 UTC
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".
Comment 26 Mikeyy - L10n HR 2016-08-17 17:53:58 UTC
I see your patch got verified. Can you please try to apply it to 5.2 branch also? Thank you!
Comment 27 Commit Notification 2016-08-19 03:14:21 UTC
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.
Comment 28 Mikeyy - L10n HR 2016-08-19 13:49:02 UTC
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
Comment 29 Bartosz 2016-08-21 19:51:21 UTC
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
Comment 30 Bartosz 2016-08-21 20:53:47 UTC
Review for LibreOffice-5-2 is in progress:
https://gerrit.libreoffice.org/#/c/28266/
Comment 31 Commit Notification 2016-08-22 21:27:09 UTC
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.
Comment 32 Bartosz 2016-10-05 22:49:15 UTC
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.
Comment 33 Commit Notification 2016-12-05 17:49:31 UTC
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.
Comment 34 Timur 2016-12-06 11:54:28 UTC
(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.
Comment 35 Mikeyy - L10n HR 2016-12-06 13:11:56 UTC
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.