Ask Your Question
1

How to reference a calc cell from base

asked 2015-11-09 08:07:15 +0100

Andyfc gravatar image

updated 2015-11-09 09:18:42 +0100

Alex Kemp gravatar image

I have a spreadsheet in calc that pulls data from a "citta" table in base.

I would like to make a query in base looking something like:

SELECT "Latitude" FROM "citta"  WHERE "City"= content of the cell A10 of spreadsheet so-and-so.

because I then need to feed this "Latitude" back in calc.

I have searched a lot, but whenever I try to google for this, I always get the other way around (entering in a calc cell data from base).

Or is something like that possible directly within calc, without invoking base?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2015-11-10 05:56:05 +0100

doug gravatar image

You can use a macro for this purpose, if I understand the question correctly, the answer is:

This code snippet will retrieve the String in Cell "A1", match it using the query string below, and output the result in Cell "A2".

REM #load the object hierarchy
sheets = ThisComponent.Sheets
sheet1 = sheets.getByIndex(0) 

REM #get the source cell
cellA1 = sheet1.getCellByPosition(0,0)
cellA1Text = cellA1.String

REM #connect to a database - mine happens to be number two on list
REM #name is dbName - can view value with MsgBox or manually input
dbContext = createUnoService("com.sun.star.sdb.DatabaseContext")
dbName = dbContext.registrationNames(2)
oDataSource = dbContext.getByName(dbName)
Conn = oDataSource.GetConnection("","")

REM #run the query using the connection
SQL = Conn.CreateStatement()

QueryStr = "SELECT `Latitude` FROM `citta`  WHERE `City`= '" & cellA1Text & "'"
ResultSet = SQL.executeQuery(QueryStr)
ResultSet.next
outputValue = ResultSet.getDouble(1)

REM #close the database connection
Conn.close
oDataSource.dispose() 

REM #output the result
outputCell = sheet1.getCellByPosition(1,0)
outputCell.String = outputValue

h/t to Dr. Bain for the connection methodology (page 10 of link). This is quick and ugly demo. The link shows some better modularization of code.

As for other details, as you can see we are working in the first Sheet which we identified by Index number. Cell "A1" is in position 0,0. I am loading the second registered database on my list. Your list will be different. You will need to change this number and make sure your LibreOffice database that connects to the MySQL database, is registered. I use backticks instead of double-quotes for field and table names in the SQL string in hopes of achieving higher compatibility. Output cell "A2" is in position 1.0.

You will create this macro via Tools --> Macros --> Organize Macros --> LibreOffice Basic then go to the document click New to make a new module, and paste that macro and customize. Press F5 to run, or go back to the macro dialog.

edit flag offensive delete link more
0

answered 2015-11-11 13:19:57 +0100

peterwt gravatar image

updated 2015-11-11 13:56:09 +0100

I have taken a slightly different approach to the good answer provided by @doug. My macro is run after you have selected a cell containing the City you want to lookup in the database. It puts the result in the cell to the right of the selected cell. The macro should be placed in the Calc spreadsheet. I have assumed that the latitude is a double in the database and City names are unique with no duplicates (as has @doug). If there are duplicate City names in the database it will use the first match so if you do have duplicates you will need to add code to decide which city to use. In addition I check for no match in case the City is not in the database, which would result in an error when the Result Set is attempted to be accessed.


Sub FromSheet
REM get the source cell contents from the selected cell
oCellSource = ThisComponent.getCurrentSelection()
lookup = oCellSource.String

REM get the current sheet object
oSheet = thiscomponent.getcurrentcontroller.activesheet

REM set the target cell to left of source cell
oCellTarget = oSheet.getCellByPosition( oCellSource.CellAddress.Column + 2, oCellSource.CellAddress.Row )

REM connect to your database by name
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("YourDatabaseName")
Conn=DB.getConnection("","")

REM run the query
Stmt=Conn.createStatement() strSQL="SELECT `Latitude` FROM `citta` WHERE `City` = '" + lookup + "'"
oResult = Stmt.executeQuery(strSQL)

REM put Lattitude of selected City in sheet cell if there is a result of the query
oResult.next
If (oResult.Row > 0) Then
oCellTarget.Value = oResult.columns.getByIndex(0).double
REM this will put the Latitude in the sheet as a double. If you want to put it in as text
use the line below
REM oCellTarget.Value = oResult.columns.getByIndex(0).string
MsgBox "OK"
Else
MsgBox "No Matches"
End If

Rem close the connection
Conn.close()
DB.dispose()
End Sub
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-11-09 08:07:15 +0100

Seen: 708 times

Last updated: Nov 11 '15