Calc Guide

Chapter 13
Calc as a Database
A guide for users and macro programmers

CopyrightThis document is Copyright © 2020 by the LibreOffice Documentation Team. Contributors are listed
below. You may distribute it and/or modify it under the terms of either the GNU General Public
License (http://www.gnu.org/licenses/gpl.html), version 3 or later, or the Creative Commons
Attribution License (http://creativecommons.org/licenses/by/4.0/), version 4.0 or later.
All trademarks within this guide belong to their legitimate owners.

Contributors
This book is adapted and updated from the LibreOffice 6.4 Calc Guide.

To this edition
Steve Fanning

Felipe Viggiano

To previous editions
Andrew Pitonyak
Simon Brydon
Steve Fanning

Barbara Duprey
Kees Kriek
Leo MoonsJean Hollis Weber
Zachary Parliman

Feedback
Please direct any comments or suggestions about this document to the Documentation Team’s
mailing list: documentation@global.libreoffice.org.

Note
Everything you send to a mailing list, including your email address and any otherpersonal information that is written in the message, is publicly archived and cannot bedeleted.

Publication date and software version
Published November 2020. Based on LibreOffice 7.0.

Using LibreOffice on macOS
Some keystrokes and menu items are different on macOS from those used in Windows and Linux.
The table below gives some common substitutions for the instructions in this book. For a moredetailed list, see the application Help and Appendix A (Keyboard Shortcuts) to this guide.
Windows or LinuxmacOS equivalent

EffectTools > Options menu
selection

LibreOffice > Preferences

Access setup options

Right-click

Control+click and/or right-click
depending on computer setup

Open a context menu

Ctrl (Control)⌘ (Command)

Used with other keys

Ctrl+Q⌘+Q

Exit / quit LibreOffice

F11

⌘+T

Open the Sidebar’s Styles deck

Documentation for LibreOffice is available at https://documentation.libreoffice.org/en/Contents
Copyright..............................................................................................................................2
Contributors................................................................................................................................. 2
To this edition.......................................................................................................................... 2
To previous editions................................................................................................................ 2
Feedback..................................................................................................................................... 2
Publication date and software version.........................................................................................2
Using LibreOffice on macOS........................................................................................................2

Introduction..........................................................................................................................4
A database primer........................................................................................................................ 4
Calc as a database-like program.................................................................................................5

Associating a range with a name.......................................................................................5
Named ranges............................................................................................................................. 5
Creating named ranges with macros.......................................................................................7
Using relative references with named expressions.................................................................9
Creating named ranges using row or column headers..........................................................10
Creating named ranges from labels using macros................................................................10
Database ranges....................................................................................................................... 12Creating database ranges with macros.................................................................................14

Sorting.................................................................................................................................15
Sorting a table using one column with a macro..........................................................................15
Sorting a table using multiple columns.......................................................................................17
Retrieving sorting information from a range...............................................................................18

Filtering...............................................................................................................................19
AutoFilter................................................................................................................................... 19
Toggling AutoFilters with a macro..........................................................................................20
Standard filters........................................................................................................................... 20
Creating standard filters with macros....................................................................................21Clearing all filters for a sheet.................................................................................................23
Filtering multiple columns and filtering with regular expressions...........................................23
Advanced filters......................................................................................................................... 24
Advanced filter example........................................................................................................25
Using an advanced filter with macros....................................................................................26
Copy advanced filter results to a different location................................................................27

Useful database-like functions.........................................................................................28
Database-specific functions.......................................................................................................30

Chapter 13 Calc as a Simple Database | 3

Introduction
In many everyday scenarios, Calc spreadsheets can be used to aggregate sets of data and to
perform analyses on them. As the data in a spreadsheet is laid out in a table view, plainly visible
and easily edited or extended, some users may not need the comprehensive relational databasefacilities provided by the Base component of LibreOffice. For such users, Calc has sufficient
functionality to act as a simple yet capable database-like platform. This chapter presents an
overview of these capabilities and explains them using LibreOffice Basic macros and userinterface examples.
For those users who initially choose to manage their data in a Calc spreadsheet andsubsequently decide that they need to use a more comprehensive database system, migrating
Calc data to Base is straightforward. See the Base Guide for more information.
Though it was created for macro programmers, this chapter is intended to be accessible for allusers. If you do not want to use macros, simply skip the sections that deal with them. However, if
you are interested in learning more about them, see Chapter 12, Macros, and Andrew Pitonyak’s
book, OpenOffice.org Macros Explained (OOME). Much of the macro information in this chapter
is drawn or adapted from the OOME and LibreOffice’s API reference at
https://api.libreoffice.org/docs/idl/ref/index.html.

A database primer
In a typical database, related data is organized into tables, which are arranged in a grid-likeseries of rows and columns similar to a spreadsheet. Each row of a table represents a data
record, while each column represents a field within each record. Each cell in a field contains an
individual data item or attribute, such as a name, while each record consists of related attributes
that correspond to a single entity, like a person. A database table tends to have a fixed number offields, but can have an indefinite number of records.
While a table may have hundreds or thousands of rows, individual records can be easily found,
retrieved, and updated using information requests, called queries, that search for records thatmeet a specified set of criteria. It is this ease of access that makes a database table more usefulthan simply filing away information in an unordered spreadsheet.
To illustrate this concept of a database table, consider the example of a class grading sheet(Figure 1). In this sheet, each row represents individual students taking the class, while eachcolumn contains their names and grades. With this table, you can quickly look up individual
students’ grades simply by searching for their names, and you can determine which students are
passing the class by filtering out records with failing average scores.

Figure 1: Grading sheet example

4 | Chapter 13 Calc as a Simple Database

NoteThis simple tabular design is based on the relational database model, which is one of
the most common and well-known design models used in modern databases. The
Base component of LibreOffice is a fully featured relational database management
system.

Calc as a database-like program
In some respects, a Calc sheet is similar to a database table, and it is even possible for a
database table to be contained in a Calc sheet. Calc offers several features, particularly sortingand filtering, that allow users to search its tables, equivalent to the facilities that would be foundin a database program such as LibreOffice Base. While this does not make Calc a replacement
for a fully featured database application, it is nevertheless useful for managing data in a smallscale personal or professional context.

Associating a range with a name
In order to set up a database table in a Calc sheet, you first need to set up an area for it tooccupy. This is necessary since some of Calc’s database-like features depend on accessing or
modifying a table’s location. Such an area is represented by a range, which is a contiguous groupof one or more cells. To make the range for a table easy to access, you can assign a meaningful
name to it. Doing this has four particular benefits:•

Giving a range a name makes it easier to identify, especially if you are working withmultiple ranges in a document.

•

A named range can be referenced by its name rather than just by its address. For
example, if you have a range named Scores, you can simply reference it in a cell with aformula like =SUM(Scores).

•

References by name to a named range are automatically updated every time therange’s address is changed. This avoids the need to change individual references
every time a range’s location is modified.

•

All named ranges can be quickly viewed and accessed through the Navigator,
which is opened by selecting View > Navigator on the Menu bar, pressing the F5 key, or
clicking the Navigator icon on the Sidebar tab panel.

Two types of named range exist in Calc: database ranges, which store settings for database-like
operations, and standard named ranges, which do not.

Named ranges
Standard named ranges are created using the Define Name dialog (Figure 2), which is opened
by selecting Sheet > Named Ranges and Expressions > Define on the Menu bar.

Associating a range with a name | 5

Figure 2: Define Name dialog
To create a named range, select a range of cells from a sheet, then open the Define Namedialog. Next, give the range a meaningful name, and click on Add to add it to the current
document’s list of named ranges. You can then access and modify these ranges using theManage Names dialog, which is opened by selecting Sheet > Named Ranges and Expressions> Manage on the Menu bar, or pressing Ctrl+F3 (Figure 3). For more detail about how to create
and manage ranges, see Chapter 6, Printing, Exporting, E-mailing, and Signing, and Chapter 7,
Using Formulas and Functions.

Figure 3: Manage Names dialog

6 | Chapter 13 Calc as a Simple Database

Creating named ranges with macros
In a macro, a named range is accessed, created, and deleted using the NamedRanges service ofa Calc document. This service has a number of methods associated with it, the following of which
are particularly useful for creating named ranges:
getByName(Name)
Returns the range or expression with the specified name.
getElementNames()
Returns an array of all named ranges in the current document.
hasByName(Name)
Returns a boolean: TRUE if a range with this name is in the current document, and FALSE
otherwise.
addNewByName(Name, Content, Position, Type)
Adds a new named range to the current document. This method has four arguments:– Name – A string that contains the name of the new range– Content – A string that contains the range address or formula expression being named– Position – The base address for relative cell references– Type – A combination of flags that specify the type of named range being defined.
These flags are listed in Table 1. This parameter defaults to zero for any common
named range.
Table 1: com.sun.star.sheet.NamedRangeFlag constant group reference
Value

Name

Description

1

FILTER_CRITERIAThe range contains filter criteria.

2

PRINT_AREA

The range can be used as a print range.

4

COLUMN_HEADER

The range can be used as column headers for printing.

8

ROW_HEADER

The range can be used as row headers for printing.

As an example, the macro in Listing 1 uses the above methods to check if a range named
MyNRange exists. If it does not exist, then the macro creates a range with the name and sets it
to access the cell range B3:D6.
Listing 1: AddNamedRange creates a new named range that references $Sheet1.$B$3:$D$6
Sub AddNamedRange()

Dim oRange' The created range.

Dim oRanges' All named ranges.

Dim sName$' Name of the named range to be created.

Dim oCell' Cell object.

Dim s$
sName$ = "MyNRange"oRanges = ThisComponent.NamedRanges
If NOT oRanges.hasByName(sName$) Then
REM Setting the base address for relative cell references
Dim oCellAddress As new com.sun.star.table.CellAddress
Associating a range with a name | 7

oCellAddress.Sheet = 0'The first sheet.

oCellAddress.Column = 1'Column B.

oCellAddress.Row = 2'Row 3.

REM The first argument is the range name.
REM The second argument is a string that defines the formula
REM or expression to be used.
REM The third argument specifies the base address for
REM relative cell references.
REM The fourth argument is a set of flags that define
REM how the range is used, but most ranges use 0.
REM The fourth argument uses values from the
REM NamedRangeFlag constants.
s$ = "$Sheet1.$B$3:$D$6"
oRanges.addNewByName(sName$, s$, oCellAddress, 0)
End If
REM Get the created named range.
oRange = ThisComponent.NamedRanges.getByName(sName$)
REM Print the string contained in cell $Sheet1.$B$3
oCell = oRange.getReferredCells().getCellByPosition(0,0)
Print oCell.getString()
End Sub

Using relative references with named expressions
If a named range uses any cell addresses that are not absolute, then these addresses will bereferenced relative to the range’s base address, which is defined by the third argument of the
addNewByName method, Position. This behavior is illustrated in Listing 2, where the macro
AddNamedFunction creates the named expression AddLeft. This expression references theequation A3+B3 with C3 as its base address. Because relative references are being used,
AddLeft sums the values of the two cells directly to the left of any cell containing the equation=AddLeft. For example, if AddLeft is referenced in cell E5, then it will sum the values in C5 andD5 (Figure 4).

Note
For more information about absolute and relative references, see Chapter 7, Using
Formulas and Functions.
Listing 2: AddNamedFunction creates the AddLeft named formula expression
Sub AddNamedFunction()

Dim oSheet
Dim oCellAddress'Sheet that contains the range oRange.
'Address for relative references.

8 | Chapter 13 Calc as a Simple DatabaseDim oRanges'The NamedRanges property.

Dim oRange'Single cell range.

Dim sName As String 'Name of the equation to create.
sName = "AddLeft"oRanges = ThisComponent.NamedRanges
If NOT oRanges.hasByName(sName) ThenoSheet = ThisComponent.getSheets().getByIndex(0)oRange = oSheet.getCellRangeByName("C3")oCellAddress = oRange.getCellAddress()oRanges.addNewByName(sName, "A3+B3", oCellAddress, 0)
End IfEnd Sub

Figure 4: AddLeft sums the values from C5 and D5 in E5

Tip
Listing 2 illustrates another little-known attribute of Calc: named ranges are a subset
of named expressions, which can include formulas as well.

Creating named ranges using row or column headersWith the Create Names tool, which is accessed by selecting Sheet > Named Ranges andExpressions > Create on the Menu bar (Figure 5), you can create multiple named ranges
simultaneously from the headers of a table. These headers can be drawn from the table’s
borders – top and bottom rows, and left and right columns – and each row or column that
corresponds to a header is used to create the named ranges themselves. For example, if you
choose to create ranges from headers contained in the top row of a table, each range will be
generated from the individual columns that correspond to each header label.

Note
Header cells are not included in the named ranges generated using the Create
Names tool. This is because the labels in each of these cells are used to name theranges.

Associating a range with a name | 9

Figure 5: Create Names dialog
To use the Create Names tool:
1) In a sheet, select the table from which to create the named ranges. Be sure to include theheader rows or columns as part of your selection.
2) Open the Create Names dialog by selecting Sheet > Named Ranges and Expressions> Create on the Menu bar.
3) Calc automatically identifies which rows or columns contain headers, and will mark thecheckboxes – Top row, Left column, Bottom row, Right column – that apply. However,
if you wish to change this selection, you can manually select or deselect any of the boxes
at this point.
4) Click OK to close the dialog and create the new named ranges.

Creating named ranges from labels using macros
In addition to the Create Names dialog, named ranges can be generated from labels using themacro method addNewFromTitles:
addNewFromTitles(Source, Border)
Creates named ranges from column or row headers. This method has two arguments:– Source – The cell range address of the named range to be created– Border – Is an enumeration value that specifies the location of the header labels. This
enumeration has one of four possible values (Table 2):
Table 2: com.sun.star.sheet.Border enumeration values
Enumerator

Description

TOP

Selects the top border row.

BOTTOM

Selects the bottom border row.

RIGHT

Selects the right border column.

LEFT

Selects the left border column.

Tip
To generate names from multiple borders, you must call addNewFromTitles for eachheader row or column that you wish to use.
The macro in Listing 3 creates three named ranges using headers from the top row of the range
A1:C20 (Figure 6). Figure 7 shows the resulting ranges listed in the Manage Names dialog,
which is accessed by selecting Sheet > Named Ranges and Expressions > Manage on the
Menu bar or pressing Ctrl+F3.

10 | Chapter 13 Calc as a Simple Database

Figure 6: Example range A1:C20
Listing 3: AddManyNamedRanges creates named ranges using labeled columns
Sub AddManyNamedRanges()
Dim oSheet'Sheet that contains the named range.

Dim oAddress'Range address.

Dim oRanges'The NamedRanges property.

Dim oRange'Single cell range.

oRanges = ThisComponent.NamedRanges
oSheet = ThisComponent.getSheets().getByIndex(0)oRange = oSheet.getCellRangeByName("A1:C20")
oAddress = oRange.getRangeAddress()oRanges.addNewFromTitles(oAddress, com.sun.star.sheet.Border.TOP)
End Sub

Figure 7: Upper section of Manage Names dialog, showing generated
named ranges

Associating a range with a name | 11

Caution
Avoid giving multiple rows or columns the same label, as the ranges generated fromthem will likewise share the same name, and can end up being overwritten by Calc.

Database ranges
Although it can be used like a regular named range, a database range is, unsurprisingly, meant
to be used like a database table, with each row representing a record and each cell a field withina record. Specifically, a database range differs from a named range in the following ways:•

A database range cannot be a formula expression, only a cell range. This range can beformatted as a table, with the first row reserved for headings and the last row for
subtotals. Cell formatting can also be preserved for each field in the table.

•

Database ranges cannot be referenced relative to a base address within a sheet.

•

Database ranges store sorting, filtering, subtotaling, and data import settings in data
structures called descriptors, which can be retrieved and accessed using macros.

•

A database range can be linked to an external database source, and can be refreshed by
selecting Data > Refresh Range on the Menu bar. Registering and linking to external
database sources are explained in more detail in Chapter 10, Linking Data.

•

Database ranges can be created, modified, and deleted using the Define Database
Range dialog, which is opened by selecting Data > Define Range on the Menu bar(Figure 8).

Figure 8: Define Database Range
dialog

