Table of Contents
I will introduce the way to show database data on spread sheet of LibreOffice Calc, using LibreOffice Base. The data you retrieve from the database onto the spread sheet can be used for any table and pivot tables. I wrote the article “Excel: Retrieve and Show Database Data” before, which shows the way to get the data onto Excel through ODBC, but LibreOffice enables us to do the same thing for free.
This time, I will connect PostgreSQL. Of course other database, such as MySQL, can be used.
- Windows 10
- LibreOffice 6
- PostgreSQL 9.5 (SSL ON)
Launch LibreOffice Base, and create new file.
Database wizard automatically starts. Then, configure connection for existing database. You can also handle ODBC connection. Here, I chose PostgreSQL direct connection.
Describe the database.
Write user name for the connection. If password is required, check the box. When you click the button
Test Connection, password input dialog appears.
Register the database to LibreOffice if it is used frequently.
That’s all for creating connection.
Define the Data You Want
Define the data to retrieve. If you want plain data of a table, you don’t have to do it.
Open the file, and select “Queries” on left pane, click “Create Query in SQL View“. Other, “Create Query in Design View“、”Use Wizard to Create Query” also can be used.
Finishing to write the SQL, try it. There are some way to run the SQL. Type F5 key, select “Run Query” in Edit menu, click the button on tool bar.
If the result is what you expected, save the query. On saving, you have to input query name.
Show Data on the Spread Sheet
Leave LibreOffice Base window open, open LibreOffice Calc and select the sheet where you want to show the data.
Drag and Drop the query from LibreOffice Base window to LibreOffice Calc spread sheet. Then you can see the data on Calc.
If the data or query definition is changed and you want to show the latest data, select the cell of data shown area, click “Refresh Range” in Data menu. Then the latest data will be shown.
You can also create pivot tables etc for the data acquired in this way.