Levels

OpenOffice.org Developer's Guide

Spreadsheet Documents

< Previous Section

Next Section >

• Example: Adding a New Spreadsheet
• Example: Editing Spreadsheet Cells

• Handling Spreadsheet Documents Files

• Saving Spreadsheet Documents

• Filter Options

• Printing Spreadsheet Documents

• Working With Spreadsheet Documents

• Spreadsheet Services - Overview

• Capabilities of Spreadsheet
• Capabilities of SheetCellRange
• Capabilities of SheetCell
• Capabilities of SheetCellRanges Container
• Capabilities of Columns and Rows

• Spreadsheet
• Cell Ranges

• Properties of Cell Ranges
• Cell and Cell Range Access
• Merging Cell Ranges into a Single Cell
• Column and Row Access
• Data Array
• Absolute Address
• Fill Series
• Operations
• Multiple Operations
• Handling Array Formulas

• Cells

• Properties of SheetCell
• Access to Formulas, Values and Errors
• Access to Text Content
• Absolute Address
• Cell Annotations

• Cell Ranges and Cells Container
• Columns and Rows

• Formatting

• Character and Paragraph Format
• Indentation
• Equally Formatted Cell Ranges
• Table Auto Formats
• Conditional Formats

• Navigating

• Cell Cursor
• Referencing Ranges by Name
• Named Ranges
• Label Ranges
• Querying for Cells with Specific Properties
• Search and Replace

• Sorting
• Database Operations

• Filtering
• Subtotals
• Database Import
• Database Ranges

• Linking External Data

• Sheet Links
• Cell Area Links
• DDE Links

• DataPilot Tables

• Creating a New DataPilot Table
• Modifying a DataPilot Table
• Removing a DataPilot Table

• DataPilot Sources

• Source Object
• Dimensions
• Hierarchies
• Levels
• Members

• Protecting Spreadsheets
• Sheet Outline
• Detective
• Other Table Operations

• Overall Document Features

• Function Handling
• Settings

• Spreadsheet Document Controller

• View Panes
• View Settings
• Range Selection

• Spreadsheet Add-Ins

• Function Descriptions
• Service Names
• Compatibility Names
• Custom Functions
• Variable Results

< Previous Page

Next Page >

Each level of a hierarchy that is used in a DataPilot table corresponds to a column or row showing its members in the left or upper part of the table. The com.sun.star.sheet.DataPilotSourceLevel service contains a com.sun.star.beans.XPropertySet interface that is used to apply the following settings to a level:

• The SubTotals property defines a list of functions that are used to calculate subtotals for each member. If the sequence is empty, no subtotal columns or rows are generated. The com.sun.star.sheet.GeneralFunction enum value AUTO is used to select "automatic" subtotals, determined by the type of the data.
• The ShowEmpty property controls if result columns or rows are generated for members that have no data.

Both of these settings can be modified by the user in the "Data Field" dialog. The example does not use them.

The com.sun.star.sheet.XDataPilotMemberResults interface is used to get the result header column that is displayed below the level's name for a row dimension, or the header row for a column dimension. The sequence returned from the getResults() call must have the same size as the data result's columns or rows respectively, or be empty. If the sequence is empty, or none of the entries contains the HASMEMBER flag, the level is not shown.

The com.sun.star.sheet.MemberResult struct contains the following members:

• Name is the name of the member that is represented by the entry, exactly as returned by the member object's getName() method. It is used to find the member object, for example when the user double-clicks on the cell.
• Caption is the string that will be displayed in the cell. It may or may not be the same as Name.
• Flags indicates the kind of result the entry represents. It can be a combination of the com.sun.star.sheet.MemberResultFlags constants:
  • HASMEMBER indicates there is a member that belongs to this entry.
  • SUBTOTAL marks an entry that corresponds to a subtotal column or row. The HASMEMBER should be set.
  • CONTINUE marks an entry that is a continuation of the previous entry. In this case, none of the others are set, and the Name and Caption members are both empty.

In the example table shown above, the resulting sequence for the "ones" level would consist of:

• an entry containing the name and caption "1" and the HASMEMBER flag
• two entries containing only the CONTINUE flag
• the same repeated for member names "2" and "3".

The implementation for the example looks similar to this:

  private ExampleSettings aSettings;
  private int nDimension;
 
  public com.sun.star.sheet.MemberResult[] getResults() {
      int nDimensions = 0;
      int nPosition = aSettings.aColDimensions.indexOf(new Integer(nDimension));
      if (nPosition >= 0)
          nDimensions = aSettings.aColDimensions.size();
      else {
          nPosition = aSettings.aRowDimensions.indexOf(new Integer(nDimension));
          if (nPosition >= 0)
              nDimensions = aSettings.aRowDimensions.size();
      }
 
      if (nPosition < 0)
          return new com.sun.star.sheet.MemberResult[0];
 
      int nMembers = aSettings.nMemberCount;
      int nRepeat = 1;
      int nFill = 1;
      for (int i=0; i<nDimensions; i++) {
          if (i < nPosition)
              nRepeat *= nMembers;
          else if (i > nPosition)
              nFill *= nMembers;
      }
      int nSize = nRepeat * nMembers * nFill;
 
      com.sun.star.sheet.MemberResult[] aResults = new com.sun.star.sheet.MemberResult[nSize];
      int nResultPos = 0;
      for (int nOuter=0; nOuter<nRepeat; nOuter++) {
          for (int nMember=0; nMember<nMembers; nMember++) {
              aResults[nResultPos] = new com.sun.star.sheet.MemberResult();
              aResults[nResultPos].Name = ExampleSettings.getMemberName( nMember );
              aResults[nResultPos].Caption = aResults[nResultPos].Name;
              aResults[nResultPos].Flags = com.sun.star.sheet.MemberResultFlags.HASMEMBER;
              ++nResultPos;
 
              for (int nInner=1; nInner<nFill; nInner++) {
                  aResults[nResultPos] = new com.sun.star.sheet.MemberResult();
                  aResults[nResultPos].Flags = com.sun.star.sheet.MemberResultFlags.CONTINUE;
                  ++nResultPos;
              }
          }
      }
      return aResults;
  }

Content on this page is licensed under the Public Documentation License (PDL).