![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Dark Mode and LibreOffice spreadsheets
Friday, December 27th, 2024 04:43 amPreviously when I tried out dark mode for various applications on my laptop, it made text seem less coherent to me, so I didn't use it.
But recently the normal (light) mode started bothering my eyes (they've been getting very tired & dry & achy), so I switched both my work and personal laptops into dark mode in the Windows settings. I've been switching individual applications into dark mode too, where possible.
My spreadsheets have been giving me difficulty. They are LibreOffice Calc ODS files. For whatever reason, most of the text in them is set to black, not "automatic". So when I switch to the "LibreOffice Dark" color scheme, I end up with black text on a black background, impossible to read.
I have many spreadsheet files, with many tabs (sheets) in each file.
There ought to be an easy way, a script, to update multiple spreadsheet files at once to convert all text, current and future, to the "Automatic" color. But I haven't found such a script yet.
Doing it manually involves going it each tab in each file, selecting all the cells, and updating the text color. And then doing something else to change the default for new cells too. I did the first step for two spreadsheets. Then I noticed that text I enter in a row below the last existing row ends up black on black again. That's why I think the 2nd step is required too (and I don't even know how to do that step manually yet).
I asked Gpt4o-mini (via DuckDuckGo) for assistance. It suggested creating a macro, and gave me sample code for the macro. The sample code was giving me an error in the part for finding all ODS files in a specified folder. I worked through that error with Gpt4o-mini's assistance. Now the part for updating each file is giving me an error (probably the first of several). Sigh. I don't want to spend my vacation time trying to code and debug a spreadsheet macro. Debugging the macro isn't as easy as debugging Java code in Eclipse. You can't just select a variable in the code and see all the details for it.
Surely such a macro or script already exists somewhere. I can't be the first person facing this problem.
I get stuck in all these problems. How can i make progress. How can I ever get to sleep at a good time. How can I ever catch up.
UPDATED:
I tried asking Copilot. There were a few issues with the first code it suggested too, but we fixed them. This logic seems to work well to update the text color to "Automatic" in all ODS files in the specified folder:
That is for both the first and 2nd steps.
For how to do the 2nd part manually, this answer works:
To ensure that all cells, including those outside the used range, default to Automatic color, you can modify the default cell style in LibreOffice Calc. Here’s how you can do it manually:
Open LibreOffice Calc.
Go to Format > Styles and Formatting (or press F11).
In the Styles and Formatting window, click on the Cell Styles icon (second icon from the left).
Right-click on Default and select Modify.
In the Cell Style dialog, go to the Font Effects tab.
Set the Font Color to Automatic.
Click OK to apply the changes.
But recently the normal (light) mode started bothering my eyes (they've been getting very tired & dry & achy), so I switched both my work and personal laptops into dark mode in the Windows settings. I've been switching individual applications into dark mode too, where possible.
My spreadsheets have been giving me difficulty. They are LibreOffice Calc ODS files. For whatever reason, most of the text in them is set to black, not "automatic". So when I switch to the "LibreOffice Dark" color scheme, I end up with black text on a black background, impossible to read.
I have many spreadsheet files, with many tabs (sheets) in each file.
There ought to be an easy way, a script, to update multiple spreadsheet files at once to convert all text, current and future, to the "Automatic" color. But I haven't found such a script yet.
Doing it manually involves going it each tab in each file, selecting all the cells, and updating the text color. And then doing something else to change the default for new cells too. I did the first step for two spreadsheets. Then I noticed that text I enter in a row below the last existing row ends up black on black again. That's why I think the 2nd step is required too (and I don't even know how to do that step manually yet).
I asked Gpt4o-mini (via DuckDuckGo) for assistance. It suggested creating a macro, and gave me sample code for the macro. The sample code was giving me an error in the part for finding all ODS files in a specified folder. I worked through that error with Gpt4o-mini's assistance. Now the part for updating each file is giving me an error (probably the first of several). Sigh. I don't want to spend my vacation time trying to code and debug a spreadsheet macro. Debugging the macro isn't as easy as debugging Java code in Eclipse. You can't just select a variable in the code and see all the details for it.
Surely such a macro or script already exists somewhere. I can't be the first person facing this problem.
I get stuck in all these problems. How can i make progress. How can I ever get to sleep at a good time. How can I ever catch up.
UPDATED:
I tried asking Copilot. There were a few issues with the first code it suggested too, but we fixed them. This logic seems to work well to update the text color to "Automatic" in all ODS files in the specified folder:
Sub ChangeTextColorInFolder()
Dim oDoc As Object
Dim oSheet As Object
Dim oCell As Object
Dim oRange As Object
Dim oCursor As Object
Dim sFolderPath As String
Dim sFileName As String
Dim i As Integer
Dim oSimpleFileAccess As Object
Dim aFiles() As String
Dim args(0) As New com.sun.star.beans.PropertyValue
' Set the folder path
sFolderPath = "C:\path\to\your\folder\"
' Get the SimpleFileAccess service
oSimpleFileAccess = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
' Get the list of files in the folder
aFiles = oSimpleFileAccess.getFolderContents(ConvertToURL(sFolderPath), False)
' Loop through all the files in the folder
For i = LBound(aFiles) To UBound(aFiles)
sFileName = aFiles(i)
' Check if the file is an ODS file
If Right(sFileName, 4) = ".ods" Then
' Open the ODS file in hidden mode
args(0).Name = "Hidden"
args(0).Value = True
oDoc = StarDesktop.loadComponentFromURL(sFileName, "_blank", 0, args)
' Loop through all the sheets in the document
For Each oSheet In oDoc.Sheets
' Get the used range of the sheet
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(False)
oRange = oSheet.getCellRangeByPosition(0, 0, oCursor.RangeAddress.EndColumn, oCursor.RangeAddress.EndRow)
' Loop through all the cells in the range
For j = 0 To oRange.Rows.Count - 1
For k = 0 To oRange.Columns.Count - 1
oCell = oRange.getCellByPosition(k, j)
' Set the text color to Automatic
oCell.CharColor = -1
Next k
Next j
Next oSheet
' Modify the default cell style to automatic color
Dim oStyle As Object
oStyle = oDoc.StyleFamilies.getByName("CellStyles").getByName("Default")
oStyle.CharColor = -1
' Save and close the document
oDoc.store()
oDoc.close(True)
End If
Next i
' Show a message box to indicate completion
MsgBox "Macro completed successfully!", 64, "Success"
End Sub
That is for both the first and 2nd steps.
For how to do the 2nd part manually, this answer works:
To ensure that all cells, including those outside the used range, default to Automatic color, you can modify the default cell style in LibreOffice Calc. Here’s how you can do it manually:
Open LibreOffice Calc.
Go to Format > Styles and Formatting (or press F11).
In the Styles and Formatting window, click on the Cell Styles icon (second icon from the left).
Right-click on Default and select Modify.
In the Cell Style dialog, go to the Font Effects tab.
Set the Font Color to Automatic.
Click OK to apply the changes.
