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

From The Document Foundation Wiki
< Macros‎ | Calc
Jump to: navigation, search
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:
English • ‎español

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/es