apache poi到目前為止，數(shù)據(jù)透視表的創(chuàng)建還只是初級(jí)階段。
Apache poi<item t="default"/>添加與數(shù)據(jù)源中的行一樣多的“默認(rèn)”類(lèi)型 ( ) 的數(shù)據(jù)透視字段項(xiàng)。這是因?yàn)樗麄儾幌氩榭磾?shù)據(jù)，因此他們假設(shè)與數(shù)據(jù)源中的行一樣多的不同值。這很好，因?yàn)?Excel 將在打開(kāi)時(shí)重建其數(shù)據(jù)透視緩存。
但如果我們想要預(yù)選項(xiàng)目，那么這就不行了。那么我們必須知道有哪些項(xiàng)目是可以預(yù)選的。
因此，我們至少需要與預(yù)選編號(hào)項(xiàng)目一樣多的項(xiàng)目：<item x="0"/><item x="1"/><item x="2"/>...。我們必須構(gòu)建一個(gè)緩存定義，其中包含這些項(xiàng)目的共享元素。
為了滿足該要求，我們需要確定數(shù)據(jù)源中所有行的唯一標(biāo)簽。然后，對(duì)于每個(gè)唯一標(biāo)簽，將該項(xiàng)目作為編號(hào)項(xiàng)目。然后構(gòu)建一個(gè)緩存定義，其中包含這些項(xiàng)目的共享元素。然后將所有不需要的項(xiàng)目隱藏起來(lái)。
讓我們有一個(gè)完整的示例，它創(chuàng)建您在問(wèn)題圖片中顯示的內(nèi)容。您問(wèn)題中的代碼不完整，因此我必須預(yù)測(cè)您有哪些數(shù)據(jù)作為數(shù)據(jù)源。請(qǐng)盡量避免在進(jìn)一步的問(wèn)題中出現(xiàn)這種情況，并始終提供最小的、可重現(xiàn)的示例。否則您將無(wú)法得到進(jìn)一步的答案。
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]);
? ? }
? ?}
? }
?}
}
