Creating a New DataPilot Table

From Apache OpenOffice Wiki
Jump to: navigation, search



The first step to creating a new DataPilot table is to create a new com.sun.star.sheet.DataPilotDescriptor object by calling the com.sun.star.sheet.XDataPilotTables interface's createDataPilotDescriptor() method. The descriptor is then used to describe the DataPilot table's layout and options, and passed to the insertNewByName() method of XDataPilotTables. The other parameters for insertNewByName() are the name for the new table, and the position where the table is to be placed on the spreadsheet.

The com.sun.star.sheet.XDataPilotDescriptor interface offers methods to change the DataPilot table settings:

  • The cell range that contains the source data is set with the setSourceRange() method. It is a com.sun.star.table.CellRangeAddress struct.
  • The individual fields are handled using the getDataPilotFields(), getColumnFields(), getRowFields(), getPageFields(), getDataFields() and getHiddenFields() methods. The details are discussed below.
  • The setTag() method sets an additional string that is stored with the DataPilot table, but does not influence its results.
  • The getFilterDescriptor() method returns a com.sun.star.sheet.SheetFilterDescriptor object that can be used to apply filter criteria to the source data. Refer to the section on data operations for details on how to use a filter descriptor.

The layout of the DataPilot table is controlled using the com.sun.star.sheet.DataPilotFields service. Each com.sun.star.sheet.DataPilotField object has an Orientation property that controls where in the DataPilot table the field is used. The com.sun.star.sheet.DataPilotFieldOrientation enum contains the possible orientations:

  • HIDDEN: The field is not used in the table.
  • COLUMN: Values from this field are used to determine the columns of the table.
  • ROW: Values from this field are used to determine the rows of the table.
  • PAGE: The field is used in the table's "page" area, where single values from the field can be selected.
  • DATA: The values from this field are used to calculate the table's data area.

The Function property is used to assign a function to the field. For instance, if the field has a DATA orientation, this is the function that is used for calculation of the results. If the field has COLUMN or ROW orientation, it is the function that is used to calculate subtotals for the values from this field.

The getDataPilotFields() method returns a collection containing one com.sun.star.sheet.DataPilotField entry for each column of source data, and one additional entry for the "Data" column that becomes visible when two or more fields get the DATA orientation. Each source column appears only once, even if it is used with several orientations or functions.

The getColumnFields(), getRowFields(), getPageFields() and getDataFields() methods each return a collection of the fields with the respective orientation. In the case of getDataFields(), a single source column can appear several times if it is used with different functions. The getHiddenFields() method returns a collection of those fields from the getDataPilotFields() collection that are not in any of the other collections.

Documentation note.png Note: Page fields and the PAGE orientation are not supported by the current implementation. Setting a field's orientation to PAGE has the same effect as using HIDDEN. The getPageFields() method always returns an empty collection.

The exact effect of changing a field orientation depends on which field collection the field object was taken from. If the object is from the getDataPilotFields() collection, the field is added to the collection that corresponds to the new Orientation value. If the object is from any of the other collections, the field is removed from the old orientation and added to the new orientation.

The following example creates a simple DataPilot table with one column, row and data field.

  // --- Create a new DataPilot table ---
  com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = (com.sun.star.sheet.XDataPilotTablesSupplier)
      UnoRuntime.queryInterface(com.sun.star.sheet.XDataPilotTablesSupplier.class, xSheet);
  com.sun.star.sheet.XDataPilotTables xDPTables = xDPSupp.getDataPilotTables();
  com.sun.star.sheet.XDataPilotDescriptor xDPDesc = xDPTables.createDataPilotDescriptor();
 
  // set source range (use data range from CellRange test)
  com.sun.star.table.CellRangeAddress aSourceAddress = createCellRangeAddress(xSheet, "A10:C30");
  xDPDesc.setSourceRange(aSourceAddress);
 
  // settings for fields
  com.sun.star.container.XIndexAccess xFields = xDPDesc.getDataPilotFields();
  Object aFieldObj;
  com.sun.star.beans.XPropertySet xFieldProp;
 
  // use first column as column field
  aFieldObj = xFields.getByIndex(0);
  xFieldProp = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
  xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.COLUMN);
 
  // use second column as row field
  aFieldObj = xFields.getByIndex(1);
  xFieldProp = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
  xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.ROW);
 
  // use third column as data field, calculating the sum
  aFieldObj = xFields.getByIndex(2);
  xFieldProp = (com.sun.star.beans.XPropertySet)
      UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, aFieldObj);
  xFieldProp.setPropertyValue("Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA);
  xFieldProp.setPropertyValue("Function", com.sun.star.sheet.GeneralFunction.SUM);
 
  // select output position
  com.sun.star.table.CellAddress aDestAddress = createCellAddress(xSheet, "A40");
  xDPTables.insertNewByName("DataPilotExample", aDestAddress, xDPDesc);
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages