Documentation/DevGuide/Spreadsheet Documents

LibreOffice API knows three variants of tables: text tables (see Tables), database tables (see Table Service) and spreadsheets. Each table concept has its own purpose. Text tables handle text contents, database tables offer database functionality and spreadsheets operate on data cells that can be evaluated. Being specialized in such a way means that each concept has its strength. Text tables offer full functionality for text formatting, where spreadsheets support complex calculations. Alternately, spreadsheets support only basic formatting capabilities and text tables perform elementary calculations.

The implementations of the various tables differ due to each of their specializations. Basic table features are defined in the module com.sun.star.table. Regarding the compatibility of text and spreadsheet tables, the corresponding features are also located in the module com.sun.star.table. In addition, spreadsheet tables are fully based on the specifications given and are extended by additional specifications from the module com.sun.star.sheet. Several services of the spreadsheet application representing cells and cell ranges extend the common services from the module com::sun::star::table. The following table shows the services for cells and cell ranges.

The spreadsheet document model in the LibreOffice API has five major architectural areas (see Illustration 9.1) The five areas are:


 * Spreadsheets Container
 * Service Manager (document internal)
 * DrawPages
 * Content Properties
 * Objects for Styling



The core of the spreadsheet document model are the spreadsheets contained in the spreadsheets container. When working with document data, almost everything happens in the spreadsheet objects extracted from the spreadsheets container.

The service manager of the spreadsheet document model creates shape objects, text fields for page headers and form controls that can be added to spreadsheets. Note, that the document service manager is different from the main service manager used when connecting to the office. Each document model has its own service manager, so that the services can be adapted to the document they are required for. For instance, a text field is ordered and inserted into the page header text of a sheet using com.sun.star.text.XText:insertTextContent or the service manager is asked for a shape object and inserts it into a sheet using   at the drawpage.

Each sheet in a spreadsheet document can have a drawpage for drawing contents. A drawpage can be visualized as a transparent layer above a sheet. The spreadsheet model is able to provide all drawpages in a spreadsheet document at once.

Linked and named contents from all sheets are accessed through content properties at the document model. There are no content suppliers as in text documents, because the actual content of a spreadsheet document lies in its sheet objects. Rather, there are only certain properties for named and linked contents in all sheets.

Finally, there are services that allow for document wide styling and structuring of the spreadsheet document. Among them are style family suppliers for cells and pages, and a number formats supplier.

Besides these five architectural areas, there are document and calculation aspects shown at the bottom of the illustration. The document aspects of our model are: it is printable, storable, and modifiable, it can be protected and audited, and it supplies general information about itself. On the lower left of the illustration, the calculation aspects are listed. Although almost all spreadsheet functionality can be found at the spreadsheet objects, a few common functions are bound to the spreadsheet document model: goal seeking, consolidation and recalculation of all cells.

Finally, the document model has a controller that provides access to the graphical user interface of the model and has knowledge about the current view status in the user interface (see the upper left of the illustration).

The usage of the spreadsheet objects in the spreadsheets container is discussed in detail in the section Working With Spreadsheet Documents. Before discussing spreadsheet objects, consider two examples and how they handle a spreadsheet document, that is, how to create, open, save and print.

Example: Adding a New Spreadsheet
The following helper method opens a new spreadsheet document component. The method  retrieves a connection. Refer to chapter First Steps for additional information.

Our helper returns a com.sun.star.lang.XComponent interface for the recently loaded document. Now the XComponent is passed to the following method  to add a new spreadsheet to our document.

Example: Editing Spreadsheet Cells
The method  returns a com.sun.star.sheet.XSpreadsheet interface. This interface is passed to the method below, which shows how to access and modify the content and formatting of single cells. The interface com.sun.star.sheet.XSpreadsheet returned by  is derived from com.sun.star.table.XCellRange. By working with it, cells can be accessed immediately using :

See also Working with a Spreadsheet Document (Example).

Running an example showing how to find out which properties an object provides (with an object of type com.sun.star.table.XCell instead of ), one gets for example the following properties (OpenOffice.org 2.4):

Creating and Loading Spreadsheet Documents
If a document in LibreOffice API is required, begin by getting a com.sun.star.frame.Desktop service from the service manager. The desktop handles all document components in LibreOffice API. It is discussed thoroughly in the chapter Office Development. Office documents are often called components, because they support the com.sun.star.lang.XComponent interface. An  is a UNO object that can be disposed of directly and broadcast an event to other UNO objects when the object is disposed.

The Desktop can load new and existing components from a URL. For this purpose it has a com.sun.star.frame.XComponentLoader interface that has one single method to load and instantiate components from a URL into a frame:

The interesting parameters in our context is the URL that describes the resource that is loaded and the load arguments. For the target frame, pass a " " and set the search flags to 0. In most cases, existing frames are not reused.

The URL can be a  URL, an   URL, an   URL or a private: URL. Locate the correct URL format in the Load URL box in the function bar of LibreOffice API. For new spreadsheet documents, a special URL scheme is used. The scheme is "private:", followed by "factory". The resource is "scalc" for LibreOffice API spreadsheet documents. For a new spreadsheet document, use "private:factory/scalc".

The load arguments are described in com.sun.star.document.MediaDescriptor. The properties  and   are boolean values and used for programming. If  is true, the loader creates a new untitled document from the given URL. If it is false, template files are loaded for editing. If  is true, the document is loaded in the background. This is useful to generate a document in the background without letting the user observe what is happening. For instance, use it to generate a document and print it out without previewing. Refer to Office Development for other available options. This snippet loads a document in hidden mode:

Storing
Documents are storable through their interface com.sun.star.frame.XStorable. This interface is discussed in detail in Office Development. An  implements these operations:

boolean hasLocation string getLocation boolean isReadonly void store void storeAsURL([in] string aURL, [in] sequence< com::sun::star::beans::PropertyValue > aArgs) void storeToURL([in] string aURL, [in] sequence< com::sun::star::beans::PropertyValue > aArgs)

The method names are evident. The method  is the exact representation of File - Save As from the File menu, that is, it changes the current document location. In contrast,  stores a copy to a new location, but leaves the current document URL untouched.

Exporting
For exporting purposes, a filter name can be passed that triggers an export to other file formats. The property needed for this purpose is the string argument  that takes filter names defined in the configuration file:

\share\config\registry\instance\org\openoffice\Office\TypeDetection.xml

In TypeDetection.xml look for  elements, their   attribute contains the needed strings for. The proper filter name for StarWriter 5.x is "StarWriter 5.0", and the export format "MS Word 97" is also popular. This is the element in TypeDetection.xml that describes the MS Excel 97 filter:



The following method stores a document using this filter:

If an empty array of  structs is passed, the native .ods format of LibreOffice API is used.

Filter Options
Loading and saving LibreOffice API documents is described in Handling Documents. This section lists all the filter names for spreadsheet documents and describes the filter options for text file import.

The filter name and options are passed on loading or saving a document in a sequence of com.sun.star.beans.PropertyValue s. The property  contains the name and the property   contains the filter options.

The list of filter names (the last two columns show the possible directions of the filters):

Filter Options for Lotus, dBase and DIF Filters
These filters accept a string containing the numerical index of the used character set for single-byte characters, that is, 0 for the system character set.

The numerical indexes assigned to the character sets:

Filter Options for the CSV Filter
This filter accepts an option string containing five to nine tokens, separated by commas. Tokens 6, to 9 are optional.

Tokens 1 to 5
The following table shows an example string for a file with four columns of type date - number - number - number. In the table the tokens are numbered from (1) to (5). Each token is explained below.

For the filter options above, set the PropertyValue  in the load arguments to "44,34,0,1,1/5/2/1/3/1/4/1". There are a number of possible settings for the five tokens.


 * 1) Field separator(s) as ASCII values. Multiple values are separated by the slash sign ("/"), that is, if the values are separated by semicolons and horizontal tabulators, the token would be 59/9. To treat several consecutive separators as one, the four letters /MRG have to be appended to the token. If the file contains fixed width fields, the three letters FIX are used.
 * 2) The text delimiter as ASCII value, that is, 34 for double quotes and 39 for single quotes.
 * 3) The character set used in the file as described above.
 * 4) Number of the first line to convert. The first line in the file has the number 1.
 * 5) Cell format of the columns. The content of this token depends on the value of the first token.
 * If value separators are used, the form of this token is column/format[/column/format/…] where column is the number of the column, with 1 being the leftmost column. The format is explained below.
 * If the first token is FIX it has the form start/format[/start/format/…], where start is the number of the first character for this field, with 0 being the leftmost character in a line. The format is explained below.
 * Format specifies which cell format should be used for a field during import:


 * {|class="wikitable"

!Format Code !Meaning
 * 1
 * Standard
 * 2
 * Text
 * 3
 * MM/DD/YY
 * 4
 * DD/MM/YY
 * 5
 * YY/MM/DD
 * 6
 * 7
 * 8
 * 9
 * ignore field (do not import)
 * 10
 * US-English
 * }
 * 7
 * 8
 * 9
 * ignore field (do not import)
 * 10
 * US-English
 * }
 * 9
 * ignore field (do not import)
 * 10
 * US-English
 * }
 * }


 * The type code 10 indicates that the content of a field is US-English. This is useful if a field contains decimal numbers that are formatted according to the US system (using "." as decimal separator and "," as thousands separator). Using 10 as a format specifier for this field tells LibreOffice API to correctly interpret its numerical content, even if the decimal and thousands separator in the current language are different.

Token 6, Language identifier
This token is the equivalent of the "Language" listbox in the user interface for csv import. It is a String expressed in decimal notation. If the value is 0 or omitted, the language identifier of the user interface is used.

The language identifier is based on the Microsoft language identifiers, for further information please see:

Language Identifier Constants and Strings ==DEPRECATED== https://msdn.microsoft.com/en-us/library/windows/desktop/dd318693%28v=vs.85%29.aspx

Use the decimal notation, example for English US : 1033 whereas Microsoft documentation used hexadecimal notation 0x0409.

Token 7, csv import
This token is the equivalent of the check box "Quoted field as text".

String, either  or. Default value :.

Token 7, csv export
This token is the equivalent of the check box "Quote all text cells".

String, either  or. Default value :.

Token 8, csv import
This token is the equivalent of the check box "Detect special numbers".

String, either  or. Default value :.

Token 8, csv export
This token has no UI equivalent. If, the number cells are stored as numbers. If, the numbers are stored as text, with text delimiters.

String, either  or. Default value :.

Token 9, csv import
Not used : only 8 tokens are used.

Token 9, csv export
This token is the equivalent of the check box "Save cell contents as shown".

String, either  or. Default value :.

Examples
Import from UTF-8, Language German, Comma separated, Text delimiter, Quoted field as text:

Export to Windows-1252, Field delimiter : comma, Text delimiter : quote, Save cell contents as shown:

Printer and Print Job Settings
Printing is a common office functionality. The chapter Office Development provides in-depth information about it. The spreadsheet document implements the com.sun.star.view.XPrintable interface for printing. It consists of three methods:

sequence< com::sun::star::beans::PropertyValue > getPrinter void setPrinter([in] sequence< com::sun::star::beans::PropertyValue > aPrinter) void print([in] sequence< com::sun::star::beans::PropertyValue > xOptions)

The following code is used with a given document  to print to the standard printer without any settings:

There are two groups of properties involved in general printing. The first one is used with  and , and controls the printer, and the second is passed to   and controls the print job.

com.sun.star.view.PrinterDescriptor comprises the properties for the printer:

com.sun.star.view.PrintOptions contains the following possibilities for a print job:

