Werkbladdocumenten bewerken
- De structuur van werkbladdocumenten
- Werkbladdoucmenten bewerken
Waar het voorgaande gedeelte de hoofdstructuur van werkbladdocumenten beschreef, beschrijft dit gedeelte de services die u in staat stellen eenvoudig toegang te krijgen tot individuele cellen of celbereiken.
Contents
Celbereiken
In aanvulling op een object voor individuele cellen (service com.sun.star.table.Cell), verschaft Apache OpenOffice ook objecten die celbereiken vertegenwoordigen. Zulke objecten CellRange worden gemaakt met behulp van de aanroep getCellRangeByName van het object werkblad:
Dim Doc As Object Dim Blad As Object Dim CelBereik As Object Doc = ThisComponent Blad = Doc.Sheets.getByName("Blad 1") CelBereik = Blad.getCellRangeByName("A1:C15")
Een dubbele punt (:) wordt gebruikt om een celbereik in een werkbladdocument te specificeren. Bijvoorbeeld, A1:C15 vertegenwoordigt alle cellen in de rijen 1 tot en met 15 in de kolommen A, B, en C.
Als de positie va het celbereik allen tijdens runtime bekend is, gebruik dan de volgende code:
Dim Doc As Object Dim Blad As Object Dim CelBereik As Object Doc = ThisComponent Blad = Doc.Sheets.getByName("Blad 1") CelBereik = Blad.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.
| Content on this page is licensed under the Public Documentation License (PDL). |
