LibreOffice Developer's Guide: Chapter 8 - Spreadsheet Documents

From The Document Foundation Wiki
Jump to navigation Jump to search

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 <idlmodule>com.sun.star.table</idlmodule>. Regarding the compatibility of text and spreadsheet tables, the corresponding features are also located in the module <idlmodule>com.sun.star.table</idlmodule>. In addition, spreadsheet tables are fully based on the specifications given and are extended by additional specifications from the module <idlmodule>com.sun.star.sheet</idlmodule>. 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

<idl>com.sun.star.sheet.SheetCell</idl>

<idl>com.sun.star.table.Cell</idl>

<idl>com.sun.star.sheet.Cells</idl>

-

<idl>com.sun.star.sheet.SheetCellRange</idl>

<idl>com.sun.star.table.CellRange</idl>

<idl>com.sun.star.sheet.SheetCellRanges</idl>

-

<idl>com.sun.star.sheet.SheetCellCursor</idl>

<idl>com.sun.star.table.CellCursor</idl>

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 <idlm>com.sun.star.text.XText:insertTextContent</idlm>() 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 = (XComponentLoader)UnoRuntime.queryInterface(
        XComponentLoader.class, desktop);
    PropertyValue[] loadProps = new PropertyValue[0];
    return xComponentLoader.loadComponentFromURL(loadUrl, "_blank", 0, loadProps);
}

Our helper returns a <idl>com.sun.star.lang.XComponent</idl> 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 = (XSpreadsheetDocument)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 = (com.sun.star.sheet.XSpreadsheet) 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> returned by insertSpreadsheet() is derived from <idl>com.sun.star.table.XCellRange</idl>. 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 = new Integer(0x008000);
    xPropSet.setPropertyValue("CellBackColor", bg_color);

    //--------------------
    // set the cell's foreground (text) color
    //--------------------
    Integer fg_color = new Integer(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);
    }

}

See also Working with a Spreadsheet Document (Example).

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

Attribute Name Type Remark
AbsoluteName string READONLY
AsianVerticalMode boolean
BottomBorder <idl>com.sun.star.table.BorderLine</idl>
CellBackColor long
CellProtection <idl>com.sun.star.util.CellProtection</idl>
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 <idl>com.sun.star.lang.Locale</idl>
CharLocaleAsian <idl>com.sun.star.lang.Locale</idl>
CharLocaleComplex <idl>com.sun.star.lang.Locale</idl>
CharPosture <idl>com.sun.star.awt.FontSlant</idl>
CharPostureAsian <idl>com.sun.star.awt.FontSlant</idl>
CharPostureComplex <idl>com.sun.star.awt.FontSlant</idl>
CharRelief short
CharShadowed boolean
CharStrikeout short
CharUnderline short
CharUnderlineColor long
CharUnderlineHasColor boolean
CharWeight float see also <idl>com.sun.star.awt.FontWeight</idl>
CharWeightAsian float see also <idl>com.sun.star.awt.FontWeight</idl>
CharWeightComplex float see also <idl>com.sun.star.awt.FontWeight</idl>
CharWordMode boolean
ChartColumnAsLabel boolean
ChartRowAsLabel boolean
ConditionalFormat <idl>com.sun.star.sheet.XSheetConditionalEntries</idl>
ConditionalFormatLocal <idl>com.sun.star.sheet.XSheetConditionalEntries</idl>
ConditionalFormatXML <idl>com.sun.star.sheet.XSheetConditionalEntries</idl> READONLY
DiagonalBLTR <idl>com.sun.star.table.BorderLine</idl>
DiagonalTLBR <idl>com.sun.star.table.BorderLine</idl>
FormulaLocal string
FormulaResultType <idl>com.sun.star.table.CellContentType</idl> READONLY
HoriJustify <idl>com.sun.star.table.CellHoriJustify</idl>
IsCellBackgroundTransparent boolean
IsTextWrapped boolean
LeftBorder <idl>com.sun.star.table.BorderLine</idl>
NumberFormat long
NumberingRules <idl>com.sun.star.container.XIndexReplace</idl>
Orientation <idl>com.sun.star.table.CellOrientation</idl>
ParaAdjust short
ParaBottomMargin long
ParaIndent short
ParaIsCharacterDistance boolean
ParaIsForbiddenRules boolean
ParaIsHangingPunctuation boolean
ParaIsHyphenation boolean
ParaLastLineAdjust short
ParaLeftMargin long
ParaRightMargin long
ParaTopMargin long
Position <idl>com.sun.star.awt.Point</idl> READONLY
RightBorder <idl>com.sun.star.table.BorderLine</idl>
RotateAngle long
RotateReference <idl>com.sun.star.table.CellVertJustify</idl>
ShadowFormat <idl>com.sun.star.table.ShadowFormat</idl>
ShrinkToFit boolean
Size <idl>com.sun.star.awt.Size</idl> READONLY
TableBorder <idl>com.sun.star.table.TableBorder</idl>
TopBorder <idl>com.sun.star.table.BorderLine</idl>
UserDefinedAttributes <idl>com.sun.star.container.XNameContainer</idl>
Validation <idl>com.sun.star.beans.XPropertySet</idl>
ValidationLocal <idl>com.sun.star.beans.XPropertySet</idl>
ValidationXML <idl>com.sun.star.beans.XPropertySet</idl> READONLY
VertJustify <idl>com.sun.star.table.CellVertJustify</idl>
WritingMode short

Handling Spreadsheet Document Files

Creating and Loading Spreadsheet Documents