The following method uses  and   to print to a special printer, and preselect the pages to print.

Page Breaks and Scaling for Printout
Manual page breaks can be inserted and removed using the property  of the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow. For details, refer to the section about page breaks in the chapter Spreadsheet Documents.

To reduce the page size of a sheet so that the sheet fits on a fixed number of printout pages, use the properties  and   of the current page style. Both of the properties are short numbers. The  property expects a percentage and   is the number of pages the printout is to fit. The page style is available through the interface com.sun.star.style.XStyleFamiliesSupplier of the document component, and is described in the chapter Overall Document Features.

Print Areas
The Interface com.sun.star.sheet.XPrintAreas is available at spreadsheets. It provides access to the addresses of all printable cell ranges, represented by a sequence of com.sun.star.table.CellRangeAddress structs.

Spreadsheet Document
The whole spreadsheet document is represented by the service com.sun.star.sheet.SpreadsheetDocument. It implements interfaces that provide access to the container of spreadsheets and methods to modify the document wide contents, for instance, data consolidation.



A spreadsheet document contains a collection of spreadsheets with at least one spreadsheet, represented by the service com.sun.star.sheet.Spreadsheets. The method  of the Interface com.sun.star.sheet.XSpreadsheetDocument returns the interface com.sun.star.sheet.XSpreadsheets for accessing the container of sheets.



When the spreadsheet container is retrieved from a document using its  method, it is possible to access the sheets in three different ways:

by index
 * Using the interface com.sun.star.container.XIndexAccess allows access to spreadsheets by their index.

with an enumeration
 * Using the service com.sun.star.sheet.SpreadsheetsEnumeration spreadsheets can be accessed as an enumeration.

by name
 * The interface com.sun.star.sheet.XSpreadsheets is derived from com.sun.star.container.XNameContainer and therefore contains all methods for accessing the sheets with a name. It is possible to get a spreadsheet using com.sun.star.container.XNameAccess ) to replace it with another sheet (interface com.sun.star.container.XNameReplace ), and to insert and remove a spreadsheet (interface com.sun.star.container.XNameContainer ).

The following two helper methods demonstrate how spreadsheets are accessed by their indexes and their names:

The interface com.sun.star.sheet.XSpreadsheets contains additional methods that use the name of spreadsheets to add new sheets, and to move and copy them:

The method below shows how a new spreadsheet is inserted into the spreadsheet collection of a document with the specified name.

Spreadsheet Services - Overview
The previous section introduced the organization of the spreadsheets in a document and how they can be handled. This section discusses the spreadsheets themselves. The following illustration provides an overview about the main API objects that can be used in a spreadsheet.



The main services in a spreadsheet are com.sun.star.sheet.Spreadsheet, com.sun.star.sheet.SheetCellRange , the cell service com.sun.star.sheet.SheetCell , the collection of cell ranges com.sun.star.sheet.SheetCellRanges and the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow. An overview of the capabilities of these services is provided below.

Capabilities of Spreadsheet
The spreadsheet is a com.sun.star.sheet.Spreadsheet service that includes the service com.sun.star.sheet.SheetCellRange, that is, a spreadsheet is a cell range with additional capabilities concerning the entire sheet:




 * It can be named using com.sun.star.container.XNamed.
 * It has interfaces for sheet analysis. Data pilot tables, sheet outlining, sheet auditing (detective) and scenarios all are controlled from the spreadsheet object. The corresponding interfaces are com.sun.star.sheet.XDataPilotTablesSupplier, com.sun.star.sheet.XScenariosSupplier , com.sun.star.sheet.XSheetOutline and com.sun.star.sheet.XSheetAuditing.
 * Cells can be inserted, and entire cell ranges can be removed, moved or copied on the spreadsheet level using com.sun.star.sheet.XCellRangeMovement.
 * Drawing elements in a spreadsheet are part of the draw page available through com.sun.star.drawing.XDrawPageSupplier.
 * Certain sheet printing features are accessed at the spreadsheet. The com.sun.star.sheet.XPrintAreas and com.sun.star.sheet.XSheetPageBreak are used to get page breaks and control print areas.
 * The spreadsheet maintains charts. The interface com.sun.star.table.XTableChartsSupplier controls charts in the spreadsheet.
 * All cell annotations can be retrieved on the spreadsheet level with com.sun.star.sheet.XSheetAnnotationsSupplier.
 * A spreadsheet can be permanently protected from changes through com.sun.star.util.XProtectable.

Capabilities of SheetCellRange
The spreadsheet, as well as the cell ranges in a spreadsheet are com.sun.star.sheet.SheetCellRange services. A  is a rectangular range of calculation cells that includes the following services:



The interfaces supported by a  are depicted in the following illustration:



A  has the following capabilities:


 * Supplies cells and sub-ranges of cells, as well as rows and columns. It has the interfaces com.sun.star.sheet.XSheetCellRange and com.sun.star.table.XColumnRowRange.
 * Performs calculations with a . The interface com.sun.star.sheet.XSheetOperation is for aggregate operations, com.sun.star.sheet.XMultipleOperation copies formulas adjusting their cell references, com.sun.star.sheet.XSubTotalCalculatable applies and removes sub totals, and com.sun.star.sheet.XArrayFormulaRange handles array formulas.
 * Formats cells in a range. The settings affect all cells in the range. There are cell properties, character properties and paragraph properties for formatting purposes. Additionally, a  supports auto formats with com.sun.star.table.XAutoFormattable and the content of the cells can be indented using com.sun.star.util.XIndent . The interfaces com.sun.star.sheet.XCellFormatRangesSupplier and com.sun.star.sheet.XUniqueCellFormatRangesSupplier obtain enumeration of cells that differ in formatting.
 * Works with the data in a cell range through a sequence of sequences of any that maps to the two-dimensional cell array of the range. This array is available through com.sun.star.sheet.XCellRangeData.
 * Fills a cell range with data series automatically through its interface com.sun.star.sheet.XCellSeries.
 * Imports data from a database using com.sun.star.util.XImportable.
 * Searches and replaces cell contents using com.sun.star.util.XSearchable.
 * Perform queries for cell contents, such as formula cells, formula result types, or empty cells. The interface com.sun.star.sheet.XCellRangesQuery of the included com.sun.star.sheet.SheetRangesQuery service is responsible for this task.
 * Merges cells into a single cell through com.sun.star.util.XMergeable.
 * Sorts and filters the content of a, using com.sun.star.util.XSortable , com.sun.star.sheet.XSheetFilterable and com.sun.star.sheet.XSheetFilterableEx.
 * Provides its unique range address in the spreadsheet document, that is, the start column and row, end column and row, and the sheet where it is located. The com.sun.star.sheet.XCellRangeAddressable:getRangeAddress returns the corresponding address description struct com.sun.star.table.CellRangeAddress.
 * Charts can be based on a, because it supports com.sun.star.chart.XChartDataArray.

Capabilities of SheetCell
A com.sun.star.sheet.SheetCell is the base unit of LibreOffice Calc tables. Values, formulas and text required for calculation jobs are all written into sheet cells. The  includes the following services:



The  exports the following interfaces:



The  service has the following capabilities:


 * It can access the cell content. It can contain numeric values that are used for calculations, formulas that operate on these values, and text supporting full-featured formatting and hyperlink text fields. The access to the cell values and formulas is provided through the  parent service com.sun.star.table.Cell . The interface com.sun.star.table.XCell is capable of manipulating the values and formulas in a cell. For text, the service com.sun.star.text.Text with the main interface com.sun.star.text.XText is available at a  . Its text fields are accessed through com.sun.star.text.XTextFieldsSupplier.
 * A  is a special case of a  . As such, it has all capabilities of the com.sun.star.sheet.SheetCellRange described above.
 * It can have an annotation: com.sun.star.sheet.XSheetAnnotationAnchor.
 * It can provide its unique cell address in the spreadsheet document, that is, its column, row and the sheet it is located in. The com.sun.star.sheet.XCellAddressable:getCellAddress returns the appropriate com.sun.star.table.CellAddress struct.
 * It can be locked temporarily against user interaction with com.sun.star.document.XActionLockable.

Capabilities of SheetCellRanges Container
The container of com.sun.star.sheet.SheetCellRanges is used where several cell ranges have to be handled at once for cell query results and other situations. The  service includes cell, paragraph and character property services, and it offers a query option:



The interfaces of com.sun.star.sheet.SheetCellRanges are element accesses for the ranges in the  container. These interfaces are discussed below.



The  container has the following capabilities:


 * It can be formatted using the character, paragraph and cell property services it includes.
 * It yields independent cell ranges through the element access interfaces com.sun.star.container.XIndexAccess, com.sun.star.container.XNameAccess and com.sun.star.container.XEnumerationAccess.
 * It can access, replace, append and remove ranges by name through com.sun.star.container.XNameContainer
 * It can add new ranges to  by their address descriptions, access the ranges by index, and obtain the cells in the ranges. This is possible through the interface com.sun.star.sheet.XSheetCellRangeContainer that was originally based on com.sun.star.container.XIndexAccess . The   maintain a sub-container of all cells in the ranges that are not empty, obtainable through the com.sun.star.sheet.XSheetCellRanges:getCells  method.
 * It can enumerate the ranges using com.sun.star.container.XEnumerationAccess.
 * It can query the ranges for certain cell contents, such as formula cells, formula result types or empty cells. The interface com.sun.star.sheet.XCellRangesQuery of the included com.sun.star.sheet.SheetRangesQuery service is responsible for this task.
 * The  supports selected   features, such as searching and replacing, indenting, sheet operations and charting.

Capabilities of Columns and Rows
All cell ranges are organized in columns and rows, therefore column and row containers are retrieved from a spreadsheet, as well as from sub-ranges of a spreadsheet through com.sun.star.table.XColumnRowRange. These containers are com.sun.star.table.TableColumns and com.sun.star.table.TableRows. Both containers support index and enumeration access. Only the  supports name access to the single columns and rows ( com.sun.star.table.TableColumn and com.sun.star.table.TableRow ) of a.

The following UML charts show table columns and rows. The first chart shows columns:



The collection of table rows differs from the collection of columns, that is, it does not support com.sun.star.container.XNameAccess :



The services for table rows and columns control the table structure and grid size of a cell range:


 * The containers for columns and rows have methods to insert and remove columns, and rows by index in their main interfaces com.sun.star.table.XTableRows and com.sun.star.table.XTableColumns.
 * The services  and   have properties to adjust their column width and row height, toggle their visibility, and set page breaks.

Spreadsheet
A spreadsheet is a cell range with additional interfaces and is represented by the service com.sun.star.sheet.Spreadsheet.

Properties of Spreadsheet
The properties of a spreadsheet deal with its visibility and its page style:

Naming
The spreadsheet interface com.sun.star.container.XNamed obtains and changes the name of the spreadsheet, and uses it to get a spreadsheet from the spreadsheet collection. Refer to Spreadsheet Documents.

Inserting Cells, Moving and Copying Cell Ranges
The interface com.sun.star.sheet.XCellRangeMovement of the  service supports inserting and removing cells from a spreadsheet, and copying and moving cell contents. When cells are copied or moved, the relative references of all formulas are updated automatically. The sheet index included in the source range addresses should be equal to the index of the sheet of this interface.

The following example copies a cell range to another location in the sheet.

Page Breaks
The methods  and   of the interface com.sun.star.sheet.XSheetPageBreak return the positions of column and row page breaks, represented by a sequence of com.sun.star.sheet.TablePageBreakData structs. Each struct contains the position of the page break and a boolean property that determines if the page break was inserted manually. Inserting and removing a manual page break uses the property  of the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow.

