Spreadsheet population

    From The Document Foundation Wiki
    < Macros‎ | Calc
    Other languages:

    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:

    1. Open the document and enter the information to connect to your SQL database
    2. Press the Load data button
    3. 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

    Macros