Difference between revisions of "Documentation/DevGuide/Spreadsheets/Cell Cursor"

Revision as of 15:43, 3 January 2021

A cell cursor is a cell range with extended functionality and is represented by the service com.sun.star.sheet.SheetCellCursor. With a cell cursor it is possible to move through a cell range. Each table can contain only one cell cursor.

Cell cursor

It implements all interfaces described in Cell Ranges and the basic cursor interfaces of the service com.sun.star.table.CellCursor that represents the cell or cell range cursor of a table.

The interface com.sun.star.sheet.XSpreadsheet of a spreadsheet creates the cell cursors. The methods return the interface com.sun.star.sheet.XSheetCellCursor of the cursor. It is derived from the interface com.sun.star.sheet.XSheetCellRange that provides access to cells and cell ranges. Refer to Cell Ranges for additional information.

• The method createCursor() creates a cursor that spans over the whole spreadsheet.
• The method createCursorByRange() creates a cursor that spans over the given cell range.

The SheetCellCursor includes the CellCursor service from the table module:

Table cell cursor

Cursor Movement

The service com.sun.star.table.CellCursor implements the interface com.sun.star.table.XCellCursor that provides methods to move to specific cells of a cell range. This interface is derived from com.sun.star.table.XCellRange so all methods that access single cells can be used.

The following example shows how to modify a cell beyond a filled area.The xCursor may be an initialized cell cursor.

  // *** Use the cell cursor to add some data below of the filled area ***
  // Move to the last filled cell.
  xCursor.gotoEnd();
  // Move one row down.
  xCursor.gotoOffset(0, 1);
  xCursor.getCellByPosition(0, 0).setFormula("Beyond of the last filled cell.");

The interface com.sun.star.sheet.XSheetCellCursor sets the cursor to specific ranges in the sheet.

• The method collapseToCurrentRegion() expands the cursor to the shortest cell range filled with any data. A few examples from the spreadsheet below are: the cursor C2:C2 expands to B2:D3, cursor C1:C2 expands to B1:D3 and cursor A1:D4 is unchanged.

	A	B	C	D	E	F	G
1
2			1	3		{=C2:D3}	{=C2:D3}
3		Text	2	4		{=C2:D3}	{=C2:D3}
4

• The method collapseToCurrentArray() expands or shortens the cursor range to an array formula range. This works only if the top-left cell of the current cursor contains an array formula. An example using the spreadsheet above: All the cursors with a top-left cell located in the range F2:G3 are modified to this array formula range, F2:F2 or G2:G4.
• The method collapseToMergedArea() expands the current cursor range so that all merged cell ranges intersecting the current range fit completely.
• The methods expandToEntireColumns() and expandToEntireRows() expand the cursor range so that it contains all cells of the columns or rows of the current range.
• The method collapseToSize() resizes the cursor range to the given dimensions. The start address of the range is left unmodified. To move the cursor range without changing the current size, use the method gotoOffset() from the interface com.sun.star.table.XCellCursor.

Some of the methods above have misleading names: collapseToCurrentRegion() and collapseToMergedArea() expand the cursor range,but never shorten it and collapseToCurrentArray() may expand or shorten the cursor range.

The following example tries to find the range of the array formula in cell F22. The xSheet is a com.sun.star.sheet.XSpreadsheet interface of a spreadsheet and getCellRangeAddressString() is a helper method that returns the range address as a string.

  // --- find the array formula using a cell cursor ---
  com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName("F22");
  com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange)
      UnoRuntime.queryInterface(com.sun.star.sheet.XSheetCellRange.class, xRange);
  com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursorByRange(xCellRange);
 
  xCursor.collapseToCurrentArray();
  com.sun.star.sheet.XArrayFormulaRange xArray = (com.sun.star.sheet.XArrayFormulaRange)
      UnoRuntime.queryInterface(com.sun.star.sheet.XArrayFormulaRange.class, xCursor);
  System.out.println(
      "Array formula in " + getCellRangeAddressString(xCursor, false)
          + " contains formula " + xArray.getArrayFormula());

Used Area

The cursor interface com.sun.star.sheet.XUsedAreaCursor contains methods to locate the used area of the entire sheet. The used area is the smallest cell range that contains all cells of the spreadsheet with any contents, such as values, text, and formulas, or visible formatting, such as borders and background color. In the following example, xSheet is a com.sun.star.sheet.XSpreadsheet interface of a spreadsheet. ,syntaxhighlight lang="java">

 // --- Find the used area ---
 com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursor();
 com.sun.star.sheet.XUsedAreaCursor xUsedCursor = (com.sun.star.sheet.XUsedAreaCursor)
     UnoRuntime.queryInterface(com.sun.star.sheet.XUsedAreaCursor.class, xCursor);
 xUsedCursor.gotoStartOfUsedArea(false);
 xUsedCursor.gotoEndOfUsedArea(true);
 System.out.println("The used area is: " + getCellRangeAddressString(xCursor, true));

</syntaxhighlight>