Border changes unable to save in xlsx

English
#1

Hello folks, this is an old problem from an earlier question here https://ask.libreoffice.org/t/autosum-and-borders-in-calc/107332.

Basically, when changing the border style from the standard thin line to medium or anything else such as double line it fails to save, at least in xlsx format.

If saving in ods all is good, no problems at all - before I move over to ods just wanted to check there are inherent limitations with xlsx format when using Calc, rather than my own settings.

Also, if switching over to ods can one just change the file extension to from xlsx to ods or is it better to open each one and then re-save?

Thanks

#2

If you want to switch to ODS, you should only work with copies of your files.

How to open files from MS-Office 2007 or 2010 (.DOCX, .XLSX,…)?

#3

Have you made the line thickness sufficient?
My version of Excel 2010 does not support many border styles so some get changed. Maybe Office 365 or 2024 has support for more styles?
Double certainly works
Borders.xlsx (32.5 KB)

#4

Hrbrgr - Yep, no problem with ods files at all, coming from Windows I do have a ton of xlsx files so chose to stick with it rather than reformat or change over.

When you say ‘copies’ that would entail duplicates of each file, no ?

I would rather just have the one file format, change this over in global preferences and that’s it, oddly enough I hardly send out any Calc files so compatibility is not a problem, for the very odd occasion I do can double up then delete the xslx to avoid confusion.

#5

EarnestAl, borders are there once applied although the medium is no longer in drop down menu on Calc, instead have to go into format cells mode, from there all the changes from fine to bold can be added.

Problem is with the saving, re-open file and its all gone but that’s okay - what I can do is open each file when working and re-save in ods remembering to delete the xslx copy afterwards.

I think there maybe a few extra features available too in ods such as thicker border styles in the menu which would be great, going into properties is a bit of a faff each time.

Cheers

#6

And that is what cell styles are for. Create a cell style with the borders you want. After that you can select a range and double click the relevant cell style to apply it.
.

BTW I wouldn’t delete your xlsx files, a newer version of LibreOffice might have an improved import filter

#7

Thanks for the tip, will give that a try.

If ods is as reliable as say xlsx then there’s no real reason to keep both, how reliable is it ?

Various responses on Google suggest ods is the way to go being an open standard, any downsides i.e its not one way you can always re-save in xlsx subject to losing some formatting.

P.S can’t figure out how to quote your post EarnestAl

#8

There are probably fewer differences between .xlsx and .ods than there are between .docx and .odt. I know that if you keep round-tripping between Word and Writer you can create problems in your files as only the parts common to both are retained.

Always work in the native format of a program but you probably know that already. I was suggesting to keep the original documents in case you subsequently found you needed to go back to it; storage is cheap.

QuoteOtherComment

#9

Changing only extensions is usually not working, as it only determines wich program is called to handle the file. (One exeption is adding .zip to LibreOffice-Files, as well as .docx, .xlsx, as the files are actually zip-archives.). No conversion/translation takes place by changing names. Even Harry Potter has to learn spells for this.
.
If you need to convert a larger number of files there is a wizard for this and it can also done on command-line.
https://help.libreoffice.org/latest/en-US/text/shared/autopi/01130000.html?&DbPAR=SHARED&System=WIN

It depends on your use-case, if you really need to convert all files or can simply use/convert them, if needed. LibreOffice will usually warn you, if you try to save in other formats (unless you click the notificationb to “don’t ask again”). In some cases I have set the old files to read-only in the OS, so nobody can save to them, but users can load the files and copy from them.

And as always: Keep backups of your old files. You may not see all
errors made when you convert

1 Like
#10

Thanks Earnest Al, that helps along with Wanderers post below.

Blockquote

#11

Blockquote

Fab, that covers everything, much appreciated.

#12

As a quick footnote and reminder; changing file extension to ODS will result in regular icons being replaced with document contents, at least on LMDE.

It looks awful and very nonuniform, there is a workaround but I try where possible to stick to a vanilla install as OS on more than one machine, for the moment sticking with xslx - appreciate all the replies which allows more options.

#13

So the workflow should be to open the xlsx, Save As [SomeName].ods and continue to work on that. If you ever need to share as xlsx (less likely nowadays) do Save a Copy.
This way your original xlsx is kept unaltered. There is only one conversion; your proposal results in a conversion every time you save because Calc converts from xlsx to Calc format on opening and then converts to xlsx for save.

Changing just the extension is a recipe for confusion.

#14

Cheers EarnestAl, that was the proposed route but the icons thing is a deal breaker,showing the contents if its an image works okay but for a spreadsheet looks like the file is damaged or missing an associated program.

Libre Office - No icons on saved files

Since using Linux some 15 years ago along with LO, never experienced a problem with file formats they all seem interchangeable without issue, that said mine are fairly basic with formulas, borders, fixed headers and a few other bits thrown in…

The one thing that does impact Calc with various formats is fonts, which can have a dramatic impact on performance as LO substitutes its own, but that’s another thing altogether…

Cheers,

#15

Just go into Tools > Options > LibreOffice > Advanced and search for thumbnail.
Under org.openoffice.Office.Common, find Save.
Double click Document GenerateThumbnail to change from true to false

Far less risky than multiple conversions.

#16

EarnestAl, I will give that a go, that sounds much simpler than my original solution.

Cheers

#17

And choose Open Expert Configuration (in case it wasn’t obvious)

1 Like
#18

Okay, some good news and not so good news : )

Good news first, EarnestAl suggestion worked like a peach…very easy to implement across a couple of machines too…

…and the not so good news, editing the ‘style sheet’ as mentioned above whilst applying the border reverts back again to thin line each time, this thread highlights the issue although mine is thin (0.75)as opposed to medium (1.0) which I would like as the default.

In summary, I would just like to change the default line thickness from thin to medium (plse see original post)

Calc default border thickness change

btw, this is in ods format

#19

This is a quote from the last post on above thread, it’s the nub of my question

Calc default border thickness change - #10 by erAck

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.