LibreOffice Developer's Guide: Chapter 8 - 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.

Spreadsheet service	Included com::sun::star::table service
com.sun.star.sheet.SheetCell	com.sun.star.table.Cell
com.sun.star.sheet.Cells	-
com.sun.star.sheet.SheetCellRange	com.sun.star.table.CellRange
com.sun.star.sheet.SheetCellRanges	-
com.sun.star.sheet.SheetCellCursor	com.sun.star.table.CellCursor

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

Spreadsheet Document Component

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 insertTextContent() or the service manager is asked for a shape object and inserts it into a sheet using add() 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 getRemoteServiceManager() retrieves a connection. Refer to chapter First Steps for additional information.

import com.sun.star.lang.XComponent;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.beans.PropertyValue;

...

protected XComponent newSpreadsheetComponent() throws java.lang.Exception {
    String loadUrl = "private:factory/scalc";
    xRemoteServiceManager = this.getRemoteServiceManager(unoUrl);
    Object desktop = xRemoteServiceManager.createInstanceWithContext(
        "com.sun.star.frame.Desktop", xRemoteContext);
    XComponentLoader xComponentLoader = UnoRuntime.queryInterface(
        XComponentLoader.class, desktop);
    PropertyValue[] loadProps = new PropertyValue[0];
    return xComponentLoader.loadComponentFromURL(loadUrl, "_blank", 0, loadProps);
}

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

import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheet;

...

/** Inserts a new empty spreadsheet with the specified name.
    @param xSheetComponent The XComponent interface of a loaded document object
    @param aName The name of the new sheet.
    @param nIndex The insertion index.
    @return The XSpreadsheet interface of the new sheet.
*/
public XSpreadsheet insertSpreadsheet(XComponent xSheetComponent, String aName, short nIndex) {
    XSpreadsheetDocument xDocument = UnoRuntime.queryInterface(
        XSpreadsheetDocument.class, xSheetComponent);

    // Collection of sheets
    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {
        xSheets.insertNewByName(aName, nIndex);
        xSheet = UnoRuntime.queryInterface(
            com.sun.star.sheet.XSpreadsheet.class, xSheets.getByName(aName));
    } catch (Exception ex) {
    }

    return xSheet;
}

Example: Editing Spreadsheet Cells

The method insertSpreadsheet() 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 insertSpreadsheet() is derived from com.sun.star.table.XCellRange. By working with it, cells can be accessed immediately using getCellByPosition():

void cellWork(XSpreadsheet xRange) {

    com.sun.star.beans.XPropertySet xPropSet = null;
    com.sun.star.table.XCell xCell = null;

    // Access and modify a VALUE CELL
    xCell = xRange.getCellByPosition(0, 0);
    // Set cell value.
    xCell.setValue(1234);

    // Get cell value.
    double nDblValue = xCell.getValue() * 2;
    xRange.getCellByPosition(0, 1).setValue(nDblValue);

    // Create a FORMULA CELL
    xCell = xRange.getCellByPosition(0, 2);
    // Set formula string.
    xCell.setFormula("=1/0");

    // Get error type.
    boolean bValid = (xCell.getError() == 0);
    // Get formula string.
    String aText = "The formula " + xCell.getFormula() + " is ";
    aText += bValid ? "valid." : "erroneous.";

    // Insert a TEXT CELL using the XText interface
    xCell = xRange.getCellByPosition(0, 3);
    com.sun.star.text.XText xCellText = (com.sun.star.text.XText)
        UnoRuntime.queryInterface(com.sun.star.text.XText.class, xCell);
    com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
    xCellText.insertString(xTextCursor, aText, false);

    //--------------------
    // get the cell properties
    //--------------------

    xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class,
    xCell);

    //--------------------
    // set the cell's background color
    //--------------------
    Integer bg_color = Integer.valueOf(0x008000);
    xPropSet.setPropertyValue("CellBackColor", bg_color);

    //--------------------
    // set the cell's foreground (text) color
    //--------------------
    Integer fg_color = Integer.valueOf(0x800000);
    xPropSet.setPropertyValue("CharColor", fg_color);

    //--------------------
    // set horizontal alignment to 'centered'
    //--------------------
    xPropSet.setPropertyValue("HoriJustify", com.sun.star.table.CellHoriJustify.CENTER);

    //--------------------
    // set vertical alignment to 'top'
    //--------------------
    xPropSet.setPropertyValue("VertJustify", com.sun.star.table.CellVertJustify.TOP);

    //--------------------
    // use bold font
    //--------------------
    xPropSet.setPropertyValue("CharWeight", com.sun.star.awt.FontWeight.BOLD);
    xPropSet.setPropertyValue("CharWeightAsian", com.sun.star.awt.FontWeight.BOLD);
    xPropSet.setPropertyValue("CharWeightComplex", com.sun.star.awt.FontWeight.BOLD);

    //--------------------
    // add a cell border
    //--------------------

    // one point seems to be 1/72 inch
    double lineWidthInPoints = 2.5;

    // convert to units of 1/100 mm
    short integerLineWidth = (short) Math.round(2540 / 72.0 * lineWidthInPoints);

    final String[] borderNames = {
    "LeftBorder", "RightBorder", "TopBorder", "BottomBorder"
    };

    for (int i = 0; i < borderNames.length; ++i)
    {
    com.sun.star.table.BorderLine borderLine = new com.sun.star.table.BorderLine();
    borderLine.OuterLineWidth = integerLineWidth;
    xPropSet.setPropertyValue(borderNames[i], borderLine);
    }

}

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 mxDocCursor), one gets for example the following properties (OpenOffice.org 2.4):

Attribute Name	Type	Remark
AbsoluteName	string	READONLY
AsianVerticalMode	boolean
BottomBorder	com.sun.star.table.BorderLine
CellBackColor	long
CellProtection	com.sun.star.util.CellProtection
CellStyle	string
CharColor	long
CharContoured	boolean
CharCrossedOut	boolean
CharEmphasis	short
CharFont	short
CharFontCharSet	short
CharFontCharSetAsian	short
CharFontCharSetComplex	short
CharFontFamily	short
CharFontFamilyAsian	short
CharFontFamilyComplex	short
CharFontName	string
CharFontNameAsian	string
CharFontNameComplex	string
CharFontPitch	short
CharFontPitchAsian	short
CharFontPitchComplex	short
CharFontStyleName	string
CharFontStyleNameAsian	string
CharFontStyleNameComplex	string
CharHeight	float
CharHeightAsian	float
CharHeightComplex	float
CharLocale	com.sun.star.lang.Locale
CharLocaleAsian	com.sun.star.lang.Locale
CharLocaleComplex	com.sun.star.lang.Locale
CharPosture	com.sun.star.awt.FontSlant
CharPostureAsian	com.sun.star.awt.FontSlant
CharPostureComplex	com.sun.star.awt.FontSlant
CharRelief	short
CharShadowed	boolean
CharStrikeout	short
CharUnderline	short
CharUnderlineColor	long
CharUnderlineHasColor	boolean
CharWeight	float	see also com.sun.star.awt.FontWeight
CharWeightAsian	float	see also com.sun.star.awt.FontWeight
CharWeightComplex	float	see also com.sun.star.awt.FontWeight
CharWordMode	boolean
ChartColumnAsLabel	boolean
ChartRowAsLabel	boolean
ConditionalFormat	com.sun.star.sheet.XSheetConditionalEntries
ConditionalFormatLocal	com.sun.star.sheet.XSheetConditionalEntries
ConditionalFormatXML	com.sun.star.sheet.XSheetConditionalEntries	READONLY
DiagonalBLTR	com.sun.star.table.BorderLine
DiagonalTLBR	com.sun.star.table.BorderLine
FormulaLocal	string
FormulaResultType	com.sun.star.table.CellContentType	READONLY
HoriJustify	com.sun.star.table.CellHoriJustify
IsCellBackgroundTransparent	boolean
IsTextWrapped	boolean
LeftBorder	com.sun.star.table.BorderLine
NumberFormat	long
NumberingRules	com.sun.star.container.XIndexReplace
Orientation	com.sun.star.table.CellOrientation
ParaAdjust	short
ParaBottomMargin	long
ParaIndent	short
ParaIsCharacterDistance	boolean
ParaIsForbiddenRules	boolean
ParaIsHangingPunctuation	boolean
ParaIsHyphenation	boolean
ParaLastLineAdjust	short
ParaLeftMargin	long
ParaRightMargin	long
ParaTopMargin	long
Position	com.sun.star.awt.Point	READONLY
RightBorder	com.sun.star.table.BorderLine
RotateAngle	long
RotateReference	com.sun.star.table.CellVertJustify
ShadowFormat	com.sun.star.table.ShadowFormat
ShrinkToFit	boolean
Size	com.sun.star.awt.Size	READONLY
TableBorder	com.sun.star.table.TableBorder
TopBorder	com.sun.star.table.BorderLine
UserDefinedAttributes	com.sun.star.container.XNameContainer
Validation	com.sun.star.beans.XPropertySet
ValidationLocal	com.sun.star.beans.XPropertySet
ValidationXML	com.sun.star.beans.XPropertySet	READONLY
VertJustify	com.sun.star.table.CellVertJustify
WritingMode	short

Handling Spreadsheet Document Files

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 XComponent 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:

com::sun::star::lang::XComponent loadComponentFromURL( [IN] string aURL,
            [IN] string aTargetFrameName,
            [IN] long nSearchFlags,
            [IN] sequence <com::sun::star::beans::PropertyValue[] aArgs > )

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 "_blank" and set the search flags to 0. In most cases, existing frames are not reused.

The URL can be a file: URL, an http: URL, an ftp: 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 AsTemplate and Hidden are boolean values and used for programming. If AsTemplate is true, the loader creates a new untitled document from the given URL. If it is false, template files are loaded for editing. If Hidden 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:

// the method getRemoteServiceManager is described in the chapter First Steps
mxRemoteServiceManager = this.getRemoteServiceManager(unoUrl);

// retrieve the Desktop object, we need its XComponentLoader
Object desktop = mxRemoteServiceManager.createInstanceWithContext(
    "com.sun.star.frame.Desktop", mxRemoteContext);

// query the XComponentLoader interface from the Desktop service
XComponentLoader xComponentLoader = UnoRuntime.queryInterface(
    XComponentLoader.class, desktop);

// define load properties according to com.sun.star.document.MediaDescriptor

/* or simply create an empty array of com.sun.star.beans.PropertyValue structs:
    PropertyValue[] loadProps = new PropertyValue[0]
*/

// the boolean property Hidden tells the office to open a file in hidden mode
PropertyValue[] loadProps = new PropertyValue[1];
loadProps[0] = new PropertyValue();
loadProps[0].Name = "Hidden";
loadProps[0].Value = Boolean.TRUE;
loadUrl = "file:///c:/MyCalcDocument.ods"

// load
return xComponentLoader.loadComponentFromURL(loadUrl, "_blank", 0, loadProps);

Saving Spreadsheet Documents

Storing

Documents are storable through their interface com.sun.star.frame.XStorable. This interface is discussed in detail in Office Development. An XStorable 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 storeAsUrl() is the exact representation of File - Save As from the File menu, that is, it changes the current document location. In contrast, storeToUrl() 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 FilterName that takes filter names defined in the configuration file:

 <OfficePath>\share\config\registry\instance\org\openoffice\Office\TypeDetection.xml

In TypeDetection.xml look for <Filter/> elements, their cfg:name attribute contains the needed strings for FilterName. 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:

<Filter cfg:name="MS Excel 97">
<Installed cfg:type="boolean">true</Installed>
<UIName cfg:type="string" cfg:localized="true">
    <cfg:value xml:lang="en-US">Microsoft Excel 97/2000/XP</cfg:value>
</UIName>
<Data cfg:type="string">5,calc_MS_Excel_97,com.sun.star.sheet.SpreadsheetDocument,,3,,0,,</Data>
</Filter>

The following method stores a document using this filter:

/** Store a document, using the MS Excel 97/2000/XP Filter
*/
protected void storeDocComponent(XComponent xDoc, String storeUrl) throws java.lang.Exception {

    XStorable xStorable = UnoRuntime.queryInterface(XStorable.class, xDoc);
    PropertyValue[] storeProps = new PropertyValue[1];
    storeProps[0] = new PropertyValue();
    storeProps[0].Name = "FilterName";
    storeProps[0].Value = "MS Excel 97";
    xStorable.storeAsURL(storeUrl, storeProps);
}

If an empty array of PropertyValue 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.PropertyValues. The property FilterName contains the name and the property FilterOptions contains the filter options.

Note:
This list is no longer current as of OpenOffice 4.1.x.

Note:
All filter names are case-sensitive. For compatibility reasons the filter names will not be changed. Therefore, some of the filters seem to have "curious" names.

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

Filter name	Description	Import	Export
StarOffice XML (Calc)	Standard XML filter	●	●
calc_StarOffice_XML_Calc_Template	XML filter for templates	●	●
StarCalc 5.0	The binary format of StarOffice Calc 5.x	●	●
StarCalc 5.0 Vorlage/Template	StarOffice Calc 5.x templates	●	●
StarCalc 4.0	The binary format of StarCalc 4.x	●	●
StarCalc 4.0 Vorlage/Template	StarCalc 4.x templates	●	●
StarCalc 3.0	The binary format of StarCalc 3.x	●	●
StarCalc 3.0 Vorlage/Template	StarCalc 3.x templates	●	●
HTML (StarCalc)	HTML filter	●	●
calc_HTML_WebQuery	HTML filter for external data queries	●
MS Excel 97	Microsoft Excel 97/2000/XP	●	●
MS Excel 97 Vorlage/Template	Microsoft Excel 97/2000/XP templates	●	●
MS Excel 95	Microsoft Excel 5.0/95	●	●
MS Excel 5.0/95	Different name for the same filter	●	●
MS Excel 95 Vorlage/Template	Microsoft Excel 5.0/95 templates	●	●
MS Excel 5.0/95 Vorlage/Template	Different name for the same filter	●	●
MS Excel 4.0	Microsoft Excel 2.1/3.0/4.0	●
MS Excel 4.0 Vorlage/Template	Microsoft Excel 2.1/3.0/4.0 templates	●
Lotus	Lotus 1-2-3	●
Text - txt - csv (StarCalc)	Comma separated values	●	●
Rich Text Format (StarCalc)		●	●
dBase		●	●
SYLK	Symbolic Link	●	●
DIF	Data Interchange Format	●	●

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:

