Currently I use LO 26.2.4.2 and Cinnamon 22.3. Changing last year from Excel I’ve been able to convert all macros and even enhance some. But one task remains, chart annotation. Shown is a now old image of what was acheived in Excel and all was simply done. In an effort to replicate, open source AI has suggested many solutions many entitled “The Complete, Fully Corrected, & Error-Free Code” but result in system hangs, chart removal, chart downsizing, no datalabels drawn, datalabels shown off plot area, incorrect vertical alignment, all datalabels bunched under one event date and the last effort shows the first datalabel(event) 7 months later than the real date and the last datalabel 7 months early. No suggestions attached datalabels to the lowest data series. AI explained how LO uses a different system to draw charts and it is complex.
With numerous failures the question has to be asked can what was once done be replicated in LO? If not, then I am prepared to compromise.
In research I discovered this topic has received little ‘official’ attention. Documentation shows how to manually attach single and multiple labels but that is not appropriate here.
Has anyone found similar issues and a solution or used an alternative?
Things to know:
Events are listed against dates on another sheet in a column.
So far, there are only 10 events listed in a period starting 2nd January 2015 and ending 31st December 2027.
Other charts on different sheets use the same events.
All date columns contiguously list weekdays only.
Chart dimensions and position remain constant on each sheet but vary sheet to sheet.
All charts are chart2.
Excel Chart
LO Chart
edited to remove data source
The latest code to do that:
Sub ANNOTATE_EVENTS()
Dim oDoc As Object: oDoc = ThisComponent
Dim oSheet10 As Object: oSheet10 = oDoc.getSheets().getByName("FX_Rates_2")
Dim oSheet12 As Object: oSheet12 = oDoc.getSheets().getByName("Gold_Fixes_1")
Dim oDrawPage As Object: oDrawPage = oSheet12.getDrawPage()
Dim oCellRange As Object
Dim sCellValue As String
Dim vDataArray As Variant
Dim nCount As Long, i As Long, x As Long, y As Long
Dim lrB10 As Long, lrB12 As Long
Dim vNoteableEvents() As Long
Dim sEventText As String
''create NoteableEvents Array
With oSheet10 ''FX_Rates_2
lrB10 = Get_Last_Row_With_Data(1, 10)
oCellRange = .getCellRangeByPosition(58, 12, 58, lrB10-1)
vDataArray = oCellRange.getDataArray()
nCount = 0
For i = LBound(vDataArray) To UBound(vDataArray)
sCellValue = Trim(CStr(vDataArray(i)(0)))
If sCellValue <> "" Then
ReDim Preserve vNoteableEvents(nCount)
vNoteableEvents(nCount) = i
nCount = nCount + 1
End If
Next i
End With
''exit if no recorded events
If nCount = 0 Then
MsgBox "There are no events recorded in this period."
Exit Sub
End If
''extract chart visual dimensions
Dim oChartsContainer As Object
Dim oChartWrapper As Object
Dim oChartShape As Object
Dim chartLeft As Long, chartTop As Long, chartWidth As Long, chartHeight As Long
Dim totalRowsCount As Long
oChartsContainer = oSheet12.getCharts()
oChartWrapper = oChartsContainer.getByIndex(0)
totalRowsCount = lrb10-12
''get primary OLE2 chart shape properties
For i = 0 To oDrawPage.getCount() - 1
oChartShape = oDrawPage.getByIndex(i)
If oChartShape.supportsService("com.sun.star.drawing.OLE2Shape") Then
chartLeft = oChartShape.getPosition().X
chartTop = oChartShape.getPosition().Y
chartWidth = oChartShape.getSize().Width
chartHeight = oChartShape.getSize().Height
Exit For
End If
Next i
''push chart to background plane so new lines draw in front of it
oChartShape.ZOrder = 0
''establish explicit mathematical bounds of inner data graph grid frame
Dim plotLeft As Long, plotTop As Long, plotWidth As Long, plotHeight As Long
plotLeft = chartLeft + (chartWidth * 0.11)
plotTop = chartTop + (chartHeight * 0.09)
plotWidth = chartWidth * 0.81
plotHeight = chartHeight * 0.72
''persistent layout trackers allocated outside loop passes
Dim aLinePoints(1) As New com.sun.star.awt.Point
Dim oShapePos As New com.sun.star.awt.Point
Dim oShapeSize As New com.sun.star.awt.Size
Dim oLeaderLine As Object
Dim oTextShape As Object
Dim targetPointX As Long, targetPointY As Long
''systemic multi-stack collision lookback engine trackers
Dim aAllocatedX() As Long
Dim aAllocatedYOffsets() As Long
Dim nAllocatedCount As Long
nAllocatedCount = 0
''remove previous datalabels and leaderlines
Dim oShape As Object
For x = oDrawPage.getCount() - 1 To 0 Step -1
oShape = oDrawPage.getByIndex(x)
If oShape.Name = "" Then
oDrawPage.remove(oShape)
End if
Next x
''process individual annotations
For x = 0 To UBound(vNoteableEvents)
sEventText = Trim(CStr(vDataArray(vNoteableEvents(x))(0)))
'calculate precise locations based purely on chronological cell sequence,
'completely removing the 18-month offset drift and reverse order bugs.
targetPointX = plotLeft + CLng((vNoteableEvents(x) / totalRowsCount) * plotWidth)
'drop target point directly to midline profile height of your graph layout
targetPointY = plotTop + (plotHeight / 1) ''originally (plotHeight / 2)
'pixel proximity multi-stack engine lookback
Dim currentYOffset As Long
currentYOffset = 1000 '' originall 500 = base clearance depth below data nodes
For y = 0 To nAllocatedCount - 1
If Abs(targetPointX - aAllocatedX(y)) < 3600 Then
If aAllocatedYOffsets(y) >= currentYOffset Then
currentYOffset = aAllocatedYOffsets(y) - 600 ' Stack up by 6.0mm steps
End If
End If
Next y
ReDim Preserve aAllocatedX(nAllocatedCount)
ReDim Preserve aAllocatedYOffsets(nAllocatedCount)
aAllocatedX(nAllocatedCount) = targetPointX
aAllocatedYOffsets(nAllocatedCount) = currentYOffset
nAllocatedCount = nAllocatedCount + 1
''build canvas graphics segment layer
''create and position leaderline
oLeaderLine = oDoc.createInstance("com.sun.star.drawing.PolyLineShape")
oDrawPage.add(oLeaderLine)
ReDim aLinePoints(1) As New com.sun.star.awt.Point
aLinePoints(0).X = targetPointX
aLinePoints(0).Y = targetPointY
aLinePoints(1).X = targetPointX
aLinePoints(1).Y = targetPointY + currentYOffset
oLeaderLine.Polygon = aLinePoints()
''format leaderline
With oLeaderLine
.LineColor = RGB(255, 0, 255) ''Magenta
.LineWidth = 80 ''0.2 mm fine-line weight path
.LineStyle = com.sun.star.drawing.LineStyle.SOLID
.LineTransparence = 70
End With
''move leaderline to foreground layer
oLeaderLine.ZOrder = 10 + x
''add custom datalabel
oTextShape = oDoc.createInstance("com.sun.star.drawing.TextShape")
oDrawPage.add(oTextShape)
''fix shape dimensions, autosize width after adding string
oShapeSize.Width = 3500 ' 35 mm box width
oShapeSize.Height = 450 ' 4.5 mm text height
oTextShape.setSize(oShapeSize)
oShapePos.X = targetPointX - (oShapeSize.Width / 2)
oShapePos.Y = targetPointY + currentYOffset
oTextShape.setPosition(oShapePos)
oTextShape.setString(sEventText)
With oTextShape
.CharFontName = "Liberation Mono"
.CharHeight = 10
''force autosize width
.TextAutoGrowWidth = True
.CharWeight = com.sun.star.awt.FontWeight.MEDIUM''BOLD
.CharColor = RGB(0,0,0)
.TextHorizontalAdjust = com.sun.star.drawing.TextHorizontalAdjust.CENTER
.FillStyle = com.sun.star.drawing.FillStyle.SOLID
.FillColor = RGB(255,255,255) ' Solid white background blocks chart grids
.FillTransparence = 50
End With
''move text box shape to absolute foreground layer
oTextShape.ZOrder = 100 + x
Next x
Erase vNoteableEvents
Erase aAllocatedX
Erase aAllocatedYOffsets
MsgBox "Done"
End Sub
