View source for Documentation/BASIC Guide/Editing Spreadsheet Documents

{{Documentation/BASICGuideTOC/v2
|ShowPrevNext=block
|ShowPrevPage=block
|PrevPage=Documentation/BASIC Guide/Structure of Spreadsheets
|NextPage=Documentation/BASIC Guide/Drawings Presentations
|spread=block
}}
{{DISPLAYTITLE:Editing Spreadsheet Documents}}

Whereas the previous section described the main structure of spreadsheet documents, this section describes the services that allow you to easily access individual cells or cell ranges.

== Cell Ranges ==
In addition to an object for individual cells (<idl>com.sun.star.table.Cell</idl> service), {{OOo}} also provides objects that represent cell ranges. Such <tt>CellRange</tt> objects are created using the <tt>getCellRangeByName</tt> call of the spreadsheet object:

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("A1:C15")
</source>

A colon (:) is used to specify a cell range in a spreadsheet document. For example, A1:C15 represents all the cells in rows 1 to 15 in columns A, B, and C.

If the position of the cell range is only known at runtime, use the following code:

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByPosition(0, 0,  2, 14)
</source>

The arguments of [http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XCellRangesAccess.html#getCellRangeByPosition <code>getCellRangeByPosition</code>] are the position of the upper left cell of the range, followed by the position of the bottom right cell of the same range.

The location of individual cells in a cell range can be determined using the <tt>getCellByPosition</tt> method, where the coordinates of the top left cell in the cell range is (0, 0). The following example uses this method to create an object of cell C3.

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim Cell As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("B2:D4")
Cell = CellRange.GetCellByPosition(1, 1)
</source>

=== Formatting Cell Ranges ===
Just like individual cells, you can apply formatting to cell ranges using the <idl>com.sun.star.table.CellProperties</idl> service. For more information and examples of this service, see [[Documentation/BASIC Guide/Formatting Spreadsheet Documents|Formatting Spreadsheet Documents]].

=== Computing With Cell Ranges ===
You can use the <tt>computeFunction</tt> method to perform mathematical operations on cell ranges. The <tt>computeFunction</tt> expects a constant as the parameter that describes the mathematical function that you want to use. The associated constants are defined in the <idl>com.sun.star.sheet.GeneralFunction</idl> enumeration. The following values are available:

;<tt>SUM</tt>:sum of all numerical values
;<tt>COUNT</tt>:total number of all values (including non-numerical values)
;<tt>COUNTNUMS</tt>:total number of all numerical values
;<tt>AVERAGE</tt>:average of all numerical values
;<tt>MAX</tt>:largest numerical value
;<tt>MIN</tt>:smallest numerical value
;<tt>PRODUCT</tt>:product of all numerical values
;<tt>STDEV</tt>:standard deviation
;<tt>VAR</tt>:variance
;<tt>STDEVP</tt>:standard deviation based on the total population
;<tt>VARP</tt>:variance based on the total population

The following example computes the average value of the <tt>A1:C3</tt> range and prints the result in a message box:

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object

Doc = ThisComponent
Sheet = Doc.Sheets.getByName("Sheet 1")
CellRange = Sheet.getCellRangeByName("A1:C3")

MsgBox CellRange.computeFunction(com.sun.star.sheet.GeneralFunction.AVERAGE)
</source>

{{Documentation/Caution|Functions VAR, VARP, STDVERP return an incorrect value when applied to a properly defined range. See {{bug|22625}}.}}

=== Deleting Cell Contents ===
The <tt>clearContents</tt> method simplifies the process of deleting cell contents and cell ranges in that it deletes one specific type of content from a cell range.

The following example removes all the strings and the direct formatting information from the <tt>B2:C3</tt> range.

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim CellRange As Object
Dim Flags As Long

Doc = ThisComponent
Sheet = Doc.Sheets(0)
CellRange = Sheet.getCellRangeByName("B2:C3")

Flags = com.sun.star.sheet.CellFlags.STRING + _
      com.sun.star.sheet.CellFlags.HARDATTR

CellRange.clearContents(Flags)
</source>

The flags specified in <tt>clearContents</tt> come from the <idl>com.sun.star.sheet.CellFlags</idl> constants list. This list provides the following elements:

;<tt>VALUE</tt>:numerical values that are not formatted as date or time
;<tt>DATETIME</tt>:numerical values that are formatted as date or time
;<tt>STRING</tt>:strings
;<tt>ANNOTATION</tt>:comments that are linked to cells
;<tt>FORMULA</tt>:formulas
;<tt>HARDATTR</tt>:direct formatting of cells
;<tt>STYLES</tt>:indirect formatting
;<tt>OBJECTS</tt>:drawing objects that are connected to cells
;<tt>EDITATTR</tt>:character formatting that only applies to parts of the cells

You can also add the constants together to delete different information using a call from <tt>clearContents</tt>.

== Searching and Replacing Cell Contents ==
Spreadsheet documents, like text documents, provide a function for searching and replacing.

The descriptor objects for searching and replacing in spreadsheet documents are not created directly through the document object, but rather through the <tt>Sheets</tt> list. The following is an example of a search and replace process:

<source lang="oobas">
Dim Doc As Object
Dim Sheet As Object
Dim ReplaceDescriptor As Object
Dim I As Integer

Doc = ThisComponent
Sheet = Doc.Sheets(0)

ReplaceDescriptor = Sheet.createReplaceDescriptor()
ReplaceDescriptor.SearchString = "is"
ReplaceDescriptor.ReplaceString = "was"
For I = 0 to Doc.Sheets.Count - 1
   Sheet = Doc.Sheets(I)
   Sheet.ReplaceAll(ReplaceDescriptor) 
Next I
</source>

This example uses the first page of the document to create a <tt>ReplaceDescriptor</tt> and then applies this to all pages in a loop.

 
{{InterWiki Languages BasicGuide|articletitle=Documentation/BASIC Guide/Editing Spreadsheet Documents}}
{{PDL1}}