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

The Application Programming Interface and the OASIS Open Document Format

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

Postby misitu » Fri Jun 17, 2016 4:51 am

Hello!

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   Expand viewCollapse view
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
Attachments
Calc Chart Macro, how to generate line from data, COLUMNS.jpg
COLUMNS example output showing data instead of lines
Calc Chart Macro, how to generate line from data, ROWS.jpg
ROWS example output showing data instead of lines
Last edited by misitu on Fri Jun 17, 2016 10:27 pm, edited 2 times in total.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 85
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Postby misitu » Fri Jun 17, 2016 5:00 am

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!

Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 85
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Postby FJCC » 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 com.sun.star.awt.Rectangle
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress

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("com.sun.star.chart.LineDiagram")
Attachments
LineChart.ods
(16.29 KiB) Downloaded 77 times
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6160
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Postby misitu » Fri Jun 17, 2016 10:26 pm

THANKS!
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.
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 85
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest