Sheets
Most of a Calc document's functionality is contained in the individual sheets rather than the document as a whole. The interfaces implemented by the spreadsheet document (see Table 1) are primarily related to the document as a whole, rather than the individual sheets.
The sheets in a Calc document implement the SheetCellRange service, which provides extensive functionality. The functionality provided by sheet cell ranges applies to any sheet cell range and is not limited to sheets. In other words, any range is able to use the methods implemented by the SheetCellRange service. The individual sheets support other interfaces that are not directly related to sheet cell ranges (see Table 31 ).
| Interfaces | Description |
|---|---|
| com.sun.star.sheet.XSpreadsheet | Methods to create a cell cursor. |
| com.sun.star.container.XNamed | Access the spreadsheet name . |
| com.sun.star.util.XProtectable | Methods to protect and unprotect the individual sheet. |
| com.sun.star.sheet.XDataPilotTablesSupplier | Access the DataPilot tables with the method getDataPilotTables(). |
| com.sun.star.sheet.XScenariosSupplier | Access the scenarios with the method getScenarios(). |
| com.sun.star.sheet.XSheetAnnotationsSupplier | Access the annotations with the method getAnnotations(). |
| com.sun.star.drawing.XDrawPageSupplier | Access the sheet's draw page with the method getDrawPage(). |
| com.sun.star.table.XTableChartsSupplier | Access the document's chart objects with the method getCharts(). |
| com.sun.star.sheet.XCellRangeMovement | Move cell ranges inside the sheet or to other spreadsheets in this document. |
| com.sun.star.sheet.XPrintAreas | Access to the print-area settings of this sheet. |
| com.sun.star.sheet.XSheetPageBreak | Access and modify the page breaks in this sheet. |
| com.sun.star.sheet.XScenario | Provide methods for a scenario sheet. |
| com.sun.star.sheet.XSheetOutline | Access the row and column outline settings for the sheet. |
| com.sun.star.sheet.XSheetAuditing | Look for linked cells (detective). |
| com.sun.star.sheet.XSheetLinkable | Methods to link to existing sheets in other documents. |
Linking to an External Spreadsheet
An individual sheet may link to a sheet from another spreadsheet document. Linking causes the "link sheet" to act as a container for the "linked sheet." After linking to a sheet, although you can modify the linked sheet in the container, these updates are not propagated back to the original document. If the linked sheet is changed in the original document, the change is not visible in the link document unless the link itself is refreshed. You can link documents by using one of the enumerated values in Table 32 .
| Value | Description |
|---|---|
| com.sun.star.sheet.SheetLinkMode.NONE | The sheet is not linked. |
| com.sun.star.sheet.SheetLinkMode.NORMAL | Copy the entire content including values and formulas. |
| com.sun.star.sheet.SheetLinkMode.VALUE | Copy the content by value; each formula's returned value is copied rather than the formula itself. |
Use the link() method to establish a link with a sheet in another document. Table 33 lists the link-related methods supported by a sheet.
| Method | Description |
|---|---|
| getLinkMode() | Get the sheet's link mode (see Table 32). |
| getLinkMode(SheetLinkMode) | Set the link mode (see Table 32). |
| getLinkUrl() | Get the link URL. |
| setLinkUrl(url) | Set the link URL. |
| getLinkSheetName() | Get the name of the linked sheet. |
| setLinkSheetName(name) | Set the name of the linked sheet. |
| link(url, sheetName, filterName, filterOptions, SheetLinkMode) | Link the sheet to another sheet in another document. |
The macro in Listing 29 creates a sheet named "LinkIt" and then links to a sheet in a specified external document. If the "Linklt" sheet already exists, the link is obtained from the spreadsheet document and the link is refreshed. Refreshing a link causes the data linked into the current document to be updated.
| Note from the author | Listing 29, "LinkASheet", creates a sheet link. How can it be removed without using the menu: Edit/Links? Before investigation, I expected to either dispose the link or remove the link from the container. It turns out that the link can be removed from the container (in a fashion), but the container is really the sheet, not the SheetLinks object. In other words, I inspected three objects before before I found the answer. Each sheet supports the XSheetLinkable interface as is shown in Table 33 on page 355. There are two errors in Table 33 as shown above. If you set the link mode to NONE, then the link is broken. For the listing that you mention in the book, you could add the following code to remove the link oSheets.getByName("LinkIt").setLinkMode (com.sun.star.sheet.SheetLinkMode.NONE) |
 |