The following example prints the positions of all the automatic column page breaks:

Cell Ranges
A cell range is a rectangular range of cells. It is represented by the service com.sun.star.sheet.SheetCellRange.

Properties of Cell Ranges
The cell range properties deal with the position and size of a range, conditional formats, and cell validation during user input.

This service extends the service com.sun.star.table.CellRange to provide common table cell range functionality.

Cell and Cell Range Access
The interface com.sun.star.sheet.XSheetCellRange is derived from com.sun.star.table.XCellRange. It provides access to cells of the range and sub ranges, and is supported by the spreadsheet and sub-ranges of a spreadsheet. The methods in com.sun.star.sheet.XSheetCellRange are:

com::sun::star::table::XCell getCellByPosition( [in] long nColumn, [in] long nRow) com::sun::star::table::XCellRange getCellRangeByPosition( [in] long nLeft, [in] long nTop,                                                           [in] long nRight, [in] long nBottom) com::sun::star::table::XCellRange getCellRangeByName ( [in] string aRange) com::sun::star::sheet::XSpreadsheet getSpreadsheet

The interface com.sun.star.table.XCellRange provides methods to access cell ranges and single cells from a cell range.

Cells are retrieved by their position. Cell addresses consist of a row index and a column index. The index is zero-based, that is, the index 0 means the first row or column of the table.

Cell ranges are retrieved:

by position
 * Addresses of cell ranges consist of indexes to the first and last row, and the first and last column. Range indexes are always zero-based, that is, the index 0 points to the first row or column of the table.

by name
 * It is possible to address a cell range over its name in A1:B2 notation as it would appear in the application.

Additionally,  contains the method   that returns the com.sun.star.sheet.XSpreadsheet interface of the spreadsheet which contains the cell range.

// --- First cell in a cell range. --- com.sun.star.table.XCell xCell = xCellRange.getCellByPosition(0, 0);

// --- Spreadsheet that contains the cell range. --- com.sun.star.sheet.XSpreadsheet xSheet = xCellRange.getSpreadsheet;

There are no methods to modify the contents of all cells of a cell range. Access to cell range formatting is supported. Refer to the chapter Formatting for additional details.

In the following example,  is an existing cell range (a com.sun.star.table.XCellRange interface):

Merging Cell Ranges into a Single Cell
The cell range interface com.sun.star.util.XMergeable merges and undoes merged cell ranges.


 * The method  merges or undoes merged the whole cell range.
 * The method  determines if the cell range is completely merged.

Column and Row Access
The cell range interface com.sun.star.table.XColumnRowRange accesses the column and row ranges in the current cell range. A column or row range contains all the cells in the selected column or row. This type of range has additional properties, such as, visibility, and width or height. For more information, see Columns and Rows.


 * The method  returns the interface com.sun.star.table.XTableColumns of the collection of columns.
 * The method  returns the interface com.sun.star.table.XTableRows of the collection of rows.

Data Array
The contents of a cell range that are stored in a 2-dimensional array of objects are set and obtained by the interface com.sun.star.sheet.XCellRangeData.

The following example uses the cell range  that has the size of 2 columns and 3 rows.
 * The method  returns a 2-dimensional array with the contents of all cells of the range.
 * The method  fills the data of the passed array into the cells. An empty cell is created by an empty string. The size of the array has to fit in the size of the cell range.

Absolute Address
The method  of the interface com.sun.star.sheet.XCellRangeAddressable returns a com.sun.star.table.CellRangeAddress struct that contains the absolute address of the cell in the spreadsheet document, including the sheet index. This is useful to get the address of cell ranges returned by other methods.

Fill Series
The interface com.sun.star.sheet.XCellSeries fills out each cell of a cell range with values based on a start value, step count and fill mode. It is possible to fill a series in each direction, specified by a com.sun.star.sheet.FillDirection constant. If the fill direction is horizontal, each row of the cell range forms a separate series. Similarly each column forms a series on a vertical fill.


 * The method  uses the first cell of each series as start value. For example, if the fill direction is "To top", the bottom-most cell of each column is used as the start value. It expects a fill mode to be used to continue the start value, a com.sun.star.sheet.FillMode constant. If the values are dates, com.sun.star.sheet.FillDateMode constants describes the mode how the dates are calculated. If the series reaches the specified end value, the calculation is stopped.
 * The method  determines the fill mode and step count automatically. It takes a parameter containing the number of cells to be examined. For example, if the fill direction is "To top" and the specified number of cells is three, the three bottom-most cells of each column are used to continue the series.

The following example may operate on the following spreadsheet:

Inserting filled series in Java:

This example produces the following result:

Operations
The cell range interface com.sun.star.sheet.XSheetOperation computes a value based on the contents of all cells of a cell range or clears specific contents of the cells.

The following code shows how to compute the average of a cell range and clear the cell contents:
 * The method  returns the result of the calculation. The constants com.sun.star.sheet.GeneralFunction specify the calculation method.
 * The method  clears contents of the cells used. The parameter describes the contents to clear, using the constants of com.sun.star.sheet.CellFlags.

Multiple Operations
A multiple operation combines a series of formulas with a variable and a series of values. The results of each formula with each value is shown in the table. Additionally, it is possible to calculate a single formula with two variables using a 2-value series. The method  of the interface com.sun.star.sheet.XMultipleOperation inserts a multiple operation range.

The following example shows how to calculate the values 1 to 5 raised to the powers of 1 to 5 (each value to each power). The first column contains the base values, and the first row the exponents, for example, cell E3 contains the result of 24. Below there are three trigonometrical functions calculated based on a series of values, for example, cell C11 contains the result of cos(0.2).

Note that the value series have to be included in the multiple operations cell range, but not the formula cell range (in the second example). The references in the formulas address any cell outside of the area to be filled. The column cell and row cell parameter have to reference these cells exactly. In the second example, a row cell address does not have to be used, because the row contains the formulas.

Handling Array Formulas
The interface com.sun.star.sheet.XArrayFormulaRange handles array formulas.


 * If the whole cell range contains an array formula, the method  returns the formula string, otherwise an empty string is returned.
 * The method  sets an array formula to the complete cell range.

Cells
A single cell of a spreadsheet is represented by the service com.sun.star.sheet.SheetCell. This service extends the service com.sun.star.table.Cell, that provides fundamental table cell functionality, such as setting formulas, values and text of a cell.

Properties of SheetCell
The service com.sun.star.sheet.SheetCell introduces new properties and interfaces, extending the formatting-related cell properties of com.sun.star.table.Cell.

Access to Formulas, Values and Errors
The cell interface com.sun.star.table.XCell provides methods to access the value, formula, content type, and error code of a single cell:

The value of a cell is a floating-point number. To set a formula to a cell, the whole formula string has to be passed including the leading equality sign. The function names must be in English.

The method  returns a value of the enumeration com.sun.star.table.CellContentType indicating the type of the cell content.

The following code fragment shows how to access and modify the content, and formatting of single cells. The  is an existing cell range (a com.sun.star.table.XCellRange interface, described in Cell Ranges). The method  is provided by this interface.

Note that since the only data attributes a cell has for representing numbers is value and formula, other types available in LibreOffice Calc have to be translated before you can use them. See com.sun.star.util.NumberFormat for the standard formats.

How this value will be shown to the user depends on the cell's Number Format.

Access to Text Content
The service com.sun.star.text.Text supports the modification of simple or formatted text contents. Changing text contents and text formatting is provided by the interface com.sun.star.text.XText as discussed in First Steps. Refer to chapter Editing Text for further information. It implements the interfaces com.sun.star.container.XEnumerationAccess that provides access to the paragraphs of the text and the interface com.sun.star.text.XText to insert and modify text contents. For detailed information about text handling, see Editing Text.

The  interface com.sun.star.text.XTextFieldsSupplier contains methods that provide access to the collection of text fields in the cell. For details on inserting text fields, refer to Text Fields.

Absolute Address
The method  of the interface com.sun.star.sheet.XCellAddressable returns a com.sun.star.table.CellAddress struct that contains the absolute address of the cell in the spreadsheet document, including the sheet index. This is useful to get the address of cells returned by other methods.

Cell Ranges and Cells Container
Cell range collections are represented by the service com.sun.star.sheet.SheetCellRanges. They are returned by several methods, for instance the cell query methods of com.sun.star.sheet.SheetRangesQuery. Besides standard container operations, it performs a few spreadsheet functions also usable with a single cell range.

Access to Single Cell Ranges in SheetCellRanges Container
The interfaces com.sun.star.container.XEnumerationAccess and com.sun.star.container.XIndexAccess iterates over all contained cell ranges by index or enumeration. With the com.sun.star.container.XNameContainer, it is possible to insert ranges with a user-defined name. Later the range can be found, replaced or removed using the name.

The following interfaces and service perform cell range actions on all ranges contained in the collection:


 * Interface com.sun.star.util.XReplaceable (see Navigating)
 * Service com.sun.star.sheet.SheetRangesQuery (see Navigating)
 * Interface com.sun.star.util.XIndent (see Formatting)
 * Interface com.sun.star.sheet.XSheetOperation (see Cell Ranges)
 * Interface com.sun.star.chart.XChartDataArray (see Charts)

The interfaces com.sun.star.sheet.XSheetCellRangeContainer and com.sun.star.sheet.XSheetCellRanges support basic handling of cell range collections.


 * The method  returns the string representation of all cell ranges.
 * The method  returns a sequence with all cell range addresses.

The interface com.sun.star.sheet.XSheetCellRangeContainer is derived from the interface com.sun.star.sheet.XSheetCellRanges to insert and remove cell ranges.


 * The methods  and   insert one or more ranges into the collection. If the boolean parameter   is set to , the methods try to merge the new range(s) with the ranges of the collection.
 * The methods  and   remove existing ranges from the collection. Only ranges that are contained in the collection are removed. The methods do not try to shorten a range.

The interface com.sun.star.sheet.XSheetCellRanges implements methods for access to cells and cell ranges:


 * The method  returns the interface com.sun.star.container.XEnumerationAccess of a cell collection. The service com.sun.star.sheet.Cells is discussed below. This collection contains the cell addresses of non-empty cells in all cell ranges.

The service com.sun.star.sheet.Cells represents a collection of cells.



The following example demonstrates the usage of cell range collections and cell collections.

Columns and Rows
Collection of table columns:



Collection of table rows:



The services com.sun.star.table.TableColumns and com.sun.star.table.TableRows represent collections of all columns and rows of a table. It is possible to access cells of columns and rows, and insert and remove columns and rows using the interfaces com.sun.star.table.XTableColumns and com.sun.star.table.XTableRows that are derived from com.sun.star.container.XIndexAccess. The method  of the interface com.sun.star.container.XEnumerationAccess creates an enumeration of all columns or rows. The interface com.sun.star.container.XNameAccess accesses columns through their names. The implementation of this interface is optional.

A single column or row is represented by the services com.sun.star.table.TableColumn and com.sun.star.table.TableRow. They implement the interfaces com.sun.star.table.XCellRange that provide access to the cells and com.sun.star.beans.XPropertySet for modifying settings. Additionally, the service  implements the interface com.sun.star.container.XNamed. It provides the method  that returns the name of a column. Changing the name of a column is not supported.

In the following example,  is an interface of a collection of columns,   is an interface of a collection of rows, and   is the range formed by the columns and rows.

Cell Formatting
In cells, cell ranges, table rows, table columns and cell ranges collections, the cells are formatted through the service com.sun.star.table.CellProperties. These properties are accessible through the interface com.sun.star.beans.XPropertySet that is supported by all the objects mentioned above. The service contains all properties that describe the cell formatting of the cell range, such as the cell background color, borders, the number format and the cell alignment. Changing the property values affects all cells of the object being formatted.

