SheetPivoter

API

SheetPivoter.java
class SheetPivoter {
  void pivot(Sheet pivotSourceSheet, Sheet pivotTargetSheet)     (1)
  List<Cell> getDistinctValuesInSourceSheetColumn(Sheet sourceSheet, Integer columnNumber)
}
1 pivot(Sheet, Sheet)

Takes the values of the source sheet and creates a pivot in the target sheet based on the information of the first couple of rows. Only cells of type CELL_TYPE_NUMERIC will be handled and summed in the pivot.

Members

pivot(Sheet, Sheet)

Takes the values of the source sheet and creates a pivot in the target sheet based on the information of the first couple of rows. Only cells of type CELL_TYPE_NUMERIC will be handled and summed in the pivot.

The source sheet has to honour the following conventions for pivot to be successful and meaningful:

  • Row (0) contains at least a Cell of type CELL_TYPE_STRING with the value "row".

  • Likewise Row (0) contains at least one Cell with value "column" and "value".

  • Row (0) may contain one or more Cell 's with value "deco". The values in this column are expected (but not enforced) to have the same value for each distinct value found in the column marked "row".

  • Row (1) may contain one or more Cell 's of type CELL_TYPE_NUMERIC that specifies an order for the value on top of it.

  • Row (2) contains the aggregation types.

  • Row (3) contains the field labels.

  • All other Row 's, if present, contain the data for the pivot. Only numeric values in the column(s) annoted "value" will be summed.

The pivot will use the distinct values of the first column marked "row" (left-to-right) as row labels. The distinct values of the columns marked as "column" as column labels (in the order of the specified order - if present. Otherwise in the order in which they appear in Row (0) left-to-right.) The values of the pivot are taken from the column(s) marked "value" (in the order of the specified order - if present. Otherwise in the order in which they appear in Row (0) left-to-right.) They have to be of type CELL_TYPE_NUMERIC and will me ignored otherwise. The values found in the column(s) marked "deco" are put in the column(s) 1 .. following the row label and are meant as decoration. Since the assumption is that every distinct row label has the same decoration(s) only the first found value for each row will be added.