multiple pivot tables in the same sheet using apache poi

Question

How to create multiple(two) pivot table using apache POI in same sheet of excel. In the below example, i can able to create single pivot table in the sheet. How to add other pivot table dynamically in the same sheet?

    /* Read the input file that contains the data to pivot */
        FileInputStream input_document = new FileInputStream(new File("inputFile.xlsx"));    
        /* Create a POI XSSFWorkbook Object from the input file */
        XSSFWorkbook my_xlsx_workbook = new XSSFWorkbook(input_document); 
        /* Read Data to be Pivoted - we have only one worksheet */
        XSSFSheet pivot_sheet=my_xlsx_workbook.createSheet();
        /* Get the reference for Pivot Data */
        AreaReference a=new AreaReference("A1:C51");
        /* Find out where the Pivot Table needs to be placed */
        CellReference b=new CellReference("I5");
        /* Create Pivot Table */
        XSSFPivotTable pivotTable = pivot_sheet.createPivotTable(a,b,sheet);
        /* Add filters */
        pivotTable.addReportFilter(0);
        pivotTable.addRowLabel(1);
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); 
        /* Write Pivot Table to File */
        FileOutputStream output_file = new FileOutputStream(new File("POI_XLS_Pivot_Example.xlsx")); 
        my_xlsx_workbook.write(output_file);
        input_document.close(); 

------EDIT--------- In the below code, first pivot table is generated and second pivot table also should be shown on the same sheet after the first pivot table. How to know the cellReference to place the second pivot table based on where the first pivot table ends. In the below code sample i gave "A35" to show the second pivot table,how to replace A35 with the dynamic generated last row value of the first pivot table in same sheet?

   //dynamic cell
       int firstRow = dataSheet.getFirstRowNum();
       int lastRow = dataSheet.getLastRowNum();
       int firstCol = dataSheet.getRow(0).getFirstCellNum();
       int lastCol = dataSheet.getRow(0).getLastCellNum();

       CellReference topLeft = new CellReference(firstRow, firstCol);
       CellReference botRight = new CellReference(lastRow, lastCol - 1);
       AreaReference ar = new AreaReference(topLeft, botRight,SpreadsheetVersion.EXCEL2007);
        /* Find out where the Pivot Table needs to be placed */
        CellReference cr=new CellReference("A5");
      // CellReference cr = new CellReference(firstRow + 4, lastCol + 1);
        /* Create Pivot Table */
        XSSFPivotTable pivotTable = sheet.createPivotTable(ar, cr, dataSheet);
 /* Add filters - first pivot table */
        pivotTable.addRowLabel(11);
        pivotTable.addRowLabel(8);
        pivotTable.addColLabel(15);
        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 77);

  /* Second Pivot table dynamic generation */
 CellReference cr2=new CellReference("A35");//How to get dynamically this cellReference "A35" value based on the first pivot table generation
       XSSFPivotTable pivotTable2 = sheet.createPivotTable(ar, cr2, dataSheet);
       /* Add filters */
       pivotTable2.addRowLabel(11);
       pivotTable2.addColLabel(15);
       pivotTable2.addColumnLabel(DataConsolidateFunction.SUM, 77);

Answer 1

Counting how much space takes a pivot table representation in the sheet top down in rows is complicated. It mainly depends on how much different items are in data for row labels. And to get this one would need to do the pivoting using code additionally. Apache POI does not so. It only creates the pivot table description. Excel does the pivoting then. So Apache POI does not know how much space a pivot table representation takes in the sheet.

One could calculate the rows per pivot table as follows:

• 4 rows at top for possible report filters
• 1 row per unique item per row label, multiple row labels multiply unique items
• 1 row for the heading of col labels
• 1 row for the heading of column labels having data consolidate functions
• 1 row for the totals

Main task for this is to get the unique items per row label. To be as independent as possible, we should get the unique items per data column in the data source.

Additional for independence reasons, we could get the used data range of the data source programmatically.

Complete example: Needs a data source in first sheet of ExcelSource.xlsx which has one heading row, three columns of text data and two columns of numeric data.

Example:

