Page 1 of 1

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

Posted: 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

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

Posted: 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

Posted: 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

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

Posted: 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.