12 | Chapter 13 Calc as a Simple DatabaseTo create a database range:
1) Select a range of cells from a sheet.
2) Open the Define Database Range dialog by selecting Data > Define Range on the Menubar.
3) Type a name for the range in the Name field. Only use letters, numbers, andunderscores; spaces, hyphens, and other characters are not allowed.
4) Click on the expand (usually a + or a triangle) sign next to the Options label to expand
this section and view and select the following options:– Contains column labels – Denotes whether the top row is reserved for field headings.
– Contains totals row – Denotes whether the bottom row is reserved for totals.
– Insert or delete cells – If active, this option will insert new rows and columns into the
database range when new records are added to its source. Only works if an external
database source is linked to the range. To manually update the database range, use
Data > Refresh Range on the Menu bar.
– Keep formatting – Applies the existing cell formats of the first data row to the whole
database range.
– Don’t save imported data – If selected, this option only saves a reference to thesource database; the contents of the range’s cells are not preserved.
– Source – Displays information about the current database source, if one exists.
– Operations – Denotes what operations, such as sorting or filtering, have been applied
to the database range.
5) Click Add to add a range to the database range list under the Name field.
6) Click OK to close the dialog and save the database range.
To modify an existing database range:
1) Open the Define Database Range dialog by selecting Data > Define Range on the Menu
bar.
2) Select a range from the range list under the Name field or type its name into the Name
field. The Add button will change to Modify at this point.
3) Make any modifications in the Range field and the Options section.
4) Click Modify to update the range.
5) Click OK to close the dialog and save the modified database range.
To delete an existing database range
1) Open the Define Database Range dialog by selecting Data > Define Range on the Menu
bar.
2) Select the range to be deleted from the list in the upper section of the dialog.
3) Click Delete.
4) Click OK to close the dialog and confirm deletion of the database range.
To select an existing database range from the current document, open the Select Database
Range dialog by choosing Data > Select Range on the Menu bar (Figure 9). Next, select a range
from the Ranges list and click OK. Another way to select an existing database range is using theNavigator, accessed on the Sidebar deck or through View > Navigator on the Menu bar. Calc
will automatically highlight the range’s position in the sheet in which it is located.

Associating a range with a name | 13

Figure 9: Select Database Range
dialogCreating database ranges with macros
In a macro, a database range is created, accessed, and deleted using the DatabaseRanges
service. This service has many of the same methods as the NamedRanges service, but lacks the
addNewFromTitles method. DatabaseRanges also uses a reduced version of the
addNewByName method that lacks arguments for a relative base address and range type:
addNewByName(Name, Range)
Adds a new database range to the current document.
As an example of creating a range using this method, the macro in Listing 4 creates a databaserange named MyName and automatically applies auto filters to each of the range’s columns:
Listing 4: AddNewDatabaseRange creates a database range and applies an auto filter
Sub AddNewDatabaseRange()
Dim oRange 'DatabaseRange object.
Dim oAddr'Cell address range for the database range.

Dim oSheet 'First sheet, which will contain the range.
Dim oDoc'Reference ThisComponent with a shorter name.

oDoc = ThisComponent
If NOT oDoc.DatabaseRanges.hasByName("MyName") ThenoSheet = ThisComponent.getSheets().getByIndex(0)oRange = oSheet.getCellRangeByName("A1:F10")oAddr = oRange.getRangeAddress()
oDoc.DatabaseRanges.addNewByName("MyName", oAddr)
End IfoRange = oDoc.DatabaseRanges.getByName("MyName")oRange.AutoFilter = True
End Sub

14 | Chapter 13 Calc as a Simple Database

Sorting
Sorting is the process of rearranging data in a range or a sheet according to a specified sortorder. In Calc, sorting is commonly done using the Sort dialog, which is accessed by selecting
Data > Sort on the Menu bar. How to use this dialog and its options is described in further detailin Chapter 2, Entering, Editing, and Formatting Data. Here, we present how to sort data in thecontext of macros.

Figure 10: Sort dialog

Sorting a table using one column with a macro
In a Calc macro, data within a range is sorted by calling the sort() method on the range object.
When a sort operation is called on a range, an array of properties known as a sort descriptor ispassed to the sort method. Contained within a descriptor’s properties are sort fields, which are
structures that inform Calc how to sort a range according to the data contained within one of its
rows or columns.
As an example, the macro in Listing 5 sorts the grade sheet from Figure 1 according to average
grade in descending order. The results are shown in Figure 11.
Listing 5: SortAverageGrade sorts the grade sheet data range (A1:H11) using a single column
Sub SortAverageGradeDim oSheet
Dim oRange
Dim oSortFields(0) as new com.sun.star.util.SortField
Dim oSortDesc(0) as new com.sun.star.beans.PropertyValueoSheet = ThisComponent.Sheets(0)REM Set the range on which to sortoRange = oSheet.getCellRangeByName("A1:H11")

Sorting | 15

REM Sort by the Average grade field in the range in descending order
oSortFields(0).Field = 7
oSortFields(0).SortAscending = FALSEREM Set the sort fields to use
oSortDesc(0).Name = "SortFields"
oSortDesc(0).Value = oSortFields()
REM Now sort the range!
oRange.Sort(oSortDesc())
End Sub

Figure 11: Grading sheet after sorting by average grade in descending order

