Excel pivot tables

Excel Pivot Table

MemPOI also supports Excel Pivot Table. Keep in mind that only XSSF supports Excel Pivot Table. Here is a basic example:

MempoiPivotTableBuilder mempoiPivotTableBuilder = MempoiPivotTableBuilder.aMempoiPivotTable()
                .withWorkbook(workbook)
                .withAreaReferenceSource("A1:F100")
                .withPosition(new CellReference("H1"));

MempoiSheet mempoiSheet = MempoiSheetBuilder.aMempoiSheet()
                .withSheetName("Nice sheet")
                .withPrepStmt(prepStmt)
                .withMempoiPivotTableBuilder(mempoiPivotTableBuilder)
                .build();

Pivot Table source

You can specify one source for the pivot table choosing from:

• explicit area reference (in this case you can also specify a source sheet if different from the one in which place the pivot table)
• a previously generated table (the table's sheet will be used as source sheet)

Unfortunately Apache POI actually doesn't support table as source for a pivot table. MemPOI makes an abstraction that is only able to extract the table area reference and use it as source for the upcoming pivot table. This means that if you open the generated excel file, you move the source table and update the pivot table, it will not be able to keep data consistency

Here an example with area reference source on different sheet:

MempoiSheet mempoiSheet1 = MempoiSheetBuilder.aMempoiSheet()
                .withSheetName("Oh sheet!")
                .withPrepStmt(prepStmt)
                .build();

MempoiPivotTableBuilder mempoiPivotTableBuilder = MempoiPivotTableBuilder.aMempoiPivotTable()
                .withWorkbook(workbook)
                .withMempoiSheetSource(mempoiSheet1)
                .withAreaReferenceSource("A1:F100")
                .withPosition(new CellReference("H1"));

MempoiSheet mempoiSheet2 = MempoiSheetBuilder.aMempoiSheet()
                .withSheetName("Second sheet")
                .withPrepStmt(prepStmt2)
                .withMempoiPivotTableBuilder(mempoiPivotTableBuilder)
                .build();

MemPOI memPOI = MempoiBuilder.aMemPOI()
                .withWorkbook(workbook)
                .withFile(fileDest)
                .addMempoiSheet(mempoiSheet1)       // NOTE THAT SHEETS ORDER IS IMPORTANT
                .addMempoiSheet(mempoiSheet2)
                .build();

Here an example with table source:

MempoiTable mempoiTable = MempoiTableBuilder.aMempoiTable()
                .withWorkbook(workbook)
                .withTableName("MyTable")
                .withDisplayTableName("MyTableName")
                .withAreaReferenceSource("A1:F100")
                .build();

MempoiPivotTableBuilder mempoiPivotTableBuilder = MempoiPivotTableBuilder.aMempoiPivotTable()
                .withWorkbook(workbook)
                .withMempoiTableSource(mempoiTable)
                .withPosition(new CellReference("H1"));

MempoiSheet mempoiSheet = MempoiSheetBuilder.aMempoiSheet()
                .withSheetName("Pets")
                .withPrepStmt(prepStmt)
                .withMempoiTable(mempoiTable)
                .withMempoiPivotTableBuilder(mempoiPivotTableBuilder)
                .build();

Pivot Table filters and labels

You can specify row labels, column labels and report filters by passing the list of relative column names (in case of db queries that use AS clause you should use AS clause values):

EnumMap<DataConsolidateFunction, List<String>> columnLabelColumnsMap = new EnumMap<>(DataConsolidateFunction.class);
columnLabelColumnsMap.put(DataConsolidateFunction.SUM, Arrays.asList("sum"));
columnLabelColumnsMap.put(DataConsolidateFunction.AVERAGE, Arrays.asList("average"));

List<String> rowLabelColumnList = Arrays.asList("name", "surname");

List<String> reportFilterColumnList = Arrays.asList("address", "city");

MempoiPivotTableBuilder mempoiPivotTableBuilder = MempoiPivotTableBuilder.aMempoiPivotTable()
                 .withWorkbook(workbook)
                 .withAreaReferenceSource("A1:F100")
                 .withPosition(new CellReference("H1"))
                 .withRowLabelColumns(rowLabelColumnList)
                 .withColumnLabelColumns(columnLabelColumnsMap)
                 .withReportFilterColumns(reportFilterColumnList);