Created attachment 117553 [details]
file created using Calc, which I have then saved as XLS and XLSX
Build ID: 40m0(Build:2)
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?
Created attachment 117554 [details]
Here is the broken XLSX.
Created attachment 117555 [details]
saved XLS that looks fine
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).
looks like it's the same problem
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 ***
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 220.127.116.11. Also note that the problem did NOT occur in prior versions to 18.104.22.168)
It seems that the "names" issue was a red herring - I had saved a template with the broken named ranges. Humble apologies.
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.)
Created attachment 117801 [details]
example file in ODS format
Created attachment 117802 [details]
example file in XLS format
Created attachment 117803 [details]
example file in XLSX format
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.
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)
Author: Kohei Yoshida <email@example.com>
AuthorDate: Tue Aug 12 17:10:40 2014 -0400
Commit: Kohei Yoshida <firstname.lastname@example.org>
CommitDate: Tue Aug 12 22:05:36 2014 -0400
Don't export builtinId of 54 or above.
XLS style hierarchy issue mentioned in comment 11 -> bug 93528
Migrating Whiteboard tags to Keywords: (bibisected)
*** Bug 98672 has been marked as a duplicate of this bug. ***
This is still present in 22.214.171.124.
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.
(In reply to Ambrose Li from comment #15)
> This is still present in 126.96.36.199.
> 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).
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.
(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
Created attachment 125913 [details]
created by excel
Created attachment 125914 [details]
created by excel saved by excel
Created attachment 125915 [details]
created by excel saved by calc
Created attachment 125916 [details]
created byc calc
Created attachment 125917 [details]
opened in excel with error error log
Created attachment 125918 [details]
saved by excel after error correction
*** Bug 100464 has been marked as a duplicate of this bug. ***
*** Bug 100347 has been marked as a duplicate of this bug. ***
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:
Build ID: 1:5.0.6-0ubuntu1
Locale: en-GB (en_GB.UTF-8)
on Ubuntu 15.
Adding Cc: to Kohei Yoshida
(In reply to Bartosz from comment #19)
> I would like to fix that issue.
Please remove yourself from "Assignee:2 if you're not working on this.
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.
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
(1) need to patch the originator of your xlsx files to include that applyAlignment="1"/"True" item in the styles.xml,
(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.
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:
as a bit of bibisecting on bibisect-win32-6.1 has shown.
*** This bug has been marked as a duplicate of bug 98074 ***