The cell border style is stored in the struct com.sun.star.table.TableBorder. A cell range contains six different kinds of border lines: upper, lower, left, right, horizontal inner, and vertical inner line. Each line is represented by a struct com.sun.star.table.BorderLine that contains the line style and color. The boolean members  specifies the validity of the   members containing the line style. If the property contains the value true, the line style is equal in all cells that include the line. The style is contained in the  struct. The value false means the cells are formatted differently and the content of the  struct is undefined. When changing the border property, these boolean values determine if the lines are changed to the style contained in the respective  struct.

Character and Paragraph Format
The following services of a cell range contain properties for the character style and paragraph format:

The chapter Formatting contains a description of these properties.
 * Service com.sun.star.style.ParagraphProperties
 * Service com.sun.star.style.CharacterProperties
 * Service com.sun.star.style.CharacterPropertiesAsian
 * Service com.sun.star.style.CharacterPropertiesComplex

This example formats a given cell range :

The code below changes the character and paragraph formatting of a cell. Assume that  is a com.sun.star.table.XCell interface of a spreadsheet cell.

Indentation
The methods of the interface com.sun.star.util.XIndent change the left indentation of the cell contents. This interface is supported by cells, cell ranges and collections of cell ranges. The indentation is incremental and decremental, independent for each cell.

The following sample shows how to increase the cell indentation by 1.
 * The method  reduces the indentation of each cell by 1.
 * The method  enlarges the indentation of each cell by 1.

Equally Formatted Cell Ranges
It is possible to get collections of all equally formatted cell ranges contained in a source cell range.

Cell Format Ranges
The service com.sun.star.sheet.CellFormatRanges represents a collection of equally formatted cell ranges. The cells inside of a cell range of the collection have the same formatting attributes. All cells of the source range are contained in one of the ranges. If there is a non-rectangular, equal-formatted range, it is split into several rectangular ranges.



Unique Cell Format Ranges
The service com.sun.star.sheet.UniqueCellFormatRanges represents, similar to Cell Format Ranges above, a collection of equally formatted cell ranges, but this collection contains cell range container objects (service com.sun.star.sheet.SheetCellRanges ) that contain the cell ranges. The cells of all ranges inside of a cell range container are equally formatted. The formatting attributes of a range container differ from each other range container. All equally formatted ranges are consolidated into one container.



In the following example, the cells have two different background colors. The formatted ranges of the range A1:G3 are queried in both described ways.

A com.sun.star.sheet.CellFormatRanges object contains the following ranges: A1:C2, D1:G1, D2:F2, G2:G2, and A3:G3.

A com.sun.star.sheet.UniqueCellFormatRanges object contains two com.sun.star.sheet.SheetCellRanges range collections. One collection contains the white ranges, that is, A1:C2, D1:G1, G2:G2, and the other collection, the gray ranges, that is, D2:F2, A3:G3.

The following code is an example of accessing the formatted ranges in Java. The  is a helper method that returns the range address as a string.

Table Auto Formats
Table auto formats are used to apply different formats to a cell range. A table auto format is a collection of cell styles used to format all cells of a range. The style applied is dependent on the position of the cell.

The table auto format contains separate information about four different row types and four different column types:


 * First row (header), first data area row, second data area row, last row (footer)
 * First column, first data area column, second data area column, last column

The row or column types for the data area (between first and last row/column) are repeated in sequence. Each cell of the formatted range belongs to one of the row types and column types, resulting in 16 different auto-format fields. In the example below, the highlighted cell has the formatting of the first data area row and last column field. Additionally, this example shows the indexes of all the auto format fields. These indexes are used to access the field with the interface com.sun.star.container.XIndexAccess.



A table auto format is represented by the service com.sun.star.sheet.TableAutoFormat. It contains exactly 16 auto format fields (service com.sun.star.sheet.TableAutoFormatField ). Each auto format field contains all properties of a single cell.

The cell range interface com.sun.star.table.XAutoFormattable contains the method  that applies a table auto format to a cell range. The cell range must have a size of at least 3x3 cells. The  properties of the table auto format determine the formatting properties are copied to the cells. The default setting of all the properties is true.

The collection of all table auto formats is represented by the service com.sun.star.sheet.TableAutoFormats. There is only one instance of this collection in the whole application. It contains all default and user-defined auto formats that are used in spreadsheets and tables of the word-processing application. It is possible to iterate through all table auto formats with an enumeration, or to access them directly using their index or their name.



The following example shows how to insert a new table auto format, fill it with properties, apply it to a cell range and remove it from the format collection.

Conditional Formats
A cell can be formatted automatically with a conditional format, depending on its contents or the result of a formula. A conditional format consists of several condition entries that contain the condition and name of a cell style. The style of the first met condition, true or "not zero", is applied to the cell.



A cell or cell range object contains the properties  and. These properties return the interface com.sun.star.sheet.XSheetConditionalEntries of the conditional format container com.sun.star.sheet.TableConditionalFormat. The objects of both properties are equal, except for the representation of formulas. The  property uses function names in the current language.

A condition entry of a conditional format is represented by the service com.sun.star.sheet.TableConditionalEntry. It implements two interfaces:


 * The interface com.sun.star.sheet.XSheetCondition gets and sets the operator, the first and second formula and the base address for relative references.
 * The interface com.sun.star.sheet.XSheetConditionalEntry gets and sets the cell style name.

The service com.sun.star.sheet.TableConditionalFormat contains all format conditions and returns com.sun.star.sheet.TableConditionalEntry objects. The interface com.sun.star.sheet.XSheetConditionalEntries inserts new conditions and removes them.


 * The method  inserts a new condition. It expects a sequence of com.sun.star.beans.PropertyValue objects. The following properties are supported:
 * : A com.sun.star.sheet.ConditionOperator constant describing the operation to perform.
 * and : Strings that contain the values or formulas to evaluate.   is used only if the property Operator contains   or.
 * : A com.sun.star.table.CellAddress struct that contains the base address for relative cell references in formulas.
 * : The name of the cell style to apply.
 * The methods  removes the condition entry at the specified position.
 * The method  removes all condition entries.

The following example applies a conditional format to a cell range. It uses the cell style "MyNewCellStyle" that is applied to each cell containing a value greater than 1. The xSheet is the com.sun.star.sheet.XSpreadsheet interface of a spreadsheet.

Navigating
Unlike other document models that provide access to their content by content suppliers, the spreadsheet document contains properties that allow direct access to various containers.

The properties allow access to various containers:


 * : The container with all the named ranges. See Named Ranges.
 * and : Containers with row labels and column labels. See Label Ranges.
 * : The container with all database ranges. See Database Ranges.
 * ,  and  : Containers with external links. See Sheet Links.

Cell Cursor
A cell cursor is a cell range with extended functionality and is represented by the service com.sun.star.sheet.SheetCellCursor. With a cell cursor it is possible to move through a cell range. Each table can contain only one cell cursor.



It implements all interfaces described in Cell Ranges and the basic cursor interfaces of the service com.sun.star.table.CellCursor that represents the cell or cell range cursor of a table.

The interface com.sun.star.sheet.XSpreadsheet of a spreadsheet creates the cell cursors. The methods return the interface com.sun.star.sheet.XSheetCellCursor of the cursor. It is derived from the interface com.sun.star.sheet.XSheetCellRange that provides access to cells and cell ranges. Refer to Cell Ranges for additional information.


 * The method  creates a cursor that spans over the whole spreadsheet.
 * The method  creates a cursor that spans over the given cell range.

The  includes the   service from the table module:



Cursor Movement
The service com.sun.star.table.CellCursor implements the interface com.sun.star.table.XCellCursor that provides methods to move to specific cells of a cell range. This interface is derived from com.sun.star.table.XCellRange so all methods that access single cells can be used.

The following example shows how to modify a cell beyond a filled area.The xCursor may be an initialized cell cursor.

The interface com.sun.star.sheet.XSheetCellCursor sets the cursor to specific ranges in the sheet.


 * The method  expands the cursor to the shortest cell range filled with any data. A few examples from the spreadsheet below are: the cursor C2:C2 expands to B2:D3, cursor C1:C2 expands to B1:D3 and cursor A1:D4 is unchanged.


 * {|class="wikitable"

!width="40"| !width="60"|A !width="60"|B !width="60"|C !width="60"|D !width="60"|E !width="60"|F !width="60"|G !bgcolor=#EDEDED|1 !bgcolor=#EDEDED|2 !bgcolor=#EDEDED|3 !bgcolor=#EDEDED|4
 * 1
 * 3
 * {=C2:D3}
 * {=C2:D3}
 * {=C2:D3}
 * {=C2:D3}
 * {=C2:D3}
 * Text
 * 2
 * 4
 * {=C2:D3}
 * {=C2:D3}
 * {=C2:D3}
 * {=C2:D3}
 * }
 * }
 * }
 * }
 * }
 * }
 * }
 * }


 * The method  expands or shortens the cursor range to an array formula range. This works only if the top-left cell of the current cursor contains an array formula. An example using the spreadsheet above: All the cursors with a top-left cell located in the range F2:G3 are modified to this array formula range, F2:F2 or G2:G4.
 * The method  expands the current cursor range so that all merged cell ranges intersecting the current range fit completely.
 * The methods  and   expand the cursor range so that it contains all cells of the columns or rows of the current range.
 * The method  resizes the cursor range to the given dimensions. The start address of the range is left unmodified. To move the cursor range without changing the current size, use the method   from the interface com.sun.star.table.XCellCursor.

The following example tries to find the range of the array formula in cell F22. The  is a com.sun.star.sheet.XSpreadsheet interface of a spreadsheet and   is a helper method that returns the range address as a string.

Used Area
The cursor interface com.sun.star.sheet.XUsedAreaCursor contains methods to locate the used area of the entire sheet. The used area is the smallest cell range that contains all cells of the spreadsheet with any contents, such as values, text, and formulas, or visible formatting, such as borders and background color. In the following example,  is a com.sun.star.sheet.XSpreadsheet interface of a spreadsheet.

Referencing Ranges by Name
Cell ranges can be assigned a name that they may be addressed by in formulas. This is done with named ranges. Another way to use names for cell references in formulas is the automatic label lookup which is controlled using label ranges.

Named Ranges
A named range is a named formula expression, where a cell range is just one possible content. Thus, the content of a named range is always set as a string.



The collection of named ranges is accessed using the document's  property. A new named range is added by calling the com.sun.star.sheet.XNamedRanges interface's  method. The method's parameters are:


 * The name for the new named range.
 * The content. This must be a string containing a valid formula expression. A commonly used type of expression is an absolute cell range reference like "$Sheet1.$A1:$C3".
 * A reference position for relative references. If the content contains relative cell references, and the named range is used in a formula, the references are adjusted for the formula's position. The reference position states which cell the references are relative to.
 * The type of the named range that controls if the named range is included in some dialogs. The type must be a combination of the com.sun.star.sheet.NamedRangeFlag constants:
 * If the  bit is set, the named range is offered as a criteria range in the "Advanced Filter" dialog.
 * If the,   or   bit is set, the named range is selected as "Print range", "Columns to repeat" or "Rows to repeat" in the Edit Print Ranges dialog.