If a document in LibreOffice API is required, begin by getting a <idl>com.sun.star.frame.Desktop</idl> 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 <idl>com.sun.star.lang.XComponent</idl> 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 <idl>com.sun.star.frame.XComponentLoader</idl> 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 <idl>com.sun.star.document.MediaDescriptor</idl>. 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 = (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 = new 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 <idl>com.sun.star.frame.XStorable</idl>. 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 = (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 <idl>com.sun.star.beans.PropertyValue</idl>s. The property FilterName contains the name and the property FilterOptions contains the filter options.

Note pin.svg

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

Note pin.svg

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 five to nine tokens, separated by commas. Tokens 6, to 9 are optional.

Tokens 1 to 5

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

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

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

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

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

Token 7, csv import

This token is the equivalent of the check box "Quoted field as text".

String, either 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 : only 8 tokens are used.

Token 9, csv export

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

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

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

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 <idl>com.sun.star.view.XPrintable</idl> 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 = (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.

<idl>com.sun.star.view.PrinterDescriptor</idl> comprises the properties for the printer:

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

<idl>com.sun.star.view.PrintOptions</idl> contains the following possibilities for a print job:

Properties of <idl>com.sun.star.view.PrintOptions</idl>
<idlm>com.sun.star.view.PrintOptions:CopyCount</idlm> short - Specifies the number of copies to print.
<idlm>com.sun.star.view.PrintOptions:FileName</idlm> string - If set, specifies the name of the file to print to.
<idlm>com.sun.star.view.PrintOptions:Collate</idlm> 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.
<idlm>com.sun.star.view.PrintOptions:Sort</idlm> boolean - Advises the printer to sort the pages of the copies.
<idlm>com.sun.star.view.PrintOptions:Pages</idlm> 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-".
<idlm>com.sun.star.view.PrintOptions:Wait</idlm> 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 = (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 <idl>com.sun.star.table.TableColumn</idl> and <idl>com.sun.star.table.TableRow</idl>. 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 <idl>com.sun.star.style.XStyleFamiliesSupplier</idl> of the document component, and is described in the chapter Overall Document Features.

Print Areas

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

Methods of <idl>com.sun.star.sheet.XPrintAreas</idl>
<idlm>com.sun.star.sheet.XPrintAreas:getPrintAreas</idlm>() Returns the print areas of the sheet.
<idlm>com.sun.star.sheet.XPrintAreas:setPrintAreas</idlm>() Sets the print areas of the sheet.
<idlm>com.sun.star.sheet.XPrintAreas:getPrintTitleColumns</idlm>() Returns true if the title columns are repeated on all subsequent print pages to the right.
<idlm>com.sun.star.sheet.XPrintAreas:setPrintTitleColumns</idlm>() Specifies if the title columns are repeated on all subsequent print pages to the right.
<idlm>com.sun.star.sheet.XPrintAreas:getTitleColumns</idlm>() Returns the range of columns that are marked as title columns.
<idlm>com.sun.star.sheet.XPrintAreas:setTitleColumns</idlm>() Sets the range of columns marked as title columns.
<idlm>com.sun.star.sheet.XPrintAreas:getPrintTitleRows</idlm>() Returns true if the title rows are repeated on all subsequent print pages to the bottom.
<idlm>com.sun.star.sheet.XPrintAreas:setPrintTitleRows</idlm>() Specifies if the title rows are repeated on all subsequent print pages to the bottom.
<idlm>com.sun.star.sheet.XPrintAreas:getTitleRows</idlm>() Returns the range of rows that are marked as title rows.
<idlm>com.sun.star.sheet.XPrintAreas:setTitleRows</idlm>() 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 <idl>com.sun.star.sheet.SpreadsheetDocument</idl>. 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 <idl>com.sun.star.sheet.Spreadsheets</idl>. The method getSheets() of the Interface <idl>com.sun.star.sheet.XSpreadsheetDocument</idl> returns the interface <idl>com.sun.star.sheet.XSpreadsheets</idl> 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 <idl>com.sun.star.container.XIndexAccess</idl> allows access to spreadsheets by their index.

with an enumeration

Using the service <idl>com.sun.star.sheet.SpreadsheetsEnumeration</idl> spreadsheets can be accessed as an enumeration.

by name

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

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 <idl>com.sun.star.sheet.XSpreadsheets</idl> contains additional methods that use the name of spreadsheets to add new sheets, and to move and copy them:

Methods of <idl>com.sun.star.sheet.XSpreadsheets</idl>
<idlm>com.sun.star.sheet.XSpreadsheets:insertNewByName</idlm>() Creates a new empty spreadsheet with the specified name and inserts it at the specified position.
<idlm>com.sun.star.sheet.XSpreadsheets:moveByName</idlm>() Moves the spreadsheet with the specified name to a new position.
<idlm>com.sun.star.sheet.XSpreadsheets:copyByName</idlm>() 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 <idl>com.sun.star.sheet.Spreadsheet</idl>, <idl>com.sun.star.sheet.SheetCellRange</idl>, the cell service <idl>com.sun.star.sheet.SheetCell</idl>, the collection of cell ranges <idl>com.sun.star.sheet.SheetCellRanges</idl> and the services <idl>com.sun.star.table.TableColumn</idl> and <idl>com.sun.star.table.TableRow</idl>. An overview of the capabilities of these services is provided below.

Capabilities of Spreadsheet

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

Spreadsheet
  • It can be named using <idl>com.sun.star.container.XNamed</idl>.
  • 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 <idl>com.sun.star.sheet.XDataPilotTablesSupplier</idl>, <idl>com.sun.star.sheet.XScenariosSupplier</idl>, <idl>com.sun.star.sheet.XSheetOutline</idl> and <idl>com.sun.star.sheet.XSheetAuditing</idl>.
  • Cells can be inserted, and entire cell ranges can be removed, moved or copied on the spreadsheet level using <idl>com.sun.star.sheet.XCellRangeMovement</idl>.
  • Drawing elements in a spreadsheet are part of the draw page available through <idl>com.sun.star.drawing.XDrawPageSupplier</idl>.
  • Certain sheet printing features are accessed at the spreadsheet. The <idl>com.sun.star.sheet.XPrintAreas</idl> and <idl>com.sun.star.sheet.XSheetPageBreak</idl> are used to get page breaks and control print areas.
  • The spreadsheet maintains charts. The interface <idl>com.sun.star.table.XTableChartsSupplier</idl> controls charts in the spreadsheet.
  • All cell annotations can be retrieved on the spreadsheet level with <idl>com.sun.star.sheet.XSheetAnnotationsSupplier</idl>.
  • A spreadsheet can be permanently protected from changes through <idl>com.sun.star.util.XProtectable</idl>.

Capabilities of SheetCellRange

The spreadsheet, as well as the cell ranges in a spreadsheet are <idl>com.sun.star.sheet.SheetCellRange</idl> 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 <idl>com.sun.star.sheet.XSheetCellRange</idl> and <idl>com.sun.star.table.XColumnRowRange</idl>.
  • Performs calculations with a SheetCellRange. The interface <idl>com.sun.star.sheet.XSheetOperation</idl> is for aggregate operations, <idl>com.sun.star.sheet.XMultipleOperation</idl> copies formulas adjusting their cell references, <idl>com.sun.star.sheet.XSubTotalCalculatable</idl> applies and removes sub totals, and <idl>com.sun.star.sheet.XArrayFormulaRange</idl> 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 <idl>com.sun.star.table.XAutoFormattable</idl> and the content of the cells can be indented using <idl>com.sun.star.util.XIndent</idl>. The interfaces <idl>com.sun.star.sheet.XCellFormatRangesSupplier</idl> and <idl>com.sun.star.sheet.XUniqueCellFormatRangesSupplier</idl> 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 <idl>com.sun.star.sheet.XCellRangeData</idl>.
  • Fills a cell range with data series automatically through its interface <idl>com.sun.star.sheet.XCellSeries</idl>.
  • Imports data from a database using <idl>com.sun.star.util.XImportable</idl>.
  • Searches and replaces cell contents using <idl>com.sun.star.util.XSearchable</idl>.
  • Perform queries for cell contents, such as formula cells, formula result types, or empty cells. The interface <idl>com.sun.star.sheet.XCellRangesQuery</idl> of the included <idl>com.sun.star.sheet.SheetRangesQuery</idl> service is responsible for this task.
  • Merges cells into a single cell through <idl>com.sun.star.util.XMergeable</idl>.
  • Sorts and filters the content of a SheetCellRange, using <idl>com.sun.star.util.XSortable</idl>, <idl>com.sun.star.sheet.XSheetFilterable</idl> and <idl>com.sun.star.sheet.XSheetFilterableEx</idl>.
  • 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 <idlm>com.sun.star.sheet.XCellRangeAddressable:getRangeAddress</idlm>() returns the corresponding address description struct <idl>com.sun.star.table.CellRangeAddress</idl>.
  • Charts can be based on a SheetCellRange, because it supports <idl>com.sun.star.chart.XChartDataArray</idl>.

Capabilities of SheetCell

A <idl>com.sun.star.sheet.SheetCell</idl> 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 <idl>com.sun.star.table.Cell</idl>. The interface <idl>com.sun.star.table.XCell</idl> is capable of manipulating the values and formulas in a cell. For text, the service <idl>com.sun.star.text.Text</idl> with the main interface <idl>com.sun.star.text.XText</idl> is available at a SheetCell. Its text fields are accessed through <idl>com.sun.star.text.XTextFieldsSupplier</idl>.
  • A SheetCell is a special case of a SheetCellRange. As such, it has all capabilities of the <idl>com.sun.star.sheet.SheetCellRange</idl> described above.
  • It can have an annotation: <idl>com.sun.star.sheet.XSheetAnnotationAnchor</idl>.
  • It can provide its unique cell address in the spreadsheet document, that is, its column, row and the sheet it is located in. The <idlm>com.sun.star.sheet.XCellAddressable:getCellAddress</idlm>() returns the appropriate <idl>com.sun.star.table.CellAddress</idl> struct.
  • It can be locked temporarily against user interaction with <idl>com.sun.star.document.XActionLockable</idl>.

Capabilities of SheetCellRanges Container

The container of <idl>com.sun.star.sheet.SheetCellRanges</idl> 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 <idl>com.sun.star.sheet.SheetCellRanges</idl> 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 <idl>com.sun.star.container.XIndexAccess</idl>, <idl>com.sun.star.container.XNameAccess</idl> and <idl>com.sun.star.container.XEnumerationAccess</idl>.
  • It can access, replace, append and remove ranges by name through <idl>com.sun.star.container.XNameContainer</idl>
  • 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 <idl>com.sun.star.sheet.XSheetCellRangeContainer</idl> that was originally based on <idl>com.sun.star.container.XIndexAccess</idl>. The SheetCellRanges maintain a sub-container of all cells in the ranges that are not empty, obtainable through the <idlm>com.sun.star.sheet.XSheetCellRanges:getCells</idlm>() method.
  • It can enumerate the ranges using <idl>com.sun.star.container.XEnumerationAccess</idl>.
  • It can query the ranges for certain cell contents, such as formula cells, formula result types or empty cells. The interface <idl>com.sun.star.sheet.XCellRangesQuery</idl> of the included <idl>com.sun.star.sheet.SheetRangesQuery</idl> 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 <idl>com.sun.star.table.XColumnRowRange</idl>. These containers are <idl>com.sun.star.table.TableColumns</idl> and <idl>com.sun.star.table.TableRows</idl>. Both containers support index and enumeration access. Only the TableColumns supports name access to the single columns and rows (<idl>com.sun.star.table.TableColumn</idl> and <idl>com.sun.star.table.TableRow</idl>) 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 <idl>com.sun.star.container.XNameAccess</idl>:

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 <idl>com.sun.star.table.XTableRows</idl> and <idl>com.sun.star.table.XTableColumns</idl>.
  • 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 <idl>com.sun.star.sheet.Spreadsheet</idl>.

Properties of Spreadsheet

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

Properties of <idl>com.sun.star.sheet.Spreadsheet</idl>

<idlm>com.sun.star.sheet.Spreadsheet:IsVisible</idlm>

boolean - Determines if the sheet is visible in the GUI.

<idlm>com.sun.star.sheet.Spreadsheet:PageStyle</idlm>

Contains the name of the page style of this spreadsheet. See Overall Document Features for details about styles.

Naming

The spreadsheet interface <idl>com.sun.star.container.XNamed</idl> 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 <idl>com.sun.star.sheet.XCellRangeMovement</idl> 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 <idl>com.sun.star.sheet.XCellRangeMovement</idl>

<idlm>com.sun.star.sheet.XCellRangeMovement:insertCells</idlm>()

Inserts a range of empty cells at a specific position. The direction of the insertion is determined by the parameter nMode (type <idl>com.sun.star.sheet.CellInsertMode</idl>).

<idlm>com.sun.star.sheet.XCellRangeMovement:removeRange</idlm>()

Deletes a range of cells from the spreadsheet. The parameter nMode (type <idl>com.sun.star.sheet.CellDeleteMode</idl>) determines how remaining cells will be moved.

<idlm>com.sun.star.sheet.XCellRangeMovement:copyRange</idlm>()

Copies the contents of a cell range to another place in the document.

<idlm>com.sun.star.sheet.XCellRangeMovement:moveRange</idlm>()

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 <idl>com.sun.star.sheet.XSheetPageBreak</idl> return the positions of column and row page breaks, represented by a sequence of <idl>com.sun.star.sheet.TablePageBreakData</idl> 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 <idl>com.sun.star.table.TableColumn</idl> and <idl>com.sun.star.table.TableRow</idl>.

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 <idl>com.sun.star.sheet.SheetCellRange</idl>.

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 <idl>com.sun.star.sheet.SheetCellRange</idl>
<idlm>com.sun.star.sheet.SheetCellRange:Position</idlm>

<idlm>com.sun.star.sheet.SheetCellRange:Size</idlm>

The position and size of the cell in 100th of a millimeter. The position is relative to the first cell of the spreadsheet. Note, that this is not always the first visible cell.
<idlm>com.sun.star.sheet.SheetCellRange:ConditionalFormat</idlm>

<idlm>com.sun.star.sheet.SheetCellRange:ConditionalFormatLocal</idlm>

Used to access conditional formats. See Conditional Formats for details.
<idlm>com.sun.star.sheet.SheetCellRange:Validation</idlm>

<idlm>com.sun.star.sheet.SheetCellRange:ValidationLocal</idlm>

Used to access data validation. See Data Validation for details.

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

Cell and Cell Range Access

The interface <idl>com.sun.star.sheet.XSheetCellRange</idl> is derived from <idl>com.sun.star.table.XCellRange</idl>. 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 <idl>com.sun.star.sheet.XSheetCellRange</idl> 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 <idl>com.sun.star.table.XCellRange</idl> 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.svg

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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.table.XCellRange</idl> 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", new Integer(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", new Integer(0xFFFF80));

Merging Cell Ranges into a Single Cell

The cell range interface <idl>com.sun.star.util.XMergeable</idl> 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 <idl>com.sun.star.table.XColumnRowRange</idl> 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 <idl>com.sun.star.table.XTableColumns</idl> of the collection of columns.
  • The method getRows() returns the interface <idl>com.sun.star.table.XTableRows</idl> 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 = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(
    com.sun.star.beans.XPropertySet.class, aColumnObj);
xPropSet.setPropertyValue( "Width", new Integer( 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 <idl>com.sun.star.sheet.XCellRangeData</idl>.

  • 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), new Integer(10)},
    {new Double(2.2), new String("")},
    {new Double(3.3), new String("Text")}
};

xData.setDataArray(aValues);

Absolute Address

The method getCellRangeAddress() of the interface <idl>com.sun.star.sheet.XCellRangeAddressable</idl> returns a <idl>com.sun.star.table.CellRangeAddress</idl> 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 <idl>com.sun.star.sheet.XCellSeries</idl> 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 <idl>com.sun.star.sheet.FillDirection</idl> 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 <idl>com.sun.star.sheet.FillMode</idl> constant. If the values are dates, <idl>com.sun.star.sheet.FillDateMode</idl> 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 C D E F 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 (com.sun.star.sheet.XCellSeries) 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 <idl>com.sun.star.sheet.XSheetOperation</idl> 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 <idl>com.sun.star.sheet.GeneralFunction</idl> specify the calculation method.
  • The method clearContents() clears contents of the cells used. The parameter describes the contents to clear, using the constants of <idl>com.sun.star.sheet.CellFlags</idl>.

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 <idl>com.sun.star.sheet.XMultipleOperation</idl> inserts a multiple operation range.

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

A B C D E F G
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 <idl>com.sun.star.sheet.XArrayFormulaRange</idl> 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.svg

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 <idl>com.sun.star.table.XCell</idl>. 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 <idl>com.sun.star.sheet.SheetCell</idl>. This service extends the service <idl>com.sun.star.table.Cell</idl>, that provides fundamental table cell functionality, such as setting formulas, values and text of a cell.

Properties of SheetCell

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

Properties of <idl>com.sun.star.sheet.SheetCell</idl>
<idlm>com.sun.star.sheet.SheetCell:Position</idlm>

<idlm>com.sun.star.sheet.SheetCell:Size</idlm>

The position and size of the cell in 100th of a millimeter. The position is relative to the first cell of the spreadsheet. Note that this is not always the first visible cell.
<idlm>com.sun.star.sheet.SheetCell:FormulaLocal</idlm> Used to query or set a formula using function names of the current language.
<idlm>com.sun.star.sheet.SheetCell:FormulaResultType</idlm> The type of the result. It is a constant from the set <idl>com.sun.star.sheet.FormulaResult</idl>.
<idlm>com.sun.star.sheet.SheetCell:ConditionalFormat</idlm>

<idlm>com.sun.star.sheet.SheetCell:ConditionalFormatLocal</idlm>

Used to access conditional formats. See Conditional Formats for details.
<idlm>com.sun.star.sheet.SheetCell:Validation</idlm>

<idlm>com.sun.star.sheet.SheetCell:ValidationLocal</idlm>

Used to access data validation. See Data Validation for details.

Access to Formulas, Values and Errors

The cell interface <idl>com.sun.star.table.XCell</idl> 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.svg

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 <idl>com.sun.star.table.CellContentType</idl> 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 <idl>com.sun.star.table.XCellRange</idl> 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 = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface(
    com.sun.star.beans.XPropertySet.class, xCell);
xPropSet.setPropertyValue("CellBackColor", new Integer(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 <idl>com.sun.star.util.NumberFormat</idl> 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 <idl>com.sun.star.text.Text</idl> supports the modification of simple or formatted text contents. Changing text contents and text formatting is provided by the interface <idl>com.sun.star.text.XText</idl> as discussed in First Steps. Refer to chapter Editing Text for further information. It implements the interfaces <idl>com.sun.star.container.XEnumerationAccess</idl> that provides access to the paragraphs of the text and the interface <idl>com.sun.star.text.XText</idl> 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 =
    (com.sun.star.container.XEnumerationAccess) 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 =
        (com.sun.star.container.XEnumerationAccess) 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 <idl>com.sun.star.text.XTextFieldsSupplier</idl> 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 pin.svg

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

Absolute Address

The method getCellAddress() of the interface <idl>com.sun.star.sheet.XCellAddressable</idl> returns a <idl>com.sun.star.table.CellAddress</idl> 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 <idl>com.sun.star.sheet.SheetCellRanges</idl>. They are returned by several methods, for instance the cell query methods of <idl>com.sun.star.sheet.SheetRangesQuery</idl>. Besides standard container operations, it performs a few spreadsheet functions also usable with a single cell range.

Properties of SheetCellRanges

Properties of <idl>com.sun.star.sheet.SheetCellRanges</idl>
<idlm>com.sun.star.sheet.SheetCellRanges:ConditionalFormat</idlm>

<idlm>com.sun.star.sheet.SheetCellRanges:ConditionalFormatLocal</idlm>

Used to access conditional formats. See Conditional Formats for details.
<idlm>com.sun.star.sheet.SheetCellRanges:Validation</idlm>

<idlm>com.sun.star.sheet.SheetCellRanges:ValidationLocal</idlm>

Used to access data validation. See Data Validation for details.

Access to Single Cell Ranges in SheetCellRanges Container

The interfaces <idl>com.sun.star.container.XEnumerationAccess</idl> and <idl>com.sun.star.container.XIndexAccess</idl> iterates over all contained cell ranges by index or enumeration. With the <idl>com.sun.star.container.XNameContainer</idl>, 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 <idl>com.sun.star.util.XReplaceable</idl> (see Navigating)
  • Service <idl>com.sun.star.sheet.SheetRangesQuery</idl> (see Navigating)
  • Interface <idl>com.sun.star.util.XIndent</idl> (see Formatting)
  • Interface <idl>com.sun.star.sheet.XSheetOperation</idl> (see Cell Ranges)
  • Interface <idl>com.sun.star.chart.XChartDataArray</idl> (see Charts)

The interfaces <idl>com.sun.star.sheet.XSheetCellRangeContainer</idl> and <idl>com.sun.star.sheet.XSheetCellRanges</idl> 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 <idl>com.sun.star.sheet.XSheetCellRangeContainer</idl> is derived from the interface <idl>com.sun.star.sheet.XSheetCellRanges</idl> 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 <idl>com.sun.star.sheet.XSheetCellRanges</idl> implements methods for access to cells and cell ranges:

  • The method getCells() returns the interface <idl>com.sun.star.container.XEnumerationAccess</idl> of a cell collection. The service <idl>com.sun.star.sheet.Cells</idl> is discussed below. This collection contains the cell addresses of non-empty cells in all cell ranges.

The service <idl>com.sun.star.sheet.Cells</idl> 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 =
        (com.sun.star.lang.XMultiServiceFactory) UnoRuntime.queryInterface(
            com.sun.star.lang.XMultiServiceFactory.class, xDocument);
    com.sun.star.sheet.XSheetCellRangeContainer xRangeCont =
        (com.sun.star.sheet.XSheetCellRangeContainer) 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 <idl>com.sun.star.table.TableColumns</idl> and <idl>com.sun.star.table.TableRows</idl> 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 <idl>com.sun.star.table.XTableColumns</idl> and <idl>com.sun.star.table.XTableRows</idl> that are derived from <idl>com.sun.star.container.XIndexAccess</idl>. The method createEnumeration() of the interface <idl>com.sun.star.container.XEnumerationAccess</idl> creates an enumeration of all columns or rows. The interface <idl>com.sun.star.container.XNameAccess</idl> accesses columns through their names. The implementation of this interface is optional.

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

Note pin.svg

Note:
The interface <idl>com.sun.star.container.XIndexAccess</idl> 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 <idl>com.sun.star.container.XNameAccess</idl> 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", new Integer(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", new 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", new Integer(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 <idl>com.sun.star.table.CellProperties</idl>. These properties are accessible through the interface <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.table.TableBorder</idl>. 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 <idl>com.sun.star.table.BorderLine</idl> 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 <idl>com.sun.star.style.ParagraphProperties</idl>
  • Service <idl>com.sun.star.style.CharacterProperties</idl>
  • Service <idl>com.sun.star.style.CharacterPropertiesAsian</idl>
  • Service <idl>com.sun.star.style.CharacterPropertiesComplex</idl>

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", new Integer(0x003399));
xPropSet.setPropertyValue("CharHeight", new Float(20.0));

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

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

The code below changes the character and paragraph formatting of a cell. Assume that xCell is a <idl>com.sun.star.table.XCell</idl> 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", new Integer(0x003399));
xPropSet.setPropertyValue("CharHeight", new Float(20.0));

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

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

Indentation

The methods of the interface <idl>com.sun.star.util.XIndent</idl> 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.svg

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 <idl>com.sun.star.sheet.CellFormatRanges</idl> 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 <idl>com.sun.star.sheet.UniqueCellFormatRanges</idl> represents, similar to Cell Format Ranges above, a collection of equally formatted cell ranges, but this collection contains cell range container objects (service <idl>com.sun.star.sheet.SheetCellRanges</idl>) 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 <idl>com.sun.star.sheet.CellFormatRanges</idl> object contains the following ranges: A1:C2, D1:G1, D2:F2, G2:G2, and A3:G3.

A <idl>com.sun.star.sheet.UniqueCellFormatRanges</idl> object contains two <idl>com.sun.star.sheet.SheetCellRanges</idl> 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 =
        (com.sun.star.sheet.XCellFormatRangesSupplier) 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 =
        (com.sun.star.sheet.XUniqueCellFormatRangesSupplier) 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 = (com.sun.star.container.XIndexAccess) 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 <idl>com.sun.star.container.XIndexAccess</idl>.

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 <idl>com.sun.star.sheet.TableAutoFormat</idl>. It contains exactly 16 auto format fields (service <idl>com.sun.star.sheet.TableAutoFormatField</idl>). Each auto format field contains all properties of a single cell.

The cell range interface <idl>com.sun.star.table.XAutoFormattable</idl> 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 pin.svg

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 <idl>com.sun.star.sheet.TableAutoFormats</idl>. 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 =
            (com.sun.star.lang.XMultiServiceFactory) 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", new Integer(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 <idl>com.sun.star.sheet.XSheetConditionalEntries</idl> of the conditional format container <idl>com.sun.star.sheet.TableConditionalFormat</idl>. The objects of both properties are equal, except for the representation of formulas. The ConditionalFormatLocal property uses function names in the current language.

Note pin.svg

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 <idl>com.sun.star.sheet.TableConditionalEntry</idl>. It implements two interfaces:

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

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

  • The method addNew() inserts a new condition. It expects a sequence of <idl>com.sun.star.beans.PropertyValue</idl> objects. The following properties are supported:
    • Operator: A <idl>com.sun.star.sheet.ConditionOperator</idl> 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 <idl>com.sun.star.table.CellAddress</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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.svg

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 <idl>com.sun.star.sheet.SheetCellCursor</idl>. 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 <idl>com.sun.star.table.CellCursor</idl> that represents the cell or cell range cursor of a table.

The interface <idl>com.sun.star.sheet.XSpreadsheet</idl> of a spreadsheet creates the cell cursors. The methods return the interface <idl>com.sun.star.sheet.XSheetCellCursor</idl> of the cursor. It is derived from the interface <idl>com.sun.star.sheet.XSheetCellRange</idl> 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 <idl>com.sun.star.table.CellCursor</idl> implements the interface <idl>com.sun.star.table.XCellCursor</idl> that provides methods to move to specific cells of a cell range. This interface is derived from <idl>com.sun.star.table.XCellRange</idl> so all methods that access single cells can be used.

Methods of <idl>com.sun.star.table.XCellCursor</idl>
<idlm>com.sun.star.table.XCellCursor:gotoStart</idlm>() Moves to the first filled cell. This cell may be outside of the current range of the cursor.
<idlm>com.sun.star.table.XCellCursor:gotoEnd</idlm>() Moves to the last filled cell. This cell may be outside of the current range of the cursor.
<idlm>com.sun.star.table.XCellCursor:gotoOffset</idlm>() Moves the cursor relative to the current position, even if the cursor is a range.
<idlm>com.sun.star.table.XCellCursor:gotoPrevious</idlm>() Moves the cursor to the latest available unprotected cell. In most cases,this is the cell to the left of the current cell.
<idlm>com.sun.star.table.XCellCursor:gotoNext</idlm>() 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 <idl>com.sun.star.sheet.XSheetCellCursor</idl> 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.
A B C D E 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 <idl>com.sun.star.table.XCellCursor</idl>.
Note pin.svg

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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.sheet.XUsedAreaCursor</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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 <idl>com.sun.star.sheet.XNamedRanges</idl> 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 <idl>com.sun.star.sheet.NamedRangeFlag</idl> 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 <idl>com.sun.star.sheet.Border</idl> 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 <idl>com.sun.star.sheet.NamedRange</idl> service accesses an existing named range. The <idl>com.sun.star.container.XNamed</idl> interface changes the name, and the <idl>com.sun.star.sheet.XNamedRange</idl> 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 <idl>com.sun.star.sheet.XCellRangeReferrer</idl> 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 <idl>com.sun.star.sheet.LabelRanges</idl> 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 <idl>com.sun.star.sheet.XLabelRanges</idl> 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 <idl>com.sun.star.sheet.LabelRange</idl> service represents a single label range and contains the <idl>com.sun.star.sheet.XLabelRange</idl> 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 <idl>com.sun.star.sheet.SheetRangesQuery</idl>. It implements interfaces to query cells and cell ranges with specific properties.

The methods of the interface <idl>com.sun.star.sheet.XCellRangesQuery</idl> search for cells with specific contents or properties inside of the given cell range. The methods of the interface <idl>com.sun.star.sheet.XFormulaQuery</idl> 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.svg

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


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

Methods of <idl>com.sun.star.sheet.XCellRangesQuery</idl>
<idlm>com.sun.star.sheet.XCellRangesQuery:queryVisibleCells</idlm>() Returns all cells that are not hidden.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryEmptyCells</idlm>() Returns all cells that do not have any content.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryContentCells</idlm>() Returns all cells that have the contents described by the passed parameter. The flags are defined in <idl>com.sun.star.sheet.CellFlags</idl>.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryFormulaCells</idlm>() Returns all formula cells whose results have a specific type described by the passed parameter. The result flags are defined in <idl>com.sun.star.sheet.FormulaResult</idl>.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryColumnDifferences</idlm>() 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.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryRowDifferences</idlm>() 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.
<idlm>com.sun.star.sheet.XCellRangesQuery:queryIntersection</idlm>() Returns all cells of the range that are contained in the passed range address.

Example:

A B C D E F G
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 <idl>com.sun.star.util.XReplaceable</idl> is derived from <idl>com.sun.star.util.XSearchable</idl> 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 <idl>com.sun.star.util.XReplaceDescriptor</idl> 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", new Boolean(false));
int nCount = xReplace.replaceAll(xReplaceDesc);
System.out.println("Search text replaced " + nCount + " times.");

Note pin.svg

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 <idl>com.sun.star.table.TableSortDescriptor2</idl> extends the service <idl>com.sun.star.util.SortDescriptor2</idl> 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 <idl>com.sun.star.table.TableSortField</idl> structs.

The size of the sequence using the MaxSortFieldsCount property.

The service <idl>com.sun.star.sheet.SheetSortDescriptor2</idl> extends the service <idl>com.sun.star.table.TableSortDescriptor2</idl>with 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 <idl>com.sun.star.sheet.GlobalSheetSettings</idl> service to find the sort list that is used.

SheetSortDescriptor2

To sort the contents of a cell range, the sort() method from the <idl>com.sun.star.util.XSortable</idl> interface is called, passing a sequence of property values with properties from the <idl>com.sun.star.sheet.SheetSortDescriptor2</idl> 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 <idl>com.sun.star.table.TableSortField</idl> elements. In the <idl>com.sun.star.table.TableSortField</idl> 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 <idl>com.sun.star.i18n.Collator</idl> 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 pin.svg

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 = new 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 <idl>com.sun.star.sheet.SheetFilterDescriptor</idl> object is created using the createFilterDescriptor() method from the range's <idl>com.sun.star.sheet.XSheetFilterable</idl> 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 <idl>com.sun.star.sheet.XSheetFilterDescriptor</idl> interface is used to set the filter criteria as a sequence of <idl>com.sun.star.sheet.TableFilterField</idl> elements. The <idl>com.sun.star.sheet.TableFilterField</idl> 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 <idl>com.sun.star.beans.XPropertySet</idl> 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", new Boolean(true));
xFilter.filter(xFilterDesc);

The <idl>com.sun.star.sheet.XSheetFilterableEx</idl> interface is used to create a filter descriptor from criteria in a cell range in the same manner as the "Advanced Filter" dialog. The <idl>com.sun.star.sheet.XSheetFilterableEx</idl> interface must be queried from the range that contains the conditions, and the <idl>com.sun.star.sheet.XSheetFilterable</idl> 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 <idl>com.sun.star.sheet.SubTotalDescriptor</idl> object is created using the createSubTotalDescriptor() method from the range's <idl>com.sun.star.sheet.XSubTotalCalculatable</idl> 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 <idl>com.sun.star.sheet.XSubTotalDescriptor</idl> 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 <idl>com.sun.star.sheet.SubTotalColumn</idl> 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 <idl>com.sun.star.container.XIndexAccess</idl> interface is used to access the fields. Each field's <idl>com.sun.star.sheet.XSubTotalField</idl> 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 <idl>com.sun.star.util.XImportable</idl> 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 <idl>com.sun.star.sheet.SpreadsheetDocument</idl> 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 <idl>com.sun.star.sheet.XDatabaseRanges</idl> interface's addNewByName() method that requires the name of the new database range, and a <idl>com.sun.star.table.CellRangeAddress</idl> with the address of the cell range as arguments. The removeByName() method removes a database range.

The <idl>com.sun.star.container.XNameAccess</idl> interface is used to get a single <idl>com.sun.star.sheet.DatabaseRange</idl> object. Its <idl>com.sun.star.sheet.XCellRangeReferrer</idl> interface is used to access the cell range that it is pointed to. The <idl>com.sun.star.sheet.XDatabaseRange</idl> interface retrieves or changes the <idl>com.sun.star.table.CellRangeAddress</idl> 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", new Boolean(false));
        xResultProp.setPropertyValue("CellBackColor", new Integer(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 <idl>com.sun.star.sheet.XSheetLinkable</idl> 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 <idl>com.sun.star.sheet.XSheetLinkable</idl> 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 <idl>com.sun.star.sheet.SheetLinkMode</idl> 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 <idl>com.sun.star.sheet.SheetLinks</idl> 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 <idl>com.sun.star.container.XNameAccess</idl> interface is the source document's URL.

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

Note pin.svg

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 <idl>com.sun.star.sheet.XAreaLinks</idl> interface's insertAtPosition() method is used with the following parameters:

  • The position where the link is placed in the document as a <idl>com.sun.star.table.CellAddress</idl> 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 <idl>com.sun.star.sheet.CellAreaLink</idl> service is used to modify or refresh an area link. The <idl>com.sun.star.sheet.XAreaLink</idl> 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 <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.util.XRefreshable</idl> 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 <idl>com.sun.star.sheet.DDELink</idl> service is only used to query the link's parameters using the <idl>com.sun.star.sheet.XDDELink</idl> interface, and refresh it using the <idl>com.sun.star.util.XRefreshable</idl> 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 <idl>com.sun.star.container.XNameAccess</idl> interface consists of the three parameter strings concatenated.

DataPilot Tables

The <idl>com.sun.star.sheet.DataPilotTables</idl> and related services create and modify DataPilot tables in a spreadsheet.

The method getDataPilotTables() of the interface <idl>com.sun.star.sheet.XDataPilotTablesSupplier</idl> returns the interface <idl>com.sun.star.sheet.XDataPilotTables</idl> of the collection of all data pilot tables contained in the spreadsheet.

DataPilotTables

The <idl>com.sun.star.sheet.DataPilotTables</idl> service is accessed by getting the <idl>com.sun.star.sheet.XDataPilotTablesSupplier</idl> interface from a spreadsheet object and calling the getDataPilotTables() method.

Note pin.svg

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 <idl>com.sun.star.sheet.DataPilotSource</idl> 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 <idl>com.sun.star.sheet.DataPilotDescriptor</idl> object by calling the <idl>com.sun.star.sheet.XDataPilotTables</idl> 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 <idl>com.sun.star.sheet.XDataPilotDescriptor</idl> 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 <idl>com.sun.star.table.CellRangeAddress</idl> 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 <idl>com.sun.star.sheet.SheetFilterDescriptor</idl> 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 <idl>com.sun.star.sheet.DataPilotFields</idl> service. Each <idl>com.sun.star.sheet.DataPilotField</idl> object has an Orientation property that controls where in the DataPilot table the field is used. The <idl>com.sun.star.sheet.DataPilotFieldOrientation</idl> 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 <idl>com.sun.star.sheet.DataPilotField</idl> 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 pin.svg

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 <idl>com.sun.star.sheet.DataPilotTable</idl> service is used to modify an existing DataPilot table. The object for an existing table is available through the <idl>com.sun.star.container.XNameAccess</idl> interface of the <idl>com.sun.star.sheet.DataPilotTables</idl> service. It implements the <idl>com.sun.star.sheet.XDataPilotDescriptor</idl> 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 <idl>com.sun.star.sheet.XDataPilotTable</idl> 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 pin.svg

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 <idl>com.sun.star.sheet.XDataPilotTables</idl> 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
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
{
    static public final int nDimensionCount = 6;
    static public final int nValueDimension = 4;
    static public final int nDataDimension = 5;
    static public final String [] aDimensionNames = {
        "ones", "tens", "hundreds", "thousands", "value", "" };

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

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

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

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

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

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

  • <idl>com.sun.star.sheet.DataPilotSourceDimensions</idl> is the parent object of <idl>com.sun.star.sheet.XDimensionsSupplier</idl>
  • <idl>com.sun.star.sheet.DataPilotSourceHierarchies</idl> is the parent object of <idl>com.sun.star.sheet.XHierarchiesSupplier</idl>
  • <idl>com.sun.star.sheet.DataPilotSourceLevels</idl> is the parent object of <idl>com.sun.star.sheet.XLevelsSupplier</idl>
  • <idl>com.sun.star.sheet.DataPilotSourceMembers</idl> is the parent object of <idl>com.sun.star.sheet.XMembersSupplier</idl>

All contain the <idl>com.sun.star.container.XNameAccess</idl> interface to access their children.

Source Object

An implementation of the <idl>com.sun.star.sheet.DataPilotSource</idl> 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 <idl>com.sun.star.lang.XInitialization</idl> 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 <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.sheet.XDataPilotResults</idl> 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 <idl>com.sun.star.sheet.DataResult</idl> struct contains the numeric value in the Value member, and a Flags member contains a combination of the <idl>com.sun.star.sheet.DataResultFlags</idl> 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 = ((Integer)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 = ((Integer)
                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 <idl>com.sun.star.util.XRefreshable</idl> 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 <idl>com.sun.star.sheet.DataPilotSourceDimensions</idl> 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 <idl>com.sun.star.sheet.DataPilotSourceDimension</idl> service contains a <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.util.XCloneable</idl> 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 <idl>com.sun.star.sheet.DataPilotFieldOrientation</idl> 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 = new Integer(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 = new Integer(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(new Integer(nDimension));
        else if (eOrientation == com.sun.star.sheet.DataPilotFieldOrientation.ROW)
            nPosition = aSettings.aRowDimensions.indexOf(new Integer(nDimension));
        else
            nPosition = nDimension;
        return new Integer(nPosition);
    }
    else if (aPropertyName.equals("Function"))
        return com.sun.star.sheet.GeneralFunction.SUM;
    else if (aPropertyName.equals("UsedHierarchy"))
        return new Integer(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 <idl>com.sun.star.util.XCloneable</idl> 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 <idl>com.sun.star.container.XNamed</idl> 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 <idl>com.sun.star.sheet.DataPilotSourceDimension</idl> 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 <idl>com.sun.star.sheet.DataPilotSourceHierarchy</idl> 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 <idl>com.sun.star.sheet.DataPilotSourceLevel</idl> service contains a <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.sheet.GeneralFunction</idl> 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 <idl>com.sun.star.sheet.XDataPilotMemberResults</idl> 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 <idl>com.sun.star.sheet.MemberResult</idl> 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 <idl>com.sun.star.sheet.MemberResultFlags</idl> 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(new Integer(nDimension));
    if (nPosition >= 0)
        nDimensions = aSettings.aColDimensions.size();
    else {
        nPosition = aSettings.aRowDimensions.indexOf(new Integer(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 <idl>com.sun.star.sheet.DataPilotSourceMember</idl> service contains two settings that are accessed through the <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.document.XActionLockable</idl> 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 <idl>com.sun.star.util.XProtectable</idl> 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 <idl>com.sun.star.sheet.XSheetOutline</idl> contains all the methods to control the row and column outlines of a spreadsheet:

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

Detective

The spreadsheet interface <idl>com.sun.star.sheet.XSheetAuditing</idl> supports the detective functionality of the spreadsheet.

Methods of <idl>com.sun.star.sheet.XSheetAuditing</idl>
<idlm>com.sun.star.sheet.XSheetAuditing:hideDependents</idlm>()

<idlm>com.sun.star.sheet.XSheetAuditing:hidePrecedents</idlm>()

Hides the last arrows to dependent or precedent cells of a formula cell. Repeated calls of the methods shrink the chains of arrows.
<idlm>com.sun.star.sheet.XSheetAuditing:showDependents</idlm>()

<idlm>com.sun.star.sheet.XSheetAuditing:showPrecedents</idlm>()

Adds arrows to the next dependent or precedent cells of a formula cell. Repeated calls of the methods extend the chains of arrows.
<idlm>com.sun.star.sheet.XSheetAuditing:showErrors</idlm>() Inserts arrows to all cells that cause an error in the specified cell.
<idlm>com.sun.star.sheet.XSheetAuditing:showInvalid</idlm>() Marks all cells that contain invalid values.
<idlm>com.sun.star.sheet.XSheetAuditing:clearArrows</idlm>() 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 <idl>com.sun.star.beans.XPropertySet</idl> of the validation object <idl>com.sun.star.sheet.TableValidation</idl>. The objects of both properties are equal, except the representation of formulas. The ValidationLocal property uses function names in the current language).

Note pin.svg

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

  • Type (type <idl>com.sun.star.sheet.ValidationType</idl>): 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 <idl>com.sun.star.sheet.ValidationAlertStyle</idl>): 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 <idl>com.sun.star.sheet.XSheetCondition</idl> 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 <idl>com.sun.star.sheet.XSpreadsheet</idl> 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", new 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 <idl>com.sun.star.sheet.XConsolidatable</idl>'s method createConsolidationDescriptor() returns the interface <idl>com.sun.star.sheet.XConsolidationDescriptor</idl> of a consolidation descriptor (service <idl>com.sun.star.sheet.ConsolidationDescriptor</idl>). 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 <idl>com.sun.star.sheet.GeneralFunction</idl>.
  • getSources() and setSources(): A sequence of <idl>com.sun.star.table.CellRangeAddress</idl> structs with all cell ranges containing the source data.
  • getStartOutputPosition() and setStartOutputPosition(): A <idl>com.sun.star.table.CellAddress</idl> 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 <idl>com.sun.star.sheet.XConsolidatable</idl> performs a consolidation with the passed descriptor.

Charts

TableCharts

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

Note pin.svg

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

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

For further information about charts, see Charts.

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

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

The following example shows how xCharts can be a <idl>com.sun.star.table.XTableCharts</idl> 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 <idl>com.sun.star.sheet.XScenariosSupplier</idl>'s method getScenarios() returns the interface <idl>com.sun.star.sheet.XScenarios</idl> of the scenario set of the spreadsheet. This scenario set is represented by the service <idl>com.sun.star.sheet.Scenarios</idl> 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 <idl>com.sun.star.sheet.ScenariosEnumeration</idl>).

The interface <idl>com.sun.star.sheet.XScenarios</idl> 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 <idl>com.sun.star.sheet.Spreadsheet</idl> implements the interface <idl>com.sun.star.sheet.XScenario</idl> 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 <idl>com.sun.star.style.XStyleFamiliesSupplier</idl>'s method getStyleFamilies(). The general handling of styles is described in this section, therefore this chapter focuses on the spreadsheet specific style properties.

Note pin.svg

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 <idl>com.sun.star.sheet.TableCellStyle</idl>. 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 <idl>com.sun.star.container.XNamed</idl>.

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

Create new style with gray background - Java  

// get the cell style container
com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier =
    (com.sun.star.style.XStyleFamiliesSupplier) 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", new Integer(0x888888));
xPropSet.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));

Page Styles

A page style is represented by the service <idl>com.sun.star.sheet.TablePageStyle</idl>. It contains the service <idl>com.sun.star.style.PageStyle</idl> and additional spreadsheet specific page properties.

TablePageStyle

The properties LeftPageFooterContent, LeftPageHeaderContent, RightPageFooterContent and RightPageHeaderContent return the interface <idl>com.sun.star.sheet.XHeaderFooterContent</idl> for the headers and footers for the left and right pages. Headers and footers are represented by the service <idl>com.sun.star.sheet.HeaderFooterContent</idl>. 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 <idl>com.sun.star.text.XText</idl> of these text portions.

Note pin.svg

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 <idl>com.sun.star.sheet.FunctionAccess</idl> 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 <idl>com.sun.star.sheet.XFunctionAccess</idl> 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 <idl>com.sun.star.sheet.XFunctionAccess</idl> 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 pin.svg

Note:
The implementation of <idl>com.sun.star.sheet.FunctionAccess</idl> 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 <idl>com.sun.star.sheet.FunctionDescriptions</idl> and <idl>com.sun.star.sheet.FunctionDescription</idl> 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 <idl>com.sun.star.sheet.FunctionDescriptions</idl> service is instantiated through the service manager. It provides three different methods to access the information for the different functions:

  • By name through the <idl>com.sun.star.container.XNameAccess</idl> interface.
  • By index through the <idl>com.sun.star.container.XIndexAccess</idl> interface.
  • By function identifier through the <idl>com.sun.star.sheet.XFunctionDescriptions</idl> interface's getById() method. The function identifier is the same used in the <idl>com.sun.star.sheet.RecentFunctions</idl> service.

The <idl>com.sun.star.sheet.FunctionDescription</idl> that is returned by any of these calls is a sequence of <idl>com.sun.star.beans.PropertyValue</idl> 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 <idl>com.sun.star.sheet.FunctionArgument</idl> 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 pin.svg

Note:
All of the strings contained in the <idl>com.sun.star.sheet.FunctionDescription</idl> 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 <idl>com.sun.star.sheet.FunctionAccess</idl> service.

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

Recently Used Functions

The <idl>com.sun.star.sheet.RecentFunctions</idl> 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 <idl>com.sun.star.sheet.XRecentFunctions</idl> interface's getRecentFunctionIds() method returns a sequence of function identifiers that are used with the <idl>com.sun.star.sheet.FunctionDescriptions</idl> 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 <idl>com.sun.star.sheet.RecentFunctions</idl> and <idl>com.sun.star.sheet.FunctionDescriptions</idl> 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 <idl>com.sun.star.sheet.GlobalSheetSettings</idl> service contains settings that affect the whole spreadsheet application. It can be instantiated through the service manager. The properties are accessed using the <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.sheet.SpreadsheetView</idl> service is the spreadsheet's extension of the <idl>com.sun.star.frame.Controller</idl> service and represents a table editing view for a spreadsheet document.

SpreadsheetView
Note pin.svg

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 <idl>com.sun.star.frame.XController</idl>, <idl>com.sun.star.frame.XDispatchProvider</idl> and <idl>com.sun.star.ui.XContextMenuInterception</idl> interfaces work as described in that chapter.

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

The <idl>com.sun.star.sheet.XSpreadsheetView</idl> 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 <idl>com.sun.star.sheet.XViewSplitable</idl> 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 <idl>com.sun.star.sheet.XViewFreezable</idl> 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 <idl>com.sun.star.container.XIndexAccess</idl> 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 <idl>com.sun.star.sheet.SpreadsheetViewPane</idl> service's interfaces directly with the <idl>com.sun.star.sheet.SpreadsheetView</idl> service that inherits them.

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

The following example uses the <idl>com.sun.star.sheet.XViewFreezable</idl> 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 <idl>com.sun.star.sheet.SpreadsheetViewPane</idl> 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 <idl>com.sun.star.sheet.XViewPane</idl> interface's getFirstVisibleColumn(), getFirstVisibleRow(), setFirstVisibleColumn() and setFirstVisibleRow() methods query and set the start of the exposed area. The getVisibleRange() method returns a <idl>com.sun.star.table.CellRangeAddress</idl> 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 <idl>com.sun.star.sheet.XCellRangeReferrer</idl> interface gives direct access to the same cell range of exposed cells that are addressed by the getVisibleRange() return value.

The <idl>com.sun.star.view.XControlAccess</idl> 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", new Boolean(false));
xRangeProp.setPropertyValue("CellBackColor", new Integer(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 <idl>com.sun.star.sheet.SpreadsheetViewSettings</idl> service are accessed through the <idl>com.sun.star.beans.XPropertySet</idl> 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 <idl>com.sun.star.sheet.SpreadsheetViewObjectsMode</idl>.

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", new Boolean(true));
xProp.setPropertyValue("GridColor", new Integer(0x00CC00));

Range Selection

The view's <idl>com.sun.star.sheet.XRangeSelection</idl> 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 <idl>com.sun.star.sheet.XRangeSelectionListener</idl> interface. Its done() or aborted() method is called when the selection is finished or aborted. The <idl>com.sun.star.sheet.RangeSelectionEvent</idl> 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 <idl>com.sun.star.sheet.XRangeSelectionChangeListener</idl> 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 <idl>com.sun.star.sheet.RangeSelectionArguments</idl> 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 = new 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 <idl>com.sun.star.sheet.AddIn</idl> 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 <idl>com.sun.star.sheet.AddIn</idl> service and the usual interfaces every component has to support.

Function Descriptions

The methods from the <idl>com.sun.star.sheet.XAddIn</idl> 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 pin.svg

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 <idl>com.sun.star.sheet.AddIn</idl> 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 <idl>com.sun.star.lang.XServiceInfo</idl> 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 <idl>com.sun.star.lang.XServiceName</idl> interface, and in its getServiceName() method return the name of the function-specific service.

Compatibility Names

Optionally, the component can implement the <idl>com.sun.star.sheet.XCompatibilityNames</idl> 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 <idl>com.sun.star.lang.XTypeProvider</idl> 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 <idl>com.sun.star.sheet.XVolatileResult</idl>, such as the getCounter() function from the example interface above. The function's implementation must return an object that implements the <idl>com.sun.star.sheet.VolatileResult</idl> 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.Hashtable aResults = new java.util.Hashtable();

public com.sun.star.sheet.XVolatileResult getCounter(String aName) {
    ExampleAddInResult aResult = (ExampleAddInResult) 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 <idl>com.sun.star.sheet.XVolatileResult</idl> interface to maintain a list of listeners, and notify each of these listeners by calling the <idl>com.sun.star.sheet.XResultListener</idl> interface's modified() method whenever a new result is available. The <idl>com.sun.star.sheet.ResultEvent</idl> 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 String aName;
    private int nValue;
    private java.util.Vector aListeners = new java.util.Vector();

    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 + " " + String.valueOf(nValue);
        aEvent.Source = this;
        return aEvent;
    }

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

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

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

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

        java.util.Enumeration aEnum = aListeners.elements();
        while (aEnum.hasMoreElements())
        ((com.sun.star.sheet.XResultListener)aEnum.nextElement()).modified(aEvent);
    }
}

Heckert GNU white.svg

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