XSSF (POI) - Changing the font on a pivot table

advertisements

I am using 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:

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

<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

<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

<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


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

General steps:

  1. Create a spreadsheet with a pivot table in excel or use the sample for poi.
  2. Open the spreadsheet in excel and save.
  3. Open the spreadsheet in poi.
  4. Create a CTDxfs entry. This is "font" for tables.
  5. Create a pivotArea definition with the 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.
  • setDataOnly(false) allows the change to affect the label as well as the data.
  • setLabelOnly(true) restricts the change to the label value only. If you want to change the whole column/row, set to 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.
  • 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.

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:

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