The  method creates named ranges from header columns or rows in a cell range. The com.sun.star.sheet.Border enum parameter selects which named ranges are created:


 * If the value is, a named range is created for each column of the cell range with the name taken from the range's first row, and the other cells of that column within the cell range as content.
 * For, the names are taken from the range's last row.
 * If the value is, a named range is created for each row of the cell range with the name taken from the range's first column, and the other cells of that row within the cell range as content.
 * For, the names are taken from the range's last column.

The  method is used to remove a named range. The  method writes a list of all the named ranges into the document, starting at the specified cell position.

The com.sun.star.sheet.NamedRange service accesses an existing named range. The com.sun.star.container.XNamed interface changes the name, and the com.sun.star.sheet.XNamedRange interface changes the other settings. See the  description above for the meaning of the individual values.

If the content of the name is a single cell range reference, the com.sun.star.sheet.XCellRangeReferrer interface is used to access that cell range.

The following example creates a named range that calculates the sum of the two cells above the position where it is used. This is done by using the relative reference "G43:G44" with the reference position G45. Then, the example uses the named range in two formulas.

Label Ranges
A label range consists of a label area containing the labels, and a data area containing the data that the labels address. There are label ranges for columns and rows of data, which are kept in two separate collections in the document.



The com.sun.star.sheet.LabelRanges service contains the document's column label ranges or row label ranges, depending if the  or   property was used to get it. The com.sun.star.sheet.XLabelRanges interface's  method is used to add a new label range, specifying the label area and data area. The  method removes a label range.

The com.sun.star.sheet.LabelRange service represents a single label range and contains the com.sun.star.sheet.XLabelRange interface to modify the label area and data area.

The following example inserts a column label range with the label area G48:H48 and the data area G49:H50, that is, the content of G48 is used as a label for G49:G50 and the content of H48 is used as a label for H49:H50, as shown in the two formulas the example inserts.

Querying for Cells with Specific Properties
Cells, cell ranges and collections of cell ranges are queried for certain cell contents through the service com.sun.star.sheet.SheetRangesQuery. It implements interfaces to query cells and cell ranges with specific properties.

The methods of the interface com.sun.star.sheet.XCellRangesQuery search for cells with specific contents or properties inside of the given cell range. The methods of the interface com.sun.star.sheet.XFormulaQuery search for cells in the entire spreadsheet that are reference to or are referenced from formula cells in the given range.



All methods return the interface com.sun.star.sheet.XSheetCellRanges of a cell range collection. Cell range collections are described in the chapter Cell Ranges and Cells Container.

Example:

The queried range is A1:C4 and the passed cell address is B2.


 * : (the row number is of interest) The cells of column A are compared with cell A2, the cells of column B with B2 and so on. The function returns the cell range list B1:B1, B4:B4, C3:C4.
 * : (the column index is of interest) The function compares row 1 with cell B1, row 2 with cell B2 and so on. It returns the cell range list C1:C1, A2:A2, A3:A3, C3:C3.

The following code queries all cells with text content:

Search and Replace
The cell range interface com.sun.star.util.XReplaceable is derived from com.sun.star.util.XSearchable providing search and replacement of text.


 * The method  creates a new descriptor that contains all data for the replace action. It returns the interface com.sun.star.util.XReplaceDescriptor of this descriptor.
 * The method  performs a replacement in all cells according to the passed replacedescriptor.

The following example replaces all occurrences of "cell" with "text":

Table Sort Descriptor
A sort descriptor describes all properties of a sort operation. The service com.sun.star.table.TableSortDescriptor2 extends the service com.sun.star.util.SortDescriptor2 with table specific sorting properties, such as:

The sorting orientation using the boolean.

A sequence of sorting fields using the  property that contains a sequence of com.sun.star.table.TableSortField structs.

The size of the sequence using the  property.

The service com.sun.star.sheet.SheetSortDescriptor2 extends the service com.sun.star.table.TableSortDescriptor2 with spreadsheet specific sorting properties, such as:

Moving cell formats with the cells they belong to using the boolean property .The existence of column or row headers using the boolean property.

Copying the sorted data to another position in the document using the boolean property.

Position where sorted data are to be copied using the property.

If the  property is true, a user-defined sort list is used that specifies an order for the strings it contains. The  property selects an entry from the   property of the com.sun.star.sheet.GlobalSheetSettings service to find the sort list that is used.



To sort the contents of a cell range, the  method from the com.sun.star.util.XSortable interface is called, passing a sequence of property values with properties from the com.sun.star.sheet.SheetSortDescriptor2 service. The sequence can be constructed from scratch containing the properties that should be set, or the return value of the  method can be used and modified. If the cell range is a database range that has a stored sort operation,  returns a sequence with the options of this sort operation.

The fields that the cell range is sorted by are specified in the  property as a sequence of com.sun.star.table.TableSortField elements. In the com.sun.star.table.TableSortField struct, the Field member specifies the field number by which to sort, and the boolean  member switches between ascending and descending sorting for that field. The boolean  specifies whether the case of letters is important when comparing entries. The  is used to sort according to the sorting rules of a given locale. For some locales, several different sorting rules exist. In this case, the  is used to select one of the sorting rules. The com.sun.star.i18n.Collator service is used to find the possible  values for a locale. Currently, it is not possible to have different locales, algorithms and case sensitivity on the different fields.

The following example sorts the cell range by the second column in ascending order:

Database Operations
This section discusses the operations that treat the contents of a cell range as database data, organized in rows and columns like a database table. These operations are filtering, sorting, adding of subtotals and importing from an external database. Each of the operations is controlled using a descriptor service. The descriptors can be used in two ways:


 * Performing an operation on a cell range. This is described in the following sections about the individual descriptors.
 * Accessing the settings that are stored with a database range. This is described in the section about database ranges.



Filtering
A com.sun.star.sheet.SheetFilterDescriptor object is created using the  method from the range's com.sun.star.sheet.XSheetFilterable interface to filter data in a cell range. After applying the settings to the descriptor, it is passed to the  method.

If true is passed as a  parameter to , the returned descriptor contains default values for all settings. If false is passed and the cell range is a database range that has a stored filter operation, the settings for that filter are used.



The com.sun.star.sheet.XSheetFilterDescriptor interface is used to set the filter criteria as a sequence of com.sun.star.sheet.TableFilterField elements. The com.sun.star.sheet.TableFilterField struct describes a single condition and contains the following members:


 * has the values  or , and specifies how the condition is connected to the previous condition in the sequence. For the first entry,   is ignored.
 * is the number of the field that the condition is applied to.
 * is the type of the condition, such as  or
 * selects a numeric or textual condition.
 * contains the value that is used in the condition if  is true.
 * contains the text that is used in the condition if  is false.

Additionally, the filter descriptor contains a com.sun.star.beans.XPropertySet interface for settings that affect the whole filter operation.

If the property  is , the data that matches the filter criteria is copied to a cell range in the document that starts at the position specified by the   property. Otherwise, the rows that do not match the filter criteria are filtered (hidden) in the original cell range.

The following example filters the range that is in the variable xRange for values greater or equal to 1998 in the second column:

The com.sun.star.sheet.XSheetFilterableEx interface is used to create a filter descriptor from criteria in a cell range in the same manner as the "Advanced Filter" dialog. The com.sun.star.sheet.XSheetFilterableEx interface must be queried from the range that contains the conditions, and the com.sun.star.sheet.XSheetFilterable interface of the range to be filtered must be passed to the  call.

The following example performs the same filter operation as the example before, but reads the filter criteria from a cell range:

Subtotals
A com.sun.star.sheet.SubTotalDescriptor object is created using the  method from the range's com.sun.star.sheet.XSubTotalCalculatable interface to create subtotals for a cell range. After applying the settings to the descriptor, it is passed to the  method.

The  parameter to the   method works in the same manner as the parameter to the   method described in the filtering section. If the  parameter to the   method is , existing subtotal rows are deleted before inserting new ones.

The  method removes the subtotal rows from the cell range without modifying the stored subtotal settings, so that the same subtotals can later be restored.



New fields are added to the subtotal descriptor using the com.sun.star.sheet.XSubTotalDescriptor interface's  method. The  parameter selects the column by which values are grouped. The subtotals are inserted at changes of the column's values. The  parameter specifies which column subtotal values are calculated. It is a sequence of com.sun.star.sheet.SubTotalColumn entries where each entry contains the column number and the function to be calculated.

To query or modify the fields in a subtotal descriptor, the com.sun.star.container.XIndexAccess interface is used to access the fields. Each field's com.sun.star.sheet.XSubTotalField interface gets and sets the group and subtotal columns.

The example below creates subtotals, grouping by the first column and calculating the sum of the third column:

Database Import
The com.sun.star.util.XImportable interface imports data from an external data source (database) into spreadsheet cells. The database has to be registered in LibreOffice API, so that it can be selected using its name. The  call takes a sequence of property values that select the data to import.

Similar to the sort descriptor, the import descriptor's sequence of property values can be constructed from scratch, or the return value of the  method can be used and modified. The  method returns a description of the previously imported data if the cell range is a database range with stored import settings and the   parameter is.



The  property selects a database. The  selects the kind of object from the database that is imported. It can have the following values:


 * If  is , the whole table that is named by   is imported.
 * If  is , the   must be the name of a named query.
 * If  is , the   is used as a literal SQL command string.

If a database name is in the  variable and a table name in , the following code imports that table from the database:

Database Ranges
A database range is a name for a cell range that also stores filtering, sorting, subtotal and import settings, as well as some options.

The com.sun.star.sheet.SpreadsheetDocument service has a property  that is used to get the document's collection of database ranges. A new database range is added using the com.sun.star.sheet.XDatabaseRanges interface's  method that requires the name of the new database range, and a com.sun.star.table.CellRangeAddress with the address of the cell range as arguments. The  method removes a database range.

The com.sun.star.container.XNameAccess interface is used to get a single com.sun.star.sheet.DatabaseRange object. Its com.sun.star.sheet.XCellRangeReferrer interface is used to access the cell range that it is pointed to. The com.sun.star.sheet.XDatabaseRange interface retrieves or changes the com.sun.star.table.CellRangeAddress that is named, and gets the stored descriptors.

All descriptors of a database range are updated when a database operation is carried out on the cell range that the database range points to. The stored filter descriptor and subtotal descriptor can also be modified by changing the objects that are returned by the  and   methods. Calling the refresh method carries out the stored operations again.

Whenever a database operation is carried out on a cell range where a database range is not defined, a temporary database range is used to hold the settings. This temporary database range has its  property set to   and is valid until another database operation is performed on a different cell range. In this case, the temporary database range is modified to refer to the new cell range.

The following example uses the  property to find the temporary database range, and applies a background color to the corresponding cell range. If run directly after the database import example above, this marks the imported data.

Linking External Data
This section explains different ways to link data from external sources into a spreadsheet document. Refer to the Database Import chapter for linking data from a database.

Sheet Links
Each sheet in a spreadsheet document can be linked to a sheet from a different document. The spreadsheet document has a collection of all the sheet links to different source documents.



The interface com.sun.star.sheet.XSheetLinkable is relevant if the current sheet is used as buffer for an external sheet link. The interfaces provides access to the data of the link. A link is established using the com.sun.star.sheet.XSheetLinkable interface's  method. The method's parameters are:


 * The source document's URL. When a sheet link is inserted or updated, the source document is loaded from its URL. Unsaved changes in a source document that is open in memory are not included. All URL types that can be used to load files can also be used in links, including HTTP to link to data from a web server.
 * The name of the sheet in the source document from the contents are copied from. If this string is empty, the source document's first sheet is used, regardless of its name.
 * The filter name and options that are used to load the source document. Refer to the Handling Documents chapter. All spreadsheet file filters can be used, so it is possible, for example, to link to a CSV text file.
 * A com.sun.star.sheet.SheetLinkMode enum value that controls how the contents are copied:
 * If the mode is, all cells from the source sheet are copied, including formulas.
 * If the mode is, formulas are replaced by their results in the copy.

