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.
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)
|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:
- 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) 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.
|Content on this page is licensed under the Public Documentation License (PDL).|