Character Set	Index
Unknown	0
Windows-1252/WinLatin 1 (Western)	1
Apple Macintosh (Western)	2
DOS/OS2-437/US (Western)	3
DOS/OS2-850/International (Western)	4
DOS/OS2-860/Portuguese (Western)	5
DOS/OS2-861/Icelandic (Western)	6
DOS/OS2-863/Canadian-French (Western)	7
DOS/OS2-865/Nordic (Western)	8
System default	9
Symbol	10
ASCII/US (Western)	11
ISO-8859-1 (Western)	12
ISO-8859-2 (Central European)	13
ISO-8859-3 (Latin 3)	14
ISO-8859-4 (Baltic)	15
ISO-8859-5 (Cyrillic)	16
ISO-8859-6 (Arabic)	17
ISO-8859-7 (Greek)	18
ISO-8859-8 (Hebrew)	19
ISO-8859-9 (Turkish)	20
ISO-8859-14 (Western)	21
ISO-8859-15/EURO (Western)	22
DOS/OS2-737 (Greek)	23
DOS/OS2-775 (Baltic)	24
DOS/OS2-852 (Central European)	25
DOS/OS2-855 (Cyrillic)	26
DOS/OS2-857 (Turkish)	27
DOS/OS2-862 (Hebrew)	28
DOS/OS2-864 (Arabic)	29
DOS/OS2-866/Russian (Cyrillic)	30
DOS/OS2-869/Modern (Greek)	31
DOS/Windows-874 (Thai)	32
Windows-1250/WinLatin 2 (Central European)	33
Windows-1251 (Cyrillic)	34
Windows-1253 (Greek)	35
Windows-1254 (Turkish)	36
Windows-1255 (Hebrew)	37
Windows-1256 (Arabic)	38
Windows-1257 (Baltic)	39
Windows-1258 (Vietnamese)	40
Apple Macintosh (Arabic)	41
Apple Macintosh (Central European)	42
Apple Macintosh/Croatian (Central European)	43
Apple Macintosh (Cyrillic)	44
Not supported: Apple Macintosh (Devanagari)	45
Not supported: Apple Macintosh (Farsi)	46
Apple Macintosh (Greek)	47
Not supported: Apple Macintosh (Gujarati)	48
Not supported: Apple Macintosh (Gurmukhi)	49
Apple Macintosh (Hebrew)	50
Apple Macintosh/Icelandic (Western)	51
Apple Macintosh/Romanian (Central European)	52
Apple Macintosh (Thai)	53
Apple Macintosh (Turkish)	54
Apple Macintosh/Ukrainian (Cyrillic)	55
Apple Macintosh (Chinese Simplified)	56
Apple Macintosh (Chinese Traditional)	57
Apple Macintosh (Japanese)	58
Apple Macintosh (Korean)	59
Windows-932 (Japanese)	60
Windows-936 (Chinese Simplified)	61
Windows-Wansung-949 (Korean)	62
Windows-950 (Chinese Traditional)	63
Shift-JIS (Japanese)	64
GB-2312 (Chinese Simplified)	65
GBT-12345 (Chinese Traditional)	66
GBK/GB-2312-80 (Chinese Simplified)	67
BIG5 (Chinese Traditional)	68
EUC-JP (Japanese)	69
EUC-CN (Chinese Simplified)	70
EUC-TW (Chinese Traditional)	71
ISO-2022-JP (Japanese)	72
ISO-2022-CN (Chinese Simplified)	73
KOI8-R (Cyrillic)	74
Unicode (UTF-7)	75
Unicode (UTF-8)	76
ISO-8859-10 (Central European)	77
ISO-8859-13 (Central European)	78
EUC-KR (Korean)	79
ISO-2022-KR (Korean)	80
JIS 0201 (Japanese)	81
JIS 0208 (Japanese)	82
JIS 0212 (Japanese)	83
Windows-Johab-1361 (Korean)	84
GB-18030 (Chinese Simplified)	85
BIG5-HKSCS (Chinese Traditional)	86
TIS 620 (Thai)	87
KOI8-U (Cyrillic)	88
ISCII Devanagari (Indian)	89
Unicode (Java's modified UTF-8)	90
Adobe Standard	91
Adobe Symbol	92
PT 154 (Windows Cyrillic Asian codepage developed in ParaType)	93
Unicode UCS4	65534
Unicode UCS2	65535

Filter Options for the CSV Filter

This filter accepts an option string containing at least 5 tokens, separated by commas. Tokens 6 and following 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.

Example Filter Options String	Field Separator (1)	Text Delimiter (2)	Character Set (3)	Number of First Line (4)	Cell Format Codes for the four Columns (5)
Example Filter Options String	Field Separator (1)	Text Delimiter (2)	Character Set (3)	Number of First Line (4)	Column	Code
File Format: Four columns date-num-num-num	,	"	System	line no. 1	1 2 3 4	YY/MM/DD = 5 Standard = 1 Standard = 1 Standard = 1
Token	44	34	0	1	1/5/2/1/3/1/4/1

For the filter options above, set the PropertyValue FilterOptions 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.

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.
The text delimiter as ASCII value, that is, 34 for double quotes and 39 for single quotes.
The character set used in the file as described above.
Number of the first line to convert. The first line in the file has the number 1.
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:

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

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:

MS-LCID: Windows Language Code Identifier (LCID) Reference

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 false or true. Default value : false.

Token 7, csv export

This token is the equivalent of the check box "Quote all text cells".

String, either false or true. Default value : false.

Token 8, csv import

This token is the equivalent of the check box "Detect special numbers".

String, either false or true. Default value : false.

Token 8, csv export

This token has no UI equivalent. If true, the number cells are stored as numbers. If false, the numbers are stored as text, with text delimiters.

String, either false or true. Default value : true.

Token 9, csv import

Not used.

Token 9, csv export

This token is the equivalent of the check box "Save cell contents as shown".

String, either false or true. Default value : true.

Token 10, csv import

Not used.

Token 10, csv export

Export cell formulas.

String, either false or true. Default value : false.

Token 11, csv import

Remove spaces. Trim leading and trailing spaces, when reading the file.

String, either false or true. Default value : false.

Token 11, csv export

Not used.

Token 12, csv import

Not used.

Token 12, csv export

Export the entire document to individual sheets .csv files or a specified sheet.

0 or absent means the default behaviour, first sheet from command line, or current sheet in macro filter options, exported to sample.csv
-1 for all sheets, each sheet is exported to an individual file of the base file name concatenated with the sheet name, for example sample-Sheet1.csv, sample-Sheet2.csv and sample-Sheet3.csv
N export the N-th sheet within the range of number of sheets. Example: to export the second sheet, set 2 here to get sample-Sheet2.csv

Since LibreOffice 7.2

Token 13, csv import

Determines whether formula expressions starting with a = equal sign character are to be evaluated as formulas or imported as textual data.

String, either false or true. Default value : false.

If true evaluate formulas on input. If false formulas are input as text. If omitted (not present at all), the default value is true to keep the behaviour of old versions' options string that didn't have this token at all. If present and empty (or any other value than true) the default value is false.

Since LibreOffice 7.3

Token 13, csv export

Not used.

Token 14, csv import

Automatically detected since LibreOffice 7.6

Token 14, csv export

String, either false or true. Default value : false.

If true include a byte-order-mark (BOM) in the export. If false the export does not include a BOM. If omitted (not present at all), the default value is false to keep the behaviour of old versions' options string that didn't have this token at all. If present and empty (or any other value than true) the default value is false.

Since LibreOffice 7.6

Examples

Import from UTF-8, Language German, Comma separated, Text delimiter ", Quoted field as text:
44,34,76,1,,1031,true,true

Export to Windows-1252, Field delimiter : comma, Text delimiter : quote, Save cell contents as shown:
44,34,ANSI,1,,0,false,true,true

Export all 3 sheets of sample.ods to sample-Sheet1.csv, sample-Sheet2.csv and sample-Sheet3.csv:
44,34,UTF-8,1,,0,false,true,false,false,false,-1

Printing Spreadsheet Documents

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 xDoc to print to the standard printer without any settings:

// query the XPrintable interface from your document
XPrintable xPrintable = UnoRuntime.queryInterface(XPrintable.class, xDoc);

// create an empty printOptions array
PropertyValue[] printOpts = new PropertyValue[0];

// kick off printing
xPrintable.print(printOpts);

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

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

Properties of com.sun.star.view.PrinterDescriptor
Name	`string` - Specifies the name of the printer queue to be used.
PaperOrientation	com.sun.star.view.PaperOrientation Specifies the orientation of the paper.
PaperFormat	com.sun.star.view.PaperFormat Specifies a predefined paper size or if the paper size is a user-defined size.
PaperSize	com.sun.star.awt.Size Specifies the size of the paper in 100th mm.
IsBusy	`boolean` - Indicates if the printer is busy.
CanSetPaperOrientation	`boolean` - Indicates if the printer allows changes to PaperOrientation.
CanSetPaperFormat	`boolean` - Indicates if the printer allows changes to PaperFormat.
CanSetPaperSize	`boolean` - Indicates if the printer allows changes to PaperSize.

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

Properties of com.sun.star.view.PrintOptions
CopyCount	`short` - Specifies the number of copies to print.
FileName	`string` - If set, specifies the name of the file to print to.
Collate	`boolean` - Advises the printer to collate the pages of the copies. If true, a whole document is printed prior to the next copy, otherwise the page copies are completed together.
Sort	`boolean` - Advises the printer to sort the pages of the copies.
Pages	`string` - Specifies the pages to print with the same format as in the print dialog of the GUI, for example, "1, 3, 4-7, 9-".
Wait	`boolean` - Advises that the print job should be performed synchronously, i.e. wait until printing is complete before returning from printing. Otherwise return is immediate and following actions (e.g. closing the corresponding model) may fail until printing is complete. Default is false.

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

protected void printDocComponent(XComponent xDoc) throws java.lang.Exception {

    XPrintable xPrintable = UnoRuntime.queryInterface(XPrintable.class, xDoc);
    PropertyValue[] printerDesc = new PropertyValue[1];
    printerDesc[0] = new PropertyValue();
    printerDesc[0].Name = "Name";
    printerDesc[0].Value = "5D PDF Creator";

    xPrintable.setPrinter(printerDesc);

    PropertyValue[] printOpts = new PropertyValue[1];
    printOpts[0] = new PropertyValue();
    printOpts[0].Name = "Pages";
    printOpts[0].Value = "3-5,7";

    xPrintable.print(printOpts);
}

Page Breaks and Scaling for Printout

Manual page breaks can be inserted and removed using the property IsStartOfNewPage 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 PageScale and ScaleToPages of the current page style. Both of the properties are short numbers. The PageScale property expects a percentage and ScaleToPages 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.

Methods of com.sun.star.sheet.XPrintAreas
getPrintAreas()	Returns the print areas of the sheet.
setPrintAreas()	Sets the print areas of the sheet.
getPrintTitleColumns()	Returns true if the title columns are repeated on all subsequent print pages to the right.
setPrintTitleColumns()	Specifies if the title columns are repeated on all subsequent print pages to the right.
getTitleColumns()	Returns the range of columns that are marked as title columns.
setTitleColumns()	Sets the range of columns marked as title columns.
getPrintTitleRows()	Returns true if the title rows are repeated on all subsequent print pages to the bottom.
setPrintTitleRows()	Specifies if the title rows are repeated on all subsequent print pages to the bottom.
getTitleRows()	Returns the range of rows that are marked as title rows.
setTitleRows()	Sets the range of rows marked as title rows.

Working with Spreadsheet Documents

Document Structure

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.

Spreadsheet Document

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

Spreadsheets Container

When the spreadsheet container is retrieved from a document using its getSheets() 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:

/** Returns the spreadsheet with the specified index (0-based).
    @param xDocument The XSpreadsheetDocument interface of the document.
    @param nIndex The index of the sheet.
    @return The XSpreadsheet interface of the sheet. */
public com.sun.star.sheet.XSpreadsheet getSpreadsheet(
    com.sun.star.sheet.XSpreadsheetDocument xDocument, int nIndex) {

    // Collection of sheets
    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {
        com.sun.star.container.XIndexAccess xSheetsIA = (com.sun.star.container.XIndexAccess)
        UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, xSheets);
        xSheet = (com.sun.star.sheet.XSpreadsheet) xSheetsIA.getByIndex(nIndex);
    } catch (Exception ex) {
    }

    return xSheet;
}

/** Returns the spreadsheet with the specified name.
    @param xDocument The XSpreadsheetDocument interface of the document.
    @param aName The name of the sheet.
    @return The XSpreadsheet interface of the sheet. */
public com.sun.star.sheet.XSpreadsheet getSpreadsheet(
        com.sun.star.sheet.XSpreadsheetDocument xDocument,
        String aName) {

    // Collection of sheets
    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {
        com.sun.star.container.XNameAccess xSheetsNA = (com.sun.star.container.XNameAccess)
        UnoRuntime.queryInterface(com.sun.star.container.XNameAccess.class, xSheets);
        xSheet = (com.sun.star.sheet.XSpreadsheet) xSheetsNA.getByName(aName);
    } catch (Exception ex) {
    }

    return xSheet;
}

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:

Methods of com.sun.star.sheet.XSpreadsheets
insertNewByName()	Creates a new empty spreadsheet with the specified name and inserts it at the specified position.
moveByName()	Moves the spreadsheet with the specified name to a new position.
copyByName()	Creates a copy of a spreadsheet, renames it and inserts it at a new position.

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

/** Inserts a new empty spreadsheet with the specified name.
    @param xDocument The XSpreadsheetDocument interface of the document.
    @param aName The name of the new sheet.
    @param nIndex The insertion index.
    @return The XSpreadsheet interface of the new sheet.
*/
public com.sun.star.sheet.XSpreadsheet insertSpreadsheet(
        com.sun.star.sheet.XSpreadsheetDocument xDocument,
        String aName, short nIndex ) {
    // Collection of sheets
    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
    com.sun.star.sheet.XSpreadsheet xSheet = null;

    try {
        xSheets.insertNewByName(aName, nIndex);
        xSheet = xSheets.getByName( aName );
    } catch (Exception ex) {
    }

    return xSheet;
}

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.

Main Spreadsheet Services

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:

Spreadsheet

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 SheetCellRange is a rectangular range of calculation cells that includes the following services:

Services supported by SheetCellRange

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

SheetCellRange Interfaces

A SheetCellRange 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 SheetCellRange. 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 SheetCellRange 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 SheetCellRange, 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 getRangeAddress() returns the corresponding address description struct com.sun.star.table.CellRangeAddress.
Charts can be based on a SheetCellRange, 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 SheetCell includes the following services:

SheetCell

The SheetCell exports the following interfaces:

SheetCell Interfaces

The SheetCell 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 SheetCell 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 SheetCell. Its text fields are accessed through com.sun.star.text.XTextFieldsSupplier.
A SheetCell is a special case of a SheetCellRange. 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 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 SheetCellRanges service includes cell, paragraph and character property services, and it offers a query option:

Services of SheetCellRanges

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

Implemented interfaces of SheetCellRanges

The SheetCellRanges 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 SheetCellRanges 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 SheetCellRanges maintain a sub-container of all cells in the ranges that are not empty, obtainable through the 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 SheetCellRanges supports selected SheetCellRange 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 TableColumns supports name access to the single columns and rows (com.sun.star.table.TableColumn and com.sun.star.table.TableRow) of a SheetCellRange.

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

Collection of table columns

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

Collection of table rows

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 TableColumn and TableRow 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:

Properties of com.sun.star.sheet.Spreadsheet
IsVisible	`boolean` - Determines if the sheet is visible in the GUI.
PageStyle	Contains the name of the page style of this spreadsheet. See Overall Document Features for details about styles.

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 Spreadsheet 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.

Methods of com.sun.star.sheet.XCellRangeMovement
insertCells()	Inserts a range of empty cells at a specific position. The direction of the insertion is determined by the parameter nMode (type com.sun.star.sheet.CellInsertMode).
removeRange()	Deletes a range of cells from the spreadsheet. The parameter nMode (type com.sun.star.sheet.CellDeleteMode) determines how remaining cells will be moved.
copyRange()	Copies the contents of a cell range to another place in the document.
moveRange()	Moves the contents of a cell range to another place in the document. Deletes all contents of the source range.

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

/** Copies a cell range to another place in the sheet.
    @param xSheet The XSpreadsheet interface of the spreadsheet.
    @param aDestCell The address of the first cell of the destination range.
    @param aSourceRange The source range address.
*/
public void doMovementExample(com.sun.star.sheet.XSpreadsheet xSheet,
        com.sun.star.table.CellAddress aDestCell, com.sun.star.table.CellRangeAddress aSourceRange)
        throws RuntimeException, Exception {
    com.sun.star.sheet.XCellRangeMovement xMovement = (com.sun.star.sheet.XCellRangeMovement)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeMovement.class, xSheet);
    xMovement.copyRange(aDestCell, aSourceRange);
}

Page Breaks

The methods getColumnPageBreaks() and getRowPageBreaks() 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 IsStartOfNewPage 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:

// --- Print automatic column page breaks ---
com.sun.star.sheet.XSheetPageBreak xPageBreak = (com.sun.star.sheet.XSheetPageBreak)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetPageBreak.class, xSheet);
com.sun.star.sheet.TablePageBreakData[] aPageBreakArray = xPageBreak.getColumnPageBreaks();

System.out.print("Automatic column page breaks:");
for (int nIndex = 0; nIndex < aPageBreakArray.length; ++nIndex)
    if (!aPageBreakArray[nIndex].ManualBreak)
        System.out.print( " " + aPageBreakArray[nIndex].Position);
System.out.println();

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.

Properties of com.sun.star.sheet.SheetCellRange
Position Size	The position and size of the cell in 100^th of a millimeter. The position is relative to the first cell of the spreadsheet. Note, that this is not always the first visible cell.
ConditionalFormat ConditionalFormatLocal	Used to access conditional formats. See Conditional Formats for details.
Validation ValidationLocal	Used to access data validation. See Data Validation for details.

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.

Tip:
In a spreadsheet, "A1:B2", "$C$1:$D$2", or "E5" are valid ranges. Even user defined cell names, range names, or database range names can be used.

Additionally, XCellRange contains the method getSpreadsheet() 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, xRange is an existing cell range (a com.sun.star.table.XCellRange interface):

com.sun.star.beans.XPropertySet xPropSet = null;
com.sun.star.table.XCellRange xCellRange = null;

// *** Accessing a CELL RANGE ***

// Accessing a cell range over its position.
xCellRange = xRange.getCellRangeByPosition(2, 0, 3, 1);

// Change properties of the range.
xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(0x8080FF));

// Accessing a cell range over its name.
xCellRange = xRange.getCellRangeByName("C4:D5");

// Change properties of the range.
xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(0xFFFF80));

Merging Cell Ranges into a Single Cell

The cell range interface com.sun.star.util.XMergeable merges and undoes merged cell ranges.

The method merge() merges or undoes merged the whole cell range.
The method getIsMerged() determines if the cell range is completely merged.

// --- Merge cells. ---
com.sun.star.util.XMergeable xMerge = (com.sun.star.util.XMergeable)
    UnoRuntime.queryInterface(com.sun.star.util.XMergeable.class, xCellRange);
xMerge.merge(true);

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 getColumns() returns the interface com.sun.star.table.XTableColumns of the collection of columns.
The method getRows() returns the interface com.sun.star.table.XTableRows of the collection of rows.

// --- Column properties. ---
com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange)
    UnoRuntime.queryInterface(com.sun.star.table.XColumnRowRange.class, xCellRange);
com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns();

Object aColumnObj = xColumns.getByIndex(0);
xPropSet = UnoRuntime.queryInterface(
    com.sun.star.beans.XPropertySet.class, aColumnObj);
xPropSet.setPropertyValue( "Width", Integer.valueOf( 6000 ) );

com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed)
    UnoRuntime.queryInterface(com.sun.star.container.XNamed.class, aColumnObj);
System.out.println("The name of the wide column is " + xNamed.getName() + ".");

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 method getDataArray() returns a 2-dimensional array with the contents of all cells of the range.
The method setDataArray() 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.

The following example uses the cell range xCellRange that has the size of 2 columns and 3 rows.

// --- Cell range data ---
com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCellRange);

Object[][] aValues =
{
    {new Double(1.1), Integer.valueOf(10)},
    {new Double(2.2), ""},
    {new Double(3.3), "Text"}
};

xData.setDataArray(aValues);

Absolute Address

The method getCellRangeAddress() 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.

// --- Get cell range address. ---
com.sun.star.sheet.XCellRangeAddressable xRangeAddr = (com.sun.star.sheet.XCellRangeAddressable)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class, xCellRange);
aRangeAddress = xRangeAddr.getRangeAddress();
System.out.println("Address of this range: Sheet=" + aRangeAddress.Sheet);
System.out.println(
    "Start column=" + aRangeAddress.StartColumn + "; Start row=" + aRangeAddress.StartRow);
System.out.println(
    "End column =" + aRangeAddress.EndColumn + "; End row =" + aRangeAddress.EndRow);

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 fillSeries() 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 fillAuto() 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:

	A	B	E	G
1	1
2	4
3	01/30/2002
4			Text 10
5	Jan			10
6
7	1	2
8	05/28/2002	02/28/2002
9	6	4

Inserting filled series in Java:

public void doSeriesSample(com.sun.star.sheet.XSpreadsheet xSheet) {
    com.sun.star.sheet.XCellSeries xSeries = null;

    // Fill 2 rows linear with end value -> 2nd series is not filled completely
    xSeries = getCellSeries(xSheet, "A1:E2");
    xSeries.fillSeries(
        com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.LINEAR,
        com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9);

    // Add months to a date
    xSeries = getCellSeries(xSheet, "A3:E3");
    xSeries.fillSeries(
        com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.DATE,
        com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1, 0x7FFFFFFF);

    // Fill right to left with a text containing a value
    xSeries = getCellSeries(xSheet, "A4:E4");
    xSeries.fillSeries(
        com.sun.star.sheet.FillDirection.TO_LEFT, com.sun.star.sheet.FillMode.LINEAR,
        com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 10, 0x7FFFFFFF);

    // Fill with an user defined list
    xSeries = getCellSeries(xSheet, "A5:E5");
    xSeries.fillSeries(
        com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.AUTO,
        com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 0x7FFFFFFF);

    // Fill bottom to top with a geometric series
    xSeries = getCellSeries(xSheet, "G1:G5");
    xSeries.fillSeries(
        com.sun.star.sheet.FillDirection.TO_TOP, com.sun.star.sheet.FillMode.GROWTH,
        com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 0x7FFFFFFF);

    // Auto fill
    xSeries = getCellSeries(xSheet, "A7:G9");
    xSeries.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 2);
}

/** Returns the XCellSeries interface of a cell range.
    @param xSheet The spreadsheet containing the cell range.
    @param aRange The address of the cell range.
    @return The XCellSeries interface. */
private com.sun.star.sheet.XCellSeries getCellSeries(
        com.sun.star.sheet.XSpreadsheet xSheet, String aRange) {
    return UnoRuntime.queryInterface(
        com.sun.star.sheet.XCellSeries.class, xSheet.getCellRangeByName(aRange));
}

This example produces the following result:

	A	B	C	D	E	F	G
1	1	3	5	7	9		160
2	4	6	8				80
3	01/30/2002	02/28/2002	03/30/2002	04/30/2002	05/30/2002		40
4	Text 50	Text 40	Text 30	Text 20	Text 10		20
5	Jan	Feb	Mar	Apr	May		10
6
7	1	2	3	4	5	6	7
8	05/28/2002	02/28/2002	11/28/2001	08/28/2001	05/28/2001	02/28/2001	11/28/2000
9	6	4	2	0	-2	-4	-6

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 method computeFunction() returns the result of the calculation. The constants com.sun.star.sheet.GeneralFunction specify the calculation method.
The method clearContents() clears contents of the cells used. The parameter describes the contents to clear, using the constants of com.sun.star.sheet.CellFlags.

The following code shows how to compute the average of a cell range and clear the cell contents:

// --- Sheet operation. ---
// Compute a function
com.sun.star.sheet.XSheetOperation xSheetOp = (com.sun.star.sheet.XSheetOperation)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetOperation.class, xCellRange);

double fResult = xSheetOp.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE);
System.out.println("Average value of the data table A10:C30: " + fResult);

// Clear cell contents
xSheetOp.clearContents(
    com.sun.star.sheet.CellFlags.ANNOTATION | com.sun.star.sheet.CellFlags.OBJECTS);

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 setTableOperation() 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 2⁴. Below there are three trigonometrical functions calculated based on a series of values, for example, cell C11 contains the result of cos(0.2).

	A	B	C	D	E	F
1	=A2^B1	1	2	3	4	5
2	1
3	2
4	3
5	4
6	5
7
8		=SIN(A8)	=COS(A8)	=TAN(A8)
9	0
10	0.1
11	0.2
12	0.3
13	0.4

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.

public void InsertMultipleOperation(com.sun.star.sheet.XSpreadsheet xSheet)
        throws RuntimeException, Exception {
    // --- Two independent value series ---
    com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress(xSheet, "A1");
    com.sun.star.table.CellAddress aColCell = createCellAddress(xSheet, "A2");
    com.sun.star.table.CellAddress aRowCell = createCellAddress(xSheet, "B1");

    com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A1:F6");
    com.sun.star.sheet.XMultipleOperation xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell);

    // --- A value series, a formula series ---
    aFormulaRange = createCellRangeAddress(xSheet, "B8:D8");
    aColCell = createCellAddress(xSheet, "A8");
    // Row cell not needed

    xCellRange = xSheet.getCellRangeByName("A9:D13");
    xMultOp = (com.sun.star.sheet.XMultipleOperation)
        UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
    xMultOp.setTableOperation(
        aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell);
}

/** Creates a com.sun.star.table.CellAddress and initializes it
    with the given range.
    @param xSheet The XSpreadsheet interface of the spreadsheet.
    @param aCell The address of the cell (or a named cell).
*/
public com.sun.star.table.CellAddress createCellAddress(
        com.sun.star.sheet.XSpreadsheet xSheet,
        String aCell ) throws RuntimeException, Exception {
    com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class,
            xSheet.getCellRangeByName(aCell).getCellByPosition(0, 0));
    return xAddr.getCellAddress();
}

/** Creates a com.sun.star.table.CellRangeAddress and initializes
    it with the given range.
    @param xSheet The XSpreadsheet interface of the spreadsheet.
    @param aRange The address of the cell range (or a named range).
*/
public com.sun.star.table.CellRangeAddress createCellRangeAddress(
        com.sun.star.sheet.XSpreadsheet xSheet, String aRange) {
    com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class,
            xSheet.getCellRangeByName(aRange));
    return xAddr.getRangeAddress();
}

Handling Array Formulas

The interface com.sun.star.sheet.XArrayFormulaRange handles array formulas.

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

// --- Array formulas ---
com.sun.star.sheet.XArrayFormulaRange xArrayFormula = (com.sun.star.sheet.XArrayFormulaRange)
    UnoRuntime.queryInterface(com.sun.star.sheet.XArrayFormulaRange.class, xCellRange);
// Insert a 3x3 unit matrix.
xArrayFormula.setArrayFormula("=A10:C12");
System.out.println("Array formula is: " + xArrayFormula.getArrayFormula());

Warning:
Due to a bug, this interface does not work correctly in the current implementation. The getArrayFormula() method returns the translated function names, but not the English names. The setArrayFormula() method accepts English names without preceding equal sign and without curly braces. This is inconsistent to the methods setFormula() and getFormula() of the interface com.sun.star.table.XCell. The latter can be used in order to read the English array formula from one of the used cells.

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.

Properties of com.sun.star.sheet.SheetCell
Position Size	The position and size of the cell in 100^th of a millimeter. The position is relative to the first cell of the spreadsheet. Note that this is not always the first visible cell.
FormulaLocal	Used to query or set a formula using function names of the current language.
FormulaResultType	The type of the result. It is a constant from the set com.sun.star.sheet.FormulaResult.
ConditionalFormat ConditionalFormatLocal	Used to access conditional formats. See Conditional Formats for details.
Validation ValidationLocal	Used to access data validation. See Data Validation for details.

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:

void setValue( [in] double nValue)
double getValue()
void setFormula( [in] string aFormula)
string getFormula()
com::sun::star::table::CellContentType getType()
long getError()

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.

Tip:
It is possible to set simple strings or even values with special number formats. In this case, the formula string consists only of a string constant or of the number as it would be entered in the table (for instance date, time, or currency values).

The method getType() 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 xRange is an existing cell range (a com.sun.star.table.XCellRange interface, described in Cell Ranges). The method getCellByPosition() is provided by this interface.

com.sun.star.beans.XPropertySet xPropSet = null;
com.sun.star.table.XCell xCell = null;

// *** Access and modify a VALUE CELL ***
xCell = xRange.getCellByPosition(0, 0);
// Set cell value.
xCell.setValue(1234);

// Get cell value.
double nDblValue = xCell.getValue() * 2;
xRange.getCellByPosition(0, 1).setValue(nDblValue);

// *** Create a FORMULA CELL and query error type ***
xCell = xRange.getCellByPosition(0, 2);
// Set formula string.
xCell.setFormula("=1/0");

// Get error type.
boolean bValid = (xCell.getError() == 0);
// Get formula string.
String aText = "The formula " + xCell.getFormula() + " is ";
aText += bValid ? "valid." : "erroneous.";

// *** Insert a TEXT CELL using the XText interface ***
xCell = xRange.getCellByPosition( 0, 3 );
com.sun.star.text.XText xCellText = (com.sun.star.text.XText)
    UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell );
com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
xCellText.insertString( xTextCursor, aText, false );

// *** Change cell properties ***
int nValue = bValid ? 0x00FF00 : 0xFF4040;
xPropSet = UnoRuntime.queryInterface(
    com.sun.star.beans.XPropertySet.class, xCell);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(nValue));

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.

NumberFormat	Mapping to double
DATE	1 = one day; day 1 is 1899-12-31
TIME	Fraction of a 24h day (1 = 24 hours)
PERCENT	1 = 100%
CURRENCY	1 = $1, £1 etc
LOGICAL	FALSE if zero, TRUE otherwise

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.

// --- Insert two text paragraphs into the cell. ---
com.sun.star.text.XText xText = (com.sun.star.text.XText)
    UnoRuntime.queryInterface(com.sun.star.text.XText.class, xCell);
com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor();

xText.insertString(xTextCursor, "Text in first line.", false);
xText.insertControlCharacter(xTextCursor,
    com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false);
xText.insertString(xTextCursor, "Some more text.", false);

// --- Query the separate paragraphs. ---
String aText;
com.sun.star.container.XEnumerationAccess xParaEA =
    UnoRuntime.queryInterface(
        com.sun.star.container.XEnumerationAccess.class, xCell);
com.sun.star.container.XEnumeration xParaEnum = xParaEA.createEnumeration();

// Go through the paragraphs
while (xParaEnum.hasMoreElements()) {
    Object aPortionObj = xParaEnum.nextElement();
    com.sun.star.container.XEnumerationAccess xPortionEA =
        UnoRuntime.queryInterface(
            com.sun.star.container.XEnumerationAccess.class, aPortionObj);
    com.sun.star.container.XEnumeration xPortionEnum = xPortionEA.createEnumeration();
    aText = "";

    // Go through all text portions of a paragraph and construct string.
    while (xPortionEnum.hasMoreElements()) {
        com.sun.star.text.XTextRange xRange =
            (com.sun.star.text.XTextRange) xPortionEnum.nextElement();
        aText += xRange.getString();
    }
    System.out.println("Paragraph text: " + aText);
}

The SheetCell 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.

Note:
Currently, the only possible text field in Calc cells is the hyperlink field com.sun.star.text.textfield.URL.

Absolute Address

The method getCellAddress() 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.

// --- Get cell address. ---
com.sun.star.sheet.XCellAddressable xCellAddr = (com.sun.star.sheet.XCellAddressable)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class, xCell);
com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress();

String aText = "Address of this cell: Column=" + aAddress.Column;
aText += "; Row=" + aAddress.Row;
aText += "; Sheet=" + aAddress.Sheet;
System.out.println(aText);

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.

Properties of SheetCellRanges

Properties of com.sun.star.sheet.SheetCellRanges
ConditionalFormat ConditionalFormatLocal	Used to access conditional formats. See Conditional Formats for details.
Validation ValidationLocal	Used to access data validation. See Data Validation for details.

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 getRangeAddressesAsString() returns the string representation of all cell ranges.
The method getRangeAddresses() 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 addRangeAddress() and addRangeAddresses() insert one or more ranges into the collection. If the boolean parameter bMergeRanges is set to true, the methods try to merge the new range(s) with the ranges of the collection.
The methods removeRangeAddress() and removeRangeAddresses() 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 getCells() 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.

Cell collections

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

/** All samples regarding cell range collections. */
public void doCellRangesSamples(com.sun.star.sheet.XSpreadsheetDocument xDocument)
        throws RuntimeException, Exception {

    // Create a new cell range container
    com.sun.star.lang.XMultiServiceFactory xDocFactory =
        UnoRuntime.queryInterface(
            com.sun.star.lang.XMultiServiceFactory.class, xDocument);
    com.sun.star.sheet.XSheetCellRangeContainer xRangeCont =
        UnoRuntime.queryInterface(
            com.sun.star.sheet.XSheetCellRangeContainer.class,
            xDocFactory.createInstance("com.sun.star.sheet.SheetCellRanges"));

    // Insert ranges
    insertRange(xRangeCont, 0, 0, 0, 0, 0, false); // A1:A1
    insertRange(xRangeCont, 0, 0, 1, 0, 2, true); // A2:A3
    insertRange(xRangeCont, 0, 1, 0, 1, 2, false); // B1:B3

    // Query the list of filled cells
    System.out.print("All filled cells: ");
    com.sun.star.container.XEnumerationAccess xCellsEA = xRangeCont.getCells();
    com.sun.star.container.XEnumeration xEnum = xCellsEA.createEnumeration();
    while (xEnum.hasMoreElements()) {
        Object aCellObj = xEnum.nextElement();
        com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
            UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class, aCellObj);
        com.sun.star.table.CellAddress aAddr = xAddr.getCellAddress();
        System.out.print(getCellAddressString(aAddr.Column, aAddr.Row) + " ");
    }
    System.out.println();
}

/** Inserts a cell range address into a cell range container and prints a message.
    @param xContainer The com.sun.star.sheet.XSheetCellRangeContainer interface of the container.
    @param nSheet Index of sheet of the range.
    @param nStartCol Index of first column of the range.
    @param nStartRow Index of first row of the range.
    @param nEndCol Index of last column of the range.
    @param nEndRow Index of last row of the range.
    @param bMerge Determines whether the new range should be merged with the existing ranges.
*/
private void insertRange(
        com.sun.star.sheet.XSheetCellRangeContainer xContainer,
        int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow,
        boolean bMerge) throws RuntimeException, Exception {
    com.sun.star.table.CellRangeAddress aAddress = new com.sun.star.table.CellRangeAddress();
    aAddress.Sheet = (short)nSheet;
    aAddress.StartColumn = nStartCol;
    aAddress.StartRow = nStartRow;
    aAddress.EndColumn = nEndCol;
    aAddress.EndRow = nEndRow;
    xContainer.addRangeAddress(aAddress, bMerge);
    System.out.println(
        "Inserting " + (bMerge ? " with" : "without") + " merge,"
        + " result list: " + xContainer.getRangeAddressesAsString());
}

Columns and Rows

Collection of table columns:

Collection of table columns

Collection of table rows:

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 createEnumeration() 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 TableColumn implements the interface com.sun.star.container.XNamed. It provides the method getName() that returns the name of a column. Changing the name of a column is not supported.

Note:
The interface com.sun.star.container.XIndexAccess returns columns and rows relative to the cell range (index 0 is always the first column or row of the cell range). But the interface com.sun.star.container.XNameAccess returns columns with their real names, regardless of the cell range.

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

com.sun.star.beans.XPropertySet xPropSet = null;

// *** Modifying COLUMNS and ROWS ***
// Get column C by index (interface XIndexAccess).
Object aColumnObj = xColumns.getByIndex(2);
xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aColumnObj);
xPropSet.setPropertyValue("Width", Integer.valueOf(5000));

// Get the name of the column.
com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed)
    UnoRuntime.queryInterface(com.sun.star.container.XNamed.class, aColumnObj);
aText = "The name of this column is " + xNamed.getName() + ".";
xRange.getCellByPosition(2, 2).setFormula(aText);

// Get column D by name (interface XNameAccess).
com.sun.star.container.XNameAccess xColumnsName = (com.sun.star.container.XNameAccess)
    UnoRuntime.queryInterface(com.sun.star.container.XNameAccess.class, xColumns);

aColumnObj = xColumnsName.getByName("D");
xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aColumnObj);
xPropSet.setPropertyValue("IsVisible", Boolean.FALSE);

// Get row 7 by index (interface XIndexAccess)
Object aRowObj = xRows.getByIndex(6);
xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aRowObj);
xPropSet.setPropertyValue("Height", Integer.valueOf(5000));

// Create a cell series with the values 1 ... 7.
for (int nRow = 8; nRow < 15; ++nRow)
    xRange.getCellByPosition( 0, nRow ).setValue( nRow - 7 );
// Insert a row between 1 and 2
xRows.insertByIndex(9, 1);
// Delete the rows with the values 3 and 4.
xRows.removeByIndex(11, 2);

Formatting

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 Is...LineValid specifies the validity of the ...Line 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 ...Line struct. The value false means the cells are formatted differently and the content of the ...Line struct is undefined. When changing the border property, these boolean values determine if the lines are changed to the style contained in the respective ...Line struct.

Character and Paragraph Format

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

Service com.sun.star.style.ParagraphProperties
Service com.sun.star.style.CharacterProperties
Service com.sun.star.style.CharacterPropertiesAsian
Service com.sun.star.style.CharacterPropertiesComplex

The chapter Formatting contains a description of these properties.

This example formats a given cell range xCellRange:

// --- Change cell range properties. ---
com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);

// from com.sun.star.styles.CharacterProperties
xPropSet.setPropertyValue("CharColor", Integer.valueOf(0x003399));
xPropSet.setPropertyValue("CharHeight", new Float(20.0));

// from com.sun.star.styles.ParagraphProperties
xPropSet.setPropertyValue("ParaLeftMargin", Integer.valueOf(500));

// from com.sun.star.table.CellProperties
xPropSet.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(0x99CCFF));

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

// --- Change cell properties. ---
com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCell);

// from styles.CharacterProperties
xPropSet.setPropertyValue("CharColor", Integer.valueOf(0x003399));
xPropSet.setPropertyValue("CharHeight", new Float(20.0));

// from styles.ParagraphProperties
xPropSet.setPropertyValue("ParaLeftMargin", Integer.valueOf(500));

// from table.CellProperties
xPropSet.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(0x99CCFF));

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 method decrementIndent() reduces the indentation of each cell by 1.
The method incrementIndent() enlarges the indentation of each cell by 1.

The following sample shows how to increase the cell indentation by 1.

// --- Change indentation. ---
com.sun.star.util.XIndent xIndent = (com.sun.star.util.XIndent)
    UnoRuntime.queryInterface(com.sun.star.util.XIndent.class, xCellRange);
xIndent.incrementIndent();

Warning:
Due to a bug, this interface does not work in the current implementation. Workaround: Use the paragraph property ParaIndent.

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.

Cell Format 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.

UniqueCellFormatRanges

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	B	C	D	E	F	G
1
2
3

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 getCellRangeAddressString is a helper method that returns the range address as a string.

/** All samples regarding formatted cell ranges. */
public void doFormattedCellRangesSamples(com.sun.star.sheet.XSpreadsheet xSheet)
        throws RuntimeException, Exception {
    // All ranges in one container
    xCellRange = xSheet.getCellRangeByName("A1:G3");
    System.out.println("Service CellFormatRanges:");
    com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp =
        UnoRuntime.queryInterface(
            com.sun.star.sheet.XCellFormatRangesSupplier.class, xCellRange);
    com.sun.star.container.XIndexAccess xRangeIA = xFormatSupp.getCellFormatRanges();
    System.out.println( getCellRangeListString(xRangeIA));

    // Ranges sorted in SheetCellRanges containers
    System.out.println("\nService UniqueCellFormatRanges:");
    com.sun.star.sheet.XUniqueCellFormatRangesSupplier xUniqueFormatSupp =
        UnoRuntime.queryInterface(
            com.sun.star.sheet.XUniqueCellFormatRangesSupplier.class, xCellRange);
    com.sun.star.container.XIndexAccess xRangesIA = xUniqueFormatSupp.getUniqueCellFormatRanges();
    int nCount = xRangesIA.getCount();
    for (int nIndex = 0; nIndex < nCount; ++nIndex) {
        Object aRangesObj = xRangesIA.getByIndex(nIndex);
        xRangeIA = UnoRuntime.queryInterface(
            com.sun.star.container.XIndexAccess.class, aRangesObj);
        System.out.println(
            "Container " + (nIndex + 1) + ": " + getCellRangeListString(xRangeIA));
    }
}

/** Returns a list of addresses of all cell ranges contained in the collection.
    @param xRangesIA The XIndexAccess interface of the collection.
    @return A string containing the cell range address list.
*/
private String getCellRangeListString( com.sun.star.container.XIndexAccess xRangesIA )
        throws RuntimeException, Exception {
    String aStr = "";
    int nCount = xRangesIA.getCount();
    for (int nIndex = 0; nIndex < nCount; ++nIndex) {
        if (nIndex > 0)
            aStr += " ";
        Object aRangeObj = xRangesIA.getByIndex(nIndex);
        com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
            UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCellRange.class, aRangeObj);
        aStr += getCellRangeAddressString(xCellRange, false);
    }
    return aStr;
}

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.

	First column	Second data area column	First data area column	Last Column
First row (header)	0	2	1	3
First data area row	4	6	5	7
Second data area row	8	10	9	11
Last row (footer)	12	14	13	15

TableAutoFormat

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 autoFormat() that applies a table auto format to a cell range. The cell range must have a size of at least 3x3 cells. The boolean properties of the table auto format determine the formatting properties are copied to the cells. The default setting of all the properties is true.

Note:
In the current implementation it is not possible to modify the cell borders of a table auto format (the property TableBorder is missing). Nevertheless, the property IncludeBorder controls whether the borders of default auto formats are applied to the cells.

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.

TableAutoFormats

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.

public void doAutoFormatSample(
            com.sun.star.lang.XMultiServiceFactory xServiceManager,
            com.sun.star.sheet.XSpreadsheetDocument xDocument) throws RuntimeException, Exception {
    // get the global collection of table auto formats, use global service manager
    Object aAutoFormatsObj = xServiceManager.createInstance("com.sun.star.sheet.TableAutoFormats");
    com.sun.star.container.XNameContainer xAutoFormatsNA = (com.sun.star.container.XNameContainer)
        UnoRuntime.queryInterface(com.sun.star.container.XNameContainer.class, aAutoFormatsObj);

    // create a new table auto format and insert into the container
    String aAutoFormatName = "Temp_Example";
    boolean bExistsAlready = xAutoFormatsNA.hasByName(aAutoFormatName);
    Object aAutoFormatObj = null;
    if (bExistsAlready)
        // auto format already exists -> use it
        aAutoFormatObj = xAutoFormatsNA.getByName(aAutoFormatName);
    else {
        // create a new auto format (with document service manager!)
        com.sun.star.lang.XMultiServiceFactory xDocServiceManager =
            UnoRuntime.queryInterface(
                com.sun.star.lang.XMultiServiceFactory.class, xDocument);
        aAutoFormatObj = xDocServiceManager.createInstance("com.sun.star.sheet.TableAutoFormat");
        xAutoFormatsNA.insertByName(aAutoFormatName, aAutoFormatObj);
    }
    // index access to the auto format fields
    com.sun.star.container.XIndexAccess xAutoFormatIA = (com.sun.star.container.XIndexAccess)
        UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, aAutoFormatObj);

    // set properties of all auto format fields
    for (int nRow = 0; nRow < 4; ++nRow) {
        int nRowColor = 0;
        switch (nRow) {
            case 0: nRowColor = 0x999999; break;
            case 1: nRowColor = 0xFFFFCC; break;
            case 2: nRowColor = 0xEEEEEE; break;
            case 3: nRowColor = 0x999999; break;
        }

        for (int nColumn = 0; nColumn < 4; ++nColumn) {
            int nColor = nRowColor;
            if ((nColumn == 0) || (nColumn == 3))
                nColor -= 0x333300;

            // get the auto format field and apply properties
            Object aFieldObj = xAutoFormatIA.getByIndex(4 * nRow + nColumn);
            com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
                UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
            xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(nColor));
        }
    }

    // set the auto format to the second spreadsheet
    com.sun.star.sheet.XSpreadsheets xSheets = xDocument.getSheets();
    com.sun.star.container.XIndexAccess xSheetsIA = (com.sun.star.container.XIndexAccess)
        UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, xSheets);

    com.sun.star.sheet.XSpreadsheet xSheet =
        (com.sun.star.sheet.XSpreadsheet) xSheetsIA.getByIndex(1);

    com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A5:H25");
    com.sun.star.table.XAutoFormattable xAutoForm = (com.sun.star.table.XAutoFormattable)
        UnoRuntime.queryInterface(com.sun.star.table.XAutoFormattable.class, xCellRange);

    xAutoForm.autoFormat(aAutoFormatName);

    // remove the auto format
    if (!bExistsAlready)
        xAutoFormatsNA.removeByName(aAutoFormatName);
}

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.

TableConditionalFormats

A cell or cell range object contains the properties ConditionalFormat and ConditionalFormatLocal. 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 ConditionalFormatLocal property uses function names in the current language.

Note:
After a conditional format is changed, it has to be reinserted into the property set of the cell or cell range.

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 addNew() inserts a new condition. It expects a sequence of com.sun.star.beans.PropertyValue objects. The following properties are supported:
- Operator: A com.sun.star.sheet.ConditionOperator constant describing the operation to perform.
- Formula1 and Formula2: Strings that contain the values or formulas to evaluate. Formula2 is used only if the property Operator contains BETWEEN or NOT_BETWEEN.
- SourcePosition: A com.sun.star.table.CellAddress struct that contains the base address for relative cell references in formulas.
- StyleName: The name of the cell style to apply.
The methods removeByIndex() removes the condition entry at the specified position.
The method clear() 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.

// get the conditional format object of the cell range
com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A1:B10");
com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);
com.sun.star.sheet.XSheetConditionalEntries xEntries =
    (com.sun.star.sheet.XSheetConditionalEntries) xPropSet.getPropertyValue("ConditionalFormat");

// create a condition and apply it to the range
com.sun.star.beans.PropertyValue[] aCondition = new com.sun.star.beans.PropertyValue[3];
aCondition[0] = new com.sun.star.beans.PropertyValue();
aCondition[0].Name = "Operator";
aCondition[0].Value = com.sun.star.sheet.ConditionOperator.GREATER;
aCondition[1] = new com.sun.star.beans.PropertyValue();
aCondition[1].Name = "Formula1";
aCondition[1].Value = "1";
aCondition[2] = new com.sun.star.beans.PropertyValue();
aCondition[2].Name = "StyleName";
aCondition[2].Value = "MyNewCellStyle";
xEntries.addNew(aCondition);
xPropSet.setPropertyValue("ConditionalFormat", xEntries);

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.

Warning:
This design inconsistency may be changed in future versions. The properties remain for compatibility.

The properties allow access to various containers:

NamedRanges: The container with all the named ranges. See Named Ranges.
ColumnLabelRanges and RowLabelRanges: Containers with row labels and column labels. See Label Ranges.
DatabaseRanges: The container with all database ranges. See Database Ranges.
SheetLinks, AreaLinks and DDELinks: 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.

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 createCursor() creates a cursor that spans over the whole spreadsheet.
The method createCursorByRange() creates a cursor that spans over the given cell range.

The SheetCellCursor includes the CellCursor service from the table module:

Table cell cursor

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.

Methods of com.sun.star.table.XCellCursor
gotoStart()	Moves to the first filled cell. This cell may be outside of the current range of the cursor.
gotoEnd()	Moves to the last filled cell. This cell may be outside of the current range of the cursor.
gotoOffset()	Moves the cursor relative to the current position, even if the cursor is a range.
gotoPrevious()	Moves the cursor to the latest available unprotected cell. In most cases,this is the cell to the left of the current cell.
gotoNext()	Moves the cursor to the next available unprotected cell. In most cases,this is the cell to the right of the current cell.

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

// *** Use the cell cursor to add some data below of the filled area ***
// Move to the last filled cell.
xCursor.gotoEnd();
// Move one row down.
xCursor.gotoOffset(0, 1);
xCursor.getCellByPosition(0, 0).setFormula("Beyond of the last filled cell.");

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

The method collapseToCurrentRegion() 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.

	B	C	D	F	G
1
2		1	3	{=C2:D3}	{=C2:D3}
3	Text	2	4	{=C2:D3}	{=C2:D3}
4

The method collapseToCurrentArray() 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 collapseToMergedArea() expands the current cursor range so that all merged cell ranges intersecting the current range fit completely.
The methods expandToEntireColumns() and expandToEntireRows() expand the cursor range so that it contains all cells of the columns or rows of the current range.
The method collapseToSize() 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 gotoOffset() from the interface com.sun.star.table.XCellCursor.

Note:
Some of the methods above have misleading names: collapseToCurrentRegion() and collapseToMergedArea() expand the cursor range,but never shorten it and collapseToCurrentArray() may expand or shorten the cursor range.

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

// --- find the array formula using a cell cursor ---
com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName("F22");
com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCellRange.class, xRange);
com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursorByRange(xCellRange);

xCursor.collapseToCurrentArray();
com.sun.star.sheet.XArrayFormulaRange xArray = (com.sun.star.sheet.XArrayFormulaRange)
    UnoRuntime.queryInterface(com.sun.star.sheet.XArrayFormulaRange.class, xCursor);
System.out.println(
    "Array formula in " + getCellRangeAddressString(xCursor, false)
        + " contains formula " + xArray.getArrayFormula());

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, xSheet is a com.sun.star.sheet.XSpreadsheet interface of a spreadsheet.

// --- Find the used area ---
com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursor();
com.sun.star.sheet.XUsedAreaCursor xUsedCursor = (com.sun.star.sheet.XUsedAreaCursor)
    UnoRuntime.queryInterface(com.sun.star.sheet.XUsedAreaCursor.class, xCursor);
xUsedCursor.gotoStartOfUsedArea(false);
xUsedCursor.gotoEndOfUsedArea(true);
System.out.println("The used area is: " + getCellRangeAddressString(xCursor, true));

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.

Named ranges

The collection of named ranges is accessed using the document's NamedRanges property. A new named range is added by calling the com.sun.star.sheet.XNamedRanges interface's addNewByName() 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 FILTER_CRITERIA bit is set, the named range is offered as a criteria range in the "Advanced Filter" dialog.
- If the PRINT_AREA, COLUMN_HEADER or ROW_HEADER 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 addNewFromTitles() 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 TOP, 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 BOTTOM, the names are taken from the range's last row.
If the value is LEFT, 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 RIGHT, the names are taken from the range's last column.

The removeByName() method is used to remove a named range. The outputList() 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 addNewByName 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.

// insert a named range
com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xDocument);
Object aRangesObj = xDocProp.getPropertyValue("NamedRanges");
com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges)
    UnoRuntime.queryInterface(com.sun.star.sheet.XNamedRanges.class, aRangesObj);
com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress();
aRefPos.Sheet = 0;
aRefPos.Column = 6;
aRefPos.Row = 44;
xNamedRanges.addNewByName("ExampleName", "SUM(G43:G44)", aRefPos, 0);

// use the named range in formulas
xSheet.getCellByPosition(6, 44).setFormula("=ExampleName");
xSheet.getCellByPosition(7, 44).setFormula("=ExampleName");

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.

Label Ranges

The com.sun.star.sheet.LabelRanges service contains the document's column label ranges or row label ranges, depending if the ColumnLabelRanges or RowLabelRanges property was used to get it. The com.sun.star.sheet.XLabelRanges interface's addNew() method is used to add a new label range, specifying the label area and data area. The removeByIndex() 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.

com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByPosition(6, 47, 7, 49);
com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xRange);
Object[][] aValues =
{
    {"Apples", "Oranges"},
    {new Double(5), new Double(7)},
    {new Double(6), new Double(8)}
};
xData.setDataArray(aValues);

// insert a column label range
Object aLabelsObj = xDocProp.getPropertyValue("ColumnLabelRanges");
com.sun.star.sheet.XLabelRanges xLabelRanges = (com.sun.star.sheet.XLabelRanges)
    UnoRuntime.queryInterface(com.sun.star.sheet.XLabelRanges.class, aLabelsObj);
com.sun.star.table.CellRangeAddress aLabelArea = new com.sun.star.table.CellRangeAddress();
aLabelArea.Sheet = 0;
aLabelArea.StartColumn = 6;
aLabelArea.StartRow = 47;
aLabelArea.EndColumn = 7;
aLabelArea.EndRow = 47;
com.sun.star.table.CellRangeAddress aDataArea = new com.sun.star.table.CellRangeAddress();
aDataArea.Sheet = 0;
aDataArea.StartColumn = 6;
aDataArea.StartRow = 48;
aDataArea.EndColumn = 7;
aDataArea.EndRow = 49;
xLabelRanges.addNew(aLabelArea, aDataArea);

// use the label range in formulas
xSheet.getCellByPosition(8, 48).setFormula("=Apples+Oranges");
xSheet.getCellByPosition(8, 49).setFormula("=Apples+Oranges");

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.

Query sheet ranges

Warning:
Due to a bug in the current implementation, both methods queryPrecedents() and queryDependents() of the interface com.sun.star.sheet.XFormulaQuery cause an endless loop in recursive mode, if parameter bRecursive is true.

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.

Methods of com.sun.star.sheet.XCellRangesQuery
queryVisibleCells()	Returns all cells that are not hidden.
queryEmptyCells()	Returns all cells that do not have any content.
queryContentCells()	Returns all cells that have the contents described by the passed parameter. The flags are defined in com.sun.star.sheet.CellFlags.
queryFormulaCells()	Returns all formula cells whose results have a specific type described by the passed parameter. The result flags are defined in com.sun.star.sheet.FormulaResult.
queryColumnDifferences()	Returns all cells of the range that have different contents than the cell in the same column of the specified row. See the example below.
queryRowDifferences()	Returns all cells of the range that have different contents than the cell in the same row of the specified column. See the example below.
queryIntersection()	Returns all cells of the range that are contained in the passed range address.

Example:

	A	B	C
1	1	1	2
2	1	2	2
3	1	2	1
4	1	1	1

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

queryColumnDifferences(): (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.
queryRowDifferences(): (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:

// --- Cell Ranges Query ---
// query addresses of all cells containing text
com.sun.star.sheet.XCellRangesQuery xRangesQuery = (com.sun.star.sheet.XCellRangesQuery)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangesQuery.class, xCellRange);

com.sun.star.sheet.XSheetCellRanges xCellRanges =
    xRangesQuery.queryContentCells((short)com.sun.star.sheet.CellFlags.STRING);
System.out.println("Cells containing text: " + xCellRanges.getRangeAddressesAsString());

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 createReplaceDescriptor() 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 replaceAll() performs a replacement in all cells according to the passed replacedescriptor.

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

// --- Replace text in all cells. ---
com.sun.star.util.XReplaceable xReplace = (com.sun.star.util.XReplaceable)
    UnoRuntime.queryInterface(com.sun.star.util.XReplaceable.class, xCellRange);
com.sun.star.util.XReplaceDescriptor xReplaceDesc = xReplace.createReplaceDescriptor();
xReplaceDesc.setSearchString("cell");
xReplaceDesc.setReplaceString("text");
// property SearchWords searches for whole cells!
xReplaceDesc.setPropertyValue("SearchWords", Boolean.FALSE);
int nCount = xReplace.replaceAll(xReplaceDesc);
System.out.println("Search text replaced " + nCount + " times.");

Note:
The property SearchWords has a different meaning in spreadsheets: If true, only cells containing the whole search text and nothing else is found. If false, cells containing the search string as a substring is found.

Sorting

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 IsSortColumns.

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

The size of the sequence using the MaxSortFieldsCount property.

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

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

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

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

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

SheetSortDescriptor2

To sort the contents of a cell range, the sort() 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 createSortDescriptor() method can be used and modified. If the cell range is a database range that has a stored sort operation, createSortDescriptor() returns a sequence with the options of this sort operation.

The fields that the cell range is sorted by are specified in the SortFields 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 IsAscending member switches between ascending and descending sorting for that field. The boolean IsCaseSensitive specifies whether the case of letters is important when comparing entries. The CollatorLocale 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 CollatorAlgorithm is used to select one of the sorting rules. The com.sun.star.i18n.Collator service is used to find the possible CollatorAlgorithm values for a locale. Currently, it is not possible to have different locales, algorithms and case sensitivity on the different fields.

Note:
The FieldType member, that is used to select textual or numeric sorting in text documents is ignored in the spreadsheet application. In a spreadsheet, a cell always has a known type of text or value, which is used for sorting, with numbers sorted before text cells.

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

// --- sort by second column, ascending ---

// define the fields to sort
com.sun.star.util.SortField[] aSortFields = new com.sun.star.table.TableSortField[1];
aSortFields[0] = new com.sun.star.table.TableSortField();
aSortFields[0].Field = 1;
aSortFields[0].IsAscending = true;
aSortFields[0].IsCaseSensitive = false;

// define the sort descriptor
com.sun.star.beans.PropertyValue[] aSortDesc = new com.sun.star.beans.PropertyValue[2];
aSortDesc[0] = new com.sun.star.beans.PropertyValue();
aSortDesc[0].Name = "SortFields";
aSortDesc[0].Value = aSortFields;
aSortDesc[1] = new com.sun.star.beans.PropertyValue();
aSortDesc[1].Name = "ContainsHeader";
aSortDesc[1].Value = Boolean.TRUE;

// perform the sorting
com.sun.star.util.XSortable xSort = (com.sun.star.util.XSortable)
    UnoRuntime.queryInterface(com.sun.star.util.XSortable.class, xRange);
xSort.sort(aSortDesc);

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.

DatabaseRange

Filtering

A com.sun.star.sheet.SheetFilterDescriptor object is created using the createFilterDescriptor() 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 filter() method.

If true is passed as a bEmpty parameter to createFilterDescriptor(), 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.

SheetFilterDescriptor

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:

Connection has the values AND or OR, and specifies how the condition is connected to the previous condition in the sequence. For the first entry, Connection is ignored.
Field is the number of the field that the condition is applied to.
Operator is the type of the condition, such as EQUAL or GREATER
IsNumeric selects a numeric or textual condition.
NumericValue contains the value that is used in the condition if IsNumeric is true.
StringValue contains the text that is used in the condition if IsNumeric 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 CopyOutputData is true, the data that matches the filter criteria is copied to a cell range in the document that starts at the position specified by the OutputPosition 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:

// --- filter for second column >= 1998 ---
com.sun.star.sheet.XSheetFilterable xFilter = (com.sun.star.sheet.XSheetFilterable)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetFilterable.class, xRange);
com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc =
    xFilter.createFilterDescriptor(true);
com.sun.star.sheet.TableFilterField[] aFilterFields =
    new com.sun.star.sheet.TableFilterField[1];
aFilterFields[0] = new com.sun.star.sheet.TableFilterField();
aFilterFields[0].Field = 1;
aFilterFields[0].IsNumeric = true;
aFilterFields[0].Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL;
aFilterFields[0].NumericValue = 1998;
xFilterDesc.setFilterFields(aFilterFields);
com.sun.star.beans.XPropertySet xFilterProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xFilterDesc);
xFilterProp.setPropertyValue("ContainsHeader", Boolean.TRUE);
xFilter.filter(xFilterDesc);

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 createFilterDescriptorByObject() call.

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

// --- do the same filter as above, using criteria from a cell range ---
com.sun.star.table.XCellRange xCritRange = xSheet.getCellRangeByName("B27:B28");
com.sun.star.sheet.XCellRangeData xCritData = (com.sun.star.sheet.XCellRangeData)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCritRange);
Object[][] aCritValues = {{"Year"}, {">= 1998"}};
xCritData.setDataArray(aCritValues);
com.sun.star.sheet.XSheetFilterableEx xCriteria = (com.sun.star.sheet.XSheetFilterableEx)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetFilterableEx.class, xCritRange);
xFilterDesc = xCriteria.createFilterDescriptorByObject(xFilter);
if (xFilterDesc != null)
    xFilter.filter(xFilterDesc);

Subtotals

A com.sun.star.sheet.SubTotalDescriptor object is created using the createSubTotalDescriptor() 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 applySubTotals() method.

The bEmpty parameter to the createSubTotalDescriptor() method works in the same manner as the parameter to the createFilterDescriptor() method described in the filtering section. If the bReplace parameter to the applySubTotals() method is true, existing subtotal rows are deleted before inserting new ones.

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

SubtotalDescriptor

New fields are added to the subtotal descriptor using the com.sun.star.sheet.XSubTotalDescriptor interface's addNew() method. The nGroupColumn parameter selects the column by which values are grouped. The subtotals are inserted at changes of the column's values. The aSubTotalColumns 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:

// --- insert subtotals ---
com.sun.star.sheet.XSubTotalCalculatable xSub = (com.sun.star.sheet.XSubTotalCalculatable)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSubTotalCalculatable.class, xRange);
com.sun.star.sheet.XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true);
com.sun.star.sheet.SubTotalColumn[] aColumns = new com.sun.star.sheet.SubTotalColumn[1];
// calculate sum of third column
aColumns[0] = new com.sun.star.sheet.SubTotalColumn();
aColumns[0].Column = 2;
aColumns[0].Function = com.sun.star.sheet.GeneralFunction.SUM;
// group by first column
xSubDesc.addNew(aColumns, 0);
xSub.applySubTotals(xSubDesc, true);

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 doImport 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 createImportDescriptor() method can be used and modified. The createImportDescriptor() method returns a description of the previously imported data if the cell range is a database range with stored import settings and the bEmpty parameter is false.

DatabaseImportDescriptor

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

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

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

// --- import from database ---
com.sun.star.beans.PropertyValue[] aImportDesc = new com.sun.star.beans.PropertyValue[3];
aImportDesc[0] = new com.sun.star.beans.PropertyValue();
aImportDesc[0].Name = "DatabaseName";
aImportDesc[0].Value = aDatabase;
aImportDesc[1] = new com.sun.star.beans.PropertyValue();
aImportDesc[1].Name = "SourceType";
aImportDesc[1].Value = com.sun.star.sheet.DataImportMode.TABLE;
aImportDesc[2] = new com.sun.star.beans.PropertyValue();
aImportDesc[2].Name = "SourceObject";
aImportDesc[2].Value = aTableName;
com.sun.star.table.XCellRange xImportRange = xSheet.getCellRangeByName("B33:B33");
com.sun.star.util.XImportable xImport = ( com.sun.star.util.XImportable )
    UnoRuntime.queryInterface(com.sun.star.util.XImportable.class, xImportRange);
xImport.doImport(aImportDesc);

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 DatabaseRanges 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 addNewByName() 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 removeByName() 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 getFilterDescriptor() and getSubTotalDescriptor() 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 IsUserDefined property set to false 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 IsUserDefined 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.

// use the temporary database range to find the imported data's size
com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, getDocument());
Object aRangesObj = xDocProp.getPropertyValue("DatabaseRanges");
com.sun.star.container.XNameAccess xRanges = (com.sun.star.container.XNameAccess)
    UnoRuntime.queryInterface(com.sun.star.container.XNameAccess.class, aRangesObj);
String[] aNames = xRanges.getElementNames();
for (int i=0; i<aNames.length; i++) {
    Object aRangeObj = xRanges.getByName(aNames[i] );
    com.sun.star.beans.XPropertySet xRangeProp = (com.sun.star.beans.XPropertySet)
        UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aRangeObj);
    boolean bUser = ((Boolean) xRangeProp.getPropertyValue("IsUserDefined")).booleanValue();
    if (!bUser) {
        // this is the temporary database range - get the cell range and format it
        com.sun.star.sheet.XCellRangeReferrer xRef = (com.sun.star.sheet.XCellRangeReferrer)
            UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj);
        com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells();
        com.sun.star.beans.XPropertySet xResultProp = (com.sun.star.beans.XPropertySet)
            UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xResultRange);
        xResultProp.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
        xResultProp.setPropertyValue("CellBackColor", Integer.valueOf(0xFFFFCC));
    }
}

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.

SheetLinks

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 link() 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 NORMAL, all cells from the source sheet are copied, including formulas.
- If the mode is VALUE, 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 getLinkMode(), setLinkMode(), getLinkUrl(), setLinkUrl(), getLinkSheetName() and setLinkSheetName() methods. Setting the mode to NONE 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.

Note:
External references in cell formulas are implemented using hidden linked sheets that show as sheet link objects.

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.

CellAreaLinks

To insert an area link, the com.sun.star.sheet.XAreaLinks interface's insertAtPosition() 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 removeByIndex() 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 RefreshDelay 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.

DDELink

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 DataPilot tables in a spreadsheet.

The method getDataPilotTables() 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.

DataPilotTables

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 getDataPilotTables() method.

Note:
Only DataPilot tables that are based on cell data are supported by these services. DataPilot tables created directly from external data sources or using the com.sun.star.sheet.DataPilotSource service cannot be created or modified this way.

Creating a New DataPilot Table

The first step to creating a new DataPilot table is to create a new com.sun.star.sheet.DataPilotDescriptor object by calling the com.sun.star.sheet.XDataPilotTables interface's createDataPilotDescriptor() method. The descriptor is then used to describe the DataPilot table's layout and options, and passed to the insertNewByName() method of XDataPilotTables. The other parameters for insertNewByName() 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 setSourceRange() method. It is a com.sun.star.table.CellRangeAddress struct.
The individual fields are handled using the getDataPilotFields(), getColumnFields(), getRowFields(), getPageFields(), getDataFields() and getHiddenFields() methods. The details are discussed below.
The setTag() method sets an additional string that is stored with the DataPilot table, but does not influence its results.
The getFilterDescriptor() 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 Orientation property that controls where in the DataPilot table the field is used. The com.sun.star.sheet.DataPilotFieldOrientation enum contains the possible orientations:

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

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

The getDataPilotFields() 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 DATA orientation. Each source column appears only once, even if it is used with several orientations or functions.

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

Note:
Note: Page fields and the PAGE orientation are not supported by the current implementation. Setting a field's orientation to PAGE has the same effect as using HIDDEN. The getPageFields() method always returns an empty collection.

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 getDataPilotFields() 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.

// --- Create a new DataPilot table ---
com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = (com.sun.star.sheet.XDataPilotTablesSupplier)
    UnoRuntime.queryInterface(com.sun.star.sheet.XDataPilotTablesSupplier.class, xSheet);
com.sun.star.sheet.XDataPilotTables xDPTables = xDPSupp.getDataPilotTables();
com.sun.star.sheet.XDataPilotDescriptor xDPDesc = xDPTables.createDataPilotDescriptor();

// set source range (use data range from CellRange test)
com.sun.star.table.CellRangeAddress aSourceAddress = createCellRangeAddress(xSheet, "A10:C30");
xDPDesc.setSourceRange(aSourceAddress);

// settings for fields
com.sun.star.container.XIndexAccess xFields = xDPDesc.getDataPilotFields();
Object aFieldObj;
com.sun.star.beans.XPropertySet xFieldProp;

// use first column as column field
aFieldObj = xFields.getByIndex(0);
xFieldProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.COLUMN);

// use second column as row field
aFieldObj = xFields.getByIndex(1);
xFieldProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.ROW);

// use third column as data field, calculating the sum
aFieldObj = xFields.getByIndex(2);
xFieldProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA);
xFieldProp.setPropertyValue("Function", com.sun.star.sheet.GeneralFunction.SUM);

// select output position
com.sun.star.table.CellAddress aDestAddress = createCellAddress(xSheet, "A40");
xDPTables.insertNewByName("DataPilotExample", aDestAddress, xDPDesc);

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 getOutputRange() method that is used to find which range on the spreadsheet the table occupies, and a refresh() 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.

// --- Modify the DataPilot table ---
Object aDPTableObj = xDPTables.getByName("DataPilotExample");
xDPDesc = (com.sun.star.sheet.XDataPilotDescriptor)
    UnoRuntime.queryInterface(com.sun.star.sheet.XDataPilotDescriptor.class, aDPTableObj);
xFields = xDPDesc.getDataPilotFields();

// add a second data field from the third column, calculating the average
aFieldObj = xFields.getByIndex(2);
xFieldProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA);
xFieldProp.setPropertyValue("Function", com.sun.star.sheet.GeneralFunction.AVERAGE);

Note:
Note how the field object for the third column is taken from the collection returned by getDataPilotFields() to create a second data field. If the field object was taken from the collection returned by getDataFields(), only the existing data field's function would be changed by the setPropertyValue() calls to that object.

Removing a DataPilot Table

To remove a DataPilot table from a spreadsheet, call the com.sun.star.sheet.XDataPilotTables interface's removeByName() 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.

DataPilotSource

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:

ones	tens	hundreds
ones	tens	0	1	2
0	0	0	100	200
	1	10	110	210
	2	20	120	220
1	0	1	101	201
	1	11	111	211
	2	21	121	221
2	0	2	102	202
	1	12	112	212
	2	22	122	222

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

class ExampleSettings
{
    public static final int nDimensionCount = 6;
    public static final int nValueDimension = 4;
    public static final int nDataDimension = 5;
    public static final String [] aDimensionNames = {
        "ones", "tens", "hundreds", "thousands", "value", "" };

    public static final String getMemberName( int nMember )
    {
        return String.valueOf( nMember );
    }

    public int nMemberCount = 3;
    public java.util.List<Integer> aColDimensions = new java.util.ArrayList<Integer>();
    public java.util.List<Integer> aRowDimensions = new java.util.ArrayList<Integer>();
}

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

The application gets the list of available dimensions (fields) from the component.
The application applies the user-specified settings to the component.
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 initialize() 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:

private ExampleSettings aSettings = new ExampleSettings();

public void initialize(Object[] aArguments) {
    // If the first argument (Source) is a number between 2 and 10,
    // use it as member count, otherwise keep the default value.
    if (aArguments.length >= 1) {
        String aSource = (String) aArguments[0];
        if (aSource != null) {
            try {
                int nValue = Integer.parseInt(aSource);
                if (nValue >= 2 && nValue <= 10)
                    aSettings.nMemberCount = nValue;
            } catch (NumberFormatException e) {
            }
        }
    }
}

The source object's com.sun.star.beans.XPropertySet interface is used to apply two settings: The ColumnGrand and RowGrand 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 getResults() 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:

HASDATA 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.
SUBTOTAL marks a subtotal value that is formatted differently in the DataPilot table output.
ERROR is set if the result at the entry's position is an error.

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

public com.sun.star.sheet.DataResult[][] getResults()
{
    int[] nDigits = new int[ExampleSettings.nDimensionCount];
    int nValue = 1;
    for (int i=0; i<ExampleSettings.nDimensionCount; i++)
    {
        nDigits[i] = nValue;
        nValue *= 10;
    }

    int nMemberCount = aSettings.nMemberCount;
    int nRowDimCount = aSettings.aRowDimensions.size();
    int nColDimCount = aSettings.aColDimensions.size();

    int nRows = 1;
    for (int i=0; i<nRowDimCount; i++)
        nRows *= nMemberCount;
    int nColumns = 1;
    for (int i=0; i<nColDimCount; i++)
        nColumns *= nMemberCount;

    com.sun.star.sheet.DataResult[][] aResults = new com.sun.star.sheet.DataResult[nRows][];
    for (int nRow=0; nRow<nRows; nRow++)
    {
        int nRowVal = nRow;
        int nRowResult = 0;
        for (int nRowDim=0; nRowDim<nRowDimCount; nRowDim++)
        {
            int nDim = aSettings.aRowDimensions.get(nRowDimCount-nRowDim-1).intValue();
            nRowResult += ( nRowVal % nMemberCount ) * nDigits[nDim];
            nRowVal /= nMemberCount;
        }

        aResults[nRow] = new com.sun.star.sheet.DataResult[nColumns];
        for (int nCol=0; nCol<nColumns; nCol++)
        {
            int nColVal = nCol;
            int nResult = nRowResult;
            for (int nColDim=0; nColDim<nColDimCount; nColDim++)
            {
                int nDim = aSettings.aColDimensions.get(nColDimCount-nColDim-1).intValue();
                nResult += ( nColVal % nMemberCount ) * nDigits[nDim];
                nColVal /= nMemberCount;
            }

            aResults[nRow][nCol] = new com.sun.star.sheet.DataResult();
            aResults[nRow][nCol].Flags = com.sun.star.sheet.DataResultFlags.HASDATA;
            aResults[nRow][nCol].Value = nResult;
        }
    }
    return aResults;
}

The com.sun.star.util.XRefreshable interface contains a refresh() method that tells the component to discard cached results and recalculate the results the next time they are needed. The addRefreshListener() and removeRefreshListener() methods are not used by LibreOffice API Calc. The refresh() 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.

private ExampleSettings aSettings;
private int nDimension;
private com.sun.star.sheet.DataPilotFieldOrientation eOrientation;

public ExampleDimension(ExampleSettings aSet, int nDim) {
    aSettings = aSet;
    nDimension = nDim;
    eOrientation = (nDim == ExampleSettings.nValueDimension) ?
        com.sun.star.sheet.DataPilotFieldOrientation.DATA :
        com.sun.star.sheet.DataPilotFieldOrientation.HIDDEN;
}

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:

Original (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.
IsDataLayoutDimension (read-only) must contain true if the dimension is the "data layout" dimension, otherwise false.
Orientation controls how a dimension is used in the DataPilot table. If it contains the com.sun.star.sheet.DataPilotFieldOrientation enum values COLUMN or ROW, the dimension is used as a column or row dimension, respectively. If the value is DATA, the dimension is used as data (measure) dimension. The PAGE designates a page dimension, but is not currently used in LibreOffice API Calc. If the value is HIDDEN, the dimension is not used.
Position 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 Position property reflects that position.
Function specifies the function that is used to aggregate data for a data dimension.
UsedHierarchy selects which of the dimension's hierarchies is used in the DataPilot table. See the section on hierarchies below.
Filter 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 setPropertyValue() method for the dimension only implements the modification of Orientation and Position, using two lists to store the order of column and row dimensions. Page dimensions are not supported in the example.

public void setPropertyValue( String aPropertyName, Object aValue )
                throws com.sun.star.beans.UnknownPropertyException
{
    if ( aPropertyName.equals( "Orientation" ) )
    {
        com.sun.star.sheet.DataPilotFieldOrientation eNewOrient =
                    (com.sun.star.sheet.DataPilotFieldOrientation) aValue;
        if ( nDimension != ExampleSettings.nValueDimension &&
              nDimension != ExampleSettings.nDataDimension &&
              eNewOrient != com.sun.star.sheet.DataPilotFieldOrientation.DATA )
        {
            // remove from list for old orientation and add for new one
            Integer aDimInt = Integer.valueOf(nDimension);
            if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN )
                aSettings.aColDimensions.remove( aDimInt );
            else if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW )
                aSettings.aRowDimensions.remove( aDimInt );
            if ( eNewOrient == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN )
                aSettings.aColDimensions.add( aDimInt );
            else if ( eNewOrient == com.sun.star.sheet.DataPilotFieldOrientation.ROW )
                aSettings.aRowDimensions.add( aDimInt );

            // change orientation
            eOrientation = eNewOrient;
        }
    }
    else if ( aPropertyName.equals( "Position" ) )
    {
        int nNewPos = ((Integer) aValue).intValue();
        Integer aDimInt = Integer.valueOf(nDimension);
        if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN )
        {
            aSettings.aColDimensions.remove( aDimInt );
            aSettings.aColDimensions.add( nNewPos, aDimInt );
        }
        else if ( eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW )
        {
            aSettings.aRowDimensions.remove( aDimInt );
            aSettings.aRowDimensions.add( nNewPos, aDimInt );
        }
    }
    else if ( aPropertyName.equals( "Function" ) || aPropertyName.equals( "UsedHierarchy" ) ||
              aPropertyName.equals( "Filter" ) )
    {
        // ignored
    }
    else
        throw new com.sun.star.beans.UnknownPropertyException();
}

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

public Object getPropertyValue(String aPropertyName)
        throws com.sun.star.beans.UnknownPropertyException {
    if (aPropertyName.equals("Original"))
        return null;
    else if (aPropertyName.equals( "IsDataLayoutDimension"))
        return new Boolean(nDimension == ExampleSettings.nDataDimension);
    else if (aPropertyName.equals("Orientation"))
        return eOrientation;
    else if (aPropertyName.equals("Position")) {
        int nPosition;
        if (eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.COLUMN)
            nPosition = aSettings.aColDimensions.indexOf(Integer.valueOf(nDimension));
        else if (eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW)
            nPosition = aSettings.aRowDimensions.indexOf(Integer.valueOf(nDimension));
        else
            nPosition = nDimension;
        return Integer.valueOf(nPosition);
    }
    else if (aPropertyName.equals("Function"))
        return com.sun.star.sheet.GeneralFunction.SUM;
    else if (aPropertyName.equals("UsedHierarchy"))
        return Integer.valueOf(0);
    else if (aPropertyName.equals("Filter"))
        return new com.sun.star.sheet.TableFilterField[0];
    else
        throw new com.sun.star.beans.UnknownPropertyException();
}

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 createClone() method. Each clone is given a new name using the com.sun.star.container.XNamed interface's setName() method, then the different settings are applied to the objects. A dimension object that was created using the createClone() method must return the original object that it was created from in the Original property.

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

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 UsedHierarchy 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 getElementNames() method. LibreOffice API Calc currently has no user interface to select a hierarchy, so it uses the hierarchy that the initial value of the UsedHierarchy 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 SubTotals 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 AUTO is used to select "automatic" subtotals, determined by the type of the data.
The ShowEmpty 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 getResults() 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 HASMEMBER flag, the level is not shown.

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

Name is the name of the member that is represented by the entry, exactly as returned by the member object's getName() method. It is used to find the member object, for example when the user double-clicks on the cell.
Caption is the string that will be displayed in the cell. It may or may not be the same as Name.
Flags indicates the kind of result the entry represents. It can be a combination of the com.sun.star.sheet.MemberResultFlags constants:
- HASMEMBER indicates there is a member that belongs to this entry.
- SUBTOTAL marks an entry that corresponds to a subtotal column or row. The HASMEMBER should be set.
- CONTINUE 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 HASMEMBER flag
two entries containing only the CONTINUE flag
the same repeated for member names "2" and "3".

The implementation for the example looks similar to this:

private ExampleSettings aSettings;
private int nDimension;

public com.sun.star.sheet.MemberResult[] getResults() {
    int nDimensions = 0;
    int nPosition = aSettings.aColDimensions.indexOf(Integer.valueOf(nDimension));
    if (nPosition >= 0)
        nDimensions = aSettings.aColDimensions.size();
    else {
        nPosition = aSettings.aRowDimensions.indexOf(Integer.valueOf(nDimension));
        if (nPosition >= 0)
            nDimensions = aSettings.aRowDimensions.size();
    }

    if (nPosition < 0)
        return new com.sun.star.sheet.MemberResult[0];

    int nMembers = aSettings.nMemberCount;
    int nRepeat = 1;
    int nFill = 1;
    for (int i=0; i<nDimensions; i++) {
        if (i < nPosition)
            nRepeat *= nMembers;
        else if (i > nPosition)
            nFill *= nMembers;
    }
    int nSize = nRepeat * nMembers * nFill;

    com.sun.star.sheet.MemberResult[] aResults = new com.sun.star.sheet.MemberResult[nSize];
    int nResultPos = 0;
    for (int nOuter=0; nOuter<nRepeat; nOuter++) {
        for (int nMember=0; nMember<nMembers; nMember++) {
            aResults[nResultPos] = new com.sun.star.sheet.MemberResult();
            aResults[nResultPos].Name = ExampleSettings.getMemberName( nMember );
            aResults[nResultPos].Caption = aResults[nResultPos].Name;
            aResults[nResultPos].Flags = com.sun.star.sheet.MemberResultFlags.HASMEMBER;
            ++nResultPos;

            for (int nInner=1; nInner<nFill; nInner++) {
                aResults[nResultPos] = new com.sun.star.sheet.MemberResult();
                aResults[nResultPos].Flags = com.sun.star.sheet.MemberResultFlags.CONTINUE;
                ++nResultPos;
            }
        }
    }
    return aResults;
}

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 IsVisible property is false, the member and its data are hidden. There is currently no user interface to change this property.
The boolean ShowDetails property controls if the results for a member should be detailed in the following level. If a member has this property set to false, 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 protect() and unprotect() to switch the protection on and off. If a wrong password is used to unprotect the spreadsheet, it leads to an exception.
The method isProtected() 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:

Methods of com.sun.star.sheet.XSheetOutline
group()	Creates a new outline group and the method `ungroup()` removes the innermost outline group for a cell range. The parameter `nOrientation` (type com.sun.star.table.TableOrientation) selects the orientation of the outline (columns or rows).
autoOutline()	Inserts outline groups for a cell range depending on formula references.
clearOutline()	Removes all outline groups from the sheet.
hideDetail()	Collapses an outline group.
showDetail()	Reopens an outline group.
showLevel()	Shows the specified number of outline group levels and hides the others.

Detective

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

Methods of com.sun.star.sheet.XSheetAuditing
hideDependents() hidePrecedents()	Hides the last arrows to dependent or precedent cells of a formula cell. Repeated calls of the methods shrink the chains of arrows.
showDependents() showPrecedents()	Adds arrows to the next dependent or precedent cells of a formula cell. Repeated calls of the methods extend the chains of arrows.
showErrors()	Inserts arrows to all cells that cause an error in the specified cell.
showInvalid()	Marks all cells that contain invalid values.
clearArrows()	Removes all auditing arrows from the spreadsheet.

Other Table Operations

Data Validation

Data validation checks if a user entered valid entries.

TableValidation

A cell or cell range object contains the properties Validation and ValidationLocal. 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 ValidationLocal property uses function names in the current language).

Note:
After the validation settings are changed, the validation object is reinserted into the property set of the cell or cell range.

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.
IgnoreBlankCells: 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.
ShowErrorMessage, ErrorTitle, ErrorMessage and ErrorAlertStyle (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 ErrorTitle 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 xSheet is the interface com.sun.star.sheet.XSpreadsheet of a spreadsheet.

// --- Data validation ---
com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A7:C7");
com.sun.star.beans.XPropertySet xCellPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xCellRange);

// validation properties
com.sun.star.beans.XPropertySet xValidPropSet = (com.sun.star.beans.XPropertySet)
    xCellPropSet.getPropertyValue("Validation");
xValidPropSet.setPropertyValue("Type", com.sun.star.sheet.ValidationType.DECIMAL);
xValidPropSet.setPropertyValue("ShowErrorMessage", Boolean.TRUE);
xValidPropSet.setPropertyValue("ErrorMessage", "This is an invalid value!");
xValidPropSet.setPropertyValue("ErrorAlertStyle", com.sun.star.sheet.ValidationAlertStyle.STOP);

// condition
com.sun.star.sheet.XSheetCondition xCondition = (com.sun.star.sheet.XSheetCondition)
    UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCondition.class, xValidPropSet);
xCondition.setOperator(com.sun.star.sheet.ConditionOperator.BETWEEN);
xCondition.setFormula1("0.0");
xCondition.setFormula2("5.0");

// apply on cell range
xCellPropSet.setPropertyValue("Validation", xValidPropSet);

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.

ConsolidationDescriptor

The com.sun.star.sheet.XConsolidatable's method createConsolidationDescriptor() 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:

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

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

Charts

TableCharts

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.

Note:
The service com.sun.star.table.TableChart does not represent the chart document, but the object in the table that contains the chart document. The interface com.sun.star.document.XEmbeddedObjectSupplier provides access to that chart document. For further information, see Charts.

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 xCharts can be a com.sun.star.table.XTableCharts interface of a collection of charts.

// *** Inserting CHARTS ***
String aName = "newChart";
com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle();
aRect.X = 10000;
aRect.Y = 3000;
aRect.Width = aRect.Height = 5000;

com.sun.star.table.CellRangeAddress[] aRanges = new com.sun.star.table.CellRangeAddress[1];
aRanges[0] = new com.sun.star.table.CellRangeAddress();
aRanges[0].Sheet = aRanges[0].StartColumn = aRanges[0].EndColumn = 0;
aRanges[0].StartRow = 0; aRanges[0].EndRow = 9;

// Create the chart.
xCharts.addNewByName(aName, aRect, aRanges, false, false);

// Get the chart by name.
Object aChartObj = xCharts.getByName(aName);
com.sun.star.table.XTableChart xChart = (com.sun.star.table.XTableChart)
    UnoRuntime.queryInterface(com.sun.star.table.XTableChart.class, aChartObj);

// Query the state of row and column headers.
aText = "Chart has column headers: ";
aText += xChart.getHasColumnHeaders() ? "yes" : "no";
System.out.println(aText);
aText = "Chart has row headers: ";
aText += xChart.getHasRowHeaders() ? "yes" : "no";
System.out.println(aText);

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.

Scenarios

The com.sun.star.sheet.XScenariosSupplier's method getScenarios() 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 addNewByName() adds a scenario with the given name that contains the specified cell ranges.
The method removeByName() removes the scenario (the spreadsheet) with the given name.

The following method shows how to create a scenario:

/** Inserts a scenario containing one cell range into a sheet and applies the value array.
    @param xSheet The XSpreadsheet interface of the spreadsheet.
    @param aRange The range address for the scenario.
    @param aValueArray The array of cell contents.
    @param aScenarioName The name of the new scenario.
    @param aScenarioComment The user comment for the scenario.
*/
public void insertScenario(
        com.sun.star.sheet.XSpreadsheet xSheet,
        String aRange,
        Object[][] aValueArray,
        String aScenarioName,
        String aScenarioComment ) throws RuntimeException, Exception {
    // get the cell range with the given address
    com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName(aRange);

    // create the range address sequence
    com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class, xCellRange);
    com.sun.star.table.CellRangeAddress[] aRangesSeq = new com.sun.star.table.CellRangeAddress[1];
    aRangesSeq[0] = xAddr.getRangeAddress();

    // create the scenario
    com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
        UnoRuntime.queryInterface(com.sun.star.sheet.XScenariosSupplier.class, xSheet);
    com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();
    xScenarios.addNewByName(aScenarioName, aRangesSeq, aScenarioComment);

    // insert the values into the range
    com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData)
        UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeData.class, xCellRange);
    xData.setDataArray(aValueArray);
}

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

The method getIsScenario() tests if this spreadsheet is used to store scenario data.
The methods getScenarioComment() and setScenarioComment() retrieves and sets the user comment for this scenario.
The method addRanges() adds new cell ranges to the scenario.
The method apply() 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:

/** Activates a scenario.
    @param xSheet The XSpreadsheet interface of the spreadsheet.
    @param aScenarioName The name of the scenario.
*/
public void showScenario( com.sun.star.sheet.XSpreadsheet xSheet,
        String aScenarioName) throws RuntimeException, Exception {
    // get the scenario set
    com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier)
        UnoRuntime.queryInterface(com.sun.star.sheet.XScenariosSupplier.class, xSheet);
    com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios();

    // get the scenario and activate it
    Object aScenarioObj = xScenarios.getByName(aScenarioName);
    com.sun.star.sheet.XScenario xScenario = (com.sun.star.sheet.XScenario)
        UnoRuntime.queryInterface(com.sun.star.sheet.XScenario.class, aScenarioObj);
    xScenario.apply();
}

