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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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

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
COLUMNS example output showing data instead of lines
COLUMNS example output showing data instead of lines
ROWS example output showing data instead of lines
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: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post 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!

Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

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

Post by FJCC »

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 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 479 times
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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
Post Reply