Key Points

1. free and open-source
2. covers all modules of Microsoft Office
3. generally document format with compatible at level 2007
4. allows Python custom functions
5. uses java jdk
6. runs on any platform: windows, linux, mac os
7. differences with excel formulas in some areas

References

Key Concepts

To Learn

SUMIFS examples

https://help.libreoffice.org/Calc/SUMIFS_function

Simple usage
=SUMIFS(B2:B6;B2:B6;">=20")
Calculates the sum of values of the range B2:B6 that are greater than or equal to 20. Returns 75, because the fifth row does not meet the criterion.

=SUMIFS(C2:C6;B2:B6;">=20";C2:C6;">70")
Calculates the sum of values of the range C2:C6 that are greater than 70 and correspond to cells of the B2:B6 range with values greater than or equal to 20. Returns 275, because the second and the fifth rows do not meet at least one criterion.

Using regular expressions and nested functions
=SUMIFS(C2:C6;B2:B6;">"&MIN(B2:B6);B2:B6;"<"&MAX(B2:B6))
Calculates the sum of values of the range C2:C6 that correspond to all values of the range B2:B6 except its minimum and maximum. Returns 255, because the third and the fifth rows do not meet at least one criterion.

=SUMIFS(C2:C6;A2:A6;"pen.*";B2:B6;"<"&MAX(B2:B6))
Calculates the sum of values of the range C2:C6 that correspond to all cells of the A2:A6 range starting with "pen" and to all cells of the B2:B6 range except its maximum. Returns 65, because only second row meets all criteria.

Reference to a cell as a criterion
If you need to change a criterion easily, you may want to specify it in a separate cell and use a reference to this cell in the condition of the SUMIFS function. For example, the above function can be rewritten as follows:
=SUMIFS(C2:C6;A2:A6;E2&".*";B2:B6;"<"&MAX(B2:B6))
If E2 = pen, the function returns 65, because the link to the cell is substituted with its content.

VLOOKUP examples 

VLOOKUP on another sheet, range for return value in column 10 for the lookup value in cell b5.

the target array is sorted on the first column

=VLOOKUP(b5; $tepFirms.$A$2.$ZZ$1000; 10; TRUE)

Create Pivot Tables

Pivot tables can use numeic inputs for data fields

If you sheet does not have data fields create them using one or more of the following methods:

use an existing numeric column

generate subtotals on a category row value

insert a qty of 1 as a new column for counts

Steps to create Pivot table

1. select a set of contiguous columns ( or all ) in the sheet
2. Data > Pivot Table > Insert
3. Fill out the Pivot table dialog including relevant options ( recommend creating a new result sheet ), filters etc
4. 
5. Generated sheet with Pivot Table opens
6. 

Create a chart on a pivot table

chart has 2 dimensions - platform and sector

Create a 1 dimensional Pivot table

add filter and drill through

ensure the base selected columns include the primary dimension, the secondard dimension ( drill through ) and the numeric field for calculations

create pivot table in new sheet

Libre Office Extension Manager - add more features

Libre Base is not as quick or easy in MOST use cases as Grails scaffolding on MySQL DB

All these products are free BUT Libre Base has many limitations and the combination of Grails scaffolding with MySQL DB using MySQL Workbench is far more capable, performant, flexible and quicker to setup for most use cases ( see Jim )

BASE has MANY issues on properly running forms, supporting JOINS in forms etc on Firebird.

NOT recommended 

always use Grails for database apps instead

Base Documenter

https://extensions.libreoffice.org/extensions/basedocumenter-to-document-your-base-applications

documents a base app easily

Base JDBC Connection to MySQL db

https://www.techrepublic.com/article/how-to-connect-libreoffice-base-to-a-remote-mysql-database/

libre-base-jdbc-mysql-techrepublic.com-How to connect LibreOffice Base to a remote MySQL database.pdf

Out of the box, LibreOffice does not have a functioning JDBC Connector. To add this, you must first download the Platform Independent connector (from the MySQL Downloads page) and then extract that file. The extraction will create a new folder that contains the necessary .jar file. Open up LibreOffice and go to Tools | Options | LibreOffice | Advanced | Class Path

Calc Quantlib Addin

https://extensions.libreoffice.org/extensions/quantlib-addin

Calc Query JDBC data source using Jaguar Plugin

https://extensions.libreoffice.org/extensions/new-jaguar-calc-add-in

New JaguarSQL is a JaguarSQL 1.0.2 update.
Within the extension file (.oxt) there is the original source code of JaguarSQL 1.0.2  which is the base of this update.

This extension adds  the SQLREQUEST function to Calc.    
SQLREQUEST allows you to query a database using a JDBC driver or an SDBC driver.
It is similar to the SQL.REQUEST function provided with old MS-Excel.
It is an array function. The result is an array containing the data returned from the data source. Calc’s online help includes the "Array Functions" section that explains how to use them.

The SQLREQUEST sintax is:

SQLREQUEST (driver, url_or_properties, sqlCommand, nRowsRequired, showColumnName, number2double, date2string, yearOffset)

To report bugs or give advice, lo.extensions@tiscali.it

Examples:

JDBC driver:
=SQLREQUEST("com.mysql.jdbc.Driver";"jdbc:mysql://server_ip_address/?user=name&password=abc";"SELECT * FROM table")

SDBC driver:
=SQLREQUEST("sdbc:odbc:mydatabase";"user=pippo;password=mypssword";"SELECT * FROM table")

Parameters

driver

It’s a string. If this string matches a full qualified java class name (for example: com.pippo.minni), SQLREQUEST

Calc GetRest Query Plugin

