亚洲在线久爱草,狠狠天天香蕉网,天天搞日日干久草,伊人亚洲日本欧美

為了賬號安全,請及時綁定郵箱和手機立即綁定
已解決430363個問題,去搜搜看,總會有你想問的

如何使用 poi 設置數據透視表中行標簽的默認值

如何使用 poi 設置數據透視表中行標簽的默認值

HUH函數 2023-09-06 14:50:56
我正在處理 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);}
查看完整描述

1 回答

?
慕容708150

TA貢獻1831條經驗 獲得超4個贊

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

? ? }

? ?}

? }

?}

}


查看完整回答
反對 回復 2023-09-06
  • 1 回答
  • 0 關注
  • 202 瀏覽
慕課專欄
更多

添加回答

舉報

0/150
提交
取消
微信客服

購課補貼
聯系客服咨詢優惠詳情

幫助反饋 APP下載

慕課網APP
您的移動學習伙伴

公眾號

掃描二維碼
關注慕課網微信公眾號