Difference between revisions of "Documentation/BASIC Guide/Cells and Ranges"

Latest revision as of 13:28, 30 January 2021

>]]

A spreadsheet consists of a two-dimensional list containing cells. Each cell is defined by its X and Y-position with respect to the top left cell which has the position (0,0).

Addressing and Editing Individual Cells

The following example creates an object that references the top left cell and inserts a text in the cell:

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(0, 0)
Cell.String = "Test"

In addition to numerical coordinates, each cell in a sheet has a name, for example, the top left cell (0,0) of a spreadsheet is called A1. The letter A stands for the column and the number 1 for the row. It is important that the name and position of a cell are not confused because row counting for names begins with 1 but the counting for position begins with 0.

If the position of the cell is fixed, it is more clear to use the following code:

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellRangeByName("A1")
Cell.String = "Test"

The above code also works with a named cell.

In Apache OpenOffice, a table cell can be empty or contain text, numbers, or formulas. The cell type is not determined by the content that is saved in the cell, but rather the object property which was used for its entry. Numbers can be inserted and called up with the Value property, text with the String property, and formulas with the Formula property.

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object   
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(0, 0)
Cell.Value = 100
 
Cell = Sheet.getCellByPosition(0, 1)
Cell.String = "Test"
 
Cell = Sheet.getCellByPosition(0, 2)
Cell.Formula = "=A1"

The example inserts one number, one text, and one formula in the fields A1 to A3.

StarOffice 5 : The Value, String, and Formula properties supersede the old PutCell method of StarOffice 5 for setting the values of a table cell.

Apache OpenOffice treats cell content that is entered using the String property as text, even if the content is a number. Numbers are left-aligned in the cell instead of right-aligned. You should also note the difference between text and numbers when you use formulas:

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
Cell = Sheet.getCellByPosition(0, 0)
Cell.Value = 100
 
Cell = Sheet.getCellByPosition(0, 1)
Cell.String = 1000
 
Cell = Sheet.getCellByPosition(0, 2)
Cell.Formula = "=A1+A2"
 
MsgBox Cell.Value

Although cell A1 contains the value 100 and cell A2 contains the value 1000, the A1+A2 formula returns the value 100. This is because the contents of cell A2 were entered as a string and not as a number.

To check if the contents of a cell contains a number or a string, use the Type property:

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
Cell = Sheet.getCellByPosition(1,1)
 
Cell.Value = 1000
 
Select Case Cell.Type 
Case com.sun.star.table.CellContentType.EMPTY 
   MsgBox "Content: Empty"
Case com.sun.star.table.CellContentType.VALUE
   MsgBox "Content: Value"
Case com.sun.star.table.CellContentType.TEXT
   MsgBox "Content: Text"
Case com.sun.star.table.CellContentType.FORMULA
   MsgBox "Content: Formula"
End Select

The Cell.Type property returns a value for the com.sun.star.table.CellContentType enumeration which identifies the contents type of a cell. The possible values are:

EMPTY

no value

VALUE

number

TEXT

strings

FORMULA

formula

Inserting, Deleting, Copying and Moving Cells

In addition to directly modifying cell content, Apache OpenOffice Calc also provides an interface that allows you to insert, delete, copy, or merge cells. The interface (com.sun.star.sheet.XCellRangeMovement) is available through the spreadsheet object and provides four methods for modifying cell content.

The insertCell method is used to insert cells into a sheet.

Dim Doc As Object
Dim Sheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 1
CellRangeAddress.StartRow = 1
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = 2
 
Sheet.insertCells(CellRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN)

This example inserts a cells range that is two rows by two columns in size into the second column and row (each bear the number 1) of the first sheet (number 0) in the spreadsheet. Any existing values in the specified cell range are moved below the range.

To define the cell range that you want to insert, use the com.sun.star.table.CellRangeAddress structure. The following values are included in this structure:

Sheet (short)

number of the sheet (numbering begins with 0).

StartColumn (long)

first column in the cell range (numbering begins with 0).

StartRow (long)

first row in the cell range (numbering begins with 0).

EndColumn (long)

final column in the cell range (numbering begins with 0).

EndRow (long)

final row in the cell range (numbering begins with 0).

The completed CellRangeAddress structure must be passed as the first parameter to the insertCells method. The second parameter of insertCells contains a value of the com.sun.star.sheet.CellInsertMode enumeration and defines what is to be done with the values that are located in front of the insert position. The CellInsertMode enumeration recognizes the following values:

NONE

the current values remain in their present position.

DOWN

the cells at and under the insert position are moved downwards.

RIGHT

the cells at and to the right of the insert position are moved to the right.

ROWS

the rows after the insert position are moved downwards.

COLUMNS

the columns after the insert position are moved to the right.

The removeRange method is the counterpart to the insertCells method. This method deletes the range that is defined in the CellRangeAddress structure from the sheet.

Dim Doc As Object
Dim Sheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 1
CellRangeAddress.StartRow = 1
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = 2
 
Sheet.removeRange(CellRangeAddress, com.sun.star.sheet.CellDeleteMode.UP)

This example removes the B2:C3 cell range from the sheet and then shifts the underlying cells up by two rows. The type of removal is defined by one of the following values from the com.sun.star.sheet.CellDeleteMode enumeration:

NONE

the current values remain in their current position.

UP

the cells at and below the insert position are moved upwards.

LEFT

the cells at and to the right of the insert position are moved to the left.

ROWS

the rows after the insert position are moved upwards.

COLUMNS

the columns after the insert position are moved to the left.

The XRangeMovement interface provides two additional methods for moving (moveRange) or copying (copyRange) cell ranges. The following example moves the B2:C3 range so that the range starts at position A6:

Dim Doc As Object
Dim Sheet As Object
Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
Dim CellAddress As New com.sun.star.table.CellAddress
 
Doc = ThisComponent
Sheet = Doc.Sheets(0)
 
CellRangeAddress.Sheet = 0
CellRangeAddress.StartColumn = 1
CellRangeAddress.StartRow = 1
CellRangeAddress.EndColumn = 2
CellRangeAddress.EndRow = 2
 
CellAddress.Sheet = 0
CellAddress.Column = 0
CellAddress.Row = 5
 
Sheet.moveRange(CellAddress, CellRangeAddress)

In addition to the CellRangeAdress structure, the moveRange method expects a com.sun.star.table.CellAddress structure to define the origin of the move's target region. The CellAddress structure provides the following values:

Sheet (short)

number of the spreadsheet (numbering begins with 0).

Column (long)

number of the addressed column (numbering begins with 0).

Row (long)

number of the addressed row (numbering begins with 0).

The cell contents in the target range are always overwritten by the moveRange method. Unlike in the InsertCells method , a parameter for performing automatic moves is not provided in the moveRange method.

The copyRange method functions in the same way as the moveRange method, except that copyRange inserts a copy of the cell range instead of moving it.

VBA : In terms of their function, the Apache OpenOffice Basic insertCell, removeRange, and copyRange methods are comparable with the VBA Range.Insert, Range.Delete ,and Range.Copy methods. Whereas in VBA, the methods are applied to the corresponding Range object, in Apache OpenOffice Basic they are applied to the associated Sheet object.