Company     Country     Product     Count     Amount
Company1    Country1    Product1    1,083     84,474.00 
Company1    Country1    Product2    1,692     11,844.00 
Company1    Country1    Product3    1,431     77,274.00 
Company1    Country1    Product4      296    -25,752.00 
Company1    Country2    Product1      996    -26,892.00 
Company1    Country2    Product2    1,315    -69,695.00 
Company1    Country2    Product3      480       -480.00 
Company1    Country2    Product4      390    -17,550.00 
Company1    Country3    Product1    1,363   -118,581.00 
Company1    Country3    Product2      419     40,224.00 
Company1    Country3    Product3    1,990   -103,480.00 
Company1    Country3    Product4      307    -19,341.00 
Company2    Country1    Product1    1,824    -80,256.00 
...

Code:

import java.io.FileOutputStream;
import java.io.FileInputStream;

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import java.util.Locale;
import java.util.Map;
import java.util.HashMap;
import java.util.TreeSet;

class CreatePivotTableDefault {
    
 static int getLastFilledRow(Sheet sheet, int col) {
  int lastStoredRowNum = sheet.getLastRowNum();
  for (int r = lastStoredRowNum; r >= 0; r--) {
   Row row = sheet.getRow(r);
   if (row != null) {
    Cell cell = row.getCell(col);
    if (cell != null && cell.getCellType() != CellType.BLANK) return row.getRowNum();  
   }       
  }
  return -1; // the sheet is empty in that col 
 }
 
 static int getLastFilledColumn(Sheet sheet, int rowIdx) {
  int lastStoredCellNum = sheet.getRow(rowIdx).getLastCellNum();
  Row row = sheet.getRow(rowIdx);
  if (row != null) {
   for (int c = lastStoredCellNum; c >= 0; c--) {
    Cell cell = row.getCell(c);
    if (cell != null && cell.getCellType() != CellType.BLANK) return cell.getColumnIndex();  
   }       
  }
  return -1; // the sheet is empty in that row
 }
 
 static Map<Integer, String> getHeadings(Sheet sheet) {
  DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  int firstRow = sheet.getFirstRowNum();
  int firstCol = sheet.getRow(firstRow).getFirstCellNum();
  int lastCol = getLastFilledColumn(sheet, firstRow);   
  Map<Integer, String> headings = new HashMap<Integer, String>();
  Row row = sheet.getRow(firstRow);
  if (row != null) {
   for (int c = firstCol; c <= lastCol; c++) {
    Cell cell = row.getCell(c);
    headings.put(c, dataFormatter.formatCellValue(cell));      
   }
  }
  return headings;
 }
 
 static Map<Integer, String> getDataFormats(Sheet sheet, int headerRows) {
  int firstRow = sheet.getFirstRowNum();
  int firstCol = sheet.getRow(firstRow).getFirstCellNum();
  int lastCol = getLastFilledColumn(sheet, firstRow);  
  Map<Integer, String> dataFormats = new HashMap<Integer, String>();
  Row row = sheet.getRow(firstRow + headerRows);
  if (row != null) {
   for (int c = firstCol; c <= lastCol; c++) {
    Cell cell = row.getCell(c);
    if (cell != null) {
     dataFormats.put(c, cell.getCellStyle().getDataFormatString());
    }     
   }
  }
  return dataFormats;
 }
 
 static Map<Integer, TreeSet<String>> getUniqueItems(Sheet sheet, int headerRows) {
  DataFormatter dataFormatter = new DataFormatter(new Locale("en", "US"));
  dataFormatter.setUseCachedValuesForFormulaCells(true);
  int firstRow = sheet.getFirstRowNum();
  int firstCol = sheet.getRow(firstRow).getFirstCellNum();
  int lastCol = getLastFilledColumn(sheet, firstRow);  
  int lastRow = getLastFilledRow(sheet, firstCol);
  Map<Integer, TreeSet<String>> uniqueItemsMap = new HashMap<Integer, TreeSet<String>>();
  for (int c = firstCol; c <= lastCol; c++) {
   TreeSet<String> uniqueItems = new TreeSet<String>(String.CASE_INSENSITIVE_ORDER);    
   for (int r = firstRow + headerRows; r <= lastRow; r++) {
    Row row = sheet.getRow(r);
    if (row != null) {
     Cell cell = row.getCell(c);
     uniqueItems.add(dataFormatter.formatCellValue(cell));      
    }
    uniqueItemsMap.put(c, uniqueItems);
   }  
  }
  return uniqueItemsMap;
 }
 
