It’s could sometimes be desirable to explain the value of a spreadsheet cell with some kind of footnote, and I’ve recently been battling the apparent lack of such functionality in LibreOffice Calc. By hijacking the annotations functionality (aka “comments”) with the help of a small macro it is possible to turn cell annotations into a numbered list of footnotes which updates automagically as these are added, edited or removed. Writing LO Basic is not my day-job, so forgive me if my solution is unnecessarily clumsy or contains outright errors - let me know if you see any obvious ways this can be improved! Here’s the macro code:
Function ClearOrphans(oDraw As Object)
' This helper function loops through all shapes on the provided DrawPage and removes
' any TextShapes that belong to a cell that has no annotation (comment). It takes care
' of removing footnote symbols from cells which had an annotation that has been removed.
Dim oShape As Object
Dim vAddr As Variant
Dim sSheet As String
Dim oSheet As Object
Dim oCell As Object
' Loop through all shapes on the DrawPage
For Each oShape in oDraw
' Check if the shape has a name (our footnote symbols always do) and is a TextShape
If oShape.Name <> "" And oShape.SupportsService("com.sun.star.drawing.TextShape") Then
' Split the (absolute) name into sheet name and cell address
vAddr = Split(oShape.Name,".")
' Remove the initial $ in the sheet name
sSheet = Right(vAddr(0), Len(vAddr(0))-1)
' Get the sheet with that name (if any)
oSheet = ThisComponent.Sheets.getByName(sSheet)
' Find the matching cell on the sheet
oCell = oSheet.getCellRangeByName(vAddr(1))
' If the cell doesn't have any annotaion, delete the TextShape (symbol)
If oCell.Annotation.String = "" Then
oDraw.remove(oShape)
End If
End If
Next
End Function
Function FindShape(oDraw As Object, sName As String) As Object
' This helper function searches a given DrawPage for shapes with the name sName.
' It is called from the Annotate sub to find an existing TextShape for a given
' annotation, if one exisist. Returns Nothing if none found.
' Loop through all shapes in the given DrawPage
For Each oShape in oDraw
' Check if the shape's name matches the sName search string
If oShape.Name = sName Then
' We found a matching shape, return it
FindShape = oShape
Exit Function
End If
Next
' No matching shape could be found, return Nothing
FindShape = Nothing
End Function
Sub Annotate(oEvent)
' This function loops through all annotations (aka "comments") on a given spreadsheet
' and turns them into footnotes. Each annotated cell gets a background symbol with a
' number that references the footnote. The sub should be called from the ContentChanged
' sheet event, which can be set with "Sheet" > "Sheet Events..." > "Content Changed".
Dim oSheet As Object ' A spreadsheet containing annotations
Dim oDraw As Object ' The spreadsheet's "DrawPage", in which we will draw the symbols
Dim oAnnos As Object ' The spreadsheet's annotations (aka "comments")
Dim oFoot As Object ' The cell on the spreadsheet where footnotes should be printed
Dim aFoot() As String ' An array of strings that will contain the footnotes
Dim sFoot As String ' A string that will contain the footnotes
Dim oShape As Object ' The TextShape used to draw the symbols
Dim iCount As Integer ' The annotation counter, which is used for the symbols
' Get the first spreadsheet
oSheet = ThisComponent.Sheets.getByIndex(0)
' Get the spreadsheet's "DrawPage"
oDraw = oSheet.getDrawPage()
' Get the spreadsheet's annotations (aka "comments")
oAnnos = oSheet.getAnnotations()
' Get the cell defined for the footnotes (set this with "Data" > "Define Range")
oFoot = oSheet.getCellRangeByName("Footnotes")
' Initialise the annotation counter
iCount = 1
' Loop through all annotations on the sheet
For Each oAnno in oAnnos
' Try to find an existing symbol for this annotation
oShape = FindShape(oDraw, oAnno.Parent.AbsoluteName)
' If no symbol was found, create a new one
If oShape Is Nothing Then
' Create a new instance of a TextShape, to be the symbol for this annotation
oShape = ThisComponent.createInstance("com.sun.star.drawing.TextShape")
' Set the symbol's name to the cell's absolute name, so we can find it again
oShape.Name = oAnno.Parent.AbsoluteName
' Add the symbol to the DrawPage
oDraw.add(oShape)
' Set the symbol to the annotation's number, with some right padding
oShape.setString(iCount & " ")
' Anchor the symbol to the parent cell
oShape.Anchor = oAnno.Parent
' Set the symbol to the same size as the cell
oShape.setSize(oAnno.Parent.Size)
' Resize the symbol when the cell is resized
oShape.ResizeWithCell = true
' We want the symbol on the right hand side of the cell
oShape.setPropertyValue("ParaAdjust", com.sun.star.style.ParagraphAdjust.RIGHT)
' Set the font size of the symbol
oShape.CharHeight = 9
' Move the symbol to the cell's background, so it can't be selected
oShape.LayerID = 1
' An existing symbol was found, update it's contents (in case numbers have changed)
Else
oShape.setString(iCount & " ")
End If
' Add the annotation's text to the list of footnotes
ReDim Preserve aFoot(iCount-1)
aFoot(iCount-1) = sFoot & iCount & ": " & oAnno.String
' Increment annotation number
iCount = iCount + 1
Next
' Remove orhpaned symbols
ClearOrphans(oDraw)
' Print out the list of footnotes in the oFoot cell
oFoot.setString(Join(aFoot, Chr(13)))
End Sub
To use this macro you need to copy the code into the macro editor for your spreadsheet:
Then you need to tell it to run the macro on the Content Changed event:
Finally you need to define a cell range as “Footnotes”, where your comments will be shown as footnotes:
Some caveats:
-
The
Content Changedevent does not fire when a comment is inserted, and therefore the footnotes do not update either - you need to change the value of some cell for this to happen*. The same is true for when you modify the contents of a comment. Strangely the event does fire when you delete a comment, so that works at least. -
The footnote indicator symbols are placed below the cell’s background layer, so they will be hidden if you set a cell background colour. I would love to find a way to place them above the background without them taking over the select action when you click on a cell with the mouse (which makes it impossible to edit cells with the mouse).
-
The
ClearOrphans()function in particular is pretty rough; it has no checks of any kind and will blindly try to do stuff that will cause an error if the necessary objects do not exist. This is particularly likely if your spreadsheet contains other TextShape drawing objects that have been named. I tried to add some basic checks but my LO Basic fu is very weak and the checks themselves ended up causing errors
It also rather stupidly fetches the sheet for each DrawPage object, all though they’re all on the same sheet. -
You might think you should be able to edit a footnote in the cell where they are all listed, and I agree that would be nice - but my macro currently does not allow this. If you try to edit the footnote text that will just trigger a
Content Changedevent and the text will be regenerated. Instead you need to edit the contents of each cell’s comment to change the footnote, and then trigger aContent Changedevent with some other edit*. -
The footnote indicator (which I call “symbol” in the macro) needs space to live on the right of the cell; easiest way to provide this is to increase the cell padding for the whole sheet.
-
The comment/annotation indicator (small triangle in the top right cell corner) cannot be moved, hidden or styled; you’ll need to adjust the cell padding and footnote indicator size and padding (in the macro code) so they coexist peacefully.
-
I have not been able to get rid of the redundant “Author” and “Date” fields in the comment popups, which looks a bit meh.
-
This forum does not appear to have colour coding support for any programming languages. I had to use the “preformatted text” option to display the code - apologies if it looks a bit odd.
Finally, some screenshots:
Have fun!
*) One easy way to trigger the Content Changed event is to select an empty cell and press the [Delete] key on your keyboard.
