Importación de datos usando una conexión SQL y graficación posterior

    From The Document Foundation Wiki
    < Macros‎ | Calc
    This page is a translated version of the page Macros/Calc/017 and the translation is 80% complete.
    Outdated translations are marked like this.
    Other languages:

    Tutorial para conocer las capacidades de gráficos que tiene LibreOffice con los datos almacenados en una base de datos SQL.

    La siguiente macro fue desarrollada para demostrar las capacidad gráficas y de conexión con bases de datos.

    Cómo usarlo

    Después de descargar el archivo populate-spreadsheet-from-db_by-lanedoCOM.ods:

    1. Abra el documento e ingrese la información necesaria para conectar con su base de datos SQL
    2. Pulse el botón Load data
    3. Si los datos se importaron correctamente, pulse el botón Create chart

    NOTA: Las macros conectan con una base de datos y no harán nada si la base de datos no está disponible. Así que no espere resultados por solo descargar y trabajar solo con el archivo.

    Descargas

    File:Populate-spreadsheet-from-db by-lanedoCOM.ods

    Cómo trabaja

    La macro se conecta a una base de datos SQL. Los datos se utilizan para crear un gráfico sencillo.

    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