Patch from Sofia Larsson and Martin Andersson from bug #56020 - XSSF support for creating Pivot tables

git-svn-id: https://svn.apache.org/repos/asf/poi/trunk@1614684 13f79535-47bb-0310-9956-ffa450edef68

7 files changed, 929 insertions, 4 deletions

diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCache.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCache.java
new file mode 100644
index 0000000000..e5896e73a1
--- /dev/null
+++ b/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 a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java
new file mode 100644
index 0000000000..e80dc7ee6d
--- /dev/null
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheDefinition.java
@@ -0,0 +1,140 @@
+/* ====================================================================
+   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 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.poi.util.Internal;
+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
+    @Internal
+    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 a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java
new file mode 100644
index 0000000000..93e04a022d
--- /dev/null
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotCacheRecords.java
@@ -0,0 +1,86 @@
+/* ====================================================================
+   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 org.apache.poi.openxml4j.opc.PackagePart;
+import org.apache.poi.openxml4j.opc.PackageRelationship;
+import org.apache.poi.util.Beta;
+import org.apache.poi.util.Internal;
+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
+    @Internal
+    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 a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFPivotTable.java
new file mode 100644
index 0000000000..94d73fbef0
--- /dev/null
+++ b/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 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.poi.util.Internal;
+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
+    @Internal
+    public CTPivotTableDefinition getCTPivotTableDefinition() {
+        return pivotTableDefinition;
+    }
+
+    @Beta
+    @Internal
+    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 a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
index 70e755574b..213f739fea 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java
@@ -318,6 +318,24 @@ public final class XSSFRelation extends POIXMLRelation {
         "http://schemas.openxmlformats.org/officeDocument/2006/relationships/printerSettings",
         "/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
     );
 
 
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
index b6b9ee6f2f..fb5c6ef601 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java
@@ -39,6 +39,7 @@ 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;
@@ -52,11 +53,13 @@ import org.apache.poi.ss.usermodel.Header;
 import org.apache.poi.ss.usermodel.IndexedColors;
 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.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;
@@ -193,7 +196,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) {
@@ -202,6 +204,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
@@ -3583,4 +3588,102 @@ 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);
+    }
+    
+    /**
+     * Returns all the pivot tables for this Sheet
+     */
+    @Beta
+    public List<XSSFPivotTable> getPivotTables() {
+        List<XSSFPivotTable> tables = new ArrayList<XSSFPivotTable>();
+        for (XSSFPivotTable table : getWorkbook().getPivotTables()) {
+            if (table.getParent() == this) {
+                tables.add(table);
+            }
+        }
+        return tables;
+    }
 }
diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
index dbfd66aca9..a502028e94 100644
--- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
+++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java
@@ -57,6 +57,7 @@ import org.apache.poi.ss.usermodel.Workbook;
 import org.apache.poi.ss.util.CellRangeAddress;
 import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.ss.util.WorkbookUtil;
+import org.apache.poi.util.Beta;
 import org.apache.poi.util.IOUtils;
 import org.apache.poi.util.Internal;
 import org.apache.poi.util.POILogFactory;
@@ -80,6 +81,8 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReference;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCaches;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets;
 import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
@@ -196,6 +199,13 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
     private XSSFCreationHelper _creationHelper;
 
     /**
+     * List of all pivot tables in workbook
+     */
+    private List<XSSFPivotTable> pivotTables;
+    private List<CTPivotCache> pivotCaches;
+
+
+    /**
      * Create a new SpreadsheetML workbook.
      */
     public XSSFWorkbook() {
@@ -306,11 +316,11 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
             }
             stylesSource.setTheme(theme);
 
-            if(sharedStringSource == null) {
+            if (sharedStringSource == null) {
                 // Create SST if it is missing
                 sharedStringSource = (SharedStringsTable)createRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.getInstance());
             }
-
+            
             // Load individual sheets. The order of sheets is defined by the order
             //  of CTSheet elements in the workbook
             sheets = new ArrayList<XSSFSheet>(shIdMap.size());
@@ -338,7 +348,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
                     externalLinks.add(el);
                 }
             }
-
+            
             // Process the named ranges
             reprocessNamedRanges();
         } catch (XmlException e) {
@@ -369,6 +379,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X
 
         namedRanges = new ArrayList<XSSFName>();
         sheets = new ArrayList<XSSFSheet>();
+        pivotTables = new ArrayList<XSSFPivotTable>();
     }
 
     /**
@@ -1830,4 +1841,45 @@ 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<CTPivotCache>();
+        }
+        pivotCaches.add(cache);
+        return cache;
+    }
+
+    @Beta
+    public List<XSSFPivotTable> getPivotTables() {
+        // Lazy create the list. It gets populated with existing ones on sheet setup
+        if (pivotTables == null) {
+            pivotTables = new ArrayList<XSSFPivotTable>();
+            pivotCaches = new ArrayList<CTPivotCache>();
+        }
+
+        return pivotTables;
+    }
+
+    @Beta
+    protected void setPivotTables(List<XSSFPivotTable> pivotTables) {
+        this.pivotTables = pivotTables;
+    }
 }