Editing Spreadsheet Documents

From Apache OpenOffice Wiki
< Documentation‎ | BASIC Guide
Revision as of 13:51, 30 January 2021 by DiGro (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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 (com.sun.star.table.Cell service), Apache OpenOffice also provides objects that represent cell ranges. Such CellRange objects are created using the getCellRangeByName call of the spreadsheet object:

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")

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:

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)

The arguments of getCellRangeByPosition 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 getCellByPosition 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.

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)

Formatting Cell Ranges

Just like individual cells, you can apply formatting to cell ranges using the com.sun.star.table.CellProperties service. For more information and examples of this service, see Formatting Spreadsheet Documents.

Computing With Cell Ranges

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

sum of all numerical values
total number of all values (including non-numerical values)
total number of all numerical values
average of all numerical values
largest numerical value
smallest numerical value
product of all numerical values
standard deviation
standard deviation based on the total population
variance based on the total population

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

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)
Documentation caution.png Functions VAR, VARP, STDVERP return an incorrect value when applied to a properly defined range. See Issue 22625 .

Deleting Cell Contents

The clearContents 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 B2:C3 range.

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 + _

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

numerical values that are not formatted as date or time
numerical values that are formatted as date or time
comments that are linked to cells
direct formatting of cells
indirect formatting
drawing objects that are connected to cells
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 clearContents.

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 Sheets list. The following is an example of a search and replace process:

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)
Next I

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

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