Difference between revisions of "Documentation/DevGuide/Spreadsheets/Subtotals"

Revision as of 09:55, 4 June 2008

A com.sun.star.sheet.SubTotalDescriptor object is created using the createSubTotalDescriptor() method from the range's com.sun.star.sheet.XSubTotalCalculatable interface to create subtotals for a cell range. After applying the settings to the descriptor, it is passed to the applySubTotals() method.

The bEmpty parameter to the createSubTotalDescriptor() method works in the same manner as the parameter to the createFilterDescriptor() method described in the filtering section. If the bReplace parameter to the applySubTotals() method is true, existing subtotal rows are deleted before inserting new ones.

The removeSubTotals() method removes the subtotal rows from the cell range without modifying the stored subtotal settings, so that the same subtotals can later be restored.

SubtotalDescriptor

New fields are added to the subtotal descriptor using the com.sun.star.sheet.XSubTotalDescriptor interface's addNew() method. The nGroupColumn parameter selects the column by which values are grouped. The subtotals are inserted at changes of the column's values. The aSubTotalColumns parameter specifies which column subtotal values are calculated. It is a sequence of com.sun.star.sheet.SubTotalColumn entries where each entry contains the column number and the function to be calculated.

To query or modify the fields in a subtotal descriptor, the com.sun.star.container.XIndexAccess interface is used to access the fields. Each field's com.sun.star.sheet.XSubTotalField interface gets and sets the group and subtotal columns.

The example below creates subtotals, grouping by the first column and calculating the sum of the third column:

 // --- insert subtotals ---
 com.sun.star.sheet.XSubTotalCalculatable xSub = (com.sun.star.sheet.XSubTotalCalculatable)
     UnoRuntime.queryInterface(com.sun.star.sheet.XSubTotalCalculatable.class, xRange);
 com.sun.star.sheet.XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true);
 com.sun.star.sheet.SubTotalColumn[] aColumns = new com.sun.star.sheet.SubTotalColumn[1];
 // calculate sum of third column
 aColumns[0] = new com.sun.star.sheet.SubTotalColumn();
 aColumns[0].Column = 2;
 aColumns[0].Function = com.sun.star.sheet.GeneralFunction.SUM;
 // group by first column
 xSubDesc.addNew(aColumns, 0);
 xSub.applySubTotals(xSubDesc, true);