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. 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
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.
Calc’s functionality is mostly divided between two Java packages (modules), sheet and table,
which are documented at https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sheet.html and https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1table.html.
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.
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)
Some Casting Required
It’s possible to manipulate a spreadsheet document as an XComponent, but it must be cast first:
xc = Lo.qi(XComponent, doc)
# 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()
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
Note that row names start at
1 but row positions begin at
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
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.
Sheet1.A3:Sheet3.D4 refers to a cube of 24 cells consisting of 3 sheets of 8 cells between
Sheets can be assigned more informative names, if you wish.
A collection of cell ranges is defined using
~ (the tilde) as the concatenation operator.
A1:C3~B2:D2 is a group of two ranges,
,, 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
(=B1*3) when copied one cell to the right into
However, an absolute reference (which uses
\ ( as a prefix) is unaffected when moved.
(=\)A$1*3) stops the
1 from being changed by a move.
Calc support class includes methods for converting between simple cell names and positions;
they don’t handle
!, or absolute references using
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
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.
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.
You can access the documentation using
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
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
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 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));
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);
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.
cell = sheet.getCellByPosition(2, 4)
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
sheet = Calc.get_sheet(doc, 0) cell = sheet.getCellByPosition(2, 4) # (column,row) cell.setValue(9)
SheetCell inherits the same properties as SheetCellRange.
CellProperties stores cell formatting properties, while text styling properties are supported by
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
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 documentation for XCell can be found using
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.