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

4 files changed, 394 insertions, 3 deletions

diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
index eb0985d7a9..3469205165 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java
@@ -57,7 +57,8 @@ import org.junit.runners.Suite;
     TestXSSFCellFill.class,
     TestXSSFSheetComments.class,
     TestColumnHelper.class,
-    TestHeaderFooterHelper.class    
+    TestHeaderFooterHelper.class,
+    TestXSSFPivotTable.class
 })
 public final class AllXSSFUsermodelTests {
 }
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java
new file mode 100644
index 0000000000..4f7c13d18c
--- /dev/null
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFPivotTable.java
@@ -0,0 +1,206 @@
+/* ====================================================================
+   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 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 {
+    private 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 a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
index b9c2a4466c..e260e9e732 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java
@@ -17,13 +17,13 @@
 
 package org.apache.poi.xssf.usermodel;
 
+import static junit.framework.TestCase.assertNotNull;
+import static junit.framework.TestCase.assertTrue;
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
-import static org.junit.Assert.assertNotNull;
 import static org.junit.Assert.assertNotSame;
 import static org.junit.Assert.assertNull;
 import static org.junit.Assert.assertSame;
-import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.fail;
 
 import java.util.List;
@@ -35,9 +35,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;
@@ -1311,4 +1314,108 @@ public final class TestXSSFSheet extends BaseTestSheet {
         XSSFTestDataSamples.openSampleWorkbook("51585.xlsx");
     }
 
+    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 sheet1 = wb.getSheetAt(0);
+        XSSFSheet sheet2 = wb.createSheet();
+
+        XSSFPivotTable pivotTable = sheet2.createPivotTable
+                (new AreaReference("A1:B2"), new CellReference("H5"), sheet1);
+
+        // TODO Test the pivot table was setup correctly
+        
+        assertEquals(1, wb.getPivotTables().size());
+        assertEquals(1, sheet1.getPivotTables().size());
+        assertEquals(1, sheet2.getPivotTables().size());
+    }
+
+    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();
+    }
 }
\ No newline at end of file
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
index 72d63e49fa..f50fb5dfa5 100644
--- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
+++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java
@@ -42,7 +42,9 @@ import org.apache.poi.openxml4j.opc.PackagePartName;
 import org.apache.poi.openxml4j.opc.PackagingURIHelper;
 import org.apache.poi.openxml4j.opc.internal.MemoryPackagePart;
 import org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart;
+
 import org.apache.poi.ss.usermodel.BaseTestWorkbook;
+import org.apache.poi.ss.usermodel.Cell;
 import org.apache.poi.ss.usermodel.CellStyle;
 import org.apache.poi.ss.usermodel.Font;
 import org.apache.poi.ss.usermodel.IndexedColors;
@@ -50,6 +52,9 @@ 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.usermodel.WorkbookFactory;
+import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellReference;
 import org.apache.poi.util.IOUtils;
 import org.apache.poi.util.TempFile;
 import org.apache.poi.xssf.XSSFITestDataProvider;
@@ -57,6 +62,7 @@ import org.apache.poi.xssf.XSSFTestDataSamples;
 import org.apache.poi.xssf.model.StylesTable;
 import org.junit.Test;
 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;
@@ -691,4 +697,75 @@ public final class TestXSSFWorkbook extends BaseTestWorkbook {
     private static int indexOf(CharSequence cs, CharSequence searchChar, int start) {
         return cs.toString().indexOf(searchChar.toString(), start);
     }
+
+    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);
+    }
 }