Apache POI - 有没有办法在源表是 SXSSFSheet 的情况下创建数据透视表?

2022-01-22 00:00:00 pivot excel java apache-poi

是这样的:我必须使用 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!!

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

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

斯特凡诺

推荐答案

SXSSFWorkbook 可以从 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();

 }
}

相关文章