我正在處理 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]);
? ? }
? ?}
? }
?}
}
添加回答
舉報
0/150
提交
取消