Calc Guide

Chapter 13
Calc as a Simple Database

A guide for users and macro programmers

Introduction

A Calc document is a very capable database, providing sufficient functionality to satisfy the needs of many users. This chapter presents the capabilities of a Calc document that make it suitable as a database tool. Where applicable, the functionality is explained using both the GUI (Graphical User Interface) and macros.

In a database, a record is a group of related data items treated as a single unit of information. Each item in the record is called a field. A table consists of records. Each record in a table has the same structure. A table can be visualized as a series of rows and columns. Each row in the table corresponds to a single record and each column corresponds to the fields. A spreadsheet in a Calc document is similar in structure to a database table. Each cell corresponds to a single field in a database record. For many people, Calc implements sufficient database functionality that no other database program or functionality is required.

While teaching, a spreadsheet might be used as a grading program. Each row represents a single student. The columns represent the grades received on homework, labs, and tests (see Table 1). The strong calculation capability provided in a spreadsheet makes this an excellent choice.

Table 1. Simple grading spreadsheet

Associating a range with a name

In a Calc document, a range refers to a contiguous group of cells containing at least one cell. You can associate a meaningful name to a range, which allows you to refer to the range using the meaningful name. You can create either a database range, which has some database-like functionality, or a named range, which does not. A name is usually associated with a range for one of three reasons:

1. Associating a range with a name enhances readability by using a meaningful name.

2. If a range is referenced by name in multiple locations, you can point the name to another location and all references point to the new location.

3. Ranges associated to a name are shown in the Navigator, which is available by pressing the F5 key or clicking on the icon. The Navigator allows for quick navigation to the associated ranges.

Named range

The most common usage of a named range is, as its name implies, to associate a range of cells to a meaningful name. For example, create a range named Scores, and then use the following equation: =SUM(Scores). To create a named range, select the range to define. Use Insert → Names → Define to open the Define Names dialog. Use the Define Names dialog to add and modify one named range at a time.

In a macro, a named range is accessed, created, and deleted using the NamedRanges property of a Calc document. Use the methods hasByName(name) and getByName(name) to verify and retrieve a named range. The method getElementNames() returns an array containing the names of all named ranges. The NamedRanges object supports the method addNewByname, which accepts four arguments; the name, content, position, and type. The macro in Listing 1 creates a named range, if it does not exist, that references a range of cells.

Listing 1. Create a 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 create.

Dim oCell ' Cell object.

Dim s$

sName$ = "MyNRange"

oRanges = ThisComponent.NamedRanges

If NOT oRanges.hasByName(sName$) Then

REM Obtain the cell address by obtaining the cell

REM and then extracting the address from the cell.

Dim oCellAddress As new com.sun.star.table.CellAddress

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 the formula or expression to use.

REM The second argument is usually a string that

REM defines a range.

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 (see Table 2).

s$ = "$Sheet1.$B$3:$D$6"

oRanges.addNewByName(sName$, s$, oCellAddress, 0)

End If

REM Get a range using 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

The method addNewByname() accepts four arguments; the name, content, position, and type. The fourth argument to the method addNewByName() is a combination of flags that specify how the named range will be used (see Table 2). The most common value is 0, which is not a defined constant value.

Table 2. com.sun.star.sheet.NamedRangeFlag constants.

The third argument, a cell address, acts as the base address for cells referenced in a relative way. If the cell range is not specified as an absolute address, the referenced range will be different based on where in the spreadsheet the range is used. The relative behavior is illustrated in Listing 2, which also illustrates another usage of a named range—defining an equation. The macro in Listing 2 creates the named range AddLeft, which refers to the equation A3+B3 with C3 as the reference cell. The cells A3 and B3 are the two cells directly to the left of C3, so, the equation =AddLeft() calculates the sum of the two cells directly to the left of the cell that contains the equation. Changing the reference cell to C4, which is below A3 and B3, causes the AddLeft equation to calculate the sum of the two cells that are to the left on the previous row.

Listing 2. Create the AddLeft named range.

Sub AddNamedFunction()

Dim oSheet 'Sheet that contains the named range.

Dim oCellAddress 'Address for relative references.

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) Then

oSheet = ThisComponent.getSheets().getByIndex(0)

oRange = oSheet.getCellRangeByName("C3")

oCellAddress = oRange.getCellAddress()

oRanges.addNewByName(sName, "A3+B3", oCellAddress, 0)

End If

End Sub

Select the range containing the headers and the data and then use Insert → Names → Create to open the Create Names dialog (see Figure 2), which allows you to simultaneously create multiple named ranges based on the top row, bottom row, right column or left column. If you choose to create ranges based on the top row, one named range is created for each column header—the header is not included in the named range. Although the header is not included in the range, the text in the header is used to name the range.

The macro in Listing 3 creates three named ranges based on the top row of a named range.

Listing 3. Create many named ranges.

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

The constants in Table 3 determine the location of the headers when multiple ranges are created using the method addNewFromTitles().

Table 3. com.sun.star.sheet.Border constants.

Database range

Although a database range can be used as a regular named range, a database range also defines a range of cells in a spreadsheet to be used as a database. Each row in a range corresponds to a record and each cell corresponds to a field. You can sort, group, search, and perform calculations on the range as if it were a database.

