Apache的POI：如何设置数据透视表显示布局？(Apache POI: How can I se

2019-09-30 02:38发布

站内文章 / 移动开发
7 0 0

我曾尝试使用POI到发电机Excel和建立在数据透视表，就像下面的代码。

这不是我想要得到。 你可以在结果看。 仅一个月有一个过滤器图标。

但是当你在数据透视表中选择一个单元格，然后选择设计选项卡。 选择“报表布局”，然后更改轮廓展现形成过滤器将应用到一个月，1组和代码。

有没有办法使用POI做我前面所描述的步骤？ 

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

Answer 1:

当您对数据透视表的轮廓形式问题，所有你需要在你的代码改变是：

不要设置： 

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

因为这是错误的地方设置轮廓。

相反，这样做： 

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

这从紧凑的布局切换到概略的布局。

但是，你的额外XSSFTable代码将导致损坏Excel文件，对我来说，因为你正在设置在表，而不是在表中自动筛选。 我使用apache poi 4.1.0和创建打开当您使用导致的警告消息表的代码Excel文件。 然后， Excel通过删除表修复文件。 不知道为什么这XSSFTable是不必要的，但这里的完整性使用其作品正确完整的示例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: How can I set the pivot table display layout?
标签： java apache-poi
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~