2024-12-27

Previously 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:

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.

2024-12-27

Dark Mode and LibreOffice spreadsheets

LLMs improvement