Sub LinkASheet Dim oSheets 'The sheets object that contains all of the sheets Dim oSheet 'Individual sheet Dim oSheetEnum 'For accessing by enumeration Dim s As String 'String variable to hold temporary data Dim i As Integer 'Index variable Dim sURL As String 'URL of the document to import Dim oLink 'The link object sURL = "file://C:/My%20Documents/CH15/test.sxc" oSheets = ThisComponent.Sheets If oSheets.hasbyName("LinkIt") Then REM The links are available from the document object REM based on the URL used to load them. oLink = ThisComponent.SheetLinks.getByName(sURL) oLink.refresh() MsgBox "The sheet named LinkIt was refreshed" Exit Sub End If REM Insert the new sheet at the end. oSheets.insertNewByName ("LinkIt", oSheets.getCount()) oSheet = oSheets.getByName("LinkIt") oSheet.link(sURL, "Sheetl", "", "", com.sun.star.sheet.SheetLinkMode.NORMAL) End Sub | |
The first application that I saw for linked sheets was to consolidate a list of various investments that were tracked in different Calc documents. Each of the Calc documents contained a summary sheet for the investments in the document. A single summary document inserted links to the summary page for each of the other investment sheets.
Although linked sheets are nice, they are sometimes overkill. If you don't want to reference an entire sheet from another document, you can set the formula to directly access just one cell. See Listing 30 .
| |
oCell = thiscomponent.sheets(0).getcellbyposition(0,0) ' Al oCell.setFormula("=" & "'file:///home/USER/CalcFile2.sxc'#$Sheet2.K89") | |
Finding Dependencies by using Auditing Functions
The methods queryDependents() and queryPrecedents(), listed in Table 18, return a list of cells that depend on a range. The query methods are useful for writing macros that manipulate each dependent cell. The auditing functionality provided by the XSheetAuditing interface provides methods for visualizing cell dependencies (see Table 34 ).
| Method | Description |
|---|---|
| hideDependents(CellAddress) | Remove arrows for one level of dependents; return True if cells are marked . |
| hidePrecedents(CellAddress) | Remove arrows for one level of precedents ; return True if cells are marked. |
| showDependents(CellAddress) | Draw arrows from the CellAddress (see Table 3) to its dependents; return True if cells are marked. |
| showPrecedents(CellAddress) | Draw arrows to the CellAddress (see Table 3) from its dependents; return True if cells are marked. |
| showErrors(CellAddress) | Draw arrows from the CellAddress (see Table 3) containing an error and the cells causing the error; return True if cells are marked. |
| showlnvalid() | Show all cells containing invalid values; return True if cells are marked. |
| clearArrows() | Remove all auditing arrows from the sheet. |
Each time that the showPrecedents() method is called, another level of precedents is marked with arrows. After the first call, arrows are drawn from all cells directly referenced by the specified cell. The QueryRange macro in Listing 14 displays the dependencies (see Figure 2); Listing 31 , however, displays the precedents in the spreadsheet. Figure 8 shows one level of precedents.
Figure 8: One level of precedents.
| |
Call QueryRange() oSheet = ThisComponent.Sheets(3) oCell = oSheet.getCellByPosition(2, 6) 'C7 oSheet.showPrecedents(oCell.CellAddress)
| |
The cell C7 contains the formula "=Sum(C2:C6)". As Figure 8 shows, cell C7 refers to all of the "summed" cells. If you call the method showPrecedents() again, you'll see the cells that reference the cells C2:C6. The showPrecedents() method returns True as long as more precedent cells are marked with arrows. Figure 9 shows the next level of precedents.
Figure 9: Two levels of precedents.
| Tip | Use the document method refreshArrows() to refresh all of the dependency arrows in all sheets at one time. |
Outlines
Outlines in a Calc document group rows and columns together so that you can collapse and expand the groups with a single mouse click. When you create an outline, you must specify whether it's row-centric or column-centric by using TableOrientation enumeration (see Table 35 ). The methods in Table 36 behave like their menu counterparts in the OOo GUI for dealing with spreadsheet outlines.
| Value | Description |
|---|---|
| com.sun.star.table.TableOrientation.ROWS | Use rows. |
| com.sun.star.table.TableOrientation.COLUMNS | Use columns. |
| Value | Description |
|---|---|
| group(CellRangeAddress, TableOrientation) | Group the cells in the cell range into one group. |
| ungroup(CellRangeAddress, TableOrientation) | Remove the innermost levels from the group. |
| autoOutline(CellRangeAddress) | Create outline groups based on formula references. |
| clearOutline() | Remove all outline groups from the sheet. |
| hideDetail(CellRangeAddress) | Collapse an outline group. |
| showDetail(CellRangeAddress) | Open (uncollapse) an outline group. |
| showLevel(n, CellRangeAddress) | Show outlined groups from levels one through n. |
Copying, Moving, and Inserting Cells
In a Writer document, the primary method for moving or copying text content is to use the clipboard. The Spreadsheet service, however, provides methods for directly moving and inserting cells. When new cells are inserted, you specify how cells are moved out of the way by using the CellInsertMode enumeration (see Table 37 ).
| Value | Description |
|---|---|
| com.sun.star.sheet.CellInsertMode.NONE | No cells are moved. |
| com.sun.starsheet.CellInsertMode.DOWN | Move cells down. |
| com.sun.starsheet.CellInsertMode.RIGHT | Move cells right. |
| com.sun.starsheet.CellInsertMode.ROWS | Move the entire row down. |
| com.sun.star.sheet.CellInsertMode.COLUMNS | Move the entire column right. |
Use the insertCells(CellRangeAddress, CelllnsertMode) method to create space the size of the cell range address. If the insert mode is COLUMNS, then the entire column, starting with the leftmost column in the range, is shifted to the right the width of the range. If the insert mode is RIGHT, then the entire column is not shifted right; only the rows in the range are shifted. The cell insert modes ROWS and DOWN behave similarly to the COLUMNS and RIGHT modes. Using the cell insert range of NONE causes no cells to be moved; in other words, nothing happens. Listing 32 moves cells down.
| |
Dim oSheet 'The fourth sheet Dim oRangeAddress 'The range to move oSheet = ThisComponent.Sheets(3) oRangeAddress = oSheet.getCellRangeByName("L4:M5").getRangeAddress() oSheet.insertCells(oRangeAddress, com.sun.star.sheet.CellInsertMode.DOWN) | |
| Tip | The insertCells() and removeRange() methods silently fail if the insertion will cause an array formula to be split. |
The removeRange(CellRangeAddress, CelllnsertMode) method is essentially an "undo" command for the insertCells() method.
Use the copyRange(CellAddress, CellRangeAddress) method to copy a range of cells to the location specified by the cell address. The top-left cell in the cell range address is positioned at the specified cell address when the range is copied. The net effect of the copyRange() method is the same as copying a range of cells to the clipboard, positioning the cursor at the specified cell, and then pasting the cells into place (see Listing 33 ).
| |
Dim oSheet 'The fourth sheet Dim oRangeAddress 'The range to move Dim oCellAddress 'Destination address oSheet = ThisComponent.Sheets(3) oRangeAddress = oSheet.getCellRangeByName("L4:M5").getRangeAddress() oCellAddress = oSheet.getCellByPosition(13, 7).getCellAddress() 'N8 oSheet.copyRange(oCellAddress, oRangeAddress) | |
Use the moveRange(CellAddress, CellRangeAddress) method to move (rather than copy) a range of cells. The behavior of the moveRange() method is similar to that of the copyRange() method except that the cells are moved, rather than copied; the cells in the original range are left empty.
Data Pilot Tables
The DataPilot is a powerful mechanism that allows you to combine, compare, and analyze large amounts of data. The DataPilot manipulates portions of the data from the "source table" and then displays the results in a new location. Unfortunately, numerous details are involved with the creation and manipulation of data pilot tables, but this is due to their enormous flexibility.
| Tip | I could write a large section on the numerous uses of data pilot tables. To get a feel for the possibilities, look at the input table in Figure 10 and then inspect the summary data in Figure 11 , which is automatically generated by the data pilot functionality.  Figure 10: Data used in the data pilot example.  Figure 11: The macro in Listing 35 inserts the data pilot table immediately after the source data. |
There are numerous details with respect to creating and using data pilot tables. Although the numerous details are logical and straightforward, they are so many that it is easy to become lost in the details. It's instructive, therefore, to review a simple example that clarifies the details. I'll present the specific types and enumerations following the example; you can review these as required.
A Data Pilot Example
For this example, I assume a fake company that sells books, candy , and pens. The company has offices in three states and multiple salespeople in each state. I created a spreadsheet that shows the sales for each product broken down by salesperson and year. The final goal of this example is to create a data pilot table that summarizes the sales of each product by type and state. The initial data used for this example in shown in Figure 10.
Generating the Data
The data shown in Figure 10 is generated by the macro in Listing 34 , which sets both the data and the formatting. Watch for the following techniques:
-
Generating random data.
-
Setting all of the data at one time using the method setDataArray().
-
Centering the headers and setting the cell background color .
-
Formatting a cell as currency.
| |
Function CreateDataPilotSource(sName) As Variant Dim oItem 'Array of item types Dim oTeam 'Array of team members Dim oCity 'Array of cities Dim oData 'Data array that is built and then set Dim oInvCompany 'Array of companies with which I have invested Dim oSheets 'The sheets object that contains all of the sheets Dim oSheet 'Individual sheet Dim oRange 'Holds a sheet cell range Dim i As Integer 'Index variable Dim nItem As Integer 'Index the items to sell Dim nTeam As Integer 'Index the team members Dim d2002 As Double 'Sales for the year 2002 Dim d2003 As Double 'Sales for the year 2003 Dim d2004 As Double 'Sales for the year 2004 oSheets = ThisComponent.Sheets If Not oSheets.hasbyName(sName) Then REM Insert the new sheet at as the last document. oSheets.insertNewByName (sName, oSheets.getCount()) End If oSheet = oSheets.getByName(sName) oItem = Array("Books", "Candy", "Pens") oTeam = Array("Jean", "Bob", "Ilsub", "Alan", "Chelle", "Andy") oCity = Array("Michigan", "Ohio", "Kentucky") REM One row for each piece of data oData = DimArray((UBound(oItem)+1)*(UBound(oTeam)+1)) oData(0) = Array("Item", "State", "Team", "2002", "2003", "2004") i = 0 For nTeam = 0 To UBound(oTeam) For nItem = 0 To UBound(oItem) i = i + 1 REM I would like to note some interesting behavior. REM At this point, I am calculating how much each person made selling REM a certain item for each year. The values are stored in the variables REM d2002, d2003, and d2004. Initially, I used the Int() function REM during this initial assignment. d2002 = Int(.....) REM Well, oData is an array of type Variant. When I added the REM variables into the oData array, it inserted a reference to the REM variables d2002, d2003, and d2004. Hopefully the problem with REM that is obvious (Hint: all entries in the 2002 column were the same). REM I avoided the problem by calling the Int() function later. REM This avoids the problem because the Int() function returns REM a new value that is then stored! d2002 = 10000.0 + 20000.0 * Rnd : d2003 = 15000.0 + 20000.0 * Rnd d2004 = 20000.0 + 20000.0 * Rnd oData(i) = Array(oItem(nItem), oCity(nTeam), oTeam(nTeam),_ Int(d2002), Int(d2003), Int(d2004)) Next Next oRange = oSheet.getCellRangeByName("A1:F" & (UBound(oData)+1)) oRange.setDataArray(oData) Dim oFormats 'Hold the number formats supported by this document. Dim oTempRange REM Set the number format to CURRENCY. oTempRange = oSheet.getCellRangeByName("D2:F" & (UBound(oData)+1)) oFormats = ThisComponent.NumberFormats Dim aLocale as new com.sun.star.lang.Locale oTempRange.NumberFormat = oFormats.getStandardFormat(_ com.sun.star.util.NumberFormat.CURRENCY, aLocale) REM Set the headers to be centered and shaded. oTempRange = oSheet.getCellRangeByName("A1:F1") oTempRange.CellBackColor = RGB (200, 200, 200) oTempRange.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER CreateDataPilotSource = oRange End Function | |
Creating the Data Pilot Table
The macro in Listing 35 creates and inserts a data pilot table as follows :
-
Create the data pilot table descriptor using the method createDataPilotDescriptor().
-
Set the source range of the data to use.
-
Configure which column is used for which purpose.
-
Insert the data pilot table descriptor into the set of tables.
| |
Sub CreateDataPilotTable() Dim oSheet 'Sheet that contains the data pilot Dim oRange 'Range for the data pilot source Dim oRangeAddress'The address of the range object Dim oTables 'Collection of data pilot tables Dim oTDescriptor 'A single data pilot descriptor Dim oFields 'Collection of all fields Dim oField 'A single field Dim oCellAddress As New com.sun.star.table.CellAddress REM Make my random numbers repeatable, then create the REM data that I will use! Randomize(37) oRange = CreateDataPilotSource("Pilot") REM Sure, I could simply specify the address, but this is much more fun! REM Set the destination address to be two rows below the data. oRangeAddress = oRange.getRangeAddress() oCellAddress.Sheet = oRangeAddress.Sheet oCellAddress.Column = oRangeAddress.StartColumn oCellAddress.Row = oRangeAddress.EndRow + 2 oSheet = ThisComponent.Sheets.getByName("Pilot") oTables = oSheet.getDataPilotTables() REM Step 1, create the descriptor oTDescriptor = oTables.createDataPilotDescriptor() REM Step 2, Set the source range oTDescriptor.setSourceRange(oRangeAddress) REM Step 3, Set the fields oFields = oTDescriptor.getDataPilotFields() REM Column 0 in the source is Item and I want this as a row item. oField = oFields.getByIndex(0) oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.ROW REM Column 1 in the source is State and I want this as a column item. oField = oFields.getByIndex(1) oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.COLUMN REM Column 3 in the source is 2002. Create a sum in the data for this! oField = oFields.getByIndex(3) oField.Orientation = com.sun.star.sheet.DataPilotFieldOrientation.DATA oField.Function = com.sun.star.sheet.GeneralFunction.SUM oTables.insertNewByName("MyFirstDataPilot", oCellAddress, oTDescriptor) End Sub | |
Even if you aren't familiar with data pilot tables, the input shown in Figure 10 and the output in Figure 11 should illustrate clearly how the data pilot functions. It's easy to create a simple summary of results with minimal effort.
Manipulating Data Pilot Tables
The getDataPilotTables() method, supported by each spreadsheet, returns an object that supports the service com. sun. star.sheet.DataPilotTables. The returned service provides access to the data pilot tables in the spreadsheet using both indexed and enumeration access. The DataPilotTables object also supports the methods in Table 38 .
| Method | Description |
|---|---|
| createDataPilotDescriptor() | Create a new data pilot descriptor. |
| insertNewByName(name, CellAddress, Data Pilot Descriptor) | Add a new data pilot table to the collection that uses the provided CellAddress (see Table 3) as the top-left corner of the table. |
| removeByName(name) | Delete a data pilot table from the collection. |
Data Pilot Fields
Each "field" in the created data pilot table is represented by a column in the source data pilot table (cell range) and is named using the topmost cell of the column in the range. The field name is available through the methods getName() and setName(String).
Each field contains an Orientation property of type DataPilotFieldOrientation that specifies how the field is used in the final output (see Table 39 ). The Function property specifies the function used to calculate results for this field based on the GeneralFunction enumeration (see Table 21).
| Value | Description |
|---|---|
| com.sun.star.sheet.DataPilotFieldOrientation.HIDDEN | Do not use the field. |
| com.sun.star.sheet.DataPilotFieldOrientation.COLUMN | Use the field as a column field. |
| com.sun.star.sheet.DataPilotFieldOrientation.ROW | Use the field as a row field. |
| com.sun.star.sheet.DataPilotFieldOrientation.PAGE | Use the field as a page field. |
| com.sun.star.sheet.DataPilotFieldOrientation.DATA | Use the field as a data field. |
Filtering Data Pilot Fields
The fields in the created table may be conditionally displayed based on a FilterOperator (see Table 40 ).
| Value | Description |
|---|---|
| com.sun.star.sheet.FilterOperator.EMPTY | Select empty entries. |
| com.sun.star.sheet.FilterOperator.NOT_EMPTY | Select nonempty entries. |
| com.sun.star.sheet.FilterOperator.EQUAL | The entry's value must equal the specified value. |
| com.sun.star.sheet.FilterOperator.NOT_EQUAL | The entry's value must not be equal to the specified value. |
| com.sun.star.sheet.FilterOperator.GREATER | The entry's value must be greater than the specified value. |
| com.sun.star.sheet.FilterOperator.GREATER_EQUAL | The entry's value must be greater than or equal to the specified value. |
| com.sun.star.sheet.FilterOperator.LESS | The entry's value must be less than the specified value. |
| com.sun.star.sheet.FilterOperator.LESS_EQUAL | The entry's value must be less than or equal to the specified value. |
| com.sun.star.sheet.FilterOperator.TOP_VALUES | Select a specified number with the greatest values. |
| com.sun.star.sheet.FilterOperator.TOP_PERCENT | Select a specified percentage with the greatest values. |
| com.sun.star.sheet.FilterOperator.BOTTOM_VALUES | Select a specified number with the lowest values. |
| com.sun.star.sheet.FilterOperator.BOTTOM_PERCENT | Select a specified percentage with the lowest values. |
The individual filter operators are combined using the FilterConnection enumeration (see Table 41 ). Each individual filter field is stored in a TableFilterField structure (see Table 42 ). The entire collection of TableFilterFields is stored in a SheetFilterDescriptor. The descriptor supports the methods getFilterFields() and setFilterFields(), to get and set the table filter fields as an array of TableFilterField structures. The properties in Table 43 are defined by the SheetFilterDescriptor to direct the filtering process.
| Value | Description |
|---|---|
| com.sun.star.sheet.FilterConnection.AND | Both conditions must be satisfied. |
| com.sun.star.sheet.FilterConnection.OR | At least one of the conditions must be satisfied. |
| Property | Description |
|---|---|
| Connection | Specify how the condition is connected to the previous condition as a FilterConnection (see Table 41). |
| Field | Specify which field (column) is used for the condition as a Long Integer. |
| Operator | Specify the condition type as a FilterOperator (see Table 40). |
| IsNumeric | If True, the NumericValue property is used; otherwise the StringValue is used. |
| NumericValue | Specify a numeric value for the condition as a Double. |
| StringValue | Specify a String value for the condition. |
| Property | Description |
|---|---|
| IsCaseSensitive | If True, string comparisons are case sensitive. |
| SkipDuplicates | If True, duplicate entries are not included in the result. |
| UseRegularExpressions | If True, string values in the TableFilterField structure are interpreted as regular expressions. |
| SaveOutputPosition | If True (and CopyOutpuData is True), the OutputPosition is saved for future calls. |
| Orientation | Specify if columns or rows are filtered using the TableOrientation enumeration (see Table 35). |
| ContainsHeader | If True, the first row (or column) is assumed to be a header and is not filtered. |
| CopyOutputData | If True, the filtered data is copied to the OutputPosition. |
| OutputPosition | Specify where filtered data is copied as a CellAddress (see Table 3). |
| MaxFieldCount | The maximum number of filter fields in the descriptor as a Long Integer. |
Tables
Each data pilot table is based on a spreadsheet cell range. Each data pilot table supports the object method getOutputRange(), which returns a CellRangeAddress (see Table 9). The refresh() method re-creates the table using the current data in the source range. Each data pilot table also suports the DataPilotDescriptor service, which defines the methods in Table 44 .
| Method | Description |
|---|---|
| getTag() | Get the data pilot table tag as a String. |
| setTag(String) | Set the data pilot table tag. |
| getSourceRange() | Return the CellRangeAddress (see Table 9) containing the data for the data pilot table. |
| setSourceRange(CellRangeAddress) | Set the cell range containing the data for the data pilot table. |
| getFilterDescriptor() | Get the SheetFilterDescriptor (see Table 43) that specifies which data from the source cell range is used for the data pilot table. |
| getDataPilotFields() | Get the data pilot fields as an object that supports indexed access. |
| getColumnFields() | Get the data pilot fields used as column fields as an object that supports indexed access. |
| getRowFields() | Get the data pilot fields used as row fields as an object that supports indexed access. |
| getPageFields() | Get the data pilot fields used as page fields as an object that supports indexed access. |
| getDataFields() | Get the data pilot fields used as data fields as an object that supports indexed access. |
| getHiddenFields() | Get the data pilot fields that are not used as column, row, page, or data fields. |
Sheet Cursors
In a Calc document, a cursor is a cell range that contains methods to move through the contained cells. Cursors are not used as frequently with Calc documents as with Writer documents because, unlike Writer documents, most content is directly accessible by index or name. Sheet cursors, like cell ranges, are limited to one sheet at a time. The SheetCellCursor service used by Calc documents is similar to cell cursors used in text tables (see Table 45 ).
| Component | Description |
|---|---|
| com.sun.star.table.CellCursor | Methods to control the position of a cell cursor. |
| com.sun.star.table.CellRange | Methods to access cells or subranges of a cell range (see Table 16). |
| com.sun.star.sheet.XSheetCellCursor | Advanced methods to control the position of the cursor. |
| com.sun.star.sheet.SheetCellRange | A rectangular range of cells in a spreadsheet document; this is an extension of the CellRange service for use in spreadsheet documents. |
| com.sun.star.sheet.XUsedAreaCursor | Methods to find the used area in a sheet. |
The primary methods supported by the SheetCellCursor are shown in Table 46 .
| Interface | Method | Description |
|---|---|---|
| XCellCursor | gotoStart() | Move the cursor to the first filled cell at the beginning of a contiguous series of filled cells. This cell may be outside the cursor's range. |
| XCellCursor | gotoEnd() | Move the cursor to the last filled cell at the end of a contiguous series of filled cells. This cell may be outside the cursor's range. |
| XCellCursor | gotoNext() | Move the cursor to the next (right) unprotected cell. |
| XCellCursor | gotoPrevious() | Move the cursor to the previous (left) unprotected cell. |
| XCellCursor | gotoOffset(nCol, nRow) | Shift the cursor's range relative to the current position. Negative numbers shift left and up; positive numbers shift right and down. |
| XCellRange | getCellByPosition(left, top) | Get a cell within the range. |
| XCellRange | getCellRangeByPosition(left, top, right, bottom) | Get a cell range within the range. |
| XCellRange | 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 . |
| XSheetCellCursor | collapseToCurrentRegion() | Expand the range to contain all contiguous nonempty cells. |
| XSheetCellCursor | collapseToCurrentArray() | Expand the range to contain the current array formula. |
| XSheetCellCursor | collapseToMergedArea() | Expand the range to contain merged cells that intersect the range. |
| XSheetCellCursor | expandToEntireColumns() | Expand the range to contain all columns that intersect the range. |
| XSheetCellCursor | expandToEntireRows() | Expand the range to contain all rows that intersect the range. |
| XSheetCellCursor | collapseToSize(nCols, nRows) | Without changing the upper-left corner, set the cursor range size. |
| XSheetCell Range | getSpreadsheet() | Get the sheet object that contains the cell range. |
| XUsedAreaCursor | gotoStartOfUsedArea() | Set the cursor to the start of the used area. |
| XUsedAreaCursor | gotoEndOfUsedArea() | Set the cursor to the end of the used area. |
Cell ranges, and therefore cell cursors, deal with rectangular regions. The use of rectangular regions may be obvious now that I state it, but it caught me by surprise when I tested the gotoStart() and gotoEnd() methods listed in Table 46.1 started with the configuration shown in Figure 3 when writing the code in Listing 36 .
| |
oCurs = oSheet.createCursorByRange(oSheet.getCellRangeByName("C3")) oCurs.gotoStart() REM Move the cursor to cell Bl oCurs.gotoEnd() REM Move the cursor to cell E8 oCurs.gotoStart() REM Move the cursor to cell E8 | |
The first line in Listing 36 positions the cursor at cell C3, right in the middle of a block of values. In Figure 3, the leftmost contiguous column is B and the topmost contiguous row is 1. The method gotoStart(), therefore, positions the cursor in the top-left corner at location B1. This is where things become a little bit unexpected. The rightmost contiguous column is E and the bottommost contiguous row is 8. The method gotoEnd(), therefore, positions the cursor at location E8. As it is shown in Figure 3, the cell E8 is completely disconnected from the contiguous group of cells. The cursor is positioned to cell E8, even if it does not contain a value. The cursor is no longer related to the original block of cells, so the method gotoStart() does not move the cursor back to cell B1.
To understand the behavior of Listing 36, it's important to understand how OOo determines contiguous cells, because it isn't documented anywhere that I can find. Experimentally, I have determined that the set of contiguous nonempty cells is defined as the smallest range (square block of cells) that can be enclosed by empty cells. If cell E9 contained a value, then even though cells E8 and E9 are not directly connected via nonempty cells to the original block of cells, they would both be considered part of the block of contiguous nonempty cells.
The method collapseToCurrentRegion() causes the cursor to contain the block of contiguous cells. The only caveat is that after the range is collapsed , it always contains the original range, even if this range includes unnecessary empty cells. The method collapseToCurrentArray() is similar to collapseToCurrentRegion(), except that it returns a range that contains an array formula. The upper-left corner of the region must include an array formula for the collapseToCurrentArray() method to work.
The code snippet in Listing 37 creates a cursor over a range and then demonstrates that getCellByPosition() and getCellRangeByPosition() are relative to the upper-left corner of the range. The method getCellRangeByName() generates an exception if a cell outside the range is requested .
| |
oCurs = oSheet.createCursorByRange(oSheet.getCellRangeByName("C3:F12")) oCell = oCurs.getCellByPosition(0, 0) REM Cell C3 oRange = oCurs.getCellRangeByPosition(1, 0, 3, 2) REM D3:F5 oRange = oCurs.getCellRangeByName("C4:D6") REM C4:D6 oRange = oCurs.getCellRangeByName("C2:D6") REM Error C2 not in range! | |
| Note | The methods getCellByPosition(), getCellRangeByPosition(), and getCellRangeByName() cannot return a value that is not in the range. |
EAN: 2147483647
Pages: 203