hssfworkbook.java

来自「EXCEL read and write」· Java 代码 · 共 1,687 行 · 第 1/4 页

               getSheetAt(i).setSelected(i == index);
        }
        workbook.getWindowOne().setNumSelectedTabs((short)1);
    }
    /**
     * deprecated May 2008
     * @deprecated use setSelectedTab(int)
     */
    public void setSelectedTab(short index) {
        setSelectedTab((int)index);
    }
    public void setSelectedTabs(int[] indexes) {

        for (int i = 0; i < indexes.length; i++) {
            validateSheetIndex(indexes[i]);
        }
        int nSheets = _sheets.size();
        for (int i=0; i<nSheets; i++) {
            boolean bSelect = false;
            for (int j = 0; j < indexes.length; j++) {
                if (indexes[j] == i) {
                    bSelect = true;
                    break;
                }

            }
               getSheetAt(i).setSelected(bSelect);
        }
        workbook.getWindowOne().setNumSelectedTabs((short)indexes.length);
    }
    /**
     * 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.
     */
    public void setActiveSheet(int index) {

        validateSheetIndex(index);
        int nSheets = _sheets.size();
        for (int i=0; i<nSheets; i++) {
             getSheetAt(i).setActive(i == index);
        }
        workbook.getWindowOne().setActiveSheetIndex(index);
    }

    /**
     * gets the tab whose data is actually seen when the sheet is opened.
     * This may be different from the "selected sheet" since excel seems to
     * allow you to show the data of one sheet when another is seen "selected"
     * in the tabs (at the bottom).
     * @see org.apache.poi.hssf.usermodel.HSSFSheet#setSelected(boolean)
     */
    public int getActiveSheetIndex() {
        return workbook.getWindowOne().getActiveSheetIndex();
    }
    /**
     * deprecated May 2008
     * @deprecated - Misleading name - use getActiveSheetIndex()
     */
    public short getSelectedTab() {
        return (short) getActiveSheetIndex();
    }


    /**
     * sets the first tab that is displayed in the list of tabs
     * in excel.
     * @param index
     */
    public void setFirstVisibleTab(int index) {
        workbook.getWindowOne().setFirstVisibleTab(index);
    }
    /**
     * deprecated May 2008
     * @deprecated - Misleading name - use setFirstVisibleTab()
     */
    public void setDisplayedTab(short index) {
       setFirstVisibleTab(index);
    }

    /**
     * sets the first tab that is displayed in the list of tabs in excel.
     */
    public int getFirstVisibleTab() {
        return workbook.getWindowOne().getFirstVisibleTab();
    }
    /**
     * deprecated May 2008
     * @deprecated - Misleading name - use getFirstVisibleTab()
     */
    public short getDisplayedTab() {
        return (short) getFirstVisibleTab();
    }

    /**
     * Sets the sheet name.
     * Will throw IllegalArgumentException if the name is duplicated or contains /\?*[]
     * Note - Excel allows sheet names up to 31 chars in length but other applications allow more.
     * Excel does not crash with names longer than 31 chars, but silently truncates such names to 
     * 31 chars.  POI enforces uniqueness on the first 31 chars.
     * 
     * @param sheetIx number (0 based)
     */
    public void setSheetName(int sheetIx, String name) {
        if (workbook.doesContainsSheetName(name, sheetIx)) {
            throw new IllegalArgumentException("The workbook already contains a sheet with this name");
        }
        validateSheetIndex(sheetIx);
        workbook.setSheetName(sheetIx, name);
    }

    /**
     * get the sheet name
     * @param sheetIx Number
     * @return Sheet name
     */
    public String getSheetName(int sheetIx)
    {
        validateSheetIndex(sheetIx);
        return workbook.getSheetName(sheetIx);
    }

    /**
     * Check whether a sheet is hidden.
     * Note that a sheet could instead be 
     *  set to be very hidden, which is different
     *  ({@link #isSheetVeryHidden(int)})
     * @param sheetIx Number
     * @return True if sheet is hidden
     */
    public boolean isSheetHidden(int sheetIx) {
        validateSheetIndex(sheetIx);
        return workbook.isSheetHidden(sheetIx);
    }
    /**
     * Check whether a sheet is very hidden.
     * This is different from the normal 
     *  hidden status  
     *  ({@link #isSheetHidden(int)})
     * @param sheetIx Number
     * @return True if sheet is very hidden
     */
    public boolean isSheetVeryHidden(int sheetIx) {
        validateSheetIndex(sheetIx);
        return workbook.isSheetVeryHidden(sheetIx);
    }

    /**
     * Hide or unhide a sheet
     *
     * @param sheetIx The sheet index
     * @param hidden True to mark the sheet as hidden, false otherwise
     */
    public void setSheetHidden(int sheetIx, boolean hidden) {
        validateSheetIndex(sheetIx);
        workbook.setSheetHidden(sheetIx, hidden);
    }
    /**
     * Hide or unhide a sheet.
     *  0 = not hidden
     *  1 = hidden
     *  2 = very hidden.
     * 
     * @param sheetIx The sheet number
     * @param hidden 0 for not hidden, 1 for hidden, 2 for very hidden
     */
    public void setSheetHidden(int sheetIx, int hidden) {
        validateSheetIndex(sheetIx);
        workbook.setSheetHidden(sheetIx, hidden);
    }

    /*
     * get the sheet's index
     * @param name  sheet name
     * @return sheet index or -1 if it was not found.
     */

    /** Returns the index of the sheet by his name
     * @param name the sheet name
     * @return index of the sheet (0 based)
     */
    public int getSheetIndex(String name)
    {
        int retval = workbook.getSheetIndex(name);

        return retval;
    }

    /** Returns the index of the given sheet
     * @param sheet the sheet to look up
     * @return index of the sheet (0 based). <tt>-1</tt> if not found
     */
    public int getSheetIndex(HSSFSheet sheet)
    {
        for(int i=0; i<_sheets.size(); i++) {
            if(_sheets.get(i) == sheet) {
                return i;
            }
        }
        return -1;
    }

    /**
     * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
     * the high level representation.  Use this to create new sheets.
     *
     * @return HSSFSheet representing the new sheet.
     */

    public HSSFSheet createSheet()
    {
        HSSFSheet sheet = new HSSFSheet(this);

        _sheets.add(sheet);
        workbook.setSheetName(_sheets.size() - 1, "Sheet" + (_sheets.size() - 1));
        boolean isOnlySheet = _sheets.size() == 1;
        sheet.setSelected(isOnlySheet);
        sheet.setActive(isOnlySheet);
        return sheet;
    }

    /**
     * create an HSSFSheet from an existing sheet in the HSSFWorkbook.
     *
     * @return HSSFSheet representing the cloned sheet.
     */

    public HSSFSheet cloneSheet(int sheetIndex) {
        validateSheetIndex(sheetIndex);
        HSSFSheet srcSheet = (HSSFSheet) _sheets.get(sheetIndex);
        String srcName = workbook.getSheetName(sheetIndex);
        HSSFSheet clonedSheet = srcSheet.cloneSheet(this);
        clonedSheet.setSelected(false);
        clonedSheet.setActive(false);

        String name = getUniqueSheetName(srcName);
        int newSheetIndex = _sheets.size(); 
        _sheets.add(clonedSheet);
        workbook.setSheetName(newSheetIndex, name);
        
        // Check this sheet has an autofilter, (which has a built-in NameRecord at workbook level)
        int filterDbNameIndex = findExistingBuiltinNameRecordIdx(sheetIndex, NameRecord.BUILTIN_FILTER_DB);
        if (filterDbNameIndex >=0) {
            NameRecord origNameRecord = workbook.getNameRecord(filterDbNameIndex);
            // copy original formula but adjust 3D refs to the new external sheet index
            int newExtSheetIx = workbook.checkExternSheet(newSheetIndex);
            Ptg[] ptgs = origNameRecord.getNameDefinition();
            for (int i=0; i< ptgs.length; i++) {
                Ptg ptg = ptgs[i];
                ptg = ptg.copy();
                
                if (ptg instanceof Area3DPtg) {
                    Area3DPtg a3p = (Area3DPtg) ptg;
                    a3p.setExternSheetIndex(newExtSheetIx);
                } else if (ptg instanceof Ref3DPtg) {
                    Ref3DPtg r3p = (Ref3DPtg) ptg;
                    r3p.setExternSheetIndex(newExtSheetIx);
                }
                ptgs[i] = ptg;
            }
            NameRecord newNameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_FILTER_DB, newSheetIndex+1);
            newNameRecord.setNameDefinition(ptgs);
            newNameRecord.setHidden(true);
            HSSFName newName = new HSSFName(this, newNameRecord);
            names.add(newName);

            workbook.cloneDrawings(clonedSheet.getSheet());
        }
        // TODO - maybe same logic required for other/all built-in name records
        
        return clonedSheet;
    }

    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 (workbook.getSheetIndex(name) == -1) {
              return name;
            }
        }
    }

    /**
     * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and
     * returns the high level representation. Use this to create new sheets.
     *
     * @param sheetname the name for the new sheet. Note - certain length limits
     * apply. See {@link #setSheetName(int, String)}.
     *
     * @return HSSFSheet representing the new sheet.
     * @throws IllegalArgumentException
     *             if there is already a sheet present with a case-insensitive
     *             match for the specified name.
     */
    public HSSFSheet createSheet(String sheetname)
    {
        if (workbook.doesContainsSheetName( sheetname, _sheets.size() ))
            throw new IllegalArgumentException( "The workbook already contains a sheet of this name" );

        HSSFSheet sheet = new HSSFSheet(this);

        _sheets.add(sheet);
        workbook.setSheetName(_sheets.size() - 1, sheetname);
        boolean isOnlySheet = _sheets.size() == 1;
        sheet.setSelected(isOnlySheet);
        sheet.setActive(isOnlySheet);
        return sheet;
    }

    /**
     * get the number of spreadsheets in the workbook (this will be three after serialization)
     * @return number of sheets
     */

    public int getNumberOfSheets()
    {
        return _sheets.size();
    }

    private HSSFSheet[] getSheets() {
        HSSFSheet[] result = new HSSFSheet[_sheets.size()];
        _sheets.toArray(result);
        return result;
    }
    /**
     * Get the HSSFSheet object at the given index.
     * @param index of the sheet number (0-based physical & logical)
     * @return HSSFSheet at the provided index
     */

    public HSSFSheet getSheetAt(int index)
    {
        return (HSSFSheet) _sheets.get(index);
    }

    /**
     * Get sheet with the given name (case insensitive match)
     * @param name of the sheet
     * @return HSSFSheet with the name provided or <code>null</code> if it does not exist
     */

    public HSSFSheet getSheet(String name)
    {
        HSSFSheet retval = null;

        for (int k = 0; k < _sheets.size(); k++)
        {
            String sheetname = workbook.getSheetName(k);

            if (sheetname.equalsIgnoreCase(name))
            {
                retval = (HSSFSheet) _sheets.get(k);
            }
        }
        return retval;
    }

    /**
     * Removes sheet at the given index.<p/>
     *
     * Care must be taken if the removed sheet is the currently active or only selected sheet in
     * the workbook. There are a few situations when Excel must have a selection and/or active
     * sheet. (For example when printing - see Bug 40414).<br/>
     *
     * This method makes sure that if the removed sheet was active, another sheet will become
     * active in its place.  Furthermore, if the removed sheet was the only selected sheet, another
     * sheet will become selected.  The newly active/selected sheet will have the same index, or
     * one less if the removed sheet was the last in the workbook.
     *
     * @param index of the sheet  (0-based)
     */
    public void removeSheetAt(int index) {
        validateSheetIndex(index);
        boolean wasActive = getSheetAt(index).isActive();
        boolean wasSelected = getSheetAt(index).isSelected();

        _sheets.remove(index);
        workbook.removeSheet(index);

        // set the remaining active/selected sheet
        int nSheets = _sheets.size();
        if (nSheets < 1) {
            // nothing more to do if there are no sheets left
            return;
        }
        // the index of the closest remaining sheet to the one just deleted
        int newSheetIndex = index;
        if (newSheetIndex >= nSheets) {
            newSheetIndex = nSheets-1;
        }
        if (wasActive) {
            setActiveSheet(newSheetIndex);
        }

        if (wasSelected) {
            boolean someOtherSheetIsStillSelected = false;
            for (int i =0; i < nSheets; i++) {
                if (getSheetAt(i).isSelected()) {
                    someOtherSheetIsStillSelected = true;
                    break;