Sorting a table using multiple columns
As with the Sort dialog, a range can be sorted using up to three columns or rows in a macro.
Sorting with extra columns or rows is as easy as adding extra sort fields to a sort descriptor. The
macro in Listing 6 again uses the grade sheet example from Figure 1 to illustrate how to sort bytwo columns. Figure 12 shows the results of this operation – note that records are sorted inascending order, first by Quiz #1 scores and then by Quiz #2 scores.
Listing 6: SortByQuizScores sorts the grade sheet data range (A1:H11) using two columns
Sub SortByQuizScores
Dim oSheet
Dim oRange
Dim oSortFields(1) as new com.sun.star.util.SortField
Dim oSortDesc(0) as new com.sun.star.beans.PropertyValueoSheet = ThisComponent.Sheets(0)REM Set the range on which to sortoRange = oSheet.getCellRangeByName("A1:H11")

16 | Chapter 13 Calc as a Simple Database

REM Sort by the Quiz #1 field in the range
oSortFields(0).Field = 4
oSortFields(0).SortAscending = True
oSortFields(0).FieldType = com.sun.star.util.SortFieldType.NUMERICREM Sort by the Quiz #2 field in the range
oSortFields(1).Field = 5
oSortFields(1).SortAscending = True
oSortFields(1).FieldType = com.sun.star.util.SortFieldType.ALPHANUMERICREM Set the sort fields to use
oSortDesc(0).Name = "SortFields"
oSortDesc(0).Value = oSortFields()
REM Now sort the range!
oRange.Sort(oSortDesc())
End Sub

Figure 12: Grading sheet sorted by quiz scores in ascending order

Retrieving sorting information from a range
You can use the method createSortDescriptor() to retrieve the sorting information for a given cellrange. If this method is called on a database range, it will create a sort descriptor using thesorting information stored with that range. On the other hand, if createSortDescriptor() is called
on a standard named range or an unnamed range, it will generate a sort descriptor with default
properties. In either case, the newly-generated sort descriptor can be modified and passed as anargument to a sort method called on a given range.
The macro in Listing 7 demonstrates how to generate and display the sorting information
associated with a range. The output of this macro is displayed in Figure 13.
Listing 7: DisplaySortDescriptor displays sort descriptor properties in a dialog
Sub DisplaySortDescriptor
On Error Resume Next

Sorting | 17

Dim oSheet
Dim oRange' A range is needed to create the sort descriptor.

Dim oSortDescript
Dim i%
Dim s$
Dim oDoc'Reference newly created calc document.

oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_default",
0, Array())
oSheet = oDoc.Sheets(0)oRange = oSheet.getCellRangeByName("B28:D33")
oSortDescript = oRange.createSortDescriptor()
For i = LBound(oSortDescript) To UBound(oSortDescript)s = s & oSortDescript(i).Name & " = "s = s & oSortDescript(i).Values = s & CHR$(10)Next
MsgBox s, 0, "Sort Descriptor"
End Sub

Figure 13: Sort descriptor
properties

Filtering
A filter is a tool that hides or displays records within a sheet based on a set of filtering criteria.
Similar to sorting, filters are useful for narrowing down long lists of data in order to find particular
data items. In Calc, three types of filter exist:•

AutoFilters•

Standard filters•

Advanced filters.

Filters are also described in Chapter 2, Entering, Editing, and Formatting Data.
18 | Chapter 13 Calc as a Simple Database

AutoFilter
AutoFilters are the most straightforward of the three filter types, and work by inserting a combo
box into one or more data columns (Figure 14). To add an AutoFilter to one or more columns,
simply select the columns, then select Data > AutoFilter on the Menu bar. To access the
AutoFilter combo box for a column, click on the down-arrow button in the first cell of that column.
To remove an AutoFilter, select the columns again and select Data > AutoFilter on the Menu bar.
Each combo box and down-arrow button will disappear. In this way, the menu option acts like a
toggle for AutoFilters. To add or remove an AutoFilter to all columns at the same time, do notselect any specific column before selecting the Data > AutoFilter option, just click on any cell of
the table prior toggle the AutoFilter on or off.
Each AutoFilter combo box has the following set up of criteria:•

A basic sort can be applied using the Sort Ascending and Sort Descending options.

•

Selecting the Top 10 filter causes the ten rows with the largest values to be displayed.
More than ten rows may be displayed if there are multiple instances of large values in acolumn. For example, if there are eleven students with a perfect score of 100, then thefilter will display all eleven instances. Similarly, if there are six students with a score of
100 and six other students with a score of 99, then the filter will display twelve instances.

•

Selecting Empty hides all non-empty rows that contain a value in the current column.
Likewise, selecting Not Empty hides all non-empty rows that lack a value in the current
column. Entirely empty rows are ignored.

•

The Standard Filter option opens the Standard Filter dialog (Figure 15) andautomatically sets the current field as the field for the first condition in the dialog.

•

Check the All box to display or hide all values in the current column.

•

The auto filter creates entries for each unique value in the current column. These values
can be filtered simply by checking off any of the check boxes next to each entry.

Figure 14: Auto filter combo box

Filtering | 19

Toggling AutoFilters with a macro
Database ranges contain an AutoFilter boolean flag that allows you to toggle auto filters on oroff. The macro from Listing 4 demonstrates how to do this.

Standard filters
Standard filters are more complex than AutoFilters, and allow for up to eight filter conditions.
Also, unlike AutoFilters, standard filters use a dialog (Figure 15), which is accessed by selecting
Data > More Filters > Standard Filter on the Menu bar.
For more information on how to use this dialog and its options, see Chapter 2, Entering, Editing,and Formatting Data.

Figure 15: Standard Filter dialog

