/* ====================================================================
   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 static org.apache.poi.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
import static org.apache.poi.xssf.usermodel.helpers.XSSFPasswordHelper.setPassword;
import static org.apache.poi.xssf.usermodel.helpers.XSSFPasswordHelper.validatePassword;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.regex.Pattern;

import javax.xml.namespace.QName;

import org.apache.commons.collections4.ListValuedMap;
import org.apache.commons.collections4.multimap.ArrayListValuedHashMap;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.POIXMLException;
import org.apache.poi.POIXMLProperties;
import org.apache.poi.hpsf.ClassID;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.PackageRelationshipTypes;
import org.apache.poi.openxml4j.opc.PackagingURIHelper;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.poifs.crypt.HashAlgorithm;
import org.apache.poi.poifs.filesystem.DirectoryNode;
import org.apache.poi.poifs.filesystem.Ole10Native;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.SheetNameFormatter;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.IndexedUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.SheetVisibility;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.util.Beta;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Internal;
import org.apache.poi.util.NotImplemented;
import org.apache.poi.util.POILogFactory;
import org.apache.poi.util.POILogger;
import org.apache.poi.util.PackageHelper;
import org.apache.poi.util.Removal;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.XLSBUnsupportedException;
import org.apache.poi.xssf.model.CalculationChain;
import org.apache.poi.xssf.model.ExternalLinksTable;
import org.apache.poi.xssf.model.MapInfo;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.model.ThemesTable;
import org.apache.poi.xssf.usermodel.helpers.XSSFFormulaUtils;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlObject;
import org.apache.xmlbeans.XmlOptions;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookView;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTBookViews;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCalcPr;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedName;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDefinedNames;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTExternalReference;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCache;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotCaches;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheets;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookProtection;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STCalcMode;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STSheetState;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;

/**
 * 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 {
    private static final Pattern COMMA_PATTERN = Pattern.compile(",");

    /**
     * Width of one character of the default font in pixels. Same for Calibry and Arial.
     * @deprecated POI 3.17 beta 1
     * @see Units#DEFAULT_CHARACTER_WIDTH
     */
    @Removal(version="3.19")
    public static final float DEFAULT_CHARACTER_WIDTH = Units.DEFAULT_CHARACTER_WIDTH;

    /**
     * Excel silently truncates long sheet names to 31 chars.
     * This constant is used to ensure uniqueness in the first 31 chars
     */
    private static final int MAX_SENSITIVE_SHEET_NAME_LEN = 31;

    /**
     * Images formats supported by XSSF but not by HSSF
     */
    public static final int PICTURE_TYPE_GIF = 8;
    public static final int PICTURE_TYPE_TIFF = 9;
    public static final int PICTURE_TYPE_EPS = 10;
    public static final int PICTURE_TYPE_BMP = 11;
    public static final int PICTURE_TYPE_WPG = 12;

    /**
     * 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, keyed by lower-case name
     */
    private ListValuedMap<String, XSSFName> namedRangesByName;

    /**
     * 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 StylesTable stylesSource;

    /**
     * The locator of user-defined functions.
     * By default includes functions from the Excel Analysis Toolpack
     */
    private IndexedUDFFinder _udfFinder = new IndexedUDFFinder(AggregatingUDFFinder.DEFAULT);

    /**
     * TODO
     */
    private CalculationChain calcChain;
    
    /**
     * External Links, for referencing names or cells in other workbooks.
     */
    private List<ExternalLinksTable> externalLinks;

    /**
     * A collection of custom XML mappings
     */
    private MapInfo mapInfo;

    /**
     * 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 = MissingCellPolicy.RETURN_NULL_AND_BLANK;

    /**
     * array of pictures for this workbook
     */
    private List<XSSFPictureData> pictures;

    private static POILogger logger = POILogFactory.getLogger(XSSFWorkbook.class);

    /**
     * cached instance of XSSFCreationHelper for this workbook
     * @see #getCreationHelper()
     */
    private XSSFCreationHelper _creationHelper;

    /**
     * List of all pivot tables in workbook
     */
    private List<XSSFPivotTable> pivotTables;
    private List<CTPivotCache> pivotCaches;


    /**
     * Create a new SpreadsheetML workbook.
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     */
    public XSSFWorkbook() {
        this(XSSFWorkbookType.XLSX);
    }

    /**
     * Create a new SpreadsheetML workbook.
     * @param workbookType The type of workbook to make (.xlsx or .xlsm).
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     * It makes calls to the following methods: OnWorkbookCreate, and super
     */
    public XSSFWorkbook(XSSFWorkbookType workbookType) {
        super(newPackage(workbookType));
        onWorkbookCreate();
    }

    /**
     * Constructs a XSSFWorkbook object given a OpenXML4J <code>Package</code> object,
     *  see <a href="http://poi.apache.org/oxml4j/">http://poi.apache.org/oxml4j/</a>.
     * 
     * <p>Once you have finished working with the Workbook, you should close the package
     * by calling either {@link #close()} or {@link OPCPackage#close()}, to avoid 
     * leaving file handles open.
     * 
     * <p>Creating a XSSFWorkbook from a file-backed OPC Package has a lower memory
     *  footprint than an InputStream backed one.
     *
     * @param pkg the OpenXML4J <code>OPC Package</code> object.
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     * It makes calls to the following methods: beforeDocumentRead, load, getInstance, isSetBookViews, addNewBookViews, addNewWorkbookView, and setActiveTab
     */
    public XSSFWorkbook(OPCPackage pkg) throws IOException {
        super(pkg);
        
        beforeDocumentRead();
        
        // Build a tree of POIXMLDocumentParts, this workbook being the root
        load(XSSFFactory.getInstance());
        
        // some broken Workbooks miss this...
        if(!workbook.isSetBookViews()) {
            CTBookViews bvs = workbook.addNewBookViews();
            CTBookView bv = bvs.addNewWorkbookView();
            bv.setActiveTab(0);
        }
    }

    /**
     * Constructs a XSSFWorkbook object, by buffering the whole stream into memory
     *  and then opening an {@link OPCPackage} object for it.
     * 
     * <p>Using an {@link InputStream} requires more memory than using a File, so
     *  if a {@link File} is available then you should instead do something like
     *   <pre><code>
     *       OPCPackage pkg = OPCPackage.open(path);
     *       XSSFWorkbook wb = new XSSFWorkbook(pkg);
     *       // work with the wb object
     *       ......
     *       pkg.close(); // gracefully closes the underlying zip file
     *   </code></pre>
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     * It makes calls to the following methods: beforeDocumentRead, load, getInstance, isSetBookViews, addNewBookViews, addNewWorkbookView, and setActiveTab
     */
    public XSSFWorkbook(InputStream is) throws IOException {
        super(PackageHelper.open(is));

        beforeDocumentRead();
        
        // Build a tree of POIXMLDocumentParts, this workbook being the root
        load(XSSFFactory.getInstance());

        // some broken Workbooks miss this...
        if(!workbook.isSetBookViews()) {
            CTBookViews bvs = workbook.addNewBookViews();
            CTBookView bv = bvs.addNewWorkbookView();
            bv.setActiveTab(0);
        }
    }

    /**
     * Constructs a XSSFWorkbook object from a given file.
     * 
     * <p>Once you have finished working with the Workbook, you should close 
     * the package by calling  {@link #close()}, to avoid leaving file 
     * handles open.
     * 
     * <p>Opening a XSSFWorkbook from a file has a lower memory footprint 
     *  than opening from an InputStream
     *  
     * @param file   the file to open
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     * It makes calls to the following methods: open
     */
    public XSSFWorkbook(File file) throws IOException, InvalidFormatException {
        this(OPCPackage.open(file));
    }
    
    /**
     * Constructs a XSSFWorkbook object given a file name.
     *
     * 
     * <p>Once you have finished working with the Workbook, you should close 
     * the package by calling  {@link #close()}, to avoid leaving file 
     * handles open.
     * 
     * <p>Opening a XSSFWorkbook from a file has a lower memory footprint 
     *  than opening from an InputStream
     *
     * @param path   the file name.
     *
     * XSSFWorkbook is a constructor stereotype that returns an object: XSSFWorkbook
     * It makes calls to the following methods: beforeDocumentRead, load, getInstance, isSetBookViews, addNewBookViews, addNewWorkbookView, and setActiveTab
     */
    public XSSFWorkbook(String path) throws IOException {
        this(openPackage(path));
    }
    

    /*
     *
     * beforeDocumentRead is a command stereotype.
     * It modifies the following member variables: pivotCaches, and pivotTables.
     * It makes calls to the following method(s): pivotCaches, and pivotTables
     */    
    protected void beforeDocumentRead() {
        // Ensure it isn't a XLSB file, which we don't support
        if (getCorePart().getContentType().equals(XSSFRelation.XLSB_BINARY_WORKBOOK.getContentType())) {
            throw new XLSBUnsupportedException();
        }

        // Create arrays for parts attached to the workbook itself
        pivotTables = new ArrayList<XSSFPivotTable>();
        pivotCaches = new ArrayList<CTPivotCache>();
    }


    /*
     *
     * onDocumentRead is a command stereotype.
     * It modifies the following member variables: calcChain, externalLinks, mapInfo, sharedStringSource, sheets, and stylesSourceworkbook.
     * It modifies more than 5 data members.
     * It modifies member variables via the following method calls: XSSFFactory via getInstance, externalLinks via add, logger via log, stylesSource via setTheme, stylesSource via setWorkbook, this via workbook.getExternalReferences, and this via workbook.getSheets.
     * It makes calls to the following method(s): externalLinks, getRelationship, parseSheet, reprocessNamedRanges, sharedStringSource, sheets, and stylesSource
     */    
    @Override
    protected void onDocumentRead() throws IOException {
        try {
            WorkbookDocument doc = WorkbookDocument.Factory.parse(getPackagePart().getInputStream(), DEFAULT_XML_OPTIONS);
            this.workbook = doc.getWorkbook();

            ThemesTable theme = null;
            Map<String, XSSFSheet> shIdMap = new HashMap<String, XSSFSheet>();
            Map<String, ExternalLinksTable> elIdMap = new HashMap<String, ExternalLinksTable>();
            for(RelationPart rp : getRelationParts()){
                POIXMLDocumentPart p = rp.getDocumentPart();
                if(p instanceof SharedStringsTable) {
                    sharedStringSource = (SharedStringsTable)p;
                } else if(p instanceof StylesTable) {
                    stylesSource = (StylesTable)p;
                } else if(p instanceof ThemesTable) {
                    theme = (ThemesTable)p;
                } else if(p instanceof CalculationChain) {
                    calcChain = (CalculationChain)p;
                } else if(p instanceof MapInfo) {
                    mapInfo = (MapInfo)p;
                } else if (p instanceof XSSFSheet) {
                    shIdMap.put(rp.getRelationship().getId(), (XSSFSheet)p);
                } else if (p instanceof ExternalLinksTable) {
                    elIdMap.put(rp.getRelationship().getId(), (ExternalLinksTable)p);
                }
            }
            boolean packageReadOnly = (getPackage().getPackageAccess() == PackageAccess.READ);
            
            if (stylesSource == null) {
                // Create Styles if it is missing
                if (packageReadOnly) {
                    stylesSource = new StylesTable();
                } else {
                    stylesSource = (StylesTable)createRelationship(XSSFRelation.STYLES, XSSFFactory.getInstance());
                }
            }
            stylesSource.setWorkbook(this);
            stylesSource.setTheme(theme);

            if (sharedStringSource == null) {
                // Create SST if it is missing
                if (packageReadOnly) {
                    sharedStringSource = new SharedStringsTable();
                } else {
                    sharedStringSource = (SharedStringsTable)createRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.getInstance());
                }
            }
            
            // Load individual sheets. The order of sheets is defined by the order
            //  of CTSheet elements in the workbook
            sheets = new ArrayList<XSSFSheet>(shIdMap.size());
            //noinspection deprecation
            for (CTSheet ctSheet : this.workbook.getSheets().getSheetArray()) {
                parseSheet(shIdMap, ctSheet);
            }
            
            // Load the external links tables. Their order is defined by the order 
            //  of CTExternalReference elements in the workbook
            externalLinks = new ArrayList<ExternalLinksTable>(elIdMap.size());
            if (this.workbook.isSetExternalReferences()) {
                for (CTExternalReference er : this.workbook.getExternalReferences().getExternalReferenceArray()) {
                    ExternalLinksTable el = elIdMap.get(er.getId());
                    if(el == null) {
                        logger.log(POILogger.WARN, "ExternalLinksTable with r:id " + er.getId()+ " was defined, but didn't exist in package, skipping");
                        continue;
                    }
                    externalLinks.add(el);
                }
            }
            
            // Process the named ranges
            reprocessNamedRanges();
        } catch (XmlException e) {
            throw new POIXMLException(e);
        }
    }

    /**
     * Not normally to be called externally, but possibly to be overridden to avoid
     * the DOM based parse of large sheets (see examples).
     */
    public void parseSheet(Map<String, XSSFSheet> shIdMap, CTSheet ctSheet) {
        XSSFSheet sh = shIdMap.get(ctSheet.getId());
        if(sh == null) {
            logger.log(POILogger.WARN, "Sheet with name " + ctSheet.getName() + " and r:id " + ctSheet.getId()+ " was defined, but didn't exist in package, skipping");
            return;
        }
        sh.sheet = ctSheet;
        sh.onDocumentRead();
        sheets.add(sh);
    }

    /**
     * Create a new CTWorkbook with all values set to default
     *
     * onWorkbookCreate is a command stereotype.
     * It modifies the following member variables: namedRanges, namedRangesByName, pivotTables, sharedStringSource, sheets, stylesSource, and workbook.
     * It modifies more than 5 data members.
     * It modifies member variables via the following method calls: XSSFFactory via getInstance, stylesSource via setWorkbook, and workbook via addNewSheets.
     * It makes calls to the following method(s): getUnderlyingProperties, namedRanges, namedRangesByName, pivotTables, sharedStringSource, sheets, and stylesSource
     */
    private void onWorkbookCreate() {
        workbook = CTWorkbook.Factory.newInstance();

        // don't EVER use the 1904 date system
        CTWorkbookPr workbookPr = workbook.addNewWorkbookPr();
        workbookPr.setDate1904(false);

        CTBookViews bvs = workbook.addNewBookViews();
        CTBookView bv = bvs.addNewWorkbookView();
        bv.setActiveTab(0);
        workbook.addNewSheets();

        POIXMLProperties.ExtendedProperties expProps = getProperties().getExtendedProperties();
        expProps.getUnderlyingProperties().setApplication(DOCUMENT_CREATOR);

        sharedStringSource = (SharedStringsTable)createRelationship(XSSFRelation.SHARED_STRINGS, XSSFFactory.getInstance());
        stylesSource = (StylesTable)createRelationship(XSSFRelation.STYLES, XSSFFactory.getInstance());
        stylesSource.setWorkbook(this);

        namedRanges = new ArrayList<XSSFName>();
        namedRangesByName = new ArrayListValuedHashMap<String, XSSFName>();
        sheets = new ArrayList<XSSFSheet>();
        pivotTables = new ArrayList<XSSFPivotTable>();
    }

    /**
     * Create a new SpreadsheetML package and setup the default minimal content
     *
     * newPackage is a set stereotype.
     */
    protected static OPCPackage newPackage(XSSFWorkbookType workbookType) {
        try {
            OPCPackage pkg = OPCPackage.create(new ByteArrayOutputStream());
            // 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, workbookType.getContentType());

            pkg.getPackageProperties().setCreatorProperty(DOCUMENT_CREATOR);

            return pkg;
        } catch (Exception e){
            throw new POIXMLException(e);
        }
    }

    /**
     * Return the underlying XML bean
     *
     * @return the underlying CTWorkbook bean
     *
     * getCTWorkbook is a get stereotype.
     * It returns one data member: workbook.
     */
    @Internal
    public CTWorkbook getCTWorkbook() {
        return this.workbook;
    }

    /**
     * Adds a picture to the workbook.
     *
     * @param pictureData       The bytes of the picture
     * @param format            The format of the picture.
     *
     * @return the index to this picture (0 based), the added picture can be obtained from {@link #getAllPictures()} .
     * @see Workbook#PICTURE_TYPE_EMF
     * @see Workbook#PICTURE_TYPE_WMF
     * @see Workbook#PICTURE_TYPE_PICT
     * @see Workbook#PICTURE_TYPE_JPEG
     * @see Workbook#PICTURE_TYPE_PNG
     * @see Workbook#PICTURE_TYPE_DIB
     * @see #getAllPictures()
     *
     * addPicture is a get stereotype.
     * It returns one data member: imageNumber - 1.
     * It delegates to a call: out via close, out via write, and pictures via add.
     */
    @Override
    public int addPicture(byte[] pictureData, int format) {
        int imageNumber = getAllPictures().size() + 1;
        XSSFPictureData img = (XSSFPictureData)createRelationship(XSSFPictureData.RELATIONS[format], XSSFFactory.getInstance(), imageNumber, true).getDocumentPart();
        try {
            OutputStream out = img.getPackagePart().getOutputStream();
            out.write(pictureData);
            out.close();
        } catch (IOException e){
            throw new POIXMLException(e);
        }
        pictures.add(img);
        return imageNumber - 1;
    }

    /**
     * Adds a picture to the workbook.
     *
     * @param is                The sream to read image from
     * @param format            The format of the picture.
     *
     * @return the index to this picture (0 based), the added picture can be obtained from {@link #getAllPictures()} .
     * @see Workbook#PICTURE_TYPE_EMF
     * @see Workbook#PICTURE_TYPE_WMF
     * @see Workbook#PICTURE_TYPE_PICT
     * @see Workbook#PICTURE_TYPE_JPEG
     * @see Workbook#PICTURE_TYPE_PNG
     * @see Workbook#PICTURE_TYPE_DIB
     * @see #getAllPictures()
     *
     * addPicture is a get stereotype.
     * It returns one data member: imageNumber - 1.
     * It delegates to a call: IOUtils via copy, out via close, and pictures via add.
     */
    public int addPicture(InputStream is, int format) throws IOException {
        int imageNumber = getAllPictures().size() + 1;
        XSSFPictureData img = createRelationship(XSSFPictureData.RELATIONS[format], XSSFFactory.getInstance(), imageNumber, true).getDocumentPart();
        OutputStream out = img.getPackagePart().getOutputStream();
        IOUtils.copy(is, out);
        out.close();
        pictures.add(img);
        return imageNumber - 1;
    }

    /**
     * Create an XSSFSheet from an existing sheet in the XSSFWorkbook.
     *  The cloned sheet is a deep copy of the original.
     *
     * @param sheetNum The index of the sheet to clone
     * @return XSSFSheet representing the cloned sheet.
     * @throws IllegalArgumentException if the sheet index in invalid
     * @throws POIXMLException if there were errors when cloning
     *
     * cloneSheet is a property stereotype.
     * It has a single return, which delegates to the following call(s): cloneSheet(sheetNum, null).
     * The return value depends on the following parameter(s): sheetNum.
     */
    @Override
    public XSSFSheet cloneSheet(int sheetNum) {
        return cloneSheet(sheetNum, null);
    }

    /**
     * Create an XSSFSheet from an existing sheet in the XSSFWorkbook.
     *  The cloned sheet is a deep copy of the original but with a new given
     *  name.
     *
     * @param sheetNum The index of the sheet to clone
     * @param newName The name to set for the newly created sheet
     * @return XSSFSheet representing the cloned sheet.
     * @throws IllegalArgumentException if the sheet index or the sheet
     *         name is invalid
     * @throws POIXMLException if there were errors when cloning
     *
     * cloneSheet is a set stereotype.
     * It modifies one member variable using the method call: logger via log.
     */
    public XSSFSheet cloneSheet(int sheetNum, String newName) {
        validateSheetIndex(sheetNum);
        XSSFSheet srcSheet = sheets.get(sheetNum);

        if (newName == null) {
            String srcName = srcSheet.getSheetName();
            newName = getUniqueSheetName(srcName);
        } else {
            validateSheetName(newName);
        }

        XSSFSheet clonedSheet = createSheet(newName);

        // copy sheet's relations
        List<RelationPart> rels = srcSheet.getRelationParts();
        // if the sheet being cloned has a drawing then remember it and re-create it too
        XSSFDrawing dg = null;
        for(RelationPart rp : rels) {
            POIXMLDocumentPart r = rp.getDocumentPart();
            // do not copy the drawing relationship, it will be re-created
            if(r instanceof XSSFDrawing) {
                dg = (XSSFDrawing)r;
                continue;
            }

            addRelation(rp, clonedSheet);
        }

        try {
            for(PackageRelationship pr : srcSheet.getPackagePart().getRelationships()) {
                if (pr.getTargetMode() == TargetMode.EXTERNAL) {
                    clonedSheet.getPackagePart().addExternalRelationship
                        (pr.getTargetURI().toASCIIString(), pr.getRelationshipType(), pr.getId());
                }
            }
        } catch (InvalidFormatException e) {
            throw new POIXMLException("Failed to clone sheet", e);
        }
        
        
        try {
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            srcSheet.write(out);
            clonedSheet.read(new ByteArrayInputStream(out.toByteArray()));
        } catch (IOException e){
            throw new POIXMLException("Failed to clone sheet", e);
        }
        CTWorksheet ct = clonedSheet.getCTWorksheet();
        if(ct.isSetLegacyDrawing()) {
            logger.log(POILogger.WARN, "Cloning sheets with comments is not yet supported.");
            ct.unsetLegacyDrawing();
        }
        if (ct.isSetPageSetup()) {
            logger.log(POILogger.WARN, "Cloning sheets with page setup is not yet supported.");
            ct.unsetPageSetup();
        }

        clonedSheet.setSelected(false);

        // clone the sheet drawing along with its relationships
        if (dg != null) {
            if(ct.isSetDrawing()) {
                // unset the existing reference to the drawing,
                // so that subsequent call of clonedSheet.createDrawingPatriarch() will create a new one
                ct.unsetDrawing();
            }
            XSSFDrawing clonedDg = clonedSheet.createDrawingPatriarch();
            // copy drawing contents
            clonedDg.getCTDrawing().set(dg.getCTDrawing());

            clonedDg = clonedSheet.createDrawingPatriarch();
            
            // Clone drawing relations
            List<RelationPart> srcRels = srcSheet.createDrawingPatriarch().getRelationParts();
            for (RelationPart rp : srcRels) {
                addRelation(rp, clonedDg);
            }
        }
        return clonedSheet;
    }
    
    /**
     * @since 3.14-Beta1
     */
    private static void addRelation(RelationPart rp, POIXMLDocumentPart target) {
        PackageRelationship rel = rp.getRelationship();
        if (rel.getTargetMode() == TargetMode.EXTERNAL) {
            target.getPackagePart().addRelationship(
                rel.getTargetURI(), rel.getTargetMode(), rel.getRelationshipType(), rel.getId());
        } else {        
            XSSFRelation xssfRel = XSSFRelation.getInstance(rel.getRelationshipType());
            if (xssfRel == null) {
                // Don't copy all relations blindly, but only the ones we know about
                throw new POIXMLException("Can't clone sheet - unknown relation type found: "+rel.getRelationshipType());
            }
            target.addRelation(rel.getId(), xssfRel, rp.getDocumentPart());
        }
    }

    
    private String getUniqueSheetName(String srcName) {
        int uniqueIndex = 2;
        String baseName = srcName;
        int bracketPos = srcName.lastIndexOf('(');
        if (bracketPos > 0 && srcName.endsWith(")")) {
            String suffix = srcName.substring(bracketPos + 1, srcName.length() - ")".length());
            try {
                uniqueIndex = Integer.parseInt(suffix.trim());
                uniqueIndex++;
                baseName = srcName.substring(0, bracketPos).trim();
            } catch (NumberFormatException e) {
                // contents of brackets not numeric
            }
        }
        while (true) {
            // Try and find the next sheet name that is unique
            String index = Integer.toString(uniqueIndex++);
            String name;
            if (baseName.length() + index.length() + 2 < 31) {
                name = baseName + " (" + index + ")";
            } else {
                name = baseName.substring(0, 31 - index.length() - 2) + "(" + index + ")";
            }

            //If the sheet name is unique, then set it otherwise move on to the next number.
            if (getSheetIndex(name) == -1) {
                return name;
            }
        }
    }

    /**
     * Create a new XSSFCellStyle and add it to the workbook's style table
     *
     * @return the new XSSFCellStyle object
     */
    @Override
    public XSSFCellStyle createCellStyle() {
        return stylesSource.createCellStyle();
    }

    /**
     * Returns the workbook's data format table (a factory for creating data format strings).
     *
     * @return the XSSFDataFormat object
     * @see org.apache.poi.ss.usermodel.DataFormat
     *
     * createDataFormat is a get stereotype.
     * It returns one data member: formatter.
     */
    @Override
    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
     *
     * createFont is a property stereotype.
     * It returns a single value: font.
     */
    @Override
    public XSSFFont createFont() {
        XSSFFont font = new XSSFFont();
        font.registerTo(stylesSource);
        return font;
    }

    /*
     *
     * createName is a property stereotype.
     * It has a single return, which delegates to the following call(s): createAndStoreName(ctName).
     */    
    @Override
    public XSSFName createName() {
        CTDefinedName ctName = CTDefinedName.Factory.newInstance();
        ctName.setName("");
        return createAndStoreName(ctName);
    }

    /*
     *
     * createAndStoreName is a property stereotype.
     * It returns a single value: name.
     * The return value depends on the following parameter(s): ctName.
     */    
    private XSSFName createAndStoreName(CTDefinedName ctName) {
        XSSFName name = new XSSFName(ctName, this);
        namedRanges.add(name);
        namedRangesByName.put(ctName.getName().toLowerCase(Locale.ENGLISH), 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.
     *
     * createSheet is a property stereotype.
     * It has a single return, which delegates to the following call(s): createSheet(sheetname).
     */
    @Override
    public XSSFSheet createSheet() {
        String sheetname = "Sheet" + (sheets.size());
        int idx = 0;
        while(getSheet(sheetname) != null) {
            sheetname = "Sheet" + idx;
            idx++;
        }
        return createSheet(sheetname);
    }

    /**
     * Create a new sheet for this Workbook and return the high level representation.
     * Use this to create new sheets.
     *
     * <p>
     *     Note that Excel allows sheet names up to 31 chars in length but other applications
     *     (such as OpenOffice) allow more. Some versions of Excel crash with names longer than 31 chars,
     *     others - truncate such names to 31 character.
     * </p>
     * <p>
     *     POI's SpreadsheetAPI silently truncates the input argument to 31 characters.
     *     Example:
     *
     *     <pre><code>
     *     Sheet sheet = workbook.createSheet("My very long sheet name which is longer than 31 chars"); // will be truncated
     *     assert 31 == sheet.getSheetName().length();
     *     assert "My very long sheet name which i" == sheet.getSheetName();
     *     </code></pre>
     * </p>
     *
     * Except the 31-character constraint, Excel applies some other rules:
     * <p>
     * Sheet name MUST be unique in the workbook and MUST NOT contain the any of the following characters:
     * <ul>
     * <li> 0x0000 </li>
     * <li> 0x0003 </li>
     * <li> colon (:) </li>
     * <li> backslash (\) </li>
     * <li> asterisk (*) </li>
     * <li> question mark (?) </li>
     * <li> forward slash (/) </li>
     * <li> opening square bracket ([) </li>
     * <li> closing square bracket (]) </li>
     * </ul>
     * The string MUST NOT begin or end with the single quote (') character.
     * </p>
     *
     * <p>
     * See {@link org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
     *      for a safe way to create valid names
     * </p>
     * @param sheetname  sheetname to set for the sheet.
     * @return Sheet representing the new sheet.
     * @throws IllegalArgumentException if the name is null or invalid
     *  or workbook already contains a sheet with this name
     * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)
     *
     * createSheet is a set stereotype.
     * It modifies one member variable using the method call: WorkbookUtil via validateSheetName, and sheets via add.
     */
    @Override
    public XSSFSheet createSheet(String sheetname) {
        if (sheetname == null) {
            throw new IllegalArgumentException("sheetName must not be null");
        }

        validateSheetName(sheetname);

        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
        if(sheetname.length() > 31) {
            sheetname = sheetname.substring(0, 31);
        }
        WorkbookUtil.validateSheetName(sheetname);

        CTSheet sheet = addSheet(sheetname);

        int sheetNumber = 1;
        outerloop:
        while(true) {
            for(XSSFSheet sh : sheets) {
                sheetNumber = (int)Math.max(sh.sheet.getSheetId() + 1, sheetNumber);
            }
            
            // Bug 57165: We also need to check that the resulting file name is not already taken
            // this can happen when moving/cloning sheets
            String sheetName = XSSFRelation.WORKSHEET.getFileName(sheetNumber);
            for(POIXMLDocumentPart relation : getRelations()) {
                if(relation.getPackagePart() != null && 
                        sheetName.equals(relation.getPackagePart().getPartName().getName())) {
                    // name is taken => try next one
                    sheetNumber++;
                    continue outerloop;
                }
            }

            // no duplicate found => use this one
            break;
        }

        RelationPart rp = createRelationship(XSSFRelation.WORKSHEET, XSSFFactory.getInstance(), sheetNumber, false);
        XSSFSheet wrapper = rp.getDocumentPart();
        wrapper.sheet = sheet;
        sheet.setId(rp.getRelationship().getId());
        sheet.setSheetId(sheetNumber);
        if (sheets.isEmpty()) {
            wrapper.setSelected(true);
        }
        sheets.add(wrapper);
        return wrapper;
    }


    /*
     *
     * validateSheetName is a command stereotype.
     */   
    private void validateSheetName(final String sheetName) throws IllegalArgumentException {
        if (containsSheet( sheetName, sheets.size() )) {
            throw new IllegalArgumentException("The workbook already contains a sheet named '" + sheetName + "'");
        }
    }

    /*
     *
     * createDialogsheet is a property stereotype.
     * It has a single return, which delegates to the following call(s): new XSSFDialogsheet(sheet).
     * The return value depends on the following parameter(s): dialogsheet, and sheetname.
     */    
    protected XSSFDialogsheet createDialogsheet(String sheetname, CTDialogsheet dialogsheet) {
        XSSFSheet sheet = createSheet(sheetname);
        return new XSSFDialogsheet(sheet);
    }

    private CTSheet addSheet(String sheetname) {
        CTSheet sheet = workbook.getSheets().addNewSheet();
        sheet.setName(sheetname);
        return sheet;
    }
    
    /**
     * Finds a font that matches the one with the supplied attributes
     */
    @Override
    public XSSFFont findFont(boolean bold, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline) {
        return stylesSource.findFont(bold, color, fontHeight, name, italic, strikeout, typeOffset, underline);
    }

    /**
     * Convenience method to get the active sheet.  The active sheet is is the sheet
     * which is currently displayed when the workbook is viewed in Excel.
     * 'Selected' sheet(s) is a distinct concept.
     *
     * getActiveSheetIndex is a get stereotype.
     * It returns one data member: (int)workbook.
     */
    @Override
    public int getActiveSheetIndex() {
        //activeTab (Active Sheet Index) Specifies an unsignedInt
        //that contains the index to the active sheet in this book view.
        return (int)workbook.getBookViews().getWorkbookViewArray(0).getActiveTab();
    }

    /**
     * Gets all pictures from the Workbook.
     *
     * @return the list of pictures (a list of {@link XSSFPictureData} objects.)
     * @see #addPicture(byte[], int)
     *
     * getAllPictures is a get stereotype.
     * It returns one data member: pictures.
     * It delegates to a call: mediaParts via size, and pictures via add.
     */
    @Override
    public List<XSSFPictureData> getAllPictures() {
        if(pictures == null){
            List<PackagePart> mediaParts = getPackage().getPartsByName(Pattern.compile("/xl/media/.*?"));
            pictures = new ArrayList<XSSFPictureData>(mediaParts.size());
            for(PackagePart part : mediaParts){
                pictures.add(new XSSFPictureData(part));
            }
        }
        return pictures; //YK: should return Collections.unmodifiableList(pictures);
    }

    /**
     * Get the cell style object at the given index
     *
     * @param idx  index within the set of styles
     * @return XSSFCellStyle object at the index
     *
     * getCellStyleAt is a get stereotype.
     * It returns one data member: stylesSource
     */
    @Override
    public XSSFCellStyle getCellStyleAt(int idx) {
        return stylesSource.getStyleAt(idx);
    }

    /**
     * Get the font at the given index number
     *
     * @param idx  index number
     * @return XSSFFont at the index
     */
    @Override
    public XSSFFont getFontAt(short idx) {
        return stylesSource.getFontAt(idx);
    }

    /**
     * Get the first named range with the given name.
     *
     * Note: names of named ranges are not unique as they are scoped by sheet.
     * {@link #getNames(String name)} returns all named ranges with the given name.
     *
     * @param name  named range name
     * @return XSSFName with the given name. <code>null</code> is returned no named range could be found.
     *
     * getName is a get stereotype.
     * It has multiple return values: list, and null.
     */
    @Override
    public XSSFName getName(String name) {
        Collection<XSSFName> list = getNames(name);
        if (list.isEmpty()) {
            return null;
        }
        return list.iterator().next();
    }

    /**
     * Get the named ranges with the given name.
     * <i>Note:</i>Excel named ranges are case-insensitive and
     * this method performs a case-insensitive search.
     *
     * @param name  named range name
     * @return list of XSSFNames with the given name. An empty list if no named ranges could be found
     */
    @Override
    public List<XSSFName> getNames(String name) {
        return Collections.unmodifiableList(namedRangesByName.get(name.toLowerCase(Locale.ENGLISH)));
    }

    /**
     * Get the named range at the given index.
     *
     * @param nameIndex the index of the named range
     * @return the XSSFName at the given index
     *
     * @deprecated 3.16. New projects should avoid accessing named ranges by index.
     */
    @Override
    @Deprecated
    public XSSFName getNameAt(int nameIndex) {
        int nNames = namedRanges.size();
        if (nNames < 1) {
            throw new IllegalStateException("There are no defined names in this workbook");
        }
        if (nameIndex < 0 || nameIndex > nNames) {
            throw new IllegalArgumentException("Specified name index " + nameIndex
                    + " is outside the allowable range (0.." + (nNames-1) + ").");
        }
        return namedRanges.get(nameIndex);
    }

    /**
     * Get a list of all the named ranges in the workbook.
     *
     * @return list of XSSFNames in the workbook
     *
     * getAllNames is a property stereotype.
     * It has a single return, which delegates to the following call(s): Collections.unmodifiableList(namedRanges).
     */
    @Override
    public List<XSSFName> getAllNames() {
        return Collections.unmodifiableList(namedRanges);
    }

    /**
     * Gets the named range index by name.
     *
     * @param name named range name
     * @return named range index. <code>-1</code> is returned if no named ranges could be found.
     *
     * @deprecated 3.16. New projects should avoid accessing named ranges by index.
     * Use {@link #getName(String)} instead.
     *
     * getNameIndex is a get stereotype.
     * It has multiple return values: -1, and namedRanges.
     */
    @Override
    @Deprecated
    public int getNameIndex(String name) {
        XSSFName nm = getName(name);
        if (nm != null) {
            return namedRanges.indexOf(nm);
        }
        return -1;
    }

    /**
     * Get the number of styles the workbook contains
     *
     * @return count of cell styles
     */
    @Override
    public int getNumCellStyles() {
        return stylesSource.getNumCellStyles();
    }

    /**
     * Get the number of fonts in the this workbook
     *
     * @return number of fonts
     *
     * getNumberOfFonts is a property stereotype.
     * It has a single return, which delegates to the following call(s): (short)stylesSource.getFonts().size().
     */
    @Override
    public short getNumberOfFonts() {
        return (short)stylesSource.getFonts().size();
    }

    /**
     * Get the number of named ranges in the this workbook
     *
     * @return number of named ranges
     */
    @Override
    public int getNumberOfNames() {
        return namedRanges.size();
    }

    /**
     * Get the number of worksheets in the this workbook
     *
     * @return number of worksheets
     */
    @Override
    public int getNumberOfSheets() {
        return sheets.size();
    }

    /**
     * Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.
     * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
     * @return String Null if no print area has been defined
     *
     * getPrintArea is a property stereotype.
     * It has multiple return values: null.
     * Some of which delegate to the following call(s): name.getRefersToFormula().
     * The return value depends on the following parameter(s): sheetIndex.
     * The return value depends on the boolean value of the following condition(s): name == null.
     */
    @Override
    public String getPrintArea(int sheetIndex) {
        XSSFName name = getBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
        if (name == null) {
            return null;
        }
        //adding one here because 0 indicates a global named region; doesnt make sense for print areas
        return name.getRefersToFormula();

    }

    /**
     * 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
     *
     * getSheet is a property stereotype.
     * It has multiple return values: null, and sheet.
     * The return value depends on the following parameter(s): name.
     * The return value depends on the boolean value of the following condition(s): name.equalsIgnoreCase(sheet.getSheetName()).
     */
    @Override
    public XSSFSheet getSheet(String name) {
        for (XSSFSheet sheet : sheets) {
            if (name.equalsIgnoreCase(sheet.getSheetName())) {
                return sheet;
            }
        }
        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
     * @throws IllegalArgumentException if the index is out of range (index
     *             0 || index = getNumberOfSheets()).
     *
     * getSheetAt is a get stereotype.
     * It returns one data member: sheets.
     */
    @Override
    public XSSFSheet getSheetAt(int index) {
        validateSheetIndex(index);
        return sheets.get(index);
    }

    /**
     * Returns the index of the sheet by his name (case insensitive match)
     *
     * @param name the sheet name
     * @return index of the sheet (0 based) or tt-1/tt if not found
     *
     * getSheetIndex is a property stereotype.
     * It has multiple return values: -1, and idx.
     * The return value depends on the following parameter(s): name.
     * The return value depends on the boolean value of the following condition(s): name.equalsIgnoreCase(sh.getSheetName()).
     */
    @Override
    public int getSheetIndex(String name) {
        int idx = 0;
        for (XSSFSheet sh : sheets) {
            if (name.equalsIgnoreCase(sh.getSheetName())) {
                return idx;
            }
            idx++;
        }
        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
     *
     * getSheetIndex is a property stereotype.
     * It has multiple return values: -1, and idx.
     * The return value depends on the following parameter(s): sheet.
     * The return value depends on the boolean value of the following condition(s): sh == sheet.
     */
    @Override
    public int getSheetIndex(Sheet sheet) {
        int idx = 0;
        for(XSSFSheet sh : sheets){
            if(sh == sheet) {
                return idx;
            }
            idx++;
        }
        return -1;
    }

    /**
     * Get the sheet name
     *
     * @param sheetIx Number
     * @return Sheet name
     *
     * getSheetName is a property stereotype.
     * It has a single return, which delegates to the following call(s): sheets.get(sheetIx).getSheetName().
     * The return value depends on the following parameter(s): sheetIx.
     */
    @Override
    public String getSheetName(int sheetIx) {
        validateSheetIndex(sheetIx);
        return sheets.get(sheetIx).getSheetName();
    }

    /**
     * Returns an iterator of the sheets in the workbook
     * in sheet order. Includes hidden and very hidden sheets.
     *
     * Note: remove() is not supported on this iterator.
     * Use {@link #removeSheetAt(int)} to remove sheets instead.
     *
     * @return an iterator of the sheets.
     */
    @Override
    public IteratorSheet sheetIterator() {
        return new SheetIteratorSheet();
    }
    
    /**
     * Alias for {@link #sheetIterator()} to allow
     * foreach loops
     * 
     * Note: remove() is not supported on this iterator.
     * Use {@link #removeSheetAt(int)} to remove sheets instead.
     * 
     * @return an iterator of the sheets.
     *
     * iterator is a property stereotype.
     * It has a single return, which delegates to the following call(s): sheetIterator().
     */
    @Override
    public IteratorSheet iterator() {
        return sheetIterator();
    }
    
    private final class SheetIteratorT extends Sheet implements IteratorT {
        final private IteratorT it;
        @SuppressWarnings("unchecked")
        public SheetIterator() {
            it = (IteratorT) sheets.iterator();
        }
        @Override
        public boolean hasNext() {
            return it.hasNext();
        }
        @Override
        public T next() throws NoSuchElementException {
            return it.next();
        }
        /**
         * Unexpected behavior may occur if sheets are reordered after iterator
         * has been created. Support for the remove method may be added in the future
         * if someone can figure out a reliable implementation.
         */
        @Override
        public void remove() throws IllegalStateException {
            throw new UnsupportedOperationException("remove method not supported on XSSFWorkbook.iterator(). "+
                    "Use Sheet.removeSheetAt(int) instead.");
        }
    }
    
    /**
     * Are we a normal workbook (.xlsx), or a
     *  macro enabled workbook (.xlsm)?
     *
     * isMacroEnabled is a predicate stereotype.
     * It has a single return, which delegates to the following call(s): getPackagePart().getContentType().equals(XSSFRelation.MACROS_WORKBOOK.getContentType()).
     */
    public boolean isMacroEnabled() {
        return getPackagePart().getContentType().equals(XSSFRelation.MACROS_WORKBOOK.getContentType());
    }

    /**
     * Remove the named range at the given index.
     *
     * @param nameIndex the index of the named range name to remove
     *
     * @deprecated 3.16. New projects should use {@link #removeName(Name)}.
     *
     * removeName is a command stereotype.
     * It makes calls to the following method(s): getNameAt, and removeName
     */
    @Override
    @Deprecated
    public void removeName(int nameIndex) {
        removeName(getNameAt(nameIndex));
    }

    /**
     * Remove the first named range found with the given name.
     * 
     * Note: names of named ranges are not unique (name + sheet
     * index is unique), so {@link #removeName(Name)} should
     * be used if possible.
     * 
     * @param name the named range name to remove
     *
     * @throws IllegalArgumentException if no named range could be found
     *
     * @deprecated 3.16. New projects should use {@link #removeName(Name)}.
     *
     * removeName is a command stereotype.
     * It makes calls to the following method(s): removeName
     */
    @Override
    @Deprecated
    public void removeName(String name) {
        ListXSSFName names = namedRangesByName.get(name.toLowerCase(Locale.ENGLISH));
        if (names.isEmpty()) {
            throw new IllegalArgumentException("Named range was not found: " + name);
        }
        removeName(names.get(0));
    }


    /**
     * As {@link #removeName(String)} is not necessarily unique
     * (name + sheet index is unique), this method is more accurate.
     *
     * @param name the name to remove.
     *
     * @throws IllegalArgumentException if the named range is not a part of this XSSFWorkbook
     */
    @Override
    public void removeName(Name name) {
        if (!namedRangesByName.removeMapping(name.getNameName().toLowerCase(Locale.ENGLISH), name)
                || !namedRanges.remove(name)) {
            throw new IllegalArgumentException("Name was not found: " + name);
        }
    }

    void updateName(XSSFName name, String oldName) {
        if (!namedRangesByName.removeMapping(oldName.toLowerCase(Locale.ENGLISH), name)) {
            throw new IllegalArgumentException("Name was not found: " + name);
        }
        namedRangesByName.put(name.getNameName().toLowerCase(Locale.ENGLISH), name);
    }


    /**
     * Delete the printarea for the sheet specified
     *
     * @param sheetIndex 0-based sheet index (0 = First Sheet)
     *
     * removePrintArea is a command stereotype.
     * It makes calls to the following method(s): removeName
     */
    @Override
    public void removePrintArea(int sheetIndex) {
        XSSFName name = getBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
        if (name != null) {
            removeName(name);
        }
    }

    /**
     * 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)
     *
     * removeSheetAt is a command stereotype.
     * It modifies member variables via the following method calls: sheets via remove.
     * It makes calls to the following method(s): onSheetDelete, removeRelation, setActiveSheet, and validateSheetIndex
     */
    @Override
    public void removeSheetAt(int index) {
        validateSheetIndex(index);

        onSheetDelete(index);

        XSSFSheet sheet = getSheetAt(index);
        removeRelation(sheet);
        sheets.remove(index);

        // only set new sheet if there are still some left
        if(sheets.size() == 0) {
            return;
        }

        // the index of the closest remaining sheet to the one just deleted
        int newSheetIndex = index;
        if (newSheetIndex = sheets.size()) {
            newSheetIndex = sheets.size()-1;
        }

        // adjust active sheet
        int active = getActiveSheetIndex();
        if(active == index) {
            // removed sheet was the active one, reset active sheet if there is still one left now
            setActiveSheet(newSheetIndex);
        } else if (active  index) {
            // removed sheet was below the active one = active is one less now
            setActiveSheet(active-1);
        }
    }

    /**
     * Gracefully remove references to the sheet being deleted
     *
     * @param index the 0-based index of the sheet to delete
     *
     * onSheetDelete is a command stereotype.
     * It modifies the following member variables: calcChain.
     * It modifies member variables via the following method calls: workbook via getSheets.
     * It makes calls to the following method(s): getSheets, removeName, and removeRelation
     */
    private void onSheetDelete(int index) {
        // remove all sheet relations
        final XSSFSheet sheet = getSheetAt(index);

        sheet.onSheetDelete();
        
        //delete the CTSheet reference from workbook.xml
        workbook.getSheets().removeSheet(index);

        //calculation chain is auxiliary, remove it as it may contain orphan references to deleted cells
        if(calcChain != null) {
            removeRelation(calcChain);
            calcChain = null;
        }

        //adjust indices of names ranges
        ListXSSFName toRemove = new ArrayListXSSFName();
        for (XSSFName nm : namedRanges) {
            CTDefinedName ct = nm.getCTName();
            if(!ct.isSetLocalSheetId()) {
                continue;
            }
            if (ct.getLocalSheetId() == index) {
                toRemove.add(nm);
            } else if (ct.getLocalSheetId()  index){
                // Bump down by one, so still points at the same sheet
                ct.setLocalSheetId(ct.getLocalSheetId()-1);
            }
        }
        for (XSSFName nm : toRemove) {
            removeName(nm);
        }
    }

    /**
     * Retrieves the current policy on what to do when
     *  getting missing or blank cells from a row.
     * The default is to return blank and null cells.
     *  {@link MissingCellPolicy}
     *
     * getMissingCellPolicy is a get stereotype.
     * It returns one data member: _missingCellPolicy.
     */
    @Override
    public MissingCellPolicy getMissingCellPolicy() {
        return _missingCellPolicy;
    }
    /**
     * 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(int)}}. See
     *  {@link MissingCellPolicy}
     *
     * setMissingCellPolicy is a command stereotype.
     * It modifies the following member variables: _missingCellPolicy.
     */
    @Override
    public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) {
        _missingCellPolicy = missingCellPolicy;
    }

    /**
     * Convenience method to set the active sheet.  The active sheet is is the sheet
     * which is currently displayed when the workbook is viewed in Excel.
     * 'Selected' sheet(s) is a distinct concept.
     *
     * setActiveSheet is a command stereotype.
     * It modifies member variables via the following method calls: workbook via getBookViews.
     * It makes calls to the following method(s): validateSheetIndex
     */
    @Override
    public void setActiveSheet(int index) {

        validateSheetIndex(index);

        for (CTBookView arrayBook : workbook.getBookViews().getWorkbookViewArray()) {
            arrayBook.setActiveTab(index);
        }
    }

    /**
     * Validate sheet index
     *
     * @param index the index to validate
     * @throws IllegalArgumentException if the index is out of range (index
     *             0 || index = getNumberOfSheets()).
     *
     * validateSheetIndex is a get stereotype.
     */
    private void validateSheetIndex(int index) {
        int lastSheetIx = sheets.size() - 1;
        if (index  0 || index  lastSheetIx) {
            String range = "(0.." +    lastSheetIx + ")";
            if (lastSheetIx == -1) {
                range = "(no sheets)";
            }
            throw new IllegalArgumentException("Sheet index ("
                    + index +") is out of range " + range);
        }
    }

    /**
     * Gets the first tab that is displayed in the list of tabs in excel.
     *
     * @return integer that contains the index to the active sheet in this book view.
     *
     * getFirstVisibleTab is a property stereotype.
     * It has a single return, which delegates to the following call(s): (short) bookView.getFirstSheet().
     */
    @Override
    public int getFirstVisibleTab() {
        CTBookViews bookViews = workbook.getBookViews();
        CTBookView bookView = bookViews.getWorkbookViewArray(0);
        return (short) bookView.getFirstSheet();
    }

    /**
     * Sets the first tab that is displayed in the list of tabs in excel.
     *
     * @param index integer that contains the index to the active sheet in this book view.
     */
    @Override
    public void setFirstVisibleTab(int index) {
        CTBookViews bookViews = workbook.getBookViews();
        CTBookView bookView= bookViews.getWorkbookViewArray(0);
        bookView.setFirstSheet(index);
    }

    /**
     * Sets the printarea for the sheet provided
     * p
     * i.e. Reference = $A$1:$B$2
     * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
     * @param reference Valid name Reference for the Print Area
     *
     * setPrintArea is a command stereotype.
     * It modifies member variables via the following method calls: SheetNameFormatter via appendFormat.
     * It makes calls to the following method(s): getSheetName
     */
    @Override
    public void setPrintArea(int sheetIndex, String reference) {
        XSSFName name = getBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
        if (name == null) {
            name = createBuiltInName(XSSFName.BUILTIN_PRINT_AREA, sheetIndex);
        }
        //short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
        //name.setExternSheetNumber(externSheetIndex);
        String[] parts = COMMA_PATTERN.split(reference);
        StringBuffer sb = new StringBuffer(32);
        for (int i = 0; i  parts.length; i++) {
            if(i0) {
                sb.append(",");
            }
            SheetNameFormatter.appendFormat(sb, getSheetName(sheetIndex));
            sb.append("!");
            sb.append(parts[i]);
        }
        name.setRefersToFormula(sb.toString());
    }

    /**
     * For the Convenience of Java Programmers maintaining pointers.
     * @see #setPrintArea(int, String)
     * @param sheetIndex Zero-based sheet index (0 = First Sheet)
     * @param startColumn Column to begin printarea
     * @param endColumn Column to end the printarea
     * @param startRow Row to begin the printarea
     * @param endRow Row to end the printarea
     *
     * setPrintArea is a command stereotype.
     * It makes calls to the following method(s): setPrintArea
     */
    @Override
    public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) {
        String reference=getReferencePrintArea(getSheetName(sheetIndex), startColumn, endColumn, startRow, endRow);
        setPrintArea(sheetIndex, reference);
    }

    private static String getReferencePrintArea(String sheetName, int startC, int endC, int startR, int endR) {
        //windows excel example: Sheet1!$C$3:$E$4
        CellReference colRef = new CellReference(sheetName, startR, startC, true, true);
        CellReference colRef2 = new CellReference(sheetName, endR, endC, true, true);

        return "$" + colRef.getCellRefParts()[2] + "$" + colRef.getCellRefParts()[1] + ":$" + colRef2.getCellRefParts()[2] + "$" + colRef2.getCellRefParts()[1];
    }

    XSSFName getBuiltInName(String builtInCode, int sheetNumber) {
        for (XSSFName name : namedRangesByName.get(builtInCode.toLowerCase(Locale.ENGLISH))) {
            if (name.getSheetIndex() == sheetNumber) {
                return name;
            }
        }
        return null;
    }

    /**
     * Generates a NameRecord to represent a built-in region
     *
     * @return a new NameRecord
     * @throws IllegalArgumentException if sheetNumber is invalid
     * @throws POIXMLException if such a name already exists in the workbook
     *
     * createBuiltInName is a property stereotype.
     * It has a single return, which delegates to the following call(s): createAndStoreName(nameRecord).
     * The return value depends on the following parameter(s): builtInName, and sheetNumber.
     * The return value depends on the boolean value of the following condition(s): getBuiltInName(builtInName, sheetNumber) != null.
     */
    XSSFName createBuiltInName(String builtInName, int sheetNumber) {
        validateSheetIndex(sheetNumber);

        CTDefinedNames names = workbook.getDefinedNames() == null ? workbook.addNewDefinedNames() : workbook.getDefinedNames();
        CTDefinedName nameRecord = names.addNewDefinedName();
        nameRecord.setName(builtInName);
        nameRecord.setLocalSheetId(sheetNumber);

        if (getBuiltInName(builtInName, sheetNumber) != null) {
            throw new POIXMLException("Builtin (" + builtInName
                    + ") already exists for sheet (" + sheetNumber + ")");
        }

        return createAndStoreName(nameRecord);
    }

    /**
     * We only set one sheet as selected for compatibility with HSSF.
     *
     * setSelectedTab is a command stereotype.
     */
    @Override
    public void setSelectedTab(int index) {
        int idx = 0;
        for (XSSFSheet sh : sheets) {
            sh.setSelected(idx == index);
            idx++;
        }
    }

    /**
     * Set the sheet name.
     *
     * @param sheetIndex sheet number (0 based)
     * @param sheetname  the new sheet name
     * @throws IllegalArgumentException if the name is null or invalid
     *  or workbook already contains a sheet with this name
     * @see #createSheet(String)
     * @see org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)
     *
     * setSheetName is a command stereotype.
     * It modifies member variables via the following method calls: WorkbookUtil via validateSheetName, and workbook via getSheets.
     * It makes calls to the following method(s): getSheets, and validateSheetIndex
     */
    @Override
    public void setSheetName(int sheetIndex, String sheetname) {
        if (sheetname == null) {
            throw new IllegalArgumentException( "sheetName must not be null" );
        }
        
        validateSheetIndex(sheetIndex);
        String oldSheetName = getSheetName(sheetIndex);

        // YK: Mimic Excel and silently truncate sheet names longer than 31 characters
        if(sheetname.length()  31) {
            sheetname = sheetname.substring(0, 31);
        }
        WorkbookUtil.validateSheetName(sheetname);

        // Do nothing if no change
        if (sheetname.equals(oldSheetName)) {
            return;
        }
        
        // Check it isn't already taken
        if (containsSheet(sheetname, sheetIndex )) {
            throw new IllegalArgumentException( "The workbook already contains a sheet of this name" );
        }

        // Update references to the name
        XSSFFormulaUtils utils = new XSSFFormulaUtils(this);
        utils.updateSheetName(sheetIndex, oldSheetName, sheetname);

        workbook.getSheets().getSheetArray(sheetIndex).setName(sheetname);
    }

    /**
     * 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)
     *
     * setSheetOrder is a command stereotype.
     * It modifies member variables via the following method calls: sheets via add, sheets via get, sheets via remove, and workbook via getSheets.
     * It makes calls to the following method(s): getSheets, updateActiveSheetAfterSheetReorder, and updateNamedRangesAfterSheetReorder
     */
    @Override
    public void setSheetOrder(String sheetname, int pos) {
        int idx = getSheetIndex(sheetname);
        sheets.add(pos, sheets.remove(idx));

        // Reorder CTSheets
        CTSheets ct = workbook.getSheets();
        XmlObject cts = ct.getSheetArray(idx).copy();
        workbook.getSheets().removeSheet(idx);
        CTSheet newcts = ct.insertNewSheet(pos);
        newcts.set(cts);

        //notify sheets
        //noinspection deprecation
        CTSheet[] sheetArray = ct.getSheetArray();
        for(int i=0; i  sheetArray.length; i++) {
            sheets.get(i).sheet = sheetArray[i];
        }
        
        updateNamedRangesAfterSheetReorder(idx, pos);
        updateActiveSheetAfterSheetReorder(idx, pos);
    }
    
    /**
     * update sheet-scoped named ranges in this workbook after changing the sheet order
     * of a sheet at oldIndex to newIndex.
     * Sheets between these indices will move left or right by 1.
     *
     * @param oldIndex the original index of the re-ordered sheet
     * @param newIndex the new index of the re-ordered sheet
     *
     * updateNamedRangesAfterSheetReorder is a command stereotype.
     */
    private void updateNamedRangesAfterSheetReorder(int oldIndex, int newIndex) {
        // update sheet index of sheet-scoped named ranges
        for (final XSSFName name : namedRanges) {
            final int i = name.getSheetIndex();
            // name has sheet-level scope
            if (i != -1) {
                // name refers to this sheet
                if (i == oldIndex) {
                    name.setSheetIndex(newIndex);
                }
                // if oldIndex  newIndex then this sheet moved left and sheets between newIndex and oldIndex moved right
                else if (newIndex = i  i  oldIndex) {
                    name.setSheetIndex(i+1);
                }
                // if oldIndex  newIndex then this sheet moved right and sheets between oldIndex and newIndex moved left
                else if (oldIndex  i  i = newIndex) {
                    name.setSheetIndex(i-1);
                }
            }
        }
    }
    

    /*
     *
     * updateActiveSheetAfterSheetReorder is a command stereotype.
     * It makes calls to the following method(s): setActiveSheet
     */    
    private void updateActiveSheetAfterSheetReorder(int oldIndex, int newIndex) {
        // adjust active sheet if necessary
        int active = getActiveSheetIndex();
        if(active == oldIndex) {
            // moved sheet was the active one
            setActiveSheet(newIndex);
        } else if ((active  oldIndex  active  newIndex) ||
                (active  oldIndex  active  newIndex)) {
            // not affected
        } else if (newIndex  oldIndex) {
            // moved sheet was below before and is above now = active is one less
            setActiveSheet(active-1);
        } else {
            // remaining case: moved sheet was higher than active before and is lower now = active is one more
            setActiveSheet(active+1);
        }
    }

    /**
     * marshal named ranges from the {@link #namedRanges} collection to the underlying CTWorkbook bean
     *
     * saveNamedRanges is a command stereotype.
     * It modifies member variables via the following method calls: workbook via setDefinedNames, and workbook via unsetDefinedNames.
     * It makes calls to the following method(s): reprocessNamedRanges
     */
    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);
            if(workbook.isSetDefinedNames()) {
                workbook.unsetDefinedNames();
            }
            workbook.setDefinedNames(names);
                        
            // Re-process the named ranges
            reprocessNamedRanges();
        } else {
            if(workbook.isSetDefinedNames()) {
                workbook.unsetDefinedNames();
            }
        }
    }
    

    /*
     *
     * reprocessNamedRanges is a command stereotype.
     * It modifies the following member variables: namedRanges, and namedRangesByName.
     * It modifies member variables via the following method calls: workbook via getDefinedNames.
     * It makes calls to the following method(s): createAndStoreName, namedRanges, and namedRangesByName
     */    
    private void reprocessNamedRanges() {
        namedRangesByName = new ArrayListValuedHashMapString, XSSFName();
        namedRanges = new ArrayListXSSFName();
        if(workbook.isSetDefinedNames()) {
            for(CTDefinedName ctName : workbook.getDefinedNames().getDefinedNameArray()) {
                createAndStoreName(ctName);
            }
        }
    }


    /*
     *
     * saveCalculationChain is a command stereotype.
     * It modifies the following member variables: calcChain.
     * It makes calls to the following method(s): removeRelation
     */    
    private void saveCalculationChain(){
        if(calcChain != null){
            int count = calcChain.getCTCalcChain().sizeOfCArray();
            if(count == 0){
                removeRelation(calcChain);
                calcChain = null;
            }
        }
    }


    /*
     *
     * commit is a command stereotype.
     * It modifies member variables via the following method calls: CTWorkbook via type.getName, and workbook via save.
     * It makes calls to the following method(s): new, saveCalculationChain, saveNamedRanges, and type.getName
     */    
    @Override
    protected void commit() throws IOException {
        saveNamedRanges();
        saveCalculationChain();

        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();
    }
    
    /**
     * Returns SharedStringsTable - tha cache of string for this workbook
     *
     * @return the shared string table
     *
     * getSharedStringSource is a get stereotype.
     * It returns one data member: sharedStringSource.
     */
    @Internal
    public SharedStringsTable getSharedStringSource() {
        return this.sharedStringSource;
    }

    /**
     * Return a object representing a collection of shared objects used for styling content,
     * e.g. fonts, cell styles, colors, etc.
     *
     * getStylesSource is a get stereotype.
     * It returns one data member: stylesSource.
     */
    public StylesTable getStylesSource() {
        return this.stylesSource;
    }

    /**
     * Returns the Theme of current workbook.
     *
     * getTheme is a property stereotype.
     * It has multiple return values: null.
     * Some of which delegate to the following call(s): stylesSource.getTheme().
     * The return value depends on the boolean value of the following condition(s): stylesSource == null.
     */
    public ThemesTable getTheme() {
        if (stylesSource == null) {
            return null;
        }
        return stylesSource.getTheme();
    }

    /**
     * Returns an object that handles instantiating concrete
     *  classes of the various instances for XSSF.
     *
     * getCreationHelper is a get stereotype.
     * It returns one data member: _creationHelper.
     */
    @Override
    public XSSFCreationHelper getCreationHelper() {
        if(_creationHelper == null) {
            _creationHelper = new XSSFCreationHelper(this);
        }
        return _creationHelper;
    }

    
    private boolean containsSheet(String name, int excludeSheetIdx) {
        //noinspection deprecation
        CTSheet[] ctSheetArray = workbook.getSheets().getSheetArray();

        if (name.length()  MAX_SENSITIVE_SHEET_NAME_LEN) {
            name = name.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN);
        }

        for (int i = 0; i  ctSheetArray.length; i++) {
            String ctName = ctSheetArray[i].getName();
            if (ctName.length()  MAX_SENSITIVE_SHEET_NAME_LEN) {
                ctName = ctName.substring(0, MAX_SENSITIVE_SHEET_NAME_LEN);
            }

            if (excludeSheetIdx != i  name.equalsIgnoreCase(ctName)) {
                return true;
            }
        }
        return false;
    }

    /**
     * Gets a boolean value that indicates whether the date systems used in the workbook starts in 1904.
     * p
     * The default value is false, meaning that the workbook uses the 1900 date system,
     * where 1/1/1900 is the first day in the system..
     * /p
     * @return true if the date systems used in the workbook starts in 1904
     */
    @Internal
    public boolean isDate1904(){
        CTWorkbookPr workbookPr = workbook.getWorkbookPr();
        return workbookPr != null  workbookPr.getDate1904();
    }

    /**
     * Get the document's embedded files.
     *
     * getAllEmbedds is a property stereotype.
     * It returns a single value: embedds.
     */
    @Override
    public ListPackagePart getAllEmbedds() throws OpenXML4JException {
        ListPackagePart embedds = new LinkedListPackagePart();

        for(XSSFSheet sheet : sheets){
            // Get the embeddings for the workbook
            for(PackageRelationship rel : sheet.getPackagePart().getRelationshipsByType(XSSFRelation.OLEEMBEDDINGS.getRelation())) {
                embedds.add( sheet.getPackagePart().getRelatedPart(rel) );
            }

            for(PackageRelationship rel : sheet.getPackagePart().getRelationshipsByType(XSSFRelation.PACKEMBEDDINGS.getRelation())) {
               embedds.add( sheet.getPackagePart().getRelatedPart(rel) );
            }
        }
        return embedds;
    }

    @Override
    @NotImplemented
    public boolean isHidden() {
        throw new RuntimeException("Not implemented yet");
    }

    @Override
    @NotImplemented
    public void setHidden(boolean hiddenFlag) {
        throw new RuntimeException("Not implemented yet");
    }


    /*
     *
     * isSheetHidden is a predicate stereotype.
     * It has a single return, which delegates to the following call(s): ctSheet.getState() == STSheetState.HIDDEN.
     * The return value depends on the following parameter(s): sheetIx.
     */    
    @Override
    public boolean isSheetHidden(int sheetIx) {
        validateSheetIndex(sheetIx);
        CTSheet ctSheet = sheets.get(sheetIx).sheet;
        return ctSheet.getState() == STSheetState.HIDDEN;
    }


    /*
     *
     * isSheetVeryHidden is a predicate stereotype.
     * It has a single return, which delegates to the following call(s): ctSheet.getState() == STSheetState.VERY_HIDDEN.
     * The return value depends on the following parameter(s): sheetIx.
     */    
    @Override
    public boolean isSheetVeryHidden(int sheetIx) {
        validateSheetIndex(sheetIx);
        CTSheet ctSheet = sheets.get(sheetIx).sheet;
        return ctSheet.getState() == STSheetState.VERY_HIDDEN;
    }
    

    /*
     *
     * getSheetVisibility is a get stereotype.
     * It returns one data member: SheetVisibility.
     */    
    @Override
    public SheetVisibility getSheetVisibility(int sheetIx) {
        validateSheetIndex(sheetIx);
        final CTSheet ctSheet = sheets.get(sheetIx).sheet;
        final STSheetState.Enum state = ctSheet.getState();
        if (state == STSheetState.VISIBLE) {
            return SheetVisibility.VISIBLE;
        }
        if (state == STSheetState.HIDDEN) {
            return SheetVisibility.HIDDEN;
        }
        if (state == STSheetState.VERY_HIDDEN) {
            return SheetVisibility.VERY_HIDDEN;
        }
        throw new IllegalArgumentException("This should never happen");
    }


    /*
     *
     * setSheetHidden is a command stereotype.
     * It makes calls to the following method(s): setSheetVisibility
     */    
    @Override
    public void setSheetHidden(int sheetIx, boolean hidden) {
        setSheetVisibility(sheetIx, hidden ? SheetVisibility.HIDDEN : SheetVisibility.VISIBLE);
    }


    /*
     *
     * setSheetHidden is a command stereotype.
     * It modifies member variables via the following method calls: SheetVisibility via values, and WorkbookUtil via validateSheetState.
     * It makes calls to the following method(s): setSheetVisibility
     */    
    @Deprecated
    @Removal(version="3.18")
    @Override
    public void setSheetHidden(int sheetIx, int state) {
        WorkbookUtil.validateSheetState(state);
        setSheetVisibility(sheetIx, SheetVisibility.values()[state]);
    }
    

    /*
     *
     * setSheetVisibility is a command stereotype.
     * It makes calls to the following method(s): validateSheetIndex
     */    
    @Override
    public void setSheetVisibility(int sheetIx, SheetVisibility visibility) {
        validateSheetIndex(sheetIx);
        
        final CTSheet ctSheet = sheets.get(sheetIx).sheet;
        switch (visibility) {
            case VISIBLE:
                ctSheet.setState(STSheetState.VISIBLE);
                break;
            case HIDDEN:
                ctSheet.setState(STSheetState.HIDDEN);
                break;
            case VERY_HIDDEN:
                ctSheet.setState(STSheetState.VERY_HIDDEN);
                break;
            default:
                throw new IllegalArgumentException("This should never happen");
        }
    }
    
    
    

    /**
     * Fired when a formula is deleted from this workbook,
     * for example when calling cell.setCellFormula(null)
     *
     * @see XSSFCell#setCellFormula(String)
     *
     * onDeleteFormula is a command stereotype.
     * It modifies member variables via the following method calls: calcChain via removeItem.
     */
    protected void onDeleteFormula(XSSFCell cell){
        if(calcChain != null) {
            int sheetId = (int)cell.getSheet().sheet.getSheetId();
            calcChain.removeItem(sheetId, cell.getReference());
        }
    }

    /**
     * Return the {@link CalculationChain} object for this workbook
     * p
     *   The calculation chain object specifies the order in which the cells in a workbook were last calculated
     * /p
     *
     * @return the codeCalculationChain/code object or codenull/code if not defined
     *
     * getCalculationChain is a get stereotype.
     * It returns one data member: calcChain.
     */
    @Internal
    public CalculationChain getCalculationChain() {
        return calcChain;
    }
    
    /**
     * Returns the list of {@link ExternalLinksTable} object for this workbook
     * 
     * pThe external links table specifies details of named ranges etc
     *  that are referenced from other workbooks, along with the last seen
     *  values of what they point to./p
     *
     * pNote that Excel uses index 0 for the current workbook, so the first
     *  External Links in a formula would be '[1]Foo' which corresponds to
     *  entry 0 in this list./p
     * @return the codeExternalLinksTable/code list, which may be empty
     *
     * getExternalLinksTable is a get stereotype.
     * It returns one data member: externalLinks.
     */
    @Internal
    public ListExternalLinksTable getExternalLinksTable() {
        return externalLinks;
    }

    /**
     *
     * @return a collection of custom XML mappings defined in this workbook
     *
     * getCustomXMLMappings is a property stereotype.
     * It has a single return, which delegates to the following call(s): mapInfo == null ? new ArrayListXSSFMap() : mapInfo.getAllXSSFMaps().
     */
    public CollectionXSSFMap getCustomXMLMappings(){
        return mapInfo == null ? new ArrayListXSSFMap() : mapInfo.getAllXSSFMaps();
    }

    /**
     *
     * @return the helper class used to query the custom XML mapping defined in this workbook
     *
     * getMapInfo is a get stereotype.
     * It returns one data member: mapInfo.
     */
    @Internal
    public MapInfo getMapInfo(){
        return mapInfo;
    }

    /**
     * Adds the External Link Table part and relations required to allow formulas 
     *  referencing the specified external workbook to be added to this one. Allows
     *  formulas such as "[MyOtherWorkbook.xlsx]Sheet3!$A$5" to be added to the 
     *  file, for workbooks not already linked / referenced.
     *
     *  Note: this is not implemented and thus currently throws an Exception stating this.
     *
     * @param name The name the workbook will be referenced as in formulas
     * @param workbook The open workbook to fetch the link required information from
     *
     * @throws RuntimeException stating that this method is not implemented yet.
     */
    @Override
    @NotImplemented
    public int linkExternalWorkbook(String name, Workbook workbook) {
        throw new RuntimeException("Not Implemented - see bug #57184");
    }

    
    public boolean isStructureLocked() {
        return workbookProtectionPresent()  workbook.getWorkbookProtection().getLockStructure();
    }

    
    public boolean isWindowsLocked() {
        return workbookProtectionPresent()  workbook.getWorkbookProtection().getLockWindows();
    }

    
    public boolean isRevisionLocked() {
        return workbookProtectionPresent()  workbook.getWorkbookProtection().getLockRevision();
    }

    /**
     * Locks the structure of workbook.
     *
     * lockStructure is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void lockStructure() {
        safeGetWorkbookProtection().setLockStructure(true);
    }

    /**
     * Unlocks the structure of workbook.
     *
     * unLockStructure is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void unLockStructure() {
        safeGetWorkbookProtection().setLockStructure(false);
    }

    /**
     * Locks the windows that comprise the workbook.
     *
     * lockWindows is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void lockWindows() {
        safeGetWorkbookProtection().setLockWindows(true);
    }

    /**
     * Unlocks the windows that comprise the workbook.
     *
     * unLockWindows is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void unLockWindows() {
        safeGetWorkbookProtection().setLockWindows(false);
    }

    /**
     * Locks the workbook for revisions.
     *
     * lockRevision is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void lockRevision() {
        safeGetWorkbookProtection().setLockRevision(true);
    }

    /**
     * Unlocks the workbook for revisions.
     *
     * unLockRevision is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection
     */
    public void unLockRevision() {
        safeGetWorkbookProtection().setLockRevision(false);
    }

    /**
     * Sets the workbook password. 
     * 
     * @param password if null, the password will be removed
     * @param hashAlgo if null, the password will be set as XOR password (Excel 2010 and earlier)
     *  otherwise the given algorithm is used for calculating the hash password (Excel 2013)
     *
     * setWorkbookPassword is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection, and setPassword
     */
    public void setWorkbookPassword(String password, HashAlgorithm hashAlgo) {
        if (password == null  !workbookProtectionPresent()) {
            return;
        }
        setPassword(safeGetWorkbookProtection(), password, hashAlgo, "workbook");
    }

    /**
     * Validate the password against the stored hash, the hashing method will be determined
     *  by the existing password attributes
     * @return true, if the hashes match (... though original password may differ ...)
     *
     * validateWorkbookPassword is a predicate stereotype.
     * It has multiple return values. One or more delegate to a call: validatePassword(safeGetWorkbookProtection(), password, "workbook").
     * And one or more do not: (password == null).
     * The return value depends on the following parameter(s): password.
     * The return value depends on the boolean value of the following condition(s): !workbookProtectionPresent().
     */
    public boolean validateWorkbookPassword(String password) {
        if (!workbookProtectionPresent()) {
            return (password == null);
        }
        return validatePassword(safeGetWorkbookProtection(), password, "workbook");
    }

    /**
     * Sets the revisions password.
     * 
     * @param password if null, the password will be removed
     * @param hashAlgo if null, the password will be set as XOR password (Excel 2010 and earlier)
     *  otherwise the given algorithm is used for calculating the hash password (Excel 2013)
     *
     * setRevisionsPassword is a command stereotype.
     * It makes calls to the following method(s): safeGetWorkbookProtection, and setPassword
     */
    public void setRevisionsPassword(String password, HashAlgorithm hashAlgo) {
        if (password == null  !workbookProtectionPresent()) {
            return;
        }
        setPassword(safeGetWorkbookProtection(), password, hashAlgo, "revisions");
    }

    /**
     * Validate the password against the stored hash, the hashing method will be determined
     *  by the existing password attributes
     * @return true if the hashes match (... though original password may differ ...)
     *
     * validateRevisionsPassword is a predicate stereotype.
     * It has multiple return values. One or more delegate to a call: validatePassword(safeGetWorkbookProtection(), password, "revisions").
     * And one or more do not: (password == null).
     * The return value depends on the following parameter(s): password.
     * The return value depends on the boolean value of the following condition(s): !workbookProtectionPresent().
     */
    public boolean validateRevisionsPassword(String password) {
        if (!workbookProtectionPresent()) {
            return (password == null);
        }
        return validatePassword(safeGetWorkbookProtection(), password, "revisions");
    }
    
    /**
     * Removes the workbook protection settings
     *
     * unLock is a command stereotype.
     * It modifies member variables via the following method calls: workbook via unsetWorkbookProtection.
     */
    public void unLock() {
        if (workbookProtectionPresent()) {
            workbook.unsetWorkbookProtection();
        }
    }
    
    private boolean workbookProtectionPresent() {
        return workbook.isSetWorkbookProtection();
    }

    /*
     *
     * safeGetWorkbookProtection is a property stereotype.
     * It has multiple returns, which delegate to the following call(s): workbook.addNewWorkbookProtection(), and workbook.getWorkbookProtection().
     * The return value depends on the boolean value of the following condition(s): !workbookProtectionPresent().
     */    
    private CTWorkbookProtection safeGetWorkbookProtection() {
        if (!workbookProtectionPresent()){
            return workbook.addNewWorkbookProtection();
        }
        return workbook.getWorkbookProtection();
    }
    
    /**
     *
     * Returns the locator of user-defined functions.
     * p
     * The default instance extends the built-in functions with the Excel Analysis Tool Pack.
     * To set / evaluate custom functions you need to register them as follows:
     *
     *
     *
     * /p
     * @return wrapped instance of UDFFinder that allows seeking functions both by index and name
     *
     * getUDFFinder is a get stereotype.
     * It returns one data member: _udfFinder.
     */ 
    UDFFinder getUDFFinder() {
        return _udfFinder;
    }

    /**
     * Register a new toolpack in this workbook.
     *
     * @param toopack the toolpack to register
     */
    @Override
    public void addToolPack(UDFFinder toopack){
        _udfFinder.add(toopack);
    }

    /**
     * Whether the application shall perform a full recalculation when the workbook is opened.
     * p
     * Typically you want to force formula recalculation when you modify cell formulas or values
     * of a workbook previously created by Excel. When set to true, this flag will tell Excel
     * that it needs to recalculate all formulas in the workbook the next time the file is opened.
     * /p
     * p
     * Note, that recalculation updates cached formula results and, thus, modifies the workbook.
     * Depending on the version, Excel may prompt you with "Do you want to save the changes in emfilename/em?"
     * on close.
     * /p
     *
     * @param value true if the application will perform a full recalculation of
     * workbook values when the workbook is opened
     * @since 3.8
     *
     * setForceFormulaRecalculation is a command stereotype.
     */
    @Override
    public void setForceFormulaRecalculation(boolean value){
        CTWorkbook ctWorkbook = getCTWorkbook();
        CTCalcPr calcPr = ctWorkbook.isSetCalcPr() ? ctWorkbook.getCalcPr() : ctWorkbook.addNewCalcPr();
        // when set to 0, will tell Excel that it needs to recalculate all formulas
        // in the workbook the next time the file is opened.
        calcPr.setCalcId(0);

        if(value  calcPr.getCalcMode() == STCalcMode.MANUAL) {
            calcPr.setCalcMode(STCalcMode.AUTO);
        }
    }

    
    @Override
    public boolean getForceFormulaRecalculation(){
        CTWorkbook ctWorkbook = getCTWorkbook();
        CTCalcPr calcPr = ctWorkbook.getCalcPr();
        return calcPr != null  calcPr.getCalcId() != 0;
    }



    /**
     * Add pivotCache to the workbook
     *
     * addPivotCache is a property stereotype.
     * It returns a single value: cache.
     * The return value depends on the following parameter(s): rId.
     * The return value depends on the boolean value of the following condition(s): ctWorkbook.isSetPivotCaches(), and pivotCaches == null.
     */
    @Beta
    protected CTPivotCache addPivotCache(String rId) {
        CTWorkbook ctWorkbook = getCTWorkbook();
        CTPivotCaches caches;
        if (ctWorkbook.isSetPivotCaches()) {
            caches = ctWorkbook.getPivotCaches();
        } else {
            caches = ctWorkbook.addNewPivotCaches();
        }
        CTPivotCache cache = caches.addNewPivotCache();

        int tableId = getPivotTables().size()+1;
        cache.setCacheId(tableId);
        cache.setId(rId);
        if(pivotCaches == null) {
            pivotCaches = new ArrayListCTPivotCache();
        }
        pivotCaches.add(cache);
        return cache;
    }


    /*
     *
     * getPivotTables is a get stereotype.
     * It returns one data member: pivotTables.
     */    
    @Beta
    public ListXSSFPivotTable getPivotTables() {
        return pivotTables;
    }

    @Beta
    protected void setPivotTables(ListXSSFPivotTable pivotTables) {
        this.pivotTables = pivotTables;
    }

    /*
     *
     * getWorkbookType is a property stereotype.
     * It has a single return, which delegates to the following call(s): isMacroEnabled() ? XSSFWorkbookType.XLSM : XSSFWorkbookType.XLSX.
     */    
    public XSSFWorkbookType getWorkbookType() {
        return isMacroEnabled() ? XSSFWorkbookType.XLSM : XSSFWorkbookType.XLSX;
    }

    /**
     * Sets whether the workbook will be an .xlsx or .xlsm (macro-enabled) file.
     *
     * setWorkbookType is a command stereotype.
     * It makes calls to the following method(s): getPackagePart
     */
    public void setWorkbookType(XSSFWorkbookType type) {
        try {
            getPackagePart().setContentType(type.getContentType());
        } catch (InvalidFormatException e) {
            throw new POIXMLException(e);
        }
    }

    /**
     * Adds a vbaProject.bin file to the workbook.  This will change the workbook
     * type if necessary.
     *
     * @throws IOException If copying data from the stream fails.
     *
     * setVBAProject is a command stereotype.
     * It modifies the following member variables: outputStream, and ppName.
     * It modifies member variables via the following method calls: IOUtils via closeQuietly, IOUtils via copy, and XSSFRelation via VBA_MACROS.getDefaultFileName.
     * It makes calls to the following method(s): outputStream, and setWorkbookType
     */
    public void setVBAProject(InputStream vbaProjectStream) throws IOException {
        if (!isMacroEnabled()) {
            setWorkbookType(XSSFWorkbookType.XLSM);
        }

        PackagePartName ppName;
        try {
            ppName = PackagingURIHelper.createPartName(XSSFRelation.VBA_MACROS.getDefaultFileName());
        } catch (InvalidFormatException e) {
            throw new POIXMLException(e);
        }
        OPCPackage opc = getPackage();
        OutputStream outputStream;
        if (!opc.containPart(ppName)) {
            POIXMLDocumentPart relationship = createRelationship(XSSFRelation.VBA_MACROS, XSSFFactory.getInstance());
            outputStream = relationship.getPackagePart().getOutputStream();
        } else {
            PackagePart part = opc.getPart(ppName);
            outputStream = part.getOutputStream();
        }
        try {
            IOUtils.copy(vbaProjectStream, outputStream);
        } finally {
            IOUtils.closeQuietly(outputStream);
        }
    }

    /**
     * Adds a vbaProject.bin file taken from another, given workbook to this one.
     * @throws IOException If copying the VBAProject stream fails.
     * @throws InvalidFormatException If an error occurs while handling parts of the XSSF format
     *
     * setVBAProject is a command stereotype.
     * It makes calls to the following method(s): setVBAProject
     */
    public void setVBAProject(XSSFWorkbook macroWorkbook) throws IOException, InvalidFormatException {
        if (!macroWorkbook.isMacroEnabled()) {
            return;
        }
        InputStream vbaProjectStream = XSSFRelation.VBA_MACROS.getContents(macroWorkbook.getCorePart());
        if (vbaProjectStream != null) {
            setVBAProject(vbaProjectStream);
        }
    }

    /**
     * Returns the spreadsheet version (EXCLE2007) of this workbook
     * 
     * @return EXCEL2007 SpreadsheetVersion enum
     * @since 3.14 beta 2
     *
     * getSpreadsheetVersion is a get stereotype.
     * It returns one data member: SpreadsheetVersion.
     */
    @Override
    public SpreadsheetVersion getSpreadsheetVersion() {
        return SpreadsheetVersion.EXCEL2007;
    }
    
    
    public XSSFTable getTable(String name) {
        if (name != null  sheets != null) {
            for (XSSFSheet sheet : sheets) {
                for (XSSFTable tbl : sheet.getTables()) {
                    if (name.equalsIgnoreCase(tbl.getName())) {
                        return tbl;
                    }
                }
            }
        }
        return null;
    }

    /*
     *
     * addOlePackage is a property stereotype.
     * It returns a single value: oleId.
     * The return value depends on the following parameter(s): command, e, fileName, label, and oleData.
     */    
    @Override
    public int addOlePackage(byte[] oleData, String label, String fileName, String command)
    throws IOException {
        // find an unused part name
        OPCPackage opc = getPackage();
        PackagePartName pnOLE;
        int oleId=0;
        do {
            try {
                pnOLE = PackagingURIHelper.createPartName( "/xl/embeddings/oleObject"+(++oleId)+".bin" );
            } catch (InvalidFormatException e) {
                throw new IOException("ole object name not recognized", e);
            }
        } while (opc.containPart(pnOLE));

        PackagePart pp = opc.createPart( pnOLE, "application/vnd.openxmlformats-officedocument.oleObject" );
        
        Ole10Native ole10 = new Ole10Native(label, fileName, command, oleData);

        ByteArrayOutputStream bos = new ByteArrayOutputStream(oleData.length+500);
        ole10.writeOut(bos);
        
        POIFSFileSystem poifs = new POIFSFileSystem();
        DirectoryNode root = poifs.getRoot();
        root.createDocument(Ole10Native.OLE10_NATIVE, new ByteArrayInputStream(bos.toByteArray()));
        root.setStorageClsid(ClassID.OLE10_PACKAGE);

        // TODO: generate CombObj stream

        OutputStream os = pp.getOutputStream();
        poifs.writeFilesystem(os);
        os.close();
        poifs.close();

        return oleId;
    }
}