Index: quick-guide.xml =================================================================== --- quick-guide.xml (revision 1559103) +++ quick-guide.xml (working copy) @@ -74,6 +74,7 @@
  • Autofilters
  • Conditional Formatting
  • Hiding and Un-Hiding Rows
    • +
  • Pivot Table
    • Features @@ -2104,5 +2105,122 @@ Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise.

    + +
    Pivot Table +

    + In order to create a Pivot Table using POI one must first create the sheet with the data wanted in the Pivot Table. Eg. +

    + + XSSFWorkbook wb = new XSSFWorkbook(); + XSSFSheet sheet = (XSSFSheet) wb.createSheet(); + + //Create some data to build the pivot table on + Row row1 = sheet.createRow(0); + // Create a cell and put a value in it. + Cell cell11 = row1.createCell(0); + cell11.setCellValue("Names"); + Cell cell12 = row1.createCell(1); + cell12.setCellValue("#"); + Cell cell13 = row1.createCell(2); + cell13.setCellValue("%"); + Cell cell14 = row1.createCell(3); + cell14.setCellValue("Human"); + + Row row2 = sheet.createRow(1); + Cell cell21 = row2.createCell(0); + cell21.setCellValue("Jane"); + Cell cell22 = row2.createCell(1); + cell22.setCellValue(10); + Cell cell23 = row2.createCell(2); + cell23.setCellValue(100); + Cell cell24 = row2.createCell(3); + cell24.setCellValue("Yes"); + + Row row3 = sheet.createRow(2); + Cell cell31 = row3.createCell(0); + cell31.setCellValue("Tarzan"); + Cell cell32 = row3.createCell(1); + cell32.setCellValue(5); + Cell cell33 = row3.createCell(2); + cell33.setCellValue(90); + Cell cell34 = row3.createCell(3); + cell34.setCellValue("Yes"); + + Row row4 = sheet.createRow(3); + Cell cell41 = row4.createCell(0); + cell41.setCellValue("Terk"); + Cell cell42 = row4.createCell(1); + cell42.setCellValue(10); + Cell cell43 = row4.createCell(2); + cell43.setCellValue(90); + Cell cell44 = row4.createCell(3); + cell44.setCellValue("No"); + +

    + The Pivot Table can then be created by referencing the area of the values (in our case A1:D4) and where we want it to be placed. +

    + + XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5")); + +

    + We have now created a basic Pivot Table. Writing this to a xlsx-file and opening this in Excel will just show an empty box, ready for configurations to be made. +

    + + FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx"); + wb.write(fileOut); + fileOut.close(); + +

    + To configure your Pivot Table to display the information as wanted you must configure which values (if any) are wanted as: + Report Filter + , + Row Label + and + Column_Label + . +

    +

    Row Label

    + + pivotTable.addRowLabel(0); + +

    Column Label

    + + pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 1); + +

    Different calculations can be performed on the selected column, such as:

    + +

    Report Filter

    + + pivotTable.addReportFilter(2); + +

    Pivot Tables can as well be created using data from another sheet using either a sheet object:

    + + //Getting an already existing sheet with index 0. + XSSFSheet sheet = wb.getSheetAt(0); + XSSFSheet sheet2 = wb.createSheet(); + + XSSFPivotTable pivotTable = sheet2.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"), sheet); + +

    Or an area reference:

    + + //Getting an already existing sheet with index 0. + XSSFSheet sheet = wb.getSheetAt(0); + XSSFSheet sheet2 = wb.createSheet(); + + XSSFPivotTable pivotTable = sheet2.createPivotTable(new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5")); + +