How can I create an Apache POI Pivot using another workbook as data source?

This is nothing what apache poi provides up to now. It can be done if one knows what Excel stores in that case.

The main principle of creating pivot tables is the same. The difference is that the pivot cache's worksheet source additional contains a rId which refers to a external relationship to the source workbook. So the additional task is to create that external relationship to the source workbook and to set that rId to the worksheet source of pivot cache.

The following code shows how to do this:

import java.io.File;
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.DataConsolidateFunction;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.xssf.usermodel.*;

class CreatePivotTable {

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

  String[] headers = new String[] { "Company", "Status", "Counter" };
  Object[][] sheetData = {
   {"Company 1", "OK", "x"},
   {"Company 1", "NOK", "x"},
   {"Company 2", "NOK", "x"},
   {"Company 1", "OK", "x"},
   {"Company 3", "OK", "x"},
   {"Company 1", "NOK", "x"},
  };

  //File sourceWorkbookFile = new File("C:/Users/axelr/Documents/PivotData.xlsx");
  //File pivotWorkbookFile = new File("C:/Users/axelr/Documents/PivotTable.xlsx");
  File sourceWorkbookFile = new File("PivotData.xlsx");
  File pivotWorkbookFile = new File("PivotTable.xlsx");
  
  // creating source workbook  
  try (XSSFWorkbook workbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream(sourceWorkbookFile) ) {

   XSSFSheet dataSheet = workbook.createSheet("Data");
   XSSFRow row;
   XSSFCell cell;
   int r = 0;
   row = dataSheet.createRow(r++);
   int c = 0;
   for (String header : headers) {
    cell = row.createCell(c++);
    cell.setCellValue(header);
   }
   for (Object[] dataRow : sheetData) {
    row = dataSheet.createRow(r++);
    c = 0;
    for (Object value : dataRow) {
     cell = row.createCell(c++);
     if (value instanceof String) {
      cell.setCellValue((String)value);
     } //else if...
    }
   } 
   workbook.write(fileout);
  }
  
  // creating pivot workbook  
  try (XSSFWorkbook sourceWorkbook = new XSSFWorkbook(new FileInputStream(sourceWorkbookFile));
       XSSFWorkbook pivotWorkbook = new XSSFWorkbook(); 
       FileOutputStream fileout = new FileOutputStream(pivotWorkbookFile) ) {
           
   XSSFSheet dataSheet = sourceWorkbook.getSheet("Data");
   XSSFSheet pivotSheet = pivotWorkbook.createSheet("Pivot");

   AreaReference areaReference = new AreaReference(
    new CellReference(0, 0),
    new CellReference(dataSheet.getLastRowNum(), dataSheet.getRow(0).getLastCellNum()-1),
    SpreadsheetVersion.EXCEL2007);

   XSSFPivotTable pivotTable = pivotSheet.createPivotTable(areaReference, new CellReference("A4"), dataSheet);
   
   XSSFPivotCacheDefinition pivotCacheDefinition = pivotTable.getPivotCacheDefinition();
   String rId =  pivotCacheDefinition.getPackagePart().addRelationship( 
    sourceWorkbookFile.toURI(), 
    TargetMode.EXTERNAL, 
    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath")
    .getId();
   pivotCacheDefinition.getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().setId(rId); 

   pivotTable.addRowLabel(0);
   pivotTable.addRowLabel(1);
   pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 2);

   pivotWorkbook.write(fileout);

  }

 }
}

This code is tested and works using current apache poi versions 4.1.2 and 5.0.0.