Apache POI-有没有一种方法可以创建数据透视表，其中源表是SXSSFSheet? [英] Apache POI - is there a way to create a pivot table where the source table is a SXSSFSheet?

问题描述

是这种情况: 我必须使用SXSSFWorkbook(XSSFWorkbook的流版本)来创建我的Excel，因为我必须创建一个包含700000/800000行，约20列的工作表. 此工作表代表了我最终的数据透视表的源表.

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!!

问题是，当我尝试在该源上创建此数据透视表时，尽管AreaReference和CellReference参数都可以，但 XSSFPivotTable.createPivotTable 方法不起作用.

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.

如果我使用XSSFWorkbook(非流版本)的行较少，则一切正常，但我达不到目标！

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

推荐答案

所以我要做的是创建一个XSSFWorkbook，该XSSFWorkbook具有一个XSSFSheet，至少包含数据的标题，而另一个XSSFSheet用于数据透视表.然后在此XSSFSheet上创建XSSFPivotTable，但使对数据表的引用足够大以容纳以后的数据.

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.

然后我将根据该XSSFWorkbook创建SXSSFWorkbook，将数据表获取为SXSSFSheet，然后将大量数据流式传输到数据表中.

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.

完整示例:

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();

 }
}

这篇关于Apache POI-有没有一种方法可以创建数据透视表，其中源表是SXSSFSheet?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！