Sheet Cell Ranges
Sheet Cell Ranges
In Writer documents, continuous text can be grouped in a text range. In a spreadsheet, cells can be grouped in rectangular regions with a SheetCellRange. Grouping cells together allows multiple cells to be operated on at one time. The SheetCellRange service supports many of the same interfaces and properties as a SheetCell.
| Tip | Each sheet in a spreadsheet document is also a SheetRange. |
Each cell has a cell address (see Table 3) that identifies its location in the spreadsheet document. Each cell range has an analogous structure that identifies its location in the spreadsheet (see Table 9 ).
| Property | Description |
|---|---|
| Sheet | Short Integer index of the sheet that contains the cell. |
| StartColumn | Long Integer index of the column where the left edge is located. |
| StartRow | Long Integer index of the row where the top edge is located. |
| EndColumn | Long Integer index of the column of the right edge of the range. |
| End Row | Long Integer index of the row of the bottom edge of the range. |
A single sheet cell range exists in a single sheet and contains one rectangular region. Multiple ranges are encapsulated by the SheetCellRanges service, which supports most of the same properties and services as a SheetCellRange. The similarity in functionality simplifies the learning curve and offers a lot of otherwise complicated functionality.
The SheetCellRanges service provides access to each range through the use of the XElementAccess interface and the XIndexAccess interface. The methods in Table 10 also provide access to the contained cell ranges.
| Method | Description |
|---|---|
| getCells() | Return the collection of contained cells as an XEnumerationAccess. |
| getRangeAddressesAsString() | Return a string with the addresses of all contained ranges. The output format is similar to "Sheet1.B2:D6;Sheet3.C4:D5". |
| getRangeAddresses() | Return an array of services of type CellRangeAddress (see Table 9). |
Sheet Cell Range Properties
Sheet cell ranges and sheet cells both support the properties Position, Size, ConditionalFormat, ConditionalFormatLocal, Validation, and ValidationLocal (see Table 7). The Position property for a sheet cell range provides the location of the upper-left cell-this is equivalent to obtaining the position property from the upper-left cell in the range. The Size property for a cell returns the size of a single cell, and the Size property for a sheet cell range provides the size for all of the cells contained in the range.
Validation Settings
Sheet cells and sheet cell ranges are able to validate the data that they contain, to prevent invalid data from populating the cells. You can display a dialog when invalid data is entered (see Table 12). The service com.sun.star.sheet.TableValidation controls the validation process.
Before demonstrating how a validation is performed, I must introduce a few enumerated values, properties, and methods. Specify the validation that is performed by using the enumerated values shown in Table 11 .
| Value | Description |
|---|---|
| com.sun.star.sheet.ValidationType.ANY | All content is valid; no conditions are used. |
| com.sun.star.sheet.ValidationType.WHOLE | Compare a whole (integer) number against the specified condition. |
| com.sun.star.sheet.ValidationType.DECIMAL | Compare any number against the specified condition. |
| com.sun.star.sheet.ValidationType.DATE | Compare a date value against the specified condition. |
| com.sun.star.sheet.ValidationType.TIME | Compare a time value against the specified condition. |
| com.sun.star.sheet.ValidationType.TEXT_LEN | Compare a string length against the specified condition. |
| com.sun.star.sheet.ValidationType.LIST | Only allow strings in the specified list. |
| com.sun.star.sheet.ValidationType.CUSTOM | Specify a formula that determines if the contents are valid. |
When a cell containing invalid data is found, the ValidationAlertStyle enum specifies how the invalid data should be handled (see Table 12 ). Table 13 lists the supported conditional operators.
| Value | Description |
|---|---|
| com.sun.star.sheet.ValidationAlertStyle.STOP | Display an error message and reject the change. |
| com.sun.star.sheet.ValidationAlertStyle.WARNING | Display a warning message and ask the user if the change will be accepted. The default answer is No. |
| com.sun.star.sheet.ValidationAlertStyle. INFO | Display an information message and ask the user if the change will be accepted. The default answer is Yes. |
| com.sun.star.sheet.ValidationAlertStyle.MACRO | Execute a specified macro. |
| Value | Description |
|---|---|
| com.sun.star.sheet.ConditionOperator.NONE | No condition is specified. |
| com.sun.star.sheet.ConditionOperator.EQUAL | The value must be equal to the specified value. |
| com.sun.star.sheet.ConditionOperator.NOT_EQUAL | The value must not be equal to the specified value. |
| com.sun.star.sheet.ConditionOperator.GREATER | The value must be greater than the specified value. |
| com.sun.star.sheet.ConditionOperator.GREATER_EQUAL | The value must be greater than or equal to the specified value. |
| com.sun.star.sheet.ConditionOperator.LESS | The value must be less than the specified value. |
| com.sun.star.sheet.ConditionOperator.LESS_EQUAL | The value must be less than or equal to the specified value. |
| com.sun.star.sheet.ConditionOperator.BETWEEN | The value must be between the two specified values. |
| com.sun.star.sheet.ConditionOperator.NOT_BETWEEN | The value must be outside of the two specified values. |
| com.sun.star.sheet.ConditionOperator.FORMULA | The specified formula must have a non-zero result. |
The validation object defines the type of validation and how to react to the validation using properties of the object (see Table 14 ).
| Property | Description |
|---|---|
| Type | The type of validation to perform, as shown in Table 11. |
| ShowInput Message | If True, an input message appears when the cursor is in an invalid cell. |
| InputTitle | Title (String) of the dialog with the input message. |
| InputMessage | Text (String) of the input message. |
| ShowErrorMessage | If True, an error message appears when invalid data is entered. |
| ErrorTitle | Title (String) of the dialog showing the error message. |
| ErrorMessage | Text (String) of the error message. |
| IgnoreBlankCells | If True, blank cells are allowed. |
| ErrorAlertStyle | The action that is taken when an error occurs, as shown in Table 12. |
Finally, the comparison that is performed is specified using methods implemented by the TableValidation service (see Table 15 ).
| Method | Description |
|---|---|
| getOperator() | Get the operator used in the condition, as shown in Table 13. |
| setOperator(condition) | Set the operator used in the condition, as shown in Table 13. |
| getFormula1() | Get the comparison value (String) used in the condition, or the first value if two are needed. |
| setFormula1(String) | Set the comparison value used in the condition, or the first value if two are required. |
| getFormula2() | Get the second value (String) if two are required. |
| setFormula2(String) | Set the second value (String) if two are required. |
| getSourcePosition() | Get the CellAddress that is used as a base for relative references in the formulas (see Table 3). |
| setSourcePosition(CellAddress) | Set the CellAddress that is used as a base for relative references in the formulas (see Table 3). |
The macro in Listing 11 sets a validation range in the fourth sheet-that is, the sheet with a numerical index of 3. The cells from B2 to D6 are set to disallow any values that are not between 1 and 10. The macro itself is unexpectedly simple.
 |
Sub SetValidationRange Dim oRange 'Range that will accept the validation Dim oValidation 'The validation object REM A little error handling, making certain that there are enough sheets If ThisComponent.Sheets.getCount() < 4 Then MsgBox "This macro requires at least four sheets", 48, "Warning" Exit Sub End If REM Sheets support returning a cell range based on UI type names. oRange = ThisComponent.Sheets(3).getCellRangeByName("B2:D6") REM Obtain the Validation object oValidation = oRange.Validation REM Configure the validation to perform oValidation.Type = com.sun.star.sheet.ValidationType.DECIMAL oValidation.ErrorMessage = "Please enter a number between one and ten" oValidation.ShowErrorMessage = True oValidation.ErrorAlertStyle = com.sun.star.sheet.ValidationAlertStyle.STOP oValidation.setOperator(com.sun.star.sheet.ConditionOperator.BETWEEN) oValidation.setFormula1(1.0) oValidation.setFormula1(10.0) REM Now set the validation oRange.Validation = oValidation End Sub | |
Conditional Formatting
Conditional formatting allows the cell style to change based on the cell's content. Sheet cells and sheet cell ranges both support the ConditionalFormat property, which in turn supports the XSheetConditionalEntries interface. You can access conditional formatting entries by using element access, indexed access, or the methods addNew(properties()), clear(), and removeBylndex(index).
You can also apply multiple conditional formatting entries to the same cell. The first one that matches is applied. Each formatting entry is represented by an array of PropertyValue structures. Conditional formatting is very similar to validation in that they both use values and types from Table 11 through Table 15.
Validation is a relatively simple condition on a data element, enforcing type and format constraints. Conditional validation supports validation and a more extended set of checks, including attributes or metadata defined on elements or collections of elements-the names are similar, but the actual operations, applications, and implications are much more elaborate. It is more difficult to explain than it is to demonstrate . The macro in Listing 12 sets a range of cells to use the "Heading1" style if the cell contains a negative number.
| |
Sub SetConditionalStyle Dim oRange 'Cell range to use Dim oConFormat 'Conditional format object Dim oCondition(2) As New com.sun.star.beans.PropertyValue REM A little error handling, making certain that there are enough sheets If ThisComponent.Sheets.getCount() < 4 Then MsgBox "This macro requires at least four sheets", 48, "Warning" Exit Sub End If REM Sheets support returning a cell range based on UI type names. oRange = ThisComponent.Sheets(3).getCellRangeByName("B2:D6") REM Obtain the Validation object oConFormat = oRange.ConditionalFormat oCondition(0).Name = "Operator" oCondition(0).Value = com.sun.star.sheet.ConditionOperator.LESS oCondition(1).Name = "Formula1" oCondition(1).Value = 0 oCondition(2).Name = "StyleName" oCondition(2).Value = "Heading1" oConFormat.addNew(oCondition()) oRange.ConditionalFormat = oConFormat End Sub | |
Sheet Cell Range Services
Sheet cells and sheet cell ranges have numerous services in common-for example, CellProperties (see Table 6), CharacterProperties, CharacterPropertiesAsian, CharacterPropertiesComplex, ParagraphProperties, and SheetRangesQuery.
Retrieving Cells and Ranges
SheetCellRanges support the CellRange service, which in turn supports CellProperties (see Table 6). The CellRange service offers extra functionality that is appropriate for cell ranges, but not for individual cells-for example, retrieving cells and cell ranges. When a cell range is retrieved using the methods in Table 16 , the cells are indexed relative to the top-left corner of the range. When the range is an entire sheet, the location (0, 0) refers to the cell "A1." If the range includes the cells "B2:D6", however, the location (0,0) refers to cell "B2." The macros in Listing 7 through Listing 10 all use the method getCellByPosition().
| Method | Description |
|---|---|
| getCellByPosition(left, top) | Get a cell within the range. |
| getCellRangeByPosition(left, top, right, bottom) | Get a cell range within the range. |
| getCellRangeByName(name) | Get a cell range within the range based on its name. The string directly references cells using the standard formats-such as "B2:D5" or "$B$2"-or defined cell range names. |
| Note | The methods getCellByPosition(), getCellRangeByPosition(), and getCellRangeByName() cannot return a value that is not in the range (see Listing 37). |
Querying Cells
Sheet cell ranges and sheet cells both support the ability to find cells with specific properties. This ability provides a mechanism for finding all cells that are referenced by the current cell's formula and the ability to see which cells reference the current cell. While performing a query based on the cell's content, the CellFlags in Table 17 specify the content type to search.
| Value | Flag | Description |
|---|---|---|
| 1 | com.sun.star.sheet.CellFlags.VALUE | Select numbers not formatted as dates or times. |
| 2 | com.sun.star.sheet.CellFlags.DATETIME | Select numbers formatted as dates or times. |
| 4 | com.sun.star.sheet.CellFlags.STIRING | Select strings. |
| 8 | com.sun.star.sheet.CellFIags.ANNOTATION | Select cell annotations. |
| 16 | com.sun.star.sheet.CellFlags.FORMULA | Select formulas. |
| 32 | com.sun.star.sheet.CellFlags.HARDATTR | Select explicit formatting-not styles. |
| 64 | com.sun.star.sheet.CellFlags.STYLES | Select cell styles. |
| 128 | com.sun.star.sheet.CellFlags.OBJECTS | Select drawing objects such as buttons and graphics. |
| 256 | com.sun.star.sheet.CellFlags.EDITATTR | Select formatting within the cell's content. |
Each of the methods used to query a cell range (see Table 18 ) also returns a cell range (see Table 10).
| Method | Description |
|---|---|
| queryDependents(boolean) | Return all cells that reference cells in this range. If True, search is recursive. |
| queryPrecedents(boolean) | Return all cells referenced by cells in this range. If True, search is recursive. |
| queryVisibleCells() | Return all visible cells. |
| queryEmptyCells() | Return all empty cells. |
| queryContentCells(CellFlags) | Return all cells with the specified content types (see Table 17). |
| queryFormulaCells(FormulaResult) | Return all cells containing a formula with the specified result type (see FormulaResultType in Table 7). |
| queryColumnDifferences(CellAddress) | Return all cells that differ from the comparison cell in the specified cell's row (see Table 3). |
| queryRowDifferences(CellAddress) | Return all cells that differ from the comparison cell in the specified cell's column (see Table 3). |
| queryIntersection(CellRangeAddress) | Return the range of cells that intersect the specified range (see Table 9). |
Finding Non-Empty Cells in a Range
Use the method queryContentCells(CellFlags) to obtain a list of all cells in a range that are not empty. The CellFlags argument is set to return all cells that contain a value, string, formula, or date/time. The interesting thing about Listing 13 is not that it uses a query to find the nonempty cells, but rather that it demonstrates how to extract the cells from the query and enumerate all of the returned cells. In other words, the macro in Listing 13 demonstrates how to visit all of the nonempty cells in a specific range.
| |
Function NonEmptyCellsInRange(oRange, sep$) As String Dim oCell 'The cell to use! Dim oRanges 'Ranges returned after querying for the cells Dim oAddrs() 'Array of CellRangeAddress Dim oAddr 'One CellRangeAddress Dim oSheet 'Sheet that contains the cell range Dim i As Long 'General index variable Dim nRow As Long 'Row number Dim nCol As Long 'Column number Dim s As String REM First, find the cells that are NOT empty in this range! REM I consider a cell to be not empty if it has a value, REM date/time, string, or formula. oRanges = oRange.queryContentCells(_ com.sun.star.sheet.CellFlags.VALUE OR _ com.sun.star.sheet.CellFlags.DATETIME OR _ com.sun.star.sheet.CellFlags.STRING OR _ com.sun.star.sheet.CellFlags.FORMULA) oAddrs() = oRanges.getRangeAddresses() For i = 0 To UBound(oAddrs()) REM Get a specific address range oAddr = oAddrs(i) For nRow = oAddr.StartRow To oAddr.EndRow For nCol = oAddr.StartColumn To oAddr.EndColumn oCell = oRange.Spreadsheet.getCellByPosition(nCol, nRow) s = s & PrintableAddressOfCell(oCell) & sep$ Next Next Next NonEmptyCellsInRange = s End Function
| |
Using Complex Queries
Although the query methods are easy to use, some of them are conceptually quite complicated. All of the query methods return a SheetCellRanges object. The macro in Listing 14 demonstrates how to find the cells referenced by a formula using the queryPrecedents() method, how to find the cells referencing a particular cell using the queryDependents() method, as well as how to find row and column differences using the queryRowDifferences() and queryColumnDifferences() methods.
| |
Sub QueryRange Dim oCell 'Holds a cell temporarily Dim oCellAddress 'Holds a cell's address Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim i As Integer 'Temporary index variable Dim s As String 'Temporary String REM A little error handling, making certain that there are enough sheets If ThisComponent.Sheets.getCount() < 4 Then MsgBox "This macro requires at least four sheets", 48, "Warning" Exit Sub End If REM Sheets support returning a cell range based on UI type names. oSheet = ThisComponent.Sheets(3) REM Get the range I want to use! oRange = oSheet.getCellRangeByName("A1:F8") REM Now clear the range of all attributes and values using the CellFlags REM Notice that I OR all of the flags together. oRange.clearContents(_ com.sun.star.sheet.CellFlags.VALUE OR _ com.sun.star.sheet.CellFlags.DATETIME OR _ com.sun.star.sheet.CellFlags.STRING OR _ com.sun.star.sheet.CellFlags.ANNOTATION OR _ com.sun.star.sheet.CellFlags.FORMULA OR _ com.sun.star.sheet.CellFlags.HARDATTR OR _ com.sun.star.sheet.CellFlags.STYLES OR _ com.sun.star.sheet.CellFlags.OBJECTS OR _ com.sun.star.sheet.CellFlags.EDITATTR) For i = 1 To 5 oCell = oSheet.getCellByPosition(1, i) 'Cell B2 through B6 oCell.setValue(i) oCell = oSheet.getCellByPosition(2, i) 'Cell C2 through C6 oCell.setFormula("=B" & CStr(i+l) & " + 1") '=B2+1, =B3+1, ... oCell = oSheet.getCellByPosition(3, i) 'Cell D2 through D6 oCell.setFormula("=C" & CStr(i+l) & " - 1") '=C2-1, =C3-1, ... Next oCell = oSheet.getCellByPosition(1, 6) 'B7 oCell.setFormula("=SUM(B2:B5)") 'This ignores B6 oCell = oSheet.getCellByPosition(2, 6) 'C7 oCell.setFormula("=SUM(C2:C6)") 'This includes C6 oCell = oSheet.getCellByPosition(2, 0) 'C1 oCell.setFormula("=B1 - 1") oCell = oSheet.getCellByPosition(1, 0) 'B1 oCell.setValue(2) oCell = oSheet.getCellByPosition(1, 7) 'B8 oCell.setValue(2) oCell = oSheet.getCellByPosition(4, 2) 'E3 oCell.setValue(2) oCell = oSheet.getCellByPosition(4, 7) 'E8 oCell.setValue(2) REM This includes cells "C2:C7". Notice that it includes the cell itself s = s & "=SUM(C2:C6) directly references " &_ oCell.queryPrecedents(False).getRangeAddressesAsString() & CHR$(10) REM This includes cells "B2:B6;C2:C7" s = s & "=SUM(C2:C6) Including indirect references " &_ oCell.queryPrecedents(True).getRangeAddressesAsString() & CHR$(10) REM Find direct and indirect references oCell = oSheet.getCellByPosition(1, 2) 'B3 s = s & "Cells that reference B3 " &_ oCell.queryDependents(True).getRangeAddressesAsString() & CHR$(10) oCellAddress = oCell.CellAddress s = s & "Column differences for B3 " &_ oRange.queryColumnDifferences(oCellAddress).getRangeAddressesAsString() s = s & CHR$(10) s = s & "Row differences for B3 " &_ oRange.queryRowDifferences(oCellAddress).getRangeAddressesAsString() s = s & CHR$(10) MsgBox s, 0, "Manipulating A Range" End Sub | |
The macro in Listing 14 configures the fourth sheet with values and formulas, and then the macro performs some queries. The code that performs the demonstrated queries is simple and short. The code that creates the data that is queried, however, is more complicated and instructive. More specifically , the macro in Listing 14 demonstrates how to clear a range of cells by using the clearContents() method. The dialog produced by Listing 14 is shown in Figure 2 .
Figure 2: Query a range of cells to find references, dependencies, and similarities.
Table 19 shows the formulas and values created by the macro in Listing 14, assists in understanding the output shown in Figure 2. The first line in Figure 2 shows the result of queryPrecedents(False) on a range that contains only cell C7. As shown in Table 19, cell C7 directly references the cells C2:C6 and itself. By calling queryPrecedents (True)-the second line in Figure 2-the cells B2:B6 are added because the cells in column C reference the cells in column B.
| B | C | D | E | |
|---|---|---|---|---|
| 1 | 2 | B1-1 | ||
| 2 | 1 | B2+1 | C2-1 | |
| 3 | 2 | B3+1 | C3-1 | 2 |
| 4 | 3 | B4+1 | C4-1 | |
| 5 | 4 | B5+1 | C5-1 | |
| 6 | 5 | B6+1 | C6-1 | |
| 7 | SUM(B2:B5) | SUM(C2:C6) | ||
| 8 | 2 | 2 |
The method queryDependents(True) provides the third line in Figure 2, which displays all of the cells that reference B3, directly or indirectly. Table 19 shows that B3, B7, and C3 directly reference cell B3, and that cells C7 and D3 indirectly reference cell B3.
The fourth line in Figure 2 lists the "column differences" based on cell B3. While calculating column differences, only the row matters. In other words, the same result is returned if cell A3, C3, D3, or E3 is used instead of cell B3. Figure 3 shows the sheet; the cells that are considered different are marked with a black background. Figure 3 helps to illustrate what is, and is not, considered a difference. By choosing cell B3, all of the cells in each column (of the range) are compared to the cell in row 3 (in the same column). The first column, A, has no cells selected because they are all empty; therefore, all the cells in the column have the same value. In column B, cell B3 contains the constant value of 2. Cells B1 and B8 also contain the constant value 2, so these cells are not considered different. Column C is very interesting in that it does not contain constants, but rather it contains formulas that are similar. The cells C2, C4, C5, and C6 are similar to cell C3. The formulas are effectively the same (see Table 19) in that they all add 1 to the cell to the left. The formula in C1 is not similar to C3 so it is included in the difference list. Column D is similar to column C and column E is similar to column B. I leave it as an exercise for the reader to explain the row differences as shown in Figure 3.
Figure 3: Output from Listing 14 with "column differences" highlighted.
Searching and Replacing
The thing that I find most interesting about searching in a spreadsheet document is that searching is not supported by the document object. Cell object and cell range objects support searching, however. Each sheet is also a sheet range, so it's possible to search an entire sheet. It is not possible, therefore, to search an entire document at one time; you must search each sheet separately. Listing 15 demonstrates searching and replacing text in a single sheet.
| |
Sub SearchSheet Dim oSheet 'Sheet in which to replace Dim oReplace 'Replace descriptor Dim nCount 'Number of times replaced oSheet = ThisComponent.Sheets(3) oReplace = oSheet.createReplaceDescriptor() oReplace.setSearchString("Xyzzy") oReplace.setReplaceString("Something Else") oReplace.SearchWords = False nCount = oSheet.replaceAll(oReplace) MsgBox "Replaced " & nCount End Sub | |
Searching a sheet in a Calc document is almost identical to searching for text in a Writer document. The difference in searching is related to the SearchWords property. The documentation for searching in a spreadsheet clearly states that when the SearchWords property is set to True, it does not search based on whole words. The SearchWords property really indicates that the entire cell must contain the search text-and nothing but the search text. Unfortunately, as of OOo 1.1.0, this is not the case; it has no effect at all.
| Bug | In OOo 1.1.0, the SearchWords flag does not work as documented; it has no effect at all. Be certain to create a test case to determine the behavior for the version that you use. |
Merging Cells
According to the OpenOffice.org Developer's Guide, a range of cells can be merged and unmerged using the merge (Boolean) method-merge(True) merges the range and merge(False) unmerges the range. The Developer's Guide makes no comment on what it means to merge a range, so I performed a few experiments and determined that the behavior was nothing like merging cells in a text table. See Listing 16 . Figure 3 shows the cells before the merge, and Figure 4 shows the cells after the merge.
Figure 4: Merging cells causes the top-left cell to use the entire merged area.
| |
Sub MergeExperiment Dim oCell 'Holds a cell temporarily Dim oRange 'The primary range Dim oSheet 'The fourth sheet REM Merge a range of cells oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("B2:D7") oRange.merge(True) REM Now obtain a cell that was merged REM and I can do it! oCell = oSheet.getCellByPosition(2, 3) 'C4 Print oCell.getValue() End Sub | |
After merging the range B2:D7, cell B2 appears in the area formerly used by the entire range. What is not shown in Figure 4 is that the cells that aren't visible still exist and are accessible; they are simply not displayed. Use the getIsMerged() method to determine if all of the cells in a range are merged.
| Tip | Not shown in Figure 4 is that the cells that are not visible still exist and are accessible; they are simply not displayed. |
Retrieving, Inserting, and Deleting Columns and Rows
By using the methods getColumns() and getRows(), you can retrieve columns and rows covered by both cells and cell ranges. After retrieving the columns for a range or a cell, you can retrieve the individual columns by using the interfaces XElementAccess or XIndexAccess. You can use two additional methods-insertByIndex(index, count) and removeByIndex(index, count)-to insert and remove columns. After obtaining a single column, you can get the column's name by using the getName() method, set cell properties for the entire column, and extract the cells by using the cell range methods in Table 16.
All of the manipulations mentioned for columns-except for getName()-also apply to the rows obtained by using the getRows() method. The difference lies in the properties supported by the individual columns and rows (see Table 20 ).
| Type | Property | Description |
|---|---|---|
| Column | Width | Width of the column (in 0.01 mm) as a Long Integer. |
| Row | Height | Height of the row (in 0.01 mm) as a Long Integer. |
| Column | OptimalWidth | If True, the column always keeps its optimal width. |
| Row | OptimalHeight | If True, the column always keeps its optimal height. |
| Both | IsVisible | If True, the row or column is visible. |
| Both | IsStartOfNewPage | If True, a horizontal (vertical) page break is attached to the column (row). |
| Tip | The properties in Table 20 also apply to Row and Column objects. |
The macro in Listing 17 obtains the range "B6:C9" and then traverses all of the nonempty cells. The hard part is done in the routine NonEmptyCellsInRange() as shown in Listing 13. Listing 17, however, demonstrates how to extract the individual rows from a range. Typically, when writing a macro, you know where the data lies so you simply write code to traverse the data directly. Figure 5 shows the output from Listing 17 when the macro is run using the data shown in Figure 3.
Figure 5: The nonempty cells are displayed from rows 6 through 8.
| |
Sub TraverseRows Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim oRows 'Rows object Dim oRow 'A single row Dim oRowEnum 'Enumerator for the rows Dim s As String 'General String Variable oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("B6:C9") REM I now want to find ALL of the cells that are NOT empty in the REM rows that are related to the range. Notice that I do not want to REM limit myself to cells in the range, but that I am interested in the REM rows. oRows = oRange.getRows() REM Sure, I could access things by index, but you probably expected that! oRowEnum = oRows.createEnumeration() Do While oRowEnum.hasMoreElements() oRow = oRowEnum.nextElement() s = s & NonEmptyCellsInRange(oRow, " ") & CHR$(10) Loop MsgBox s, 0, "Non-Empty Cells In Rows" End Sub | |
Retrieving and Setting Data as an Array
You can quickly and easily obtain all data from a range as an array of arrays by using the getDataArray() method. The data in each cell is returned as either a number or a string. You can also set the data for a range by using the setDataArray() method; just be certain that the array dimensions match the range dimensions (see Listing 18 ).
| |
Sub GetAndSetData Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim oAllData 'Array containing the data Dim s As String 'General string variable Dim i As Integer 'General index variable oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("B6:E8") REM Get the data contained in the range! REM Data from empty cells is included. oAllData = oRange.getDataArray() For i = 0 To UBound(oAllData) REM oAllData(i) is an array, so simply join the data together REM for a quick printing! s = s & " (" & Join(oAllData(i), ") (") & ")" & CHR$(10) Next MsgBox s, 0, "Data In Range" REM Now quickly set some data. oRange = oSheet.getCellRangeByName("F1:G2") oRange.setDataArray(Array(Array(1, "One"), Array(2, "Two"))) End Sub | |
| Tip | As of OOo 1.1.1, the maximum sheet dimensions are 32,000 rows by 256 columns-this number will be increased in future versions. When the sheet is used as a cell range, the maximum dimensions are used. The getDataArray() method attempts to return data for all of these cells and then generates an exception. To access only the used portion of a sheet, create a sheet cell cursor and then use the method gotoEndOfUsedArea() to obtain only the used cells. |
Sheet cell ranges also provide the ability to get and set formulas in bulk using arrays. Use the methods getFormulaArray() and setFormulaArray() to get and set the formulas in a range as an array-these two methods can be a real time saver.
Computing Functions on a Range
It's possible to compute a value based on a specified range (see Listing 13 and Listing 17), but it is tedious . You can use the getDataArray() method (see Listing 18) to quickly obtain all of the data and simply process the data in the nested arrays. To easily apply a simple function to a range, use the object method computeFunction (GeneralFunction). Table 21 lists the functions supported by the GeneralFunction enumeration.
| Value | Description |
|---|---|
| com.sun.star.sheet.GeneralFunction.NONE | Nothing is calculated. |
| com.sun.star.sheet.GeneralFunction.AUTO | Use SUM if all values are numerical; otherwise use COUNT. |
| com.sun.star.sheet.GeneralFunction.SUM | Sum (add) all of the numerical values. |
| com.sun.star.sheet.GeneralFunction.COUNT | Count all of the values. |
| com.sun.star.sheet.GeneralFunction.AVERAGE | Average all of the numerical values. |
| com.sun.star.sheet.GeneralFunction.MAX | Maximum numerical value. |
| com.sun.star.sheet.GeneralFunction.MIN | Minimum numerical value. |
| com.sun.star.sheet.GeneralFunction.PRODUCT | Product (multiplication) of all the numerical values. |
| com.sun.star.sheet.GeneralFunction.COUNTNUMS | Count the numerical values. |
| com.sun.star.sheet.GeneralFunction.STDEV | Standard deviation based on a sample. |
| com.sun.star.sheet.GeneralFunction.STDEVP | Standard deviation based on the entire population. |
| com.sun.star.sheet.GeneralFunction.VAR | Variance based on a sample. |
| com.sun.star.sheet.GeneralFunction.VARP | Variance based on the entire population. |
The macro in Listing 19 demonstrates the use of the compute function. Using the sheet shown in Figure 3 and the macro in Listing 19, there are seven nonempty cells in the range A5:C9.
| |
Sub UseCompute Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim d As Double 'Return value oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("A5:C9") d = oRange.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT) MsgBox "Non-Empty values in A5:C9 = " & d, 0, "ComputFuntion ()" End Sub | |
Clearing Cells and Cell Ranges
In a Calc document, click on a cell and press the Delete key. A dialog opens with a list of things that can be deleted. The ability to delete any combination of things from different types of content or formatting is amazingly powerful and flexible. The types of things that can be deleted are encapsulated by the CellFlags shown in Table 17. CellFlags may be combined using the OR operator and passed to the clearContents(CellFlags) method (see Listing 14).
| Tip | The clearContents() method is supported by cells, cell ranges, and even rows and columns. |
Automatic Data Fill
Listing 14 painfully fills consecutive cells with data. Sheet cell ranges provide a better method for automatically filling a range with data by using the fillAuto(FillDirection, nCount) method. The FillDirection enumerated values shown in Table 22 control how the data is propagated.
| Value | Description |
|---|---|
| com.sun.star.sheet.FillDirection.TO_BOTTOM | Rows are filled from top to bottom. |
| com.sun.star.sheet.FillDirection.TO_RIGHT | Columns are filled from left to right. |
| com.sun.star.sheet.FillDirection.TO_TOP | Rows are filled from bottom to top. |
| com.sun.star.sheet.FillDirection.TO_LEFT | Columns are filled from right to left. |
Use the method fillAuto(FillDirection, nCount) to automatically fill an area. First, select the range that you want to fill. Second, set the initial value that will be incremented by the fillAuto() method. The location of the initial values depend on the direction that will be filled. For example, if using TO_LEFT, the rightmost cells in the range must contain an initial value so that they can be filled to the left.
While filling new values, the fillAuto() method increments the number by 1 while moving to the right or bottom, and decrements the number by 1 while moving to the left or top. If the number is formatted as a time or date, incrementing by 1 adds one day.
| Tip | When a time is incremented using fillAuto(), it is incremented by one day. If the cell is formatted to show only the time, it will appear as though the value does not change-although it has. |
The fillAuto() method uses the last argument, nCount, to determine how many cells to move before entering a new value. A value of 1, therefore, fills every cell as the cursor moves. While filling new cells, the fillAuto() method won't move outside the range used to call fillAuto(). The code snippet in Listing 20 assumes that Sheet 3 contains numerical values in the cell range E11:E20.
| |
oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("E11:N20") oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 1) | |
OOo also supports more complicated fill methods. The FillMode enumeration (see Table 23 ) directs the special functionality provided by the method fillSeries(). The autoFill() method always uses the LINEAR mode to increment the value by one, whereas the fillSeries() method allows for any fill mode.
| Value | Description |
|---|---|
| com.sun.star.sheet.FillMode.SIMPLE | All of the values are the same (constant series). |
| com.sun.star.sheet.FillMode.LINEAR | The values change by a constant increment (arithmetic series). |
| com.sun.star.sheet.FillMode.GROWTH | The values change by a constant multiple (geometric series). |
| com.sun.star.sheet.FillMode.DATE | An arithmetic series for date values. This causes all numbers to be treated as dates, regardless of formatting. |
| com.sun.star.sheet.FillMode.AUTO | The cells are filled from a user-defined list. |
The fillSeries() method recognizes dates and times based on the numerical format used to display them. Using the FillMode DATE forces all numbers to be recognized as dates rather than just numbers that are formatted as dates. When a date is filled, the day, month, or year can be changed as specified by the FillDateMode enumerated values (see Table 24 ).
| Value | Description |
|---|---|
| com.sun.star.sheet.FillDateMode.FILL_DATE_DAY | Increment the day by 1. |
| com.sun.star.sheet.FillDateMode.FILL_DATE_WEEKDAY | Increment the day by 1 but skip Saturday and Sunday. |
| com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH | Increment the month (the day is unchanged). |
| com.sun.star.sheet.FillDateMode.FILL_DATE_YEAR | Increment the year (the day and month are unchanged). |
The method fillSeries(FillDirection, FillMode, FillDateMode, nStep, nEndValue) provides the greatest flexibility for filling values. The nStep value indicates how the value is modified from cell to cell. The final argument specifies a final value not to exceed during the fill. The fillSeries() method will not modify a value outside of the range and stops adding values when passing the end value. While specifying an end value, remember that dates expressed as a regular number are rather large. Without thinking, I attempted to increment a date and I used an end value of 30 but the date did not increment-I needed to use a number closer to 40,000. Listing 21 uses the fillSeries() method to fill a range of dates.
| |
oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("E11:N20") oRange.fillSeries(com.sun.star.sheet.FillDirection.TO_LEFT,_ com.sun.star.sheet.FillMode.LINEAR,_ com.sun.star.sheet.FillDateMode.FILL_DATE_DAY,_ 2, 40000) | |
| Note | The fillAuto() method increments or decrements the value depending on the direction in which the values are filled. The fillSeries() method, however, always uses the nStep value regardless of the direction. |
| Bug | The documentation for the com.sun.star.sheet.XCellSeries interface claims that the final argument for both fillSeries() and fillAuto() specifies how many cells to fill. As of OOo 1.1.0, however, the implementation is different. As specified in the text, the final argument specifies a final value not to exceed during the fill. |
If text containing a number is found in an initial cell, the copied value copies the text and increments the rightmost number in the text. For example, I entered the text "Text 1" and the filled text contained "Text 3", "Text 5", and so on.
Array Formulas
I must admit that I don't completely understand all of the implications and possibilities associated with array formulas. The simplest usage of an array formula that I have seen involves placing an array formula in one cell and using the formula in multiple cells. Now that I have provided just enough detail to cause confusion, consider the simple example shown in Table 25 .
| F | G | H | I | J | K | |
|---|---|---|---|---|---|---|
| 3 | 1 | 3 | =G3+H3 | |||
| 4 | 2 | 4 | =G4+H4 | |||
| 5 | 3 | 5 | =G5+H5 | |||
| 6 | 4 | 6 | =G6+H6 | |||
| 7 | 5 | 7 | =G7+H7 | |||
| 8 | 6 | 8 | =G8+H8 |
Column I contains the formula to add column G to column H. This can be done using an array formula by entering one formula in one cell. To enter an array formula into column J that mimics the formula in column I, first place the cursor in cell J3. Enter the formula "=G3:G8+H3:H8" and then press Ctrl-Shift-Enter. The cells J3 through J8 now contain the single formula "{=G3:G8+H3:H8}" and the values in column J should match those in column I. Column I contains six formulas that are not directly related to each other, but the cells in column J use only one formula. The macro in Listing 22 sets a sheet to look like Table 25 and then sets column J to contain an array formula that calculates the same values as column I.
| |
Sub ArrayFormula Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim oCell 'Holds a cell temporarily Dim i As Integer 'General Index Variable oSheet = ThisComponent.Sheets(3) REM Set the two top cells in G3:H8 oCell = oSheet.getCellByPosition(6, 2) 'Cell G3 oCell.setValue(1) oCell = oSheet.getCellByPosition(7, 2) 'Cell H3 oCell.setValue(3) REM Fill the values down! oRange = oSheet.getCellRangeByName("G3:H8") oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1) REM This demonstrates setting each cell individually. For i = 3 To 8 oCell = oSheet.getCellByPosition(8, i-1) 'Cell 13 - I8 oCell.setFormula("=G" & i & "+H" & i) Next REM Setting a single array formula is much easier in this case. oRange = oSheet.getCellRangeByName("J3:J8") oRange.setArrayFormula("=G3:G8+H3:H8") REM Add some headings! oRange = oSheet.getCellRangeByName("G2:J2") oRange.setDataArray(Array(Array("G", "H", "Formula", "Array Formula"))) End Sub | |
Computing Multiple Functions on a Range
OOo also supports using a series of single variable formulas against a series of values. A typical example of this involves a single column (or row) of numbers with adjacent columns (or rows) containing formulas using the numbers. The enumerated values in Table 26 specify if rows or columns are used.
| Value | Description |
|---|---|
| com.sun.star.sheet.TableOperationMode.COLUMN | Apply the operation down the columns. |
| com.sun.star.sheet.TableOperationMode.ROW | Apply the operation across the rows. |
| com.sun.star.sheet.TableOperationMode.BOTH | Apply the operation to both rows and columns. |
The setTableOperation() method provides the ability to quickly apply multiple single variable functions to the same set of data producing a table of values. The method setTableOperation() accepts four arguments as follows :
-
CellRangeAddress-Cell range address that contains the functions to apply.
-
TableOperationMode-Identifies if the data is in rows or columns (see Table 26).
-
CellAddress-Cell address that is used if using columns (row mode or both).
-
CellAddress-Cell address that is used if using rows (column mode or both).
The macro in Listing 23 generates a column of numbers from 0 to 6.3 in Sheet4. The functions Sin() and Cos() are then applied to the columns.
| |
Sub MultipleOpsColumns Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim oCell 'Holds a cell temporarily Dim oBlockAddress 'Address of the block to fill Dim oCellAddress 'Row or column cell oSheet = ThisComponent.Sheets(3) REM Set the topmost value! oCell = oSheet.getCellByPosition(0, 9) 'Cell A10 oCell.setValue(0) REM Fill the values down! for 0 to about 6.4 oRange = oSheet.getCellRangeByName("A10:A73") oRange.fillSeries(com.sun.star.sheet.FillDirection.TO_BOTTOM,_ com.sun.star.sheet.FillMode.LINEAR,_ com.sun.star.sheet.FillDateMode.FILL_DATE_DAY,_ 0.1, 6.4) REM Now set the Sin() and Cos() Header values oCell = oSheet.getCellByPosition(1, 8) 'Cell B9 oCell.setString("Sin()") oCell = oSheet.getCellByPosition(2, 8) 'Cell C9 oCell.setString("Cos()") REM Now set the Sin() and Cos() formulas oCell = oSheet.getCellByPosition(1, 9) 'Cell B10 oCell.setFormula("=Sin(A10)") oCell = oSheet.getCellByPosition(2, 9) 'Cell C10 oCell.setFormula("=Cos(A10)") REM Obtain the entire block on which to operate. oRange = oSheet.getCellRangeByName("A11:C73") REM Obtain the address that contains the formulas to copy. oBlockAddress = oSheet.getCellRangeByName("B10:C10").getRangeAddress() REM The address of the cell that contains the column of data. oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress() REM I really only need the column value because the row value is not used. oRange.setTableOperation(oBlockAddress,_ com.sun.star.sheet.TableOperationMode.COLUMN,_ oCellAddress, oCellAddress) End Sub | |
If the table operation mode is set to BOTH rather than just ROW or COLUMN, then a single function is applied to two variables . The macro in Listing 24 creates the multiplication table that I memorized in third grade.
| |
Sub MultipleOpsBoth Dim oRowCell 'The row cell Dim oColCell 'The column cell Dim oRange 'The primary range Dim oSheet 'The fourth sheet Dim oCell 'Holds a cell temporarily Dim oBlockAddress 'Address of the block to fill Dim oCellAddress 'Row or column cell oSheet = ThisComponent.Sheets(3) REM Set the row of constant values oRowCell = oSheet.getCellByPosition(1, 9) 'Cell B10 oRowCell.setValue(1) oRange = oSheet.getCellRangeByName("B10:K10") oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_RIGHT, 1) REM Set the column of constant values oColCell = oSheet.getCellByPosition(0, 10) 'Cell A11 oColCell.setValue(1) oRange = oSheet.getCellRangeByName("A11:A20") oRange.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1) REM Set the formula that will be used. It references the first values! oCell = oSheet.getCellByPosition(0, 9) 'Cell A10 oCell.setFormula("=A11*B10") REM Get the range of the cells oRange = oSheet.getCellRangeByName("A10:K20") REM Fill the multiplication tables for the values 1x1 through 10x10 oRange.setTableOperation(oRange.getRangeAddress() ,_ com.sun.star.sheet.TableOperationMode.BOTH,_ oColCell.getCellAddress(),_ oRowCell.getCellAddress()) End Sub | |
Cells with the Same Formatting
Sheet cell ranges provide two methods for obtaining groups of cells that contain the same format. The getCellFormatRanges() method returns an object that supports both index and enumeration access. The equally formatted cells are split into multiple rectangular ranges. The enumerated ranges are returned as a SheetCellRange object.
The object method getUniqueCellFormatRanges() is very similar to the method getCellFormatRanges() except that the returned values are objects of type SheetCellRanges. The primary difference is that all of the similarly formatted objects are returned in one container. The macro in Listing 25 displays the similarly formatted ranges using the two different methods.
| |
Sub DisplaySimilarRanges Dim oSheetCellRange 'An individual sheet cell range Dim oSheetCellRanges 'Sheet cell ranges Dim oAddr 'An address object from the sheet cell range Dim s$ 'Utility string variable Dim x 'The returned range objects are stored here Dim i% 'Utility index variable REM Do this for the entire sheet! x = ThisComponent.Sheets(3).getCellFormatRanges() s = "**** getCellFormatRanges()" & CHR$(10) For i = 0 To x.getcount()-1 oSheetCellRange = x.getByIndex(i) oAddr = oSheetCellRange.getRangeAddress() s = s & i & " = Sheet" & (oAddr.Sheet + 1) & "." &_ ColumnNumberToString(oAddr.StartColumn) & (oAddr.StartRow + 1) &_ ":" & ColumnNumberToString(oAddr.EndColumn) & (oAddr.EndRow + 1) &_ CHR$(10) Next REM SheetCellRanges x = ThisComponent.Sheets(3).getUniqueCellFormatRanges() s = s & CHR$(10) & "**** getUniqueCellFormatRanges()" & CHR$(10) For i = 0 To x.getcount()-1 oSheetCellRanges = x.getByIndex(i) s = s & i & " = "& oSheetCellRanges.getRangeAddressesAsString() & CHR$(10) Next MsgBox s, 0, "Like Ranges" End Sub
| |
Figure 6 demonstrates very clearly the difference between the two methods. The getUniqueCellFormatRanges() method shows that eight of the ranges are formatted similarly (index 0 in Figure 6). Both methods contain the same ranges; it is only a question of grouping.
Figure 6: The two methods group the data differently.
Sorting
In general, OOo knows what type of data a cell contains. Knowledge of the contained data types reduces the need to inform OOo of the type of data that it is sorting, but it is still possible using the TableSortFieldType enumeration (see Table 27 ). When you request a sort operation, an array of TableSortField structures identifies which columns or rows are used to determine the sort order and how they are sorted (see Table 28 ).
| Value | Description |
|---|---|
| com.sun.star.table.TableSortFieldType.AUTOMATIC | Automatically determine the data type. |
| com.sun.star.table.TableSortFieldType.NUMERIC | Sort the data as a number. |
| com.sun.star.table.TableSortFieldType.ALPHANUMERIC | Sort the data as text. |
| Property | Description |
|---|---|
| Field | Zero-based index of the row or column in the table to sort. The index is relative to the start of the sort range. |
| IsAscending | If True, sort the data in ascending order. |
| IsCaseSensitive | If True, the sort is case sensitive. |
| FieldType | Specify the data type as a TableSortFieldType (see Table 27). |
| CollatorLocale | The Locale object to use when sorting text. |
| CollatorAlgorithm | The sorting algorithm used by the collator when sorting text. Check the interface com.sun.star.i18n.XCollator to investigate what algorithms are supported for your locale. I have always used the default value. |
When you request a sort operation, an array of properties is passed to the sort routine. The properties determine what to sort and how it is sorted. One of the supported properties is SortFields (see Table 29 ), which contains the array of TableSortField structures that determine how the rows or columns are sorted.
| Property | Description |
|---|---|
| IsCaseSensitive | If True, the sort is case sensitive. |
| SortAscending | If True, sort the data in ascending order. This property typically is not used, because the TableSortField specifies IsAscending for each field. |
| SortColumns | If True, columns are sorted. If False, rows are sorted. |
| CollatorLocale | The Locale object to use when sorting text (usually set in the TableSortField). |
| CollatorAlgorithm | Sorting algorithm to use (usually set in the TableSortField). |
| SortFields | Array of type TableSortField (see Table 28) that directs what is sorted. |
| MaxSortFieldsCount | Long Integer that specifies the maximum number of sort fields the descriptor can hold. This value cannot be set, but it can be read. |
| ContainsHeader | If True, the first row or column is considered a header and is not sorted. |
| Orientation | This property is deprecated and should no longer be used! |
Tables 28 and 29 reveal that there is a great deal of redundancy. For example, you can specify if the sort is case sensitive either globally (using Table 29) or for each specific field (using Table 28). Although the redundant fields in Table 29 are not required and are therefore typically not used, a new set of sort descriptors has been introduced (see Table 30 ). Although you can use properties from Table 29 or Table 30, you cannot mix the two. My recommendation is that you use the new set in Table 30; the OOo development team has already deprecated the use of the Orientation property in Table 29.
| Property | Description |
|---|---|
| SortFields | Array of type TableSortField (see Table 28) that directs what is sorted. |
| MaxSortFieldsCount | Long Integer that specifies the maximum number of sort fields the descriptor can hold. This value cannot be set, but it can be read. |
| IsSortColumns | If True, columns are sorted. If False, rows are sorted. |
| BindFormatsToContent | If True, cell formats are moved with the contents during the sort. This property matters only if different cells in the sort range use different formatting. |
| IsUserListEnabled | If True, a user-defined sorting list is used from the GlobalSheetSettings. |
| UserListIndex | Specify which user-defined sorting list is used as a Long Integer. |
| CopyOutputData | If True, the sorted data is copied to another position in the document. |
| OutputPosition | CellAddress that specifies where to copy the sorted data (if CopyOutputData is True). |
| ContainsHeader | If True, the first row or column is considered a header and is not sorted. |
The first step in sorting a range is to define the fields on which to sort by using an array of type SortField. Next, define the properties from Table 30 that you intend to use in the sort. Finally, call the sort() routine on the range to sort. The macro in Listing 26 performs a descending sort on the first column.
| |
Sub SortColZero Dim oSheet Dim oRange Dim oSortFields(0) as new com.sun.star.util.SortField Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue oSheet = ThisComponent.Sheets(3) REM Set the range on which to sort oRange = oSheet.getCellRangeByName("B28:D33") REM Sort on the first field in the range oSortFields(0).Field = 0 oSortFields(0).SortAscending = FALSE REM Set the sort fields to use oSortDesc(0).Name = "SortFields" oSortDesc(0) .Value = oSortFields() REM Now sort the range! oRange.Sort(oSortDesc()) End Sub | |
Sorting on two columns rather than just one is as easy as adding a second sort field. Listing 27 sorts on the second and third columns.
| |
Sub SortColOne Dim oSheet Dim oRange Dim oSortFields(1) as new com.sun.star.util.SortField Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue oSheet = ThisComponent.Sheets(3) REM Set the range on which to sort oRange = oSheet.getCellRangeByName("B28:D33") REM Sort on the second field in the range oSortFields(0).Field = 1 oSortFields(0).SortAscending = True oSortFields(0).FieldType = com.sun.star.util.SortFieldType.NUMERIC REM Sort on the third field in the range oSortFields(1).Field = 2 oSortFields(1).SortAscending = True oSortFields(1) .FieldType = com.sun.star.util.SortFieldType.ALPHANUMERIC REM Set the sort fields to use oSortDesc(0).Name = "SortFields" oSortDesc(0).Value = oSortFields() REM Now sort the range! oRange.Sort(oSortDesc()) End Sub | |
The method create SortDescriptor() returns an array of property values that define how a sort should occur. Inspecting this created sort descriptor indicates that you can use a maximum of three fields when sorting (see the MaxSortFieldsCount in Table 30). The macro in Listing 28 creates a sort descriptor and then displays the properties that it contains (see Figure 7 ).
Figure 7: Sort descriptor attributes.
| |
Sub DisplaySortDescriptor On Error Resume Next Dim oSheet Dim oRange Dim oSortDescript Dim i% Dim s$ oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("B28:D33") oSortDescript = oRange.createSortDescriptor() For i = LBound(oSortDescript) To UBound(oSortDescript) s = s & oSortDescript(i).Name & " = " s = s & oSortDescript(i).Value s = s & CHR$(10) Next MsgBox s, 0, "Sort Descriptor" End Sub | |
EAN: 2147483647
Pages: 203
- Chapter V Consumer Complaint Behavior in the Online Environment
- Chapter IX Extrinsic Plus Intrinsic Human Factors Influencing the Web Usage
- Chapter XII Web Design and E-Commerce
- Chapter XIII Shopping Agent Web Sites: A Comparative Shopping Environment
- Chapter XVIII Web Systems Design, Litigation, and Online Consumer Behavior