XSSF (POI) -更改pivot表上的字体

I am using Apache POI 3.12:

我使用Apache POI 3.12:

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>

How do I change the font within a pivot table? Examining the generated .xlsx after changing the font for one cell (I7) to size 8pt shows the following changes:

如何更改透视表中的字体?检查将一个单元格(I7)的字体更改为8pt后生成的.xlsx，可以看到以下更改:

styles.xml, within the tag as the 2nd entry:

风格。xml，标签内作为第二项:

<font>
    <sz val="8"/>
    <color indexed="8"/>
    <name val="Calibri"/>
    <family val="2"/>
    <scheme val="minor"/>
</font>

within the <cellXfs> tag as the 5th entry:
<xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyFont="1"/>

New tag: dxfs:
<dxfs count="1">
    <dxf>
        <font>
            <sz val="8"/>
        </font>
    </dxf>
</dxfs>

pivottable.xml

pivottable.xml

<formats count="1">
    <format dxfId="0">
        <pivotArea collapsedLevelsAreSubtotals="1" fieldPosition="0">
        <references count="2">
        <reference field="4294967294" count="1" selected="0">
        <x v="0"/>
        </reference>
        <reference field="0" count="1">
        <x v="0"/>
        </reference>
        </references>
        </pivotArea>
    </format>
</formats>

sheet1.xml

sheet1 . xml

<c r="I7" s="4">

Note: I may close this as a self-answer as I am still trying to solve it myself. However, I've been at this for almost a week. POI Pivot table sample

注意:我可能会以自我回答的方式关闭它，因为我还在尝试自己解决它。然而，我已经做了差不多一个星期了。POI数据透视表样本

2 个解决方案

#1

This is a partial answer since it requires using excel to setup rather than pure poi.

这是部分答案，因为它需要使用excel来设置，而不是纯粹的poi。

General steps:

一般的步骤:

1. Create a spreadsheet with a pivot table in excel or use the sample for poi.
2. 使用excel中的pivot表创建电子表格，或者使用poi的示例。
3. Open the spreadsheet in excel and save.
4. 在excel中打开电子表格并保存。
5. Open the spreadsheet in poi.
6. 在poi中打开电子表格。
7. Create a CTDxfs entry. This is "font" for tables.
8. 创建一个CTDxfs条目。这是表的“字体”。
9. Create a pivotArea definition with the CTDXfs id.
10. 使用CTDXfs id创建一个数据透视区域定义。

On to the code:

上代码:

private static CTFormats getFormats(XSSFPivotTable pivotTable) {
    CTFormats formats = pivotTable.getCTPivotTableDefinition().getFormats();
    if(formats==null)
        formats=pivotTable.getCTPivotTableDefinition().addNewFormats();
    return formats;
}
private static int createDXFs(XSSFWorkbook wb,int font) {
    CTDxfs dxfs=wb.getStylesSource().getCTStylesheet().getDxfs();
    if(dxfs==null)
        dxfs=wb.getStylesSource().getCTStylesheet().addNewDxfs();
    dxfs.setCount(dxfs.getCount()+1);
    CTDxf dxf=dxfs.addNewDxf();
    CTFontSize fontSize=dxf.addNewFont().addNewSz();
    fontSize.setVal(font);
    return (int) dxfs.getCount()-1;
}
public static void setAxisFont(CTFormats pivotTableFormats,int dxfId) {
    CTFormat format=pivotTableFormats.addNewFormat();
    format.setDxfId(dxfId);
    CTPivotArea pivotArea = format.addNewPivotArea();
    pivotArea.setDataOnly(false);
    pivotArea.setLabelOnly(true);
    pivotArea.setOutline(false);
    pivotArea.setFieldPosition(0L);
    pivotArea.setAxis(STAxis.AXIS_ROW);
    pivotArea.setType(STPivotAreaType.BUTTON);
}
public static void setColHeaderFont(CTFormats pivotTableFormats,int dxfId,int colInd) {
    CTFormat format=pivotTableFormats.addNewFormat();
    format.setDxfId(dxfId);
    CTPivotArea pivotArea = format.addNewPivotArea();
    pivotArea.setDataOnly(false);
    pivotArea.setLabelOnly(true);
    pivotArea.setOutline(false);
    CTPivotAreaReferences references = pivotArea.addNewReferences();
    CTPivotAreaReference reference = references.addNewReference();

    reference.setField(new Long(Integer.MAX_VALUE)*2);
    CTIndex x = reference.addNewX();
    x.setV(colInd); //Column
}
public static void setLabelFont(CTFormats pivotTableFormats,int dxfId, int rowInd) {
    CTFormat format=pivotTableFormats.addNewFormat();
    format.setDxfId(dxfId);
    CTPivotArea pivotArea = format.addNewPivotArea();
    pivotArea.setDataOnly(false);
    pivotArea.setLabelOnly(true);
    pivotArea.setFieldPosition(0L);
    CTPivotAreaReferences references = pivotArea.addNewReferences();
    CTPivotAreaReference reference = references.addNewReference();

    reference.setField(0L);
    CTIndex x = reference.addNewX();
    x.setV(rowInd); //Row
}
public static void setDataElementFont(CTFormats pivotTableFormats,int dxfId,int col,int row) {
    CTFormat format=pivotTableFormats.addNewFormat();
    format.setDxfId(dxfId);
    CTPivotArea pivotArea = format.addNewPivotArea();
    //Default values, don't need to explicitly define.
    //pivotArea.setDataOnly(true);
    //pivotArea.setLabelOnly(false);
    CTPivotAreaReferences references = pivotArea.addNewReferences();
    CTPivotAreaReference reference = references.addNewReference();

    reference.setField(new Long(Integer.MAX_VALUE)*2);
    CTIndex x = reference.addNewX();
    x.setV(col); //Column
    reference = references.addNewReference();
    reference.setField(0L);
    x = reference.addNewX();
    x.setV(row); //Row
}

