How to preserve custom styles between Excel and Calc

[mildlyodd]

I need to use Excel and Calc in parallel, continually working on spreadsheets created by the two office suites. I find that the custom styles get lost when saving in Calc.

1. I created and saved an xlsx document in Excel with both the MS pre-defined styles, modified pre-defined styles and custom styles.

2. Upon opening it in Calc, so far so good: I can see all of the styles in the Calc “Styles and Formatting”/“Custom Styles”. The Excel predefined ones, modified or otherwise, have been renamed with the prefix “Excel Built-in …”. The styles appear to work, i.e. they change text to the correct colours and previously written text formatting is preserved.

3. I save the file with a new name from within Calc.

4. When I open the new saved version in Calc all of the Custom Styles are no longer listed, nor present in “Hidden Styles”. Also, cell text that had previously had these styles has lost the backgrounds but mainly kept the font colours.

5. When I open the same file from step 3 in Excel the sheet opens with the Excel default predefined colours only and no trace of the modified or custom styles. The previously written text has lost its formatting as per step 4.

It seems as if the problem must lie at step 3, somehow its not saving the changes.

THE QUESTION: Have you experienced this problem and what did you do about it?

I use:

Excel 14.07116.5000 (32 bit) (part of Office Home 2010)  on Windows 7 

Calc on Libre Office 5.0.3.2 on Linux Mint (64 bit).

[petermau]

Are you saving the file as a .ods? Otherwise, won’t the .ods styles be lost?

[mildlyodd]

No, as an “.xlsx”, so that it can be read by both Calc and Excel. I’m new to Calc, are you saying that it makes no attempt at all to bother to save styles in the xlsx format, only in its native “.ods” type? This would seem surprisingly partisan, is there a way around it?

[petermau]

Calc uses the international standard .ods files to store spreadsheets. This is not a LibreOffice Calc standard. If you save as a .xlsx file, you are warned that some formatting information could be lost. This is not surprisingly partisan, but a restriction in style support in Excel. We normally suggest using .ods and saving in .xls or .xlsx for giving to a user who does not have LibreOffice installed.

[mildlyodd]

I have to use the two. Are there any ways of getting Calc to preserve font, font colour and background formatting in a saved .xlsx file? It doesn’t have to use custom styles.