The link mode, source URL and source sheet name can also be queried and changed using the,  ,  ,  ,   and   methods. Setting the mode to  removes the link.

The com.sun.star.sheet.SheetLinks collection contains an entry for every source document that is used in sheet links. If several sheets are linked to different sheets from the same source document, there is only one entry for them. The name that is used for the com.sun.star.container.XNameAccess interface is the source document's URL.

The com.sun.star.sheet.SheetLink service changes a link's source URL, filter or filter options through the com.sun.star.beans.XPropertySet interface. The com.sun.star.util.XRefreshable interface is used to update the link. This affects all sheets that are linked to any sheet from the link's source document.

Cell Area Links
A cell area link is a cell area (range) in a spreadsheet that is linked to a cell area from a different document.



To insert an area link, the com.sun.star.sheet.XAreaLinks interface's  method is used with the following parameters:


 * The position where the link is placed in the document as a com.sun.star.table.CellAddress struct.
 * The source document's URL is used in the same manner as sheet links.
 * A string describing the source range in the source document. This can be the name of a named range or database range, or a direct cell reference, such as "sheet1.a1:c5". Note that the WebQuery import filter creates a named range for each HTML table. These names can be used also.
 * The filter name and filter options are used in the same manner as sheet links.

The  method is used to remove a link.

The com.sun.star.sheet.CellAreaLink service is used to modify or refresh an area link. The com.sun.star.sheet.XAreaLink interface queries and modifies the link's source range and its output range in the document. Note that the output range changes in size after updating if the size of the source range changes.

The com.sun.star.beans.XPropertySet interface changes the link's source URL, filter name and filter options. Unlike sheet links, these changes affect only one linked area. Additionally, the  property is used to set an interval in seconds to periodically update the link. If the value is 0, no automatic updates occur.

The com.sun.star.util.XRefreshable interface is used to update the link.

DDE Links
A DDE link is created whenever the DDE spreadsheet function is used in a cell formula.



The com.sun.star.sheet.DDELink service is only used to query the link's parameters using the com.sun.star.sheet.XDDELink interface, and refresh it using the com.sun.star.util.XRefreshable interface. The DDE link's parameters, Application, Topic and Item are determined by the formula that contains the DDE function, therefore it is not possible to change these parameters in the link object.

The link's name used for the com.sun.star.container.XNameAccess interface consists of the three parameter strings concatenated.

DataPilot Tables
The com.sun.star.sheet.DataPilotTables and related services create and modify  tables in a spreadsheet.

The method  of the interface com.sun.star.sheet.XDataPilotTablesSupplier returns the interface com.sun.star.sheet.XDataPilotTables of the collection of all data pilot tables contained in the spreadsheet.



The com.sun.star.sheet.DataPilotTables service is accessed by getting the com.sun.star.sheet.XDataPilotTablesSupplier interface from a spreadsheet object and calling the  method.

Creating a New DataPilot Table
The first step to creating a new  table is to create a new com.sun.star.sheet.DataPilotDescriptor object by calling the com.sun.star.sheet.XDataPilotTables interface's   method. The descriptor is then used to describe the DataPilot table's layout and options, and passed to the  method of. The other parameters for  are the name for the new table, and the position where the table is to be placed on the spreadsheet.

The com.sun.star.sheet.XDataPilotDescriptor interface offers methods to change the DataPilot table settings:


 * The cell range that contains the source data is set with the  method. It is a com.sun.star.table.CellRangeAddress struct.
 * The individual fields are handled using the,  ,  ,  ,   and   methods. The details are discussed below.
 * The  method sets an additional string that is stored with the DataPilot table, but does not influence its results.
 * The  method returns a com.sun.star.sheet.SheetFilterDescriptor object that can be used to apply filter criteria to the source data. Refer to the section on data operations for details on how to use a filter descriptor.

The layout of the DataPilot table is controlled using the com.sun.star.sheet.DataPilotFields service. Each com.sun.star.sheet.DataPilotField object has an  property that controls where in the DataPilot table the field is used. The com.sun.star.sheet.DataPilotFieldOrientation enum contains the possible orientations:


 * : The field is not used in the table.
 * : Values from this field are used to determine the columns of the table.
 * : Values from this field are used to determine the rows of the table.
 * : The field is used in the table's "page" area, where single values from the field can be selected.
 * : The values from this field are used to calculate the table's data area.

The  property is used to assign a function to the field. For instance, if the field has a  orientation, this is the function that is used for calculation of the results. If the field has  or   orientation, it is the function that is used to calculate subtotals for the values from this field.

The  method returns a collection containing one com.sun.star.sheet.DataPilotField entry for each column of source data, and one additional entry for the "Data" column that becomes visible when two or more fields get the   orientation. Each source column appears only once, even if it is used with several orientations or functions.

The,  ,   and   methods each return a collection of the fields with the respective orientation. In the case of, a single source column can appear several times if it is used with different functions. The  method returns a collection of those fields from the   collection that are not in any of the other collections.

The exact effect of changing a field orientation depends on which field collection the field object was taken from. If the object is from the  collection, the field is added to the collection that corresponds to the new Orientation value. If the object is from any of the other collections, the field is removed from the old orientation and added to the new orientation.

The following example creates a simple DataPilot table with one column, row and data field.

Modifying a DataPilot Table
The com.sun.star.sheet.DataPilotTable service is used to modify an existing DataPilot table. The object for an existing table is available through the com.sun.star.container.XNameAccess interface of the com.sun.star.sheet.DataPilotTables service. It implements the com.sun.star.sheet.XDataPilotDescriptor interface, so that the DataPilot table can be modified in the same manner as the descriptor for a new table in the preceding section. After any change to a DataPilot table's settings, the table is automatically recalculated.

Additionally, the com.sun.star.sheet.XDataPilotTable interface offers a  method that is used to find which range on the spreadsheet the table occupies, and a   method that recalculates the table without changing any settings.

The following example modifies the table from the previous example to contain a second data field using the same source column as the existing data field, but using the "average" function instead.

Removing a DataPilot Table
To remove a DataPilot table from a spreadsheet, call the com.sun.star.sheet.XDataPilotTables interface's  method, passing the DataPilot table's name.

DataPilot Sources
The DataPilot feature in LibreOffice API Calc makes use of an external component that provides the tabular results in the DataPilot table using the field orientations and other settings that are made in the DataPilot dialog or interactively by dragging the fields in the spreadsheet.

Such a component might, for example, connect to an OLAP server, allowing the use of a DataPilot table to interactively display results from that server.



The example that is used here provides four dimensions with the same number of members each, and one data dimension that uses these members as digits to form integer numbers. A resulting DataPilot table look similar to the following:

The example uses the following class to hold the settings that are applied to the DataPilot source:

To create a DataPilot table using a DataPilot source component, three steps are carried out:


 * 1) The application gets the list of available dimensions (fields) from the component.
 * 2) The application applies the user-specified settings to the component.
 * 3) The application gets the results from the component.

The same set of objects are used for all three steps. The root object from which the other objects are accessed is the implementation of the com.sun.star.sheet.DataPilotSource service.

The com.sun.star.sheet.DataPilotSourceDimensions, com.sun.star.sheet.DataPilotSourceHierarchies , com.sun.star.sheet.DataPilotSourceLevels and com.sun.star.sheet.DataPilotSourceMembers services are accessed using their parent object interfaces. That is:


 * com.sun.star.sheet.DataPilotSourceDimensions is the parent object of com.sun.star.sheet.XDimensionsSupplier
 * com.sun.star.sheet.DataPilotSourceHierarchies is the parent object of com.sun.star.sheet.XHierarchiesSupplier
 * com.sun.star.sheet.DataPilotSourceLevels is the parent object of com.sun.star.sheet.XLevelsSupplier
 * com.sun.star.sheet.DataPilotSourceMembers is the parent object of com.sun.star.sheet.XMembersSupplier

All contain the com.sun.star.container.XNameAccess interface to access their children.

Source Object
An implementation of the com.sun.star.sheet.DataPilotSource service must be registered, so that a component can be used as a DataPilot source. If any implementations for the service are present, the External source/interface option in the DataPilot Select Source dialog is enabled. Any of the implementations can then be selected by its implementation name in the External Source dialog, along with four option strings labeled "Source", "Name", "User" and "Password". The four options are passed to the component unchanged.

The option strings are passed to the com.sun.star.lang.XInitialization interface's  method if that interface is present. The sequence that is passed to the call contains four strings with the values from the dialog. Note that the "Password" string is only saved in LibreOffice API's old binary file format, but not in the XML-based format. If the component needs a password, for example, to connect to a database, it must be able to prompt for that password.

The example below uses the first of the strings to determine how many members each dimension should have:

The source object's com.sun.star.beans.XPropertySet interface is used to apply two settings: The  and   properties control if grand totals for columns or rows should be added. The settings are taken from the DataPilot dialog. The example does not use them.

The com.sun.star.sheet.XDataPilotResults interface is used to query the results from the component. This includes only the numeric "data" part of the table. In the example table above, it would be the 9x3 area of cells that are right-aligned. The  call returns a sequence of rows, where each row is a sequence of the results for that row. The com.sun.star.sheet.DataResult struct contains the numeric value in the Value member, and a Flags member contains a combination of the com.sun.star.sheet.DataResultFlags constants:


 * is set if there is a valid result at the entry's position. A result value of zero is different from no result, so this must be set only if the result is not empty.
 * marks a subtotal value that is formatted differently in the DataPilot table output.
 * is set if the result at the entry's position is an error.

In the example table above, all entries have different  numbers, and a   value of. The implementation for the example looks like this:

The com.sun.star.util.XRefreshable interface contains a  method that tells the component to discard cached results and recalculate the results the next time they are needed. The  and   methods are not used by LibreOffice API Calc. The  implementation in the example is empty, because the results are always calculated dynamically.

Dimensions
The com.sun.star.sheet.DataPilotSourceDimensions service contains an entry for each dimension that can be used as column, row or page dimension, for each possible data (measure) dimension, and one for the "data layout" dimension that contains the names of the data dimensions.

The example below initializes a dimension's orientation as DATA for the data dimension, and is otherwise HIDDEN. Thus, when the user creates a new DataPilot table using the example component, the data dimension is already present in the "Data" area of the DataPilot dialog.

The com.sun.star.sheet.DataPilotSourceDimension service contains a com.sun.star.beans.XPropertySet interface that is used for the following properties of a dimension:


 * (read-only) contains the dimension object from which a dimension was cloned, or null if it was not cloned. A description of the com.sun.star.util.XCloneable interface is described below.
 * (read-only) must contain true if the dimension is the "data layout" dimension, otherwise false.
 * controls how a dimension is used in the DataPilot table. If it contains the com.sun.star.sheet.DataPilotFieldOrientation enum values  or , the dimension is used as a column or row dimension, respectively. If the value is  , the dimension is used as data (measure) dimension. The   designates a page dimension, but is not currently used in LibreOffice API Calc. If the value is  , the dimension is not used.
 * contains the position of the dimension within the orientation. This controls the order of the dimensions. If a dimension's orientation is changed, it is added at the end of the dimensions for that orientation, and the  property reflects that position.
 * specifies the function that is used to aggregate data for a data dimension.
 * selects which of the dimension's hierarchies is used in the DataPilot table. See the section on hierarchies below.
 * specifies a list of filter criteria to be applied to the source data before processing. It is currently not used by LibreOffice API Calc.

