I am on the next bit of the journey in automating my spreadsheets. This time, Charts.
I have used the various bits of boilerplate helpfully lying around this site, Andrew Pitonyak, and other searches.
The only bit I am missing now is how to turn the data into line plots. At the moment I either get two vertical lists
(using DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS, attachment 1)
or an extended Legend listing all values
(using DataRowSource = com.sun.star.chart.ChartDataRowSource.ROWS, attachment 2).
Clearly, there is something in the middle that I cannot figure out for myself. As usual, all advice gracefully accepted.
Background:
Taking two columns of data to plot against the first column of dates, produce a graph showing each column as a coloured line. Please see code below!
Code: Select all
REM ***** BASIC *****
sub Main
Dim RowSetObj As Object, ConnectToDatabase As Object, Database as Object
' ----------------------------------- Connect to database
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("LIVE")
ConnectToDatabase=DataSource.GetConnection ("User Name","Password")
' ----------------------------------- prepare spreadsheet
Dim noArgs() 'An empty array for the arguments
Dim sURL As String 'URL of the document to load
Dim oDoc
sURL = "private:factory/scalc"
oDoc = StarDesktop.LoadComponentFromUrl(sURL, "_blank", 0, Array())
createNewCalcDoc = oDoc
Dim oSheets
oSheets = oDoc.Sheets
oSheets.insertNewByName("Data", 1)
oSheets.insertNewByName("Chart", 0)
dim i as integer
for i = 1 to 3
if oSheets.hasbyName("Sheet" & i) Then
oSheets.removeByName("Sheet" & i)
end if
next
' ----------------------------------- load spreadsheet from database
i = 0
oSheet=oDoc.Sheets(1)
SQLQuery = "select * from ""View FOREX_STATE_PENSION_HISTORY"""
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery (SQLQuery)
dim oCell as object
dim concatenationString as string
dim colLabel as string
dim ii, colWidth as integer
oColumn = oSheet.getColumns.getByIndex(0)
oColumn.Width = 3230
oColumn = oSheet.getColumns.getByIndex(1)
oColumn.Width = 2530
oColumn = oSheet.getColumns.getByIndex(2)
oColumn.Width = 3290
While RowSetObj.Next
if i = 0 then
for ii = 1 to 3
col=RowSetObj.Columns.getByIndex(ii-1)
colLabel = String(col.DisplaySize, "*")
LSet colLabel = CStr(col.Name)
colLabel = String(1+ Max(len(col.Name), col.DisplaySize), "*")
Lset colLabel = CStr(col.Name)
oCell=oSheet.getCellByPosition(ii-1,i)
oCell.String=colLabel
next
end if
i=i+1
oCell=oSheet.getCellByPosition(0,i)
oCell.String=RowSetObj.getString(1)
oCell=oSheet.getCellByPosition(1,i)
oCell.String=RowSetObj.getString(2)
oCell=oSheet.getCellByPosition(2,i)
oCell.String=RowSetObj.getString(3)
Wend
' ----------------------------------- release database
ConnectToDatabase.close
ConnectToDatabase.dispose()
' ----------------------------------- find last used cell in column A
dim colA as Object
oSheet=oDoc.Sheets(1)
colA = oSheet.Columns.getByName("A") 'Get the Column named A
emptyCellRanges = colA.queryEmptyCells() 'Find all the Empty Cell Ranges
firstEmptyRange = emptyCellRanges.getByIndex(0) 'Get the first empty range
emptyRangeAddress = firstEmptyRange.RangeAddress 'Get the RangeAddress of the first empty range
' ----------------------------------- set up conditional formatting for greater of columns B and C cells by row
dim leftCell as object
dim rightCell as Object
dim rowNumber, lastRow as integer
rowNumber = emptyRangeAddress.StartRow - 1
for i = 1 to rowNumber
leftCell = oSheet.getCellByPosition(1,i)
rightCell = oSheet.getCellByPosition(2,i)
if leftCell.string > rightCell.string then leftCell.CellBackColor = RGB(204, 255, 153)
if leftCell.string < rightCell.string then rightCell.CellBackColor = RGB(204, 255, 153)
next
lastRow = rowNumber
dim dataRange as string
dim oChartDoc
Dim Charts As Object
Dim Chart as Object
Dim Rect As New com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
lastRow = lastRow + 1 ' rowNumber + 1
' Print lastRow
dataRange = "b2:c" & lastRow
oChartDoc = ThisComponent
Dim oRect 'How big is the chart
Dim oAddress 'Address of data to plot
Dim oTitle 'Chart title object
Charts = oChartDoc.Sheets(0).Charts
Rect.X = 12920 ' was 2270
Rect.Y = 450
Rect.Width = 16000
Rect.Height = 9200
with RangeAddress(0)
.Sheet = 1 ' change this back to 0 when it looks a lot better
.StartColumn = 1
.StartRow = 0
.EndColumn = 2
.EndRow = lastRow
end with
Charts = oChartDoc.Sheets(1).Charts
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, False)
Chart = Charts.getByName("MyChart")
ChartDoc = Chart.getEmbeddedObject()
ChartDoc.createInstance(ChartType)
oData = ChartDoc.getData()
dim oDiagram as object
with ChartDoc
.hasMainTitle = True
.Title.String = "Direct Forex GBPPEN from State Pension Receipts"
.HasSubTitle = True
.Subtitle.String = "Cubic spline with resolution = 6 drawn at " & Now
.HasLegend = True
.Legend.Alignment = com.sun.star.chart.ChartLegendPosition.RIGHT
.Legend.FillStyle = com.sun.star.drawing.FillStyle.SOLID
.Legend.FillColor = RGB(204, 255, 153)
.Legend.CharHeight = 7
end with
oDiagram = ChartDoc.createInstance( "com.sun.star.chart.LineDiagram" )
ChartDoc.setDiagram( oDiagram )
oDiagram = ChartDoc.getDiagram()
with oDiagram
.HasXAxisTitle = true
.XAxisTitle.String = "X axis title"
.HasYAxisTitle = true
.YAxisTitle.String = "Y axis title"
.DataCaption = com.sun.star.chart.ChartDataCaption.VALUE
.DataRowSource = com.sun.star.chart.ChartDataRowSource.COLUMNS
.SymbolType = 1
.SplineType = 1
.SplineResolution = 6
.Lines = true
end with
end Sub
Function Max( p1, p2 )
If p1 > p2 Then
Max() = p1
Else
Max() = p2
EndIf
End Function