A database range provides behavior that is useful when performing database related activities. For example, you can mark the first row as headings. To create, modify, or delete a database range, use Data → Define Range to open the Define Data Range dialog (see Figure 3). When you first define a range, the Modify button shown in the example is labeled New.

In a macro, a database range is accessed, created, and deleted from the DatabaseRanges property. The macro in Listing 4 creates a database range named MyName and sets the range to be used as an auto filter.

Listing 4. Create a database range and 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") Then

oSheet = ThisComponent.getSheets().getByIndex(0)

oRange = oSheet.getCellRangeByName("A1:F10")

oAddr = oRange.getRangeAddress()

oDoc.DatabaseRanges.addNewByName("MyName", oAddr)

End If

oRange = oDoc.DatabaseRanges.getByName("MyName")

oRange.AutoFilter = True

End Sub

Sorting

The sorting mechanism in a Calc document rearranges the data in the sheet. The first step in sorting data is to select the data that you want to sort. To sort the data in Table 1, select the cells from A1 to G16—if you include the column headers, indicate this in the sort dialog (see Figure 5). Use Data → Sort to open the Sort dialog (see Figure 4). You can sort by up to three columns or rows at a time.

Click on the Options tab (see Figure 5) to set the sort options. Check the Range contains column labels checkbox to prevent column headers from being sorted with the rest of the data. The Sort by list box in Figure 4 displays the columns using the column headers if the Range contains column labels checkbox in Figure 5 is checked. If the Range contains column labels checkbox is not checked, however, then the columns are identified by their column name; Column A, for example.

Normally, sorting the data causes the existing data to be replaced by the newly sorted data. The Copy sort results to checkbox, however, causes the selected data to be left unchanged and a copy of the sorted data is copied to the specified location. You can either directly enter a target address (Sheet3.A1, for example) or select a predefined range.

Check the Custom sort order checkbox to sort based on a predefined list of values. To set your own predefined lists, use Tools → Options → LibreOffice Calc → Sort Lists and then enter your own sort lists. Predefined sort lists are useful for sorting lists of data that should not be sorted alphabetically or numerically. For example, sorting days based on their name.

Filters

Use filters to limit the visible rows in a spreadsheet. Generic filters, common to all sorts of data manipulations, are automatically provided by the auto filter capability. You can also define your own filters.

Auto filters

Use auto filters to quickly create easily accessible filters found to be commonly used in many different types of applications. After creating an auto filter for a specific column, a combo box is added to the column. The combo box provides quick access to each of the auto filter types.

• The All auto filter causes all rows to be visible.

• The Standard auto filter opens the Standard Filter dialog and is the same as the standard filter.

• The Top 10 auto filter displays the ten rows with the largest value. If the value 70 is in the top ten values, then all rows containing the value 70 in the filtered column are displayed. In other words, more than ten rows may be displayed.

• An auto filter entry is created for each unique entry in the column.

To create an auto filter, first select the columns to filter. For example, using the data in Table 1, select data in columns B and C. If you do not select the title rows, Calc asks if the title row or the current row should be used. Although you can place the auto filter in any row, only the rows below the auto filter are filtered. Use Data → Filter → AutoFilter to insert the auto filter combo box in the appropriate cell. Finally, use the drop-down arrow to choose an appropriate auto filter (see Figure 6).

Remove an auto filter by repeating the steps to create the auto filter—in other words, the menu option acts as a toggle to turn the auto filter on and off. When an auto filter is removed, the combo box is removed from the cell. The macro in Listing 4 demonstrates creating an auto filter for a range.

Standard filters

Use Data → Filter → Standard Filter to open Standard Filter dialog (see Figure 7) and limit the view based on 1 to 3 filter conditions. Use Data → Filter → Remove Filter to turn off the filter.

The macro in Listing 5 creates a simple filter for the first sheet.

Listing 5. Create a simple sheet 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

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

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,

REM CopyOutputData, OutputPosition, and MaxFieldCount.

oFilterDesc.setFilterFields(oFields())

oFilterDesc.ContainsHeader = True

oSheet.filter(oFilterDesc)

End Sub

When a filter is applied to a sheet, it replaces any existing filter for the sheet. Setting an empty filter in a sheet will therefore remove all filters for that sheet (see Listing 6).

Listing 6. Remove the current sheet 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

Listing 7 demonstrates a more advanced filter that filters two columns and uses regular expressions. Some unexpected behavior occurred while working with Listing 7. Although you can create a filter descriptor using any sheet cell range, the filter applies to the entire sheet.

Listing 7. A simple sheet filter using two columns.

Sub SimpleSheetFilter_2()

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

oSheet = ThisComponent.getSheets().getByIndex(0)

oRange = oSheet.getCellRangeByName("E12:G19")

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.

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

.StringValue = "b.*" ' Everything starting with b.

.Operator = com.sun.star.sheet.FilterOperator.EQUAL

End With

REM Setup a field that requires both conditions and

REM this new condition requires a value greater or

REM equal to 70.

With oFields(1)

.Connection = com.sun.star.sheet.FilterConnection.AND

.Field = 5 ' Filter column F.