How to reference a calc cell from base

[Andyfc]

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?

[doug]

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.

[peterwt]

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