Overall Document Features

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.

StyleFamilies

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

Note:
A new style is inserted into the family container,then it is possible to set any properties.

Cell Styles

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

CellStyle

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 CellStyle. 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 xDocument is the com.sun.star.sheet.XSpreadsheetDocument interface of a spreadsheet document.

Create new style with gray background - Java

// get the cell style container
com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier =
    UnoRuntime.queryInterface(
        com.sun.star.style.XStyleFamiliesSupplier.class, xDocument);
com.sun.star.container.XNameAccess xFamiliesNA = xFamiliesSupplier.getStyleFamilies();
Object aCellStylesObj = xFamiliesNA.getByName("CellStyles");
com.sun.star.container.XNameContainer xCellStylesNA = (com.sun.star.container.XNameContainer)
    UnoRuntime.queryInterface(com.sun.star.container.XNameContainer.class, aCellStylesObj);

// create a new cell style
com.sun.star.lang.XMultiServiceFactory xServiceManager = (com.sun.star.lang.XMultiServiceFactory)
    UnoRuntime.queryInterface(com.sun.star.lang.XMultiServiceFactory.class, xDocument);
Object aCellStyle = xServiceManager.createInstance("com.sun.star.style.CellStyle");
xCellStylesNA.insertByName("MyNewCellStyle", aCellStyle);

// modify properties of the new style
com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aCellStyle);
xPropSet.setPropertyValue("CellBackColor", Integer.valueOf(0x888888));
xPropSet.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);

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.

TablePageStyle

The properties LeftPageFooterContent, LeftPageHeaderContent, RightPageFooterContent and RightPageHeaderContent 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 getLeftText(), getCenterText() and getRightText() return the interface com.sun.star.text.XText of these text portions.

Note:
After the text of a header or footer is changed, it is reinserted into the property set of the page style.

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.

FunctionAccess

The service can be instantiated through the service manager. The com.sun.star.sheet.XFunctionAccess interface contains only one method, callFunction(). 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 getIncremented function in the example from the add-in section, this would be: "com.sun.star.sheet.addin.ExampleAddIn.getIncremented".

The second parameter to callFunction() 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 ZTEST function, an array of values and a single value.

Calculate a function - Java

// --- Calculate a function ---
Object aFuncInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionAccess");
com.sun.star.sheet.XFunctionAccess xFuncAcc = (com.sun.star.sheet.XFunctionAccess)
    UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionAccess.class, aFuncInst);
// put the data into a two-dimensional array
double[][] aData = {{1.0, 2.0, 3.0}};
// construct the array of function arguments
Object[] aArgs = { aData, new Double( 2.0 ) };
Object aResult = xFuncAcc.callFunction("ZTEST", aArgs);
System.out.println("ZTEST result for data {1,2,3} and value 2 is "
    + ((Double)aResult).doubleValue());

In Calc, an argument of the function may accept a range of cells, e.g =SUM(B12:G55). 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.

Note:
The implementation of com.sun.star.sheet.FunctionAccess uses the same internal structures as a spreadsheet document, therefore it is bound by the same limitations, such as the limit of rows and columns for the function arguments. Version 3.3 of LibreOffice supports 1048576 rows and 1024 columns.

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.

FunctionDescriptions

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 getById() 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.

Note:
All of the strings contained in the com.sun.star.sheet.FunctionDescription service are to be used in user interaction, and therefore translated to the application's UI language. They cannot be used where programmatic function names are expected, for example, the com.sun.star.sheet.FunctionAccess service.

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.

RecentFunctions

The service can be instantiated through the service manager. The com.sun.star.sheet.XRecentFunctions interface's getRecentFunctionIds() method returns a sequence of function identifiers that are used with the com.sun.star.sheet.FunctionDescriptions service. The setRecentFunctionIds() method changes the list. If the parameter to the setRecentFunctionIds() 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 getMaxRecentFunctions() method.

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

RecentFunctions and FunctionDescriptions in Calc - Java

// --- Get the list of recently used functions ---
Object aRecInst = xServiceManager.createInstance("com.sun.star.sheet.RecentFunctions");
com.sun.star.sheet.XRecentFunctions xRecFunc = (com.sun.star.sheet.XRecentFunctions)
    UnoRuntime.queryInterface(com.sun.star.sheet.XRecentFunctions.class, aRecInst);
int[] nRecentIds = xRecFunc.getRecentFunctionIds();

// --- Get the names for these functions ---
Object aDescInst = xServiceManager.createInstance("com.sun.star.sheet.FunctionDescriptions");
com.sun.star.sheet.XFunctionDescriptions xFuncDesc = (com.sun.star.sheet.XFunctionDescriptions)
    UnoRuntime.queryInterface(com.sun.star.sheet.XFunctionDescriptions.class, aDescInst);
System.out.print("Recently used functions: ");
for (int nFunction=0; nFunction<nRecentIds.length; nFunction++) {
    com.sun.star.beans.PropertyValue[] aProperties = xFuncDesc.getById(nRecentIds[nFunction]);
    for (int nProp=0; nProp<aProperties.length; nProp++)
        if (aProperties[nProp].Name.equals("Name"))
            System.out.print(aProperties[nProp].Value + " ");
}
System.out.println();

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.

GlobalSheetSettings

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

Display the list of user-defined sort lists - Java

// --- Get the user defined sort lists ---
Object aSettings = xServiceManager.createInstance("com.sun.star.sheet.GlobalSheetSettings");
com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aSettings );
String[] aEntries = (String[]) xPropSet.getPropertyValue("UserLists");
System.out.println("User defined sort lists:");
for (int i=0; i<aEntries.length; i++)
    System.out.println( aEntries[i] );

Spreadsheet Document Controller

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.

SpreadsheetView

Note:
The page preview does not have an API representation.

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 getActiveSheet() method returns the active sheet's object, the setActiveSheet() method switches to a different sheet. The parameter to setActiveSheet() 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 splitAtPosition() method splits the view at the specified pixel positions. To remove the split, a position of 0 is passed. The getIsWindowSplit() method returns true if the view is split, the getSplitHorizontal() and getSplitVertical() methods return the pixel positions where the view is split. The getSplitColumn() and getSplitRow() 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 freezeAtPosition() method freezes the specified number of columns and rows. This also sets the split positions accordingly. The hasFrozenPanes() 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:

Freeze desired rows and columns - Java

// freeze the first column and first two rows
com.sun.star.sheet.XViewFreezable xFreeze = (com.sun.star.sheet.XViewFreezable)
    UnoRuntime.queryInterface(com.sun.star.sheet.XViewFreezable.class, xController);
xFreeze.freezeAtPosition(1, 2);

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 getFirstVisibleColumn(), getFirstVisibleRow(), setFirstVisibleColumn() and setFirstVisibleRow() methods query and set the start of the exposed area. The getVisibleRange() 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 getVisibleRange() return value.

The com.sun.star.view.XControlAccess interface's getControl() 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:

Assigning cell background - Java

// get the cell range shown in the second pane and assign a cell background to them
com.sun.star.container.XIndexAccess xIndex = (com.sun.star.container.XIndexAccess)
    UnoRuntime.queryInterface(com.sun.star.container.XIndexAccess.class, xController);
Object aPane = xIndex.getByIndex(1);
com.sun.star.sheet.XCellRangeReferrer xRefer = (com.sun.star.sheet.XCellRangeReferrer)
    UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeReferrer.class, aPane);
com.sun.star.table.XCellRange xRange = xRefer.getReferredCells();
com.sun.star.beans.XPropertySet xRangeProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xRange);
xRangeProp.setPropertyValue("IsCellBackgroundTransparent", Boolean.FALSE);
xRangeProp.setPropertyValue("CellBackColor", Integer.valueOf(0xFFFFCC));

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

Index	No split	Vertical split	Horizontal split (*)	Vertical and horizontal split (*)
0	Entire window	Top pane	Left pane	Top left pane
1	--	Bottom pane	Right pane	Bottom left pane
2	--	--	--	Top right pane
3	--	--	--	Bottom right pane

(*) 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 ShowObjects, ShowCharts and ShowDrawing 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:

Display green grid lines - Java

// change the view to display green grid lines
com.sun.star.beans.XPropertySet xProp = (com.sun.star.beans.XPropertySet)
    UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, xController);
xProp.setPropertyValue("ShowGrid", Boolean.TRUE);
xProp.setPropertyValue("GridColor", Integer.valueOf(0x00CC00));

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.

XRangeSelection interface

Before the range selection mode is started, a listener is registered using the addRangeSelectionListener() method. The listener implements the com.sun.star.sheet.XRangeSelectionListener interface. Its done() or aborted() 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 RangeDescriptor 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 done() method, and notifies the main thread about the completion of the selection in the done() and aborted() methods:

ExampleRangeListener - Java

private class ExampleRangeListener implements com.sun.star.sheet.XRangeSelectionListener {
    public String aResult;

    public void done(com.sun.star.sheet.RangeSelectionEvent aEvent) {
        aResult = aEvent.RangeDescriptor;
        synchronized (this) {
            notify();
        }
    }

    public void aborted( com.sun.star.sheet.RangeSelectionEvent aEvent ) {
        synchronized (this) {
            notify();
        }
    }

    public void disposing( com.sun.star.lang.EventObject aObj ) {
    }
}

It is also possible to add another listener using the addRangeSelectionChangeListener() method. This listener implements the com.sun.star.sheet.XRangeSelectionChangeListener interface, and its descriptorChanged() 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 startRangeSelection() method. The parameter to that method is a sequence of property values with properties from the com.sun.star.sheet.RangeSelectionArguments service:

InitialValue specifies an existing selection value that is shown in the dialog and highlighted in the view when the selection mode is started.
Title is the title for the range selection dialog.
CloseOnMouseRelease 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 startRangeSelection() method returns immediately after starting the range selection mode. This allows it to be called from a dialog's event handler. The abortRangeSelection() 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.

Select a range in the view - Java

// let the user select a range and use it as the view's selection
com.sun.star.sheet.XRangeSelection xRngSel = (com.sun.star.sheet.XRangeSelection)
    UnoRuntime.queryInterface(com.sun.star.sheet.XRangeSelection.class, xController);
ExampleRangeListener aListener = new ExampleRangeListener();
xRngSel.addRangeSelectionListener(aListener);
com.sun.star.beans.PropertyValue[] aArguments = new com.sun.star.beans.PropertyValue[2];
aArguments[0] = new com.sun.star.beans.PropertyValue();
aArguments[0].Name = "Title";
aArguments[0].Value = "Please select a range";
aArguments[1] = new com.sun.star.beans.PropertyValue();
aArguments[1].Name = "CloseOnMouseRelease";
aArguments[1].Value = Boolean.TRUE;
xRngSel.startRangeSelection(aArguments);
synchronized (aListener) {
    aListener.wait(); // wait until the selection is done
}
xRngSel.removeRangeSelectionListener(aListener);
if (aListener.aResult != null && aListener.aResult.length() != 0)
{
    com.sun.star.view.XSelectionSupplier xSel = (com.sun.star.view.XSelectionSupplier)
        UnoRuntime.queryInterface(com.sun.star.view.XSelectionSupplier.class, xController);
    com.sun.star.sheet.XSpreadsheetView xView = (com.sun.star.sheet.XSpreadsheetView)
        UnoRuntime.queryInterface(com.sun.star.sheet.XSpreadsheetView.class, xController);
    com.sun.star.sheet.XSpreadsheet xSheet = xView.getActiveSheet();
    com.sun.star.table.XCellRange xResultRange = xSheet.getCellRangeByName(aListener.aResult);
    xSel.select(xResultRange);
}

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.

AddIn

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:

XExampleAddIn interface - IDL

#include <com/sun/star/uno/XInterface.idl>
#include <com/sun/star/sheet/XVolatileResult.idl>

module com { module sun { module star { module sheet { module addin {

    interface XExampleAddIn : com::sun::star::uno::XInterface
    {
        /// Sample function that just increments a value.
        long getIncremented( [in] long nValue );

        /// Sample function that returns a volatile result.
        com::sun::star::sheet::XVolatileResult getCounter( [in] string aName );
    };

}; }; }; }; };

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 getDisplayFunctionName() and getProgrammaticFuntionName() 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 setLocale().

The getProgrammaticCategoryName() 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 getDisplayCategoryName() method provides a translated name for the category.

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

Note:
The getProgrammaticFuntionName() method name is misspelled, but the wrong spelling has to be retained for compatibility reasons.

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 supportsService() and getSupportedServiceNames() 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 getServiceName() 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 getCompatibilityNames() 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 getIncremented() function from the example interface above can be implemented like this:

`getIncremented()` - Java
`public int getIncremented( int nValue ) { return nValue + 1; }`

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 getCounter() 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:

getCounter() - Java

private java.util.HashMap<String, ExampleAddInResult> aResults;

public com.sun.star.sheet.XVolatileResult getCounter(String aName) {
    aResults = new java.util.HashMap<String, ExampleAddInResult>();
    ExampleAddInResult aResult = aResults.get(aName);
    if (aResult == null) {
        aResult = new ExampleAddInResult(aName);
        aResults.put(aName, aResult);
    }
    return aResult;
}

The result object has to implement the addResultListener() and removeResultListener() 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 modified() method whenever a new result is available. The com.sun.star.sheet.ResultEvent object that is passed to the modified() 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 addResultListener() is called, it can be publicized by immediately calling modified() 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.

ExampleAddInResult - Java

class ExampleAddInResult implements com.sun.star.sheet.XVolatileResult
{
    private final String aName;
    private int nValue;
    private final java.util.ArrayList<XResultListener> aListeners = new java.util.ArrayList<XResultListener>();

    public ExampleAddInResult( String aNewName )
    {
        aName = aNewName;
    }

    private com.sun.star.sheet.ResultEvent getResult()
    {
        com.sun.star.sheet.ResultEvent aEvent =
            new com.sun.star.sheet.ResultEvent();
        aEvent.Value = aName + " " + nValue;
        aEvent.Source = this;
        return aEvent;
    }

    public void addResultListener(com.sun.star.sheet.XResultListener aListener)
    {
        aListeners.add( aListener );

        // immediately notify of initial value
        aListener.modified( getResult() );
    }

    public void removeResultListener(com.sun.star.sheet.XResultListener aListener)
    {
        aListeners.remove( aListener );
    }

    public void incrementValue()
    {
        ++nValue;
        com.sun.star.sheet.ResultEvent aEvent = getResult();

        for( XResultListener l : aListeners)
            l.modified(aEvent);
    }
}

Content on this page is licensed under the Public Documentation License (PDL).