Difference between revisions of "Documentation/BASIC Guide/Editing Spreadsheet Documents"

Latest revision as of 13:51, 30 January 2021

>]]

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

sum of all numerical values

COUNT

total number of all values (including non-numerical values)

COUNTNUMS

total number of all numerical values

AVERAGE

average of all numerical values

MAX

largest numerical value

MIN

smallest numerical value

PRODUCT

product of all numerical values

STDEV

standard deviation

VAR

variance

STDEVP

standard deviation based on the total population

VARP

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)

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 + _
      com.sun.star.sheet.CellFlags.HARDATTR
 
CellRange.clearContents(Flags)

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

VALUE

numerical values that are not formatted as date or time

DATETIME

numerical values that are formatted as date or time

STRING

strings

ANNOTATION

comments that are linked to cells

FORMULA

formulas

HARDATTR

direct formatting of cells

STYLES

indirect formatting

OBJECTS

drawing objects that are connected to cells

EDITATTR

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)
   Sheet.ReplaceAll(ReplaceDescriptor) 
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.