Attachment 31216 Details for Bug 56020 – Patch for creating PivotTables

Patch for creating PivotTables

pivottable-diff.txt (text/plain), 141.03 KB, created by Sofia Larsson on 2014-01-16 15:11:19 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Sofia Larsson

Created: 2014-01-16 15:11:19 UTC

Size: 141.03 KB

patch

obsolete

>ÿþdiff --git src/examples/src/org/apache/poi/xssf/usermodel/examples/CreatePivotTable.java src/examples/src/org/apache/poi/xssf/usermodel/examples/CreatePivotTable.java
>new file mode 100644
>index 0000000..a2c61e2
>--- /dev/null
>+++ src/examples/src/org/apache/poi/xssf/usermodel/examples/CreatePivotTable.java
>@@ -0,0 +1,100 @@
>+/* ====================================================================
>+ Licensed to the Apache Software Foundation (ASF) under one or more
>+ contributor license agreements.  See the NOTICE file distributed with
>+ this work for additional information regarding copyright ownership.
>+ The ASF licenses this file to You under the Apache License, Version 2.0
>+ (the "License"); you may not use this file except in compliance with
>+ the License.  You may obtain a copy of the License at
>+
>+ http://www.apache.org/licenses/LICENSE-2.0
>+
>+ Unless required by applicable law or agreed to in writing, software
>+ distributed under the License is distributed on an "AS IS" BASIS,
>+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+ See the License for the specific language governing permissions and
>+ limitations under the License.
>+ ==================================================================== */
>+package org.apache.poi.xssf.usermodel.examples;
>+
>+import java.io.FileNotFoundException;
>+import java.io.FileOutputStream;
>+import java.io.IOException;
>+
>+import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
>+import org.apache.poi.ss.usermodel.Cell;
>+import org.apache.poi.ss.usermodel.DataConsolidateFunction;
>+import org.apache.poi.ss.usermodel.Row;
>+import org.apache.poi.ss.util.AreaReference;
>+import org.apache.poi.ss.util.CellReference;
>+import org.apache.poi.xssf.usermodel.XSSFPivotTable;
>+import org.apache.poi.xssf.usermodel.XSSFSheet;
>+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
>+
>+public class CreatePivotTable {
>+
>+    public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
>+        XSSFWorkbook wb = new XSSFWorkbook();
>+        XSSFSheet sheet = (XSSFSheet) wb.createSheet();
>+
>+        //Create some data to build the pivot table on
>+        setCellData(sheet);
>+
>+        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
>+        //Configure the pivot table
>+        //Use first column as row label
>+        pivotTable.addRowLabel(0);
>+        //Sum up the second column
>+        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
>+        //Set the third column as filter
>+        pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
>+        //Add filter on forth column
>+        pivotTable.addReportFilter(3);
>+
>+        FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
>+        wb.write(fileOut);
>+        fileOut.close();
>+    }
>+
>+    public static void setCellData(XSSFSheet sheet){
>+        Row row1 = sheet.createRow(0);
>+        // Create a cell and put a value in it.
>+        Cell cell11 = row1.createCell(0);
>+        cell11.setCellValue("Names");
>+        Cell cell12 = row1.createCell(1);
>+        cell12.setCellValue("#");
>+        Cell cell13 = row1.createCell(2);
>+        cell13.setCellValue("%");
>+        Cell cell14 = row1.createCell(3);
>+        cell14.setCellValue("Human");
>+
>+        Row row2 = sheet.createRow(1);
>+        Cell cell21 = row2.createCell(0);
>+        cell21.setCellValue("Jane");
>+        Cell cell22 = row2.createCell(1);
>+        cell22.setCellValue(10);
>+        Cell cell23 = row2.createCell(2);
>+        cell23.setCellValue(100);
>+        Cell cell24 = row2.createCell(3);
>+        cell24.setCellValue("Yes");
>+
>+        Row row3 = sheet.createRow(2);
>+        Cell cell31 = row3.createCell(0);
>+        cell31.setCellValue("Tarzan");
>+        Cell cell32 = row3.createCell(1);
>+        cell32.setCellValue(5);
>+        Cell cell33 = row3.createCell(2);
>+        cell33.setCellValue(90);
>+        Cell cell34 = row3.createCell(3);
>+        cell34.setCellValue("Yes");
>+
>+        Row row4 = sheet.createRow(3);
>+        Cell cell41 = row4.createCell(0);
>+        cell41.setCellValue("Terk");
>+        Cell cell42 = row4.createCell(1);
>+        cell42.setCellValue(10);
>+        Cell cell43 = row4.createCell(2);
>+        cell43.setCellValue(90);
>+        Cell cell44 = row4.createCell(3);
>+        cell44.setCellValue("No");
>+    }
>+}
>\ No newline at end of file
>diff --git src/java/org/apache/poi/ss/usermodel/DataConsolidateFunction.java src/java/org/apache/poi/ss/usermodel/DataConsolidateFunction.java
>new file mode 100644
>index 0000000..f54fb4f
>--- /dev/null
>+++ src/java/org/apache/poi/ss/usermodel/DataConsolidateFunction.java
>@@ -0,0 +1,56 @@
>+/*
>+ *  ====================================================================
>+ *    Licensed to the Apache Software Foundation (ASF) under one or more
>+ *    contributor license agreements.  See the NOTICE file distributed with
>+ *    this work for additional information regarding copyright ownership.
>+ *    The ASF licenses this file to You under the Apache License, Version 2.0
>+ *    (the "License"); you may not use this file except in compliance with
>+ *    the License.  You may obtain a copy of the License at
>+ *
>+ *        http://www.apache.org/licenses/LICENSE-2.0
>+ *
>+ *    Unless required by applicable law or agreed to in writing, software
>+ *    distributed under the License is distributed on an "AS IS" BASIS,
>+ *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+ *    See the License for the specific language governing permissions and
>+ *    limitations under the License.
>+ * ====================================================================
>+ */
>+
>+package org.apache.poi.ss.usermodel;
>+
>+import org.apache.poi.util.Beta;
>+
>+/**
>+ * Enum mapping the values of STDataConsolidateFunction
>+ */
>+@Beta
>+public enum DataConsolidateFunction {
>+    AVERAGE(1,"Average"),
>+    COUNT(2, "Count"),
>+    COUNT_NUMS(3, "Count"),
>+    MAX(4, "Max"),
>+    MIN(5, "Min"),
>+    PRODUCT(6, "Product"),
>+    STD_DEV(7, "StdDev"),
>+    STD_DEVP(8, "StdDevp"),
>+    SUM(9, "Sum"),
>+    VAR(10, "Var"),
>+    VARP(11, "Varp");
>+
>+    private int value;
>+    private String name;
>+
>+    DataConsolidateFunction(int value, String name) {
>+        this.value = value;
>+        this.name = name;
>+    }
>+
>+    public String getName() {
>+        return this.name;
>+    }
>+
>+    public int getValue() {
>+        return this.value;
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCache.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCache.java
>new file mode 100644
>index 0000000..e5896e7
>--- /dev/null
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCache.java
>@@ -0,0 +1,78 @@
>+/* ====================================================================
>+   Licensed to the Apache Software Foundation (ASF) under one or more
>+   contributor license agreements.  See the NOTICE file distributed with
>+   this work for additional information regarding copyright ownership.
>+   The ASF licenses this file to You under the Apache License, Version 2.0
>+   (the "License"); you may not use this file except in compliance with
>+   the License.  You may obtain a copy of the License at
>+
>+       http://www.apache.org/licenses/LICENSE-2.0
>+
>+   Unless required by applicable law or agreed to in writing, software
>+   distributed under the License is distributed on an "AS IS" BASIS,
>+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+   See the License for the specific language governing permissions and
>+   limitations under the License.
>+==================================================================== */
>+package org.apache.poi.xssf.usermodel;
>+
>+import java.io.IOException;
>+import java.io.InputStream;
>+
>+import org.apache.poi.POIXMLDocumentPart;
>+import static org.apache.poi.POIXMLDocumentPart.DEFAULT_XML_OPTIONS;
>+import org.apache.poi.openxml4j.opc.PackagePart;
>+import org.apache.poi.openxml4j.opc.PackageRelationship;
>+import org.apache.poi.util.Beta;
>+
>+import org.apache.xmlbeans.XmlException;
>+import org.apache.xmlbeans.XmlOptions;
>+
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache;
>+
>+public class XSSFPivotCache extends POIXMLDocumentPart {
>+
>+    private CTPivotCache ctPivotCache;
>+
>+    @Beta
>+    public XSSFPivotCache(){
>+        super();
>+        ctPivotCache = CTPivotCache.Factory.newInstance();
>+    }
>+
>+    @Beta
>+    public XSSFPivotCache(CTPivotCache ctPivotCache) {
>+        super();
>+        this.ctPivotCache = ctPivotCache;
>+    }
>+
>+     /**
>+     * Creates n XSSFPivotCache representing the given package part and relationship.
>+     * Should only be called when reading in an existing file.
>+     *
>+     * @param part - The package part that holds xml data representing this pivot cache definition.
>+     * @param rel - the relationship of the given package part in the underlying OPC package
>+     */
>+    @Beta
>+    protected XSSFPivotCache(PackagePart part, PackageRelationship rel) throws IOException {
>+        super(part, rel);
>+        readFrom(part.getInputStream());
>+    }
>+
>+    @Beta
>+    protected void readFrom(InputStream is) throws IOException {
>+	try {
>+        XmlOptions options  = new XmlOptions(DEFAULT_XML_OPTIONS);
>+        //Removing root element
>+        options.setLoadReplaceDocumentElement(null);
>+            ctPivotCache = CTPivotCache.Factory.parse(is, options);
>+        } catch (XmlException e) {
>+            throw new IOException(e.getLocalizedMessage());
>+        }
>+    }
>+
>+    @Beta
>+    public CTPivotCache getCTPivotCache() {
>+        return ctPivotCache;
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java
>new file mode 100644
>index 0000000..2c9eedf
>--- /dev/null
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java
>@@ -0,0 +1,142 @@
>+/* ====================================================================
>+   Licensed to the Apache Software Foundation (ASF) under one or more
>+   contributor license agreements.  See the NOTICE file distributed with
>+   this work for additional information regarding copyright ownership.
>+   The ASF licenses this file to You under the Apache License, Version 2.0
>+   (the "License"); you may not use this file except in compliance with
>+   the License.  You may obtain a copy of the License at
>+
>+       http://www.apache.org/licenses/LICENSE-2.0
>+
>+   Unless required by applicable law or agreed to in writing, software
>+   distributed under the License is distributed on an "AS IS" BASIS,
>+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+   See the License for the specific language governing permissions and
>+   limitations under the License.
>+==================================================================== */
>+package org.apache.poi.xssf.usermodel;
>+
>+import java.io.IOException;
>+import java.io.InputStream;
>+import java.io.OutputStream;
>+
>+import java.util.Date;
>+
>+import javax.xml.namespace.QName;
>+
>+import org.apache.poi.POIXMLDocumentPart;
>+import static org.apache.poi.POIXMLDocumentPart.DEFAULT_XML_OPTIONS;
>+import org.apache.poi.openxml4j.opc.PackagePart;
>+import org.apache.poi.openxml4j.opc.PackageRelationship;
>+import org.apache.poi.ss.usermodel.Cell;
>+import org.apache.poi.ss.usermodel.Row;
>+import org.apache.poi.ss.usermodel.Sheet;
>+import org.apache.poi.ss.util.AreaReference;
>+import org.apache.poi.ss.util.CellReference;
>+import org.apache.poi.util.Beta;
>+
>+import org.apache.xmlbeans.XmlException;
>+import org.apache.xmlbeans.XmlOptions;
>+
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheField;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCacheDefinition;
>+
>+public class XSSFPivotCacheDefinition extends POIXMLDocumentPart{
>+
>+    private CTPivotCacheDefinition ctPivotCacheDefinition;
>+
>+    @Beta
>+    public XSSFPivotCacheDefinition(){
>+        super();
>+        ctPivotCacheDefinition = CTPivotCacheDefinition.Factory.newInstance();
>+        createDefaultValues();
>+    }
>+
>+     /**
>+     * Creates an XSSFPivotCacheDefintion representing the given package part and relationship.
>+     * Should only be called when reading in an existing file.
>+     *
>+     * @param part - The package part that holds xml data representing this pivot cache definition.
>+     * @param rel - the relationship of the given package part in the underlying OPC package
>+     */
>+    @Beta
>+    protected XSSFPivotCacheDefinition(PackagePart part, PackageRelationship rel) throws IOException {
>+        super(part, rel);
>+        readFrom(part.getInputStream());
>+    }
>+
>+    @Beta
>+    public void readFrom(InputStream is) throws IOException {
>+	try {
>+            XmlOptions options  = new XmlOptions(DEFAULT_XML_OPTIONS);
>+            //Removing root element
>+            options.setLoadReplaceDocumentElement(null);
>+            ctPivotCacheDefinition = CTPivotCacheDefinition.Factory.parse(is, options);
>+        } catch (XmlException e) {
>+            throw new IOException(e.getLocalizedMessage());
>+        }
>+    }
>+
>+    @Beta
>+    public CTPivotCacheDefinition getCTPivotCacheDefinition() {
>+        return ctPivotCacheDefinition;
>+    }
>+
>+    @Beta
>+    private void createDefaultValues() {
>+        ctPivotCacheDefinition.setCreatedVersion(XSSFPivotTable.CREATED_VERSION);
>+        ctPivotCacheDefinition.setMinRefreshableVersion(XSSFPivotTable.MIN_REFRESHABLE_VERSION);
>+        ctPivotCacheDefinition.setRefreshedVersion(XSSFPivotTable.UPDATED_VERSION);
>+        ctPivotCacheDefinition.setRefreshedBy("Apache POI");
>+        ctPivotCacheDefinition.setRefreshedDate(new Date().getTime());
>+        ctPivotCacheDefinition.setRefreshOnLoad(true);
>+    }
>+
>+    @Beta
>+    @Override
>+    protected void commit() throws IOException {
>+        PackagePart part = getPackagePart();
>+        OutputStream out = part.getOutputStream();
>+        XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
>+        //Sets the pivotCacheDefinition tag
>+        xmlOptions.setSaveSyntheticDocumentElement(new QName(CTPivotCacheDefinition.type.getName().
>+                getNamespaceURI(), "pivotCacheDefinition"));
>+        ctPivotCacheDefinition.save(out, xmlOptions);
>+        out.close();
>+    }
>+
>+    /**
>+     * Generates a cache field for each column in the reference area for the pivot table.
>+     * @param sheet The sheet where the data i collected from
>+     */
>+    @Beta
>+    protected void createCacheFields(Sheet sheet) {
>+        //Get values for start row, start and end column
>+        AreaReference ar = new AreaReference(ctPivotCacheDefinition.getCacheSource().getWorksheetSource().getRef());
>+        CellReference firstCell = ar.getFirstCell();
>+        CellReference lastCell = ar.getLastCell();
>+        int columnStart = firstCell.getCol();
>+        int columnEnd = lastCell.getCol();
>+        Row row = sheet.getRow(firstCell.getRow());
>+        CTCacheFields cFields;
>+        if(ctPivotCacheDefinition.getCacheFields() != null) {
>+            cFields = ctPivotCacheDefinition.getCacheFields();
>+        } else {
>+            cFields = ctPivotCacheDefinition.addNewCacheFields();
>+        }
>+        //For each column, create a cache field and give it en empty sharedItems
>+        for(int i=columnStart; i<=columnEnd; i++) {
>+            CTCacheField cf = cFields.addNewCacheField();
>+            if(i==columnEnd){
>+                cFields.setCount(cFields.getCacheFieldList().size());
>+            }
>+            //General number format
>+            cf.setNumFmtId(0);
>+            Cell cell = row.getCell(i);
>+            cell.setCellType(Cell.CELL_TYPE_STRING);
>+            cf.setName(row.getCell(i).getStringCellValue());
>+            cf.addNewSharedItems();
>+        }
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java
>new file mode 100644
>index 0000000..82726d5
>--- /dev/null
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java
>@@ -0,0 +1,87 @@
>+/* ====================================================================
>+   Licensed to the Apache Software Foundation (ASF) under one or more
>+   contributor license agreements.  See the NOTICE file distributed with
>+   this work for additional information regarding copyright ownership.
>+   The ASF licenses this file to You under the Apache License, Version 2.0
>+   (the "License"); you may not use this file except in compliance with
>+   the License.  You may obtain a copy of the License at
>+
>+       http://www.apache.org/licenses/LICENSE-2.0
>+
>+   Unless required by applicable law or agreed to in writing, software
>+   distributed under the License is distributed on an "AS IS" BASIS,
>+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+   See the License for the specific language governing permissions and
>+   limitations under the License.
>+==================================================================== */
>+package org.apache.poi.xssf.usermodel;
>+
>+import java.io.IOException;
>+import java.io.InputStream;
>+import java.io.OutputStream;
>+
>+import javax.xml.namespace.QName;
>+
>+import org.apache.poi.POIXMLDocumentPart;
>+import static org.apache.poi.POIXMLDocumentPart.DEFAULT_XML_OPTIONS;
>+import org.apache.poi.openxml4j.opc.PackagePart;
>+import org.apache.poi.openxml4j.opc.PackageRelationship;
>+import org.apache.poi.util.Beta;
>+
>+import org.apache.xmlbeans.XmlException;
>+import org.apache.xmlbeans.XmlOptions;
>+
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCacheRecords;
>+
>+public class XSSFPivotCacheRecords extends POIXMLDocumentPart {
>+
>+    private CTPivotCacheRecords ctPivotCacheRecords;
>+    @Beta
>+    public XSSFPivotCacheRecords() {
>+        super();
>+        ctPivotCacheRecords = CTPivotCacheRecords.Factory.newInstance();
>+    }
>+
>+    /**
>+     * Creates an XSSFPivotCacheRecords representing the given package part and relationship.
>+     * Should only be called when reading in an existing file.
>+     *
>+     * @param part - The package part that holds xml data representing this pivot cache records.
>+     * @param rel - the relationship of the given package part in the underlying OPC package
>+     */
>+    @Beta
>+    protected XSSFPivotCacheRecords(PackagePart part, PackageRelationship rel) throws IOException {
>+        super(part, rel);
>+        readFrom(part.getInputStream());
>+    }
>+
>+    @Beta
>+    protected void readFrom(InputStream is) throws IOException {
>+	try {
>+        XmlOptions options  = new XmlOptions(DEFAULT_XML_OPTIONS);
>+        //Removing root element
>+        options.setLoadReplaceDocumentElement(null);
>+            ctPivotCacheRecords = CTPivotCacheRecords.Factory.parse(is, options);
>+        } catch (XmlException e) {
>+            throw new IOException(e.getLocalizedMessage());
>+        }
>+    }
>+
>+    @Beta
>+    public CTPivotCacheRecords getCtPivotCacheRecords() {
>+        return ctPivotCacheRecords;
>+    }
>+
>+    @Beta
>+    @Override
>+    protected void commit() throws IOException {
>+        PackagePart part = getPackagePart();
>+        OutputStream out = part.getOutputStream();
>+        XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
>+        //Sets the pivotCacheDefinition tag
>+        xmlOptions.setSaveSyntheticDocumentElement(new QName(CTPivotCacheRecords.type.getName().
>+                getNamespaceURI(), "pivotCacheRecords"));
>+        ctPivotCacheRecords.save(out, xmlOptions);
>+        out.close();
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java
>new file mode 100644
>index 0000000..3e55914
>--- /dev/null
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java
>@@ -0,0 +1,448 @@
>+/* ====================================================================
>+   Licensed to the Apache Software Foundation (ASF) under one or more
>+   contributor license agreements.  See the NOTICE file distributed with
>+   this work for additional information regarding copyright ownership.
>+   The ASF licenses this file to You under the Apache License, Version 2.0
>+   (the "License"); you may not use this file except in compliance with
>+   the License.  You may obtain a copy of the License at
>+
>+       http://www.apache.org/licenses/LICENSE-2.0
>+
>+   Unless required by applicable law or agreed to in writing, software
>+   distributed under the License is distributed on an "AS IS" BASIS,
>+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+   See the License for the specific language governing permissions and
>+   limitations under the License.
>+==================================================================== */
>+package org.apache.poi.xssf.usermodel;
>+
>+import java.io.IOException;
>+import java.io.InputStream;
>+import java.io.OutputStream;
>+import java.util.List;
>+
>+import javax.xml.namespace.QName;
>+
>+import static org.apache.poi.POIXMLDocumentPart.DEFAULT_XML_OPTIONS;
>+import org.apache.poi.POIXMLDocumentPart;
>+import org.apache.poi.openxml4j.opc.PackagePart;
>+import org.apache.poi.openxml4j.opc.PackageRelationship;
>+import org.apache.poi.ss.usermodel.Cell;
>+import org.apache.poi.ss.usermodel.DataConsolidateFunction;
>+import org.apache.poi.ss.usermodel.Sheet;
>+import org.apache.poi.ss.util.AreaReference;
>+import org.apache.poi.ss.util.CellReference;
>+import org.apache.poi.util.Beta;
>+
>+import org.apache.xmlbeans.XmlException;
>+import org.apache.xmlbeans.XmlOptions;
>+
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCacheSource;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTColFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataField;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTItems;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLocation;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageField;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCacheDefinition;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotTableDefinition;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotTableStyle;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRowFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheetSource;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STAxis;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataConsolidateFunction;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STItemType;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STSourceType;
>+
>+public class XSSFPivotTable extends POIXMLDocumentPart {
>+
>+    protected final static short CREATED_VERSION = 3;
>+    protected final static short MIN_REFRESHABLE_VERSION = 3;
>+    protected final static short UPDATED_VERSION = 3;
>+
>+    private CTPivotTableDefinition pivotTableDefinition;
>+    private XSSFPivotCacheDefinition pivotCacheDefinition;
>+    private XSSFPivotCache pivotCache;
>+    private XSSFPivotCacheRecords pivotCacheRecords;
>+    private Sheet parentSheet;
>+    private Sheet dataSheet;
>+
>+    @Beta
>+    protected XSSFPivotTable() {
>+        super();
>+        pivotTableDefinition = CTPivotTableDefinition.Factory.newInstance();
>+        pivotCache = new XSSFPivotCache();
>+        pivotCacheDefinition = new XSSFPivotCacheDefinition();
>+        pivotCacheRecords = new XSSFPivotCacheRecords();
>+    }
>+
>+     /**
>+     * Creates an XSSFPivotTable representing the given package part and relationship.
>+     * Should only be called when reading in an existing file.
>+     *
>+     * @param part - The package part that holds xml data representing this pivot table.
>+     * @param rel - the relationship of the given package part in the underlying OPC package
>+     */
>+    @Beta
>+    protected XSSFPivotTable(PackagePart part, PackageRelationship rel) throws IOException {
>+        super(part, rel);
>+        readFrom(part.getInputStream());
>+    }
>+
>+    @Beta
>+    public void readFrom(InputStream is) throws IOException {
>+	try {
>+            XmlOptions options  = new XmlOptions(DEFAULT_XML_OPTIONS);
>+            //Removing root element
>+            options.setLoadReplaceDocumentElement(null);
>+            pivotTableDefinition = CTPivotTableDefinition.Factory.parse(is, options);
>+        } catch (XmlException e) {
>+            throw new IOException(e.getLocalizedMessage());
>+        }
>+    }
>+
>+    @Beta
>+    public void setPivotCache(XSSFPivotCache pivotCache) {
>+        this.pivotCache = pivotCache;
>+    }
>+
>+    @Beta
>+    public XSSFPivotCache getPivotCache() {
>+        return pivotCache;
>+    }
>+
>+    @Beta
>+    public Sheet getParentSheet() {
>+        return parentSheet;
>+    }
>+
>+    @Beta
>+    public void setParentSheet(XSSFSheet parentSheet) {
>+        this.parentSheet = parentSheet;
>+    }
>+
>+    @Beta
>+    public CTPivotTableDefinition getCTPivotTableDefinition() {
>+        return pivotTableDefinition;
>+    }
>+
>+    @Beta
>+    public void setCTPivotTableDefinition(CTPivotTableDefinition pivotTableDefinition) {
>+        this.pivotTableDefinition = pivotTableDefinition;
>+    }
>+
>+    @Beta
>+    public XSSFPivotCacheDefinition getPivotCacheDefinition() {
>+        return pivotCacheDefinition;
>+    }
>+
>+    @Beta
>+    public void setPivotCacheDefinition(XSSFPivotCacheDefinition pivotCacheDefinition) {
>+        this.pivotCacheDefinition = pivotCacheDefinition;
>+    }
>+
>+    @Beta
>+    public XSSFPivotCacheRecords getPivotCacheRecords() {
>+        return pivotCacheRecords;
>+    }
>+
>+    @Beta
>+    public void setPivotCacheRecords(XSSFPivotCacheRecords pivotCacheRecords) {
>+        this.pivotCacheRecords = pivotCacheRecords;
>+    }
>+
>+    @Beta
>+    public Sheet getDataSheet() {
>+        return dataSheet;
>+    }
>+
>+    @Beta
>+    private void setDataSheet(Sheet dataSheet) {
>+        this.dataSheet = dataSheet;
>+    }
>+
>+    @Beta
>+    @Override
>+    protected void commit() throws IOException {
>+        XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS);
>+        //Sets the pivotTableDefinition tag
>+        xmlOptions.setSaveSyntheticDocumentElement(new QName(CTPivotTableDefinition.type.getName().
>+                getNamespaceURI(), "pivotTableDefinition"));
>+        PackagePart part = getPackagePart();
>+        OutputStream out = part.getOutputStream();
>+        pivotTableDefinition.save(out, xmlOptions);
>+        out.close();
>+    }
>+
>+    /**
>+     * Set default values for the table definition.
>+     */
>+    @Beta
>+    protected void setDefaultPivotTableDefinition() {
>+        //Not more than one until more created
>+        pivotTableDefinition.setMultipleFieldFilters(false);
>+        //Indentation increment for compact rows
>+        pivotTableDefinition.setIndent(0);
>+        //The pivot version which created the pivot cache set to default value
>+        pivotTableDefinition.setCreatedVersion(CREATED_VERSION);
>+        //Minimun version required to update the pivot cache
>+        pivotTableDefinition.setMinRefreshableVersion(MIN_REFRESHABLE_VERSION);
>+        //Version of the application which "updated the spreadsheet last"
>+        pivotTableDefinition.setUpdatedVersion(UPDATED_VERSION);
>+        //Titles shown at the top of each page when printed
>+        pivotTableDefinition.setItemPrintTitles(true);
>+        //Set autoformat properties
>+        pivotTableDefinition.setUseAutoFormatting(true);
>+        pivotTableDefinition.setApplyNumberFormats(false);
>+        pivotTableDefinition.setApplyWidthHeightFormats(true);
>+        pivotTableDefinition.setApplyAlignmentFormats(false);
>+        pivotTableDefinition.setApplyPatternFormats(false);
>+        pivotTableDefinition.setApplyFontFormats(false);
>+        pivotTableDefinition.setApplyBorderFormats(false);
>+        pivotTableDefinition.setCacheId(pivotCache.getCTPivotCache().getCacheId());
>+        pivotTableDefinition.setName("PivotTable"+pivotTableDefinition.getCacheId());
>+        pivotTableDefinition.setDataCaption("Values");
>+
>+        //Set the default style for the pivot table
>+        CTPivotTableStyle style = pivotTableDefinition.addNewPivotTableStyleInfo();
>+        style.setName("PivotStyleLight16");
>+        style.setShowLastColumn(true);
>+        style.setShowColStripes(false);
>+        style.setShowRowStripes(false);
>+        style.setShowColHeaders(true);
>+        style.setShowRowHeaders(true);
>+    }
>+
>+    /**
>+     * Add a row label using data from the given column.
>+     * @param columnIndex, the index of the column to be used as row label.
>+     */
>+    @Beta
>+    public void addRowLabel(int columnIndex) {
>+        AreaReference pivotArea = new AreaReference(getPivotCacheDefinition().
>+                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
>+        int lastRowIndex = pivotArea.getLastCell().getRow() - pivotArea.getFirstCell().getRow();
>+        int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
>+
>+        if(columnIndex > lastColIndex) {
>+            throw new IndexOutOfBoundsException();
>+        }
>+        CTPivotFields pivotFields = pivotTableDefinition.getPivotFields();
>+
>+        List<CTPivotField> pivotFieldList = pivotTableDefinition.getPivotFields().getPivotFieldList();
>+        CTPivotField pivotField = CTPivotField.Factory.newInstance();
>+        CTItems items = pivotField.addNewItems();
>+
>+        pivotField.setAxis(STAxis.AXIS_ROW);
>+        pivotField.setShowAll(false);
>+        for(int i = 0; i <= lastRowIndex; i++) {
>+            items.addNewItem().setT(STItemType.DEFAULT);
>+        }
>+        items.setCount(items.getItemList().size());
>+        pivotFieldList.set(columnIndex, pivotField);
>+
>+        pivotFields.setPivotFieldArray(pivotFieldList.toArray(new CTPivotField[pivotFieldList.size()]));
>+
>+        CTRowFields rowFields;
>+        if(pivotTableDefinition.getRowFields() != null) {
>+            rowFields = pivotTableDefinition.getRowFields();
>+        } else {
>+            rowFields = pivotTableDefinition.addNewRowFields();
>+        }
>+
>+        rowFields.addNewField().setX(columnIndex);
>+        rowFields.setCount(rowFields.getFieldList().size());
>+    }
>+
>+    /**
>+     * Add a column label using data from the given column and specified function
>+     * @param columnIndex, the index of the column to be used as column label.
>+     * @param function, the function to be used on the data
>+     * The following functions exists:
>+     * Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp
>+     */
>+    @Beta
>+    public void addColumnLabel(DataConsolidateFunction function, int columnIndex) {
>+        AreaReference pivotArea = new AreaReference(getPivotCacheDefinition().
>+                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
>+        int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
>+
>+        if(columnIndex > lastColIndex && columnIndex < 0) {
>+            throw new IndexOutOfBoundsException();
>+        }
>+
>+        addDataColumn(columnIndex, true);
>+        addDataField(function, columnIndex);
>+
>+        //Only add colfield if there is already one.
>+        if (pivotTableDefinition.getDataFields().getCount() > 1) {
>+            CTColFields colFields;
>+            if(pivotTableDefinition.getColFields() != null) {
>+                colFields = pivotTableDefinition.getColFields();
>+            } else {
>+                colFields = pivotTableDefinition.addNewColFields();
>+            }
>+            colFields.addNewField().setX(-2);
>+            colFields.setCount(colFields.getFieldList().size());
>+        }
>+    }
>+
>+    /**
>+     * Add data field with data from the given column and specified function.
>+     * @param function, the function to be used on the data
>+     * @param index, the index of the column to be used as column label.
>+     * The following functions exists:
>+     * Sum, Count, Average, Max, Min, Product, Count numbers, StdDev, StdDevp, Var, Varp
>+     */
>+    @Beta
>+    private void addDataField(DataConsolidateFunction function, int columnIndex) {
>+        AreaReference pivotArea = new AreaReference(getPivotCacheDefinition().
>+                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
>+        int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
>+
>+        if(columnIndex > lastColIndex && columnIndex < 0) {
>+            throw new IndexOutOfBoundsException();
>+        }
>+        CTDataFields dataFields;
>+        if(pivotTableDefinition.getDataFields() != null) {
>+            dataFields = pivotTableDefinition.getDataFields();
>+        } else {
>+            dataFields = pivotTableDefinition.addNewDataFields();
>+        }
>+        CTDataField dataField = dataFields.addNewDataField();
>+        dataField.setSubtotal(STDataConsolidateFunction.Enum.forInt(function.getValue()));
>+        Cell cell = getDataSheet().getRow(pivotArea.getFirstCell().getRow()).getCell(columnIndex);
>+        cell.setCellType(Cell.CELL_TYPE_STRING);
>+        dataField.setName(function.getName());
>+        dataField.setFld(columnIndex);
>+        dataFields.setCount(dataFields.getDataFieldList().size());
>+    }
>+
>+    /**
>+     * Add column containing data from the referenced area.
>+     * @param columnIndex, the index of the column containing the data
>+     * @param isDataField, true if the data should be displayed in the pivot table.
>+     */
>+    @Beta
>+    public void addDataColumn(int columnIndex, boolean isDataField) {
>+        AreaReference pivotArea = new AreaReference(getPivotCacheDefinition().
>+                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
>+        int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
>+        if(columnIndex > lastColIndex && columnIndex < 0) {
>+            throw new IndexOutOfBoundsException();
>+        }
>+        CTPivotFields pivotFields = pivotTableDefinition.getPivotFields();
>+        List<CTPivotField> pivotFieldList = pivotFields.getPivotFieldList();
>+        CTPivotField pivotField = CTPivotField.Factory.newInstance();
>+
>+        pivotField.setDataField(isDataField);
>+        pivotField.setShowAll(false);
>+        pivotFieldList.set(columnIndex, pivotField);
>+        pivotFields.setPivotFieldArray(pivotFieldList.toArray(new CTPivotField[pivotFieldList.size()]));
>+    }
>+
>+    /**
>+     * Add filter for the column with the corresponding index and cell value
>+     * @param columnIndex, index of column to filter on
>+     */
>+    @Beta
>+    public void addReportFilter(int columnIndex) {
>+        AreaReference pivotArea = new AreaReference(getPivotCacheDefinition().
>+                getCTPivotCacheDefinition().getCacheSource().getWorksheetSource().getRef());
>+        int lastColIndex = pivotArea.getLastCell().getCol() - pivotArea.getFirstCell().getCol();
>+        int lastRowIndex = pivotArea.getLastCell().getRow() - pivotArea.getFirstCell().getRow();
>+
>+        if(columnIndex > lastColIndex && columnIndex < 0) {
>+            throw new IndexOutOfBoundsException();
>+        }
>+        CTPivotFields pivotFields = pivotTableDefinition.getPivotFields();
>+
>+        List<CTPivotField> pivotFieldList = pivotTableDefinition.getPivotFields().getPivotFieldList();
>+        CTPivotField pivotField = CTPivotField.Factory.newInstance();
>+        CTItems items = pivotField.addNewItems();
>+
>+        pivotField.setAxis(STAxis.AXIS_PAGE);
>+        pivotField.setShowAll(false);
>+        for(int i = 0; i <= lastRowIndex; i++) {
>+            items.addNewItem().setT(STItemType.DEFAULT);
>+        }
>+        items.setCount(items.getItemList().size());
>+        pivotFieldList.set(columnIndex, pivotField);
>+
>+        CTPageFields pageFields;
>+        if (pivotTableDefinition.getPageFields()!= null) {
>+            pageFields = pivotTableDefinition.getPageFields();
>+            //Another filter has already been created
>+            pivotTableDefinition.setMultipleFieldFilters(true);
>+        } else {
>+            pageFields = pivotTableDefinition.addNewPageFields();
>+        }
>+        CTPageField pageField = pageFields.addNewPageField();
>+        pageField.setHier(-1);
>+        pageField.setFld(columnIndex);
>+
>+        pageFields.setCount(pageFields.getPageFieldList().size());
>+        pivotTableDefinition.getLocation().setColPageCount(pageFields.getCount());
>+    }
>+
>+    /**
>+     * Creates cacheSource and workSheetSource for pivot table and sets the source reference as well assets the location of the pivot table
>+     * @param source Source for data for pivot table
>+     * @param position Position for pivot table in sheet
>+     * @param sourceSheet Sheet where the source will be collected from
>+     */
>+    @Beta
>+    protected void createSourceReferences(AreaReference source, CellReference position, Sheet sourceSheet){
>+        //Get cell one to the right and one down from position, add both to AreaReference and set pivot table location.
>+        AreaReference destination = new AreaReference(position, new CellReference(position.getRow()+1, position.getCol()+1));
>+
>+        CTLocation location;
>+        if(pivotTableDefinition.getLocation() == null) {
>+            location = pivotTableDefinition.addNewLocation();
>+            location.setFirstDataCol(1);
>+            location.setFirstDataRow(1);
>+            location.setFirstHeaderRow(1);
>+        } else {
>+            location = pivotTableDefinition.getLocation();
>+        }
>+        location.setRef(destination.formatAsString());
>+        pivotTableDefinition.setLocation(location);
>+
>+        //Set source for the pivot table
>+        CTPivotCacheDefinition cacheDef = getPivotCacheDefinition().getCTPivotCacheDefinition();
>+        CTCacheSource cacheSource = cacheDef.addNewCacheSource();
>+        cacheSource.setType(STSourceType.WORKSHEET);
>+        CTWorksheetSource worksheetSource = cacheSource.addNewWorksheetSource();
>+        worksheetSource.setSheet(sourceSheet.getSheetName());
>+        setDataSheet(sourceSheet);
>+
>+        String[] firstCell = source.getFirstCell().getCellRefParts();
>+        String[] lastCell = source.getLastCell().getCellRefParts();
>+        worksheetSource.setRef(firstCell[2]+firstCell[1]+':'+lastCell[2]+lastCell[1]);
>+    }
>+
>+    @Beta
>+    protected void createDefaultDataColumns() {
>+        CTPivotFields pivotFields;
>+        if (pivotTableDefinition.getPivotFields() != null) {
>+            pivotFields = pivotTableDefinition.getPivotFields();
>+        } else {
>+            pivotFields = pivotTableDefinition.addNewPivotFields();
>+        }
>+        String source = pivotCacheDefinition.getCTPivotCacheDefinition().
>+                getCacheSource().getWorksheetSource().getRef();
>+        AreaReference sourceArea = new AreaReference(source);
>+        int firstColumn = sourceArea.getFirstCell().getCol();
>+        int lastColumn = sourceArea.getLastCell().getCol();
>+        CTPivotField pivotField;
>+        for(int i = 0; i<=lastColumn-firstColumn; i++) {
>+            pivotField = pivotFields.addNewPivotField();
>+            pivotField.setDataField(false);
>+            pivotField.setShowAll(false);
>+        }
>+        pivotFields.setCount(pivotFields.getPivotFieldList().size());
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
>index 1f5c512..ed50ec3 100644
>--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
>@@ -40,6 +40,7 @@ import org.apache.poi.xssf.model.SharedStringsTable;
> import org.apache.poi.xssf.model.SingleXmlCells;
> import org.apache.poi.xssf.model.StylesTable;
> import org.apache.poi.xssf.model.ThemesTable;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCacheDefinition;
> 
> /**
>  *
>@@ -284,6 +285,24 @@ public final class XSSFRelation extends POIXMLRelation {
>           "/xl/printerSettings/printerSettings#.bin",
>           null
>    );
>+    public static final XSSFRelation PIVOT_TABLE = new XSSFRelation(
>+            "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml",
>+            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotTable",
>+            "/xl/pivotTables/pivotTable#.xml",
>+            XSSFPivotTable.class
>+    );
>+    public static final XSSFRelation PIVOT_CACHE_DEFINITION = new XSSFRelation(
>+            "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml",
>+            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheDefinition",
>+            "/xl/pivotCache/pivotCacheDefinition#.xml",
>+            XSSFPivotCacheDefinition.class
>+    );
>+    public static final XSSFRelation PIVOT_CACHE_RECORDS = new XSSFRelation(
>+            "application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheRecords+xml",
>+            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/pivotCacheRecords",
>+            "/xl/pivotCache/pivotCacheRecords#.xml",
>+            XSSFPivotCacheRecords.class
>+    );
> 
> 
> 	private XSSFRelation(String type, String rel, String defaultName, Class<? extends POIXMLDocumentPart> cls) {
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
>index 439c269..0c3d14d 100644
>--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
>@@ -39,15 +39,18 @@ import org.apache.poi.openxml4j.exceptions.PartAlreadyExistsException;
> import org.apache.poi.openxml4j.opc.PackagePart;
> import org.apache.poi.openxml4j.opc.PackageRelationship;
> import org.apache.poi.openxml4j.opc.PackageRelationshipCollection;
>+import org.apache.poi.openxml4j.opc.TargetMode;
> import org.apache.poi.ss.SpreadsheetVersion;
> import org.apache.poi.ss.formula.FormulaShifter;
> import org.apache.poi.ss.formula.SheetNameFormatter;
> import org.apache.poi.ss.usermodel.*;
>+import org.apache.poi.ss.util.AreaReference;
> import org.apache.poi.ss.util.CellRangeAddress;
> import org.apache.poi.ss.util.CellRangeAddressList;
> import org.apache.poi.ss.util.CellReference;
> import org.apache.poi.ss.util.SSCellRange;
> import org.apache.poi.ss.util.SheetUtil;
>+import org.apache.poi.util.Beta;
> import org.apache.poi.util.HexDump;
> import org.apache.poi.util.Internal;
> import org.apache.poi.util.POILogFactory;
>@@ -55,8 +58,10 @@ import org.apache.poi.util.POILogger;
> import org.apache.poi.xssf.model.CommentsTable;
> import org.apache.poi.xssf.usermodel.helpers.ColumnHelper;
> import org.apache.poi.xssf.usermodel.helpers.XSSFRowShifter;
>+
> import org.apache.xmlbeans.XmlException;
> import org.apache.xmlbeans.XmlOptions;
>+
> import org.openxmlformats.schemas.officeDocument.x2006.relationships.STRelationshipId;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.*;
> 
>@@ -142,7 +147,6 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
> 
>         initRows(worksheet);
>         columnHelper = new ColumnHelper(worksheet);
>-
>         // Look for bits we're interested in
>         for(POIXMLDocumentPart p : getRelations()){
>             if(p instanceof CommentsTable) {
>@@ -152,6 +156,9 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
>             if(p instanceof XSSFTable) {
>                tables.put( p.getPackageRelationship().getId(), (XSSFTable)p );
>             }
>+            if(p instanceof XSSFPivotTable) {
>+                getWorkbook().getPivotTables().add((XSSFPivotTable) p);
>+            }
>         }
>         
>         // Process external hyperlinks for the sheet, if there are any
>@@ -3367,4 +3374,88 @@ public class XSSFSheet extends POIXMLDocumentPart implements Sheet {
>       return null;
>     }
> 
>-}
>+    /**
>+     * Creates an empty XSSFPivotTable and sets up all its relationships
>+     * including: pivotCacheDefinition, pivotCacheRecords
>+     * @return returns a pivotTable
>+     */
>+    @Beta
>+    private XSSFPivotTable createPivotTable() {
>+        XSSFWorkbook wb = getWorkbook();
>+        List<XSSFPivotTable> pivotTables = wb.getPivotTables();
>+        int tableId = getWorkbook().getPivotTables().size()+1;
>+        //Create relationship between pivotTable and the worksheet
>+        XSSFPivotTable pivotTable = (XSSFPivotTable) createRelationship(XSSFRelation.PIVOT_TABLE,
>+                XSSFFactory.getInstance(), tableId);
>+        pivotTable.setParentSheet(this);
>+        pivotTables.add(pivotTable);
>+        XSSFWorkbook workbook = getWorkbook();
>+
>+        //Create relationship between the pivot cache defintion and the workbook
>+        XSSFPivotCacheDefinition pivotCacheDefinition = (XSSFPivotCacheDefinition) workbook.
>+                createRelationship(XSSFRelation.PIVOT_CACHE_DEFINITION, XSSFFactory.getInstance(), tableId);
>+        String rId = workbook.getRelationId(pivotCacheDefinition);
>+        //Create relationship between pivotTable and pivotCacheDefinition without creating a new instance
>+        PackagePart pivotPackagePart = pivotTable.getPackagePart();
>+        pivotPackagePart.addRelationship(pivotCacheDefinition.getPackagePart().getPartName(),
>+                TargetMode.INTERNAL, XSSFRelation.PIVOT_CACHE_DEFINITION.getRelation());
>+
>+        pivotTable.setPivotCacheDefinition(pivotCacheDefinition);
>+
>+        //Create pivotCache and sets up it's relationship with the workbook
>+        pivotTable.setPivotCache(new XSSFPivotCache(workbook.addPivotCache(rId)));
>+
>+        //Create relationship between pivotcacherecord and pivotcachedefinition
>+        XSSFPivotCacheRecords pivotCacheRecords = (XSSFPivotCacheRecords) pivotCacheDefinition.
>+                createRelationship(XSSFRelation.PIVOT_CACHE_RECORDS, XSSFFactory.getInstance(), tableId);
>+
>+        //Set relationships id for pivotCacheDefinition to pivotCacheRecords
>+        pivotTable.getPivotCacheDefinition().getCTPivotCacheDefinition().setId(pivotCacheDefinition.getRelationId(pivotCacheRecords));
>+
>+        wb.setPivotTables(pivotTables);
>+
>+        return pivotTable;
>+    }
>+
>+    /**
>+     * Create a pivot table and set area of source, source sheet and a position for pivot table
>+     * @param source Area from where data will be collected
>+     * @param position A reference to the cell where the table will start
>+     * @param sourceSheet The sheet where source will be collected from
>+     * @return The pivot table
>+     */
>+    @Beta
>+    public XSSFPivotTable createPivotTable(AreaReference source, CellReference position, Sheet sourceSheet){
>+
>+        if(source.getFirstCell().getSheetName() != null && !source.getFirstCell().getSheetName().equals(sourceSheet.getSheetName())) {
>+            throw new IllegalArgumentException("The area is referenced in another sheet than the "
>+                    + "defined source sheet " + sourceSheet.getSheetName() + ".");
>+        }
>+        XSSFPivotTable pivotTable = createPivotTable();
>+        //Creates default settings for the pivot table
>+        pivotTable.setDefaultPivotTableDefinition();
>+
>+        //Set sources and references
>+        pivotTable.createSourceReferences(source, position, sourceSheet);
>+
>+        //Create cachefield/s and empty SharedItems
>+        pivotTable.getPivotCacheDefinition().createCacheFields(sourceSheet);
>+        pivotTable.createDefaultDataColumns();
>+
>+        return pivotTable;
>+    }
>+
>+    /**
>+     * Create a pivot table and set area of source and a position for pivot table
>+     * @param source Area from where data will be collected
>+     * @param position A reference to the cell where the table will start
>+     * @return The pivot table
>+     */
>+    @Beta
>+    public XSSFPivotTable createPivotTable(AreaReference source, CellReference position){
>+        if(source.getFirstCell().getSheetName() != null && !source.getFirstCell().getSheetName().equals(this.getSheetName())) {
>+            return createPivotTable(source, position, getWorkbook().getSheet(source.getFirstCell().getSheetName()));
>+        }
>+        return createPivotTable(source, position, this);
>+    }
>+}
>\ No newline at end of file
>diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
>index e6d33eb..1df666a 100644
>--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
>+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
>@@ -159,6 +159,14 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
>      */
>     private XSSFCreationHelper _creationHelper;
> 
>+    private List<CTPivotCache> pivotCaches;
>+
>+    /**
>+     * List of all pivot tables in workbook
>+     */
>+    private List<XSSFPivotTable> pivotTables;
>+
>+
>     /**
>      * Create a new SpreadsheetML workbook.
>      */
>@@ -181,7 +189,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
>      */
>     public XSSFWorkbook(OPCPackage pkg) throws IOException {
>         super(pkg);
>-
>+        pivotTables = new ArrayList<>();
>         //build a tree of POIXMLDocumentParts, this workbook being the root
>         load(XSSFFactory.getInstance());
>     }
>@@ -202,7 +210,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
>      */
>     public XSSFWorkbook(InputStream is) throws IOException {
>         super(PackageHelper.open(is));
>-        
>+         pivotTables = new ArrayList<>();
>         //build a tree of POIXMLDocumentParts, this workbook being the root
>         load(XSSFFactory.getInstance());
>     }
>@@ -323,6 +331,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
> 
>         namedRanges = new ArrayList<XSSFName>();
>         sheets = new ArrayList<XSSFSheet>();
>+        pivotTables = new ArrayList<XSSFPivotTable>();
>     }
> 
>     /**
>@@ -1145,7 +1154,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
>       
>       CellRangeAddress rows = null;
>       CellRangeAddress cols = null;
>-      
>+
>       if (startRow != -1) {
>         rows = new CellRangeAddress(startRow, endRow, -1, -1);
>       }
>@@ -1603,7 +1612,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
> 		createProtectionFieldIfNotPresent();
> 		workbook.getWorkbookProtection().setLockRevision(false);
> 	}
>-	
>+
> 	private boolean workbookProtectionPresent() {
> 		return workbook.getWorkbookProtection() != null;
> 	}
>@@ -1679,4 +1688,39 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
>         return calcPr != null && calcPr.getCalcId() != 0;
>     }
> 
>+
>+
>+    /**
>+     * Add pivotCache to the workbook
>+     */
>+    @Beta
>+    protected CTPivotCache addPivotCache(String rId) {
>+        CTWorkbook ctWorkbook = getCTWorkbook();
>+        CTPivotCaches caches;
>+        if (ctWorkbook.isSetPivotCaches()) {
>+            caches = ctWorkbook.getPivotCaches();
>+        } else {
>+            caches = ctWorkbook.addNewPivotCaches();
>+        }
>+        CTPivotCache cache = caches.addNewPivotCache();
>+
>+        int tableId = getPivotTables().size()+1;
>+        cache.setCacheId(tableId);
>+        cache.setId(rId);
>+        if(pivotCaches == null) {
>+            pivotCaches = new ArrayList<>();
>+        }
>+        pivotCaches.add(cache);
>+        return cache;
>+    }
>+
>+    @Beta
>+    public List<XSSFPivotTable> getPivotTables() {
>+        return pivotTables;
>+    }
>+
>+    @Beta
>+    public void setPivotTables(List<XSSFPivotTable> pivotTables) {
>+        this.pivotTables = pivotTables;
>+    }
> }
>diff --git src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
>index 5c5f69e..e5d824b 100644
>--- src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
>+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
>@@ -64,6 +64,8 @@ public final class AllXSSFUsermodelTests {
> 		result.addTestSuite(TestColumnHelper.class);
> 		result.addTestSuite(TestHeaderFooterHelper.class);
> 
>+                result.addTestSuite(TestXSSFPivotTable.class);
>+
> 		return result;
> 	}
> }
>diff --git src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java
>new file mode 100644
>index 0000000..3cf2a69
>--- /dev/null
>+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java
>@@ -0,0 +1,207 @@
>+/* ====================================================================
>+   Licensed to the Apache Software Foundation (ASF) under one or more
>+   contributor license agreements.  See the NOTICE file distributed with
>+   this work for additional information regarding copyright ownership.
>+   The ASF licenses this file to You under the Apache License, Version 2.0
>+   (the "License"); you may not use this file except in compliance with
>+   the License.  You may obtain a copy of the License at
>+
>+       http://www.apache.org/licenses/LICENSE-2.0
>+
>+   Unless required by applicable law or agreed to in writing, software
>+   distributed under the License is distributed on an "AS IS" BASIS,
>+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
>+   See the License for the specific language governing permissions and
>+   limitations under the License.
>+==================================================================== */
>+package org.apache.poi.xssf.usermodel;
>+
>+import junit.framework.TestCase;
>+import static junit.framework.TestCase.assertEquals;
>+import static junit.framework.TestCase.fail;
>+import org.apache.poi.ss.usermodel.Cell;
>+import org.apache.poi.ss.usermodel.DataConsolidateFunction;
>+import org.apache.poi.ss.usermodel.Row;
>+import org.apache.poi.ss.usermodel.Workbook;
>+import org.apache.poi.ss.util.AreaReference;
>+import org.apache.poi.ss.util.CellReference;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageField;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotFields;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotTableDefinition;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataConsolidateFunction;
>+
>+public class TestXSSFPivotTable extends TestCase {
>+
>+    XSSFPivotTable pivotTable;
>+    @Override
>+    public void setUp(){
>+        Workbook wb = new XSSFWorkbook();
>+        XSSFSheet sheet = (XSSFSheet) wb.createSheet();
>+
>+        Row row1 = sheet.createRow(0);
>+        // Create a cell and put a value in it.
>+        Cell cell = row1.createCell(0);
>+        cell.setCellValue("Names");
>+        Cell cell2 = row1.createCell(1);
>+        cell2.setCellValue("#");
>+        Cell cell7 = row1.createCell(2);
>+        cell7.setCellValue("Data");
>+
>+        Row row2 = sheet.createRow(1);
>+        Cell cell3 = row2.createCell(0);
>+        cell3.setCellValue("Jan");
>+        Cell cell4 = row2.createCell(1);
>+        cell4.setCellValue(10);
>+        Cell cell8 = row2.createCell(2);
>+        cell8.setCellValue("Apa");
>+
>+        Row row3 = sheet.createRow(2);
>+        Cell cell5 = row3.createCell(0);
>+        cell5.setCellValue("Ben");
>+        Cell cell6 = row3.createCell(1);
>+        cell6.setCellValue(9);
>+        Cell cell9 = row3.createCell(2);
>+        cell9.setCellValue("Bepa");
>+
>+        AreaReference source = new AreaReference("A1:B2");
>+        pivotTable = sheet.createPivotTable(source, new CellReference("H5"));
>+    }
>+
>+    /*
>+     * Verify that when creating a row label it's  created on the correct row
>+     * and the count is increased by one.
>+     */
>+    public void testAddRowLabelToPivotTable() {
>+        int columnIndex = 0;
>+
>+        pivotTable.addRowLabel(columnIndex);
>+        CTPivotTableDefinition defintion = pivotTable.getCTPivotTableDefinition();
>+
>+        assertEquals(defintion.getRowFields().getFieldArray(0).getX(), columnIndex);
>+        assertEquals(defintion.getRowFields().getCount(), 1);
>+    }
>+    /**
>+     * Verify that it's not possible to create a row label outside of the referenced area.
>+     */
>+    public void testAddRowLabelOutOfRangeThrowsException() {
>+        int columnIndex = 5;
>+
>+        try {
>+            pivotTable.addRowLabel(columnIndex);
>+        } catch(IndexOutOfBoundsException e) {
>+            return;
>+        }
>+        fail();
>+    }
>+
>+     /*
>+     * Verify that when creating one column label, no col fields are being created.
>+     */
>+    public void testAddOneColumnLabelToPivotTableDoesNotCreateColField() {
>+        int columnIndex = 0;
>+
>+        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnIndex);
>+        CTPivotTableDefinition defintion = pivotTable.getCTPivotTableDefinition();
>+
>+        assertEquals(defintion.getColFields(), null);
>+    }
>+
>+     /*
>+     * Verify that when creating two column labels, a col field is being created and X is set to -2.
>+     */
>+    public void testAddTwoColumnLabelsToPivotTable() {
>+        int columnOne = 0;
>+        int columnTwo = 1;
>+
>+        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnOne);
>+        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnTwo);
>+        CTPivotTableDefinition defintion = pivotTable.getCTPivotTableDefinition();
>+
>+        assertEquals(defintion.getColFields().getFieldArray(0).getX(), -2);
>+    }
>+
>+     /*
>+     * Verify that a data field is created when creating a data column
>+     */
>+    public void testColumnLabelCreatesDataField() {
>+        int columnIndex = 0;
>+
>+        pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnIndex);
>+
>+        CTPivotTableDefinition defintion = pivotTable.getCTPivotTableDefinition();
>+
>+        assertEquals(defintion.getDataFields().getDataFieldArray(0).getFld(), columnIndex);
>+        assertEquals(defintion.getDataFields().getDataFieldArray(0).getSubtotal(),
>+                STDataConsolidateFunction.Enum.forInt(DataConsolidateFunction.SUM.getValue()));
>+    }
>+
>+    /**
>+     * Verify that it's not possible to create a column label outside of the referenced area.
>+     */
>+    public void testAddColumnLabelOutOfRangeThrowsException() {
>+        int columnIndex = 5;
>+
>+        try {
>+            pivotTable.addColumnLabel(DataConsolidateFunction.SUM, columnIndex);
>+        } catch(IndexOutOfBoundsException e) {
>+            return;
>+        }
>+        fail();
>+    }
>+
>+     /**
>+     * Verify when creating a data column set to a data field, the data field with the corresponding
>+     * column index will be set to true.
>+     */
>+    public void testAddDataColumn() {
>+        int columnIndex = 0;
>+        boolean isDataField = true;
>+
>+        pivotTable.addDataColumn(columnIndex, isDataField);
>+        CTPivotFields pivotFields = pivotTable.getCTPivotTableDefinition().getPivotFields();
>+        assertEquals(pivotFields.getPivotFieldArray(columnIndex).getDataField(), isDataField);
>+    }
>+
>+    /**
>+     * Verify that it's not possible to create a data column outside of the referenced area.
>+     */
>+    public void testAddDataColumnOutOfRangeThrowsException() {
>+        int columnIndex = 5;
>+        boolean isDataField = true;
>+
>+        try {
>+            pivotTable.addDataColumn(columnIndex, isDataField);
>+        } catch(IndexOutOfBoundsException e) {
>+            return;
>+        }
>+        fail();
>+    }
>+
>+     /**
>+     * Verify that it's possible to create a new filter
>+     */
>+    public void testAddReportFilter() {
>+        int columnIndex = 0;
>+
>+        pivotTable.addReportFilter(columnIndex);
>+        CTPageFields fields = pivotTable.getCTPivotTableDefinition().getPageFields();
>+        CTPageField field = fields.getPageFieldArray(0);
>+        assertEquals(field.getFld(), columnIndex);
>+        assertEquals(field.getHier(), -1);
>+        assertEquals(fields.getCount(), 1);
>+    }
>+
>+     /**
>+     * Verify that it's not possible to create a new filter outside of the referenced area.
>+     */
>+    public void testAddReportFilterOutOfRangeThrowsException() {
>+        int columnIndex = 5;
>+        try {
>+            pivotTable.addReportFilter(columnIndex);
>+        } catch(IndexOutOfBoundsException e) {
>+            return;
>+        }
>+        fail();
>+    }
>+}
>diff --git src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
>index 9f1cb92..0c3804e 100644
>--- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
>+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
>@@ -18,6 +18,8 @@
> package org.apache.poi.xssf.usermodel;
> 
> import java.util.List;
>+import static junit.framework.TestCase.assertNotNull;
>+import static junit.framework.TestCase.assertTrue;
> 
> import org.apache.poi.hssf.HSSFTestDataSamples;
> import org.apache.poi.hssf.record.PasswordRecord;
>@@ -25,9 +27,12 @@ import org.apache.poi.ss.usermodel.BaseTestSheet;
> import org.apache.poi.ss.usermodel.Cell;
> import org.apache.poi.ss.usermodel.CreationHelper;
> import org.apache.poi.ss.usermodel.RichTextString;
>+import org.apache.poi.ss.usermodel.Row;
> import org.apache.poi.ss.usermodel.Sheet;
> import org.apache.poi.ss.usermodel.Workbook;
>+import org.apache.poi.ss.util.AreaReference;
> import org.apache.poi.ss.util.CellRangeAddress;
>+import org.apache.poi.ss.util.CellReference;
> import org.apache.poi.util.HexDump;
> import org.apache.poi.xssf.SXSSFITestDataProvider;
> import org.apache.poi.xssf.XSSFITestDataProvider;
>@@ -1209,4 +1214,100 @@ public final class TestXSSFSheet extends BaseTestSheet {
>         assertNotNull(sh);
>         assertEquals(ROW_COUNT-1, sh.getLastRowNum());
>     }
>+
>+    private XSSFWorkbook setupSheet(){
>+        //set up workbook
>+        XSSFWorkbook wb = new XSSFWorkbook();
>+        XSSFSheet sheet = wb.createSheet();
>+
>+        Row row1 = sheet.createRow((short) 0);
>+        Cell cell = row1.createCell((short) 0);
>+        cell.setCellValue("Names");
>+        Cell cell2 = row1.createCell((short) 1);
>+        cell2.setCellValue("#");
>+
>+        Row row2 = sheet.createRow((short) 1);
>+        Cell cell3 = row2.createCell((short) 0);
>+        cell3.setCellValue("Jane");
>+        Cell cell4 = row2.createCell((short) 1);
>+        cell4.setCellValue(3);
>+
>+        Row row3 = sheet.createRow((short) 2);
>+        Cell cell5 = row3.createCell((short) 0);
>+        cell5.setCellValue("John");
>+        Cell cell6 = row3.createCell((short) 1);
>+        cell6.setCellValue(3);
>+
>+        return wb;
>+    }
>+    public void testCreateTwoPivotTablesInOneSheet(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+
>+        assertNotNull(wb);
>+        assertNotNull(sheet);
>+        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));
>+        assertNotNull(pivotTable);
>+        assertTrue(wb.getPivotTables().size() > 0);
>+        XSSFPivotTable pivotTable2 = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("L5"), sheet);
>+        assertNotNull(pivotTable2);
>+        assertTrue(wb.getPivotTables().size() > 1);
>+    }
>+    public void testCreateTwoPivotTablesInTwoSheets(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+
>+        assertNotNull(wb);
>+        assertNotNull(sheet);
>+        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));
>+        assertNotNull(pivotTable);
>+        assertTrue(wb.getPivotTables().size() > 0);
>+        assertNotNull(wb);
>+        XSSFSheet sheet2 = wb.createSheet();
>+        XSSFPivotTable pivotTable2 = sheet2.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"), sheet);
>+        assertNotNull(pivotTable2);
>+        assertTrue(wb.getPivotTables().size() > 1);
>+    }
>+    public void testCreatePivotTable(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+
>+        assertNotNull(wb);
>+        assertNotNull(sheet);
>+        XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:B2"), new CellReference("H5"));
>+        assertNotNull(pivotTable);
>+        assertTrue(wb.getPivotTables().size() > 0);
>+    }
>+
>+    public void testCreatePivotTableInOtherSheetThanDataSheet(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+        XSSFSheet sheet2 = wb.createSheet();
>+
>+        XSSFPivotTable pivotTable = sheet2.createPivotTable
>+                (new AreaReference("A1:B2"), new CellReference("H5"), sheet);
>+    }
>+
>+    public void testCreatePivotTableInOtherSheetThanDataSheetUsingAreaReference(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+        XSSFSheet sheet2 = wb.createSheet();
>+
>+        XSSFPivotTable pivotTable = sheet2.createPivotTable
>+                (new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5"));
>+    }
>+
>+    public void testCreatePivotTableWithConflictingDataSheets(){
>+        XSSFWorkbook wb = setupSheet();
>+        XSSFSheet sheet = wb.getSheetAt(0);
>+        XSSFSheet sheet2 = wb.createSheet();
>+
>+        try {
>+            XSSFPivotTable pivotTable = sheet2.createPivotTable
>+                (new AreaReference(sheet.getSheetName()+"!A$1:B$2"), new CellReference("H5"), sheet2);
>+        } catch(IllegalArgumentException e) {
>+            return;
>+        }
>+        fail();
>+    }
> }
>diff --git src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
>index 3d80aa7..80d70cf 100644
>--- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
>+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
>@@ -23,16 +23,22 @@ import java.io.OutputStream;
> import java.util.List;
> import java.util.zip.CRC32;
> 
>+import static junit.framework.TestCase.assertTrue;
>+
> import org.apache.poi.POIXMLProperties;
> import org.apache.poi.hssf.HSSFTestDataSamples;
> import org.apache.poi.openxml4j.opc.*;
> import org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart;
> import org.apache.poi.ss.usermodel.*;
>+import org.apache.poi.ss.util.AreaReference;
>+import org.apache.poi.ss.util.CellReference;
> import org.apache.poi.util.TempFile;
> import org.apache.poi.xssf.XSSFITestDataProvider;
> import org.apache.poi.xssf.XSSFTestDataSamples;
> import org.apache.poi.xssf.model.StylesTable;
>+
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr;
>+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
> import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCalcMode;
>@@ -448,4 +454,75 @@ public final class TestXSSFWorkbook extends BaseTestWorkbook {
>         assertEquals(IndexedColors.RED.index,
>                 sh.getCTWorksheet().getSheetPr().getTabColor().getIndexed());
>     }
>+
>+    public void testAddPivotCache() {
>+        XSSFWorkbook wb = new XSSFWorkbook();
>+        CTWorkbook ctWb = wb.getCTWorkbook();
>+        CTPivotCache pivotCache = wb.addPivotCache("0");
>+        //Ensures that pivotCaches is initiated
>+        assertTrue(ctWb.isSetPivotCaches());
>+        assertSame(pivotCache, ctWb.getPivotCaches().getPivotCacheList().get(0));
>+        assertEquals("0", pivotCache.getId());
>+    }
>+
>+    public void setPivotData(XSSFWorkbook wb){
>+        XSSFSheet sheet = (XSSFSheet) wb.createSheet();
>+
>+        Row row1 = sheet.createRow(0);
>+        // Create a cell and put a value in it.
>+        Cell cell = row1.createCell(0);
>+        cell.setCellValue("Names");
>+        Cell cell2 = row1.createCell(1);
>+        cell2.setCellValue("#");
>+        Cell cell7 = row1.createCell(2);
>+        cell7.setCellValue("Data");
>+
>+        Row row2 = sheet.createRow(1);
>+        Cell cell3 = row2.createCell(0);
>+        cell3.setCellValue("Jan");
>+        Cell cell4 = row2.createCell(1);
>+        cell4.setCellValue(10);
>+        Cell cell8 = row2.createCell(2);
>+        cell8.setCellValue("Apa");
>+
>+        Row row3 = sheet.createRow(2);
>+        Cell cell5 = row3.createCell(0);
>+        cell5.setCellValue("Ben");
>+        Cell cell6 = row3.createCell(1);
>+        cell6.setCellValue(9);
>+        Cell cell9 = row3.createCell(2);
>+        cell9.setCellValue("Bepa");
>+
>+        AreaReference source = new AreaReference("A1:B2");
>+        sheet.createPivotTable(source, new CellReference("H5"));
>+    }
>+
>+    public void testLoadWorkbookWithPivotTable() throws Exception {
>+        String fileName = "ooxml-pivottable.xlsx";
>+
>+        XSSFWorkbook wb = new XSSFWorkbook();
>+        setPivotData(wb);
>+
>+        FileOutputStream fileOut = new FileOutputStream(fileName);
>+        wb.write(fileOut);
>+        fileOut.close();
>+
>+        XSSFWorkbook wb2 = (XSSFWorkbook) WorkbookFactory.create(new File(fileName));
>+        assertTrue(wb2.getPivotTables().size() == 1);
>+    }
>+
>+    public void testAddPivotTableToWorkbookWithLoadedPivotTable() throws Exception {
>+        String fileName = "ooxml-pivottable.xlsx";
>+
>+        XSSFWorkbook wb = new XSSFWorkbook();
>+        setPivotData(wb);
>+
>+        FileOutputStream fileOut = new FileOutputStream(fileName);
>+        wb.write(fileOut);
>+        fileOut.close();
>+
>+        XSSFWorkbook wb2 = (XSSFWorkbook) WorkbookFactory.create(new File(fileName));
>+        setPivotData(wb2);
>+        assertTrue(wb2.getPivotTables().size() == 2);
>+    }
> }
>

Actions: View

Attachments on bug 56020: 31216 | 31222 | 31223 | 31224 | 31281 | 31543