Creating standard filters with macros
In a macro, filtering a data range is performed by calling the filter() method either on the range or
the sheet in which the range is contained. Like a sort descriptor, a filter descriptor contains thefilter settings for the current sheet, such as whether the first row or column in the sheet contains
headers which should not be filtered. A filter descriptor can be generated by calling thecreateFilterDescriptor() method on a sheet or on a cell range:
createFilterDescriptor(Empty)
Creates a filter descriptor. If the boolean flag Empty is set to TRUE, then an empty filter
descriptor is created. If Empty is FALSE, then the descriptor is filled with the previous
settings of the current object (such as a database range).
As with sort descriptors, filter descriptors can be created using this method, then modified andpassed as an argument to the filter method. The macro in Listing 8 demonstrates this process bycreating a simple standard filter for the first sheet in a document. Figure 16 display the results offiltering the grading sheet example in Figure 1:
Listing 8: SimpleSheetFilter creates a simple standard filter
Sub SimpleSheetFilter()Dim oSheet' Sheet that will contain the filter.

Dim oFilterDesc' Filter descriptor.

Dim oFields(0) As New com.sun.star.sheet.TableFilterField

20 | Chapter 13 Calc as a Simple Database

oSheet = ThisComponent.getSheets().getByIndex(0)REM If argument is True, creates an empty filter
REM descriptor. If argument is False, create a
REM descriptor with the previous settings.
oFilterDesc = oSheet.createFilterDescriptor(True)
With oFields(0)REM You could use the Connection property to indicate
REM how to connect to the previous field. This is
REM the first field, so this is not required.
'.Connection = com.sun.star.sheet.FilterConnection.AND'.Connection = com.sun.star.sheet.FilterConnection.OR
REM The Field property is the zero based column
REM number to filter. If you have the cell, you
REM can use .Field = oCell.CellAddress.Column.
.Field = 5' The Quiz #2 grades field

REM Compare using a numeric or a string?
.IsNumeric = True
REM The NumericValue property is used
REM because .IsNumeric = True from above.
.NumericValue = 80
REM If IsNumeric was False, then the
REM StringValue property would be used.
REM .StringValue = "what ever"
REM Valid operators include EMPTY, NOT_EMPTY, EQUAL,REM NOT_EQUAL, GREATER, GREATER_EQUAL, LESS,REM LESS_EQUAL, TOP_VALUES, TOP_PERCENT,REM BOTTOM_VALUES, and BOTTOM_PERCENT.Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL
End With
REM The filter descriptor supports the following
REM properties: IsCaseSensitive, SkipDuplicates,
REM UseRegularExpressions,
REM SaveOutputPosition, Orientation, ContainsHeader,

Filtering | 21

REM CopyOutputData, OutputPosition, and MaxFieldCount.
oFilterDesc.setFilterFields(oFields())
oFilterDesc.ContainsHeader = True
oSheet.filter(oFilterDesc)
End Sub

Figure 16: Grading sheet filtered by Quiz #2 values greater than 80

Note
When the filter method is called on a sheet, every empty row in the sheet is hidden.
When filter is called on a range, only empty rows in the range itself are hidden.

Clearing all filters for a sheet
When a filter is applied to a sheet, it replaces any existing filter for that sheet. Therefore, toremove a filter in a sheet, simply create and set an empty filter for that sheet (Listing 9).
Listing 9: RemoveSheetFilter removes the current sheet filter by setting an empty filter
Sub RemoveSheetFilter()Dim oSheet' Sheet to filter.

Dim oFilterDesc' Filter descriptor.

oSheet = ThisComponent.getSheets().getByIndex(0)oFilterDesc = oSheet.createFilterDescriptor(True)
oSheet.filter(oFilterDesc)
End Sub

Filtering multiple columns and filtering with regular expressions
The macro in Listing 10 demonstrates a filter that filters two columns and uses regularexpressions. Note that the filter method is called on a range rather than its sheet in this example.
Figure 17 displays the results of this macro on the grading sheet example in Figure 1.
Listing 10: SimpleRangeFilter uses two columns
Sub SimpleRangeFilter()Dim oSheet' Sheet to filter.

Dim oRange' Range to be filtered.

Dim oFilterDesc' Filter descriptor.

Dim oFields(1) As New com.sun.star.sheet.TableFilterField

22 | Chapter 13 Calc as a Simple Database

oSheet = ThisComponent.getSheets().getByIndex(0)oRange = oSheet.getCellRangeByName("A1:H11")
REM If argument is True, creates an
REM empty filter descriptor.
oFilterDesc = oRange.createFilterDescriptor(True)
REM Setup a field to view cells with content that
REM start with the letter B.
With oFields(0).Field = 0' Filter column A (Student names).

.IsNumeric = False' Use a string, not a number.

.StringValue = "b.*"' Every name starting with a B.

.Operator = com.sun.star.sheet.FilterOperator.EQUAL
End With
REM Set up a field that requires at least one of the conditions.
REM This new condition requires a value less than or
REM equal to 90.
With oFields(1).Connection = com.sun.star.sheet.FilterConnection.OR.Field = 6' Filter column G (Test #1 grades).

.IsNumeric = True

' Use a number.NumericValue = 90

' Scores less than 90.Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
End WithoFilterDesc.setFilterFields(oFields())
oFilterDesc.ContainsHeader = TrueoFilterDesc.UseRegularExpressions = TrueoRange.filter(oFilterDesc)
End Sub

Figure 17: Grading sheet filtered by Test #1 scores <= 90% and student namesthat begin with “B”
Filtering | 23

