Apache POI:如何设置数据透视表的显示布局? [英] Apache POI: How can I set the pivot table display layout?

问题描述

我尝试使用poi生成excel并在其中建立数据透视表. 像下面的代码.

I have tried use the poi to generator excel and build a pivot table in. like the code following.

这不是我想要的. 您可以在结果中看到.只有一个月带有过滤器图标.

this is not i want to get. you can see in the result. only month have a filter icon.

，但是当您在数据透视表中选择一个单元格时，请选择设计选项卡. 选择报告布局"，然后更改为以大纲形式显示 该过滤器将应用于month，group1和代码.

but when you select a cell in the pivot table then select the design tab. choose the 'Report Layout' then change to show in outline form the filter will apply to month,group1 and code.

有什么方法可以使用poi来完成我之前描述的步骤吗?

is there any way to use the poi to do the step I described before?

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import static org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;

public class ReportGenerator {
  public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    String[][] data = new String[][] { { "group1", "type", "BOA", "Month", "code", "uuid" },
        { "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" }, { "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },
        { "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" }, { "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },
        { "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" }, { "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },
        { "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" }, { "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }, };

    XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
    XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
    for (String[] dataRow : data) {
      XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
      for (String dataCell : dataRow) {
        XSSFCell cell = row.createCell(row.getPhysicalNumberOfCells());
        cell.setCellValue(dataCell);
      }
    }

    XSSFTable table = sheet.createTable();
    CTTable cttable = table.getCTTable();
    table.setDisplayName("table");
    cttable.setRef("A1:F9");
    cttable.setId(1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(6);

    int i = 1;
    for (String colName : data[0]) {
      CTTableColumn column = columns.addNewTableColumn();
      column.setId(++i);
      column.setName(colName);
    }

    CellRangeAddress c = new CellRangeAddress(0, 0, 0, 5);
    sheet.setAutoFilter(c);

    XSSFPivotTable pivotTable = pivot.createPivotTable(new AreaReference("A1:F9", SpreadsheetVersion.EXCEL2007), new CellReference("A1"), sheet);
    pivotTable.addRowLabel(3);
    pivotTable.addRowLabel(0);
    pivotTable.addRowLabel(4);

    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);
    pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);
    // pivotTable.getCTPivotTableDefinition().setOutline(false);
    pivotTable.getCTPivotTableDefinition().setColHeaderCaption("BOA");
    pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Month");

    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Count of SVVD");
    pivotTable.addDataColumn(2, true);
    pivotTable.addColLabel(2);
    pivotTable.addReportFilter(1);
    List<CTDataField> ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();
    for (CTDataField ctDataField : ctDataFields) {
      ctDataField.setShowDataAs(PERCENT_OF_ROW);
    }
    for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
      ctPivotField.setSubtotalTop(true);
      ctPivotField.setCompact(false);
    }
    pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");
    FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");
    wb.write(fileOut);
    wb.close();
  }
}

推荐答案

关于数据透视表的大纲形式的问题，您需要在代码中进行的更改是:

As of your question about the pivot table's outline form, all you needs changing in your code is:

不要不设置:

pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false);

因为这是设置轮廓的错误位置.

as this is the wrong place to set outline.

代替:

...
pivotTable.getCTPivotTableDefinition().setCompact(false);
pivotTable.getCTPivotTableDefinition().setCompactData(false);
pivotTable.getCTPivotTableDefinition().setOutline(true); 
pivotTable.getCTPivotTableDefinition().setOutlineData(true);
...

这将从紧凑布局切换到轮廓布局.

This switches from compact layout to outline layout.

但是您的其他XSSFTable代码对我来说导致损坏的Excel文件，因为您是在工作表而不是表中设置自动过滤器.我正在使用apache poi 4.1.0，打开Excel文件时，用于创建正在使用的表的代码会导致警告消息.然后Excel通过删除表来修复文件.不确定为什么完全需要此XSSFTable，但是为了完整起见，使用apache poi 4.1.0可以正常工作的完整示例.

But your additional XSSFTable code leads to a corrupt Excel file for me because you are setting the auto filter in the sheet instead of in the table. I am using apache poi 4.1.0 and the code for creating a table you are using leads to a warning message when opening the Excel file. Then Excel repairs the file by removing the table. Not sure why this XSSFTable is necessary at all but for completeness here the complete example which works correct using apache poi 4.1.0.

import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.usermodel.DataConsolidateFunction;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import static org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_ROW;

public class ReportGenerator {
  public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    String[][] data = new String[][] { 
      { "group1", "type", "BOA", "Month", "code", "uuid" },
      { "COS1", "type1", "Y", "2017-01", "AC2", "23-2214-232-1" },
      { "COS1", "type2", "N", "2017-01", "A3C", "23-2214-232-2" },
      { "COS2", "type1", "Y", "2018-01", "A3C", "23-2214-232-3" },
      { "COS1", "type2", "Y", "2018-01", "A3C", "23-2214-232-4" },
      { "COS1", "type1", "N/A", "2017-01", "A2C", "23-2214-232-5" },
      { "COS1", "type2", "Y", "2017-01", "A2C", "23-2214-232-6" },
      { "COS1", "type1", "N", "2018-01", "A2C", "23-2214-232-7" },
      { "COS1", "type2", "Y", "2018-01", "A2C", "23-2214-232-8" }
    };

