Bug 93045 - FILESAVE: custom styles lost on saving to XLSX (Summary in Comment 6)
Summary: FILESAVE: custom styles lost on saving to XLSX (Summary in Comment 6)
Status: RESOLVED DUPLICATE of bug 98074
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.0.beta1
Hardware: All All
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisected, bisected, regression
: 98672 100464 (view as bug list)
Depends on:
Blocks:
 
Reported: 2015-07-31 07:44 UTC by Elmar
Modified: 2019-01-31 12:09 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
file created using Calc, which I have then saved as XLS and XLSX (18.46 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-07-31 07:44 UTC, Elmar
Details
Broken XLSX (10.44 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-07-31 07:46 UTC, Elmar
Details
saved XLS that looks fine (28.00 KB, application/vnd.ms-excel)
2015-07-31 07:48 UTC, Elmar
Details
example file in ODS format (10.23 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-08-10 08:22 UTC, Elmar
Details
example file in XLS format (7.50 KB, application/vnd.ms-excel)
2015-08-10 08:23 UTC, Elmar
Details
example file in XLSX format (5.42 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-08-10 08:23 UTC, Elmar
Details
created by excel (12.91 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-06-26 11:44 UTC, Elmar
Details
created by excel saved by excel (8.41 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-06-26 11:45 UTC, Elmar
Details
created by excel saved by calc (11.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-06-26 11:46 UTC, Elmar
Details
created byc calc (12.85 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-06-26 11:47 UTC, Elmar
Details
opened in excel with error error log (448 bytes, text/xml)
2016-06-26 11:49 UTC, Elmar
Details
saved by excel after error correction (8.95 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2016-06-26 11:49 UTC, Elmar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Elmar 2015-07-31 07:44:12 UTC
Created attachment 117553 [details]
file created using Calc, which I have then saved as XLS and XLSX

Version: 4.4.5.2
Build ID: 40m0(Build:2)
Locale: en_ZA.UTF-8
This seems to be a regression as I have not noticed this before and I save to XLSX all the time.

Created a spreadsheet (see .ods attached)

Saved to .XLSX - when opened it in MSExcel asked if wanted to fix it "Removed Records: Named range from /xl/workbook.xml part (Workbook)"
 
A number of named ranges have been created, and most of the styles have been deleted.

Allowed MSExcel to fix it, it displays fine in MSExcel - styles appear to have been changed to cell formatting, but still not in in Calc.

When saved same .ods to XLS, there is no problem. Opens XLS as saved with Calc and MSExcel.

How do I save a second attachment - the resulting XLSX?
Comment 1 Elmar 2015-07-31 07:46:41 UTC
Created attachment 117554 [details]
Broken XLSX

Here is the broken XLSX.
Comment 2 Elmar 2015-07-31 07:48:14 UTC
Created attachment 117555 [details]
saved XLS that looks fine
Comment 3 raal 2015-07-31 12:35:54 UTC
Hello [user's name],

Thank you for reporting the bug. Can you see if bug 86575 is similar to your problem so that I can mark your report as a duplicate of it? (Later bugs are generally marked as duplicates of earlier bugs).
Comment 4 tommy27 2015-07-31 13:36:36 UTC
@raal
looks like it's the same problem
Comment 5 Buovjaga 2015-08-01 16:24:34 UTC
Yep, seems to be the named ranges, that are pointing to referenced targets that don't exist anymore.
Checked with Insert - Names - Manage (see #REF!).
This seems to be same: bug 92841

*** This bug has been marked as a duplicate of bug 86575 ***
Comment 6 Elmar 2015-08-10 08:21:45 UTC
Apologies for taking time to revert.

I cannot see how this is the same as bug 92481. (Note: that I am getting the same problem in LO 5.0.0.5. Also note that the problem did NOT occur in prior versions to 4.4.5.2)

It seems that the "names" issue was a red herring - I had saved a template with the broken named ranges. Humble apologies.

To repeat:
I have now started with an empty workbook.
It includes custom styles.
I type "abc" into sheet 1 cell A1.
I save it to ods format.
I save it to xls format
I save it to xlsx format
Reopen ods in Calc.
It is as I saved it.

Open xls in Calc
It is as I saved it

Open xlsx in Calc
the custom styles are no longer there, only the six basic styles.

(as a matter of interest, in LO 5 the styles in the sidebar are double spaced -  how can I change this back to single line spacing? they are also not formatted as in Writer. The same is true of the Style Picker in the toolbar - and there the font looks like 4 points - also not like Writer.)
Comment 7 Elmar 2015-08-10 08:22:47 UTC
Created attachment 117801 [details]
example file in ODS format
Comment 8 Elmar 2015-08-10 08:23:15 UTC
Created attachment 117802 [details]
example file in XLS format
Comment 9 Elmar 2015-08-10 08:23:39 UTC
Created attachment 117803 [details]
example file in XLSX format
Comment 10 Elmar 2015-08-17 05:44:40 UTC
Not sure if this helps, or gives a clue as to what might be wrong:

Yesterday, I saved a Writer odt to Word docx format (as much as I wish it were not so, most of industry and government are locked into MS, and I notice that MS has reneged on its commitment made years ago to open and save ODS, ODT, etc. Office 2010 recognises the format but every document I try to open is "corrupt" and cannot be repaired.)

When I tried to open the docx in Word2010 it did but again, no custom styles. I checked using the Manage Styles function, and there they were, but I could not make them visible in the Style Picker.

I cannot say if the same is true for the XLSX. There does not seem to be a "manage styles" function in Excel.
Comment 11 Matthew Francis 2015-08-19 11:21:17 UTC
Before the below commit, when saved as .xslx the styles come back as "Excel Built-in <name>" with no hierarchy. After, they're absent.

I note also that at the start of 4.4 master, the same styles retain their hierarchy when saved as .xls, but that's been broken since as well (a separate bug - will investigate separately)


    commit 1e68aabdeaacd8d4cac94d46925a55e2a93880d7
    Author:     Kohei Yoshida <kohei.yoshida@collabora.com>
    AuthorDate: Tue Aug 12 17:10:40 2014 -0400
    Commit:     Kohei Yoshida <kohei.yoshida@collabora.com>
    CommitDate: Tue Aug 12 22:05:36 2014 -0400
    
        Don't export builtinId of 54 or above.
    
        Change-Id: I5ab651e0c92ab032d892080f979aa4f53295a8b6
Comment 12 Matthew Francis 2015-08-19 11:35:52 UTC
XLS style hierarchy issue mentioned in comment 11 -> bug 93528
Comment 13 Robinson Tryon (qubit) 2015-12-13 11:13:15 UTC Comment hidden (obsolete)
Comment 14 Buovjaga 2016-03-22 18:47:30 UTC
*** Bug 98672 has been marked as a duplicate of this bug. ***
Comment 15 Ambrose Li 2016-04-28 16:21:38 UTC
This is still present in 5.0.5.2.

IMHO this is a serious problem, because the .xlsx file does not survive across a round trip from Excel to LibreOffice back to Excel, making LibreOffice useless when Excel is in the loop anywhere.
Comment 16 Elmar 2016-04-29 10:32:50 UTC
(In reply to Ambrose Li from comment #15)
> This is still present in 5.0.5.2.
> 
> IMHO this is a serious problem, because the .xlsx file does not survive
> across a round trip from Excel to LibreOffice back to Excel, making
> LibreOffice useless when Excel is in the loop anywhere.

I agree. I have to do this all the time. My workaround is to save it to xls (Excel 2003) and then if necessary to open that in Excel and then save it to xlsx (Excel 2007 and later).
Comment 17 Bartosz 2016-06-24 23:35:52 UTC Comment hidden (obsolete)
Comment 18 Bartosz 2016-06-24 23:35:52 UTC Comment hidden (obsolete)
Comment 19 Bartosz 2016-06-24 23:36:04 UTC Comment hidden (obsolete)
Comment 20 Elmar 2016-06-26 11:43:49 UTC
(In reply to Bartosz Kosiorek from comment #19)
> I would like to fix that issue.
> Could you please create .xlsx file with custom formatting via MS Excel and
> then open it with LibreOffice?
> After that save it into .xlsx format again, reopen and check if this issue
> is still there.
> 
> 
> I would like to compare two .xlsx files (correct one and incorrect). 
> It will dramatically help in resolving that issue.

done what you asked:
1. created "test1 created by excel.xlsx" using ms excel saved by ms excel
2.     saved "test1a created by excel saved by calc.ods" saved by calc
3.     saved "test1b created by excel saved by calc.xlsx" saved by excel
4. create "test2 created by calc.ods" using calc
5.     on opening has error, see log file "test2 error015520_01.xml"
6.     saved by excel as "test2a created by calc opened by excel saved by excel.xlsx"

hope that helps
Comment 21 Elmar 2016-06-26 11:44:41 UTC
Created attachment 125913 [details]
created by excel
Comment 22 Elmar 2016-06-26 11:45:27 UTC
Created attachment 125914 [details]
created by excel saved by excel
Comment 23 Elmar 2016-06-26 11:46:54 UTC
Created attachment 125915 [details]
created by excel saved by calc
Comment 24 Elmar 2016-06-26 11:47:45 UTC
Created attachment 125916 [details]
created byc calc
Comment 25 Elmar 2016-06-26 11:49:04 UTC
Created attachment 125917 [details]
opened in excel with error error log
Comment 26 Elmar 2016-06-26 11:49:50 UTC
Created attachment 125918 [details]
saved by excel after error correction
Comment 27 Bartosz 2016-06-27 14:49:36 UTC
*** Bug 100464 has been marked as a duplicate of this bug. ***
Comment 28 Bartosz 2016-06-28 09:17:36 UTC
*** Bug 100347 has been marked as a duplicate of this bug. ***
Comment 29 deri.jones 2016-07-14 12:11:44 UTC
This Bug can be reproduced just within Calc - no need to use Excel.

In Calc: in a document: create a custom style.

Save as ODS and close.
Re-open:  it is still there.

Save as XLSX and close.
Re-open:  style is gone!

It does not matter if the style is used in the document or not used.

My set up:

Version: 5.0.6.2
Build ID: 1:5.0.6-0ubuntu1
Locale: en-GB (en_GB.UTF-8)

on Ubuntu 15.
Comment 30 Xisco Faulí 2016-10-03 09:24:46 UTC Comment hidden (obsolete)
Comment 31 Timur 2017-04-28 06:57:27 UTC Comment hidden (obsolete)
Comment 32 Rob 2018-09-20 14:01:24 UTC
Possibly related I too had xlsx files that the alignments rendered correctly in Excel but wrong in LO if they originated from sources other than Excel.
i.e.
1. create xlsx in Excel, alignment all OK in both Excel and LO
2. create xlsx in non excel source, alignment OK in Excel, alignment wrong in LO

It turned out [in my case] the styles.xml had a difference,
- those files created in Excel included: applyAlignment="true" in the elements within the cellXfs section of styles.xml
- those files created outside Excel didn't include this item, (adding this fixed the problem*.)

(* in my case the "outside Excel" source was from a package called EPPlus, I patched my own copy to include the: applyAlignment="1" where necessary.)

Another way of looking at it:
Excel: styles default for applyAlignment is True (or 1) [ if allignment items are present]
LibreOffice: styles default for applyAlignment is False (or 0) unless explicitly set

so either:
(1) need to patch the originator of your xlsx files to include that applyAlignment="1"/"True" item in the styles.xml,
or 
(2)patch LibreOffice to change it's default to True.

- In my case #1 was much easier.
- The LibreOffice maintainers would need to decide if #2 is an appropriate / permanent fix and is so apply it.
Comment 33 Gabor Kelemen 2019-01-31 12:09:13 UTC
We checked this with recent versions, and it seems to be gone now. Cell styles are now imported and exported mostly correctly.

In particular, the problematic commit in comment #11 was fixed in: 

https://cgit.freedesktop.org/libreoffice/core/commit/?id=1e68aabdeaacd8d4cac94d46925a55e2a93880d7

as a bit of bibisecting on bibisect-win32-6.1 has shown.

*** This bug has been marked as a duplicate of bug 98074 ***