Editing Spreadsheet Documents

From Wiki
Jump to navigationJump to 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), OpenOffice.org 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)
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 + _
      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. fr:FR/Documentation/BASIC Guide/Editing Spreadsheet Documents hu:HU/Documentation/BASIC Guide/Editing Spreadsheet Documents it:IT/Documentation/BASIC Guide/Editing Spreadsheet Documents ja:JA/Documentation/BASIC Guide/Editing Spreadsheet Documents zh:ZH/Documentation/BASIC Guide/Editing Spreadsheet Documents


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



Retrieved from "https://wiki.harlamenkov.ru/index.php?title=Documentation/BASIC_Guide/Editing_Spreadsheet_Documents&oldid=20799"