hssfworkbook.java

Office格式转换代码

//        if (getNumberOfSheets() == 3)
//            throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0");
        HSSFSheet sheet = new HSSFSheet(workbook);

        sheets.add(sheet);
        workbook.setSheetName(sheets.size() - 1,
                "Sheet" + (sheets.size() - 1));
        WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
        windowTwo.setSelected(sheets.size() == 1);
        windowTwo.setPaged(sheets.size() == 1);
        return sheet;
    }

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

    public HSSFSheet cloneSheet(int sheetNum) {
      HSSFSheet srcSheet = (HSSFSheet)sheets.get(sheetNum);
      String srcName = workbook.getSheetName(sheetNum);
      if (srcSheet != null) {
        HSSFSheet clonedSheet = srcSheet.cloneSheet(workbook);
        WindowTwoRecord windowTwo = (WindowTwoRecord) clonedSheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
        windowTwo.setSelected(sheets.size() == 1);
        windowTwo.setPaged(sheets.size() == 1);

        sheets.add(clonedSheet);
        if (srcName.length()<28) {
            workbook.setSheetName(sheets.size()-1, srcName+"(2)");
        }else {
            workbook.setSheetName(sheets.size()-1,srcName.substring(0,28)+"(2)");
        }
        return clonedSheet;
      }
      return null;
    }

    /**
     * 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     sheetname to set for the sheet.
     * @return HSSFSheet representing the new sheet.
     */

    public HSSFSheet createSheet(String sheetname)
    {

//        if (getNumberOfSheets() == 3)
//            throw new RuntimeException("You cannot have more than three sheets in HSSF 1.0");
        HSSFSheet sheet = new HSSFSheet(workbook);

        sheets.add(sheet);
        workbook.setSheetName(sheets.size() - 1, sheetname);
        WindowTwoRecord windowTwo = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
        windowTwo.setSelected(sheets.size() == 1);
        windowTwo.setPaged(sheets.size() == 1);
        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();
    }

    /**
     * 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
     * @param name of the sheet
     * @return HSSFSheet with the name provided or null 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.equals(name))
            {
                retval = (HSSFSheet) sheets.get(k);
            }
        }
        return retval;
    }

    /**
     * removes sheet at the given index
     * @param index of the sheet  (0-based)
     */

    public void removeSheetAt(int index)
    {
        sheets.remove(index);
        workbook.removeSheet(index);
    }

    /**
     * determine whether the Excel GUI will backup the workbook when saving.
     *
     * @param backupValue   true to indicate a backup will be performed.
     */

    public void setBackupFlag(boolean backupValue)
    {
        BackupRecord backupRecord = workbook.getBackupRecord();

        backupRecord.setBackup(backupValue ? (short) 1
                : (short) 0);
    }

    /**
     * determine whether the Excel GUI will backup the workbook when saving.
     *
     * @return the current setting for backups.
     */

    public boolean getBackupFlag()
    {
        BackupRecord backupRecord = workbook.getBackupRecord();

        return (backupRecord.getBackup() == 0) ? false
                : true;
    }

    /**
     * Sets the repeating rows and columns for a sheet (as found in
     * File->PageSetup->Sheet).  This is function is included in the workbook
     * because it creates/modifies name records which are stored at the
     * workbook level.
     * <p>
     * To set just repeating columns:
     * <pre>
     *  workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
     * </pre>
     * To set just repeating rows:
     * <pre>
     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
     * </pre>
     * To remove all repeating rows and columns for a sheet.
     * <pre>
     *  workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
     * </pre>
     *
     * @param sheetIndex    0 based index to sheet.
     * @param startColumn   0 based start of repeating columns.
     * @param endColumn     0 based end of repeating columns.
     * @param startRow      0 based start of repeating rows.
     * @param endRow        0 based end of repeating rows.
     */
    public void setRepeatingRowsAndColumns(int sheetIndex,
                                           int startColumn, int endColumn,
                                           int startRow, int endRow)
    {
        // Check arguments
        if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification");
        if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification");
        if (startColumn < -1 || startColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
        if (endColumn < -1 || endColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
        if (startRow < -1 || startRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
        if (endRow < -1 || endRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
        if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification");
        if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification");

        HSSFSheet sheet = getSheetAt(sheetIndex);
        short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);

        boolean settingRowAndColumn =
                startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
        boolean removingRange =
                startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;

        boolean isNewRecord = false;
        NameRecord nameRecord;
        nameRecord = findExistingRowColHeaderNameRecord(sheetIndex);
        if (removingRange )
        {
            if (nameRecord != null)
                workbook.removeName(findExistingRowColHeaderNameRecordIdx(sheetIndex));
            return;
        }
        if ( nameRecord == null )
        {
            nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, externSheetIndex+1);
            //does a lot of the house keeping for builtin records, like setting lengths to zero etc
            isNewRecord = true;
        }
        
        short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b;
        nameRecord.setDefinitionTextLength(definitionTextLength);

        Stack ptgs = new Stack();

        if (settingRowAndColumn)
        {
            MemFuncPtg memFuncPtg = new MemFuncPtg();
            memFuncPtg.setLenRefSubexpression(23);
            ptgs.add(memFuncPtg);
        }
        if (startColumn >= 0)
        {
            Area3DPtg area3DPtg1 = new Area3DPtg();
            area3DPtg1.setExternSheetIndex(externSheetIndex);
            area3DPtg1.setFirstColumn((short)startColumn);
            area3DPtg1.setLastColumn((short)endColumn);
            area3DPtg1.setFirstRow((short)0);
            area3DPtg1.setLastRow((short)0xFFFF);
            ptgs.add(area3DPtg1);
        }
        if (startRow >= 0)
        {
            Area3DPtg area3DPtg2 = new Area3DPtg();
            area3DPtg2.setExternSheetIndex(externSheetIndex);
            area3DPtg2.setFirstColumn((short)0);
            area3DPtg2.setLastColumn((short)0x00FF);
            area3DPtg2.setFirstRow((short)startRow);
            area3DPtg2.setLastRow((short)endRow);
            ptgs.add(area3DPtg2);
        }
        if (settingRowAndColumn)
        {
            UnionPtg unionPtg = new UnionPtg();
            ptgs.add(unionPtg);
        }
        nameRecord.setNameDefinition(ptgs);

        if (isNewRecord)
        {
            HSSFName newName = new HSSFName(workbook, nameRecord);
            names.add(newName);
        }

        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setValidSettings(false);

        WindowTwoRecord w2 = (WindowTwoRecord) sheet.getSheet().findFirstRecordBySid(WindowTwoRecord.sid);
        w2.setPaged(true);
    }

    private NameRecord findExistingRowColHeaderNameRecord( int sheetIndex )
    {
        int index = findExistingRowColHeaderNameRecordIdx(sheetIndex);
        if (index == -1)
            return null;
        else
            return (NameRecord)workbook.findNextRecordBySid(NameRecord.sid, index);
    }

    private int findExistingRowColHeaderNameRecordIdx( int sheetIndex )
    {
        int index = 0;
        NameRecord r = null;
        while ((r = (NameRecord) workbook.findNextRecordBySid(NameRecord.sid, index)) != null)
        {
            int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(r.getEqualsToIndexToSheet() - 1);
            if (isRowColHeaderRecord( r ) && nameRecordSheetIndex == sheetIndex)
            {
                return index;
            }
            index++;
        }

        return -1;
    }

    private boolean isRowColHeaderRecord( NameRecord r )
    {
        return r.getOptionFlag() == 0x20 && ("" + ((char)7)).equals(r.getNameText());
    }

    /**
     * create a new Font and add it to the workbook's font table
     * @return new font object
     */

    public HSSFFont createFont()
    {
        FontRecord font = workbook.createNewFont();
        short fontindex = (short) (getNumberOfFonts() - 1);

        if (fontindex > 3)
        {
            fontindex++;   // THERE IS NO FOUR!!
        }
        HSSFFont retval = new HSSFFont(fontindex, font);

        return retval;
    }

    /**
     * get the number of fonts in the font table
     * @return number of fonts
     */

    public short getNumberOfFonts()
    {
        return (short) workbook.getNumberOfFontRecords();
    }

    /**
     * get the font at the given index number
     * @param idx  index number
     * @return HSSFFont at the index
     */

    public HSSFFont getFontAt(short idx)
    {
        FontRecord font = workbook.getFontRecordAt(idx);
        HSSFFont retval = new HSSFFont(idx, font);

        return retval;
    }

    /**
     * create a new Cell style and add it to the workbook's style table
     * @return the new Cell Style object
     */

    public HSSFCellStyle createCellStyle()
    {
        ExtendedFormatRecord xfr = workbook.createCellXF();
        short index = (short) (getNumCellStyles() - 1);
        HSSFCellStyle style = new HSSFCellStyle(index, xfr);

        return style;
    }

    /**
     * get the number of styles the workbook contains
     * @return count of cell styles
     */

    public short getNumCellStyles()
    {
        return (short) workbook.getNumExFormats();
    }

    /**