https://extensions.libreoffice.org/extensions/libreoffice-getrest-plugin-1

1) Get Json from REST call

Get function allow user to perform get request to remote server end get data from it.

Syntax: =get(URL) Where URL is URL which to send a GET request.
Value of cell will be result of get request.
Example:
Cell content:

=get("http://api.openweathermap.org/data/2.5/weather?q=London,uk")
Cell value:
{ "coord":{ "lon":-0.13, "lat":51.51 }, "sys":{ "type":1, "id":5091, "message":0.1999, "country":"GB", "sunrise":1413008314, "sunset":1413047741 }, "weather":[ { "id":801, "main":"Clouds", "description":"few clouds", "icon":"02d" } ], "base":"cmc stations", "main":{ "temp":287.3, "pressure":1011, "humidity":62, "temp_min":285.15, "temp_max":289.15 }, "wind":{ "speed":5.1, "deg":230 }, "clouds":{ "all":20 }, "dt":1413046702, "id":2643743, "name":"London", "cod":200 }

2) Parse JSON

Parse JSON function allow u to get JSON substructure or direct value from JSON structure
Syntax: =PARSEJSON(JSON,Pattern) Where JSON is valid JSON structure. Pattern is a set of commands to parser splited by dote symbol '.' command.command.command.
There two different commands get(XXX) which return the XXX element of JSON array structure, and objectName command which get the value of json object by its name.
Value of cell will be result of parsing if JSON valid and pattern correct or error message otherwise
Example:
Cell content:

A1=
{

{
"firstName": "John",
"lastName": "Smith", 
"isAlive": true,
"age": 25,
"height_cm": 167.6,
"address": {
"streetAddress": "21 2nd Street",
"city": "New York", 
"state": "NY",
"postalCode": "10021-3100"
},
"phoneNumbers": [ { 
"type": "home",
"number": "212 555-1234" 
},
{ 
"type": "office",
"number": "646 555-4567"
} ],
"children": [],
"spouse": null
}

A2: =parsejson(A1,"phoneNumbers.get(1).number")
Cell value:
646 555-4567

Office - Integrate Other Data Sources

ODBC Data Source integration options

Buy Easysoft ODBC drivers

https://www.easysoft.com/applications/openoffice_org/odbc.html

Easysoft ODBC drivers have been tested with:

• OpenOffice 3.0+ and LibreOffice 3.0+ on Ubuntu (Precise Pangolin, which includes unixODBC 2.2.14).
• OpenOffice.org 2.0+ on RedHat 9.0 (which includes unixODBC 2.2.3).
• OpenOffice.org 1.0+ on RedHat 8.0 (which includes unixODBC 2.2.2), Ubuntu (Edgy Eft, Feisty Fawn, Gutsy Gibbon and Hardy Heron) and Debian.

Easysoft ODBC drivers should work with any recent Linux distribution.

When testing Easysoft ODBC drivers with Apache OpenOffice and LibreOffice we used both the unixODBC Driver Manager included with the operating system and the Easysoft ODBC driver distribution. As both Apache OpenOffice and LibreOffice are built with threads, make sure that you specify the thread-safe OOB client driver ("OOB_r"), if you are using the OOB.

To use an Easysoft ODBC driver with OpenOffice.org, ensure that the unixODBC Driver Manager is installed and that the Easysoft ODBC driver is installed as an ODBC driver under unixODBC. If unixODBC is not installed already, install the version that comes with the Easysoft ODBC driver distribution. OpenOffice.org is built with threads, so if you build unixODBC yourself, use --enable-threads=yes on the configure line. If you are using the OOB, make sure that you specify the thread-safe OOB client driver ("OOB_r") when creating data sources for use with OpenOffice.org. For earlier OOB releases, the thread-safe client is contained in distributions that contain "-mt" in their file name.

Before trying to use ODBC in OpenOffice, create an ODBC data source and test it with unixODBC’s isql utility.

Example of Using Calc with ODBC Data Sources

After you have registered your ODBC data source, the external ODBC data is then available to OpenOffice applications. This example shows how to connect Calc to ODBC databases such as Mssql, Oracle®, InterBase and Sybase. Calc is the OpenOffice spreadsheet program.

1. In Calc, choose View > Data Sources or press the F4 key.

   The Data Sources pane opens at the top of the Calc window.

2. In the left Data Sources pane, double-click your ODBC data source.
3. Do one of the following:
   • To make all the data in a table available to your Calc spreadsheet, double click Tables.
   • To make the results of a query available to your Calc spreadsheet, double click Queries.
4. Double-click the table or query that will retrieve the data you want to copy into Calc.
5. To filter the data in Calc before copying it, do one of the following:
   • Click and then define the filter criteria.
   • Click in a field that contains the value you want to filter records with, and then choose Data > Filter > Auto Filter.

Potential Value Opportunities

can embed office apps on server apps ( eg Trade compliance with ooffice doc apis )

try creating data sources for calc on normal sql and nosql dbs using jdbc or odbc drivers

Potential Challenges

Inserting new line characters in a Writer > Text > Replace function with Regex expressions

Original Text

my first try at fixing text with regex.z I want to replace the period with a period and a new line character.z I should be able to do this using the proper regex on find and replace expressions.z Then I see each sentence on it’s own line.z

Replace characters: ".z" 

with ".\n" 

\n = new line character

Find and Replace Screen

Revised Text

my first try at fixing text with regex.

I want to replace the period with a period and a new line character.

I should be able to do this using the proper regex on find and replace expressions.

Then I see each sentence on its own line.

Candidate Solutions

Step-by-step guide for Example

sample code block

Recommended Next Steps

Related articles