Page 1 of 1

[Solved] Calc Chart Macro, how to generate line from data

PostPosted: Fri Jun 17, 2016 4:51 am
by misitu

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 =, attachment 1)
or an extended Legend listing all values
(using DataRowSource =, attachment 2).

Clearly, there is something in the middle that I cannot figure out for myself. As usual, all advice gracefully accepted.

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   Expand viewCollapse view
REM  *****  BASIC  *****

sub Main

   Dim RowSetObj As Object, ConnectToDatabase As Object, Database as Object
' -----------------------------------  Connect to database
   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
' ----------------------------------- load spreadsheet from database
   i = 0
   SQLQuery = "select * from ""View FOREX_STATE_PENSION_HISTORY"""
   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
            colLabel = String(col.DisplaySize, "*")
            LSet colLabel = CStr(col.Name)
            colLabel = String(1+ Max(len(col.Name), col.DisplaySize), "*")
            Lset colLabel = CStr(col.Name)
      end if   
' ----------------------------------- release database
' ----------------------------------- find last used cell in column A   
   dim colA as Object
   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)
   lastRow = rowNumber

   dim dataRange as string
   dim oChartDoc
   Dim Charts As Object
   Dim Chart as Object
   Dim Rect As New
   Dim RangeAddress(0) As New
   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()
   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 =
      .Legend.FillStyle =
      .Legend.FillColor = RGB(204, 255, 153)
      .Legend.CharHeight = 7
   end with

   oDiagram = ChartDoc.createInstance( "" )
   ChartDoc.setDiagram( oDiagram )
   oDiagram = ChartDoc.getDiagram()
   with oDiagram
      .HasXAxisTitle = true
      .XAxisTitle.String = "X axis title"
      .HasYAxisTitle = true
      .YAxisTitle.String = "Y axis title"
      .DataCaption =
      .DataRowSource =
      .SymbolType = 1
      .SplineType = 1
      .SplineResolution = 6
      .Lines = true
   end with
end Sub

Function Max( p1, p2 )
   If p1 > p2 Then
      Max() = p1
      Max() = p2
End Function

Re: Calc Chart Macro, how to generate line from data

PostPosted: Fri Jun 17, 2016 5:00 am
by misitu
Right, I can see there is something called DataArray that is probably my missing link.

I will try fixing this. If I cannot fix it I'll ask for a bit more help!


Re: Calc Chart Macro, how to generate line from data

PostPosted: Fri Jun 17, 2016 3:08 pm
Here is a bare bones version of making a line chart with dates on the x axis and two data series. The data are on the first sheet and the chart is on the second.
Code: Select all   Expand viewCollapse view
Dim Rect As New
Dim RangeAddress(0) As New

Rect.X = 8000
Rect.Y = 1000
Rect.Width = 25000
Rect.Height = 10000
RangeAddress(0).Sheet = 0
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 2
RangeAddress(0).EndRow = 12

Charts = ThisComponent.Sheets(1).Charts
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Chart = Charts.getByName("MyChart").EmbeddedObject
Chart.Diagram = Chart.createInstance("")

Re: Calc Chart Macro, how to generate line from data

PostPosted: Fri Jun 17, 2016 10:26 pm
by misitu
That helped a great deal.
Most of my code turned out to be OK. The bit that wasn't was the RowSetObj loop to extract raw data from the database table.
It said "string". It should have said "value" and also "cdate". Without those there was nothing to show!

It was good to have a really simple example against which to match my "slightly complicated" real thing, helping immeasurably to focus on the essentials.. which, in this case, were elsewhere.

Very grateful.