Sheet Cells Contain the Data
Sheet Cells Contain the Data
A spreadsheet document contains individual sheets that are composed of rows and columns of cells. Each column is labeled alphabetically starting with the letter A, and each row is labeled numerically starting with the number 1. A cell can be identified by its name , which uses the column letter and the row number, or by its position. The upper-left cell is "Al" at position (0, 0) and cell "B3" is at location (1, 2).
The Calc user interface identifies cells with names such as "Sheet2:D5". A cell, on the other hand, identifies itself by the row and column offset, which requires a bit of work to turn into human-readable form. I assume that readers of this text are content to obtain the cell address as a row and an offset. The computer, on the other hand, is not; and it requires that formulas reference cells based on the human-readable form (see Listing 4 ).
 |
'Return cell address in a human-readable form. 'For the sheet name, obtain the sheet directly and get its name. 'I could also use the sheet index from the CellAddress. 'The row number is easy; simply add 1. 'Converting the column from an index is more difficult. Function PrintableAddressOfCell(oCell) As String If IsNull(oCell) OR IsEmpty(oCell) Then PrintableAddressOfCell = "Unknown" Else PrintableAddressOfCell = oCell.getSpreadSheet().getName & ":" &_ ColumnNumberToString(oCell.CellAddress.Column) &_ Cstr(oCell.CellAddress.Row+1) End If End Function ' Columns are numbered starting at 0, where 0 corresponds to A ' Columns are named as A-Z,AA-AZ,BA-BZ,...,IV. ' This is esentially a question of how to convert a Base 10 number to ' a base 26 number. ' Note that nColumn is passed by value! If nColumn is not explicitly passed ' by value, it is passed by reference. This macro modifies the argument. ' Strange things happen in BASIC when you modify a constant value passed ' by reference. Function ColumnNumberToString(ByVal nColumn As Long) As String Dim s As String Do While nColumn >= 0 s = Chr$(65 + (nColumn MOD 26)) & s REM Notice the use of integer division. If the "/" operator is used REM then the number is rounded and an incorrect value is returned. nColumn = nColumn \ 26 - 1 Loop ColumnNumberToString = s End Function
| |
| Note | The com.sun.star.table.CellAddressConversion service was introduced in OOo version 1.1.1 |
The CellAddressConversion service converts a cell address into human readable form. The CellAddressConversion service is not yet documented, so I performed some tests. When a cell address is assigned to the Address property (See Listing 5 ), the PersistentRepresentation property is set to the full cell name including the sheet name. The UserInterfaceRepresentation property, however, contains the sheet name only if the cell is not contained in the active sheet.
| |
Dim oConv Dim oCell oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion") oCell = ThisComponent.Sheets(2).getCellByPosition(0, 0) 'Cell A1 oConv.Address = oCell.getCellAddress() Print oConv.UserInterfaceRepresentation 'A1 print oConv.PersistentRepresentation 'Sheet1.A1 | |
Cell Address
In OOo, a cell's address is specified by the sheet that contains the cell, and the row and column in which the cell is located. OOo encapsulates a cell's address in a CellAddress structure (see Table 3 ). The CellAddress structure is available directly from a cell and it is also used as an argument to numerous object methods .
| Property | Description |
|---|---|
| Sheet | Short Integer index of the sheet that contains the cell. |
| Column | Long Integer index of the column where the cell is located. |
| Row | Long Integer index of the row where the cell is located. |
Cell Data
A cell can contain four types of data. Use the method getType() to find out the type of data that it contains. A cell that contains no data is considered empty. A cell can contain a floating-point Double value. Use the object methods getValue() and setValue(Double) to get and set a cell's value.
| Note | If the cell contains a formula, you can still determine the type of data that is stored in the cell from the FormulaResultType property shown in Table 7. |
A cell can contain textual data. The standard method of getting and setting textual data is to use the methods getString() and setString(String). The real story, however, is that the com.sun.star.table.Cell service implements the com.sun.star.text.XText interface. The XText interface is the primary text interface used in Writer documents, and it allows individual cells to contain very complex data.
| Tip | Sheet cells support the com.sun.star.text.XText interface. It should come as no surprise, therefore, that sheets also support the com.sun.star.text.XTextFieldsSupplier interface-in case you want to insert special text fields into a cell. |
A cell can contain a formula. The methods getFormula() and setFormula(String) get and set a cell's formula. To determine if a formula contains an error, use the getError() method-the Long Integer return value is zero if there is no error. The macro in Listing 6 demonstrates how to inspect a cell's type.
| |
Function GetCellType(oCell) As String Select Case oCell.getType() Case com.sun.star.table.CellContentType.EMPTY GetCellType = "Empty" Case com.sun.star.table.CellContentType.VALUE GetCellType = "Value" Case com.sun.star.table.CellContentType.TEXT GetCellType = "Text" Case com.sun.star.table.CellContentType.FORMULA GetCellType = "Formula" Case Else GetCellType = "Unknown" End Select End Function
| |
| Tip | When setting a cell's formula, you must include the leading equals sign (=) and the formula must be in English. To set a formula using your own local language, use the FormulaLocal property shown in Table 7. |
Listing 7 demonstrates how to get and set information in a cell. A numeric value, a string, and a formula are set in a cell. After setting each type, information is printed about the cell (see Figure 1 ). The macro in Listing 7 is very simple but it demonstrates some very important behavior. Inspect the output in Figure 1 to see what is returned by getString(), getValue(), and getFormula() for each different cell content type.
Figure 1: Values returned by getType(), getString(), getValue(), and getFormula() for different types of content.
| |
Function SimpleCellInfo(oCell) As String Dim s As String SimpleCellInfo = PrintableAddressOfCell(oCell) & " has type " &_ GetCellType(oCell) & " String(" & oCell.getString() & ") Value(" &_ oCell.getValue() & ") Formula(" & oCell.getFormula() & ")" End Function Sub GetSetCells Dim oCell Dim s As String oCell = ThisComponent.Sheets(0).getCellByPosition(0, 0) 'Cell A1 oCell.setString("Andy") oCell = ThisComponent.Sheets(0).getCellByPosition(104, 0) 'Cell DA1 s = SimpleCellInfo(oCell) & CHR$(10) oCell.setValue(23.2) s = s & SimpleCellInfo(oCell) & CHR$(10) oCell.setString("4") s = s & SimpleCellInfo(oCell) & CHR$(10) oCell.setFormula("=A1") s = s & SimpleCellInfo(oCell) & CHR$(10) oCell.setFormula("") s = s & SimpleCellInfo(oCell) & CHR$(10) MsgBox s, 0, "Cell Values And Types" End Sub | |
| Tip | The methods getString() and getFormula() return relevant values even when the cell type is not String or Formula (see Figure 1). Notice also that setting a string value of 4 does not set a numeric value, and the formula even shows a single quotation mark in front of the "4". This provides a pretty good clue that you can also do things such as setFormula("'I am text") to set text. |
Cell Properties
Cells contained in a sheet are defined by the com.sun.star.sheet.SheetCell service, which supports numerous properties for formatting the cell contents. Given that a cell also supports the Text service, it is of no surprise that it also supports properties related to text content: CharacterProperties, CharacterPropertiesAsian, CharacterPropertiesComplex, and ParagraphProperties. There are also cell-specific properties such as setting border lines. You specify the border lines for cells by using a BorderLine structure as shown in Table 4 . The BorderLine structure defines how a single border line is displayed, and the TableBorder structure defines how the lines in an entire table are displayed (see Table 5 ).
| Property | Description |
|---|---|
| Color | Line color as a Long Integer. |
| InnerLineWidth | Width of the inner part of a double line (in 0.01 mm) as a Short Integer-zero for a single line. |
| OuterLineWidth | Width of a single line, or width of the outer part of a double line (in 0.01 mm) as a Short Integer. |
| LineDistance | Distance between the inner and outer parts of a double line (in 0.01 mm) as a Short Integer. |
| Property | Description |
|---|---|
| TopLine | Line style at the top edge (see Table 4). |
| IsTopLineValid | If True, the TopLine is used when setting values. |
| BottomLine | Line style at the bottom edge (see Table 4). |
| IsBottomLineValid | If True, the BottomLine is used when setting values. |
| LeftLine | Line style at the left edge (see Table 4). |
| IsLeftLineValid | If True, the LeftLine is used when setting values. |
| RightLine | Line style at the right edge (see Table 4). |
| IsRightLineValid | If True, the RightLine is used when setting values. |
| HorizontalLine | Line style for horizontal lines between cells (see Table 4). |
| IsHorizontalLineValid | If True, the HorizontalLine is used when setting values. |
| VerticalLine | Line style for vertical lines between cells (see Table 4). |
| IsVerticalLineValid | If True, the VerticalLine is used when setting values. |
| Distance | Distance between the lines and other contents as a Short Integer. |
| IsDistanceValid | If True, the Distance is used when setting values. |
When setting values in a TableBorder structure, not all values are always required. For example, when using a TableBorder structure to configure a cell's border, the individual values are used only if the corresponding "Is...Valid" property is set. This provides the ability to set a single value and leave the other values unchanged. If, on the other hand, a TableBorder structure is obtained by using a query (meaning you get the value), the flags indicate that not all lines use the same value.
Table 6 contains cell-specific properties. Cell properties use the TableBorder structure to set the border types.
| Property | Description |
|---|---|
| CellStyle | Optional property; the name of the style of the cell as a String. |
| CellBackColor | The cell background color as a Long Integer (see IsCellBackgroundTransparent). |
| IsCellBackgroundTransparent | If True, the cell background is transparent and the CellBackColor is ignored. |
| HoriJustify | The cell's horizontal alignment as a com.sun.star.table.CellHoriJustify enum:
|
| VertJustify | The cell's vertical alignment as a com.sun.star.table.CellVertJustify enum:
|
| IsTextWrapped | If True, the cell's content is automatically wrapped at the right border. |
| ParaIndent | The indentation of the cell's content (in 0.01 mm) as a Short Integer. |
| Orientation | If the RotateAngle is zero, this specifies the orientation of the cell's content as an enum of type com.sun.star.table.CellOrientation:
|
| RotateAngle | Defines how much to rotate the cell's content (in 0.01 degrees) as a Long Integer. The entire string is rotated as one unit rather than as individual characters . |
| RotateReference | Defines the edge where rotated cells are aligned using the same enum as VertJustify. |
| AsianVerticalMode | If True, only Asian characters use a vertical orientation. In other words, in Asian mode only Asian characters are printed in horizontal orientation if the Orientation property is STACKED; for other orientations, this value is not used. |
| TableBorder | Description of the cell or cell range border (see Table 5). When used with a single cell, the values set the borders for the single cell. When used with a range of cells, the borders are for the outer edges of the range rather than the individual cells. |
| TopBorder | Description of the cell's top border line (see Table 4). |
| BottomBorder | Description of the cell's bottom border line (see Table 4). |
| LeftBorder | Description of the cell's left border line (see Table 4). |
| RightBorder | Description of the cell's right border line (see Table 4). |
| NumberFormat | Index of the cell's number format. The proper value can be determined by using the com.sun.star.util.XNumberFormatter interface supported by the document. |
| ShadowFormat | Defines the shadow format using a com.sun.star.table.ShadowFormat structure:
|
| CellProtection | Defines the cell's protection as a com.sun.star.util.CellProtection structure:
|
| UserDefinedAttributes | This property is used to store additional attributes in a com.sun.star.container.XNameContainer interface. |
In general, the process of setting cell attributes is very simple. The macro in Listing 8 demonstrates some of the properties in Table 6 by setting the text of cell B1 to "Hello", centering the text, and then rotating the text 30 degrees counterclockwise.
| |
Dim oCell oCell = ThisComponent.Sheets(0).getCellByPosition(1, 0) 'Cell B1 oCell.setString("Hello") oCell.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER oCell.RotateAngle = 3000 '30 degrees | |
Although setting attributes is generally straightforward, the UserDefinedAttributes property seems to be a source of confusion. User-defined attributes allow you to add your own properties that are then saved along with the document. OpenOffice.org stores documents as XML. When an OOo document is read, it is parsed by an XML parser. User-defined attributes are not interpreted by the parser, but they are simply read, stored, and then written back again when the file is saved. Although this is very simple to do, it is usually not done correctly and therefore it does not work. The most common problem seems to be attempting to use the UserDefinedAttbributes property directly (see Listing 9 ).
| |
Dim oCell 'The cell that will contain the attribute Dim oUserData 'A copy of the UserDefinedAttributes Dim oMyAttribute As New com.sun.star.xml.AttributeData REM First, get the cell that will contain the new attribute oCell = ThisComponent.Sheets(0).getCellByPosition(1, 0) 'Cell B1 REM Now add data to the attribute. REM The Namespace is usually left blank, but set it if you want to. REM omyAttribute.Namespace = "http://what/ever/you/want" REM Notice that the type is CDATA and not something like "String" oMyAttribute.Type = "CDATA" oMyAttribute.Value = "Andrew Pitonyak" REM Here is where things usually go wrong with a statement like REM oCell.UserDefinedAttributes.insertByName("Andy", oMyAttribute) REM This fails every time. Instead, first make a copy and then REM operate on the copy. oUserData = oCell.UserDefinedAttributes If NOT oUserData.hasByName("Andy") Then oUserData.insertByName("Andy", oMyAttribute) oCell.UserDefinedAttributes = oUserData End If | |
| Tip | Learn to use UserDefinedAttributes with cells, and you can use them on the other services that support them. I learned to manipulate user-defined attributes from Niklas Nebel, who is on the OpenOffice.org team at Sun Microsystems. |
Cell properties (see Table 6) are generic to most types of cells including text table cells. Cells in a spreadsheet contain other properties as well (see Table 7 ).
| Property | Description |
|---|---|
| Position | The position of the cell in the sheet (in 0.01 mm) as a com.sun.star.awt.Point structure. This is the absolute position in the entire sheet, not the position in the visible area.
|
| Size | The size of the cell (in 0.01 mm) as a com.sun.star.awt.Size structure:
|
| FormulaLocal | Optional string containing the formula using localized function names. |
| FormulaResultType | Formula result type with values from the com.sun.star.sheet.FormulaResult constant group :
|
| ConditionalFormat | The conditional formatting settings for this cell. When a conditional format is changed, it must be reinserted into the property set. |
| ConditionalFormatLocal | Optional property duplicating ConditionalFormat except that formula names use local names. |
| Validation | The data validation settings for this cell as a com.sun.star.beans.XPropertySet. |
| ValidationLocal | Optional property duplicating Validation except that formula names use local names. |
The macro in Listing 10 demonstrates how to obtain a cell's position and size in a sheet.
| |
Sub CellDimensions Dim oCell Dim s As String oCell = ThisComponent.Sheets(0).getCellByPosition(1, 0) 'Cell B1 s = s & CStr(oCell.Position.X \ 100) & " mm from the left" & CHR$(10) s = s & CStr(oCell.Position.Y \ 100) & " mm from the top" & CHR$(10) s = s & CStr(oCell.Size.Width \ 100) & " mm wide" & CHR$(10) s = s & CStr(oCell.Size.Height \ 100) & " mm tall" & CHR$(10) MsgBox s, 0, "Size And Postion Of Cell " & PrintableAddressOfCell(oCell) End Sub
| |
Cell Annotations
Each cell can contain one annotation that consists of simple unformatted text. The cell's getAnnotation() method returns an object that supports the com.sun.star.sheet.CellAnnotation service. Cell annotations support the XSimpleText interface used in Writer documents as well as the more specific methods shown in Table 8 .
| Method | Description |
|---|---|
| getParent() | Get the object (cell) containing this annotation. |
| setParent(oCell) | Set the cell that contains this annotation. |
| getPosition() | Get the com.sun.star.table.CellAddress of the cell containing this annotation (see Table 3). |
| getAuthor() | Get the user who last changed the annotation as a String. |
| getDate() | Get the date the annotation was last changed as a formatted String. |
| getIsVisible() | Returns True if the annotation is visible. |
| setIsVisible(boolean) | Set whether the annotation is visible. |
The spreadsheet object supports the method getAnnotations(), which returns all of the annotations in the spreadsheet. You can obtain individual annotations by using indexed access or enumeration. The object returned by getAnnotations() also supports the methods removeByIndex(n) and insertNew(CellAddress, String).
EAN: 2147483647
Pages: 203