Calc - How to remove the "no border" specification from Default style

stabiloboss · November 21, 2019, 6:36am

Hi, whenever I apply conditional formatting that specifies a background or font colour, cell borders disappear. This seems to be due to the fact that ultimately all styles inherit from Default, and Default specifies “no borders”, meaning that it actively removes borders. Thus, applying a conditional style will overwrite existing borders with “no borders”. I would like to remove this specification from Default style so that Default style and all inheriting styles would leave existing borders as they are, but I don’t know how. Any ideas?

Using LibreOffice 6.2.8.2

Thanks

-edit-

I’m not referring to the borders that form the grid, I’m referring to borders that I manually add with format > borders, eg when drawing a table, or cell borders added to visually group columns or rows together. I would like those borders to remain when i apply a conditional format to cells that have borders added to them.

To see what I mean:

add borders to a cell (format cells > border)
add conditional formatting to the cell with the rule: when cell content = “test” ==> apply style “Default”
write “test” in the cell
the borders you just added are gone

I’ve uploaded an example: example.ods

gtomorrow · November 21, 2019, 6:50am

Have you tried modifying “Default Style” to include borders?

stabiloboss · November 21, 2019, 9:14am

Not sure how that would help, or maybe I don’t understand you correctly, Currently the Default style overwrites borders by deleting them (“no border”). If I include borders in the Default Style it would still overwrite existing borders, but now with its own set of borders…

JShal · December 21, 2020, 4:23pm

To remove the override of borders in the default style:

Save your document as an xml file (.fods extension). Then open in Notepad++ or equivalent and edit the line of the default style. Remove the fo:border=“none” part in the line.

<style:style style:name="Default" style:family="table-cell">
<style:table-cell-properties fo:border="none" loext:vertical-justify="auto"/>
[...]
</style:style

Save your file and reopen in libreoffice. Same principle also works for other overridden items.

It is not user friendly to have to resort editing the xml file itself. But your problem is at least solved!

ajlittoz · December 21, 2020, 4:48pm

There is no use of such a hack if you use correctly the styles, i.e. without direct formatting.

The dialogn for Format>Conditional>Condition declares the name of the style to apply when the condition is met. If this style contains a border, the border will be drawn, whatever the definition of Default.

LeroyG · December 21, 2020, 4:52pm

@JShal answer works in LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

JShal · December 22, 2020, 6:29pm

Well, the problem is that if you have direct formatting like a border applied to a cell, and your default style is not “default” anymore, whatever you do, the conditional formatting will always change this border (e.g. remove (part of) the border or show a border). This is the problem stabiloboss is describing.

It’s easy to replicate this issue:

edit your default style by going the the border tab.
click “all borders” (the 2nd icon).
click OK.

Now edit the default style again
4) set in border tab “no borders” (the 1st icon). In the first tab the default style contains “no border”.
5) Press OK again. This “no border” override cannot be removed any more.

From now on, all conditional formatting will mess up the direct formatting of borders in your sheet. I tested this on version 6.0.7.3.

Maybe I overlook something obvious in the user interface (or resolved in update)? If you know another solution to this problem please share!

Halfwit · June 3, 2023, 7:17am

JShal’s workaround solution solved this problem.
Stabiloboss’s question is well put. I had the same problem. I have a spreadsheet which uses borders (not gridlines) to improve its readibility and conditional formatting to accentuate errors. The conditional formatting obliterates the pre-existing borders whenever the defined condition exists. Although I am unfamiliar with xml and found it difficult to locate the pertinent lines in Notepad (not Notepad++), this did correct the problem.
A better solution would be welcome.

mariosv · November 21, 2019, 7:57am

Has nothing to do with the default style border, what it’s behind the background color it’s the grid.

There is an option:
Menu/Tools/Options/LibreOffice calc/View - Visual aids - Grid lines - Show border in colored cells.

Edited 20191122

Editing your sample with 16 styles can be solved.

16 conditions with 16 styles

stabiloboss · November 21, 2019, 8:41am

Hmpf, I see I wasn’t all that clear

I edited my question in an attempt to be more precise. I was not referring to the grid, I was referring to borders that I manually add with format > borders

But thanks for replying!

mariosv · November 21, 2019, 1:27pm

Please attach a sample file showing what you want achieve, If I’m right the way is having more CF with the different borders.

stabiloboss · November 21, 2019, 4:38pm

Hi, I’ve uploaded an example.

You suggested multiple CF with different borders in order to maintain the looks of the table. This simple example already contains 48 unique combinations of borders, so I would have to make 48 variations of the same condition. That doesn’t sound very practical.

It seems to me that the problem would be solved if there was a way to remove the specification “no borders” from Default Style so that it would no longer remove existing borders.

mariosv · November 21, 2019, 6:52pm

Or you don’t need to inherit from Default style, on the styles use in CF you can change from what style they are inherited.

stabiloboss · November 22, 2019, 6:20am

I’m impressed with your work on the example I uploaded! But it still needs 16 styles for something that should actually only take one if Default Style wouldn’t actively remove borders.

Not inheriting from Default Style doesnt really work for me because all styles ultimately inherit from Default Style and thus remove borders. Unless they explicitly set borders. But then my simple example needs 16 styles and your analytical powers to set just one condition.

I still think the most practical solution would be to remove the “no borders” specification from Default Style, OR to create a blank style that doesn’t inherit from anything. But I don’t know how to do either. Is it possible at all?