diff options
26 files changed, 3022 insertions, 2256 deletions
diff --git a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java index e60c342287..b1b815ec6b 100644 --- a/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java +++ b/src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateNewSpreadsheet.java @@ -29,7 +29,7 @@ public class CreateNewSpreadsheet { CreationHelper createHelper = wb.getCreationHelper(); XSSFSheet s1 = wb.createSheet("Sheet One"); - XSSFSheet s2 = wb.createSheet("Sheet One"); + XSSFSheet s2 = wb.createSheet("Sheet Two"); // Create a few cells s1.createRow(0); diff --git a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java index f4defbae65..2ed1c3c7b1 100644 --- a/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java +++ b/src/ooxml/interfaces-jdk15/org/apache/poi/ss/usermodel/Workbook.java @@ -376,9 +376,6 @@ public interface Workbook { Palette getCustomPalette(); - /** Test only. Do not use */ - void insertChartRecord(); - /** * Adds a picture to the workbook. * @@ -392,7 +389,7 @@ public interface Workbook { /** * Gets all pictures from the Workbook. * - * @return the list of pictures (a list of {@link HSSFPictureData} objects.) + * @return the list of pictures (a list of {@link PictureData} objects.) */ List getAllPictures(); diff --git a/src/ooxml/java/org/apache/poi/POIXMLDocument.java b/src/ooxml/java/org/apache/poi/POIXMLDocument.java index c268dd4c8c..74be9909ca 100644 --- a/src/ooxml/java/org/apache/poi/POIXMLDocument.java +++ b/src/ooxml/java/org/apache/poi/POIXMLDocument.java @@ -16,78 +16,61 @@ ==================================================================== */ package org.apache.poi; -import java.io.IOException; -import java.io.InputStream; -import java.io.PushbackInputStream; +import java.io.*; import java.util.LinkedList; import java.util.List; import org.apache.poi.poifs.common.POIFSConstants; import org.apache.poi.util.IOUtils; +import org.apache.poi.util.PackageHelper; import org.apache.xmlbeans.XmlException; import org.openxml4j.exceptions.InvalidFormatException; import org.openxml4j.exceptions.OpenXML4JException; +import org.openxml4j.opc.*; import org.openxml4j.opc.Package; -import org.openxml4j.opc.PackagePart; -import org.openxml4j.opc.PackagePartName; -import org.openxml4j.opc.PackageRelationship; -import org.openxml4j.opc.PackageRelationshipCollection; -import org.openxml4j.opc.PackageRelationshipTypes; -import org.openxml4j.opc.PackagingURIHelper; -public abstract class POIXMLDocument { +public class POIXMLDocument extends POIXMLDocumentPart{ public static final String CORE_PROPERTIES_REL_TYPE = "http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"; public static final String EXTENDED_PROPERTIES_REL_TYPE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"; public static final String CUSTOM_PROPERTIES_REL_TYPE = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties"; - + // OLE embeddings relation name public static final String OLE_OBJECT_REL_TYPE="http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject"; - + // Embedded OPC documents relation name public static final String PACK_OBJECT_REL_TYPE="http://schemas.openxmlformats.org/officeDocument/2006/relationships/package"; - + /** The OPC Package */ private Package pkg; /** The OPC core Package Part */ private PackagePart corePart; - + /** * The properties of the OPC package, opened as needed */ private POIXMLProperties properties; - - /** - * The embedded OLE2 files in the OPC package - */ - protected List<PackagePart> embedds = new LinkedList<PackagePart>(); - - protected POIXMLDocument() {} - + + /** + * The embedded OLE2 files in the OPC package + */ + protected List<PackagePart> embedds; + + protected POIXMLDocument() { + super(null, null); + embedds = new LinkedList<PackagePart>(); + } + protected POIXMLDocument(Package pkg) throws IOException { - try { - this.pkg = pkg; - - PackageRelationship coreDocRelationship = this.pkg.getRelationshipsByType( - PackageRelationshipTypes.CORE_DOCUMENT).getRelationship(0); - - // Get core part - this.corePart = this.pkg.getPart(coreDocRelationship); - - // Verify it's there - if(corePart == null) { - throw new IllegalArgumentException("No core part found for this document! Nothing with " + coreDocRelationship.getRelationshipType() + " present as a relation."); - } - } catch (OpenXML4JException e) { - throw new IOException(e.toString()); - } + this(); + initialize(pkg); } - + protected POIXMLDocument(String path) throws IOException { - this(openPackage(path)); + this(openPackage(path)); } - + /** * Wrapper to open a package, returning an IOException * in the event of a problem. @@ -100,25 +83,37 @@ public abstract class POIXMLDocument { throw new IOException(e.toString()); } } - public static Package openPackage(InputStream is) throws IOException { + + protected void initialize(Package pkg) throws IOException { try { - return Package.open(is); - } catch (InvalidFormatException e) { + this.pkg = pkg; + + PackageRelationship coreDocRelationship = this.pkg.getRelationshipsByType( + PackageRelationshipTypes.CORE_DOCUMENT).getRelationship(0); + + // Get core part + this.corePart = super.packagePart = this.pkg.getPart(coreDocRelationship); + + // Verify it's there + if(corePart == null) { + throw new IllegalArgumentException("No core part found for this document! Nothing with " + coreDocRelationship.getRelationshipType() + " present as a relation."); + } + } catch (OpenXML4JException e) { throw new IOException(e.toString()); } } - protected Package getPackage() { + public Package getPackage() { return this.pkg; } - + protected PackagePart getCorePart() { return this.corePart; } /** * Get the PackagePart that is the target of a relationship. - * + * * @param rel The relationship * @return The target part * @throws InvalidFormatException @@ -128,7 +123,7 @@ public abstract class POIXMLDocument { } /** * Get the PackagePart that is the target of a relationship. - * + * * @param rel The relationship * @param pkg The package to fetch from * @return The target part @@ -143,90 +138,90 @@ public abstract class POIXMLDocument { return part; } - /** - * Fetches the (single) PackagePart which is defined as - * the supplied relation content type of the base - * package/container, or null if none found. - * @param relationType The relation content type to search for - * @throws IllegalArgumentException If we find more than one part of that type - */ - protected PackagePart getSinglePartByRelationType(String relationType) throws IllegalArgumentException, OpenXML4JException { - PackageRelationshipCollection rels = - pkg.getRelationshipsByType(relationType); - if(rels.size() == 0) { - return null; - } - if(rels.size() > 1) { - throw new IllegalArgumentException("Found " + rels.size() + " relations for the type " + relationType + ", should only ever be one!"); - } - PackageRelationship rel = rels.getRelationship(0); - return getTargetPart(rel); - } - - /** - * Retrieves all the PackageParts which are defined as - * relationships of the base document with the - * specified content type. - */ - protected PackagePart[] getRelatedByType(String contentType) throws InvalidFormatException { - PackageRelationshipCollection partsC = - getCorePart().getRelationshipsByType(contentType); - - PackagePart[] parts = new PackagePart[partsC.size()]; - int count = 0; - for (PackageRelationship rel : partsC) { - parts[count] = getTargetPart(rel); - count++; - } - return parts; - } - - - + /** + * Fetches the (single) PackagePart which is defined as + * the supplied relation content type of the base + * package/container, or null if none found. + * @param relationType The relation content type to search for + * @throws IllegalArgumentException If we find more than one part of that type + */ + protected PackagePart getSinglePartByRelationType(String relationType) throws IllegalArgumentException, OpenXML4JException { + PackageRelationshipCollection rels = + pkg.getRelationshipsByType(relationType); + if(rels.size() == 0) { + return null; + } + if(rels.size() > 1) { + throw new IllegalArgumentException("Found " + rels.size() + " relations for the type " + relationType + ", should only ever be one!"); + } + PackageRelationship rel = rels.getRelationship(0); + return getTargetPart(rel); + } + + /** + * Retrieves all the PackageParts which are defined as + * relationships of the base document with the + * specified content type. + */ + protected PackagePart[] getRelatedByType(String contentType) throws InvalidFormatException { + PackageRelationshipCollection partsC = + getCorePart().getRelationshipsByType(contentType); + + PackagePart[] parts = new PackagePart[partsC.size()]; + int count = 0; + for (PackageRelationship rel : partsC) { + parts[count] = getTargetPart(rel); + count++; + } + return parts; + } + + + /** * Checks that the supplied InputStream (which MUST - * support mark and reset, or be a PushbackInputStream) + * support mark and reset, or be a PushbackInputStream) * has a OOXML (zip) header at the start of it. * If your InputStream does not support mark / reset, * then wrap it in a PushBackInputStream, then be * sure to always use that, and not the original! - * @param inp An InputStream which supports either mark/reset, or is a PushbackInputStream + * @param inp An InputStream which supports either mark/reset, or is a PushbackInputStream */ public static boolean hasOOXMLHeader(InputStream inp) throws IOException { - // We want to peek at the first 4 bytes - inp.mark(4); + // We want to peek at the first 4 bytes + inp.mark(4); - byte[] header = new byte[4]; - IOUtils.readFully(inp, header); + byte[] header = new byte[4]; + IOUtils.readFully(inp, header); // Wind back those 4 bytes if(inp instanceof PushbackInputStream) { - PushbackInputStream pin = (PushbackInputStream)inp; - pin.unread(header); + PushbackInputStream pin = (PushbackInputStream)inp; + pin.unread(header); } else { - inp.reset(); + inp.reset(); } - - // Did it match the ooxml zip signature? + + // Did it match the ooxml zip signature? return ( - header[0] == POIFSConstants.OOXML_FILE_HEADER[0] && - header[1] == POIFSConstants.OOXML_FILE_HEADER[1] && - header[2] == POIFSConstants.OOXML_FILE_HEADER[2] && - header[3] == POIFSConstants.OOXML_FILE_HEADER[3] - ); + header[0] == POIFSConstants.OOXML_FILE_HEADER[0] && + header[1] == POIFSConstants.OOXML_FILE_HEADER[1] && + header[2] == POIFSConstants.OOXML_FILE_HEADER[2] && + header[3] == POIFSConstants.OOXML_FILE_HEADER[3] + ); + } + + /** + * Get the document properties. This gives you access to the + * core ooxml properties, and the extended ooxml properties. + */ + public POIXMLProperties getProperties() throws OpenXML4JException, IOException, XmlException { + if(properties == null) { + properties = new POIXMLProperties(pkg); + } + return properties; } - /** - * Get the document properties. This gives you access to the - * core ooxml properties, and the extended ooxml properties. - */ - public POIXMLProperties getProperties() throws OpenXML4JException, IOException, XmlException { - if(properties == null) { - properties = new POIXMLProperties(pkg); - } - return properties; - } - /** * Get the document's embedded files. */ diff --git a/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java b/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java new file mode 100755 index 0000000000..ed3e59fd00 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/POIXMLDocumentPart.java @@ -0,0 +1,203 @@ +/* ====================================================================
+ 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;
+
+import java.io.IOException;
+import java.util.LinkedList;
+import java.util.List;
+
+import org.apache.xmlbeans.XmlOptions;
+import org.apache.poi.util.POILogger;
+import org.apache.poi.util.POILogFactory;
+import org.openxml4j.exceptions.OpenXML4JException;
+import org.openxml4j.opc.*;
+
+/**
+ * Represents an entry of a OOXML package.
+ *
+ * <p>
+ * Each POIXMLDocumentPart keeps a reference to the underlying a {@link org.openxml4j.opc.PackagePart}.
+ * </p>
+ *
+ * @author Yegor Kozlov
+ */
+public class POIXMLDocumentPart {
+ private static POILogger logger = POILogFactory.getLogger(POIXMLDocumentPart.class);
+
+ public static XmlOptions DEFAULT_XML_OPTIONS;
+ static {
+ DEFAULT_XML_OPTIONS = new XmlOptions();
+ DEFAULT_XML_OPTIONS.setSaveOuter();
+ DEFAULT_XML_OPTIONS.setUseDefaultNamespace();
+ }
+
+ protected PackagePart packagePart;
+ protected PackageRelationship packageRel;
+ protected POIXMLDocumentPart parent;
+
+ protected List<POIXMLDocumentPart> relations;
+
+ public POIXMLDocumentPart(PackagePart part, PackageRelationship rel){
+ relations = new LinkedList<POIXMLDocumentPart>();
+ this.packagePart = part;
+ this.packageRel = rel;
+ }
+
+ /**
+ * Provides access to the underlying PackagePart
+ *
+ * @return the underlying PackagePart
+ */
+ public PackagePart getPackagePart(){
+ return packagePart;
+ }
+
+ /**
+ * Provides access to the PackageRelationship that identifies this POIXMLDocumentPart
+ *
+ * @return the PackageRelationship that identifies this POIXMLDocumentPart
+ */
+ public PackageRelationship getPackageRelationship(){
+ return packageRel;
+ }
+
+ /**
+ * Returns the list of child relations for this POIXMLDocumentPart
+ *
+ * @return child relations
+ */
+ public List<POIXMLDocumentPart> getRelations(){
+ return relations;
+ }
+
+ /**
+ * Add a new child POIXMLDocumentPart
+ *
+ * @param part the child to add
+ */
+ protected void addRelation(POIXMLDocumentPart part){
+ relations.add(part);
+ }
+
+ /**
+ * Returns the parent POIXMLDocumentPart. All parts except root have not-null parent.
+ *
+ * @return the parent POIXMLDocumentPart or <code>null</code> for the root element.
+ */
+ public POIXMLDocumentPart getParent(){
+ return parent;
+ }
+
+ @Override
+ public String toString(){
+ return packagePart.toString();
+ }
+
+ /**
+ * Save the content in the underlying package part.
+ * Default implemenation is empty meaning that the package part is left unmodified.
+ *
+ * Sub-classes should override and add logic to marshal the "model" into Ooxml4J.
+ *
+ * For example, the code saving a generic XML entry may look as follows:
+ * <pre><code>
+ * protected void commit() throws IOException {
+ * PackagePart part = getPackagePart();
+ * OutputStream out = part.getOutputStream();
+ * XmlObject bean = getXmlBean(); //the "model" which holds changes in memory
+ * bean.save(out, DEFAULT_XML_OPTIONS);
+ * out.close();
+ * </code></pre>
+ *
+ */
+ protected void commit() throws IOException {
+
+ }
+
+ /**
+ * Save changes in the underlying OOXML package.
+ */
+ protected void save() throws IOException{
+ commit();
+ for(POIXMLDocumentPart p : relations){
+ p.save();
+ }
+ }
+
+ /**
+ * Create a new child POIXMLDocumentPart
+ *
+ * @param descriptor the part descriptor
+ * @param cls the Class object identifying the type of instance to create
+ * @return the created child POIXMLDocumentPart
+ */
+ protected POIXMLDocumentPart createRelationship(POIXMLRelation descriptor, Class<? extends POIXMLDocumentPart> cls){
+ return createRelationship(descriptor, cls, -1);
+ }
+
+ /**
+ * Create a new child POIXMLDocumentPart
+ *
+ * @param descriptor the part descriptor
+ * @param cls the Class object identifying the type of instance to create
+ * @param idx part number
+ * @return the created child POIXMLDocumentPart
+ */
+ protected POIXMLDocumentPart createRelationship(POIXMLRelation descriptor, Class<? extends POIXMLDocumentPart> cls, int idx){
+ try {
+
+ PackagePartName ppName = PackagingURIHelper.createPartName(descriptor.getFileName(idx));
+ PackageRelationship rel =
+ packagePart.addRelationship(ppName, TargetMode.INTERNAL, descriptor.getRelation());
+
+ PackagePart part = packagePart.getPackage().createPart(ppName, descriptor.getContentType());
+ POIXMLDocumentPart doc = cls.newInstance();
+ doc.packageRel = rel;
+ doc.packagePart = part;
+ addRelation(doc);
+ return doc;
+ } catch (Exception e){
+ throw new POIXMLException(e);
+ }
+ }
+
+ /**
+ * Iterate through the underlying PackagePart and create child POIXMLFactory instances
+ * using the specified factory
+ *
+ * @param factory the factory object that creates POIXMLFactory instances
+ */
+ protected void read(POIXMLFactory factory) throws OpenXML4JException {
+ PackageRelationshipCollection rels = packagePart.getRelationships();
+ for (PackageRelationship rel : rels) {
+ if(rel.getTargetMode() == TargetMode.INTERNAL){
+ PackagePartName relName = PackagingURIHelper.createPartName(rel.getTargetURI());
+ PackagePart p = packagePart.getPackage().getPart(relName);
+ if(p == null) {
+ logger.log(POILogger.ERROR, "Skipped invalid entry " + rel.getTargetURI());
+ continue;
+ }
+ POIXMLDocumentPart childPart = factory.create(rel, p);
+ childPart.parent = this;
+ addRelation(childPart);
+
+ if(p.hasRelationships()) childPart.read(factory);
+ }
+ }
+ }
+
+}
diff --git a/src/ooxml/java/org/apache/poi/POIXMLException.java b/src/ooxml/java/org/apache/poi/POIXMLException.java new file mode 100755 index 0000000000..1a4bf7ff1d --- /dev/null +++ b/src/ooxml/java/org/apache/poi/POIXMLException.java @@ -0,0 +1,69 @@ +/* ====================================================================
+ 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;
+
+/**
+ * Indicates a generic OOXML error.
+ *
+ * @author Yegor Kozlov
+ */
+public class POIXMLException extends RuntimeException{
+ /**
+ * Create a new <code>POIXMLException</code> with no
+ * detail mesage.
+ */
+ public POIXMLException() {
+ super();
+ }
+
+ /**
+ * Create a new <code>POIXMLException</code> with
+ * the <code>String</code> specified as an error message.
+ *
+ * @param msg The error message for the exception.
+ */
+ public POIXMLException(String msg) {
+ super(msg);
+ }
+
+ /**
+ * Create a new <code>POIXMLException</code> with
+ * the <code>String</code> specified as an error message and the cause.
+ *
+ * @param msg The error message for the exception.
+ * @param cause the cause (which is saved for later retrieval by the
+ * {@link #getCause()} method). (A <tt>null</tt> value is
+ * permitted, and indicates that the cause is nonexistent or
+ * unknown.)
+ */
+ public POIXMLException(String msg, Throwable cause) {
+ super(msg, cause);
+ }
+
+ /**
+ * Create a new <code>POIXMLException</code> with
+ * the specified cause.
+ *
+ * @param cause the cause (which is saved for later retrieval by the
+ * {@link #getCause()} method). (A <tt>null</tt> value is
+ * permitted, and indicates that the cause is nonexistent or
+ * unknown.)
+ */
+ public POIXMLException(Throwable cause) {
+ super(cause);
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/POIXMLFactory.java b/src/ooxml/java/org/apache/poi/POIXMLFactory.java new file mode 100755 index 0000000000..6909cb9c31 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/POIXMLFactory.java @@ -0,0 +1,40 @@ +/* ====================================================================
+ 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;
+
+import org.openxml4j.opc.PackageRelationship;
+import org.openxml4j.opc.PackagePart;
+
+
+/**
+ * Defines a factory API that enables sub-classes to create instances of <code>POIXMLDocumentPart</code>
+ *
+ * @author Yegor Kozlov
+ */
+public class POIXMLFactory {
+
+ /**
+ * Creates a new instance of a {@link POIXMLDocumentPart}
+ *
+ * @param rel the package part relationship
+ * @param part the PackagePart representing the created instance
+ * @return A new instance of a POIXMLDocumentPart.
+ */
+ public POIXMLDocumentPart create(PackageRelationship rel, PackagePart part){
+ return new POIXMLDocumentPart(part, rel);
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/POIXMLRelation.java b/src/ooxml/java/org/apache/poi/POIXMLRelation.java new file mode 100755 index 0000000000..a786c02d47 --- /dev/null +++ b/src/ooxml/java/org/apache/poi/POIXMLRelation.java @@ -0,0 +1,54 @@ +/* ====================================================================
+ 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;
+
+/**
+ * Represents a descriptor of a OOXML relation.
+ *
+ * @author Yegor Kozlov
+ */
+public class POIXMLRelation {
+
+ protected String _type;
+ protected String _relation;
+ protected String _defaultName;
+
+ /**
+ * Instantiates a POIXMLRelation.
+ */
+ protected POIXMLRelation(String type, String rel, String defaultName) {
+ _type = type;
+ _relation = rel;
+ _defaultName = defaultName;
+ }
+
+ public String getContentType() { return _type; }
+ public String getRelation() { return _relation; }
+ public String getDefaultFileName() { return _defaultName; }
+
+ /**
+ * Returns the filename for the nth one of these,
+ * eg /xl/comments4.xml
+ */
+ public String getFileName(int index) {
+ if(_defaultName.indexOf("#") == -1) {
+ // Generic filename in all cases
+ return getDefaultFileName();
+ }
+ return _defaultName.replace("#", Integer.toString(index));
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/util/PackageHelper.java b/src/ooxml/java/org/apache/poi/util/PackageHelper.java new file mode 100755 index 0000000000..4471a6046f --- /dev/null +++ b/src/ooxml/java/org/apache/poi/util/PackageHelper.java @@ -0,0 +1,150 @@ +/* ====================================================================
+ 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.util;
+
+import org.openxml4j.opc.*;
+import org.openxml4j.opc.Package;
+import org.openxml4j.opc.internal.PackagePropertiesPart;
+import org.openxml4j.opc.internal.marshallers.PackagePropertiesMarshaller;
+import org.openxml4j.exceptions.OpenXML4JException;
+import org.apache.poi.util.IOUtils;
+
+import java.io.*;
+import java.util.ArrayList;
+import java.lang.reflect.Method;
+
+/**
+ * Provides handy methods to work with OOXML packages
+ *
+ * @author Yegor Kozlov
+ */
+public class PackageHelper {
+
+ /**
+ * Clone the specified package.
+ *
+ * @param pkg the package to clone
+ * @return the cloned package
+ */
+ public static Package clone(Package pkg) throws OpenXML4JException, IOException {
+ return clone(pkg, createTempFile());
+ }
+
+ /**
+ * Clone the specified package.
+ *
+ * @param pkg the package to clone
+ * @param file the destination file
+ * @return the cloned package
+ */
+ public static Package clone(Package pkg, File file) throws OpenXML4JException, IOException {
+
+ String path = file.getAbsolutePath();
+
+ Package dest = Package.create(path);
+ PackageRelationshipCollection rels = pkg.getRelationships();
+ for (PackageRelationship rel : rels) {
+ PackagePart part = pkg.getPart(rel);
+ PackagePart part_tgt;
+ if (rel.getRelationshipType().equals(PackageRelationshipTypes.CORE_PROPERTIES)) {
+ copyProperties(pkg.getPackageProperties(), dest.getPackageProperties());
+ continue;
+ } else {
+ dest.addRelationship(part.getPartName(), rel.getTargetMode(), rel.getRelationshipType());
+ part_tgt = dest.createPart(part.getPartName(), part.getContentType());
+ }
+
+ OutputStream out = part_tgt.getOutputStream();
+ IOUtils.copy(part.getInputStream(), out);
+ out.close();
+
+ if(part.hasRelationships()) {
+ copy(pkg, part, dest, part_tgt);
+ }
+ }
+ dest.close();
+
+ //the temp file will be deleted when JVM terminates
+ new File(path).deleteOnExit();
+ return Package.open(path);
+ }
+
+ /**
+ *
+ * @return
+ * @throws IOException
+ */
+ public static File createTempFile() throws IOException {
+ File file = File.createTempFile("poi-ooxml-", ".tmp");
+ //there is no way to pass an existing file to Package.create(file),
+ //delete first, the file will be re-created in Packe.create(file)
+ file.delete();
+ file.deleteOnExit();
+ return file;
+
+ }
+
+ /**
+ * Recursively copy package parts to the destination package
+ */
+ private static void copy(Package pkg, PackagePart part, Package tgt, PackagePart part_tgt) throws OpenXML4JException, IOException {
+ PackageRelationshipCollection rels = part.getRelationships();
+ if(rels != null) for (PackageRelationship rel : rels) {
+ PackagePart p;
+ if(rel.getTargetMode() == TargetMode.EXTERNAL){
+ part_tgt.addExternalRelationship(rel.getTargetURI().toString(), rel.getRelationshipType(), rel.getId());
+ //external relations don't have associated package parts
+ continue;
+ } else {
+ PackagePartName relName = PackagingURIHelper.createPartName(rel.getTargetURI());
+ p = pkg.getPart(relName);
+ }
+
+ part_tgt.addRelationship(p.getPartName(), rel.getTargetMode(), rel.getRelationshipType(), rel.getId());
+
+ PackagePart dest;
+ if(!tgt.containPart(p.getPartName())){
+ dest = tgt.createPart(p.getPartName(), p.getContentType());
+ OutputStream out = dest.getOutputStream();
+ IOUtils.copy(p.getInputStream(), out);
+ out.close();
+ copy(pkg, p, tgt, dest);
+ }
+ }
+ }
+
+ /**
+ * Copy core package properties
+ *
+ * @param src source properties
+ * @param tgt target properties
+ */
+ private static void copyProperties(PackageProperties src, PackageProperties tgt){
+ tgt.setCategoryProperty(src.getCategoryProperty().getValue());
+ tgt.setContentStatusProperty(src.getContentStatusProperty().getValue());
+ tgt.setContentTypeProperty(src.getContentTypeProperty().getValue());
+ tgt.setCreatorProperty(src.getCreatorProperty().getValue());
+ tgt.setDescriptionProperty(src.getDescriptionProperty().getValue());
+ tgt.setIdentifierProperty(src.getIdentifierProperty().getValue());
+ tgt.setKeywordsProperty(src.getKeywordsProperty().getValue());
+ tgt.setLanguageProperty(src.getLanguageProperty().getValue());
+ tgt.setRevisionProperty(src.getRevisionProperty().getValue());
+ tgt.setSubjectProperty(src.getSubjectProperty().getValue());
+ tgt.setTitleProperty(src.getTitleProperty().getValue());
+ tgt.setVersionProperty(src.getVersionProperty().getValue());
+ }
+}
diff --git a/src/ooxml/java/org/apache/poi/xssf/dev/XSSFDump.java b/src/ooxml/java/org/apache/poi/xssf/dev/XSSFDump.java index 6c3d2dcbdc..3bb60e9034 100755 --- a/src/ooxml/java/org/apache/poi/xssf/dev/XSSFDump.java +++ b/src/ooxml/java/org/apache/poi/xssf/dev/XSSFDump.java @@ -21,10 +21,7 @@ import org.w3c.dom.Document; import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.DocumentBuilder;
-import java.io.FileOutputStream;
-import java.io.File;
-import java.io.IOException;
-import java.io.InputStream;
+import java.io.*;
import java.util.zip.ZipFile;
import java.util.zip.ZipEntry;
import java.util.Enumeration;
@@ -70,24 +67,32 @@ public class XSSFDump { FileOutputStream out = new FileOutputStream(f);
if(entry.getName().endsWith(".xml") || entry.getName().endsWith(".vml") || entry.getName().endsWith(".rels")){
- //pass the xml through the Xerces serializer to produce nicely formatted output
- Document doc = builder.parse(zip.getInputStream(entry));
-
- OutputFormat format = new OutputFormat( doc );
- format.setIndenting(true);
-
- XMLSerializer serial = new XMLSerializer( out, format );
- serial.asDOMSerializer();
- serial.serialize( doc.getDocumentElement() );
-
+ try {
+ //pass the xml through the Xerces serializer to produce nicely formatted output
+ Document doc = builder.parse(zip.getInputStream(entry));
+
+ OutputFormat format = new OutputFormat( doc );
+ format.setIndenting(true);
+
+ XMLSerializer serial = new XMLSerializer( out, format );
+ serial.asDOMSerializer();
+ serial.serialize( doc.getDocumentElement() );
+ } catch (Exception e){
+ System.err.println("Failed to parse " + entry.getName() + ", dumping raw content");
+ dump(zip.getInputStream(entry), out);
+ }
} else {
- int pos;
- byte[] chunk = new byte[2048];
- InputStream is = zip.getInputStream(entry);
- while((pos = is.read(chunk)) > 0) out.write(chunk, 0, pos);
+ dump(zip.getInputStream(entry), out);
}
out.close();
}
}
+
+ protected static void dump(InputStream is, OutputStream out) throws IOException{
+ int pos;
+ byte[] chunk = new byte[2048];
+ while((pos = is.read(chunk)) > 0) out.write(chunk, 0, pos);
+
+ }
}
diff --git a/src/ooxml/java/org/apache/poi/xssf/dev/XSSFSave.java b/src/ooxml/java/org/apache/poi/xssf/dev/XSSFSave.java index 148a02fcb2..7ca4a3d691 100755 --- a/src/ooxml/java/org/apache/poi/xssf/dev/XSSFSave.java +++ b/src/ooxml/java/org/apache/poi/xssf/dev/XSSFSave.java @@ -31,6 +31,7 @@ public class XSSFSave { for (int i = 0; i < args.length; i++) {
XSSFWorkbook wb = new XSSFWorkbook(args[i]);
+ System.out.println("wb.getNumberOfSheets(): " + wb.getNumberOfSheets());
int sep = args[i].lastIndexOf('.');
String outfile = args[i].substring(0, sep) + "-save.xlsx";
FileOutputStream out = new FileOutputStream(outfile);
diff --git a/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java b/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java index 388fda66ba..15c09c80a9 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/CommentsTable.java @@ -23,6 +23,7 @@ import java.io.OutputStream; import org.apache.poi.ss.usermodel.CommentsSource; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFComment; +import org.apache.poi.POIXMLDocumentPart; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlOptions; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAuthors; @@ -30,103 +31,121 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCommentList; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CommentsDocument; +import org.openxml4j.opc.PackagePart; +import org.openxml4j.opc.PackageRelationship; -public class CommentsTable implements CommentsSource, XSSFModel { - private CTComments comments; - - public CommentsTable(InputStream is) throws IOException { - readFrom(is); - } - public CommentsTable() { - comments = CTComments.Factory.newInstance(); - } - /** - * For unit testing only! - */ - public CommentsTable(CTComments comments) { - this.comments = comments; - } - - public void readFrom(InputStream is) throws IOException { - try { - CommentsDocument doc = CommentsDocument.Factory.parse(is); - comments = doc.getComments(); +public class CommentsTable extends POIXMLDocumentPart implements CommentsSource, XSSFModel { + private CTComments comments; + + public CommentsTable(InputStream is) throws IOException { + super(null, null); + readFrom(is); + } + public CommentsTable() { + super(null, null); + comments = CTComments.Factory.newInstance(); + } + /** + * For unit testing only! + */ + public CommentsTable(CTComments comments) { + super(null, null); + this.comments = comments; + } + + public CommentsTable(PackagePart part, PackageRelationship rel) throws IOException { + super(part, rel); + readFrom(part.getInputStream()); + } + + public void readFrom(InputStream is) throws IOException { + try { + CommentsDocument doc = CommentsDocument.Factory.parse(is); + comments = doc.getComments(); } catch (XmlException e) { throw new IOException(e.getLocalizedMessage()); } - } - public void writeTo(OutputStream out) throws IOException { + } + public void writeTo(OutputStream out) throws IOException { XmlOptions options = new XmlOptions(); options.setSaveOuter(); options.setUseDefaultNamespace(); - + // Requests use of whitespace for easier reading //options.setSavePrettyPrint(); - + CommentsDocument doc = CommentsDocument.Factory.newInstance(options); doc.setComments(comments); doc.save(out, options); - } - - public int getNumberOfComments() { - return comments.getCommentList().sizeOfCommentArray(); - } - public int getNumberOfAuthors() { - return getCommentsAuthors().sizeOfAuthorArray(); - } - - public String getAuthor(long authorId) { - return getCommentsAuthors().getAuthorArray((int)authorId); - } - - public int findAuthor(String author) { - for (int i = 0 ; i < getCommentsAuthors().sizeOfAuthorArray() ; i++) { - if (getCommentsAuthors().getAuthorArray(i).equals(author)) { - return i; - } - } - return addNewAuthor(author); - } - - public XSSFComment findCellComment(int row, int column) { - return findCellComment( - (new CellReference(row, column)).formatAsString() ); - } - - public XSSFComment findCellComment(String cellRef) { - for (CTComment comment : getCommentsList().getCommentArray()) { - if (cellRef.equals(comment.getRef())) { - return new XSSFComment(this, comment); - } - } - return null; - } - - /** - * Generates a new XSSFComment, associated with the - * current comments list. - */ - public XSSFComment addComment() { - return new XSSFComment(this, getCommentsList().addNewComment()); - } - - private CTCommentList getCommentsList() { - if (comments.getCommentList() == null) { - comments.addNewCommentList(); - } - return comments.getCommentList(); - } - - private CTAuthors getCommentsAuthors() { - if (comments.getAuthors() == null) { - comments.addNewAuthors(); - } - return comments.getAuthors(); - } - - private int addNewAuthor(String author) { - int index = getCommentsAuthors().sizeOfAuthorArray(); - getCommentsAuthors().insertAuthor(index, author); - return index; - } + } + + @Override + protected void commit() throws IOException { + PackagePart part = getPackagePart(); + OutputStream out = part.getOutputStream(); + writeTo(out); + out.close(); + } + + public int getNumberOfComments() { + return comments.getCommentList().sizeOfCommentArray(); + } + public int getNumberOfAuthors() { + return getCommentsAuthors().sizeOfAuthorArray(); + } + + public String getAuthor(long authorId) { + return getCommentsAuthors().getAuthorArray((int)authorId); + } + + public int findAuthor(String author) { + for (int i = 0 ; i < getCommentsAuthors().sizeOfAuthorArray() ; i++) { + if (getCommentsAuthors().getAuthorArray(i).equals(author)) { + return i; + } + } + return addNewAuthor(author); + } + + public XSSFComment findCellComment(int row, int column) { + return findCellComment( + (new CellReference(row, column)).formatAsString() ); + } + + public XSSFComment findCellComment(String cellRef) { + for (CTComment comment : getCommentsList().getCommentArray()) { + if (cellRef.equals(comment.getRef())) { + return new XSSFComment(this, comment); + } + } + return null; + } + + /** + * Generates a new XSSFComment, associated with the + * current comments list. + */ + public XSSFComment addComment() { + return new XSSFComment(this, getCommentsList().addNewComment()); + } + + private CTCommentList getCommentsList() { + if (comments.getCommentList() == null) { + comments.addNewCommentList(); + } + return comments.getCommentList(); + } + + private CTAuthors getCommentsAuthors() { + if (comments.getAuthors() == null) { + comments.addNewAuthors(); + } + return comments.getAuthors(); + } + + private int addNewAuthor(String author) { + int index = getCommentsAuthors().sizeOfAuthorArray(); + getCommentsAuthors().insertAuthor(index, author); + return index; + } }
\ No newline at end of file diff --git a/src/ooxml/java/org/apache/poi/xssf/model/SharedStringsTable.java b/src/ooxml/java/org/apache/poi/xssf/model/SharedStringsTable.java index b526d30828..c004905412 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/SharedStringsTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/SharedStringsTable.java @@ -27,9 +27,13 @@ import java.util.Map; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlOptions; +import org.apache.poi.POIXMLDocumentPart; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSst; import org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; +import org.openxml4j.opc.PackagePart; +import org.openxml4j.opc.PackageRelationship; /** @@ -56,7 +60,7 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.SstDocument; * @author Nick Birch * @author Yegor Kozlov */ -public class SharedStringsTable implements SharedStringSource, XSSFModel { +public class SharedStringsTable extends POIXMLDocumentPart implements XSSFModel, SharedStringSource { /** * Array of individual string items in the Shared String table. @@ -89,13 +93,17 @@ public class SharedStringsTable implements SharedStringSource, XSSFModel { * @throws IOException if an error occurs while reading. */ public SharedStringsTable(InputStream is) throws IOException { + super(null, null); readFrom(is); } - /** - * Create a new, empty SharedStringsTable - */ + public SharedStringsTable() { - count = uniqueCount = 0; + super(null, null); + } + + public SharedStringsTable(PackagePart part, PackageRelationship rel) throws IOException { + super(part, rel); + readFrom(part.getInputStream()); } /** @@ -204,4 +212,12 @@ public class SharedStringsTable implements SharedStringSource, XSSFModel { sst.setSiArray(ctr); doc.save(out, options); } + + @Override + protected void commit() throws IOException { + PackagePart part = getPackagePart(); + OutputStream out = part.getOutputStream(); + writeTo(out); + out.close(); + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java index 79d28bf115..4f4c92e177 100644 --- a/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java +++ b/src/ooxml/java/org/apache/poi/xssf/model/StylesTable.java @@ -36,6 +36,7 @@ import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder; import org.apache.poi.xssf.usermodel.extensions.XSSFCellFill; +import org.apache.poi.POIXMLDocumentPart; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlOptions; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBorder; @@ -54,13 +55,16 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTStylesheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf; import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPatternType; import org.openxmlformats.schemas.spreadsheetml.x2006.main.StyleSheetDocument; +import org.openxml4j.opc.PackagePart; +import org.openxml4j.opc.PackageRelationship; + /** * Table of styles shared across all sheets in a workbook. * * @author ugo */ -public class StylesTable implements StylesSource, XSSFModel { +public class StylesTable extends POIXMLDocumentPart implements StylesSource, XSSFModel { private final Hashtable<Long,String> numberFormats = new Hashtable<Long,String>(); private final List<CTFont> fonts = new ArrayList<CTFont>(); private final List<CTFill> fills = new LinkedList<CTFill>(); @@ -85,18 +89,25 @@ public class StylesTable implements StylesSource, XSSFModel { * @throws IOException if an error occurs while reading. */ public StylesTable(InputStream is) throws IOException { + super(null, null); readFrom(is); } /** * Create a new, empty StylesTable */ public StylesTable() { + super(null, null); doc = StyleSheetDocument.Factory.newInstance(); doc.addNewStyleSheet(); // Initialization required in order to make the document readable by MSExcel initialize(); } + public StylesTable(PackagePart part, PackageRelationship rel) throws IOException { + super(part, rel); + readFrom(part.getInputStream()); + } + /** * Read this shared styles table from an XML file. * @@ -365,6 +376,14 @@ public class StylesTable implements StylesSource, XSSFModel { doc.save(out, options); } + @Override + protected void commit() throws IOException { + PackagePart part = getPackagePart(); + OutputStream out = part.getOutputStream(); + writeTo(out); + out.close(); + } + private long putBorder(XSSFCellBorder border, List<CTBorder> borders) { return border.putBorder((LinkedList<CTBorder>) borders); // TODO - use List instead of LinkedList } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java index 771f68482f..a0bc00d5ab 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCreationHelper.java @@ -23,26 +23,22 @@ import org.apache.poi.ss.usermodel.RichTextString; public class XSSFCreationHelper implements CreationHelper { private XSSFWorkbook workbook; - private XSSFDataFormat dataFormat; XSSFCreationHelper(XSSFWorkbook wb) { workbook = wb; - - // Create the things we only ever need one of - dataFormat = new XSSFDataFormat(workbook.getStylesSource()); } /** * Creates a new XSSFRichTextString for you. */ - public RichTextString createRichTextString(String text) { + public XSSFRichTextString createRichTextString(String text) { return new XSSFRichTextString(text); } - public DataFormat createDataFormat() { - return dataFormat; + public XSSFDataFormat createDataFormat() { + return workbook.createDataFormat(); } - public Hyperlink createHyperlink(int type) { + public XSSFHyperlink createHyperlink(int type) { return new XSSFHyperlink(type); } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFactory.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFactory.java new file mode 100755 index 0000000000..44f8054b3c --- /dev/null +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFFactory.java @@ -0,0 +1,57 @@ +/* ====================================================================
+ 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 org.apache.poi.POIXMLDocumentPart;
+import org.apache.poi.POIXMLFactory;
+import org.apache.poi.POIXMLException;
+import org.apache.poi.xssf.model.SharedStringsTable;
+import org.apache.poi.xssf.model.StylesTable;
+import org.apache.poi.xssf.model.CommentsTable;
+import org.openxml4j.opc.PackageRelationship;
+import org.openxml4j.opc.PackagePart;
+
+import java.util.Map;
+import java.util.HashMap;
+import java.lang.reflect.Constructor;
+
+/**
+ * Instantiates sub-classes of POIXMLDocumentPart depending on their relationship type
+ *
+ * @author Yegor Kozlov
+ */
+public class XSSFFactory extends POIXMLFactory {
+ protected static Map<String, Class> parts = new HashMap<String, Class>();
+ static {
+ parts.put(XSSFRelation.WORKSHEET.getRelation(), XSSFSheet.class);
+ parts.put(XSSFRelation.SHARED_STRINGS.getRelation(), SharedStringsTable.class);
+ parts.put(XSSFRelation.STYLES.getRelation(), StylesTable.class);
+ parts.put(XSSFRelation.SHEET_COMMENTS.getRelation(), CommentsTable.class);
+ }
+
+ public POIXMLDocumentPart create(PackageRelationship rel, PackagePart p){
+ Class cls = parts.get(rel.getRelationshipType());
+ if(cls == null) return super.create(rel, p);
+
+ try {
+ Constructor<? extends POIXMLDocumentPart> constructor = cls.getConstructor(PackagePart.class, PackageRelationship.class);
+ return constructor.newInstance(p, rel);
+ } catch (Exception e){
+ throw new POIXMLException(e);
+ }
+ }
+}
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 71f3f8ce0d..42781a5926 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFRelation.java @@ -26,6 +26,7 @@ import java.util.Iterator; import java.util.List; import org.apache.poi.POIXMLDocument; +import org.apache.poi.POIXMLRelation; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; import org.apache.poi.xssf.model.BinaryPart; @@ -49,7 +50,7 @@ import org.openxml4j.opc.TargetMode; /** * */ -public final class XSSFRelation<W extends XSSFModel> { +public final class XSSFRelation<W extends XSSFModel> extends POIXMLRelation { public static final XSSFRelation WORKBOOK = new XSSFRelation( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", @@ -156,16 +157,12 @@ public final class XSSFRelation<W extends XSSFModel> { return new XSSFRelation<R>(type, rel, defaultName, cls); } - private String _type; - private String _relation; - private String _defaultName; private Constructor<W> _constructor; private final boolean _constructorTakesTwoArgs; private XSSFRelation(String type, String rel, String defaultName, Class<W> cls) { - _type = type; - _relation = rel; - _defaultName = defaultName; + super(type, rel, defaultName); + if (cls == null) { _constructor = null; _constructorTakesTwoArgs = false; @@ -189,10 +186,7 @@ public final class XSSFRelation<W extends XSSFModel> { _constructorTakesTwoArgs = twoArg; } } - public String getContentType() { return _type; } - public String getRelation() { return _relation; } - public String getDefaultFileName() { return _defaultName; } - + /** * Does one of these exist for the given core * package part? 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 dbf6a45de7..1d356b188e 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFSheet.java @@ -24,6 +24,7 @@ import java.util.Arrays; import java.util.Iterator; import java.util.LinkedList; import java.util.List; +import javax.xml.namespace.QName; import org.apache.poi.hssf.util.PaneInformation; import org.apache.poi.ss.usermodel.CellStyle; @@ -41,1531 +42,1528 @@ import org.apache.poi.xssf.model.CommentsTable; import org.apache.poi.xssf.model.Control; import org.apache.poi.xssf.model.Drawing; import org.apache.poi.xssf.usermodel.helpers.ColumnHelper; +import org.apache.poi.POIXMLDocumentPart; +import org.apache.poi.POIXMLException; import org.apache.xmlbeans.XmlOptions; +import org.apache.xmlbeans.XmlException; import org.openxml4j.opc.PackagePart; import org.openxml4j.opc.PackageRelationship; import org.openxml4j.opc.PackageRelationshipCollection; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBreak; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHeaderFooter; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTHyperlink; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCell; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTMergeCells; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTOutlinePr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageBreak; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageMargins; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPageSetUpPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPane; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPrintOptions; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRow; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSelection; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetFormatPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetPr; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetView; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetViews; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.STPane; +import org.openxmlformats.schemas.spreadsheetml.x2006.main.*; /** - * High level representation of a worksheet. + * High level representation of a SpreadsheetML worksheet. * * <p> * Sheets are the central structures within a workbook, and are where a user does most of his spreadsheet work. * The most common type of sheet is the worksheet, which is represented as a grid of cells. Worksheet cells can - * contain text, numbers, dates, and formulas. Cells can also be formatted. A workbook usually contains more than one sheet. + * contain text, numbers, dates, and formulas. Cells can also be formatted. * </p> */ -public class XSSFSheet implements Sheet { - protected CTSheet sheet; - protected CTWorksheet worksheet; - protected CTDialogsheet dialogsheet; - protected List<Row> rows; - protected List<XSSFHyperlink> hyperlinks; - protected ColumnHelper columnHelper; - protected XSSFWorkbook workbook; - protected CommentsSource sheetComments; - protected CTMergeCells ctMergeCells; - - - protected List<Drawing> drawings; - protected List<Control> controls; - - - public static final short LeftMargin = 0; - public static final short RightMargin = 1; - public static final short TopMargin = 2; - public static final short BottomMargin = 3; - public static final short HeaderMargin = 4; - public static final short FooterMargin = 5; - - public XSSFSheet(CTSheet sheet, CTWorksheet worksheet, XSSFWorkbook workbook, CommentsSource sheetComments, List<Drawing> drawings, List<Control> controls) { - this(sheet, worksheet, workbook, sheetComments); - this.drawings = drawings; - this.controls = controls; - } - - public List<Drawing> getDrawings() - { - return drawings; - } - - public List<Control> getControls() - { - return controls; - } - - public XSSFSheet(CTSheet sheet, CTWorksheet worksheet, XSSFWorkbook workbook, CommentsSource sheetComments) { - this(sheet, worksheet, workbook); - this.sheetComments = sheetComments; - } - - public XSSFSheet(CTSheet sheet, CTWorksheet worksheet, XSSFWorkbook workbook) { - this.workbook = workbook; - this.sheet = sheet; - this.worksheet = worksheet; - - if (this.worksheet.getSheetData() == null) { - this.worksheet.addNewSheetData(); - } - - initRows(this.worksheet); - initColumns(this.worksheet); - - hyperlinks = new ArrayList<XSSFHyperlink>(); - } - - protected XSSFSheet(XSSFWorkbook workbook) { - this.workbook = workbook; - - hyperlinks = new ArrayList<XSSFHyperlink>(); - } - - /** - * Returns the parent XSSFWorkbook - * - * @return the parent XSSFWorkbook - */ - public XSSFWorkbook getWorkbook() { - return this.workbook; - } - - /** - * Create a new CTWorksheet instance and setup default values - * - * @return a new instance - */ - protected static CTWorksheet newInstance(){ - CTWorksheet worksheet = CTWorksheet.Factory.newInstance(); - CTSheetFormatPr ctFormat = worksheet.addNewSheetFormatPr(); - ctFormat.setDefaultRowHeight(15.0); - - CTSheetView ctView = worksheet.addNewSheetViews().addNewSheetView(); - ctView.setTabSelected(true); - ctView.setWorkbookViewId(0); - - worksheet.addNewDimension().setRef("A1"); - - worksheet.addNewSheetData(); - - CTPageMargins ctMargins = worksheet.addNewPageMargins(); - ctMargins.setBottom(0.75); - ctMargins.setFooter(0.3); - ctMargins.setHeader(0.3); - ctMargins.setLeft(0.7); - ctMargins.setRight(0.7); - ctMargins.setTop(0.75); - - return worksheet; - } - - /** - * Tweaks the CTWorksheet to fit with what Excel - * will accept without a massive huff, and write into - * the OutputStream supplied. - */ - protected void save(PackagePart sheetPart, XmlOptions xmlOptions) throws IOException { - // Excel objects to <cols/> - if(worksheet.getColsArray().length == 1) { - CTCols col = worksheet.getColsArray(0); - if(col.getColArray().length == 0) { - worksheet.setColsArray(null); - } - } - - // Now re-generate our CTHyperlinks, if needed - if(hyperlinks.size() > 0) { - if(worksheet.getHyperlinks() == null) { - worksheet.addNewHyperlinks(); - } - CTHyperlink[] ctHls = new CTHyperlink[hyperlinks.size()]; - for(int i=0; i<ctHls.length; i++) { - // If our sheet has hyperlinks, have them add - // any relationships that they might need - XSSFHyperlink hyperlink = hyperlinks.get(i); - hyperlink.generateRelationIfNeeded(sheetPart); - // Now grab their underling object - ctHls[i] = hyperlink.getCTHyperlink(); - } - worksheet.getHyperlinks().setHyperlinkArray(ctHls); - } - - // Save - OutputStream out = sheetPart.getOutputStream(); - worksheet.save(out, xmlOptions); - out.close(); - } - - /** - * Provide access to the underlying XML bean - * - * @return the underlying CTWorksheet bean - */ - public CTWorksheet getWorksheet() { - return this.worksheet; - } - - public ColumnHelper getColumnHelper() { - return columnHelper; - } - - protected void initRows(CTWorksheet worksheet) { - this.rows = new LinkedList<Row>(); - for (CTRow row : worksheet.getSheetData().getRowArray()) { - this.rows.add(new XSSFRow(row, this)); - } - } - - protected void initColumns(CTWorksheet worksheet) { - columnHelper = new ColumnHelper(worksheet); - } - - protected void initHyperlinks(PackageRelationshipCollection hyperRels) { - if(worksheet.getHyperlinks() == null) return; - - // Turn each one into a XSSFHyperlink - for(CTHyperlink hyperlink : worksheet.getHyperlinks().getHyperlinkArray()) { - PackageRelationship hyperRel = null; - if(hyperlink.getId() != null) { - hyperRel = hyperRels.getRelationshipByID(hyperlink.getId()); - } - - hyperlinks.add( - new XSSFHyperlink(hyperlink, hyperRel) - ); - } - } - - protected CTSheet getSheet() { - return this.sheet; - } - public int addMergedRegion(CellRangeAddress cra) { - Region r = new Region(cra.getFirstRow(), (short)cra.getFirstColumn(), - cra.getLastRow(), (short)cra.getLastColumn()); - return addMergedRegion(r); - } - - - public int addMergedRegion(Region region) { - addNewMergeCell(region); - return ctMergeCells.sizeOfMergeCellArray(); - } - - /** - * Adjusts the column width to fit the contents. - * - * This process can be relatively slow on large sheets, so this should - * normally only be called once per column, at the end of your - * processing. - * - * @param column the column index - */ +public class XSSFSheet extends POIXMLDocumentPart implements Sheet { + protected CTSheet sheet; + protected CTWorksheet worksheet; + protected CTDialogsheet dialogsheet; + protected List<Row> rows; + protected List<XSSFHyperlink> hyperlinks; + protected ColumnHelper columnHelper; + protected CommentsSource sheetComments; + protected CTMergeCells ctMergeCells; + + + protected List<Drawing> drawings; + protected List<Control> controls; + + + public static final short LeftMargin = 0; + public static final short RightMargin = 1; + public static final short TopMargin = 2; + public static final short BottomMargin = 3; + public static final short HeaderMargin = 4; + public static final short FooterMargin = 5; + + + public XSSFSheet() { + super(null, null); + this.worksheet = newSheet(); + initialize(); + } + + public XSSFSheet(PackagePart part, PackageRelationship rel) throws IOException, XmlException { + super(part, rel); + worksheet = WorksheetDocument.Factory.parse(part.getInputStream()).getWorksheet(); + } + + public XSSFSheet(CTSheet sheet, CTWorksheet worksheet, XSSFWorkbook workbook, CommentsSource sheetComments) { + super(null, null); + this.parent = workbook; + this.sheet = sheet; + this.worksheet = worksheet; + this.sheetComments = sheetComments; + + initialize(); + } + + public XSSFSheet(CTSheet sheet, CTWorksheet worksheet, XSSFWorkbook workbook) { + this(sheet, worksheet, workbook, null); + } + + protected XSSFSheet(XSSFWorkbook workbook) { + super(null, null); + this.parent = workbook; + + hyperlinks = new ArrayList<XSSFHyperlink>(); + } + + /** + * Returns the parent XSSFWorkbook + * + * @return the parent XSSFWorkbook + */ + public XSSFWorkbook getWorkbook() { + return (XSSFWorkbook)getParent(); + } + + protected void initialize(){ + if (this.worksheet.getSheetData() == null) { + this.worksheet.addNewSheetData(); + } + initRows(this.worksheet); + initColumns(this.worksheet); + + for(POIXMLDocumentPart p : getRelations()){ + if(p instanceof CommentsTable) sheetComments = (CommentsTable)p; + } + hyperlinks = new ArrayList<XSSFHyperlink>(); + } + + /** + * Create a new CTWorksheet instance and setup default values + * + * @return a new instance + */ + protected static CTWorksheet newSheet(){ + CTWorksheet worksheet = CTWorksheet.Factory.newInstance(); + CTSheetFormatPr ctFormat = worksheet.addNewSheetFormatPr(); + ctFormat.setDefaultRowHeight(15.0); + + CTSheetView ctView = worksheet.addNewSheetViews().addNewSheetView(); + ctView.setTabSelected(true); + ctView.setWorkbookViewId(0); + + worksheet.addNewDimension().setRef("A1"); + + worksheet.addNewSheetData(); + + CTPageMargins ctMargins = worksheet.addNewPageMargins(); + ctMargins.setBottom(0.75); + ctMargins.setFooter(0.3); + ctMargins.setHeader(0.3); + ctMargins.setLeft(0.7); + ctMargins.setRight(0.7); + ctMargins.setTop(0.75); + + return worksheet; + } + + public List<Drawing> getDrawings() + { + return drawings; + } + + public List<Control> getControls() + { + return controls; + } + + /** + * Provide access to the underlying XML bean + * + * @return the underlying CTWorksheet bean + */ + public CTWorksheet getWorksheet() { + return this.worksheet; + } + + public ColumnHelper getColumnHelper() { + return columnHelper; + } + + protected void initRows(CTWorksheet worksheet) { + this.rows = new LinkedList<Row>(); + for (CTRow row : worksheet.getSheetData().getRowArray()) { + this.rows.add(new XSSFRow(row, this)); + } + } + + protected void initColumns(CTWorksheet worksheet) { + columnHelper = new ColumnHelper(worksheet); + } + + protected void initHyperlinks(PackageRelationshipCollection hyperRels) { + if(worksheet.getHyperlinks() == null) return; + + // Turn each one into a XSSFHyperlink + for(CTHyperlink hyperlink : worksheet.getHyperlinks().getHyperlinkArray()) { + PackageRelationship hyperRel = null; + if(hyperlink.getId() != null) { + hyperRel = hyperRels.getRelationshipByID(hyperlink.getId()); + } + + hyperlinks.add( + new XSSFHyperlink(hyperlink, hyperRel) + ); + } + } + + protected CTSheet getSheet() { + return this.sheet; + } + public int addMergedRegion(CellRangeAddress cra) { + Region r = new Region(cra.getFirstRow(), (short)cra.getFirstColumn(), + cra.getLastRow(), (short)cra.getLastColumn()); + return addMergedRegion(r); + } + + + public int addMergedRegion(Region region) { + addNewMergeCell(region); + return ctMergeCells.sizeOfMergeCellArray(); + } + + /** + * Adjusts the column width to fit the contents. + * + * This process can be relatively slow on large sheets, so this should + * normally only be called once per column, at the end of your + * processing. + * + * @param column the column index + */ public void autoSizeColumn(short column) { - autoSizeColumn(column, false); - } - - /** - * Adjusts the column width to fit the contents. - * - * This process can be relatively slow on large sheets, so this should - * normally only be called once per column, at the end of your - * processing. - * - * You can specify whether the content of merged cells should be considered or ignored. - * Default is to ignore merged cells. - * - * @param column the column index - * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column - */ - public void autoSizeColumn(short column, boolean useMergedCells) { - //TODO: - columnHelper.setColBestFit(column, true); - } - - public Patriarch createDrawingPatriarch() { - // TODO Auto-generated method stub - return null; - } - - /** - * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. - * @param colSplit Horizonatal position of split. - * @param rowSplit Vertical position of split. - * @param topRow Top row visible in bottom pane - * @param leftmostColumn Left column visible in right pane. - */ - public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) { - this.createFreezePane(colSplit, rowSplit); - this.showInPane((short)topRow, (short)leftmostColumn); - } - - /** - * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. - * @param colSplit Horizonatal position of split. - * @param rowSplit Vertical position of split. - */ - public void createFreezePane(int colSplit, int rowSplit) { - getPane().setXSplit(colSplit); - getPane().setYSplit(rowSplit); - // make bottomRight default active pane - getPane().setActivePane(STPane.BOTTOM_RIGHT); - } - - /** - * Creates a new comment for this sheet. You still - * need to assign it to a cell though - */ - public XSSFComment createComment() { - return (XSSFComment)getComments().addComment(); - } - - protected XSSFRow addRow(int index, int rownum) { - CTRow row = this.worksheet.getSheetData().insertNewRow(index); - XSSFRow xrow = new XSSFRow(row, this); - xrow.setRowNum(rownum); - return xrow; - } - - /** - * Create a new row within the sheet and return the high level representation - * - * @param rownum row number - * @return High level {@link XSSFRow} object representing a row in the sheet - * @see #removeRow(org.apache.poi.ss.usermodel.Row) - */ - public XSSFRow createRow(int rownum) { - int index = 0; - for (Row r : this.rows) { - if (r.getRowNum() == rownum) { - // Replace r with new row - XSSFRow xrow = addRow(index, rownum); - rows.set(index, xrow); - return xrow; - } - if (r.getRowNum() > rownum) { - XSSFRow xrow = addRow(index, rownum); - rows.add(index, xrow); - return xrow; - } - ++index; - } - XSSFRow xrow = addRow(index, rownum); - rows.add(xrow); - return xrow; - } - - /** - * Creates a split pane. Any existing freezepane or split pane is overwritten. - * @param xSplitPos Horizonatal position of split (in 1/20th of a point). - * @param ySplitPos Vertical position of split (in 1/20th of a point). - * @param topRow Top row visible in bottom pane - * @param leftmostColumn Left column visible in right pane. - * @param activePane Active pane. One of: PANE_LOWER_RIGHT, - * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT - * @see #PANE_LOWER_LEFT - * @see #PANE_LOWER_RIGHT - * @see #PANE_UPPER_LEFT - * @see #PANE_UPPER_RIGHT - */ - public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) { - createFreezePane(xSplitPos, ySplitPos, leftmostColumn, topRow); - getPane().setActivePane(STPane.Enum.forInt(activePane)); - } - - public boolean getAlternateExpression() { - // TODO Auto-generated method stub - return false; - } - - public boolean getAlternateFormula() { - // TODO Auto-generated method stub - return false; - } - - public XSSFComment getCellComment(int row, int column) { - return (XSSFComment)getComments().findCellComment(row, column); - } - - public XSSFHyperlink getHyperlink(int row, int column) { - String ref = new CellReference(row, column).formatAsString(); - for(XSSFHyperlink hyperlink : hyperlinks) { - if(hyperlink.getCellRef().equals(ref)) { - return hyperlink; - } - } - return null; - } - - /** - * Vertical page break information used for print layout view, page layout view, drawing print breaks - * in normal view, and for printing the worksheet. - * - * @return column indexes of all the vertical page breaks, never <code>null</code> - */ - public int[] getColumnBreaks() { - if (!worksheet.isSetColBreaks() || worksheet.getColBreaks().sizeOfBrkArray() == 0) { - return new int[0]; - } - - CTBreak[] brkArray = worksheet.getColBreaks().getBrkArray(); - int[] breaks = new int[brkArray.length]; - for (int i = 0 ; i < brkArray.length ; i++) { - CTBreak brk = brkArray[i]; - breaks[i] = (int)brk.getId(); - } - return breaks; - } - - protected CTPageBreak getSheetTypeColumnBreaks() { - if (worksheet.getColBreaks() == null) { - worksheet.setColBreaks(CTPageBreak.Factory.newInstance()); - } - return worksheet.getColBreaks(); - } - - public int getColumnWidth(int columnIndex) { - return (int) columnHelper.getColumn(columnIndex, false).getWidth(); - } - public short getColumnWidth(short column) { - return (short) getColumnWidth(column & 0xFFFF); - } - - public int getDefaultColumnWidth() { - return (int)getSheetTypeSheetFormatPr().getDefaultColWidth(); - } - - public short getDefaultRowHeight() { - return (short) (getSheetTypeSheetFormatPr().getDefaultRowHeight() * 20); - } - - protected CTSheetFormatPr getSheetTypeSheetFormatPr() { - if (worksheet.getSheetFormatPr() == null) { - worksheet.setSheetFormatPr(CTSheetFormatPr.Factory.newInstance()); - } - return worksheet.getSheetFormatPr(); - } - - public float getDefaultRowHeightInPoints() { - return (short) getSheetTypeSheetFormatPr().getDefaultRowHeight(); - } - - public boolean getDialog() { - if (dialogsheet != null) { - return true; - } - return false; - } - - public boolean getDisplayGuts() { - // TODO Auto-generated method stub - return false; - } - - /** - * Gets the first row on the sheet - * - * @return the number of the first logical row on the sheet, zero based - */ - public int getFirstRowNum() { - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - Row row = it.next(); - if (row != null) { - return row.getRowNum(); - } - } - return -1; - } - - /** - * Flag indicating whether the Fit to Page print option is enabled. - * - * @return <code>true</code> - */ - public boolean getFitToPage() { - CTSheetPr sheetPr = getSheetTypeSheetPr(); - CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ? - CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr(); - return psSetup.getFitToPage(); - } - - protected CTSheetPr getSheetTypeSheetPr() { - if (worksheet.getSheetPr() == null) { - worksheet.setSheetPr(CTSheetPr.Factory.newInstance()); - } - return worksheet.getSheetPr(); - } - - protected CTHeaderFooter getSheetTypeHeaderFooter() { - if (worksheet.getHeaderFooter() == null) { - worksheet.setHeaderFooter(CTHeaderFooter.Factory.newInstance()); - } - return worksheet.getHeaderFooter(); - } - - - - /** - * Returns the default footer for the sheet, - * creating one as needed. - * You may also want to look at - * {@link #getFirstFooter()}, - * {@link #getOddFooter()} and - * {@link #getEvenFooter()} - */ - public Footer getFooter() { - // The default footer is an odd footer - return getOddFooter(); - } - - /** - * Returns the default header for the sheet, - * creating one as needed. - * You may also want to look at - * {@link #getFirstHeader()}, - * {@link #getOddHeader()} and - * {@link #getEvenHeader()} - */ - public Header getHeader() { - // The default header is an odd header - return getOddHeader(); - } - - /** - * Returns the odd footer. Used on all pages unless - * other footers also present, when used on only - * odd pages. - */ - public Footer getOddFooter() { - return new XSSFOddFooter(getSheetTypeHeaderFooter()); - } - /** - * Returns the even footer. Not there by default, but - * when set, used on even pages. - */ - public Footer getEvenFooter() { - return new XSSFEvenFooter(getSheetTypeHeaderFooter()); - } - /** - * Returns the first page footer. Not there by - * default, but when set, used on the first page. - */ - public Footer getFirstFooter() { - return new XSSFFirstFooter(getSheetTypeHeaderFooter()); - } - - /** - * Returns the odd header. Used on all pages unless - * other headers also present, when used on only - * odd pages. - */ - public Header getOddHeader() { - return new XSSFOddHeader(getSheetTypeHeaderFooter()); - } - /** - * Returns the even header. Not there by default, but - * when set, used on even pages. - */ - public Header getEvenHeader() { - return new XSSFEvenHeader(getSheetTypeHeaderFooter() + autoSizeColumn(column, false); + } + + /** + * Adjusts the column width to fit the contents. + * + * This process can be relatively slow on large sheets, so this should + * normally only be called once per column, at the end of your + * processing. + * + * You can specify whether the content of merged cells should be considered or ignored. + * Default is to ignore merged cells. + * + * @param column the column index + * @param useMergedCells whether to use the contents of merged cells when calculating the width of the column + */ + public void autoSizeColumn(short column, boolean useMergedCells) { + //TODO: + columnHelper.setColBestFit(column, true); + } + + public Patriarch createDrawingPatriarch() { + // TODO Auto-generated method stub + return null; + } + + /** + * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. + * @param colSplit Horizonatal position of split. + * @param rowSplit Vertical position of split. + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + */ + public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow) { + this.createFreezePane(colSplit, rowSplit); + this.showInPane((short)topRow, (short)leftmostColumn); + } + + /** + * Creates a split (freezepane). Any existing freezepane or split pane is overwritten. + * @param colSplit Horizonatal position of split. + * @param rowSplit Vertical position of split. + */ + public void createFreezePane(int colSplit, int rowSplit) { + getPane().setXSplit(colSplit); + getPane().setYSplit(rowSplit); + // make bottomRight default active pane + getPane().setActivePane(STPane.BOTTOM_RIGHT); + } + + /** + * Creates a new comment for this sheet. You still + * need to assign it to a cell though + */ + public XSSFComment createComment() { + return (XSSFComment)getComments().addComment(); + } + + protected XSSFRow addRow(int index, int rownum) { + CTRow row = this.worksheet.getSheetData().insertNewRow(index); + XSSFRow xrow = new XSSFRow(row, this); + xrow.setRowNum(rownum); + return xrow; + } + + /** + * Create a new row within the sheet and return the high level representation + * + * @param rownum row number + * @return High level {@link XSSFRow} object representing a row in the sheet + * @see #removeRow(org.apache.poi.ss.usermodel.Row) + */ + public XSSFRow createRow(int rownum) { + int index = 0; + for (Row r : this.rows) { + if (r.getRowNum() == rownum) { + // Replace r with new row + XSSFRow xrow = addRow(index, rownum); + rows.set(index, xrow); + return xrow; + } + if (r.getRowNum() > rownum) { + XSSFRow xrow = addRow(index, rownum); + rows.add(index, xrow); + return xrow; + } + ++index; + } + XSSFRow xrow = addRow(index, rownum); + rows.add(xrow); + return xrow; + } + + /** + * Creates a split pane. Any existing freezepane or split pane is overwritten. + * @param xSplitPos Horizonatal position of split (in 1/20th of a point). + * @param ySplitPos Vertical position of split (in 1/20th of a point). + * @param topRow Top row visible in bottom pane + * @param leftmostColumn Left column visible in right pane. + * @param activePane Active pane. One of: PANE_LOWER_RIGHT, + * PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT + * @see #PANE_LOWER_LEFT + * @see #PANE_LOWER_RIGHT + * @see #PANE_UPPER_LEFT + * @see #PANE_UPPER_RIGHT + */ + public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane) { + createFreezePane(xSplitPos, ySplitPos, leftmostColumn, topRow); + getPane().setActivePane(STPane.Enum.forInt(activePane)); + } + + public boolean getAlternateExpression() { + // TODO Auto-generated method stub + return false; + } + + public boolean getAlternateFormula() { + // TODO Auto-generated method stub + return false; + } + + public XSSFComment getCellComment(int row, int column) { + if (sheetComments == null) return null; + else return (XSSFComment)getComments().findCellComment(row, column); + } + + public XSSFHyperlink getHyperlink(int row, int column) { + String ref = new CellReference(row, column).formatAsString(); + for(XSSFHyperlink hyperlink : hyperlinks) { + if(hyperlink.getCellRef().equals(ref)) { + return hyperlink; + } + } + return null; + } + + /** + * Vertical page break information used for print layout view, page layout view, drawing print breaks + * in normal view, and for printing the worksheet. + * + * @return column indexes of all the vertical page breaks, never <code>null</code> + */ + public int[] getColumnBreaks() { + if (!worksheet.isSetColBreaks() || worksheet.getColBreaks().sizeOfBrkArray() == 0) { + return new int[0]; + } + + CTBreak[] brkArray = worksheet.getColBreaks().getBrkArray(); + int[] breaks = new int[brkArray.length]; + for (int i = 0 ; i < brkArray.length ; i++) { + CTBreak brk = brkArray[i]; + breaks[i] = (int)brk.getId(); + } + return breaks; + } + + protected CTPageBreak getSheetTypeColumnBreaks() { + if (worksheet.getColBreaks() == null) { + worksheet.setColBreaks(CTPageBreak.Factory.newInstance()); + } + return worksheet.getColBreaks(); + } + + public int getColumnWidth(int columnIndex) { + return (int) columnHelper.getColumn(columnIndex, false).getWidth(); + } + public short getColumnWidth(short column) { + return (short) getColumnWidth(column & 0xFFFF); + } + + public int getDefaultColumnWidth() { + return (int)getSheetTypeSheetFormatPr().getDefaultColWidth(); + } + + public short getDefaultRowHeight() { + return (short) (getSheetTypeSheetFormatPr().getDefaultRowHeight() * 20); + } + + protected CTSheetFormatPr getSheetTypeSheetFormatPr() { + if (worksheet.getSheetFormatPr() == null) { + worksheet.setSheetFormatPr(CTSheetFormatPr.Factory.newInstance()); + } + return worksheet.getSheetFormatPr(); + } + + public float getDefaultRowHeightInPoints() { + return (short) getSheetTypeSheetFormatPr().getDefaultRowHeight(); + } + + public boolean getDialog() { + if (dialogsheet != null) { + return true; + } + return false; + } + + public boolean getDisplayGuts() { + // TODO Auto-generated method stub + return false; + } + + /** + * Gets the first row on the sheet + * + * @return the number of the first logical row on the sheet, zero based + */ + public int getFirstRowNum() { + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + Row row = it.next(); + if (row != null) { + return row.getRowNum(); + } + } + return -1; + } + + /** + * Flag indicating whether the Fit to Page print option is enabled. + * + * @return <code>true</code> + */ + public boolean getFitToPage() { + CTSheetPr sheetPr = getSheetTypeSheetPr(); + CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ? + CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr(); + return psSetup.getFitToPage(); + } + + protected CTSheetPr getSheetTypeSheetPr() { + if (worksheet.getSheetPr() == null) { + worksheet.setSheetPr(CTSheetPr.Factory.newInstance()); + } + return worksheet.getSheetPr(); + } + + protected CTHeaderFooter getSheetTypeHeaderFooter() { + if (worksheet.getHeaderFooter() == null) { + worksheet.setHeaderFooter(CTHeaderFooter.Factory.newInstance()); + } + return worksheet.getHeaderFooter(); + } + + + + /** + * Returns the default footer for the sheet, + * creating one as needed. + * You may also want to look at + * {@link #getFirstFooter()}, + * {@link #getOddFooter()} and + * {@link #getEvenFooter()} + */ + public Footer getFooter() { + // The default footer is an odd footer + return getOddFooter(); + } + + /** + * Returns the default header for the sheet, + * creating one as needed. + * You may also want to look at + * {@link #getFirstHeader()}, + * {@link #getOddHeader()} and + * {@link #getEvenHeader()} + */ + public Header getHeader() { + // The default header is an odd header + return getOddHeader(); + } + + /** + * Returns the odd footer. Used on all pages unless + * other footers also present, when used on only + * odd pages. + */ + public Footer getOddFooter() { + return new XSSFOddFooter(getSheetTypeHeaderFooter()); + } + /** + * Returns the even footer. Not there by default, but + * when set, used on even pages. + */ + public Footer getEvenFooter() { + return new XSSFEvenFooter(getSheetTypeHeaderFooter()); + } + /** + * Returns the first page footer. Not there by + * default, but when set, used on the first page. + */ + public Footer getFirstFooter() { + return new XSSFFirstFooter(getSheetTypeHeaderFooter()); + } + + /** + * Returns the odd header. Used on all pages unless + * other headers also present, when used on only + * odd pages. + */ + public Header getOddHeader() { + return new XSSFOddHeader(getSheetTypeHeaderFooter()); + } + /** + * Returns the even header. Not there by default, but + * when set, used on even pages. + */ + public Header getEvenHeader() { + return new XSSFEvenHeader(getSheetTypeHeaderFooter() ); - } - /** - * Returns the first page header. Not there by - * default, but when set, used on the first page. - */ - public Header getFirstHeader() { - return new XSSFFirstHeader(getSheetTypeHeaderFooter()); - } - - - public boolean getHorizontallyCenter() { - return getSheetTypePrintOptions().getHorizontalCentered(); - } - - protected CTPrintOptions getSheetTypePrintOptions() { - if (worksheet.getPrintOptions() == null) { - worksheet.setPrintOptions(CTPrintOptions.Factory.newInstance()); - } - return worksheet.getPrintOptions(); - } - - public int getLastRowNum() { - int lastRowNum = -1; - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - Row row = it.next(); - if (row != null) { - lastRowNum = row.getRowNum(); - } - } - return lastRowNum; - } - - public short getLeftCol() { - String cellRef = worksheet.getSheetViews().getSheetViewArray(0).getTopLeftCell(); - CellReference cellReference = new CellReference(cellRef); - return (short)cellReference.getCol(); - } - - public double getMargin(short margin) { - CTPageMargins pageMargins = getSheetTypePageMargins(); - switch (margin) { - case LeftMargin: - return pageMargins.getLeft(); - case RightMargin: - return pageMargins.getRight(); - case TopMargin: - return pageMargins.getTop(); - case BottomMargin: - return pageMargins.getBottom(); - case HeaderMargin: - return pageMargins.getHeader(); - case FooterMargin: - return pageMargins.getFooter(); - default : - throw new RuntimeException( "Unknown margin constant: " + margin ); - } - } - - protected CTPageMargins getSheetTypePageMargins() { - if (worksheet.getPageMargins() == null) { - worksheet.setPageMargins(CTPageMargins.Factory.newInstance()); - } - return worksheet.getPageMargins(); - } - - public Region getMergedRegionAt(int index) { - CTMergeCell ctMergeCell = getMergedCells().getMergeCellArray(index); - return new Region(ctMergeCell.getRef()); - } - - public int getNumMergedRegions() { - return getMergedCells().sizeOfMergeCellArray(); - } - - public int getNumHyperlinks() { - return hyperlinks.size(); - } - - public boolean getObjectProtect() { - // TODO Auto-generated method stub - return false; - } - - public PaneInformation getPaneInformation() { - // TODO Auto-generated method stub - return null; - } - - public short getPassword() { - // TODO Auto-generated method stub - return 0; - } - - public int getPhysicalNumberOfRows() { - int counter = 0; - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - if (it.next() != null) { - counter++; - } - } - return counter; - } - - public PrintSetup getPrintSetup() { - // TODO Auto-generated method stub - return null; - } - - public boolean getProtect() { - // TODO Auto-generated method stub - return false; - } - - /** - * Returns the logical row ( 0-based). If you ask for a row that is not - * defined you get a null. This is to say row 4 represents the fifth row on a sheet. - * - * @param rownum row to get - * @return <code>XSSFRow</code> representing the rownumber or <code>null</code> if its not defined on the sheet - */ - public XSSFRow getRow(int rownum) { - //TODO current implemenation is expensive, it should take O(1), not O(N) - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - Row row = it.next(); - if (row.getRowNum() == rownum) { - return (XSSFRow)row; - } - } - return null; - } - - /** - * Horizontal page break information used for print layout view, page layout view, drawing print breaks in normal - * view, and for printing the worksheet. - * - * @return row indexes of all the horizontal page breaks, never <code>null</code> - */ - public int[] getRowBreaks() { - if (!worksheet.isSetRowBreaks() || worksheet.getRowBreaks().sizeOfBrkArray() == 0) { - return new int[0]; - } - - CTBreak[] brkArray = worksheet.getRowBreaks().getBrkArray(); - int[] breaks = new int[brkArray.length]; - for (int i = 0 ; i < brkArray.length ; i++) { - CTBreak brk = brkArray[i]; - breaks[i] = (int)brk.getId(); - } - return breaks; - } - - /** - * Flag indicating whether summary rows appear below detail in an outline, when applying an outline. - * - * <p> - * When true a summary row is inserted below the detailed data being summarized and a - * new outline level is established on that row. - * </p> - * <p> - * When false a summary row is inserted above the detailed data being summarized and a new outline level - * is established on that row. - * </p> - * @return <code>true</code> if row summaries appear below detail in the outline - */ - public boolean getRowSumsBelow() { - CTSheetPr sheetPr = worksheet.getSheetPr(); - CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr()) - ? sheetPr.getOutlinePr() : CTOutlinePr.Factory.newInstance(); - return outlinePr.getSummaryBelow(); - } - - /** - * Flag indicating whether summary rows appear below detail in an outline, when applying an outline. - * - * <p> - * When true a summary row is inserted below the detailed data being summarized and a - * new outline level is established on that row. - * </p> - * <p> - * When false a summary row is inserted above the detailed data being summarized and a new outline level - * is established on that row. - * </p> - * @param value <code>true</code> if row summaries appear below detail in the outline - */ - public void setRowSumsBelow(boolean value) { - ensureOutlinePr().setSummaryBelow(value); - } - - /** - * Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline. - * - * <p> - * When true a summary column is inserted to the right of the detailed data being summarized - * and a new outline level is established on that column. - * </p> - * <p> - * When false a summary column is inserted to the left of the detailed data being - * summarized and a new outline level is established on that column. - * </p> - * @return <code>true</code> if col summaries appear right of the detail in the outline - */ - public boolean getRowSumsRight() { - CTSheetPr sheetPr = worksheet.getSheetPr(); - CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr()) - ? sheetPr.getOutlinePr() : CTOutlinePr.Factory.newInstance(); - return outlinePr.getSummaryRight(); - } - - /** - * Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline. - * - * <p> - * When true a summary column is inserted to the right of the detailed data being summarized - * and a new outline level is established on that column. - * </p> - * <p> - * When false a summary column is inserted to the left of the detailed data being - * summarized and a new outline level is established on that column. - * </p> - * @param value <code>true</code> if col summaries appear right of the detail in the outline - */ - public void setRowSumsRight(boolean value) { - ensureOutlinePr().setSummaryRight(value); - } - - - /** - * Ensure CTWorksheet.CTSheetPr.CTOutlinePr - */ - private CTOutlinePr ensureOutlinePr(){ - CTSheetPr sheetPr = worksheet.isSetSheetPr() ? worksheet.getSheetPr() : worksheet.addNewSheetPr(); - CTOutlinePr outlinePr = sheetPr.isSetOutlinePr() ? sheetPr.getOutlinePr() : sheetPr.addNewOutlinePr(); - return outlinePr; - } - - /** - * A flag indicating whether scenarios are locked when the sheet is protected. - * - * @return true => protection enabled; false => protection disabled - */ - public boolean getScenarioProtect() { - return getSheetTypeProtection().getScenarios(); - } - - protected CTSheetProtection getSheetTypeProtection() { - if (worksheet.getSheetProtection() == null) { - worksheet.setSheetProtection(CTSheetProtection.Factory.newInstance()); - } - return worksheet.getSheetProtection(); - } - - /** - * The top row in the visible view when the sheet is - * first viewed after opening it in a viewer - * - * @return integer indicating the rownum (0 based) of the top row - */ - public short getTopRow() { - String cellRef = getSheetTypeSheetView().getTopLeftCell(); - CellReference cellReference = new CellReference(cellRef); - return (short) cellReference.getRow(); - } - - /** - * Determine whether printed output for this sheet will be vertically centered. - * - * @return whether printed output for this sheet will be vertically centered. - */ - public boolean getVerticallyCenter() { - return getSheetTypePrintOptions().getVerticalCentered(); - } - - /** - * Group between (0 based) columns - */ - public void groupColumn(short fromColumn, short toColumn) { - groupColumn1Based(fromColumn+1, toColumn+1); - } - private void groupColumn1Based(int fromColumn, int toColumn) { - CTCols ctCols=worksheet.getColsArray(0); - CTCol ctCol=CTCol.Factory.newInstance(); - ctCol.setMin(fromColumn); - ctCol.setMax(toColumn); - this.columnHelper.addCleanColIntoCols(ctCols, ctCol); - for(int index=fromColumn;index<=toColumn;index++){ - CTCol col=columnHelper.getColumn1Based(index, false); - //col must exist - short outlineLevel=col.getOutlineLevel(); - col.setOutlineLevel((short)(outlineLevel+1)); - index=(int)col.getMax(); - } - worksheet.setColsArray(0,ctCols); - setSheetFormatPrOutlineLevelCol(); - } - - public void groupRow(int fromRow, int toRow) { - for(int i=fromRow;i<=toRow;i++){ - XSSFRow xrow = getRow(i-1); - if(xrow == null){//create a new Row - xrow = createRow(i-1); - } - CTRow ctrow=xrow.getCTRow(); - short outlineLevel=ctrow.getOutlineLevel(); - ctrow.setOutlineLevel((short)(outlineLevel+1)); - } - setSheetFormatPrOutlineLevelRow(); - } + } + /** + * Returns the first page header. Not there by + * default, but when set, used on the first page. + */ + public Header getFirstHeader() { + return new XSSFFirstHeader(getSheetTypeHeaderFooter()); + } + + + public boolean getHorizontallyCenter() { + return getSheetTypePrintOptions().getHorizontalCentered(); + } + + protected CTPrintOptions getSheetTypePrintOptions() { + if (worksheet.getPrintOptions() == null) { + worksheet.setPrintOptions(CTPrintOptions.Factory.newInstance()); + } + return worksheet.getPrintOptions(); + } + + public int getLastRowNum() { + int lastRowNum = -1; + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + Row row = it.next(); + if (row != null) { + lastRowNum = row.getRowNum(); + } + } + return lastRowNum; + } + + public short getLeftCol() { + String cellRef = worksheet.getSheetViews().getSheetViewArray(0).getTopLeftCell(); + CellReference cellReference = new CellReference(cellRef); + return (short)cellReference.getCol(); + } + + public double getMargin(short margin) { + CTPageMargins pageMargins = getSheetTypePageMargins(); + switch (margin) { + case LeftMargin: + return pageMargins.getLeft(); + case RightMargin: + return pageMargins.getRight(); + case TopMargin: + return pageMargins.getTop(); + case BottomMargin: + return pageMargins.getBottom(); + case HeaderMargin: + return pageMargins.getHeader(); + case FooterMargin: + return pageMargins.getFooter(); + default : + throw new POIXMLException( "Unknown margin constant: " + margin ); + } + } + + protected CTPageMargins getSheetTypePageMargins() { + if (worksheet.getPageMargins() == null) { + worksheet.setPageMargins(CTPageMargins.Factory.newInstance()); + } + return worksheet.getPageMargins(); + } + + public Region getMergedRegionAt(int index) { + CTMergeCell ctMergeCell = getMergedCells().getMergeCellArray(index); + return new Region(ctMergeCell.getRef()); + } + + public int getNumMergedRegions() { + return getMergedCells().sizeOfMergeCellArray(); + } + + public int getNumHyperlinks() { + return hyperlinks.size(); + } + + public boolean getObjectProtect() { + // TODO Auto-generated method stub + return false; + } + + public PaneInformation getPaneInformation() { + // TODO Auto-generated method stub + return null; + } + + public short getPassword() { + // TODO Auto-generated method stub + return 0; + } + + public int getPhysicalNumberOfRows() { + int counter = 0; + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + if (it.next() != null) { + counter++; + } + } + return counter; + } + + public PrintSetup getPrintSetup() { + // TODO Auto-generated method stub + return null; + } + + public boolean getProtect() { + // TODO Auto-generated method stub + return false; + } + + /** + * Returns the logical row ( 0-based). If you ask for a row that is not + * defined you get a null. This is to say row 4 represents the fifth row on a sheet. + * + * @param rownum row to get + * @return <code>XSSFRow</code> representing the rownumber or <code>null</code> if its not defined on the sheet + */ + public XSSFRow getRow(int rownum) { + //TODO current implemenation is expensive, it should take O(1), not O(N) + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + Row row = it.next(); + if (row.getRowNum() == rownum) { + return (XSSFRow)row; + } + } + return null; + } + + /** + * Horizontal page break information used for print layout view, page layout view, drawing print breaks in normal + * view, and for printing the worksheet. + * + * @return row indexes of all the horizontal page breaks, never <code>null</code> + */ + public int[] getRowBreaks() { + if (!worksheet.isSetRowBreaks() || worksheet.getRowBreaks().sizeOfBrkArray() == 0) { + return new int[0]; + } + + CTBreak[] brkArray = worksheet.getRowBreaks().getBrkArray(); + int[] breaks = new int[brkArray.length]; + for (int i = 0 ; i < brkArray.length ; i++) { + CTBreak brk = brkArray[i]; + breaks[i] = (int)brk.getId(); + } + return breaks; + } + + /** + * Flag indicating whether summary rows appear below detail in an outline, when applying an outline. + * + * <p> + * When true a summary row is inserted below the detailed data being summarized and a + * new outline level is established on that row. + * </p> + * <p> + * When false a summary row is inserted above the detailed data being summarized and a new outline level + * is established on that row. + * </p> + * @return <code>true</code> if row summaries appear below detail in the outline + */ + public boolean getRowSumsBelow() { + CTSheetPr sheetPr = worksheet.getSheetPr(); + CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr()) + ? sheetPr.getOutlinePr() : CTOutlinePr.Factory.newInstance(); + return outlinePr.getSummaryBelow(); + } + + /** + * Flag indicating whether summary rows appear below detail in an outline, when applying an outline. + * + * <p> + * When true a summary row is inserted below the detailed data being summarized and a + * new outline level is established on that row. + * </p> + * <p> + * When false a summary row is inserted above the detailed data being summarized and a new outline level + * is established on that row. + * </p> + * @param value <code>true</code> if row summaries appear below detail in the outline + */ + public void setRowSumsBelow(boolean value) { + ensureOutlinePr().setSummaryBelow(value); + } + + /** + * Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline. + * + * <p> + * When true a summary column is inserted to the right of the detailed data being summarized + * and a new outline level is established on that column. + * </p> + * <p> + * When false a summary column is inserted to the left of the detailed data being + * summarized and a new outline level is established on that column. + * </p> + * @return <code>true</code> if col summaries appear right of the detail in the outline + */ + public boolean getRowSumsRight() { + CTSheetPr sheetPr = worksheet.getSheetPr(); + CTOutlinePr outlinePr = (sheetPr != null && sheetPr.isSetOutlinePr()) + ? sheetPr.getOutlinePr() : CTOutlinePr.Factory.newInstance(); + return outlinePr.getSummaryRight(); + } + + /** + * Flag indicating whether summary columns appear to the right of detail in an outline, when applying an outline. + * + * <p> + * When true a summary column is inserted to the right of the detailed data being summarized + * and a new outline level is established on that column. + * </p> + * <p> + * When false a summary column is inserted to the left of the detailed data being + * summarized and a new outline level is established on that column. + * </p> + * @param value <code>true</code> if col summaries appear right of the detail in the outline + */ + public void setRowSumsRight(boolean value) { + ensureOutlinePr().setSummaryRight(value); + } + + + /** + * Ensure CTWorksheet.CTSheetPr.CTOutlinePr + */ + private CTOutlinePr ensureOutlinePr(){ + CTSheetPr sheetPr = worksheet.isSetSheetPr() ? worksheet.getSheetPr() : worksheet.addNewSheetPr(); + CTOutlinePr outlinePr = sheetPr.isSetOutlinePr() ? sheetPr.getOutlinePr() : sheetPr.addNewOutlinePr(); + return outlinePr; + } + + /** + * A flag indicating whether scenarios are locked when the sheet is protected. + * + * @return true => protection enabled; false => protection disabled + */ + public boolean getScenarioProtect() { + return getSheetTypeProtection().getScenarios(); + } + + protected CTSheetProtection getSheetTypeProtection() { + if (worksheet.getSheetProtection() == null) { + worksheet.setSheetProtection(CTSheetProtection.Factory.newInstance()); + } + return worksheet.getSheetProtection(); + } + + /** + * The top row in the visible view when the sheet is + * first viewed after opening it in a viewer + * + * @return integer indicating the rownum (0 based) of the top row + */ + public short getTopRow() { + String cellRef = getSheetTypeSheetView().getTopLeftCell(); + CellReference cellReference = new CellReference(cellRef); + return (short) cellReference.getRow(); + } + + /** + * Determine whether printed output for this sheet will be vertically centered. + * + * @return whether printed output for this sheet will be vertically centered. + */ + public boolean getVerticallyCenter() { + return getSheetTypePrintOptions().getVerticalCentered(); + } + + /** + * Group between (0 based) columns + */ + public void groupColumn(short fromColumn, short toColumn) { + groupColumn1Based(fromColumn+1, toColumn+1); + } + private void groupColumn1Based(int fromColumn, int toColumn) { + CTCols ctCols=worksheet.getColsArray(0); + CTCol ctCol=CTCol.Factory.newInstance(); + ctCol.setMin(fromColumn); + ctCol.setMax(toColumn); + this.columnHelper.addCleanColIntoCols(ctCols, ctCol); + for(int index=fromColumn;index<=toColumn;index++){ + CTCol col=columnHelper.getColumn1Based(index, false); + //col must exist + short outlineLevel=col.getOutlineLevel(); + col.setOutlineLevel((short)(outlineLevel+1)); + index=(int)col.getMax(); + } + worksheet.setColsArray(0,ctCols); + setSheetFormatPrOutlineLevelCol(); + } + + public void groupRow(int fromRow, int toRow) { + for(int i=fromRow;i<=toRow;i++){ + XSSFRow xrow = getRow(i-1); + if(xrow == null){//create a new Row + xrow = createRow(i-1); + } + CTRow ctrow=xrow.getCTRow(); + short outlineLevel=ctrow.getOutlineLevel(); + ctrow.setOutlineLevel((short)(outlineLevel+1)); + } + setSheetFormatPrOutlineLevelRow(); + } private short getMaxOutlineLevelRows(){ - short outlineLevel=0; - for(Row r:rows){ - XSSFRow xrow=(XSSFRow)r; - outlineLevel=xrow.getCTRow().getOutlineLevel()>outlineLevel? xrow.getCTRow().getOutlineLevel(): outlineLevel; - } - return outlineLevel; - } - - - private short getMaxOutlineLevelCols(){ - CTCols ctCols=worksheet.getColsArray(0); - CTCol[]colArray=ctCols.getColArray(); - short outlineLevel=0; - for(CTCol col: colArray){ - outlineLevel=col.getOutlineLevel()>outlineLevel? col.getOutlineLevel(): outlineLevel; - } - return outlineLevel; - } - - /** - * Determines if there is a page break at the indicated column - */ - public boolean isColumnBroken(short column) { - int[] colBreaks = getColumnBreaks(); - for (int i = 0 ; i < colBreaks.length ; i++) { - if (colBreaks[i] == column) { - return true; - } - } - return false; - } - - public boolean isColumnHidden(int columnIndex) { - return columnHelper.getColumn(columnIndex, false).getHidden(); - } - public boolean isColumnHidden(short column) { - return isColumnHidden(column & 0xFFFF); - } - - public boolean isDisplayFormulas() { - return getSheetTypeSheetView().getShowFormulas(); - } - - public boolean isDisplayGridlines() { - return getSheetTypeSheetView().getShowGridLines(); - } - - public boolean isDisplayRowColHeadings() { - return getSheetTypeSheetView().getShowRowColHeaders(); - } - - public boolean isGridsPrinted() { - return isPrintGridlines(); - } - - public boolean isPrintGridlines() { - return getSheetTypePrintOptions().getGridLines(); - } - - /** - * Tests if there is a page break at the indicated row - * - * @param row index of the row to test - * @return <code>true</code> if there is a page break at the indicated row - */ - public boolean isRowBroken(int row) { - int[] rowBreaks = getRowBreaks(); - for (int i = 0 ; i < rowBreaks.length ; i++) { - if (rowBreaks[i] == row) { - return true; - } - } - return false; - } - - /** - * Sets a page break at the indicated row - */ - public void setRowBreak(int row) { - CTPageBreak pgBreak = worksheet.isSetRowBreaks() ? worksheet.getRowBreaks() : worksheet.addNewRowBreaks(); - if (! isRowBroken(row)) { - CTBreak brk = pgBreak.addNewBrk(); - brk.setId(row); - } - } - - /** - * Removes a page break at the indicated column - */ - public void removeColumnBreak(short column) { - CTBreak[] brkArray = getSheetTypeColumnBreaks().getBrkArray(); - for (int i = 0 ; i < brkArray.length ; i++) { - if (brkArray[i].getId() == column) { - getSheetTypeColumnBreaks().removeBrk(i); - } - } - } - - public void protectSheet(String password) { - // TODO Auto-generated method stub - - } - - public void removeMergedRegion(int index) { - CTMergeCell[] mergeCellsArray = new CTMergeCell[getMergedCells().sizeOfMergeCellArray() - 1]; - for (int i = 0 ; i < getMergedCells().sizeOfMergeCellArray() ; i++) { - if (i < index) { - mergeCellsArray[i] = getMergedCells().getMergeCellArray(i); - } - else if (i > index) { - mergeCellsArray[i - 1] = getMergedCells().getMergeCellArray(i); - } - } - getMergedCells().setMergeCellArray(mergeCellsArray); - } - - public void removeRow(Row row) { - int counter = 0; - int rowNum=row.getRowNum(); - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - Row r = it.next(); - if (r.getRowNum() == rowNum) { - it.remove(); - worksheet.getSheetData().removeRow(counter); - } - counter++; - } - } - - /** - * Removes the page break at the indicated row - */ - public void removeRowBreak(int row) { - CTPageBreak pgBreak = worksheet.isSetRowBreaks() ? worksheet.getRowBreaks() : worksheet.addNewRowBreaks(); - CTBreak[] brkArray = pgBreak.getBrkArray(); - for (int i = 0 ; i < brkArray.length ; i++) { - if (brkArray[i].getId() == row) { - pgBreak.removeBrk(i); - } - } - } - - public Iterator<Row> rowIterator() { - return rows.iterator(); - } - - /** - * Alias for {@link #rowIterator()} to - * allow foreach loops - */ - public Iterator<Row> iterator() { - return rowIterator(); - } - - public void setAlternativeExpression(boolean b) { - // TODO Auto-generated method stub - - } - - public void setAlternativeFormula(boolean b) { - // TODO Auto-generated method stub - - } - - /** - * Flag indicating whether the sheet displays Automatic Page Breaks. - * - * @return <code>true</code> if the sheet displays Automatic Page Breaks. - */ - public boolean getAutobreaks() { - CTSheetPr sheetPr = getSheetTypeSheetPr(); - CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ? - CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr(); - return psSetup.getAutoPageBreaks(); - } - - /** - * Flag indicating whether the sheet displays Automatic Page Breaks. - * - * @param value <code>true</code> if the sheet displays Automatic Page Breaks. - */ - public void setAutobreaks(boolean value) { - CTSheetPr sheetPr = getSheetTypeSheetPr(); - CTPageSetUpPr psSetup = sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr(); - psSetup.setAutoPageBreaks(value); - } - - public void setColumnBreak(short column) { - if (! isColumnBroken(column)) { - CTBreak brk = getSheetTypeColumnBreaks().addNewBrk(); - brk.setId(column); - } - } - - public void setColumnGroupCollapsed(short columnNumber, boolean collapsed) { - // TODO Auto-generated method stub - - } - - public void setColumnHidden(int columnIndex, boolean hidden) { - columnHelper.setColHidden(columnIndex, hidden); - } - public void setColumnHidden(short column, boolean hidden) { - setColumnHidden(column & 0xFFFF, hidden); - } - - public void setColumnWidth(int columnIndex, int width) { - columnHelper.setColWidth(columnIndex, width); - } - public void setColumnWidth(short column, short width) { - setColumnWidth(column & 0xFFFF, width & 0xFFFF); - } - - public void setDefaultColumnStyle(short column, CellStyle style) { - columnHelper.setColDefaultStyle(column, style); - } - - public void setDefaultColumnWidth(int width) { - getSheetTypeSheetFormatPr().setDefaultColWidth(width); - } - public void setDefaultColumnWidth(short width) { - setDefaultColumnWidth(width & 0xFFFF); - } - - public void setDefaultRowHeight(short height) { - getSheetTypeSheetFormatPr().setDefaultRowHeight(height / 20); - - } - - public void setDefaultRowHeightInPoints(float height) { - getSheetTypeSheetFormatPr().setDefaultRowHeight(height); - - } - - public void setDialog(boolean b) { - if(b && dialogsheet == null){ - CTDialogsheet dialogSheet = CTDialogsheet.Factory.newInstance(); - dialogsheet = dialogSheet; - }else{ - dialogsheet = null; - } - } - - public void setDisplayFormulas(boolean show) { - getSheetTypeSheetView().setShowFormulas(show); - } - - protected CTSheetView getSheetTypeSheetView() { - if (getDefaultSheetView() == null) { - getSheetTypeSheetViews().setSheetViewArray(0, CTSheetView.Factory.newInstance()); - } - return getDefaultSheetView(); - } - - public void setDisplayGridlines(boolean show) { - getSheetTypeSheetView().setShowGridLines(show); - } - - public void setDisplayGuts(boolean b) { - // TODO Auto-generated method stub - - } - - public void setDisplayRowColHeadings(boolean show) { - getSheetTypeSheetView().setShowRowColHeaders(show); - } - - public void setFitToPage(boolean b) { - getSheetTypePageSetUpPr().setFitToPage(b); - } - - public void setGridsPrinted(boolean value) { - setPrintGridlines(value); - } - - public void setHorizontallyCenter(boolean value) { - getSheetTypePrintOptions().setHorizontalCentered(value); - } - - public void setMargin(short margin, double size) { - CTPageMargins pageMargins = getSheetTypePageMargins(); - switch (margin) { - case LeftMargin: - pageMargins.setLeft(size); - case RightMargin: - pageMargins.setRight(size); - case TopMargin: - pageMargins.setTop(size); - case BottomMargin: - pageMargins.setBottom(size); - case HeaderMargin: - pageMargins.setHeader(size); - case FooterMargin: - pageMargins.setFooter(size); - } - } - - public void setPrintGridlines(boolean newPrintGridlines) { - getSheetTypePrintOptions().setGridLines(newPrintGridlines); - } - - public void setRowGroupCollapsed(int row, boolean collapse) { - // TODO Auto-generated method stub - - } - - public void setVerticallyCenter(boolean value) { - getSheetTypePrintOptions().setVerticalCentered(value); - } - - /** - * Sets the zoom magnication for the sheet. The zoom is expressed as a - * fraction. For example to express a zoom of 75% use 3 for the numerator - * and 4 for the denominator. - * - * @param numerator The numerator for the zoom magnification. - * @param denominator The denominator for the zoom magnification. - * @see #setZoom(int) - */ - public void setZoom(int numerator, int denominator) { - Float result = new Float(numerator)/new Float(denominator)*100; - setZoom(result.intValue()); - } - - /** - * Window zoom magnification for current view representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * Current view can be Normal, Page Layout, or Page Break Preview. - * - * @param scale window zoom magnification - */ - public void setZoom(int scale) { - getSheetTypeSheetView().setZoomScale(scale); - } - - /** - * Zoom magnification to use when in normal view, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * Applies for worksheet sheet type only; zero implies the automatic setting. - * - * @param scale window zoom magnification - */ - public void setZoomNormal(int scale) { - getSheetTypeSheetView().setZoomScaleNormal(scale); - } - - /** - * Zoom magnification to use when in page layout view, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * Applies for worksheet sheet type only; zero implies the automatic setting. - * - * @param scale - */ - public void setZoomPageLayoutView(int scale) { - getSheetTypeSheetView().setZoomScalePageLayoutView(scale); - } - - /** - * Zoom magnification to use when in page break preview, representing percent values. - * Valid values range from 10 to 400. Horizontal & Vertical scale together. - * - * For example: - * <pre> - * 10 - 10% - * 20 - 20% - * … - * 100 - 100% - * … - * 400 - 400% - * </pre> - * - * Applies for worksheet only; zero implies the automatic setting. - * - * @param scale - */ - public void setZoomSheetLayoutView(int scale) { - getSheetTypeSheetView().setZoomScaleSheetLayoutView(scale); - } - - public void shiftRows(int startRow, int endRow, int n) { - shiftRows(startRow, endRow, n, false, false); - } - - public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { - for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { - Row row = it.next(); - if (!copyRowHeight) { - row.setHeight((short)0); - } - if (resetOriginalRowHeight && getDefaultRowHeight() >= 0) { - row.setHeight(getDefaultRowHeight()); - } - if (removeRow(startRow, endRow, n, row.getRowNum())) { - it.remove(); - } - else if (row.getRowNum() >= startRow && row.getRowNum() <= endRow) { - row.setRowNum(row.getRowNum() + n); - } - } - } - - /** - * Location of the top left visible cell Location of the top left visible cell in the bottom right - * pane (when in Left-to-Right mode). - * - * @param toprow the top row to show in desktop window pane - * @param leftcol the left column to show in desktop window pane - */ - public void showInPane(short toprow, short leftcol) { - CellReference cellReference = new CellReference(toprow, leftcol); - String cellRef = cellReference.formatAsString(); - getSheetTypeSheetView().setTopLeftCell(cellRef); - } - - public void ungroupColumn(short fromColumn, short toColumn) { - CTCols cols=worksheet.getColsArray(0); - for(int index=fromColumn;index<=toColumn;index++){ - CTCol col=columnHelper.getColumn(index, false); - if(col!=null){ - short outlineLevel=col.getOutlineLevel(); - col.setOutlineLevel((short)(outlineLevel-1)); - index=(int)col.getMax(); - - if(col.getOutlineLevel()<=0){ - int colIndex=columnHelper.getIndexOfColumn(cols,col); - worksheet.getColsArray(0).removeCol(colIndex); - } - } - } - worksheet.setColsArray(0,cols); - setSheetFormatPrOutlineLevelCol(); - } - - public void ungroupRow(int fromRow, int toRow) { - for(int i=fromRow;i<=toRow;i++){ - XSSFRow xrow=(XSSFRow)getRow(i-1); - if(xrow!=null){ - CTRow ctrow=xrow.getCTRow(); - short outlinelevel=ctrow.getOutlineLevel(); - ctrow.setOutlineLevel((short)(outlinelevel-1)); - //remove a row only if the row has no cell and if the outline level is 0 - if(ctrow.getOutlineLevel()==0 && xrow.getFirstCellNum()==-1){ - removeRow(xrow); - } - } - } - setSheetFormatPrOutlineLevelRow(); - } - - private void setSheetFormatPrOutlineLevelRow(){ - short maxLevelRow=getMaxOutlineLevelRows(); - getSheetTypeSheetFormatPr().setOutlineLevelRow((short)(maxLevelRow)); - } - - private void setSheetFormatPrOutlineLevelCol(){ - short maxLevelCol=getMaxOutlineLevelCols(); - getSheetTypeSheetFormatPr().setOutlineLevelCol((short)(maxLevelCol)); - } - - protected CTSheetViews getSheetTypeSheetViews() { - if (worksheet.getSheetViews() == null) { - worksheet.setSheetViews(CTSheetViews.Factory.newInstance()); - worksheet.getSheetViews().addNewSheetView(); - } - return worksheet.getSheetViews(); - } - - /** - * Returns a flag indicating whether this sheet is selected. - * <p> - * When only 1 sheet is selected and active, this value should be in synch with the activeTab value. - * In case of a conflict, the Start Part setting wins and sets the active sheet tab. - * </p> - * Note: multiple sheets can be selected, but only one sheet can be active at one time. - * - * @return <code>true</code> if this sheet is selected - */ - public boolean isSelected() { - CTSheetView view = getDefaultSheetView(); - return view != null && view.getTabSelected(); - } - - /** - * Sets a flag indicating whether this sheet is selected. - * - * <p> - * When only 1 sheet is selected and active, this value should be in synch with the activeTab value. - * In case of a conflict, the Start Part setting wins and sets the active sheet tab. - * </p> - * Note: multiple sheets can be selected, but only one sheet can be active at one time. - * - * @param value <code>true</code> if this sheet is selected - */ - public void setSelected(boolean value) { - CTSheetViews views = getSheetTypeSheetViews(); - for (CTSheetView view : views.getSheetViewArray()) { - view.setTabSelected(value); - } - } - - /** - * Assign a cell comment to a cell region in this worksheet - * - * @param cellRef cell region - * @param comment the comment to assign - */ - public void setCellComment(String cellRef, XSSFComment comment) { - CellReference cellReference = new CellReference(cellRef); - - comment.setRow(cellReference.getRow()); - comment.setColumn(cellReference.getCol()); - } - - protected void setCellHyperlink(XSSFHyperlink hyperlink) { - hyperlinks.add(hyperlink); - } - - /** - * Return location of the active cell, e.g. <code>A1</code>. - * - * @return the location of the active cell. - */ - public String getActiveCell() { - return getSheetTypeSelection().getActiveCell(); - } - - /** - * Sets location of the active cell - * - * @param cellRef the location of the active cell, e.g. <code>A1</code>.. - */ - public void setActiveCell(String cellRef) { - CTSelection ctsel = getSheetTypeSelection(); - ctsel.setActiveCell(cellRef); - ctsel.setSqref(Arrays.asList(cellRef)); - } - - /** - * Does this sheet have any comments on it? We need to know, - * so we can decide about writing it to disk or not - */ - public boolean hasComments() { - if(sheetComments == null) { return false; } - return (sheetComments.getNumberOfComments() > 0); - } - protected int getNumberOfComments() { - if(sheetComments == null) { return 0; } - return sheetComments.getNumberOfComments(); - } - - private CTSelection getSheetTypeSelection() { - if (getSheetTypeSheetView().sizeOfSelectionArray() == 0) { - getSheetTypeSheetView().insertNewSelection(0); - } - return getSheetTypeSheetView().getSelectionArray(0); - } - - /** - * Return the default sheet view. This is the last one if the sheet's views, according to sec. 3.3.1.83 - * of the OOXML spec: "A single sheet view definition. When more than 1 sheet view is defined in the file, - * it means that when opening the workbook, each sheet view corresponds to a separate window within the - * spreadsheet application, where each window is showing the particular sheet. containing the same - * workbookViewId value, the last sheetView definition is loaded, and the others are discarded. - * When multiple windows are viewing the same sheet, multiple sheetView elements (with corresponding - * workbookView entries) are saved." - */ - private CTSheetView getDefaultSheetView() { - CTSheetViews views = getSheetTypeSheetViews(); - if (views == null || views.getSheetViewArray() == null || views.getSheetViewArray().length <= 0) { - return null; - } - return views.getSheetViewArray(views.getSheetViewArray().length - 1); - } - - protected XSSFSheet cloneSheet() { - XSSFSheet newSheet = new XSSFSheet(this.workbook); - newSheet.setSheet((CTSheet)sheet.copy()); - return newSheet; - } - - private void setSheet(CTSheet sheet) { - this.sheet = sheet; - } - - private CommentsSource getComments() { - if (sheetComments == null) { - sheetComments = new CommentsTable(); - } - return sheetComments; - } - /** - * Returns the sheet's comments object if there is one, - * or null if not - */ - protected CommentsSource getCommentsSourceIfExists() { - return sheetComments; - } - - private void addNewMergeCell(Region region) { - ctMergeCells = getMergedCells(); - CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); - ctMergeCell.setRef(region.getRegionRef()); - } - - private CTMergeCells getMergedCells() { - if (ctMergeCells == null) { - ctMergeCells = worksheet.addNewMergeCells(); - } - return ctMergeCells; - } - - private CTPageSetUpPr getSheetTypePageSetUpPr() { - CTSheetPr sheetPr = getSheetTypeSheetPr(); - return sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr(); - } - - private boolean removeRow(int startRow, int endRow, int n, int rownum) { - if (rownum >= (startRow + n) && rownum <= (endRow + n)) { - if (n > 0 && rownum > endRow) { - return true; - } - else if (n < 0 && rownum < startRow) { - return true; - } - } - return false; - } - - private CTPane getPane() { - if (getDefaultSheetView().getPane() == null) { - getDefaultSheetView().addNewPane(); - } - return getDefaultSheetView().getPane(); - } + short outlineLevel=0; + for(Row r:rows){ + XSSFRow xrow=(XSSFRow)r; + outlineLevel=xrow.getCTRow().getOutlineLevel()>outlineLevel? xrow.getCTRow().getOutlineLevel(): outlineLevel; + } + return outlineLevel; + } + + + private short getMaxOutlineLevelCols(){ + CTCols ctCols=worksheet.getColsArray(0); + CTCol[]colArray=ctCols.getColArray(); + short outlineLevel=0; + for(CTCol col: colArray){ + outlineLevel=col.getOutlineLevel()>outlineLevel? col.getOutlineLevel(): outlineLevel; + } + return outlineLevel; + } + + /** + * Determines if there is a page break at the indicated column + */ + public boolean isColumnBroken(short column) { + int[] colBreaks = getColumnBreaks(); + for (int i = 0 ; i < colBreaks.length ; i++) { + if (colBreaks[i] == column) { + return true; + } + } + return false; + } + + public boolean isColumnHidden(int columnIndex) { + return columnHelper.getColumn(columnIndex, false).getHidden(); + } + public boolean isColumnHidden(short column) { + return isColumnHidden(column & 0xFFFF); + } + + public boolean isDisplayFormulas() { + return getSheetTypeSheetView().getShowFormulas(); + } + + public boolean isDisplayGridlines() { + return getSheetTypeSheetView().getShowGridLines(); + } + + public boolean isDisplayRowColHeadings() { + return getSheetTypeSheetView().getShowRowColHeaders(); + } + + public boolean isGridsPrinted() { + return isPrintGridlines(); + } + + public boolean isPrintGridlines() { + return getSheetTypePrintOptions().getGridLines(); + } + + /** + * Tests if there is a page break at the indicated row + * + * @param row index of the row to test + * @return <code>true</code> if there is a page break at the indicated row + */ + public boolean isRowBroken(int row) { + int[] rowBreaks = getRowBreaks(); + for (int i = 0 ; i < rowBreaks.length ; i++) { + if (rowBreaks[i] == row) { + return true; + } + } + return false; + } + + /** + * Sets a page break at the indicated row + */ + public void setRowBreak(int row) { + CTPageBreak pgBreak = worksheet.isSetRowBreaks() ? worksheet.getRowBreaks() : worksheet.addNewRowBreaks(); + if (! isRowBroken(row)) { + CTBreak brk = pgBreak.addNewBrk(); + brk.setId(row); + } + } + + /** + * Removes a page break at the indicated column + */ + public void removeColumnBreak(short column) { + CTBreak[] brkArray = getSheetTypeColumnBreaks().getBrkArray(); + for (int i = 0 ; i < brkArray.length ; i++) { + if (brkArray[i].getId() == column) { + getSheetTypeColumnBreaks().removeBrk(i); + } + } + } + + public void protectSheet(String password) { + // TODO Auto-generated method stub + + } + + public void removeMergedRegion(int index) { + CTMergeCell[] mergeCellsArray = new CTMergeCell[getMergedCells().sizeOfMergeCellArray() - 1]; + for (int i = 0 ; i < getMergedCells().sizeOfMergeCellArray() ; i++) { + if (i < index) { + mergeCellsArray[i] = getMergedCells().getMergeCellArray(i); + } + else if (i > index) { + mergeCellsArray[i - 1] = getMergedCells().getMergeCellArray(i); + } + } + getMergedCells().setMergeCellArray(mergeCellsArray); + } + + public void removeRow(Row row) { + int counter = 0; + int rowNum=row.getRowNum(); + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + Row r = it.next(); + if (r.getRowNum() == rowNum) { + it.remove(); + worksheet.getSheetData().removeRow(counter); + } + counter++; + } + } + + /** + * Removes the page break at the indicated row + */ + public void removeRowBreak(int row) { + CTPageBreak pgBreak = worksheet.isSetRowBreaks() ? worksheet.getRowBreaks() : worksheet.addNewRowBreaks(); + CTBreak[] brkArray = pgBreak.getBrkArray(); + for (int i = 0 ; i < brkArray.length ; i++) { + if (brkArray[i].getId() == row) { + pgBreak.removeBrk(i); + } + } + } + + public Iterator<Row> rowIterator() { + return rows.iterator(); + } + + /** + * Alias for {@link #rowIterator()} to + * allow foreach loops + */ + public Iterator<Row> iterator() { + return rowIterator(); + } + + public void setAlternativeExpression(boolean b) { + // TODO Auto-generated method stub + + } + + public void setAlternativeFormula(boolean b) { + // TODO Auto-generated method stub + + } + + /** + * Flag indicating whether the sheet displays Automatic Page Breaks. + * + * @return <code>true</code> if the sheet displays Automatic Page Breaks. + */ + public boolean getAutobreaks() { + CTSheetPr sheetPr = getSheetTypeSheetPr(); + CTPageSetUpPr psSetup = (sheetPr == null || !sheetPr.isSetPageSetUpPr()) ? + CTPageSetUpPr.Factory.newInstance() : sheetPr.getPageSetUpPr(); + return psSetup.getAutoPageBreaks(); + } + + /** + * Flag indicating whether the sheet displays Automatic Page Breaks. + * + * @param value <code>true</code> if the sheet displays Automatic Page Breaks. + */ + public void setAutobreaks(boolean value) { + CTSheetPr sheetPr = getSheetTypeSheetPr(); + CTPageSetUpPr psSetup = sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr(); + psSetup.setAutoPageBreaks(value); + } + + public void setColumnBreak(short column) { + if (! isColumnBroken(column)) { + CTBreak brk = getSheetTypeColumnBreaks().addNewBrk(); + brk.setId(column); + } + } + + public void setColumnGroupCollapsed(short columnNumber, boolean collapsed) { + // TODO Auto-generated method stub + + } + + public void setColumnHidden(int columnIndex, boolean hidden) { + columnHelper.setColHidden(columnIndex, hidden); + } + public void setColumnHidden(short column, boolean hidden) { + setColumnHidden(column & 0xFFFF, hidden); + } + + public void setColumnWidth(int columnIndex, int width) { + columnHelper.setColWidth(columnIndex, width); + } + public void setColumnWidth(short column, short width) { + setColumnWidth(column & 0xFFFF, width & 0xFFFF); + } + + public void setDefaultColumnStyle(short column, CellStyle style) { + columnHelper.setColDefaultStyle(column, style); + } + + public void setDefaultColumnWidth(int width) { + getSheetTypeSheetFormatPr().setDefaultColWidth(width); + } + public void setDefaultColumnWidth(short width) { + setDefaultColumnWidth(width & 0xFFFF); + } + + public void setDefaultRowHeight(short height) { + getSheetTypeSheetFormatPr().setDefaultRowHeight(height / 20); + + } + + public void setDefaultRowHeightInPoints(float height) { + getSheetTypeSheetFormatPr().setDefaultRowHeight(height); + + } + + public void setDialog(boolean b) { + if(b && dialogsheet == null){ + CTDialogsheet dialogSheet = CTDialogsheet.Factory.newInstance(); + dialogsheet = dialogSheet; + }else{ + dialogsheet = null; + } + } + + public void setDisplayFormulas(boolean show) { + getSheetTypeSheetView().setShowFormulas(show); + } + + protected CTSheetView getSheetTypeSheetView() { + if (getDefaultSheetView() == null) { + getSheetTypeSheetViews().setSheetViewArray(0, CTSheetView.Factory.newInstance()); + } + return getDefaultSheetView(); + } + + public void setDisplayGridlines(boolean show) { + getSheetTypeSheetView().setShowGridLines(show); + } + + public void setDisplayGuts(boolean b) { + // TODO Auto-generated method stub + + } + + public void setDisplayRowColHeadings(boolean show) { + getSheetTypeSheetView().setShowRowColHeaders(show); + } + + public void setFitToPage(boolean b) { + getSheetTypePageSetUpPr().setFitToPage(b); + } + + public void setGridsPrinted(boolean value) { + setPrintGridlines(value); + } + + public void setHorizontallyCenter(boolean value) { + getSheetTypePrintOptions().setHorizontalCentered(value); + } + + public void setMargin(short margin, double size) { + CTPageMargins pageMargins = getSheetTypePageMargins(); + switch (margin) { + case LeftMargin: + pageMargins.setLeft(size); + case RightMargin: + pageMargins.setRight(size); + case TopMargin: + pageMargins.setTop(size); + case BottomMargin: + pageMargins.setBottom(size); + case HeaderMargin: + pageMargins.setHeader(size); + case FooterMargin: + pageMargins.setFooter(size); + } + } + + public void setPrintGridlines(boolean newPrintGridlines) { + getSheetTypePrintOptions().setGridLines(newPrintGridlines); + } + + public void setRowGroupCollapsed(int row, boolean collapse) { + // TODO Auto-generated method stub + + } + + public void setVerticallyCenter(boolean value) { + getSheetTypePrintOptions().setVerticalCentered(value); + } + + /** + * Sets the zoom magnication for the sheet. The zoom is expressed as a + * fraction. For example to express a zoom of 75% use 3 for the numerator + * and 4 for the denominator. + * + * @param numerator The numerator for the zoom magnification. + * @param denominator The denominator for the zoom magnification. + * @see #setZoom(int) + */ + public void setZoom(int numerator, int denominator) { + Float result = new Float(numerator)/new Float(denominator)*100; + setZoom(result.intValue()); + } + + /** + * Window zoom magnification for current view representing percent values. + * Valid values range from 10 to 400. Horizontal & Vertical scale together. + * + * For example: + * <pre> + * 10 - 10% + * 20 - 20% + * … + * 100 - 100% + * … + * 400 - 400% + * </pre> + * + * Current view can be Normal, Page Layout, or Page Break Preview. + * + * @param scale window zoom magnification + */ + public void setZoom(int scale) { + getSheetTypeSheetView().setZoomScale(scale); + } + + /** + * Zoom magnification to use when in normal view, representing percent values. + * Valid values range from 10 to 400. Horizontal & Vertical scale together. + * + * For example: + * <pre> + * 10 - 10% + * 20 - 20% + * … + * 100 - 100% + * … + * 400 - 400% + * </pre> + * + * Applies for worksheet sheet type only; zero implies the automatic setting. + * + * @param scale window zoom magnification + */ + public void setZoomNormal(int scale) { + getSheetTypeSheetView().setZoomScaleNormal(scale); + } + + /** + * Zoom magnification to use when in page layout view, representing percent values. + * Valid values range from 10 to 400. Horizontal & Vertical scale together. + * + * For example: + * <pre> + * 10 - 10% + * 20 - 20% + * … + * 100 - 100% + * … + * 400 - 400% + * </pre> + * + * Applies for worksheet sheet type only; zero implies the automatic setting. + * + * @param scale + */ + public void setZoomPageLayoutView(int scale) { + getSheetTypeSheetView().setZoomScalePageLayoutView(scale); + } + + /** + * Zoom magnification to use when in page break preview, representing percent values. + * Valid values range from 10 to 400. Horizontal & Vertical scale together. + * + * For example: + * <pre> + * 10 - 10% + * 20 - 20% + * … + * 100 - 100% + * … + * 400 - 400% + * </pre> + * + * Applies for worksheet only; zero implies the automatic setting. + * + * @param scale + */ + public void setZoomSheetLayoutView(int scale) { + getSheetTypeSheetView().setZoomScaleSheetLayoutView(scale); + } + + public void shiftRows(int startRow, int endRow, int n) { + shiftRows(startRow, endRow, n, false, false); + } + + public void shiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight) { + for (Iterator<Row> it = rowIterator() ; it.hasNext() ; ) { + Row row = it.next(); + if (!copyRowHeight) { + row.setHeight((short)0); + } + if (resetOriginalRowHeight && getDefaultRowHeight() >= 0) { + row.setHeight(getDefaultRowHeight()); + } + if (removeRow(startRow, endRow, n, row.getRowNum())) { + it.remove(); + } + else if (row.getRowNum() >= startRow && row.getRowNum() <= endRow) { + row.setRowNum(row.getRowNum() + n); + } + } + } + + /** + * Location of the top left visible cell Location of the top left visible cell in the bottom right + * pane (when in Left-to-Right mode). + * + * @param toprow the top row to show in desktop window pane + * @param leftcol the left column to show in desktop window pane + */ + public void showInPane(short toprow, short leftcol) { + CellReference cellReference = new CellReference(toprow, leftcol); + String cellRef = cellReference.formatAsString(); + getSheetTypeSheetView().setTopLeftCell(cellRef); + } + + public void ungroupColumn(short fromColumn, short toColumn) { + CTCols cols=worksheet.getColsArray(0); + for(int index=fromColumn;index<=toColumn;index++){ + CTCol col=columnHelper.getColumn(index, false); + if(col!=null){ + short outlineLevel=col.getOutlineLevel(); + col.setOutlineLevel((short)(outlineLevel-1)); + index=(int)col.getMax(); + + if(col.getOutlineLevel()<=0){ + int colIndex=columnHelper.getIndexOfColumn(cols,col); + worksheet.getColsArray(0).removeCol(colIndex); + } + } + } + worksheet.setColsArray(0,cols); + setSheetFormatPrOutlineLevelCol(); + } + + public void ungroupRow(int fromRow, int toRow) { + for(int i=fromRow;i<=toRow;i++){ + XSSFRow xrow=(XSSFRow)getRow(i-1); + if(xrow!=null){ + CTRow ctrow=xrow.getCTRow(); + short outlinelevel=ctrow.getOutlineLevel(); + ctrow.setOutlineLevel((short)(outlinelevel-1)); + //remove a row only if the row has no cell and if the outline level is 0 + if(ctrow.getOutlineLevel()==0 && xrow.getFirstCellNum()==-1){ + removeRow(xrow); + } + } + } + setSheetFormatPrOutlineLevelRow(); + } + + private void setSheetFormatPrOutlineLevelRow(){ + short maxLevelRow=getMaxOutlineLevelRows(); + getSheetTypeSheetFormatPr().setOutlineLevelRow((short)(maxLevelRow)); + } + + private void setSheetFormatPrOutlineLevelCol(){ + short maxLevelCol=getMaxOutlineLevelCols(); + getSheetTypeSheetFormatPr().setOutlineLevelCol((short)(maxLevelCol)); + } + + protected CTSheetViews getSheetTypeSheetViews() { + if (worksheet.getSheetViews() == null) { + worksheet.setSheetViews(CTSheetViews.Factory.newInstance()); + worksheet.getSheetViews().addNewSheetView(); + } + return worksheet.getSheetViews(); + } + + /** + * Returns a flag indicating whether this sheet is selected. + * <p> + * When only 1 sheet is selected and active, this value should be in synch with the activeTab value. + * In case of a conflict, the Start Part setting wins and sets the active sheet tab. + * </p> + * Note: multiple sheets can be selected, but only one sheet can be active at one time. + * + * @return <code>true</code> if this sheet is selected + */ + public boolean isSelected() { + CTSheetView view = getDefaultSheetView(); + return view != null && view.getTabSelected(); + } + + /** + * Sets a flag indicating whether this sheet is selected. + * + * <p> + * When only 1 sheet is selected and active, this value should be in synch with the activeTab value. + * In case of a conflict, the Start Part setting wins and sets the active sheet tab. + * </p> + * Note: multiple sheets can be selected, but only one sheet can be active at one time. + * + * @param value <code>true</code> if this sheet is selected + */ + public void setSelected(boolean value) { + CTSheetViews views = getSheetTypeSheetViews(); + for (CTSheetView view : views.getSheetViewArray()) { + view.setTabSelected(value); + } + } + + /** + * Assign a cell comment to a cell region in this worksheet + * + * @param cellRef cell region + * @param comment the comment to assign + */ + public void setCellComment(String cellRef, XSSFComment comment) { + CellReference cellReference = new CellReference(cellRef); + + comment.setRow(cellReference.getRow()); + comment.setColumn(cellReference.getCol()); + } + + protected void setCellHyperlink(XSSFHyperlink hyperlink) { + hyperlinks.add(hyperlink); + } + + /** + * Return location of the active cell, e.g. <code>A1</code>. + * + * @return the location of the active cell. + */ + public String getActiveCell() { + return getSheetTypeSelection().getActiveCell(); + } + + /** + * Sets location of the active cell + * + * @param cellRef the location of the active cell, e.g. <code>A1</code>.. + */ + public void setActiveCell(String cellRef) { + CTSelection ctsel = getSheetTypeSelection(); + ctsel.setActiveCell(cellRef); + ctsel.setSqref(Arrays.asList(cellRef)); + } + + /** + * Does this sheet have any comments on it? We need to know, + * so we can decide about writing it to disk or not + */ + public boolean hasComments() { + if(sheetComments == null) { return false; } + return (sheetComments.getNumberOfComments() > 0); + } + protected int getNumberOfComments() { + if(sheetComments == null) { return 0; } + return sheetComments.getNumberOfComments(); + } + + private CTSelection getSheetTypeSelection() { + if (getSheetTypeSheetView().sizeOfSelectionArray() == 0) { + getSheetTypeSheetView().insertNewSelection(0); + } + return getSheetTypeSheetView().getSelectionArray(0); + } + + /** + * Return the default sheet view. This is the last one if the sheet's views, according to sec. 3.3.1.83 + * of the OOXML spec: "A single sheet view definition. When more than 1 sheet view is defined in the file, + * it means that when opening the workbook, each sheet view corresponds to a separate window within the + * spreadsheet application, where each window is showing the particular sheet. containing the same + * workbookViewId value, the last sheetView definition is loaded, and the others are discarded. + * When multiple windows are viewing the same sheet, multiple sheetView elements (with corresponding + * workbookView entries) are saved." + */ + private CTSheetView getDefaultSheetView() { + CTSheetViews views = getSheetTypeSheetViews(); + if (views == null || views.getSheetViewArray() == null || views.getSheetViewArray().length <= 0) { + return null; + } + return views.getSheetViewArray(views.getSheetViewArray().length - 1); + } + + protected XSSFSheet cloneSheet() { + XSSFSheet newSheet = new XSSFSheet(getWorkbook()); + newSheet.setSheet((CTSheet)sheet.copy()); + return newSheet; + } + + private void setSheet(CTSheet sheet) { + this.sheet = sheet; + } + + private CommentsSource getComments() { + if (sheetComments == null) { + sheetComments = (CommentsTable)createRelationship(XSSFRelation.SHEET_COMMENTS, CommentsTable.class, (int)sheet.getSheetId()); + } + return sheetComments; + } + /** + * Returns the sheet's comments object if there is one, + * or null if not + */ + protected CommentsSource getCommentsSourceIfExists() { + return sheetComments; + } + + private void addNewMergeCell(Region region) { + ctMergeCells = getMergedCells(); + CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); + ctMergeCell.setRef(region.getRegionRef()); + } + + private CTMergeCells getMergedCells() { + if (ctMergeCells == null) { + ctMergeCells = worksheet.addNewMergeCells(); + } + return ctMergeCells; + } + + private CTPageSetUpPr getSheetTypePageSetUpPr() { + CTSheetPr sheetPr = getSheetTypeSheetPr(); + return sheetPr.isSetPageSetUpPr() ? sheetPr.getPageSetUpPr() : sheetPr.addNewPageSetUpPr(); + } + + private boolean removeRow(int startRow, int endRow, int n, int rownum) { + if (rownum >= (startRow + n) && rownum <= (endRow + n)) { + if (n > 0 && rownum > endRow) { + return true; + } + else if (n < 0 && rownum < startRow) { + return true; + } + } + return false; + } + + private CTPane getPane() { + if (getDefaultSheetView().getPane() == null) { + getDefaultSheetView().addNewPane(); + } + return getDefaultSheetView().getPane(); + } + + + @Override + protected void commit() throws IOException { + + if(worksheet.getColsArray().length == 1) { + CTCols col = worksheet.getColsArray(0); + if(col.getColArray().length == 0) { + worksheet.setColsArray(null); + } + } + // Now re-generate our CTHyperlinks, if needed + if(hyperlinks.size() > 0) { + if(worksheet.getHyperlinks() == null) { + worksheet.addNewHyperlinks(); + } + CTHyperlink[] ctHls = new CTHyperlink[hyperlinks.size()]; + for(int i=0; i<ctHls.length; i++) { + // If our sheet has hyperlinks, have them add + // any relationships that they might need + XSSFHyperlink hyperlink = hyperlinks.get(i); + hyperlink.generateRelationIfNeeded(getPackagePart()); + // Now grab their underling object + ctHls[i] = hyperlink.getCTHyperlink(); + } + worksheet.getHyperlinks().setHyperlinkArray(ctHls); + } + + XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS); + xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorksheet.type.getName().getNamespaceURI(), "worksheet")); + PackagePart part = getPackagePart(); + OutputStream out = part.getOutputStream(); + worksheet.save(out, xmlOptions); + out.close(); + } + + protected void setParent(POIXMLDocumentPart p){ + this.parent = p; + } + } 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 10449078db..d6b6c515dd 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java @@ -18,16 +18,15 @@ package org.apache.poi.xssf.usermodel; import java.io.IOException; -import java.io.InputStream; import java.io.OutputStream; import java.util.LinkedList; import java.util.List; - +import java.util.HashMap; +import java.util.Iterator; import javax.xml.namespace.QName; - import org.apache.poi.POIXMLDocument; -import org.apache.poi.ss.usermodel.CommentsSource; -import org.apache.poi.ss.usermodel.CreationHelper; +import org.apache.poi.POIXMLDocumentPart; +import org.apache.poi.POIXMLFactory; import org.apache.poi.ss.usermodel.Palette; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.ss.usermodel.Row; @@ -37,19 +36,15 @@ import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; import org.apache.poi.util.POILogFactory; import org.apache.poi.util.POILogger; +import org.apache.poi.util.PackageHelper; import org.apache.poi.xssf.model.*; -import org.apache.xmlbeans.XmlException; +import org.apache.poi.POIXMLException; import org.apache.xmlbeans.XmlObject; import org.apache.xmlbeans.XmlOptions; import org.openxml4j.exceptions.InvalidFormatException; +import org.openxml4j.exceptions.OpenXML4JException; +import org.openxml4j.opc.*; import org.openxml4j.opc.Package; -import org.openxml4j.opc.PackagePart; -import org.openxml4j.opc.PackagePartName; -import org.openxml4j.opc.PackageRelationship; -import org.openxml4j.opc.PackageRelationshipCollection; -import org.openxml4j.opc.PackageRelationshipTypes; -import org.openxml4j.opc.PackagingURIHelper; -import org.openxml4j.opc.TargetMode; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookView; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookViews; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName; @@ -57,137 +52,203 @@ import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf; import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument; -import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument; - - -public class XSSFWorkbook extends POIXMLDocument implements Workbook { - /** Are we a normal workbook, or a macro enabled one? */ - private boolean isMacroEnabled = false; - - private CTWorkbook workbook; - - private List<XSSFSheet> sheets = new LinkedList<XSSFSheet>(); - private List<XSSFName> namedRanges = new LinkedList<XSSFName>(); - private SharedStringSource sharedStringSource; - private StylesSource stylesSource; - - private List<ThemeTable> themes = new LinkedList<ThemeTable>(); - - private MissingCellPolicy missingCellPolicy = Row.RETURN_NULL_AND_BLANK; +/** + * High level representation of a SpreadsheetML workbook. This is the first object most users + * will construct whether they are reading or writing a workbook. It is also the + * top level object for creating new sheets/etc. + */ +public class XSSFWorkbook extends POIXMLDocument implements Workbook, Iterable<XSSFSheet> { + + /** + * The underlying XML bean + */ + private CTWorkbook workbook; + + /** + * this holds the XSSFSheet objects attached to this workbook + */ + private List<XSSFSheet> sheets; + + /** + * this holds the XSSFName objects attached to this workbook + */ + private List<XSSFName> namedRanges; + + /** + * shared string table - a cache of strings in this workbook + */ + private SharedStringsTable sharedStringSource; + + /** + * A collection of shared objects used for styling content, + * e.g. fonts, cell styles, colors, etc. + */ + private StylesSource stylesSource; + + /** + * Used to keep track of the data formatter so that all + * createDataFormatter calls return the same one for a given + * book. This ensures that updates from one places is visible + * someplace else. + */ + private XSSFDataFormat formatter; + + /** + * The policy to apply in the event of missing or + * blank cells when fetching from a row. + * See {@link org.apache.poi.ss.usermodel.Row.MissingCellPolicy} + */ + private MissingCellPolicy missingCellPolicy = Row.RETURN_NULL_AND_BLANK; private static POILogger log = POILogFactory.getLogger(XSSFWorkbook.class); + /** + * Create a new SpreadsheetML workbook. + */ public XSSFWorkbook() { - this.workbook = CTWorkbook.Factory.newInstance(); - CTBookViews bvs = this.workbook.addNewBookViews(); - CTBookView bv = bvs.addNewWorkbookView(); - bv.setActiveTab(0); - this.workbook.addNewSheets(); - - // We always require styles and shared strings - sharedStringSource = new SharedStringsTable(); - stylesSource = new StylesTable(); - } - + super(); + try { + newWorkbook(); + }catch (Exception e){ + throw new POIXMLException(e); + } + } + + /** + * Constructs a XSSFWorkbook object given a file name. + * + * @param path the file name. + */ public XSSFWorkbook(String path) throws IOException { this(openPackage(path)); } - public XSSFWorkbook(InputStream is) throws IOException { - this(openPackage(is)); - } - public XSSFWorkbook(Package pkg) throws IOException { - super(pkg); + /** + * Constructs a XSSFWorkbook object given a OpenXML4J <code>Package</code> object, + * see <a href="http://openxml4j.org/">www.openxml4j.org</a>. + * + * @param pkg the OpenXML4J <code>Package</code> object. + */ + public XSSFWorkbook(Package pkg) throws IOException { + super(); + if(pkg.getPackageAccess() == PackageAccess.READ){ + //current implementation of OpenXML4J is funny. + //Packages opened by Package.open(InputStream is) are read-only, + //there is no way to change or even save such an instance in a OutputStream. + //The workaround is to create a copy via a temp file + try { + Package tmp = PackageHelper.clone(pkg); + initialize(tmp); + } catch (OpenXML4JException e){ + throw new POIXMLException(e); + } + } else { + initialize(pkg); + } + } + + /** + * Initialize this workbook from the specified Package + */ + @Override + protected void initialize(Package pkg) throws IOException { + super.initialize(pkg); + try { - WorkbookDocument doc = WorkbookDocument.Factory.parse(getCorePart().getInputStream()); - this.workbook = doc.getWorkbook(); + //build the POIXMLDocumentPart tree, this workbook is the root + read(new XSSFFactory()); - // Are we macro enabled, or just normal? - isMacroEnabled = - getCorePart().getContentType().equals(XSSFRelation.MACROS_WORKBOOK.getContentType()); + PackagePart corePart = getCorePart(); - try { - // Load shared strings - sharedStringSource = XSSFRelation.SHARED_STRINGS.load(getCorePart()); - } catch(Exception e) { - throw new IOException("Unable to load shared strings - " + e.toString()); - } - try { - // Load styles source - stylesSource = XSSFRelation.STYLES.load(getCorePart()); - } catch(Exception e) { - e.printStackTrace(); - throw new IOException("Unable to load styles - " + e.toString()); - } - try { - // Load themes - themes = XSSFRelation.THEME.loadAll(getCorePart()); - } catch(Exception e) { - throw new IOException("Unable to load shared strings - " + e.toString()); - } + WorkbookDocument doc = WorkbookDocument.Factory.parse(corePart.getInputStream()); + this.workbook = doc.getWorkbook(); + HashMap<String, XSSFSheet> shIdMap = new HashMap<String, XSSFSheet>(); + for(POIXMLDocumentPart p : getRelations()){ + if(p instanceof SharedStringsTable) sharedStringSource = (SharedStringsTable)p; + else if(p instanceof StylesSource) stylesSource = (StylesSource)p; + else if (p instanceof XSSFSheet) { + shIdMap.put(p.getPackageRelationship().getId(), (XSSFSheet)p); + } + } // Load individual sheets + sheets = new LinkedList<XSSFSheet>(); for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { - PackagePart part = getPackagePart(ctSheet); - if (part == null) { + String id = ctSheet.getId(); + XSSFSheet sh = shIdMap.get(id); + sh.sheet = ctSheet; + if(sh == null) { log.log(POILogger.WARN, "Sheet with name " + ctSheet.getName() + " and r:id " + ctSheet.getId()+ " was defined, but didn't exist in package, skipping"); continue; } + //initialize internal arrays of rows and columns + sh.initialize(); - // Load child streams of the sheet - List<CommentsTable> childModels; - CommentsSource comments = null; - List<Drawing> drawings; - List<Control> controls; - try { - // Get the comments for the sheet, if there are any - childModels = XSSFRelation.SHEET_COMMENTS.loadAll(part); - if(childModels.size() > 0) { - comments = childModels.get(0); - } - - // Get the drawings for the sheet, if there are any - drawings = XSSFRelation.VML_DRAWINGS.loadAll(part); - // Get the activeX controls for the sheet, if there are any - controls = XSSFRelation.ACTIVEX_CONTROLS.loadAll(part); - } catch(Exception e) { - throw new RuntimeException("Unable to construct child part",e); - } - - // Now create the sheet - WorksheetDocument worksheetDoc = WorksheetDocument.Factory.parse(part.getInputStream()); - XSSFSheet sheet = new XSSFSheet(ctSheet, worksheetDoc.getWorksheet(), this, comments, drawings, controls); - this.sheets.add(sheet); - + PackagePart sheetPart = sh.getPackagePart(); // Process external hyperlinks for the sheet, // if there are any PackageRelationshipCollection hyperlinkRels = - part.getRelationshipsByType(XSSFRelation.SHEET_HYPERLINKS.getRelation()); - sheet.initHyperlinks(hyperlinkRels); + sheetPart.getRelationshipsByType(XSSFRelation.SHEET_HYPERLINKS.getRelation()); + sh.initHyperlinks(hyperlinkRels); // Get the embeddings for the workbook - for(PackageRelationship rel : part.getRelationshipsByType(XSSFRelation.OLEEMBEDDINGS.getRelation())) + for(PackageRelationship rel : sheetPart.getRelationshipsByType(XSSFRelation.OLEEMBEDDINGS.getRelation())) embedds.add(getTargetPart(rel)); // TODO: Add this reference to each sheet as well - for(PackageRelationship rel : part.getRelationshipsByType(XSSFRelation.PACKEMBEDDINGS.getRelation())) + for(PackageRelationship rel : sheetPart.getRelationshipsByType(XSSFRelation.PACKEMBEDDINGS.getRelation())) embedds.add(getTargetPart(rel)); - } - } catch (XmlException e) { - throw new IOException(e.toString()); - } catch (InvalidFormatException e) { - throw new IOException(e.toString()); - } - // Process the named ranges - if(workbook.getDefinedNames() != null) { - for(CTDefinedName ctName : workbook.getDefinedNames().getDefinedNameArray()) { - namedRanges.add(new XSSFName(ctName, this)); + sheets.add(sh); } - } + + if(sharedStringSource == null) { + //Create SST if it is missing + sharedStringSource = (SharedStringsTable)createRelationship(XSSFRelation.SHARED_STRINGS, SharedStringsTable.class); + } + + // Process the named ranges + namedRanges = new LinkedList<XSSFName>(); + if(workbook.getDefinedNames() != null) { + for(CTDefinedName ctName : workbook.getDefinedNames().getDefinedNameArray()) { + namedRanges.add(new XSSFName(ctName, this)); + } + } + + } catch (Exception e) { + throw new POIXMLException(e); + } + } + + /** + * Create a new SpreadsheetML OOXML package and setup the default minimal content + */ + protected void newWorkbook() throws IOException, OpenXML4JException{ + Package pkg = Package.create(PackageHelper.createTempFile()); + // Main part + PackagePartName corePartName = PackagingURIHelper.createPartName(XSSFRelation.WORKBOOK.getDefaultFileName()); + // Create main part relationship + pkg.addRelationship(corePartName, TargetMode.INTERNAL, PackageRelationshipTypes.CORE_DOCUMENT); + // Create main document part + pkg.createPart(corePartName, XSSFRelation.WORKBOOK.getContentType()); + + pkg.getPackageProperties().setCreatorProperty("Apache POI"); + + super.initialize(pkg); + + workbook = CTWorkbook.Factory.newInstance(); + CTBookViews bvs = workbook.addNewBookViews(); + CTBookView bv = bvs.addNewWorkbookView(); + bv.setActiveTab(0); + workbook.addNewSheets(); + + sharedStringSource = (SharedStringsTable)createRelationship(XSSFRelation.SHARED_STRINGS, SharedStringsTable.class); + stylesSource = (StylesTable)createRelationship(XSSFRelation.STYLES, StylesTable.class); + + namedRanges = new LinkedList<XSSFName>(); + sheets = new LinkedList<XSSFSheet>(); } /** @@ -199,22 +260,6 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return this.workbook; } - /** - * Get the PackagePart corresponding to a given sheet. - * - * @param ctSheet The sheet - * @return A PackagePart, or null if no matching part found. - * @throws InvalidFormatException - */ - private PackagePart getPackagePart(CTSheet ctSheet) throws InvalidFormatException { - PackageRelationship rel = this.getCorePart().getRelationship(ctSheet.getId()); - if (rel == null) { - log.log(POILogger.WARN, "No relationship found for sheet " + ctSheet.getId() + " - core part has " + this.getCorePart().getRelationships().size() + " relations defined"); - return null; - } - return getTargetPart(rel); - } - public int addPicture(byte[] pictureData, int format) { // TODO Auto-generated method stub return 0; @@ -252,6 +297,11 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return null; } + /** + * Create a new XSSFCellStyle and add it to the workbook's style table + * + * @return the new XSSFCellStyle object + */ public XSSFCellStyle createCellStyle() { CTXf xf=CTXf.Factory.newInstance(); xf.setNumFmtId(0); @@ -265,53 +315,93 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return style; } - public XSSFDataFormat createDataFormat() { - return (XSSFDataFormat)getCreationHelper().createDataFormat(); - } - + /** + * Returns the instance of XSSFDataFormat for this workbook. + * + * @return the XSSFDataFormat object + * @see org.apache.poi.ss.usermodel.DataFormat + */ + public XSSFDataFormat createDataFormat() { + if (formatter == null) + formatter = new XSSFDataFormat(stylesSource); + return formatter; + } + + /** + * create a new Font and add it to the workbook's font table + * + * @return new font object + */ public XSSFFont createFont() { XSSFFont font= new XSSFFont(); stylesSource.putFont(font); return font; } + /** + * Creates a new named range and add it to the model + * + * @return named range high level + */ public XSSFName createName() { XSSFName name = new XSSFName(this); namedRanges.add(name); return name; } + /** + * create an XSSFSheet for this workbook, adds it to the sheets and returns + * the high level representation. Use this to create new sheets. + * + * @return XSSFSheet representing the new sheet. + */ public XSSFSheet createSheet() { String sheetname = "Sheet" + (sheets.size() + 1); return createSheet(sheetname); } + /** + * create an XSSFSheet for this workbook, adds it to the sheets and returns + * the high level representation. Use this to create new sheets. + * + * @param sheetname sheetname to set for the sheet, can't be duplicate, greater than 31 chars or contain /\?*[] + * @return XSSFSheet representing the new sheet. + */ public XSSFSheet createSheet(String sheetname) { if (doesContainsSheetName( sheetname, sheets.size() )) throw new IllegalArgumentException( "The workbook already contains a sheet of this name" ); - return createSheet(sheetname, XSSFSheet.newInstance()); - } - public XSSFSheet createSheet(String sheetname, CTWorksheet worksheet) { + int sheetNumber = getNumberOfSheets() + 1; + XSSFSheet wrapper = (XSSFSheet)createRelationship(XSSFRelation.WORKSHEET, XSSFSheet.class, sheetNumber); + wrapper.setParent(this); + CTSheet sheet = addSheet(sheetname); - XSSFSheet wrapper = new XSSFSheet(sheet, worksheet, this); + wrapper.sheet = sheet; + sheet.setId(wrapper.getPackageRelationship().getId()); + sheet.setSheetId(sheetNumber); + this.sheets.add(wrapper); return wrapper; } - public XSSFSheet createDialogsheet(String sheetname, CTDialogsheet dialogsheet) { - CTSheet sheet = addSheet(sheetname); - XSSFDialogsheet wrapper = new XSSFDialogsheet(sheet, dialogsheet, this); - this.sheets.add(wrapper); - return wrapper; + protected XSSFSheet createDialogsheet(String sheetname, CTDialogsheet dialogsheet) { + CTSheet sheet = addSheet(sheetname); + XSSFDialogsheet wrapper = new XSSFDialogsheet(sheet, dialogsheet, this); + this.sheets.add(wrapper); + return wrapper; } private CTSheet addSheet(String sheetname) { - CTSheet sheet = workbook.getSheets().addNewSheet(); + validateSheetName(sheetname); + + CTSheet sheet = workbook.getSheets().addNewSheet(); sheet.setName(sheetname); return sheet; } + /** + * Finds a font that matches the one with the supplied attributes + */ public XSSFFont findFont(short boldWeight, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline) { short fontNum=getNumberOfFonts(); for (short i = 0; i < fontNum; i++) { @@ -344,34 +434,43 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return index.intValue(); } + /** + * Gets all embedded OLE2 objects from the Workbook. + * + * @return the list of embedded objects (a list of {@link org.openxml4j.opc.PackagePart} objects.) + */ public List getAllEmbeddedObjects() { - // TODO Auto-generated method stub - return null; + return embedds; } + /** + * Gets all pictures from the Workbook. + * + * @return the list of pictures (a list of {@link XSSFPictureData} objects.) + */ public List<PictureData> getAllPictures() { // In OOXML pictures are referred to in sheets List<PictureData> pictures = new LinkedList<PictureData>(); - for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) { - try { - PackagePart sheetPart = getPackagePart(ctSheet); - if (sheetPart == null) { - continue; - } - PackageRelationshipCollection prc = sheetPart.getRelationshipsByType(XSSFRelation.DRAWINGS.getRelation()); - for (PackageRelationship rel : prc) { - PackagePart drawingPart = getTargetPart(rel); - PackageRelationshipCollection prc2 = drawingPart.getRelationshipsByType(XSSFRelation.IMAGES.getRelation()); - for (PackageRelationship rel2 : prc2) { - PackagePart imagePart = getTargetPart(rel2); - XSSFPictureData pd = new XSSFPictureData(imagePart); - pictures.add(pd); - } - } - } catch (InvalidFormatException e) { - throw new RuntimeException(e.getMessage(), e); - } - } + for(POIXMLDocumentPart p : getRelations()){ + if (p instanceof XSSFSheet) { + PackagePart sheetPart = p.getPackagePart(); + try { + PackageRelationshipCollection prc = sheetPart.getRelationshipsByType(XSSFRelation.DRAWINGS.getRelation()); + for (PackageRelationship rel : prc) { + PackagePart drawingPart = getTargetPart(rel); + PackageRelationshipCollection prc2 = drawingPart.getRelationshipsByType(XSSFRelation.IMAGES.getRelation()); + for (PackageRelationship rel2 : prc2) { + PackagePart imagePart = getTargetPart(rel2); + XSSFPictureData pd = new XSSFPictureData(imagePart); + pictures.add(pd); + } + } + } catch (InvalidFormatException e) { + throw new POIXMLException(e.getMessage(), e); + } + + } + } return pictures; } @@ -389,39 +488,91 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return null; } + /** + * Get the font at the given index number + * + * @param idx index number + * @return XSSFFont at the index + */ public XSSFFont getFontAt(short idx) { return (XSSFFont)stylesSource.getFontAt(idx); } + /** + * Gets the Named range at the given index number + * + * @param index position of the named range + * @return XSSFName at the index + */ public XSSFName getNameAt(int index) { return namedRanges.get(index); } + + /** + * Gets the Named range name at the given index number, + * this method is equivalent to <code>getNameAt(index).getName()</code> + * + * @param index the named range index (0 based) + * @return named range name + * @see #getNameAt(int) + */ public String getNameName(int index) { return getNameAt(index).getNameName(); } - public int getNameIndex(String name) { - for(int i=0; i<namedRanges.size(); i++) { - if(namedRanges.get(i).getNameName().equals(name)) { - return i; - } - } + + /** + * Gets the named range index by his name + * <i>Note:</i>Excel named ranges are case-insensitive and + * this method performs a case-insensitive search. + * + * @param name named range name + * @return named range index + */ + public int getNameIndex(String name) { + int i = 0; + for(XSSFName nr : namedRanges) { + if(nr.getNameName().equals(name)) { + return i; + } + i++; + } return -1; } + /** + * Get the number of styles the workbook contains + * + * @return count of cell styles + */ public short getNumCellStyles() { return (short) ((StylesTable)stylesSource).getNumCellStyles(); } + /** + * Get the number of fonts in the this workbook + * + * @return number of fonts + */ public short getNumberOfFonts() { return (short)((StylesTable)stylesSource).getNumberOfFonts(); } + /** + * Get the number of named ranges in the this workbook + * + * @return number of named ranges + */ public int getNumberOfNames() { return namedRanges.size(); } + /** + * Get the number of worksheets in the this workbook + * + * @return number of worksheets + */ public int getNumberOfSheets() { - return this.workbook.getSheets().sizeOfSheetArray(); + return this.sheets.size(); } public String getPrintArea(int sheetIndex) { @@ -429,6 +580,10 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return null; } + /** + * deprecated May 2008 + * @deprecated - Misleading name - use getActiveSheetIndex() + */ public short getSelectedTab() { short i = 0; for (XSSFSheet sheet : this.sheets) { @@ -440,7 +595,13 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return -1; } - public Sheet getSheet(String name) { + /** + * Get sheet with the given name (case insensitive match) + * + * @param name of the sheet + * @return XSSFSheet with the name provided or <code>null</code> if it does not exist + */ + public XSSFSheet getSheet(String name) { CTSheet[] sheets = this.workbook.getSheets().getSheetArray(); for (int i = 0 ; i < sheets.length ; ++i) { if (name.equals(sheets[i].getName())) { @@ -450,10 +611,22 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return null; } + /** + * Get the XSSFSheet object at the given index. + * + * @param index of the sheet number (0-based physical & logical) + * @return XSSFSheet at the provided index + */ public XSSFSheet getSheetAt(int index) { return this.sheets.get(index); } + /** + * Returns the index of the sheet by his name + * + * @param name the sheet name + * @return index of the sheet (0 based) + */ public int getSheetIndex(String name) { CTSheet[] sheets = this.workbook.getSheets().getSheetArray(); for (int i = 0 ; i < sheets.length ; ++i) { @@ -464,25 +637,50 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { return -1; } + /** + * Returns the index of the given sheet + * + * @param sheet the sheet to look up + * @return index of the sheet (0 based). <tt>-1</tt> if not found + */ public int getSheetIndex(Sheet sheet) { - return this.sheets.indexOf(sheet); - } - - public String getSheetName(int sheet) { - return this.workbook.getSheets().getSheetArray(sheet).getName(); - } - + int idx = 0; + for(XSSFSheet sh : this){ + if(sh == sheet) return idx; + idx++; + } + return -1; + } + + /** + * Get the sheet name + * + * @param sheetIx Number + * @return Sheet name + */ + public String getSheetName(int sheetIx) { + validateSheetIndex(sheetIx); + return this.workbook.getSheets().getSheetArray(sheetIx).getName(); + } + + /** + * Allow foreach loops: + * <pre><code> + * XSSFWorkbook wb = new XSSFWorkbook(package); + * for(XSSFSheet sheet : wb){ + * + * } + * </code></pre> + */ + public Iterator<XSSFSheet> iterator() { + return sheets.iterator(); + } /** * Are we a normal workbook (.xlsx), or a * macro enabled workbook (.xlsm)? */ public boolean isMacroEnabled() { - return isMacroEnabled; - } - - public void insertChartRecord() { - // TODO Auto-generated method stub - + return getCorePart().getContentType().equals(XSSFRelation.MACROS_WORKBOOK.getContentType()); } public void removeName(int index) { @@ -500,6 +698,20 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } + /** + * Removes sheet at the given index.<p/> + * + * Care must be taken if the removed sheet is the currently active or only selected sheet in + * the workbook. There are a few situations when Excel must have a selection and/or active + * sheet. (For example when printing - see Bug 40414).<br/> + * + * This method makes sure that if the removed sheet was active, another sheet will become + * active in its place. Furthermore, if the removed sheet was the only selected sheet, another + * sheet will become selected. The newly active/selected sheet will have the same index, or + * one less if the removed sheet was the last in the workbook. + * + * @param index of the sheet (0-based) + */ public void removeSheetAt(int index) { this.sheets.remove(index); this.workbook.getSheets().removeSheet(index); @@ -518,7 +730,7 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { * Sets the policy on what to do when * getting missing or blank cells from a row. * This will then apply to all calls to - * {@link Row.getCell()}. See + * {@link Row#getCell(int)}}. See * {@link MissingCellPolicy} */ public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) { @@ -600,12 +812,24 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } } + /** + * Set the sheet name. + * Will throw IllegalArgumentException if the name is greater than 31 chars + * or contains /\?*[] + * @param sheet number (0 based) + */ public void setSheetName(int sheet, String name) { if (doesContainsSheetName(name, sheet )) throw new IllegalArgumentException( "The workbook already contains a sheet of this name" ); this.workbook.getSheets().getSheetArray(sheet).setName(name); } + /** + * sets the order of appearance for a given sheet. + * + * @param sheetname the name of the sheet to reorder + * @param pos the position that we want to insert the sheet into (0 based) + */ public void setSheetOrder(String sheetname, int pos) { int idx = getSheetIndex(sheetname); sheets.add(pos, sheets.remove(idx)); @@ -621,141 +845,53 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } - public void write(OutputStream stream) throws IOException { - // What kind of workbook are we? - XSSFRelation workbookRelation = XSSFRelation.WORKBOOK; - if(isMacroEnabled) { - workbookRelation = XSSFRelation.MACROS_WORKBOOK; - } - - try { - // Create a package referring the temp file. - Package pkg = Package.create(stream); - // Main part - PackagePartName corePartName = PackagingURIHelper.createPartName(workbookRelation.getDefaultFileName()); - // Create main part relationship - int rId = 1; - pkg.addRelationship(corePartName, TargetMode.INTERNAL, PackageRelationshipTypes.CORE_DOCUMENT, "rId" + (rId++)); - - // Create main document part - PackagePart corePart = pkg.createPart(corePartName, workbookRelation.getContentType()); - OutputStream out; - - XmlOptions xmlOptions = new XmlOptions(); - // Requests use of whitespace for easier reading - xmlOptions.setSavePrettyPrint(); - xmlOptions.setSaveOuter(); - xmlOptions.setUseDefaultNamespace(); - - // Write out our sheets, updating the references - // to them in the main workbook as we go - int drawingIndex = 1; - for (int i=0 ; i < this.getNumberOfSheets(); i++) { - int sheetNumber = (i+1); - XSSFSheet sheet = this.getSheetAt(i); - PackagePartName partName = PackagingURIHelper.createPartName( - XSSFRelation.WORKSHEET.getFileName(sheetNumber)); - PackageRelationship rel = - corePart.addRelationship(partName, TargetMode.INTERNAL, XSSFRelation.WORKSHEET.getRelation(), "rId" + sheetNumber); - PackagePart part = pkg.createPart(partName, XSSFRelation.WORKSHEET.getContentType()); - - // XXX This should not be needed, but apparently the setSaveOuter call above does not work in XMLBeans 2.2 - xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorksheet.type.getName().getNamespaceURI(), "worksheet")); - sheet.save(part, xmlOptions); - - // Update our internal reference for the package part - workbook.getSheets().getSheetArray(i).setId(rel.getId()); - workbook.getSheets().getSheetArray(i).setSheetId(sheetNumber); - - // If our sheet has drawings, then write out those - if(sheet.getDrawings() != null) { - for(Drawing drawing : sheet.getDrawings()) { - XSSFRelation.VML_DRAWINGS.save( - drawing, - part, - drawingIndex - ); - drawingIndex++; - } - } - - // If our sheet has comments, then write out those - if(sheet.hasComments()) { - CommentsTable ct = (CommentsTable)sheet.getCommentsSourceIfExists(); - XSSFRelation.SHEET_COMMENTS.save(ct, part, sheetNumber); - } - - // If our sheet has controls, then write out those - if(sheet.getControls() != null) { - int controlIndex = 1; - for(Control control : sheet.getControls()) { - XSSFRelation.ACTIVEX_CONTROLS.save( - control, - part, - controlIndex - ); - controlIndex++; - } - } - } - - // Write shared strings and styles - if(sharedStringSource != null) { - SharedStringsTable sst = (SharedStringsTable)sharedStringSource; - XSSFRelation.SHARED_STRINGS.save(sst, corePart); - } - if(stylesSource != null) { - StylesTable st = (StylesTable)stylesSource; - XSSFRelation.STYLES.save(st, corePart); - } - if(themes.size() > 0) { - for(int i=0; i< themes.size(); i++) { - XSSFRelation.THEME.save(themes.get(i), corePart, i+1); - } - } - - // Named ranges - if(namedRanges.size() > 0) { - CTDefinedNames names = CTDefinedNames.Factory.newInstance(); - CTDefinedName[] nr = new CTDefinedName[namedRanges.size()]; - for(int i=0; i<namedRanges.size(); i++) { - nr[i] = namedRanges.get(i).getCTName(); - } - names.setDefinedNameArray(nr); - workbook.setDefinedNames(names); - } else { - if(workbook.isSetDefinedNames()) { - workbook.setDefinedNames(null); - } - } - - // Macro related bits - if(isMacroEnabled) { - // Copy VBA Macros if present - if(XSSFRelation.VBA_MACROS.exists( getCorePart() )) { - try { - BinaryPart vba = XSSFRelation.VBA_MACROS.load(getCorePart()); - XSSFRelation.VBA_MACROS.save(vba, corePart); - } catch(Exception e) { - throw new RuntimeException("Unable to copy vba macros over", e); - } - } - } - - // Now we can write out the main Workbook, with - // the correct references to the other parts - out = corePart.getOutputStream(); - // XXX This should not be needed, but apparently the setSaveOuter call above does not work in XMLBeans 2.2 - xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorkbook.type.getName().getNamespaceURI(), "workbook")); - workbook.save(out, xmlOptions); - out.close(); - - // All done - pkg.close(); - } catch (InvalidFormatException e) { - // TODO: replace with more meaningful exception - throw new RuntimeException(e); - } + /** + * marshal named ranges from the {@link #namedRanges} collection to the underlying CTWorkbook bean + */ + private void saveNamedRanges(){ + // Named ranges + if(namedRanges.size() > 0) { + CTDefinedNames names = CTDefinedNames.Factory.newInstance(); + CTDefinedName[] nr = new CTDefinedName[namedRanges.size()]; + int i = 0; + for(XSSFName name : namedRanges) { + nr[i] = name.getCTName(); + i++; + } + names.setDefinedNameArray(nr); + workbook.setDefinedNames(names); + } else { + if(workbook.isSetDefinedNames()) { + workbook.unsetDefinedNames(); + } + } + + } + + @Override + protected void commit() throws IOException { + saveNamedRanges(); + + XmlOptions xmlOptions = new XmlOptions(DEFAULT_XML_OPTIONS); + xmlOptions.setSaveSyntheticDocumentElement(new QName(CTWorkbook.type.getName().getNamespaceURI(), "workbook")); + PackagePart part = getPackagePart(); + OutputStream out = part.getOutputStream(); + workbook.save(out, xmlOptions); + out.close(); + } + + /** + * Method write - write out this workbook to an Outputstream. + * + * @param stream - the java OutputStream you wish to write the XLS to + * + * @exception IOException if anything can't be written. + */ + public void write(OutputStream stream) throws IOException { + //force all children to commit their changes into the underlying OOXML Package + save(); + + getPackage().save(stream); } public void writeProtectWorkbook(String password, String username) { @@ -763,24 +899,46 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } - public SharedStringSource getSharedStringSource() { + /** + * Returns SharedStringsTable - tha cache of string for this workbook + * + * @return the shared string table + */ + public SharedStringsTable getSharedStringSource() { return this.sharedStringSource; } - protected void setSharedStringSource(SharedStringSource sharedStringSource) { + //TODO do we really need setSharedStringSource? + protected void setSharedStringSource(SharedStringsTable sharedStringSource) { this.sharedStringSource = sharedStringSource; } + /** + * Return a object representing a collection of shared objects used for styling content, + * e.g. fonts, cell styles, colors, etc. + */ public StylesSource getStylesSource() { return this.stylesSource; } + //TODO do we really need setStylesSource? protected void setStylesSource(StylesSource stylesSource) { this.stylesSource = stylesSource; } - public CreationHelper getCreationHelper() { + /** + * Returns an object that handles instantiating concrete + * classes of the various instances for XSSF. + */ + public XSSFCreationHelper getCreationHelper() { return new XSSFCreationHelper(this); } + /** + * Determines whether a workbook contains the provided sheet name. + * + * @param name the name to test (case insensitive match) + * @param excludeSheetIdx the sheet to exclude from the check or -1 to include all sheets in the check. + * @return true if the sheet contains the name, false otherwise. + */ private boolean doesContainsSheetName(String name, int excludeSheetIdx) { CTSheet[] ctSheetArray = workbook.getSheets().getSheetArray(); for (int i = 0; i < ctSheetArray.length; i++) { @@ -789,4 +947,32 @@ public class XSSFWorkbook extends POIXMLDocument implements Workbook { } return false; } + + private static void validateSheetName(String sheetName) { + if (sheetName == null) { + throw new IllegalArgumentException("sheetName must not be null"); + } + int len = sheetName.length(); + if (len < 1 || len > 31) { + throw new IllegalArgumentException("sheetName '" + sheetName + + "' is invalid - must be 1-30 characters long"); + } + for (int i=0; i<len; i++) { + char ch = sheetName.charAt(i); + switch (ch) { + case '/': + case '\\': + case '?': + case '*': + case ']': + case '[': + break; + default: + // all other chars OK + continue; + } + throw new IllegalArgumentException("Invalid char (" + ch + + ") found at index (" + i + ") in sheet name '" + sheetName + "'"); + } + } } diff --git a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java index d57b824467..f947d7bd14 100644 --- a/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java +++ b/src/ooxml/java/org/apache/poi/xssf/usermodel/extensions/XSSFCellBorder.java @@ -75,10 +75,22 @@ public class XSSFCellBorder { private CTBorderPr getBorder(BorderSide side) { switch (side) { - case TOP: return border.getTop(); - case RIGHT: return border.getRight(); - case BOTTOM: return border.getBottom(); - case LEFT: return border.getLeft(); + case TOP: { + CTBorderPr borderPr = border.isSetTop() ? border.getTop() : border.addNewTop(); + return borderPr; + } + case RIGHT: { + CTBorderPr borderPr = border.isSetRight() ? border.getRight() : border.addNewRight(); + return borderPr; + } + case BOTTOM:{ + CTBorderPr borderPr = border.isSetBottom() ? border.getBottom() : border.addNewBottom(); + return borderPr; + } + case LEFT:{ + CTBorderPr borderPr = border.isSetLeft() ? border.getLeft() : border.addNewLeft(); + return borderPr; + } default: throw new IllegalArgumentException("No suitable side specified for the border"); } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/XSSFTestDataSamples.java b/src/ooxml/testcases/org/apache/poi/xssf/XSSFTestDataSamples.java index aa1d78fe5f..1fe0b120c1 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/XSSFTestDataSamples.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/XSSFTestDataSamples.java @@ -17,10 +17,7 @@ package org.apache.poi.xssf;
-import java.io.ByteArrayInputStream;
-import java.io.ByteArrayOutputStream;
-import java.io.IOException;
-import java.io.InputStream;
+import java.io.*;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@@ -47,15 +44,20 @@ public class XSSFTestDataSamples { }
}
public static <R extends Workbook> R writeOutAndReadBack(R wb) {
- ByteArrayOutputStream baos = new ByteArrayOutputStream(8192);
Workbook result;
try {
- wb.write(baos);
- InputStream is = new ByteArrayInputStream(baos.toByteArray());
if (wb instanceof HSSFWorkbook) {
+ ByteArrayOutputStream baos = new ByteArrayOutputStream(8192);
+ wb.write(baos);
+ InputStream is = new ByteArrayInputStream(baos.toByteArray());
result = new HSSFWorkbook(is);
} else if (wb instanceof XSSFWorkbook) {
- Package pkg = Package.open(is);
+ File tmp = File.createTempFile("poi-ooxml-", ".xlsx");
+ tmp.deleteOnExit();
+ FileOutputStream out = new FileOutputStream(tmp);
+ wb.write(out);
+ out.close();
+ Package pkg = Package.open(tmp.getAbsolutePath());
result = new XSSFWorkbook(pkg);
} else {
throw new RuntimeException("Unexpected workbook type ("
diff --git a/src/ooxml/testcases/org/apache/poi/xssf/model/TestCommentsTable.java b/src/ooxml/testcases/org/apache/poi/xssf/model/TestCommentsTable.java index 418ddec476..c37d1d8b50 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/model/TestCommentsTable.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/model/TestCommentsTable.java @@ -30,6 +30,7 @@ import org.apache.poi.xssf.usermodel.XSSFComment; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; +import org.apache.poi.xssf.XSSFTestDataSamples; import org.openxml4j.opc.Package; import org.openxml4j.opc.PackagePart; import org.openxml4j.opc.PackagingURIHelper; @@ -212,12 +213,8 @@ public class TestCommentsTable extends TestCase { // Save, and re-load the file - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - workbook.write(baos); - - ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray()); - workbook = new XSSFWorkbook(Package.open(bais)); - + workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook); + // Check we still have comments where we should do sheet1 = workbook.getSheetAt(0); sheet2 = (XSSFSheet)workbook.getSheetAt(1); @@ -259,12 +256,8 @@ public class TestCommentsTable extends TestCase { sheet1.getRow(12).getCell(2).getCellComment().getAuthor()); // Save, and re-load the file - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - workbook.write(baos); - - ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray()); - workbook = new XSSFWorkbook(Package.open(bais)); - + workbook = XSSFTestDataSamples.writeOutAndReadBack(workbook); + // Check we still have comments where we should do sheet1 = workbook.getSheetAt(0); assertNotNull(sheet1.getRow(4).getCell(2).getCellComment()); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java index 570cc077b4..b3f3e533dd 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFBugs.java @@ -17,120 +17,107 @@ package org.apache.poi.xssf.usermodel; -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; import java.io.File; -import java.io.FileOutputStream; import junit.framework.TestCase; import org.openxml4j.opc.Package; import org.openxml4j.opc.PackagePart; import org.openxml4j.opc.PackagingURIHelper; +import org.apache.poi.xssf.XSSFTestDataSamples; public class TestXSSFBugs extends TestCase { - private String getFilePath(String file) { - File xml = new File( - System.getProperty("HSSF.testdata.path") + - File.separator + file - ); - assertTrue(xml.exists()); - - return xml.toString(); - } - - private Package saveAndOpen(XSSFWorkbook wb) throws Exception { - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - wb.write(baos); - ByteArrayInputStream inp = new ByteArrayInputStream( - baos.toByteArray() - ); - Package pkg = Package.open(inp); - return pkg; - } - - /** - * Named ranges had the right reference, but - * the wrong sheet name - */ - public void test45430() throws Exception { - XSSFWorkbook wb = new XSSFWorkbook(getFilePath("45430.xlsx")); - assertFalse(wb.isMacroEnabled()); - assertEquals(3, wb.getNumberOfNames()); - - assertEquals(0, wb.getNameAt(0).getCTName().getLocalSheetId()); - assertFalse(wb.getNameAt(0).getCTName().isSetLocalSheetId()); - assertEquals("SheetA!$A$1", wb.getNameAt(0).getReference()); - assertEquals("SheetA", wb.getNameAt(0).getSheetName()); - - assertEquals(0, wb.getNameAt(1).getCTName().getLocalSheetId()); - assertFalse(wb.getNameAt(1).getCTName().isSetLocalSheetId()); - assertEquals("SheetB!$A$1", wb.getNameAt(1).getReference()); - assertEquals("SheetB", wb.getNameAt(1).getSheetName()); - - assertEquals(0, wb.getNameAt(2).getCTName().getLocalSheetId()); - assertFalse(wb.getNameAt(2).getCTName().isSetLocalSheetId()); - assertEquals("SheetC!$A$1", wb.getNameAt(2).getReference()); - assertEquals("SheetC", wb.getNameAt(2).getSheetName()); - - // Save and re-load, still there - Package nPkg = saveAndOpen(wb); - XSSFWorkbook nwb = new XSSFWorkbook(nPkg); - assertEquals(3, nwb.getNumberOfNames()); - assertEquals("SheetA!$A$1", nwb.getNameAt(0).getReference()); - } - - /** - * We should carry vba macros over after save - */ - public void test45431() throws Exception { - Package pkg = Package.open(getFilePath("45431.xlsm")); - XSSFWorkbook wb = new XSSFWorkbook(pkg); - assertTrue(wb.isMacroEnabled()); - - // Check the various macro related bits can be found - PackagePart vba = pkg.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - // And the drawing bit - PackagePart drw = pkg.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - - // Save and re-open, both still there - Package nPkg = saveAndOpen(wb); - XSSFWorkbook nwb = new XSSFWorkbook(nPkg); - assertTrue(nwb.isMacroEnabled()); - - vba = nPkg.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - drw = nPkg.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - // And again, just to be sure - nPkg = saveAndOpen(nwb); - nwb = new XSSFWorkbook(nPkg); - assertTrue(nwb.isMacroEnabled()); - - vba = nPkg.getPart( - PackagingURIHelper.createPartName("/xl/vbaProject.bin") - ); - assertNotNull(vba); - drw = nPkg.getPart( - PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") - ); - assertNotNull(drw); - - // For testing with excel + private String getFilePath(String file) { + File xml = new File( + System.getProperty("HSSF.testdata.path") + + File.separator + file + ); + assertTrue(xml.exists()); + + return xml.toString(); + } + + /** + * Named ranges had the right reference, but + * the wrong sheet name + */ + public void test45430() throws Exception { + XSSFWorkbook wb = new XSSFWorkbook(getFilePath("45430.xlsx")); + assertFalse(wb.isMacroEnabled()); + assertEquals(3, wb.getNumberOfNames()); + + assertEquals(0, wb.getNameAt(0).getCTName().getLocalSheetId()); + assertFalse(wb.getNameAt(0).getCTName().isSetLocalSheetId()); + assertEquals("SheetA!$A$1", wb.getNameAt(0).getReference()); + assertEquals("SheetA", wb.getNameAt(0).getSheetName()); + + assertEquals(0, wb.getNameAt(1).getCTName().getLocalSheetId()); + assertFalse(wb.getNameAt(1).getCTName().isSetLocalSheetId()); + assertEquals("SheetB!$A$1", wb.getNameAt(1).getReference()); + assertEquals("SheetB", wb.getNameAt(1).getSheetName()); + + assertEquals(0, wb.getNameAt(2).getCTName().getLocalSheetId()); + assertFalse(wb.getNameAt(2).getCTName().isSetLocalSheetId()); + assertEquals("SheetC!$A$1", wb.getNameAt(2).getReference()); + assertEquals("SheetC", wb.getNameAt(2).getSheetName()); + + // Save and re-load, still there + XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb); + assertEquals(3, nwb.getNumberOfNames()); + assertEquals("SheetA!$A$1", nwb.getNameAt(0).getReference()); + } + + /** + * We should carry vba macros over after save + */ + public void test45431() throws Exception { + Package pkg = Package.open(getFilePath("45431.xlsm")); + XSSFWorkbook wb = new XSSFWorkbook(pkg); + assertTrue(wb.isMacroEnabled()); + + // Check the various macro related bits can be found + PackagePart vba = pkg.getPart( + PackagingURIHelper.createPartName("/xl/vbaProject.bin") + ); + assertNotNull(vba); + // And the drawing bit + PackagePart drw = pkg.getPart( + PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") + ); + assertNotNull(drw); + + + // Save and re-open, both still there + XSSFWorkbook nwb = XSSFTestDataSamples.writeOutAndReadBack(wb); + Package nPkg = nwb.getPackage(); + assertTrue(nwb.isMacroEnabled()); + + vba = nPkg.getPart( + PackagingURIHelper.createPartName("/xl/vbaProject.bin") + ); + assertNotNull(vba); + drw = nPkg.getPart( + PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") + ); + assertNotNull(drw); + + // And again, just to be sure + nwb = XSSFTestDataSamples.writeOutAndReadBack(nwb); + nPkg = nwb.getPackage(); + assertTrue(nwb.isMacroEnabled()); + + vba = nPkg.getPart( + PackagingURIHelper.createPartName("/xl/vbaProject.bin") + ); + assertNotNull(vba); + drw = nPkg.getPart( + PackagingURIHelper.createPartName("/xl/drawings/vmlDrawing1.vml") + ); + assertNotNull(drw); + + // For testing with excel // FileOutputStream fout = new FileOutputStream("/tmp/foo.xlsm"); // nwb.write(fout); // fout.close(); - } + } } diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFComment.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFComment.java index 4a943597e6..c2ddb01bae 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFComment.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFComment.java @@ -17,9 +17,6 @@ package org.apache.poi.xssf.usermodel; -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; - import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Comment; @@ -27,6 +24,7 @@ import org.apache.poi.ss.usermodel.RichTextString; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.model.CommentsTable; +import org.apache.poi.xssf.XSSFTestDataSamples; import org.openxml4j.opc.Package; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAuthors; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; @@ -135,7 +133,7 @@ public class TestXSSFComment extends TestCase { */ public void testCreateSave() throws Exception { XSSFWorkbook wb = new XSSFWorkbook(); - XSSFSheet s1 = (XSSFSheet)wb.createSheet(); + XSSFSheet s1 = wb.createSheet(); Row r1 = s1.createRow(0); Cell r1c1 = r1.createCell(0); r1c1.setCellValue(2.2); @@ -150,12 +148,8 @@ public class TestXSSFComment extends TestCase { assertEquals(1, s1.getNumberOfComments()); // Save and re-load - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - wb.write(baos); - ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray()); - - wb = new XSSFWorkbook(Package.open(bais)); - s1 = (XSSFSheet)wb.getSheetAt(0); + wb = XSSFTestDataSamples.writeOutAndReadBack(wb); + s1 = wb.getSheetAt(0); assertEquals(1, s1.getNumberOfComments()); assertNotNull(s1.getRow(0).getCell(0).getCellComment()); @@ -171,12 +165,9 @@ public class TestXSSFComment extends TestCase { assertEquals(2, s1.getNumberOfComments()); // Save and re-load - baos = new ByteArrayOutputStream(); - wb.write(baos); - bais = new ByteArrayInputStream(baos.toByteArray()); - - wb = new XSSFWorkbook(Package.open(bais)); - s1 = (XSSFSheet)wb.getSheetAt(0); + + wb = XSSFTestDataSamples.writeOutAndReadBack(wb); + s1 = wb.getSheetAt(0); assertEquals(2, s1.getNumberOfComments()); assertNotNull(s1.getCellComment(0, 0)); diff --git a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFHyperlink.java b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFHyperlink.java index 2c469c49b3..0e5141db78 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFHyperlink.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFHyperlink.java @@ -17,8 +17,6 @@ package org.apache.poi.xssf.usermodel; -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; import java.io.File; import junit.framework.TestCase; @@ -27,6 +25,7 @@ import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Hyperlink; import org.apache.poi.ss.usermodel.Row; +import org.apache.poi.xssf.XSSFTestDataSamples; import org.openxml4j.opc.Package; public class TestXSSFHyperlink extends TestCase { @@ -79,12 +78,9 @@ public class TestXSSFHyperlink extends TestCase { // Write out, and check - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - workbook.write(baos); - ByteArrayInputStream bais = new ByteArrayInputStream(baos.toByteArray()); - + // Load up again, check all links still there - XSSFWorkbook wb2 = new XSSFWorkbook(Package.open(bais)); + XSSFWorkbook wb2 = XSSFTestDataSamples.writeOutAndReadBack(workbook); assertEquals(3, wb2.getNumberOfSheets()); assertNotNull(wb2.getSheetAt(0)); assertNotNull(wb2.getSheetAt(1)); @@ -119,18 +115,14 @@ public class TestXSSFHyperlink extends TestCase { // Save and re-load once more - baos = new ByteArrayOutputStream(); - wb2.write(baos); - bais = new ByteArrayInputStream(baos.toByteArray()); - - - XSSFWorkbook wb3 = new XSSFWorkbook(Package.open(bais)); + + XSSFWorkbook wb3 = XSSFTestDataSamples.writeOutAndReadBack(wb2); assertEquals(3, wb3.getNumberOfSheets()); assertNotNull(wb3.getSheetAt(0)); assertNotNull(wb3.getSheetAt(1)); assertNotNull(wb3.getSheetAt(2)); - sheet = (XSSFSheet)wb3.getSheetAt(0); + sheet = wb3.getSheetAt(0); assertEquals(5, sheet.getNumHyperlinks()); doTestHyperlinkContents(sheet); 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 2ccedebf8c..5060125b0e 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFSheet.java @@ -17,8 +17,6 @@ package org.apache.poi.xssf.usermodel; -import java.io.ByteArrayInputStream; -import java.io.ByteArrayOutputStream; import java.io.File; import java.util.Iterator; import junit.framework.TestCase; @@ -30,11 +28,7 @@ import org.apache.poi.ss.util.Region; import org.apache.poi.xssf.model.CommentsTable; import org.apache.poi.xssf.model.StylesTable; import org.apache.poi.xssf.usermodel.helpers.ColumnHelper; -import org.apache.poi.hssf.usermodel.HSSFSheet; -import org.apache.poi.hssf.usermodel.HSSFWorkbook; -import org.apache.poi.hssf.usermodel.HSSFRow; -import org.apache.poi.hssf.usermodel.HSSFCell; -import org.openxml4j.opc.Package; +import org.apache.poi.xssf.XSSFTestDataSamples; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCol; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCols; import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment; @@ -349,11 +343,7 @@ public class TestXSSFSheet extends TestCase { // Save and reload - ByteArrayOutputStream baos = new ByteArrayOutputStream(); - workbook.write(baos); - XSSFWorkbook wb = new XSSFWorkbook(Package.open( - new ByteArrayInputStream(baos.toByteArray()) - )); + XSSFWorkbook wb = XSSFTestDataSamples.writeOutAndReadBack(workbook); hdr = (XSSFOddHeader)wb.getSheetAt(0).getHeader(); ftr = (XSSFOddFooter)wb.getSheetAt(0).getFooter(); 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 4bfaf3ac2a..a8cdda1f55 100644 --- a/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java +++ b/src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFWorkbook.java @@ -441,7 +441,7 @@ public final class TestXSSFWorkbook extends TestCase { // Now, an existing file with named ranges workbook = XSSFTestDataSamples.openSampleWorkbook("WithVariousData.xlsx"); - + assertEquals(2, workbook.getNumberOfNames()); assertEquals("Sheet1!$A$2:$A$7", workbook.getNameAt(0).getReference()); assertEquals("AllANumbers", workbook.getNameAt(0).getNameName()); |