Querying for Cells with Specific Properties

From Apache OpenOffice Wiki
Jump to: navigation, search



Cells, cell ranges and collections of cell ranges are queried for certain cell contents through the service com.sun.star.sheet.SheetRangesQuery. It implements interfaces to query cells and cell ranges with specific properties.

The methods of the interface com.sun.star.sheet.XCellRangesQuery search for cells with specific contents or properties inside of the given cell range. The methods of the interface com.sun.star.sheet.XFormulaQuery search for cells in the entire spreadsheet that are reference to or are referenced from formula cells in the given range.

Query sheet ranges
Documentation caution.png Due to a bug in the current implementation, both methods queryPrecedents() and queryDependents() of the interface com.sun.star.sheet.XFormulaQuery cause an endless loop in recursive mode, if parameter bRecursive is true.

All methods return the interface com.sun.star.sheet.XSheetCellRanges of a cell range collection. Cell range collections are described in the chapter Cell Ranges and Cells Container.

Methods of com.sun.star.sheet.XCellRangesQuery
queryVisibleCells() Returns all cells that are not hidden.
queryEmptyCells() Returns all cells that do not have any content.
queryContentCells() Returns all cells that have the contents described by the passed parameter. The flags are defined in com.sun.star.sheet.CellFlags.
queryFormulaCells() Returns all formula cells whose results have a specific type described by the passed parameter. The result flags are defined in com.sun.star.sheet.FormulaResult.
queryColumnDifferences() Returns all cells of the range that have different contents than the cell in the same column of the specified row. See the example below.
queryRowDifferences() Returns all cells of the range that have different contents than the cell in the same row of the specified column. See the example below.
queryIntersection() Returns all cells of the range that are contained in the passed range address.


Example:

A B C D E F G
1
1
1
2
2
1
2
2
3
1
2
1
4
1
1
1

The queried range is A1:C4 and the passed cell address is B2.

  • queryColumnDifferences(): (the row number is of interest) The cells of column A are compared with cell A2, the cells of column B with B2 and so on. The function returns the cell range list B1:B1, B4:B4, C3:C4.
  • queryRowDifferences(): (the column index is of interest) The function compares row 1 with cell B1, row 2 with cell B2 and so on. It returns the cell range list C1:C1, A2:A2, A3:A3, C3:C3.

The following code queries all cells with text content:

  // --- Cell Ranges Query ---
  // query addresses of all cells containing text
  com.sun.star.sheet.XCellRangesQuery xRangesQuery = (com.sun.star.sheet.XCellRangesQuery)
      UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangesQuery.class, xCellRange);
 
  com.sun.star.sheet.XSheetCellRanges xCellRanges =
      xRangesQuery.queryContentCells((short)com.sun.star.sheet.CellFlags.STRING);
  System.out.println("Cells containing text: " + xCellRanges.getRangeAddressesAsString());
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages