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
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.
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.
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.
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.
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.
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.
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:
Similar coding is used to retrieve a column: XColumnRowRange.getColumns()
gets all the columns.
Fig. 171 shows 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.
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.
The documentation for XCell can be found using lodoc xcell
.
See also
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.
See also
SheetCellRanges doesn’t turn up much when programming since it’s easy to access multiple cell ranges by accessing them one at a time inside a loop.
One major use for SheetCellRanges are in sheet searches which return the matching cell ranges in a XSheetCellRangeContainer object. There are examples in Chapter 26.