Chapter 19. Calc API Overview

This chapter gives an overview of the main services and interfaces used in the Calc parts of the Office API, illustrated with small code fragments. In OooDev version 0.15.0 the calc module was added. This module contains many classes and methods that simplify the use of the Calc API, such as Class CalcDoc, Class CalcSheet, Class CalcCell, and Class CalcCellRange. We’ll revisit these topics in greater details (and with larger examples) in subsequent chapters.

If you’re unfamiliar with Calc, then a good starting point is its user guide, available from https://libreoffice.org/get-help/documentation. Chapter 8 of the Developer’s Guide looks at spreadsheet programming, and is available from https://wiki.openoffice.org/w/images/d/d9/DevelopersGuide_OOo3.1.0.pdf. Alternatively, you can access the chapter online, starting at https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Spreadsheet_Documents (or use loguide calc). The guide’s examples can be found at https://api.libreoffice.org/examples/DevelopersGuide/examples.html#Spreadsheet. There’s also a few examples in the “Spreadsheet Document Examples” section of https://api.libreoffice.org/examples/examples.html#Java_examples.

19.1 The Spreadsheet Document 

Diagram of Some Spreadsheet Services and Interfaces — Fig. 163 :Some Spreadsheet Services and Interfaces.

Calc’s functionality is mostly divided between two packages (modules), sheet and table, which are documented at com.sun.star.sheet Module Reference and com.sun.star.table Module Reference. Alternatively, you can try lodoc star sheet module and lodoc star table module, but these only get you ‘close’ to the right pages.

The reason for this module division is Office’s support for three types of ‘table’: text tables, database tables, and spreadsheets. A spreadsheet is a table with formulae added into the mix.

19.2 Document Spreadsheets 

A spreadsheet document (i.e. a Calc file) can consist of multiple spreadsheets (or sheets). This is implemented using two services – called Spreadsheets (note the ‘s’) and Spreadsheet, as in Fig. 164.

Diagram of A Spreadsheet Document Hierarchy — Fig. 164 :A Spreadsheet Document Hierarchy.

The sheets stored in a Spreadsheets object can be accessed by index or by name. A newly created document always contains a blank spreadsheet in index position 0.

The following code fragment shows how the first sheet in the test.odt document is accessed:

loader = Lo.load_office(Lo.ConnectSocket())
compdoc = Lo.open_doc("test.odt", loader)
doc = Lo.qi(XSpreadsheetDocument, compdoc)
sheets = doc.getSheets()
sheets_idx = Lo.qi(XIndexAccess, sheets)
sheet = Lo.qi(XSpreadsheet, sheets_idx.getByIndex(0))

These steps are hidden by methods in the Calc utility class, so the programmer can write:

loader = Lo.load_office(Lo.ConnectSocket())
Calc.open_doc(doc_path, loader)
sheet = Calc.get_sheet(doc, 0)

Since 0.15.0 can also be written as:

# from ooodev.calc import CalcDoc, Calc
loader = Lo.load_office(Lo.ConnectSocket())
doc = CalcDoc(Calc.open_doc(doc_path, loader))
sheet = doc.get_sheet(0) # CalcSheet instance

Some Casting Required

Surprisingly, XSpreadsheetDocument doesn’t subclass XComponent. This means that it’s not possible to pass an XSpreadsheetDocument reference to a method expecting an XComponent argument:

Text documents can be passed to methods that expect XComponent because XTextDocument does subclass XComponent. The same is possible for Draw and Impress documents.

It’s possible to manipulate a spreadsheet document as an XComponent, but it must be cast first:

xc = Lo.qi(XComponent, doc)

This is why casting to XComponent is done automatically in GUI.set_visible(). For example, the odoc arg of GUI.set_visible() assumes that it is of type Object:

# in GUI class
@classmethod
def set_visible(cls, is_visible: bool, odoc: object = None) -> None:
    if odoc is None:
        xwindow = cls.get_window()
    else:
        doc = Lo.qi(XComponent, odoc)
        if doc is None:
            return
        xwindow = cls.get_frame(doc).getContainerWindow()

    if xwindow is not None:
        xwindow.setVisible(is_visible)
        xwindow.setFocus()

This GUI.set_visible() can be called with a XSpreadsheet reference: GUI.set_visible(True, doc). The document is cast to XComponent inside set_visible() and then processed.

19.3 Spreadsheet Data 

The data in a spreadsheet can be accessed in many ways: for example, as individual cells, cell ranges, collections of cell ranges, rows, and columns. These ways of viewing data are supported by different services which are used as labels in Fig. 165.

Diagram of Services used with Spreadsheet Data. — Fig. 165 :Services used with Spreadsheet Data.

The simplest spreadsheet unit is a cell, which can be located by its (column, row) coordinate/position or by its name, as in Fig. 166.

Diagram of Addressing Cells — Fig. 166 :Addressing Cells.

For instance, the cell named C5 in Fig. 166 is at coordinate (2,4). Note that row names start at 1 but row positions begin at 0. A cell range is defined by the position of the top-left and bottom-right cells in the range’s rectangle, and can use the same dual naming scheme. For example, the cell range B2:D3 is the rectangle between the cells (1,1) and (3,2).

A spreadsheet document may contain multiple sheets, so a cell address can include a sheet name. The first sheet is called Sheet1, the second Sheet2, and so on. For example, Sheet1.A3:Sheet3.D4 refers to a cube of 24 cells consisting of 3 sheets of 8 cells between A3 and D4. Sheets can be assigned more informative names, if you wish.

A collection of cell ranges is defined using ~ (the tilde) as the concatenation operator. For example, A1:C3~B2:D2 is a group of two ranges, A1:C3 and B2:D2. The comma, ,, can be used as an alternative concatenation symbol, at least in some Calc functions.

There’s also an intersection operator, !, for calculating the intersection of two ranges.

Cell references can be relative or absolute, which mainly affect how formulae are copied between cells. For example, a formula (=A1*3) in cell C3 becomes (=B1*3) when copied one cell to the right into D3. However, an absolute reference (which uses \ ( as a prefix) is unaffected when moved. For instance (=\)A$1*3) stops the A and 1 from being changed by a move.

The Calc support class includes methods for converting between simple cell names and positions; they don’t handle ~, !, or absolute references using $.

19.4 The Spreadsheet Service 

The Spreadsheet service is a subclass of SheetCellRange, as shown in Fig. 167, which means that a sheet can be treated as a very big cell range.

OooDev has Class CalcSheet which is usually accessed via Class CalcDoc, and provides a more convenient way of accessing a sheet’s cells and cell ranges and working with the Spreadsheet service.

Diagram of The Spreadsheet Service — Fig. 167 :The Spreadsheet Service.

A great deal of spreadsheet-related functionality is implemented as interfaces belonging to the Spreadsheet service. The most important is probably XSpreadsheet (see lodoc xspreadsheet reference ), which gives the programmer access to a sheet’s cells and cell ranges via getCellByPosition(), getCellRangeByPosition(), and getCellRangeByName(). For example:

sheet = Calc.get_sheet(doc, 0)
cell = sheet.getCellByPosition(2, 4) # (column,row)

# startColumn, startRow, endColumn, endRow
cell_range1 = sheet.getCellRangeByPosition(1, 1, 3, 2)

cell_range2 = sheet.getCellRangeByName("B2:D3")

Oddly enough there’s no getCellByName() method, but the Calc.get_cell() has an overload that takes a name.

19.5 Cell Range Services 

The main service for cell ranges is SheetCellRange, which inherits the CellRange service from the table module and several property-based classes, as indicated in Fig. 168.

OooDev has Class CalcCellRange for working with cell ranges.

Diagram of The Cell Range Services — Fig. 168 :The Cell Range Services.

SheetCellRange supports an XSheetCellRange interface, but that interface gets most of its functionality by inheriting XSheetCellRange from the table module. Most programs that manipulate cell ranges tend to use XCellRange rather than XSheetCellRange.

XCellRange is where the useful cell and cell range access methods are defined, as shown in the class diagram in Fig. 169.

Screen shot of The Cell Range Class Diagram — Fig. 169 :The CellRange Class Diagram.

You can access the documentation using lodoc XCellRange.

What’s missing from XCellRange is a way to set the values in a cell range. This is supported by the XCellRangeData interface (see Fig. 168) which offers a setDataArray() method (and a getDataArray()).