Notes:

注:

• setOutline(false) is required to access the column headers.
• 访问列标头需要setOutline(false)。
• setDataOnly(false) allows the change to affect the label as well as the data.
• setDataOnly(false)允许更改影响标签和数据。
• setLabelOnly(true) restricts the change to the label value only. If you want to change the whole column/row, set to false.
• setLabelOnly(true)只限制对标签值的更改。如果要更改整个列/行，请设置为false。
• Reference field value of unsigned int max value defines the reference as a column, the other valid value is 0 which defines the reference as a row.
• 无符号整数最大值的引用字段值将引用定义为列，另一个有效值为0，将引用定义为行。
• If the col/row reference is undefined such as in setColHeaderFont/setLabel font, it affects the whole column/row. This may be desirable for formatting particular columns.
• 如果col/row引用没有定义，例如setColHeaderFont/setLabel字体，它会影响整个列/行。这对于格式化特定列可能是可取的。

Warning The poi-ooxml-schemas used by maven for poi 3.12 does not include CTFormats. This can be overriden by excluding it and including the 1.1 version:

警告maven对poi 3.12使用的poi-ooxml-schemas不包含ctformat。这可以通过排除它和包括1.1版本来进行概述:

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>ooxml-schemas</artifactId>
        <version>1.1</version>
</dependency>

#2

This is a full answer but is much more involved than the prior answer of using excel.

这是一个完整的答案，但是比之前使用excel的答案要复杂得多。

General Steps:

一般的步骤:

1. Create a spreadsheet with a pivot table in excel or use the sample for poi.
2. 使用excel中的pivot表创建电子表格，或者使用poi的示例。
3. Create a pivot cache definition.
4. 创建一个pivot缓存定义。
5. Create a CTDxfs entry. This is "font" for tables. (See other-answer)
6. 创建一个CTDxfs条目。这是表的“字体”。(见其他答案)
7. Create a pivotArea definition with the CTDXfs id. (See other-answer)
8. 使用CTDXfs id创建一个数据透视区域定义。

Step 2 looks so innocuous by itself but is the key to the whole process. When Excel opens the .xlsx document, it re-verifies all of the pivotTable entries deleting any that no longer exist including the CTDXfs entry. Then it generates the cache, then it applies any formatting. However, all the formatting rules are deleted during the verification process if the cache doesn't exist yet!

第2步本身看起来无伤大雅，但却是整个过程的关键。当Excel打开.xlsx文档时，它将重新验证所有的数据透视表条目，删除任何不再存在的条目，包括CTDXfs条目。然后它生成缓存，然后应用任何格式。但是，如果缓存还不存在，则在验证过程中删除所有格式化规则!

Below is the code-block I used to generate the pivotCache. This is far longer than a normal * answer, but not sure if this is preferred or "general guidance" on how to do it.

下面是我用来生成数据透视缓存的代码块。这比一个普通的*答案要长得多，但是不确定这是首选的还是如何实现的“一般指导”。