In the following example, the  method for the dimension only implements the modification of   and , using two lists to store the order of column and row dimensions. Page dimensions are not supported in the example.

The associated  method returns the stored values for   and. If it is the data layout dimension, then IsDataLayoutDimension is true, and the values default for the remaining properties.

The dimension's com.sun.star.util.XCloneable interface is required when a dimension is used in multiple positions. The DataPilot dialog allows the use of a column or row dimension additionally as data dimension, and it also allows multiple use of a data dimension by assigning several functions to it. In both cases, additional dimension objects are created from the original one by calling the  method. Each clone is given a new name using the com.sun.star.container.XNamed interface's  method, then the different settings are applied to the objects. A dimension object that was created using the  method must return the original object that it was created from in the   property.

The example does not support multiple uses of a dimension, so it always returns null from the  method, and the   property is also always.

Hierarchies
A single dimension can have several hierarchies, that is, several ways of grouping the elements of the dimension. For example, date values may be grouped:


 * in a hierarchy with the levels "year", "month" and "day of month".
 * in a hierarchy with the levels "year", "week" and "day of week".

The property  of the com.sun.star.sheet.DataPilotSourceDimension service selects which hierarchy of a dimension is used. The property contains an index into the sequence of names that is returned by the dimension's  method. LibreOffice API Calc currently has no user interface to select a hierarchy, so it uses the hierarchy that the initial value of the  property selects.

The com.sun.star.sheet.DataPilotSourceHierarchy service serves as a container to access the levels object.

In the example, each dimension has only one hierarchy, which in turn has one level.

Levels
Each level of a hierarchy that is used in a DataPilot table corresponds to a column or row showing its members in the left or upper part of the table. The com.sun.star.sheet.DataPilotSourceLevel service contains a com.sun.star.beans.XPropertySet interface that is used to apply the following settings to a level:


 * The  property defines a list of functions that are used to calculate subtotals for each member. If the sequence is empty, no subtotal columns or rows are generated. The com.sun.star.sheet.GeneralFunction enum value   is used to select "automatic" subtotals, determined by the type of the data.
 * The  property controls if result columns or rows are generated for members that have no data.

Both of these settings can be modified by the user in the "Data Field" dialog. The example does not use them.

The com.sun.star.sheet.XDataPilotMemberResults interface is used to get the result header column that is displayed below the level's name for a row dimension, or the header row for a column dimension. The sequence returned from the  call must have the same size as the data result's columns or rows respectively, or be empty. If the sequence is empty, or none of the entries contains the  flag, the level is not shown.

The com.sun.star.sheet.MemberResult struct contains the following members:


 * is the name of the member that is represented by the entry, exactly as returned by the member object's  method. It is used to find the member object, for example when the user double-clicks on the cell.
 * is the string that will be displayed in the cell. It may or may not be the same as.
 * indicates the kind of result the entry represents. It can be a combination of the com.sun.star.sheet.MemberResultFlags constants:
 * indicates there is a member that belongs to this entry.
 * marks an entry that corresponds to a subtotal column or row. The  should be set.
 * marks an entry that is a continuation of the previous entry. In this case, none of the others are set, and the Name and Caption members are both empty.

In the example table shown above, the resulting sequence for the "ones" level would consist of:


 * an entry containing the name and caption "1" and the  flag
 * two entries containing only the  flag
 * the same repeated for member names "2" and "3".

The implementation for the example looks similar to this:

Members
The com.sun.star.sheet.DataPilotSourceMember service contains two settings that are accessed through the com.sun.star.beans.XPropertySet interface:


 * If the boolean  property is , the member and its data are hidden. There is currently no user interface to change this property.
 * The boolean  property controls if the results for a member should be detailed in the following level. If a member has this property set to , only a single result column or row is generated for each data dimension. The property can be changed by the user by double-clicking on a result header cell for the member.

These properties are not used in the example.

Protecting Spreadsheets
The interface com.sun.star.document.XActionLockable protects this cell from painting or updating during changes. The interface can be used to optimize the performance of complex changes, for instance, inserting or deleting formatted text.

The interface com.sun.star.util.XProtectable contains methods to protect and unprotect the spreadsheet with a password. Protecting the spreadsheet protects the locked cells only.


 * The methods  and   to switch the protection on and off. If a wrong password is used to unprotect the spreadsheet, it leads to an exception.
 * The method  returns the protection state of the spreadsheet as a boolean value.

Sheet Outline
The spreadsheet interface com.sun.star.sheet.XSheetOutline contains all the methods to control the row and column outlines of a spreadsheet:

Detective
The spreadsheet interface com.sun.star.sheet.XSheetAuditing supports the detective functionality of the spreadsheet.

Data Validation
Data validation checks if a user entered valid entries.



A cell or cell range object contains the properties  and. They return the interface com.sun.star.beans.XPropertySet of the validation object com.sun.star.sheet.TableValidation. The objects of both properties are equal, except the representation of formulas. The  property uses function names in the current language).


 * Type (type com.sun.star.sheet.ValidationType ): Describes the type of data the cells contain. In text cells, it is possible to check the length of the text.
 * : Determines if blank cells are valid.
 * ShowInputMessage, InputTitle and InputMessage: These properties describe the message that appears if a cell of the validation area is selected.
 * ,,   and   (type com.sun.star.sheet.ValidationAlertStyle ): These properties describe the error message that appear if an invalid value has been entered. If the alert style is STOP, all invalid values are rejected. With the alerts WARNING and INFO, it is possible to keep invalid values. The alert MACRO starts a macro on invalid values. The property   has to contain the name of the macro.

The interface com.sun.star.sheet.XSheetCondition sets the conditions for valid values. The comparison operator, the first and second formula and the base address for relative references in formulas.

The following example enters values between 0.0 and 5.0 in a cell range. The  is the interface com.sun.star.sheet.XSpreadsheet of a spreadsheet.

Validating macro
The macro started on invalid values has two arguments of type String.


 * The first argument is the exact string typed by the user. It is always a string, even for numbers. It may differ from the value displayed by the cell.


 * The second argument is a string that represents the cell address, example : Sheet3.B17

The macro should return a Boolean value. If it returns nothing, or if it returns True, the input is accepted and the cell is updated according to the input.

If the macro decides that the input is incorrect, it may set a value in the cell and then it returns False to reject the input.

Data Consolidation
The data consolidation feature calculates results based on several cell ranges.



The com.sun.star.sheet.XConsolidatable 's method  returns the interface com.sun.star.sheet.XConsolidationDescriptor of a consolidation descriptor (service com.sun.star.sheet.ConsolidationDescriptor ). This descriptor contains all data needed for a consolidation. It is possible to get and set all properties:


 * and : The function for calculation, type com.sun.star.sheet.GeneralFunction.
 * and : A sequence of com.sun.star.table.CellRangeAddress structs with all cell ranges containing the source data.
 * and : A com.sun.star.table.CellAddress containing the first cell of the result cell range.
 * ,,   and  : Determine if the first column or row of each cell range is used to find matching data.
 * and : Determine if the results are linked to the source data (formulas are inserted) or not (only results are inserted).

The method  of the interface com.sun.star.sheet.XConsolidatable performs a consolidation with the passed descriptor.

Charts


The service com.sun.star.table.TableChart represents a chart object. The interface com.sun.star.table.XTableChart provides access to the cell range of the source data and controls the existence of column and row headers.

The interface com.sun.star.container.XNamed retrieves and changes the name of the chart object.

For further information about charts, see Charts.

The service com.sun.star.table.TableCharts represents the collection of all chart objects contained in the table. It implements the interfaces:


 * com.sun.star.table.XTableCharts to create new charts and accessing them by their names.
 * com.sun.star.container.XIndexAccess to access the charts by the insertion index.
 * com.sun.star.container.XEnumerationAccess to create an enumeration of all charts.

The following example shows how  can be a com.sun.star.table.XTableCharts interface of a collection of charts.

Scenarios
A set of scenarios contains different selectable cell contents for one or more cell ranges in a spreadsheet. The data of each scenario in this set is stored in a hidden sheet following the scenario sheet. To change the scenario's data, its hidden sheet has to be modified.



The com.sun.star.sheet.XScenariosSupplier 's method  returns the interface com.sun.star.sheet.XScenarios of the scenario set of the spreadsheet. This scenario set is represented by the service com.sun.star.sheet.Scenarios containing spreadsheet objects. It is possible to access the scenarios through their names that is equal to the name of the corresponding spreadsheet, their index, or using an enumeration (represented by the service com.sun.star.sheet.ScenariosEnumeration ).

The interface com.sun.star.sheet.XScenarios inserts and removes scenarios:


 * The method  adds a scenario with the given name that contains the specified cell ranges.
 * The method  removes the scenario (the spreadsheet) with the given name.

The following method shows how to create a scenario:

The service com.sun.star.sheet.Spreadsheet implements the interface com.sun.star.sheet.XScenario to modify an existing scenario:


 * The method  tests if this spreadsheet is used to store scenario data.
 * The methods  and   retrieves and sets the user comment for this scenario.
 * The method  adds new cell ranges to the scenario.
 * The method  copies the data of this scenario to the spreadsheet containing the scenario set, that is, it makes the scenario visible.

The following method shows how to activate a scenario:

Styles
A style contains all formatting properties for a specific object. All styles of the same type are contained in a collection named a style family. Each style family has a specific name to identify it in the collection. In LibreOffice API Calc, there are two style families named CellStyles and PageStyles. A cell style can be applied to a cell, a cell range, or all cells of the spreadsheet. A page style can be applied to a spreadsheet itself.



The collection of style families is available from the spreadsheet document with the com.sun.star.style.XStyleFamiliesSupplier 's method. The general handling of styles is described in this section, therefore this chapter focuses on the spreadsheet specific style properties.

Cell Styles
Cell styles are predefined packages of format settings that are applied in a single step.



A cell style is represented by the service com.sun.star.sheet.TableCellStyle. If a formatting property is applied directly to a cell, it covers the property of the applied cell style. This service does not support the property. The name of the style is set with the interface com.sun.star.container.XNamed.

The following example creates a new cell style with gray background. The  is the com.sun.star.sheet.XSpreadsheetDocument interface of a spreadsheet document.

Page Styles
A page style is represented by the service com.sun.star.sheet.TablePageStyle. It contains the service com.sun.star.style.PageStyle and additional spreadsheet specific page properties.



The properties,  ,   and   return the interface com.sun.star.sheet.XHeaderFooterContent for the headers and footers for the left and right pages. Headers and footers are represented by the service com.sun.star.sheet.HeaderFooterContent. Each header or footer object contains three text objects for the left, middle and right portion of a header or footer. The methods,   and   return the interface com.sun.star.text.XText of these text portions.

Function Handling
This section describes the services which handle spreadsheet functions.

Calculating Function Results
The com.sun.star.sheet.FunctionAccess service calls any spreadsheet function and gets its result without having to insert a formula into a spreadsheet document.



The service can be instantiated through the service manager. The com.sun.star.sheet.XFunctionAccess interface contains only one method,. The first parameter is the name of the function to call. The name has to be the function's programmatic name.


 * For a built-in function, the English name is always used, regardless of the application's UI language.
 * For an add-in function, the complete internal name that is the add-in component's service name, followed by a dot and the function's name as defined in the interface. For the  function in the example from the add-in section, this would be: " ".

The second parameter to  is a sequence containing the function arguments. The supported types for each argument are described in the com.sun.star.sheet.XFunctionAccess interface description, and are similar to the argument types for add-in functions. The following example passes two arguments to the  function, an array of values and a single value.

