Patch for creating PivotTables

pivottable-diff.txt (text/plain), 151.14 KB, created by Sofia Larsson on 2014-01-17 10:09:13 UTC

(hide)

Description: Patch for creating PivotTables

Filename:

MIME Type:

Creator: Sofia Larsson

Created: 2014-01-17 10:09:13 UTC

Size: 151.14 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/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java >new file mode 100644 >index 0000000..c26ff06 >--- /dev/null >+++ src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java >@@ -0,0 +1,92 @@ >+/* ==================================================================== >+ 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.ss.usermodel.Workbook; >+import org.apache.poi.ss.util.AreaReference; >+import org.apache.poi.ss.util.CellReference; >+import org.apache.poi.xssf.usermodel.XSSFCell; >+import org.apache.poi.xssf.usermodel.XSSFRow; >+import org.apache.poi.xssf.usermodel.XSSFSheet; >+import org.apache.poi.xssf.usermodel.XSSFTable; >+import org.apache.poi.xssf.usermodel.XSSFWorkbook; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns; >+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo; >+ >+/** >+ * Demonstrates how to create a simple table using Apache POI. >+ */ >+public class CreateTable { >+ >+ public static void main(String[] args) throws FileNotFoundException, >+ IOException { >+ >+ Workbook wb = new XSSFWorkbook(); >+ XSSFSheet sheet = (XSSFSheet) wb.createSheet(); >+ >+ //Create >+ XSSFTable table = sheet.createTable(); >+ table.setDisplayName("Test"); >+ CTTable cttable = table.getCTTable(); >+ >+ //Style configurations >+ CTTableStyleInfo style = cttable.addNewTableStyleInfo(); >+ style.setName("TableStyleMedium2"); >+ style.setShowColumnStripes(false); >+ style.setShowRowStripes(true); >+ >+ //Set which area the table should be placed in >+ AreaReference reference = new AreaReference(new CellReference(0, 0), >+ new CellReference(2,2)); >+ cttable.setRef(reference.formatAsString()); >+ cttable.setId(1); >+ cttable.setName("Test"); >+ cttable.setTotalsRowCount(1); >+ >+ CTTableColumns columns = cttable.addNewTableColumns(); >+ columns.setCount(3); >+ CTTableColumn column; >+ XSSFRow row; >+ XSSFCell cell; >+ for(int i=0; i<3; i++) { >+ //Create column >+ column = columns.addNewTableColumn(); >+ column.setName("Column"); >+ column.setId(i+1); >+ //Create row >+ row = sheet.createRow(i); >+ for(int j=0; j<3; j++) { >+ //Create cell >+ cell = row.createCell(j); >+ if(i == 0) { >+ cell.setCellValue("Column"+j); >+ } else { >+ cell.setCellValue("0"); >+ } >+ } >+ } >+ >+ FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx"); >+ wb.write(fileOut); >+ fileOut.close(); >+ } >+} >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 1e8b641..352d84b 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) { >@@ -151,6 +155,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 >@@ -3411,4 +3418,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); >+ } > } >diff --git src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java >index 6450d92..93c58a1 100644 >--- src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java >+++ src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java >@@ -56,6 +56,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; >@@ -173,6 +174,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. > */ >@@ -195,7 +204,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()); > } >@@ -216,7 +225,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()); > } >@@ -337,6 +346,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X > > namedRanges = new ArrayList<XSSFName>(); > sheets = new ArrayList<XSSFSheet>(); >+ pivotTables = new ArrayList<XSSFPivotTable>(); > } > > /** >@@ -1159,7 +1169,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); > } >@@ -1618,7 +1628,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<X > createProtectionFieldIfNotPresent(); > workbook.getWorkbookProtection().setLockRevision(false); > } >- >+ > private boolean workbookProtectionPresent() { > return workbook.getWorkbookProtection() != null; > } >@@ -1694,4 +1704,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 1130815..ad1c068 100644 >--- src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java >+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/AllXSSFUsermodelTests.java >@@ -55,7 +55,8 @@ import org.junit.runners.Suite; > TestXSSFCellFill.class, > TestXSSFSheetComments.class, > TestColumnHelper.class, >- TestHeaderFooterHelper.class >+ TestHeaderFooterHelper.class, >+ TestXSSFPivotTable.class > }) > public final class AllXSSFUsermodelTests { > } >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 329283c..66964c8 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; >@@ -26,9 +28,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; >@@ -1255,4 +1260,103 @@ public final class TestXSSFSheet extends BaseTestSheet { > assertNotNull(name); > assertEquals("Sheet0!$B:$C", name.getRefersToFormula()); > } >+ >+ 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 4f09813..230e256 100644 >--- src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java >+++ src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java >@@ -25,6 +25,8 @@ 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.ContentTypes; >@@ -34,6 +36,7 @@ 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.CellStyle; > import org.apache.poi.ss.usermodel.Font; >@@ -42,12 +45,20 @@ 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.*; >+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; > 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; >@@ -659,4 +670,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); >+ } > } > View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]