Using the Current Controller
Each OOo document contains a controller that interacts with the user . Therefore, the current controller knows what text is selected, the location of the current cursor, and which sheet is active.
Selected Cells
The document's controller interacts with the user and therefore knows which cells are selected. In a Calc document, a selected cell can be a few different things. The following cases are numbered to simply reference each case; the numbering serves no other purpose.
-
One cell selected. To select an entire cell, click in a cell once and then hold down the Shift key and click in the cell again.
-
Partial text in a single cell selected. Double-click in a single cell and then select some text.
-
Nothing appears selected. Single-click in a cell or tab between cells.
-
Multiple cells selected. Single-click in a cell and then drag the cursor.
-
Multiple disjointed selections. Select some cells. Hold down the Ctrl key and select some more.
So far, I have not been able to distinguish between the first three cases; they all look like one cell is selected. If only one cell is selected, the current selection returned by the current controller is the sheet cell containing the cursor. If a single cell is selected (cases 1 through 3), the selections object supports the SheetCell service (see Listing 41 ).
 |
Function CalcIsAnythingSelected(oDoc) As Boolean Dim oSelections REM Assume that nothing is selected. CalcIsAnythingSelected = False If IsNull(oDoc) Then Exit Function REM The current selection in the current controller. REM If there is no current controller, this returns NULL. oSelections = oDoc.getCurrentSelection() If IsNull(oSelections) Then Exit Function If oSelections.supportsService("com.sun.star.sheet.SheetCell") Then Print "One Cell selected = " & oSelections.getImplementationName() MsgBox "getString() = " & oSelections.getString() ElseIf oSelections.supportsService("com.sun.star.sheet.SheetCellRange") Then Print "One Cell Range selected = " & oSelections.getImplementationName() ElseIf oSelections.supportsService("com.sun.star.sheet.SheetCellRanges") Then Print "Multiple Cell Ranges selected = " &_ oSelections.getImplementationName() Print "Count = " & oSelections.getCount() Else Print "Something else selected = " & oSelections.getImplementationName() End If CalcIsAnythingSelected = True End Function | |
If multiple cells are selected as a single range (case 4), the selections object is a SheetCellRange. Check to see if the selections object supports the SheetCellRange service. If so, then more than one cell range is selected. Use the getCount() method of the selections object to see how many ranges are selected.
Enumerating the Selected Cells
Listing 42 is a utility routine that sets the text of the cells in a range to a specific value. Although Listing 42 is specifically designed to operate on a cell range, it uses methods that are also supported by a single cell. The macro, therefore, may be used by a cell or a cell range object. The method used in Listing 42 is a modification of an algorithm introduced to me by Sasa Kelecevic, on the OOo dev mailing list.
| |
Sub SetRangeText(oRange, s As String) Dim nCol As Long 'Column index variable Dim nRow As Long 'Row index variable Dim oCols 'Columns in the selected range Dim oRows 'Rows in the selected range oCols = oRange.Columns : oRows = oRange.Rows For nCol = 0 To oCols.getCount() - 1 For nRow = 0 To oRows.getCount() - 1 oRange.getCellByPosition(nCol, nRow).setString(s) Next Next End Sub
| |
To demonstrate how to inspect all of the selected cells, the macro in Listing 43 sets the text in every selected cell to a specific text value.
| |
Sub SetSelectedCells(s As String) Dim oSelections 'The selections object Dim oCell 'If one cell is selected Dim oRanges 'If multiple ranges are selected, use this Dim i As Long 'General index variable REM The current selection in the current controller. REM If there is no current controller, this returns NULL. oSelections = ThisComponent.getCurrentSelection() If IsNull(oSelections) Then Exit Sub If oSelections.supportsService("com.sun.star.sheet.SheetCell") Then oCell = oSelections oCell.setString(s) REM For consistency, I could use the same call for a range as a cell REM but this is only because a cell can also return the columns and rows. REM SetRangeText(oSelections, s) ElseIf oSelections.supportsService("com.sun.star.sheet.SheetCellRange") Then SetRangeText(oSelections, s) ElseIf oSelections.supportsService("com.sun.star.sheet.SheetCellRanges") Then oRanges = oSelections For i = 0 To oRanges.getCount() - 1 SetRangeText(oRanges.getByIndex(i), s) Next Else Print "Something else selected = " & oSelections.getImplementationName() End If End Sub | |
While writing the macro in Listing 43, I experienced some initially perplexing behavior. My initial version of Listing 43 would sometimes generate an error as it attempted to enter the subroutine, depending on the selected values. The run-time error would complain that an unsupported property or invalid value was used. To explain the problem (and the solution), notice that there are two places in the code where I assign oSelections to a temporary variable and then I use the variable. Listing 44 contains the small sections of code extracted from Listing 43.
| |
oCell = oSelections oCell.setString(s) ..... oRanges = oSelections For i = 0 To oRanges.getCount() - 1 SetRangeText(oRanges.getByIndex(i), s) Next
| |
If oSelections refers to a cell, it doesn't support the object method getCount(). If oSelections refers to a SheetCellRanges object, it doesn't support the setString() object method. I can but assume that the OOo Basic interpreter attempts to resolve the properties and methods that an object references when it is assigned a value. For example, if the selections object is a cell, an error is raised because a cell doesn't support the getCount() method. On the other hand, if more than one cell is selected, the returned object does not support the setString() method. Although this is not the first time that I have experienced this problem, it is the first time that I determined the nature of the problem and how to avoid it.
Selecting Text
The current controller is used to determine the current selection, and it can also be used to set the current selection. Use the current controller's select(obj) method to select cells in a sheet. The documentation essentially says that if the controller recognizes and is able to select the object passed as an argument, it will. Listing 45 demonstrates how to select cells.
| |
Dim oSheet, oRange, oCell, oController oController = ThisComponent.getCurrentController() oSheet = ThisComponent.Sheets(3) oRange = oSheet.getCellRangeByName("B28:D33") oController.select(oRange) oCell = oSheet.getCellByPosition(0, 0) oController.select(oCell) | |
The Active Cell
The active cell contains the cursor. There is an active cell even when multiple cells are selected. Unfortunately, OOo does not provide a method to return the active cell when more than one cell is selected. Paolo Mantovani posted a very nice solution to this problem on the dev mailing list as shown in Listing 46 . The disadvantage to the macro in Listing 46 is that the active cell is no longer active after the macro runs.
| |
REM Author: Paolo Mantovani REM email: mantovani.paolo@tin.it Sub RetrieveTheActiveCell() Dim oOldSelection 'The original selection of cell ranges Dim oRanges 'A blank range created by the document Dim oActiveCell 'The current active cell Dim oConv 'The cell address conversion service Dim oDoc oDoc = ThisComponent REM store the current selection oOldSelection = oDoc.CurrentSelection REM Create an empty SheetCellRanges service and then select it. REM This leaves ONLY the active cell selected. oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") oDoc.CurrentController.Select(oRanges) REM Get the active cell! oActiveCell = oDoc.CurrentSelection oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion") oConv.Address = oActiveCell.getCellAddress Print oConv.UserInterfaceRepresentation print oConv.PersistentRepresentation REM Restore the old selection, but loose the previously active cell oDoc.CurrentController.Select(oOldSelection) End Sub | |
General Functionality
When searching for view- related functionality, the current controller is a good place to start. Table 52 and Table 53 contain most of the methods and properties supported by the current controller that have not already been discussed.
| Methods | Description |
|---|---|
| getActiveSheet() | Get the active spreadsheet. |
| setActiveSheet(XSpreadsheet) | Cause the specified sheet to become active. |
| getIsWindowSplit() | Return True if the view is split. |
| getSplitHorizontal() | Long Integer horizontal split position (in pixels). |
| getSplitVertical() | Long Integer vertical split position (in pixels). |
| getSplitColumn() | Long Integer column before which the view is split. |
| getSplitRow() | Long Integer row before which the view is split. |
| splitAtPosition(x, y) | Split the view at the specified position. If x=0, the split is only horizontal. If y=0, the split is only vertical. |
| hasFrozenPanes() | True if the view contains frozen panes. |
| freezeAtPosition(nCol, nRow) | Freeze panes with the specified number of columns and rows. |
| getFirstVisibleColumn() | Get the first visible column in the pane as a Long Integer. |
| setFirstVisibleColumn(Long) | Set the first visible column in the pane. |
| getFirstVisibleRow() | Get the first visible row in the pane as a Long Integer. |
| setFirstVisibleRow(Long) | Set the first visible row in the pane. |
| getVisibleRange() | Get the visible range in the pane as a CellRangeAddress. |
| Property | Description |
|---|---|
| ShowFormulas | If True, formulas are displayed instead of their results. |
| ShowZeroValues | If True, zero values are visible. |
| IsValueHighlightingEnabled | If True, strings, values, and formulas are displayed in different colors. |
| ShowNotes | If True, a marker is shown for notes in cells. |
| HasVerticalScrollBar | If True, a vertical scroll bar is used in the view. |
| HasHorizontalScrollBar | If True, a horizontal scroll bar is used in the view. |
| HasSheetTabs | If True, sheet tabs are used in the view. |
| IsOutlineSymbolsSet | If True, outline symbols are displayed. |
| HasColumnRowHeaders | If True, column and row headers are visible. |
| ShowGrid | If True, cell grid lines are displayed. |
| GridColor | Grid color as a Long Integer. |
| ShowHelpLines | If True, help lines are displayed while moving drawing objects. |
| ShowAnchor | If True, anchor symbols are displayed when drawing objects are selected. |
| ShowPageBreaks | If True, page breaks are displayed. |
| SolidHandles | If True, solid (colored) handles are displayed when drawing objects are selected. |
| ShowObjects | If True, embedded objects are displayed. |
| ShowCharts | If True, charts are displayed. |
| ShowDrawing | If True, drawing objects are displayed. |
| HideSpellMarks | If True, spelling marks are not displayed. |
| ZoomType | Document zoom type as a com.sun.star.view.DocumentZoomType with the following values:
|
| ZoomValue | Zoom value if the ZoomType is set to BY_VALUE. |
EAN: 2147483647
Pages: 203