Advanced filters
The criteria for an advanced filter are stored in a sheet rather than entered into a dialog. As aresult, you must first set up a cell range that contains the criteria before you use the Advanced
Filter dialog (Figure 18).

Figure 18: Advanced Filter dialog
To set up a criteria range:
1) Copy the column headings of the range to be filtered to an empty space in a sheet. It
does not need to be the same sheet as the one with the source range.
2) Enter filter criteria underneath the column headings in the criteria range. Each individual
criterion in the same row is connected with AND, while the criteria groups from each row
are connected with OR. Empty cells are ignored. Up to eight criteria rows may be definedfor a filter.
After creating a criteria range, set up an advanced filter as follows:
1) Select the cell range that you wish to filter.
2) Go to Data > More Filters > Advanced Filter on the Menu bar to open the Advanced
Filter dialog (Figure 18).
3) In the Read Filter Criteria From field, enter the address for the criteria range, either byselecting a named range from the drop-down box, typing in a reference, or selecting cells
from a sheet. Remember to use the Shrink / Expand button if you need to temporarily
minimize the dialog while selecting cells.
4) Click OK to apply the filter and close the dialog.
Advanced filter options are the same as standard filter options, and are described in further detailin Chapter 2, Entering, Editing, and Formatting Data.

Advanced filter example
Figure 19 demonstrates an example filter range for the grading sheet example in Figure 1:

Figure 19: Advanced filter criteria range (in Sheet 2)

24 | Chapter 13 Calc as a Simple DatabaseIn this range, there are two criteria groups: the first displays the records of students who scoredabove a 75% in every homework, and the second displays records of any student named
Ferdinand. Figure 20 displays the result of this filter operation using these criteria:

Figure 20: Grading sheet example filtered using an advanced filter

Using an advanced filter with macros
Applying an advanced filter with a macro works similarly to setting up a standard filter. The key
difference is that the cell range containing the filter criteria is used to create the filter descriptor.
The macro in Listing 11 demonstrates how this is done using the createFilterDescriptorByObject
method. It uses the grading sheet example in Figure 1 as the data range and the range in Figure
19 as the criteria range. The results should be the same as those found in Figure 20:
Listing 11: AdvancedRangeFilter creates a filter descriptor from the filter criteria range
Sub AdvancedRangeFilter()Dim oSheet'A sheet from the Calc document.

Dim oRanges'The NamedRanges property.

Dim oCritRange 'Range that contains the filter criteria.
Dim oDataRange 'Range that contains the data to filter.
Dim oFiltDesc'Filter descriptor.

REM Range that contains the filter criteria
oSheet = ThisComponent.getSheets().getByIndex(1)oCritRange = oSheet.getCellRangeByName("A1:H3")
REM You can also obtain the range containing the
REM filter criteria from a named range.
REM oRanges = ThisComponent.NamedRanges
REM oRange = oRanges.getByName("AverageLess80")
REM oCritRange = oRange.getReferredCells()
REM The data that you want to filter
oSheet = ThisComponent.getSheets().getByIndex(0)oDataRange = oSheet.getCellRangeByName("A1:H11")
oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)oDataRange.filter(oFiltDesc)

Filtering | 25

End Sub

Table 3 contains a list of properties that correspond to advanced and standard filter settings.
Table 3: Advanced and standard filter properties
Function

Description

ContainsHeader

Boolean (TRUE/FALSE) that specifies if the first row or column
contains headers which should not be filtered.

CopyOutputData

Boolean that specifies if the filtered data should be copied to anotherposition in the document.

IsCaseSensitive

Boolean that specifies if the case of letters is important whencomparing entries.

Orientation

An enumeration that specifies if a range is filtered by column or row:
Column – com.sun.star.table.TableOrientation.COLUMNS
Row – com.sun.star.table.TableOrientation.ROWS

OutputPosition

If CopyOutputData is TRUE, this property specifies the position where
filtered data are to be copied.

SaveOutputPosition

Boolean that specifies if the OutputPosition position is saved for future
calls.

SkipDuplicates

Boolean that specifies if duplicate entries are left out of the result.

UseRegularExpressions

Boolean that specifies if the filter strings are interpreted as regularexpressions.

Copy advanced filter results to a different location
The results of an advanced filter can be extracted to a different position using the OutputPosition
property. Copying results in this way eliminates the need for Calc to hide rows that do not matchsearch criteria, which it would normally do if you filter in-place.
The macro code snippet in Listing 12 demonstrates how to copy filter results to a different
location, and Figure 21 shows the results when this snippet is applied to the macro in Listing 11
just before the filter method is called. Note that the filter descriptor must first be modified before
these filter settings are applied.
Listing 12: A code snippet that copies filtered results to a different locations
REM Copy the output data rather than filter in place.
oFiltDesc.CopyOutputData = True

REM Create a CellAddress and set it for Sheet1
REM Column B, Row 13 (remember, start counting with 0)
Dim outputCell As New com.sun.star.table.CellAddress

outputCell.Sheet = 0
outputCell.Column = 1
outputCell.Row = 12
oFiltDesc.OutputPosition = outputCell

26 | Chapter 13 Calc as a Simple Database

Note
The OutputPosition property returns a copy of a struct. As a result, it is not possible to set
individual values, such as the row or column, directly through this property. For example,
oFilterDesc.OutputPosition.Row = 2 will not work, since it is the Row property on
the copy, not the original, that changes.

Figure 21: Advanced filter results copied to cell B13