CellProperties in the table module is frequently accessed to adjust cell styling, such as color, borders, and the justification and orientation of data inside a cell. However, styling for a cell’s text is handled by properties in the CharacterProperties or ParagraphProperties classes (see Fig. 168).

Rows and columns of cells can be accessed using the TableRows and TableColumns services (and their corresponding XTableRows and XTableColumns interfaces). They’re accessed through the XColumnRowRange interface shown in Fig. 168. Code for obtaining the first row of a sheet is:

# get the XColumnRowRange interface for the sheet
# sheet is XSpreadsheet
cr_range = Lo.qi(XColumnRowRange, sheet)

# get all the rows
rows = cr_range.getRows()

# treat the rows as an indexed container
con = Lo.qi(XIndexAccess, rows)

# access the first row as a cell range
row_range = Lo.qi(XCellRange, con.getByIndex(0));

XTableRows is an indexed container containing a sequence of XCellRange objects. The TableRow services and interfaces are shown in Fig. 170:

Diagram of The TableRow Services and Interfaces — Fig. 170 :The TableRow Services and Interfaces.

Similar coding is used to retrieve a column: XColumnRowRange.getColumns() gets all the columns. Fig. 171 shows the TableColumn services and interfaces.

Diagram of The TableColumn Services and Interfaces. — Fig. 171 :The TableColumn Services and Interfaces.

Calc class includes methods that hide these details, so the accessing the first row of the sheet becomes:

row_range = Calc.get_row_range(sheet, 0);

19.6 Cell Services 

OooDev has Class CalcCell for working with cells.

XCellRange.getCellByPosition() returns a single cell from a given cell range. However, this method can also be applied to a sheet because the API considers a sheet to be a very big cell range. For example:

# sheet is XSpreadsheet
cell = sheet.getCellByPosition(2, 4)

Or using Class CalcCell:

doc = CalcDoc(Calc.create_doc())
sheet = doc.get_sheet(idx=0)
cell = sheet.get_cell(col=2, row=4) # Zero-based

The SheetCell service manages properties related to cell formulae and cell input validation. However, most cell functionality comes from inheriting the Cell service in the table module, and its XCell interface. This arrangement is shown in Fig. 172.

Diagram of The The SheetCell Services and Interfaces. — Fig. 172 :The SheetCell Services and Interfaces.

SheetCell doesn’t support an XSheetCell interface; instead most programming is done using XCell. XCell contains useful methods for getting and setting the values in a cell (which may be numbers, text, or formulae). For example, the following stores the number 9 in the cell at coordinate (2, 4) (the C5 cell):

sheet = Calc.get_sheet(doc, 0)
cell = sheet.getCellByPosition(2, 4) # (column,row)
cell.setValue(9)

Or using Class CalcCell:

doc = CalcDoc(Calc.create_doc())
sheet = doc.get_sheet(idx=0)
cell = sheet.get_cell(col=2, row=4) # Zero-based
cell.set_val(9)

SheetCell inherits the same properties as SheetCellRange. For example, CellProperties stores cell formatting properties, while text styling properties are supported by CharacterProperties and ParagraphProperties (see Fig. 172).

The Cell service supports both the XCell and XText interfaces. Via the XText interface, it’s possible to manipulate cell text in the same way that text is handled in a text document. However, for most purposes, it’s enough to use XCell's setFormula() which, despite its name, can be used to assign plain text to a cell. For instance:

cell.setFormula("hello") # put "hello" text in the cell

Calc differentiates between ordinary text and formulae by expecting a formula to begin with =.

The XCell class diagram is shown in Fig. 173.

Diagram of The XCell Class — Fig. 173 :The XCell Class Diagram.

The documentation for XCell can be found using lodoc xcell.

19.7 Sheet Cell Ranges 

A collection of cell ranges has its own service, SheetCellRanges, shown in Fig. 174.

OooDev has Class CalcCellRange for working with cell ranges.

Chapter 19. Calc API Overview

19.1 The Spreadsheet Document

19.2 Document Spreadsheets

Some Casting Required

19.3 Spreadsheet Data

19.4 The Spreadsheet Service

19.5 Cell Range Services

19.6 Cell Services

19.7 Sheet Cell Ranges