Sancho: Основной текст

2011-09-02T18:05:09Z

Основной текст

New page

<section begin=title /><noinclude>{{DISPLAYTITLE:</noinclude>Cells and Ranges<noinclude>}}</noinclude><section end=title />{{BreadCrumbL10n}}

{{Documentation/BASICGuideTOC
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=EN/documentation/basic_guide/rows_and_columns
|NextPage=EN/documentation/basic_guide/formatting_spreadsheet_documents
|spread=block
}}

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:

<source lang="oobas">
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"
</source>

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 <tt>A1</tt>. The letter <tt>A</tt> 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:

<source lang="oobas">
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"
</source>

The above code also works with a named cell.

In {{OOo}}, 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 <tt>Value</tt> property, text with the <tt>String</tt> property, and formulas with the <tt>Formula</tt> property.

<source lang="oobas">
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"
</source>

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

{{SO5note|The <tt>Value</tt>, <tt>String</tt>, and <tt>Formula</tt> properties supersede the old <tt>PutCell</tt> method of StarOffice 5 for setting the values of a table cell.}}

{{OOo}} treats cell content that is entered using the <tt>String</tt> 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:

<source lang="oobas">
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
</source>

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 <tt>Type</tt> property:

<source lang="oobas">
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
</source>

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

;<tt>EMPTY</tt>:no value
;<tt>VALUE</tt>:number
;<tt>TEXT</tt>:strings
;<tt>FORMULA</tt>:formula

== Inserting, Deleting, Copying and Moving Cells ==

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

The <tt>insertCell</tt> method is used to insert cells into a sheet.

<source lang="oobas">
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)
</source>

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 <idl>com.sun.star.table.CellRangeAddress</idl> structure. The following values are included in this structure:

;<tt>Sheet (short)</tt>:number of the sheet (numbering begins with 0).
;<tt>StartColumn (long)</tt>:first column in the cell range (numbering begins with 0).
;<tt>StartRow (long)</tt>:first row in the cell range (numbering begins with 0).
;<tt>EndColumn (long)</tt>:final column in the cell range (numbering begins with 0).
;<tt>EndRow (long)</tt>:final row in the cell range (numbering begins with 0).

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

;<tt>NONE</tt>:the current values remain in their present position.
;<tt>DOWN</tt>:the cells at and under the insert position are moved downwards.
;<tt>RIGHT</tt>:the cells at and to the right of the insert position are moved to the right.
;<tt>ROWS</tt>:the rows after the insert position are moved downwards.
;<tt>COLUMNS</tt>:the columns after the insert position are moved to the right.

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

<source lang="oobas">
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)
</source>

This example removes the <tt>B2:C3</tt> 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 <idl>com.sun.star.sheet.CellDeleteMode</idl> enumeration:

;<tt>NONE</tt>:the current values remain in their current position.
;<tt>UP</tt>:the cells at and below the insert position are moved upwards.
;<tt>LEFT</tt>:the cells at and to the right of the insert position are moved to the left.
;<tt>ROWS</tt>:the rows after the insert position are moved upwards.
;<tt>COLUMNS</tt>:the columns after the insert position are moved to the left.

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

<source lang="oobas">
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)
</source>

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

;<tt>Sheet (short)</tt>:number of the spreadsheet (numbering begins with 0).
;<tt>Column (long)</tt>:number of the addressed column (numbering begins with 0).
;<tt>Row (long)</tt>:number of the addressed row (numbering begins with 0).

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

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

{{VBAnote|In terms of their function, the {{OOo}} Basic <tt>insertCell</tt>, <tt>removeRange</tt>, and <tt>copyRange</tt> methods are comparable with the VBA <tt>Range.Insert</tt>, <tt>Range.Delete</tt> ,and <tt>Range.Copy</tt> methods. Whereas in VBA, the methods are applied to the corresponding <tt>Range</tt> object, in {{OOo}} Basic they are applied to the associated <tt>Sheet</tt> object.}}

{{PDL1}}

EN/documentation/basic guide/cells and ranges - Revision history

Sancho: Основной текст