 static AreaReference getUsedDataRange(Sheet sheet) {
  int firstRow = sheet.getFirstRowNum();
  int firstCol = sheet.getRow(firstRow).getFirstCellNum();
  int lastCol = getLastFilledColumn(sheet, firstRow);  
  int lastRow = getLastFilledRow(sheet, firstCol);
  AreaReference usedDataRange = null;
  try {
   usedDataRange = new AreaReference( // this might fail if firstRow, firstCol, lastRow and/or lastCol are -1
    new CellReference(firstRow, firstCol), 
    new CellReference(lastRow, lastCol), 
    SpreadsheetVersion.EXCEL2007
   );
  } catch (Exception ex) {
   // do nothing simply return usedDataRange as null
  }
  return usedDataRange;     
 }

 public static void main(String[] args) throws Exception {

  try (XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("./ExcelSource.xlsx")); 
       FileOutputStream fileout = new FileOutputStream("./ExcelResult.xlsx") ) {

   XSSFSheet dataSheet = workbook.getSheetAt(0);
      
   Map<Integer, String> headings = getHeadings(dataSheet);
   Map<Integer, String> dataFormats = getDataFormats(dataSheet, 1);
   
   Map<Integer, TreeSet<String>> uniqueItems = getUniqueItems(dataSheet, 1);
     
   AreaReference usedDataRange = getUsedDataRange(dataSheet);
   int firstCol = usedDataRange.getFirstCell().getCol();

   XSSFSheet pivotSheet = workbook.createSheet("Pivot"); 
   
   int startRow = 4; // row 0 to 3: 4 rows for report filters
   int endRow = startRow;
   CellReference pivotTableLocation = new CellReference(startRow, 0);
   XSSFPivotTable pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
   pivotTable.addReportFilter(0); // does not need a row additional
   pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 1).size(); // needs one row per unique item per row label
   pivotTable.addRowLabel(2); endRow += uniqueItems.get(firstCol + 1).size() * uniqueItems.get(firstCol + 2).size(); // needs one row per unique item per row label
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
   endRow += 1; // one row for the totals
   
   startRow = endRow + 1 + 4; // one row distance + 4 rows for report filters
   endRow = startRow;
   pivotTableLocation = new CellReference(startRow, 0);
   pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
   pivotTable.addRowLabel(2); endRow += uniqueItems.get(firstCol + 2).size(); // needs one row per unique item per row label
   pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 2).size() * uniqueItems.get(firstCol + 1).size(); // needs one row per unique item per row label
   pivotTable.addColLabel(0); endRow += 1; // needs one row for the heading
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
   endRow += 1; // one row for the totals
   
   startRow = endRow + 1 + 4; // one row distance + 4 rows for report filters
   endRow = startRow;
   pivotTableLocation = new CellReference(startRow, 0);
   pivotTable = pivotSheet.createPivotTable(usedDataRange, pivotTableLocation, dataSheet);
   pivotTable.addReportFilter(2); // does not need a row additional
   pivotTable.addRowLabel(1); endRow += uniqueItems.get(firstCol + 0).size(); // needs one row per unique item per row label
   pivotTable.addColLabel(0); endRow += 1; // needs one row for the heading
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Sum " + headings.get(firstCol + 3), dataFormats.get(firstCol + 3)); 
   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 4, "Sum " + headings.get(firstCol + 4), dataFormats.get(firstCol + 4)); endRow += 1; // needs one row for the heading
   endRow += 1; // one row for the totals
   
   System.out.println(endRow);
      
   workbook.write(fileout);

  }

 }
}

Overall, placing multiple pivot tables in one worksheet is not a good idea. The end user might want to change the pivot table settings in Excel's GUI. But this will fail very fast, because even the GUI does not shift rows or columns to take place for the changed pivot table. If the changed pivot table needs morre rows , the GUI only tells that pivot tables cannot overlap and fails.

So better approach would be to take one worksheet per pivot table.