Author: Dmitri Popov
While OpenOffice.org Base is good for storing and querying data, it doesn’t provide any easy way to chart information. This is exactly what Calc does best, with its dedicated chart module. If you want to visualize data stored in a Base database, you can write an OOo Basic macro that pulls data from a database, inserts it into a Calc spreadsheet, and then creates a chart. Here’s how.
We can illustrate this exercise with a simple database for managing tasks. It consists of three fields: a Task text field, a Date, and the Status boolean field. The latter field is used to mark finished tasks. The macro retrieves all the tasks and presents finished and unfinished tasks as a chart.
From past OOo Basic crash course articles you know how to establish a connection to a database and retrieve data from it using an SQL query:
Sub TasksToCalcWithChart()
Dim RowSetObj As Object, ConnectToDatabase As Object
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Dim Rect As New com.sun.star.awt.Rectangle
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("TasksDB")
ConnectToDatabase=DataSource.GetConnection ("","")
SQLQuery= "SELECT ""Task"", ""Date"", ""Done"" FROM ""tasks"" ORDER BY ""Date"" ASC"
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery (SQLQuery)
The code above connects to the TasksDB database and retrieves all records from the tasks table sorted by date in the ascending order. The macro also defines two new variables, RangeAddress and Rect, which we’ll need later to define a cell range in a spreadsheet and to draw a chart area. Next, the macro creates an empty Calc spreadsheet and names the first sheet “Tasks”:
URL="private:factory/scalc"
ThisDoc=StarDesktop.loadComponentFromURL(URL, "_blank", 0, Array())
Sheet=ThisDoc.Sheets(0)
Sheet.Name="Tasks"
Before the macro can populate the sheet with the retrieved records, it uses the first row to add column names. The names makes it easier to view the data and, more importantly, they are used as labels in a generated chart. As you can see, to add content to a cell, the macro first specifies the cell’s address using the getCellByPosition property of the Sheet object. The address itself consists of the row and column numbers, so the address of the A1 cell is 0,0. The macro then sets the String property of the Cell object to the specified text:
Cell=Sheet.getCellByPosition(0,0)
Cell.String="Task"
Cell=Sheet.getCellByPosition(1,0)
Cell.String="Date"
Cell=Sheet.getCellByPosition(2,0)
Cell.String="Status"
Now the macro can populate the specified columns with the data from the tasks database. To do this, the macro uses the While…Wend loop, which moves from cell to cell in a row and inserts the contents of record fields in them:
While RowSetObj.Next
i=i+1
Cell=Sheet.getCellByPosition(0,i)
Cell.String=RowSetObj.getString(1)
Cell=Sheet.getCellByPosition(1,i)
Cell.String=RowSetObj.getString(2)
Cell=Sheet.getCellByPosition(2,i)
Cell.String=RowSetObj.getString(3)
Wend
While you could insert a chart into the current Tasks sheet, you can also do this in a separate sheet to keep things tidy. To do this, the macro initializes the next sheet in the spreadsheet and adds a row with column names (“Status” and “Count”) using the previously described techniques:
Sheet=ThisDoc.Sheets(1)
Sheet.Name="PieTasks"
Cell=Sheet.getCellByPosition(0,0)
Cell.String="Status"
Cell=Sheet.getCellByPosition(1,0)
Cell.String="Count"
Next, the macro has to calculate the number of finished and unfinished tasks. The value of the Status boolean field is imported as “true” for every finished task or “false” for unfinished ones. We can use the COUNTIF occurrences of the “true” and “false” strings to find out the number of finished and unfinished tasks. In this example, the formula is limited to 37 rows (the last cell in the range is C:37), but you can set it to whatever value you wish. To set the contents of a cell to the specified formula, the macro uses the Formula property. The entire code block that calculates the number of finished and unfinished tasks, puts the results into the appropriate cells, and labels them looks like this:
Cell=Sheet.getCellByPosition(0,1)
Cell.String="Unfinished"
Cell=Sheet.getCellByPosition(1,1)
Cell.Formula="=COUNTIF(Tasks.C2:C37;""false"")"
Cell=Sheet.getCellByPosition(0,2)
Cell.String="Finished"
Cell=Sheet.getCellByPosition(1,2)
Cell.Formula="=COUNTIF(Tasks.C2:C37;""true"")"
Now the macro is ready to generate a chart based on the number of finished and unfinished tasks. It starts with specifying the size and position of the chart using the Rect object:
Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
The X and Y properties of the object specify the position of the chart, while the Width and Height properties specify its size. Next, the macro must define a cell range to use with the chart by specifying the properties of the RangeAddress object such as StartColumn, EndColumn, StartRow, and EndRow:
RangeAddress(0).Sheet = 1
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 1
RangeAddress(0).EndRow = 2
With all the pieces in place, the macro can generate a chart with just a single line of code:
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
The code above generates a bar chart. If you want to create another type of graph, you have to explicitly specify the desired diagram type via the appropriate service. For example, if you want to create a pie chart, you have to use the following code:
Chart = Charts.getByName("MyChart").embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.PieDiagram")
For a donut chart, use the com.sun.star.chart.DonutDiagram service instead. You can turn the 2-D chart into 3-D by using the Chart.Diagram.Dim3D =True statement. There are also other properties that you can specify to customize the chart’s settings. The code block below, for example, enables and specifies the chart’s title and subtitle:
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Chart = Charts.getByName("MyChart").embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.PieDiagram")
Chart.HasMainTitle = True
Chart.Title.String = "Tasks"
Chart.HasSubTitle = True
Chart.Subtitle.String = "My tasks"
And that’s all there is to it. Here is the full macro:
Sub TasksToCalcWithChart()
Dim RowSetObj As Object, ConnectToDatabase As Object
Dim RangeAddress(0) As New com.sun.star.table.CellRangeAddress
Dim Rect As New com.sun.star.awt.Rectangle
DBContext=createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource=DBContext.getByName("TasksDB")
ConnectToDatabase=DataSource.GetConnection ("","")
SQLQuery= "SELECT ""Task"", ""Date"", ""Done"" FROM ""tasks"" ORDER BY ""Date"" ASC"
SQLStatement=ConnectToDatabase.createStatement
RowSetObj=SQLStatement.executeQuery (SQLQuery)
URL="private:factory/scalc"
ThisDoc=StarDesktop.loadComponentFromURL(URL, "_blank", 0, Array())
Sheet=ThisDoc.Sheets(0)
Sheet.Name="Tasks"
Cell=Sheet.getCellByPosition(0,0)
Cell.String="Task"
Cell=Sheet.getCellByPosition(1,0)
Cell.String="Date"
Cell=Sheet.getCellByPosition(2,0)
Cell.String="Status"
While RowSetObj.Next
i=i+1
Cell=Sheet.getCellByPosition(0,i)
Cell.String=RowSetObj.getString(1)
Cell=Sheet.getCellByPosition(1,i)
Cell.String=RowSetObj.getString(2)
Cell=Sheet.getCellByPosition(2,i)
Cell.String=RowSetObj.getString(3)
Wend
Sheet=ThisDoc.Sheets(1)
Sheet.Name="PieTasks"
Cell=Sheet.getCellByPosition(0,0)
Cell.String="Status"
Cell=Sheet.getCellByPosition(1,0)
Cell.String="Count"
Cell=Sheet.getCellByPosition(0,1)
Cell.String="Unfinished"
Cell=Sheet.getCellByPosition(1,1)
Cell.Formula="=COUNTIF(Tasks.C2:C37;""false"")"
Cell=Sheet.getCellByPosition(0,2)
Cell.String="Finished"
Cell=Sheet.getCellByPosition(1,2)
Cell.Formula="=COUNTIF(Tasks.C2:C37;""true"")"
ThisDoc=ThisComponent
Charts=ThisDoc.Sheets(1).Charts
Rect.X = 8000
Rect.Y = 1000
Rect.Width = 10000
Rect.Height = 7000
RangeAddress(0).Sheet = 1
RangeAddress(0).StartColumn = 0
RangeAddress(0).StartRow = 0
RangeAddress(0).EndColumn = 1
RangeAddress(0).EndRow = 2
Charts.addNewByName("MyChart", Rect, RangeAddress(), True, True)
Chart = Charts.getByName("MyChart").embeddedObject
Chart.Diagram = Chart.createInstance("com.sun.star.chart.PieDiagram")
Chart.HasMainTitle = True
Chart.Title.String = "Tasks"
Chart.HasSubTitle = True
Chart.Subtitle.String = "My tasks"
ConnectToDatabase.close
ConnectToDatabase.dispose
End Sub
Categories:
- Programming
- Office Software
