如何使用 poi 设置数据透视表中行标签的默认值

Question

我正在处理 Excel 报告。我需要生成数据透视表，其中某些特定字段作为行标签中的默认值，而不是选择所有字段。我正在使用 Apache POI。这是我加载 Excel 工作表时自动得到的这就是我需要的@Entitypublic class Actor {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long id;    private String name;    private String surname;    private String age;    @ManyToMany    @JoinTable(name = "movie_actor")    private List<Movie> movies = new ArrayList<>();    public void addMovie(Movie movie) {        movies.add(movie);        movie.getActors().add(this);    }    public void removeMovie(Movie movie) {        movies.remove(movie);        movie.getActors().remove(this);    }    // Constructors, getters and setters...    // Equals and hashCode methods a la     // https://vladmihalcea.com/how-to-implement-equals-and-hashcode-using-the-jpa-entity-identifier/}@Entitypublic class Movie {    @Id    @GeneratedValue(strategy = GenerationType.IDENTITY)    private Long id;    private String title;    private String genre;    private String year;    @ManyToMany(mappedBy = "movies", cascade = {CascadeType.PERSIST, CascadeType.MERGE})    private List<Actor> actors;    public Movie(String title, String genre, String year, List<Actor> actors) {        this.title = title;        this.genre = genre;        this.year = year;        actors.forEach(a -> a.addMovie(this));    }    // Getters and setters...}创建方法@GetMapping("/create")public void create() {    Actor actor1 = new Actor("Pedro", "Perez", "40");    Actor actor2 = new Actor("Alfredo", "Mora", "25");    Actor actor3 = new Actor("Juan", "Martinez", "20");    Actor actor4 = new Actor("Mario", "Arenas", "30");    List<Actor> actorList = new ArrayList<>();    actorList.add(actor1);    actorList.add(actor2);    actorList.add(actor3);    actorList.add(actor4);    Movie movie = new Movie("Titanic", "Drama", "1984", actorList);    movieService.create(movie);}

Answer 1

apache poi到目前为止，数据透视表的创建还只是初级阶段。

Apache poi<item t="default"/>添加与数据源中的行一样多的“默认”类型 ( ) 的数据透视字段项。这是因为他们不想查看数据，因此他们假设与数据源中的行一样多的不同值。这很好，因为 Excel 将在打开时重建其数据透视缓存。

但如果我们想要预选项目，那么这就不行了。那么我们必须知道有哪些项目是可以预选的。

因此，我们至少需要与预选编号项目一样多的项目：<item x="0"/><item x="1"/><item x="2"/>...。我们必须构建一个缓存定义，其中包含这些项目的共享元素。

为了满足该要求，我们需要确定数据源中所有行的唯一标签。然后，对于每个唯一标签，将该项目作为编号项目。然后构建一个缓存定义，其中包含这些项目的共享元素。然后将所有不需要的项目隐藏起来。

让我们有一个完整的示例，它创建您在问题图片中显示的内容。您问题中的代码不完整，因此我必须预测您有哪些数据作为数据源。请尽量避免在进一步的问题中出现这种情况，并始终提供最小的、可重现的示例。否则您将无法得到进一步的答案。

import java.io.FileOutputStream;

import org.apache.poi.ss.*;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.ss.util.*;

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

class CreatePivotTablePercentAndFilter {

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

  try (Workbook workbook = new XSSFWorkbook(); 

       FileOutputStream fileout = new FileOutputStream("ooxml-pivottable.xlsx") ) {

   Sheet pivotSheet = workbook.createSheet("Pivot");

   Sheet dataSheet = workbook.createSheet("Data");

   setCellData(dataSheet);

   AreaReference areaReference = new AreaReference("A1:D5", SpreadsheetVersion.EXCEL2007);

   

   XSSFPivotTable pivotTable = ((XSSFSheet)pivotSheet).createPivotTable(areaReference, new CellReference("A4"), dataSheet);

   pivotTable.addRowLabel(0);

 

   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);

   pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1,"% of value");

   pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(1).setShowDataAs(

    org.openxmlformats.schemas.spreadsheetml.x2006.main.STShowDataAs.PERCENT_OF_COL);

   DataFormat dataformat = workbook.createDataFormat();

   short numFmtId = dataformat.getFormat("0.00%");

   pivotTable.getCTPivotTableDefinition().getDataFields().getDataFieldArray(1).setNumFmtId(numFmtId);

   /*   

   Apache poi adds 5 pivot field items of type "default" (<item t="default"/>) for each row label here. 

   This is because there are 5 rows (A1:D5) and, because they don't want have a look at the data, 

   they are assuming max 5 different values. 

   This is fine because Excel will rebuild its pivot cache while opening. 

   But if we want preselect items, then this is not fine. Then we must know what items there are that can be preselected.

   So we need at least as much items as we want preselect being numbered items: <item x="0"/><item x="1"/><item x="2"/>...

   And we must build a cache definition which has shared elements for those items.

   To fulfill that we need determine the unique labels in column. 

   Then for each unique label take the item as numbered item. 

   Then build a cache definition which has shared elements for those items. 

   Then set all not wanted items hidden.

   */

   //determine unique labels in column 0

   java.util.TreeSet<String> uniqueItems = new java.util.TreeSet<String>(String.CASE_INSENSITIVE_ORDER);

   for (int r = areaReference.getFirstCell().getRow()+1; r < areaReference.getLastCell().getRow()+1; r++) {

    uniqueItems.add(dataSheet.getRow(r).getCell(areaReference.getFirstCell().getCol()).getStringCellValue());

   }

   System.out.println(uniqueItems);

   int i = 0;

   for (String item : uniqueItems) {

    //take the items as numbered items: <item x="0"/><item x="1"/><item x="2"/>

    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemArray(i).unsetT();

    pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemArray(i).setX((long)i);

    //build a cache definition which has shared elements for those items 

    //<sharedItems><s v="Jack"/><s v="Jane"/><s v="Tarzan"/><s v="Terk"/></sharedItems>

    pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldArray(0)

    .getSharedItems().addNewS().setV(item);

    i++;

   }

   //Now we can predefinite a filter.

   //If the need is selecting multiple items, first MultipleItemSelectionAllowed needs to be set.

   pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).setMultipleItemSelectionAllowed(true);

   //Then set H(idden) true for all items which not shall be selected. All except "Jane" in this case.

   i = 0;

   for (String item : uniqueItems) {

    if (!"Jane".equals(item))

     pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(0).getItems().getItemArray(i).setH(true);

    i++;

   }

   workbook.write(fileout);

  }

 }

 static void setCellData(Sheet sheet) {

  Row row;

  Cell cell;

  Object[][] data = new Object[][]{

   new Object[]{"Names", "Values", "ColC", "ColD"},

   new Object[]{"Jane", 10d, "?", "?"},

   new Object[]{"Tarzan", 5d, "?", "?"},

   new Object[]{"Terk", 10d, "?", "?"},

   new Object[]{"Jack", 10d, "?", "?"}

  };

  for (int r = 0; r < data.length; r++) {

   row = sheet.createRow(r);

   Object[] rowData = data[r];

   for (int c = 0; c < rowData.length; c++) {

    cell = row.createCell(c);

    if (rowData[c] instanceof String) {

     cell.setCellValue((String)rowData[c]);

    } else if (rowData[c] instanceof Double) {

     cell.setCellValue((Double)rowData[c]);

    }

   }

  }

 }

}