Access to Formulas, Values and Errors

OpenOffice.org Developer's Guide

Spreadsheet Documents

< Previous Section

Next Section >

• Example: Adding a New Spreadsheet
• Example: Editing Spreadsheet Cells

• Handling Spreadsheet Documents Files

• Saving Spreadsheet Documents

• Filter Options

• Printing Spreadsheet Documents

• Working With Spreadsheet Documents

• Spreadsheet Services - Overview

• Capabilities of Spreadsheet
• Capabilities of SheetCellRange
• Capabilities of SheetCell
• Capabilities of SheetCellRanges Container
• Capabilities of Columns and Rows

• Spreadsheet
• Cell Ranges

• Properties of Cell Ranges
• Cell and Cell Range Access
• Merging Cell Ranges into a Single Cell
• Column and Row Access
• Data Array
• Absolute Address
• Fill Series
• Operations
• Multiple Operations
• Handling Array Formulas

• Cells

• Properties of SheetCell
• Access to Formulas, Values and Errors
• Access to Text Content
• Absolute Address
• Cell Annotations

• Cell Ranges and Cells Container
• Columns and Rows

• Formatting

• Character and Paragraph Format
• Indentation
• Equally Formatted Cell Ranges
• Table Auto Formats
• Conditional Formats

• Navigating

• Cell Cursor
• Referencing Ranges by Name
• Named Ranges
• Label Ranges
• Querying for Cells with Specific Properties
• Search and Replace

• Sorting
• Database Operations

• Filtering
• Subtotals
• Database Import
• Database Ranges

• Linking External Data

• Sheet Links
• Cell Area Links
• DDE Links

• DataPilot Tables

• Creating a New DataPilot Table
• Modifying a DataPilot Table
• Removing a DataPilot Table

• DataPilot Sources

• Source Object
• Dimensions
• Hierarchies
• Levels
• Members

• Protecting Spreadsheets
• Sheet Outline
• Detective
• Other Table Operations

• Overall Document Features

• Function Handling
• Settings

• Spreadsheet Document Controller

• View Panes
• View Settings
• Range Selection

• Spreadsheet Add-Ins

• Function Descriptions
• Service Names
• Compatibility Names
• Custom Functions
• Variable Results

< Previous Page

Next Page >

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

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

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

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

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

 com.sun.star.beans.XPropertySet xPropSet = null;
 com.sun.star.table.XCell xCell = null;
 
 // *** Access and modify a VALUE CELL ***
 xCell = xRange.getCellByPosition(0, 0);
 // Set cell value.
 xCell.setValue(1234);
 
 // Get cell value.
 double nDblValue = xCell.getValue() * 2;
 xRange.getCellByPosition(0, 1).setValue(nDblValue);
 
 // *** Create a FORMULA CELL and query error type ***
 xCell = xRange.getCellByPosition(0, 2);
 // Set formula string.
 xCell.setFormula("=1/0");
 
 // Get error type.
 boolean bValid = (xCell.getError() == 0);
 // Get formula string.
 String aText = "The formula " + xCell.getFormula() + " is ";
 aText += bValid ? "valid." : "erroneous.";
 
 // *** Insert a TEXT CELL using the XText interface ***
 xCell = xRange.getCellByPosition( 0, 3 );
 com.sun.star.text.XText xCellText = (com.sun.star.text.XText)
     UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell );
 com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
 xCellText.insertString( xTextCursor, aText, false );
 
 // *** Change cell properties ***
 int nValue = bValid ? 0x00FF00 : 0xFF4040;
 xPropSet = (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 OpenOffice.org Calc have to be translated before you can use them. See com.sun.star.util.NumberFormat for the standard formats.

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

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

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