Useful database-like functions
Calc has many functions that are often used in the context of databases. Some of these functions
are straightforward to use (like SUM) or are familiar in the context in which they are typically used
(like STDEV for statistics). A few, like the LOOKUP functions, are somewhat more infrequently
used, but are nevertheless useful to know if you plan to use Calc for database tables. Thissection provides a condensed list of some of these functions. Further reference material for all
Calc’s functions can be found in the Help system.

Note
Functions with the suffix -A treat text values as a number with the value of zero.
Blank cells are still ignored by these functions.
Function

Category

Description

AVERAGE

Statistical

Returns the average of its arguments. Ignores empty cells
and cells that contain text.

AVERAGEA

Statistical

Returns the average of its arguments, but only ignores empty
cells. The value of text is 0.

AVERAGEIF

Statistical

Returns the arithmetic mean of all cells in a range that satisfy
a given condition.

AVERAGEIFS

Statistical

Returns the arithmetic mean of all cells in a range that satisfy
given multiple criteria
Useful database-like functions | 27

Function

Category

Description

COUNT

Statistical

Counts the number of numeric values in a list of arguments.
Ignores empty cells and cells that contain text.

COUNTA

Statistical

Counts the number of values in a list of arguments, butcounts both numeric and text arguments. Empty cells are still
ignored.

COUNTBLANK

Statistical

Returns the number of empty cells within a range.

COUNTIF

Statistical

Returns the number of cells in a range that meet the
specified search criteria.

COUNTIFS

Statistical

Returns the number of cells that meet criteria in multiple
ranges.

HLOOKUP

Spreadsheet

Searches for a “look-up” value in the first row of an array andreturns a value from a different row in the same column.

INDEX

Spreadsheet

Returns the contents of a cell at a specified index (denotedby row and column numbers) within a range.

INDIRECT

Spreadsheet

Returns the reference specified by a text string.

LOOKUP

Spreadsheet

Returns the contents of a cell contained within a single row
or column of a range or from an array.

MATCH

Spreadsheet

Searches an array for an item and returns its relative positionin the array.

MAX

Statistical

Returns the largest value in a list of arguments.

MAXA

Statistical

Returns the largest value in a list of arguments. The value oftext is 0.

MAXIFS

Statistical

Returns the largest value in the cells of a range that meet
multiple criteria in multiple ranges.

MEDIAN

Statistical

Returns the median of a list of numbers.

MIN

Statistical

Returns the smallest value in a list of arguments.

MINA

Statistical

Returns the smallest value in a list of arguments. The valueof text is 0.

MINIFS

Statistical

Returns the smallest value in the cells of a range that meet
multiple criteria in multiple ranges.

MODE

Statistical

Returns the most common value in a list of values.

OFFSET

Spreadsheet

Return the value of a cell offset by a certain number of rows
and columns from a given reference point.

PRODUCT

Mathematical

Multiples all the numbers in a list of arguments and returns
the product.

STDEV
STDEV.S

Statistical

Calculates the standard deviation of a population sample.

STDEVA

Statistical

Calculates the standard deviation of a population sample.
The value of text is 0.

STDEVPSTDEV.P

Statistical

Calculates the standard deviation of an entire population.

STDEVPA

Statistical

Calculates the standard deviation of an entire population.
The value of text is 0.

SUBTOTAL

Mathematical

Calculates the total of a subset of an array.

28 | Chapter 13 Calc as a Simple DatabaseFunction

Category

Description

SUM

Mathematical

Returns the sum of a list of values.

SUMIF

Mathematical

Calculates the sum of values from cells that meet the
specified search criteria.

SUMIFS

Mathematical

Returns the sum of values from cells in a range that meet
multiple criteria in multiple ranges.

VAR
VAR.S

Statistical

Calculates the variance of a population sample.

VARA

Statistical

Calculates the variance of a population sample. The value oftext is 0.

VARP
VAR.P

Statistical

Calculates the variance of an entire population.

VARPA

Statistical

Calculates the variance of an entire population. The value oftext is 0.

VLOOKUP

Spreadsheet

Searches for a “look-up” value in the first column of an arrayand returns a value from a different column in the same row.

Database-specific functions
Some Calc functions are specifically designed for use with a database table. With one exception(DGET), these functions are specialized forms of commonly-used functions such as COUNT, andall are denoted with the D- prefix (such as DAVERAGE). A brief list of these functions is given in
Table 4, while more detailed descriptions of all Calc’s functions are presented in the Help system.

Note
Table 4 uses the following terms interchangeably: row and record; column and field.
Table 4: Database functions in a Calc document
Function

Description

DAVERAGE

Returns the average of all fields that match the search criteria.

DCOUNT

Counts the number of records containing numeric data that match the search
criteria.

DCOUNTACounts the number of records containing numeric or alphanumeric data thatmatch the search criteria.

DGET

Returns the contents of a field that matches the specified search criteria.

DMAX

Returns the maximum value in a field for every record that matches the specified
search criteria.

DMIN

Returns the minimum value in a field for every record that matches the specified
search criteria.

DPRODUCT

Returns the product of all values in a field that match the search criteria.

DSTDEV

Calculates the standard deviation of all values in a field that match the search
criteria. The values are treated as a sample.

DSTDEVP

Calculates the standard deviation of all values in a field that match the search
criteria. The values are treated as an entire population.

DSUM

Sums all values in a field that match the search criteria.

Useful database-like functions | 29

Function

Description

DVAR

Calculates the variance of all values in a field that match the search criteria. The
values are treated as a sample.

DVARP

Calculates the variance of all values in a field that match the search criteria. The
values are treated as an entire population.

30 | Chapter 13 Calc as a Simple Database