In Calc, an argument of the function may accept a range of cells, e.g. When calling a spreadsheet function, a range argument is transmitted as an array with two dimensions. The first corresponds to the row, the second corresponds to the column, indexes are zero-based.

Information about Functions
The services com.sun.star.sheet.FunctionDescriptions and com.sun.star.sheet.FunctionDescription provide help texts about the available spreadsheet cell functions, including add-in functions and their arguments. This is the same information that LibreOffice API Calc displays in the function AutoPilot.



The com.sun.star.sheet.FunctionDescriptions service is instantiated through the service manager. It provides three different methods to access the information for the different functions:


 * By name through the com.sun.star.container.XNameAccess interface.
 * By index through the com.sun.star.container.XIndexAccess interface.
 * By function identifier through the com.sun.star.sheet.XFunctionDescriptions interface's  method. The function identifier is the same used in the com.sun.star.sheet.RecentFunctions service.

The com.sun.star.sheet.FunctionDescription that is returned by any of these calls is a sequence of com.sun.star.beans.PropertyValue structs. To access one of these properties, loop through the sequence, looking for the desired property's name in the Name member. The Arguments property contains a sequence of com.sun.star.sheet.FunctionArgument structs, one for each argument that the function accepts. The struct contains the name and description of the argument, as well as a boolean flag showing if the argument is optional.

The Recently Used Functions section below provides an example on how to use the com.sun.star.sheet.FunctionDescriptions service.

Recently Used Functions
The com.sun.star.sheet.RecentFunctions service provides access to the list of recently used functions of the spreadsheet application, that is displayed in the AutoPilot:Functions and the Function List window for example.



The service can be instantiated through the service manager. The com.sun.star.sheet.XRecentFunctions interface's  method returns a sequence of function identifiers that are used with the com.sun.star.sheet.FunctionDescriptions service. The  method changes the list. If the parameter to the  call contains more entries than the application handles, only the first entries are used. The maximum size of the list of recently used functions, currently 10, can be queried with the  method.

The following example demonstrates the use of the com.sun.star.sheet.RecentFunctions and com.sun.star.sheet.FunctionDescriptions services.

Settings
The com.sun.star.sheet.GlobalSheetSettings service contains settings that affect the whole spreadsheet application. It can be instantiated through the service manager. The properties are accessed using the com.sun.star.beans.XPropertySet interface.



The following example gets the list of user-defined sort lists from the settings and displays them:

Spreadsheet View
The com.sun.star.sheet.SpreadsheetView service is the spreadsheet's extension of the com.sun.star.frame.Controller service and represents a table editing view for a spreadsheet document.



The view object is the spreadsheet application's controller object as described in the chapter Frame-Controller-Model Paradigm in OpenOffice.org. The com.sun.star.frame.XController, com.sun.star.frame.XDispatchProvider and com.sun.star.ui.XContextMenuInterception interfaces work as described in that chapter.

The com.sun.star.view.XSelectionSupplier interface queries and modifies the view's selection. The selection in a spreadsheet view can be a com.sun.star.sheet.SheetCell, com.sun.star.sheet.SheetCellRange , com.sun.star.sheet.SheetCellRanges , com.sun.star.drawing.Shape or com.sun.star.drawing.Shapes object.

The com.sun.star.sheet.XSpreadsheetView interface gives access to the spreadsheet that is displayed in the view. The  method returns the active sheet's object, the   method switches to a different sheet. The parameter to  must be a sheet of the view's document.

The com.sun.star.sheet.XViewSplitable interface splits a view into two parts or panes, horizontally and vertically. The  method splits the view at the specified pixel positions. To remove the split, a position of 0 is passed. The  method returns true if the view is split, the   and   methods return the pixel positions where the view is split. The  and   methods return the cell column or row that corresponds to the split position, and are used with frozen panes as discussed below.

The com.sun.star.sheet.XViewFreezable interface is used to freeze a number of columns and rows in the left and upper part of the view. The  method freezes the specified number of columns and rows. This also sets the split positions accordingly. The  method returns true if the columns or rows are frozen. A view can only have frozen columns or rows, or normal split panes at a time.

If a view is split or frozen, it has up to four view pane objects that represent the individual parts. These are accessed using the com.sun.star.container.XIndexAccess interface. If a view is not split, it contains only one pane object. The active pane of a spreadsheet view is also accessed using the com.sun.star.sheet.SpreadsheetViewPane service's interfaces directly with the com.sun.star.sheet.SpreadsheetView service that inherits them.

The com.sun.star.sheet.XRangeSelection interface is explained in the "Range Selection" chapter below.

The following example uses the com.sun.star.sheet.XViewFreezable interface to freeze the first column and the first two rows:

View Panes
The com.sun.star.sheet.SpreadsheetViewPane service represents a pane in a view that shows a rectangular area of the document. The exposed area of a view pane always starts at a cell boundary. The com.sun.star.sheet.XViewPane interface's,  ,   and   methods query and set the start of the exposed area. The  method returns a com.sun.star.table.CellRangeAddress struct describing which cells are shown in the pane. Columns or rows that are only partly visible at the right or lower edge of the view are not included.

The com.sun.star.sheet.XCellRangeReferrer interface gives direct access to the same cell range of exposed cells that are addressed by the  return value.

The com.sun.star.view.XControlAccess interface's  method gives access to a control model's control for the view pane. Refer to the chapter Models and Views for additional information.

The example below retrieves the cell range that is shown in the second pane. It is the lower left one after freezing both columns and rows, and assigns a cell background:

The index container provides the available panes in a specific order, depending on how the view has been split.

(*) If the sheet is in right-to-left layout mode, the panes are swapped too (e.g., pane 0 would be the right pane or the top right pane).

View Settings
The properties from the com.sun.star.sheet.SpreadsheetViewSettings service are accessed through the com.sun.star.beans.XPropertySet interface controlling the appearance of the view. Most of the properties correspond to settings in the options dialog. The,   and   properties take values of 0 for "show" and 1 for "hide", see com.sun.star.sheet.SpreadsheetViewObjectsMode.

The following example changes the view to display green grid lines:

Range Selection
The view's com.sun.star.sheet.XRangeSelection interface is used to let a user interactively select a cell range in the view, independently of the view's selection. This is used for dialogs that require a cell reference as input. While the range selection is active, a small dialog is shown, similar to the minimized state of LibreOffice API's own dialogs that allow cell reference input.



Before the range selection mode is started, a listener is registered using the  method. The listener implements the com.sun.star.sheet.XRangeSelectionListener interface. Its  or   method is called when the selection is finished or aborted. The com.sun.star.sheet.RangeSelectionEvent struct that is passed to the calls contains the selected range in the  member. It is a string because the user can type into the minimized dialog during range selection.

In the following example, the listener implementation stores the result in a member in the  method, and notifies the main thread about the completion of the selection in the   and   methods:

It is also possible to add another listener using the  method. This listener implements the com.sun.star.sheet.XRangeSelectionChangeListener interface, and its  method is called during the selection when the selection changes. Using this listener normally is not necessary.

After registering the listeners, the range selection mode is started using the  method. The parameter to that method is a sequence of property values with properties from the com.sun.star.sheet.RangeSelectionArguments service:


 * specifies an existing selection value that is shown in the dialog and highlighted in the view when the selection mode is started.
 * is the title for the range selection dialog.
 * specifies when the selection mode is ended. If the value is true, selection is ended when the mouse button is released after selecting a cell range. If it is false or not specified, the user presses the Shrink button in the dialog to end selection mode.

The  method returns immediately after starting the range selection mode. This allows it to be called from a dialog's event handler. The  method is used to cancel the range selection mode programmatically.

The following example lets the user pick a range, and then selects that range in the view. Note that the use of wait to wait for the end of the selection is not how a GUI application normally handles the events.

Spreadsheet Add-Ins
An add-in component is used to add new functions to the spreadsheet application that can be used in cell formulas, such as the built-in functions. A spreadsheet add-in is a UNO component. The chapter Writing UNO Components describes how to write and deploy a UNO component.



The functions that the add-in component exports to the spreadsheet application have to be defined in a new interface. The function names in the interface, together with the component's service name, are used internally to identify an add-in function. For a list of the supported types for function arguments and return values, see the com.sun.star.sheet.AddIn service description. An example interface that defines two functions is similar to the following code:

In addition to this interface, the add-in has to implement the interfaces from the com.sun.star.sheet.AddIn service and the usual interfaces every component has to support.

Function Descriptions
The methods from the com.sun.star.sheet.XAddIn interface are used to provide descriptions of the user-visible functions.

The  and   methods are used to map between the internal function name, as defined in the interface and the function name as shown to the user of the spreadsheet application. The user-visible name, as well as the function and argument descriptions, can be translated strings for the language which is set using.

The  method sorts each add-in functions into one of the spreadsheet application's function categories. It returns the category's internal (non-translated) name. In addition, the  method provides a translated name for the category.

The,   and   methods provide descriptions of the function and its arguments that are shown to the user, for example in the function AutoPilot.

Service Names
The add-in component has to support two services, the com.sun.star.sheet.AddIn service, and an additional service that is used to identify the set of functions that the add-in supplies. There may be several implementations of the same set of functions. In that case, they all use the same service name, but different implementation names. Therefore, a spreadsheet document that uses the functions can make use of the implementation that is present.

The com.sun.star.lang.XServiceInfo methods  and   handle both service names, and the component also has to be registered for both services. In addition, the component has to implement the com.sun.star.lang.XServiceName interface, and in its  method return the name of the function-specific service.

Compatibility Names
Optionally, the component can implement the com.sun.star.sheet.XCompatibilityNames interface, and in the  method return a sequence of locale-dependent compatibility names for a function. These names are used by the spreadsheet application when loading or saving Excel files. They should only be present for a function if it is known to be an Excel add-in function with equivalent functionality.

The sequence of compatibility names for a function may contain several names for a single locale. In that case, all of these names are considered when importing a file. When exporting, the first name is used. If a file is exported in a locale for which no entry is present, the first entry is used. If there is a default locale, the entries for that locale are first in the sequence.

Custom Functions
The user-visible functions have to be implemented as defined in the interface. The spreadsheet application does the necessary conversions to pass the arguments. For example, floating point numbers are rounded if a function has integer arguments. To enable the application to find the functions, it is important that the component implements the com.sun.star.lang.XTypeProvider interface.

The  function from the example interface above can be implemented like this:

Variable Results
It is also possible to implement functions with results that change over time. Whenever such a result changes, the formulas that use the result are recalculated and the new values are shown in the spreadsheet. This can be used to display data from a real-time data feed in a spreadsheet.

In its interface, a function with a variable result must be defined with a return type of com.sun.star.sheet.XVolatileResult, such as the  function from the example interface above. The function's implementation must return an object that implements the com.sun.star.sheet.VolatileResult service. Subsequent calls to the same function with the same arguments return the same object. An implementation that returns a different result object for every name looks like this:

The result object has to implement the  and   methods from the com.sun.star.sheet.XVolatileResult interface to maintain a list of listeners, and notify each of these listeners by calling the com.sun.star.sheet.XResultListener interface's   method whenever a new result is available. The com.sun.star.sheet.ResultEvent object that is passed to the  call must contain the new result in the Value member. The possible types for the result are the same as for a function's return value if no volatile results are involved.

If a result is already available when  is called, it can be publicized by immediately calling   for the new listener. Otherwise, the spreadsheet application displays a "#N/A" error value until a result is available.

The following example shows a simple implementation of a result object. Every time the incrementValue method is called, for example, from a background thread, the result value is incremented and the listeners are notified.