java - Apache POI - is there a way to create a pivot table where the source table is a SXSSFSheet?

Question

This is the case: I have to use SXSSFWorkbook (Streaming version of XSSFWorkbook) to create my Excel, because I have to create a sheet with 700000/800000 rows with about 20 columns. This sheet represents the source table for my final Pivot.

SXSSFWorkbook workbook();
XSSFSheet pivotSheet = workbook.getXSSFWorkbook().createSheet("Pivot sheet");
AreaReference ar = ....:
CellReference cr = ....;
XSSFPivotTable pivotTable = pivotSheet.createPivotTable(ar, cr); // ERROR!!

The problem is that, when I try to create this Pivot on that source, the XSSFPivotTable.createPivotTable method not work, despite AreaReference and CellReference arguments are ok.

If I use XSSFWorkbook (not streaming version) with less rows, all is ok, but I do not reach my goal!

Can someone give me a solution? Thank you very much!!!!!

Stefano

Answer 1

SXSSFWorkbook can be created from XSSFWorkbook.

So what I would do is creating XSSFWorkbook having a XSSFSheet with at least the headings of the data and another XSSFSheet for the pivot table. Then creating the XSSFPivotTable on this XSSFSheet but making the reference to the data sheet big enough for later data.

Then I would creating the SXSSFWorkbook from this XSSFWorkbook, getting the data sheet as SXSSFSheet and then streaming the big amount of data into the data sheet.

Complete example:

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

import org.apache.poi.xssf.streaming.*;

import java.util.Random;
import java.io.FileOutputStream;

class SXSSFPivotTableTest {

 private static void streamCellData(Sheet sheet, int rowsCount) {

  for (int r = 1; r <= rowsCount; r++) {
   Row row = sheet.createRow(r);
   Cell cell = row.createCell(0);
   cell.setCellValue("Name " + ((r-1) % 4 + 1));
   cell = row.createCell(1);
   cell.setCellValue(r * new java.util.Random().nextDouble());
   cell = row.createCell(2);
   cell.setCellValue(r * new java.util.Random().nextDouble());
   cell = row.createCell(3);
   cell.setCellValue("City " + ((r-1) % 3 + 1));  
  }

 }

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

  int rowsCount = 1000000;

  //first create XSSFWorkbook
  XSSFWorkbook wb = new XSSFWorkbook();

  //create XSSFSheet with at least the headings
  XSSFSheet sheet = wb.createSheet("Sheet1");
  Row row = sheet.createRow(0);
  Cell cell = row.createCell(0);
  cell.setCellValue("Name");
  cell = row.createCell(1);
  cell.setCellValue("Value1");
  cell = row.createCell(2);
  cell.setCellValue("Value2");
  cell = row.createCell(3);
  cell.setCellValue("City");

  //create XSSFSheet for pivot table
  XSSFSheet pivotSheet = wb.createSheet("Pivot sheet");

  //create pivot table
  XSSFPivotTable pivotTable = pivotSheet.createPivotTable(
    new AreaReference(new CellReference("Sheet1!A1"), 
                      new CellReference("Sheet1!D" + (rowsCount +1)), //make the reference big enough for later data
                      SpreadsheetVersion.EXCEL2007),
    new CellReference("A5"));
  //Configure the pivot table
  //Use first column as row label
  pivotTable.addRowLabel(0);
  //Sum up the second column
  pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
  //Avarage the third column
  pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
  //Add filter on forth column
  pivotTable.addReportFilter(3);

  //now create SXSSFWorkbook from XSSFWorkbook
  SXSSFWorkbook swb = new SXSSFWorkbook(wb);
  SXSSFSheet ssheet = swb.getSheet("Sheet1");

  //now stream the big amount of data to build the pivot table on into Sheet1
  streamCellData(ssheet, rowsCount);

  swb.write(new FileOutputStream("SXSSFPivotTableTest.xlsx"));
  swb.close();
  swb.dispose();

 }
}