Way to select all plain text with regular expressions?

English
#1

So I have this long spreadsheet, and some of the text is colored. I want to use dark mode in Libreoffice, but the plain text is black so you can’t see it. Normally I would select all, and change font color to “automatic”, but if you do that you lose the colored text.

Is there a way to only select plain text, so that I can change the font color to automatic for it only?

Sorry to bother you, and thanks in advance.

EDIT the text in the cells that is colored is not in a cell that only has colored text. All of the text is plain text, except for the colored words. I do not have control over the styles, I have a spreadsheet already formatted.

#2

Set the font color to Automatic.
Assign a cell style to all cells that should have a different font color.

Cell Styles in Calc

1 Like
#3

There are not any “plain text” in a spreadsheet document. At least they are formatted by the Default Cell style.

I strongly suggest you to use the Styles instead of the manual (direct) formatting method.

1 Like
#4

See my edit.

#5

Please upload a small, .ods type sample file here.

#6

You didn’t add the information about your LibreOffice.
Menu/Help/About LibreOffice, there is an icon to copy it.
If you do not have 7.5 version, update to it, there was some improvements about dark mode.

#7

Hey if you guys don’t know how to select plain text with regular expressions, or if it’s not possible, just say so.

I asked a simple question, I don’t want to re-format anything. If I was going to do that, I would manually change the color. But there are too many cells.

Libreoffice version is 7.5.2.2

#9

As I wrote for you: there is not one character in an ODF document that is would be a “plain text”. Every content has - at least - a Style with many default properties. The color property maybe is “automatic” or “black” - for example - in the applied Style.
.
Please upload your SAMPLE file here.

#11

There is not Character style in the spreadsheet application. You can format a text (located in a cell) partially by manual formatting only.

Avoid to use this method - if it is possible.
You can reset these formatting properties by usage the CTRL-M

#12

@Frederico2 , in your example, there are three font colors: automatic, red, blue. There is no black font color.

#21

text.ods (18.6 KB)

#25
  1. Better use plain English. I’m not a “guy” in any sense you might think of.
  2. You are not the boss.
  3. You were told that “plain text” isn’t an applicable term in this case. If you are talking about cells that contain differently formatted text portions, you may mean those portions where all attributes match the CellStyle preset for the respective cells. This should be clearly stated.
  4. There is no way in Calc to select special text portions by means like F&R or by user code. Only during interactive editing you can select one special portion.
  5. Bypassing the restriction by user code is partly possible, but complicated. Not recommended!

[I did not study the *answers* below and the related comments.]

#27

  1. Here in NA “you guys” is used without reference to the sex of the person, in a plural form similar to ‘you all’. It is common to address groups of persons of either sex or both, or even animals, as ‘you guys’. ‘Dude’ is the same; while the word can be used to specifically mean a man, it is used for both sexes. I don’t care if people get offended, however, because people get offended no matter what you say!

  2. ‘Not the boss’. Was I being bossy? My question was simple, I didn’t know if it was possible or not.

  3. If people know what I mean, then I have successfully communicated, whatever the “terminology”.

4, 5) Ok.

#8

While handling of cells with colour improves (we can now filter and sort in 7.6) i don’t see a way to solve your problem with regular expressions. These will usually “see” only the text-representation of the contents.
.
If necessary I’d try this with macros and would have to find out how the segments are coded internally. So nothing “easy”.

#14

It is easy to make a macro to…

…in the content.xml subfile?
I don’t know, so I ask.

#15

My main concern would be the usually existing closure for the style, usually something like </style> and as styles can be nested I can not just delete the next tag. So I will need an xml-parser to do this right. Obviously LibreOffice has one in its source or on may use pythons libraries.
.
Second problem would be to access the representation of an open file, so better process not the file, where the macro is in…
.
However: As the answer by @LeroyG suggests: The closure of the style tags may be less important than I think.

#16

A tag can contain several attributes, for example:

<style:text-properties fo:color="#000000" fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"/>

Therefore, instead of removing the tag, the attribute

fo:color="#000000"

can be replaced with:

style:use-window-font-color="true"

An example of correcting a file (archive) using the Package service is given here. The same approach can be applied to content.xml. I can compose such a macro soon (if someone doesn’t do it sooner).
If the Calc file is open, then to replace the color with a macro, you need to go the other way, adjusting the contents of the cells. I will try to show an effective way for this (if there is interest from the forum participants).

1 Like
#17

It’s included there:

Adding a slash inside the start tag, like <br /> , is the proper way of closing empty elements in XHTML (and XML).

#18

So, let’s try this macro to replace black with automatic.
I spied on working with the XPathAPI service from @ms777 here.

Sub RemoveBlackColor() 
  Dim oPackage As Object, oDom As Object, oNode As Object, oXPath As Object, oNodeList As Object
  Dim oPackageFolder As Object, oPackageStream As Object, oOutputStream As Object, oTempFile As Object
  Dim filePath As String, content As String, arr, nReps As Long, i As Long
  
  filePath=ConvertToUrl("C:\Temp\test.ods") 
  
  oPackage=createUnoService("com.sun.star.packages.Package")
  oPackage.initialize Array(filePath)
  
  content="content.xml"
  oPackageStream=oPackage.getByHierarchicalName(content)
  oDom=createUnoService("com.sun.star.xml.dom.DocumentBuilder").parse(oPackageStream.inputStream)
  
  oXPath=createUnoService("com.sun.star.xml.xpath.XPathAPI")
  oNodeList=oXPath.selectNodeList(oDom.getDocumentElement, "//*[name()='style:text-properties']")
  
  For i=0 To oNodeList.length-1
    oNode=oNodeList.Item(i)
    If oNode.getAttribute("color")="#000000" Then
      oNode.attributes.removeNamedItem("color")
      oNode.setAttribute("fo:color", "")
      nReps=nReps+1
    End If 
  Next i

  If nReps=0 Then
    Msgbox "Replacement color not found"
    Exit Sub 
  End If    
  
  oTempFile=createUnoService("com.sun.star.io.TempFile")
  oDom.setOutputStream oTempFile.OutputStream
  oDom.start
  oTempFile.OutputStream.closeOutput
   
  oPackageFolder=oPackage.getByHierarchicalName("")
  oPackageStream=oPackage.createInstanceWithArguments(Array(False))
  oPackageStream.SetInputStream(oTempFile.InputStream)
  oPackageFolder.replaceByName(content, oPackageStream)
  
  oPackage.commitChanges
  Msgbox "Replacement done!"
End Sub
#19

BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message: .

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