Spreadsheet population
Jump to navigation
Jump to search
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
Tutorial on using LibreOffice charting capabilities with data stored in a SQL database.
The following macro was developed in order to use LibreOffice charting capabilities with data stored in a SQL database.
How to use it
After downloading the Populate-spreadsheet-from-db.ods LibreOffice file:
- Open the document and enter the information to connect to your SQL database
- Press the Load data button
- If the data was successfully imported, press Create chart
NOTE: The macros connect to a database and will do nothing if that database is not available. So you can not expect it to just work from downloading the file alone.
Downloads
File:Populate-spreadsheet-from-db by-lanedoCOM.ods
How it works
The macro connects to a SQL database and populates the spreadsheet. This data is then used to produce a chart.
REM ***** BASIC *****
Function db_escape (str As String, connection) As String
Dim delim As String
meta = connection.getMetaData ()
delim = meta.getIdentifierQuoteString ()
db_escape = delim & str & delim
End Function
Sub LoadDataFromDB
Dim dm
Dim driver
Dim params(1) As New com.sun.star.beans.PropertyValue
REM Get Current Spreadsheet
REM http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/module-ix.html
REM http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/SpreadsheetDocument.html (getSheets -> XSpreadsheets)
REM http://api.openoffice.org/docs/common/ref/com/sun/star/table/module-ix.html
doc = ThisComponent
addr = doc.getCurrentSelection().getCellAddress()
sheet = doc.getSheets().getByIndex(addr.Sheet)
REM rSheets->insertNewByName(OUString::createFromAscii("MySheet"), (short)0)
draw_page = sheet.DrawPage
db_form = draw_page.Forms.GetByIndex(0)
hst_ctl = db_form.getByName ("txtHost")
usr_ctl = db_form.getByName ("txtUser")
pwd_ctl = db_form.getByName ("txtPwd")
dbm_ctl = db_form.getByName ("txtDBM")
sts_ctl = db_form.getByName ("lblStatus")
host = hst_ctl.Text
usr = usr_ctl.Text
pwd = pwd_ctl.Text
dbm = dbm_ctl.Text
url = "jdbc:mysql://" & host & "/" & dbm & "?user=" & usr & "&password=" & pwd
Print "Connecting to " & dbm & " on " & host
sts_ctl.Label = "Connecting to " & dbm & " on " & host
REM Database Setup
dm = CreateUnoService ("com.sun.star.sdbc.DriverManager")
params(0).Name = "JavaDriverClass"
params(0).Value = "com.mysql.jdbc.Driver"
con = dm.getConnectionWithInfo (url, params())
if IsNull (con) Then
MsgBox "Could not connect to DB"
Exit Sub
End if
sts_ctl.Label = "Fetching data from DB ..."
stmt = con.CreateStatement()
sqltxt = "SELECT * FROM " & db_escape("key_value", con)
res = stmt.executeQuery (sqltxt)
If IsNull (res) OR IsEmpty (res) Then
MsgBox "Nothing in tables"
Exit Sub
End If
Do While res.next ()
meta = res.getMetaData()
REM c = meta.getColumnCount ()
REM http://api.openoffice.org/docs/common/ref/com/sun/star/sdbc/XRow.html
REM Print res.getInt (1) & res.getString (2) & res.getFloat (3)
cur_row = res.getRow ()
cell = sheet.getCellByPosition (0, cur_row)
cell.Value = res.getInt (1)
cell.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
cell = sheet.getCellByPosition (1, cur_row)
cell.String = res.getString (2)
cell = sheet.getCellByPosition (2, cur_row)
cell.Value = res.getFloat (3)
Loop
sts_ctl.Label = "Data loaded!"
End Sub
Sub CreateChart
Dim rect As New com.sun.star.awt.Rectangle
Dim ra(0) As New com.sun.star.table.CellRangeAddress
rect.X = 1000
rect.Y = 9000
rect.Width = 10000
rect.Height = 7000
ra(0).Sheet = 0
ra(0).StartColumn = 1
ra(0).StartRow = 0
ra(0).EndColumn = 2
ra(0).EndRow = 9
doc = ThisComponent
sheet = Doc.Sheets(0)
charts = sheet.Charts
charts.addNewByName("Bar Chart", Rect, ra(), True, True)
chart = charts.getByName("Bar Chart").EmbeddedObject
End Sub