    XSSFSheet sheet = (XSSFSheet) wb.createSheet("data");
    XSSFSheet pivot = (XSSFSheet) wb.createSheet("summary");
    XSSFRow row;
    XSSFCell cell;
    for (int r = 0; r < data.length; r++) {
      row = sheet.createRow(r);
      String[] rowData = data[r];
      for (int c = 0; c < rowData.length; c++) {
        cell = row.createCell(c);
        cell.setCellValue(rowData[c]);
      }
    }

    AreaReference areaReference = new AreaReference(
    new CellReference(0,0),
    new CellReference(data.length-1, data[0].length-1),
    SpreadsheetVersion.EXCEL2007);

    XSSFTable table = sheet.createTable(areaReference);
    table.setName("Table1");
    table.setDisplayName("Table1");
    table.getCTTable().addNewTableStyleInfo();
    XSSFTableStyleInfo style = (XSSFTableStyleInfo) table.getStyle();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);
    table.getCTTable().addNewAutoFilter().setRef(areaReference.formatAsString()); // set AutoFilter in table

    //CellRangeAddress c = new CellRangeAddress(0, 0, 0, 5);
    //sheet.setAutoFilter(c); // do **not** set AutoFilter in sheet since this range is in a table

    XSSFPivotTable pivotTable = pivot.createPivotTable(areaReference , new CellReference("A1"), sheet);
    pivotTable.addRowLabel(3);
    pivotTable.addRowLabel(0);
    pivotTable.addRowLabel(4);

    //pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setOutline(false); // don't do this
    pivotTable.getCTPivotTableDefinition().setColGrandTotals(false);
    // pivotTable.getCTPivotTableDefinition().setOutline(false);
    pivotTable.getCTPivotTableDefinition().setColHeaderCaption("BOA");
    pivotTable.getCTPivotTableDefinition().setRowHeaderCaption("Month");

    // switch from compact layout to outline layout
    pivotTable.getCTPivotTableDefinition().setCompact(false);
    pivotTable.getCTPivotTableDefinition().setCompactData(false);
    pivotTable.getCTPivotTableDefinition().setOutline(true);
    pivotTable.getCTPivotTableDefinition().setOutlineData(true);

    pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 5, "Count of SVVD");
    pivotTable.addDataColumn(2, true);
    pivotTable.addColLabel(2);
    pivotTable.addReportFilter(1);
    List<CTDataField> ctDataFields = pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldList();
    for (CTDataField ctDataField : ctDataFields) {
      ctDataField.setShowDataAs(PERCENT_OF_ROW);
    }
    for (CTPivotField ctPivotField : pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldList()) {
      ctPivotField.setSubtotalTop(true);
      ctPivotField.setCompact(false);
    }
    pivotTable.getCTPivotTableDefinition().getPivotTableStyleInfo().setName("PivotStyleMedium7");

    sheet.setSelected(false);
    wb.setActiveSheet(1);

    FileOutputStream fileOut = new FileOutputStream("pivotsample.xlsx");
    wb.write(fileOut);
    wb.close();
  }
}

这篇关于Apache POI:如何设置数据透视表的显示布局?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！