If you need to maintain or extend this, rename the .xlsx to .zip, extract it, then look at xl\pivotTables\pivotTable1.xml, xl\pivotCache\pivotCacheDefinition1.xml before and after saving it in excel. If this is working correctly, pivotCacheDefinition should be mostly unchanged after saving it in excel.

如果您需要维护或扩展它，请将.xlsx重命名为.zip，提取它，然后查看xl\数据透视表\数据透视表1。xml,xl \ pivotCache \ pivotCacheDefinition1。在excel中保存之前和之后的xml。如果操作正确，数据透视表缓存定义在保存到excel后应该基本保持不变。

public class PivotUtilitiesExample {
    public static void updateCache(XSSFPivotTable pivotTable) {
        updateCache(pivotTable,STAxis.AXIS_ROW);
        updateCache(pivotTable,STAxis.AXIS_COL);
        updateCache(pivotTable,STAxis.AXIS_PAGE);
    }
    /**
     * Updates the pivotCacheDefinition.xml file.  This must be run before any formatting is done.
     * However, it must also be run *AFTER* the pivotTable's source data is created and all label definitions are defined.
     *   the labels are sorted by default.
     * @param pivotTable
     * @param rowLabel if true, updates rowLabels, if false, updates columnLabels.
     */
    private static void updateCache(XSSFPivotTable pivotTable,STAxis.Enum axisType) {
        XSSFSheet sheet=(XSSFSheet) pivotTable.getDataSheet();
        AreaReference pivotArea = new AreaReference(pivotTable.getPivotCacheDefinition().
                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
        CellReference firstCell = pivotArea.getFirstCell();
        CellReference lastCell = pivotArea.getLastCell();
        List<Integer> labelColumns=getLabelColumns(pivotTable,axisType);

        for(int labelCtr=0;labelCtr<labelColumns.size();++labelCtr) {
            CTSharedItems sharedItems=getSharedItems(pivotTable,labelColumns.get(labelCtr));
            //The pivotField entry associated will be the nth axis="axisRow" entry where N is the row label #.
            CTPivotField pivotField=getPivotField(pivotTable,axisType,labelCtr);
            CTItems items=pivotField.getItems();

            ArrayList<String> toCache=new ArrayList<String>(lastCell.getRow()-firstCell.getRow());
            //To make this work, sharedItems and pivotFields must be properly populated.
            //sharedItems should look like:
            //<cacheField name="Names" numFmtId="0"> (Cell A1)
            //<sharedItems count="3">                (Count of unique rows)
            //<s v="Jane"/>                          (Cell B1)
            //<s v="Tarzan"/>                        (Cell C1)
            //<s v="Terk"/>                          (Cell D1)
            //</sharedItems>
            //pivotFields should have an entry like this:
            //<pivotField axis="axisRow" showAll="0">
            //<items count="4">
            //<item x="0"/>
            //<item x="1"/>
            //<item x="2"/>
            //<item t="default"/>
            //</items>
            //Initially, POI will populate with:
            //<pivotField axis="axisRow" showAll="0">
            //<items count="4">
            //<item t="default"/>
            //<item t="default"/>
            //<item t="default"/>
            //<item t="default"/>
            //</items>
            //Start with firstCell.getRow()+1 since the first row is the column name.
            for(int i=firstCell.getRow()+1;i<=lastCell.getRow();++i) {
                String s=sheet.getRow(i).getCell(firstCell.getCol()+labelColumns.get(labelCtr)).getStringCellValue();
                //Only add unique entries.
                if(!toCache.contains(s))
                    toCache.add(s);
            }
            //Blank entries cannot be sorted unless they are specially entered after an M tag.
            //  For most projects this'll be overkill.
            boolean containsBlank=false;
            if(toCache.contains("")) {
                toCache.remove("");
                containsBlank=true;
            }
            //Remove the old cache list.
            for(int i=items.sizeOfItemArray()-1;i>=0;--i)
                items.removeItem(i);
            for(int i=sharedItems.sizeOfBArray()-1;i>=0;--i)
                sharedItems.removeB(i);
            for(int i=sharedItems.sizeOfDArray()-1;i>=0;--i)
                sharedItems.removeD(i);
            for(int i=sharedItems.sizeOfEArray()-1;i>=0;--i)
                sharedItems.removeE(i);
            for(int i=sharedItems.sizeOfMArray()-1;i>=0;--i)
                sharedItems.removeM(i);
            for(int i=sharedItems.sizeOfNArray()-1;i>=0;--i)
                sharedItems.removeN(i);
            for(int i=sharedItems.sizeOfSArray()-1;i>=0;--i)
                sharedItems.removeS(i);
            sharedItems.setCount(sharedItems.getDomNode().getChildNodes().getLength());
            items.setCount(items.sizeOfItemArray());
            for(int i=0;i<toCache.size();++i) {
                CTString string;
                CTItem item;
                string=sharedItems.addNewS();
                sharedItems.setCount(sharedItems.getDomNode().getChildNodes().getLength());
                string.setV(toCache.get(i));

                item=items.addNewItem();
                items.setCount(items.sizeOfItemArray());
                item.setX(i);
            }
            //Create the special blank tag.
            if(containsBlank) {
                int mPosition;
                sharedItems.addNewM();
                mPosition=sharedItems.sizeOfSArray();
                CTString s=sharedItems.addNewS();
                s.setV("");
                s.setU(true);
                sharedItems.setCount(sharedItems.getDomNode().getChildNodes().getLength());
                sharedItems.setContainsBlank(true);
                CTItem item=items.addNewItem();
                item.setM(true);
                item.setX(sharedItems.sizeOfSArray());
                item=items.addNewItem();
                item.setX(mPosition);
                items.setCount(items.sizeOfItemArray());
            }
            //Add the t="default" entry, required for subtotals.
            if(!pivotField.isSetDefaultSubtotal() || pivotField.getDefaultSubtotal()==true) {
                CTItem item;
                item=items.addNewItem();
                items.setCount(items.sizeOfItemArray());
                item.setT(STItemType.DEFAULT);
            }
        }
    }
    //Returns the label columns for all AXIS.  Default POI only has a method for RowLabelColumns.
    private static List<Integer> getLabelColumns(XSSFPivotTable pivotTable,STAxis.Enum axisType) {
        List<Integer> labelColumns;
        if(axisType.equals(STAxis.AXIS_ROW))
            labelColumns=pivotTable.getRowLabelColumns();
        else if(axisType.equals(STAxis.AXIS_COL)) {
            List<CTField> fieldList = pivotTable.getCTPivotTableDefinition().getColFields().getFieldList();
            labelColumns=new ArrayList(fieldList.size());
            for(CTField field:fieldList)
                labelColumns.add(field.getX());
        } else if(axisType.equals(STAxis.AXIS_PAGE)) {
            List<CTPageField> fieldList = pivotTable.getCTPivotTableDefinition().getPageFields().getPageFieldList();
            labelColumns=new ArrayList(fieldList.size());
            for(CTPageField field:fieldList)
                labelColumns.add(field.getFld());
        } else {
            throw new UnsupportedOperationException("Error, STAxis: "+axisType+" is not supported");
        }
        return labelColumns;
    }
    //Returns the sharedItems entry associated with a particular labelColumn.
    private static CTSharedItems getSharedItems(XSSFPivotTable pivotTable,int columnIndex) {
        XSSFSheet sheet=(XSSFSheet) pivotTable.getDataSheet();
        AreaReference pivotArea = new AreaReference(pivotTable.getPivotCacheDefinition().
                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
        CellReference firstCell = pivotArea.getFirstCell();
        String labelName=sheet.getRow(firstCell.getRow()).getCell(firstCell.getCol()+columnIndex).getStringCellValue();
        List<CTCacheField> cacheFieldList = pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().getCacheFields().getCacheFieldList();
        CTSharedItems sharedItems=null;
        //The sharedItem entry associated will have name=labelColumn's name.
        for(int i=0;i<cacheFieldList.size();++i)
            if(labelName.equals(cacheFieldList.get(i).getName())) {
                sharedItems=cacheFieldList.get(i).getSharedItems();
                break;
            }
        //Should never be true.
        if(sharedItems==null) {
            throw new RuntimeException("Error, unable to find sharedItems entry in pivotCacheDefinition.xml");
        }
        return sharedItems;
    }
    //Return the nth pivotField entry from the pivotTable definition of a particular Axis.
    private static CTPivotField getPivotField(XSSFPivotTable pivotTable,STAxis.Enum axisType,int index) {
        CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
        CTPivotField pivotField=null;
        for(int i=0,axisRowNum=-1;i<pivotFields.getPivotFieldList().size();++i) {
            if(axisType.equals(pivotFields.getPivotFieldList().get(i).getAxis()))
                ++axisRowNum;
            if(axisRowNum==index) {
                pivotField=pivotFields.getPivotFieldList().get(i);
                break;
            }
        }
        if(pivotField==null)
            throw new RuntimeException("Error, unable to find pivotField entry in pivotTable.xml");
        return pivotField;
    }
}