workbook.java

Office格式转换代码

                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0xfffff800);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

            case 20 :
                retval.setFontIndex(( short ) 1);
                retval.setFormatIndex(( short ) 0x9);
                retval.setCellOptions(( short ) 0xfffffff5);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0xfffff800);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

                // unused from this point down
            case 21 :
                retval.setFontIndex(( short ) 5);
                retval.setFormatIndex(( short ) 0x0);
                retval.setCellOptions(( short ) 0x1);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0x800);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

            case 22 :
                retval.setFontIndex(( short ) 6);
                retval.setFormatIndex(( short ) 0x0);
                retval.setCellOptions(( short ) 0x1);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0x5c00);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

            case 23 :
                retval.setFontIndex(( short ) 0);
                retval.setFormatIndex(( short ) 0x31);
                retval.setCellOptions(( short ) 0x1);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0x5c00);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

            case 24 :
                retval.setFontIndex(( short ) 0);
                retval.setFormatIndex(( short ) 0x8);
                retval.setCellOptions(( short ) 0x1);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0x5c00);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;

            case 25 :
                retval.setFontIndex(( short ) 6);
                retval.setFormatIndex(( short ) 0x8);
                retval.setCellOptions(( short ) 0x1);
                retval.setAlignmentOptions(( short ) 0x20);
                retval.setIndentionOptions(( short ) 0x5c00);
                retval.setBorderOptions(( short ) 0);
                retval.setPaletteOptions(( short ) 0);
                retval.setAdtlPaletteOptions(( short ) 0);
                retval.setFillPaletteOptions(( short ) 0x20c0);
                break;
        }
        return retval;
    }

    /**
     * creates an default cell type ExtendedFormatRecord object.
     * @return ExtendedFormatRecord with intial defaults (cell-type)
     */

    protected ExtendedFormatRecord createExtendedFormat() {
        ExtendedFormatRecord retval = new ExtendedFormatRecord();

        retval.setFontIndex(( short ) 0);
        retval.setFormatIndex(( short ) 0x0);
        retval.setCellOptions(( short ) 0x1);
        retval.setAlignmentOptions(( short ) 0x20);
        retval.setIndentionOptions(( short ) 0);
        retval.setBorderOptions(( short ) 0);
        retval.setPaletteOptions(( short ) 0);
        retval.setAdtlPaletteOptions(( short ) 0);
        retval.setFillPaletteOptions(( short ) 0x20c0);
        retval.setTopBorderPaletteIdx(HSSFColor.BLACK.index);
        retval.setBottomBorderPaletteIdx(HSSFColor.BLACK.index);
        retval.setLeftBorderPaletteIdx(HSSFColor.BLACK.index);
        retval.setRightBorderPaletteIdx(HSSFColor.BLACK.index);
        return retval;
    }

    /**
     * Creates a StyleRecord object
     * @param id        the number of the style record to create (meaning its position in
     *                  a file as MS Excel would create it.
     * @return record containing a StyleRecord
     * @see org.apache.poi.hssf.record.StyleRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createStyle(int id) {   // we'll need multiple editions
        StyleRecord retval = new StyleRecord();

        switch (id) {

            case 0 :
                retval.setIndex(( short ) 0xffff8010);
                retval.setBuiltin(( byte ) 3);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;

            case 1 :
                retval.setIndex(( short ) 0xffff8011);
                retval.setBuiltin(( byte ) 6);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;

            case 2 :
                retval.setIndex(( short ) 0xffff8012);
                retval.setBuiltin(( byte ) 4);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;

            case 3 :
                retval.setIndex(( short ) 0xffff8013);
                retval.setBuiltin(( byte ) 7);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;

            case 4 :
                retval.setIndex(( short ) 0xffff8000);
                retval.setBuiltin(( byte ) 0);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;

            case 5 :
                retval.setIndex(( short ) 0xffff8014);
                retval.setBuiltin(( byte ) 5);
                retval.setOutlineStyleLevel(( byte ) 0xffffffff);
                break;
        }
        return retval;
    }

    /**
     * Creates a palette record initialized to the default palette
     * @return a PaletteRecord instance populated with the default colors
     * @see org.apache.poi.hssf.record.PaletteRecord
     */
    protected PaletteRecord createPalette()
    {
        return new PaletteRecord(PaletteRecord.sid);
    }
    
    /**
     * Creates the UseSelFS object with the use natural language flag set to 0 (false)
     * @return record containing a UseSelFSRecord
     * @see org.apache.poi.hssf.record.UseSelFSRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createUseSelFS() {
        UseSelFSRecord retval = new UseSelFSRecord();

        retval.setFlag(( short ) 0);
        return retval;
    }

    /**
     * create a "bound sheet" or "bundlesheet" (depending who you ask) record
     * Always sets the sheet's bof to 0.  You'll need to set that yourself.
     * @param id either sheet 0,1 or 2.
     * @return record containing a BoundSheetRecord
     * @see org.apache.poi.hssf.record.BoundSheetRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createBoundSheet(int id) {   // 1,2,3 sheets
        BoundSheetRecord retval = new BoundSheetRecord();

        switch (id) {

            case 0 :
                retval.setPositionOfBof(0x0);   // should be set later
                retval.setOptionFlags(( short ) 0);
                retval.setSheetnameLength(( byte ) 0x6);
                retval.setCompressedUnicodeFlag(( byte ) 0);
                retval.setSheetname("Sheet1");
                break;

            case 1 :
                retval.setPositionOfBof(0x0);   // should be set later
                retval.setOptionFlags(( short ) 0);
                retval.setSheetnameLength(( byte ) 0x6);
                retval.setCompressedUnicodeFlag(( byte ) 0);
                retval.setSheetname("Sheet2");
                break;

            case 2 :
                retval.setPositionOfBof(0x0);   // should be set later
                retval.setOptionFlags(( short ) 0);
                retval.setSheetnameLength(( byte ) 0x6);
                retval.setCompressedUnicodeFlag(( byte ) 0);
                retval.setSheetname("Sheet3");
                break;
        }
        return retval;
    }

    /**
     * Creates the Country record with the default country set to 1
     * and current country set to 7 in case of russian locale ("ru_RU") and 1 otherwise
     * @return record containing a CountryRecord
     * @see org.apache.poi.hssf.record.CountryRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createCountry() {   // what a novel idea, create your own!
        CountryRecord retval = new CountryRecord();

        retval.setDefaultCountry(( short ) 1);

        // from Russia with love ;)
        if ( Locale.getDefault().toString().equals( "ru_RU" ) ) {
	        retval.setCurrentCountry(( short ) 7);
        }
        else {
	        retval.setCurrentCountry(( short ) 1);
        }

        return retval;
    }

    /**
     * Creates the SST record with no strings and the unique/num string set to 0
     * @return record containing a SSTRecord
     * @see org.apache.poi.hssf.record.SSTRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createSST() {
        return new SSTRecord();
    }

    /**
     * Creates the ExtendedSST record with numstrings per bucket set to 0x8.  HSSF
     * doesn't yet know what to do with this thing, but we create it with nothing in
     * it hardly just to make Excel happy and our sheets look like Excel's
     *
     * @return record containing an ExtSSTRecord
     * @see org.apache.poi.hssf.record.ExtSSTRecord
     * @see org.apache.poi.hssf.record.Record
     */

    protected Record createExtendedSST() {
        ExtSSTRecord retval = new ExtSSTRecord();

        retval.setNumStringsPerBucket(( short ) 0x8);
        return retval;
    }

    /**
     * creates the EOF record
     * @see org.apache.poi.hssf.record.EOFRecord
     * @see org.apache.poi.hssf.record.Record
     * @return record containing a EOFRecord
     */

    protected Record createEOF() {
        return new EOFRecord();
    }

    public SheetReferences getSheetReferences() {
        SheetReferences refs = new SheetReferences();
        
        if (externSheet != null) {
            for (int k = 0; k < externSheet.getNumOfREFStructures(); k++) {
                
                String sheetName = findSheetNameFromExternSheet((short)k);
                refs.addSheetReference(sheetName, k);
                
            }
        }
        return refs;
    }

    /** finds the sheet name by his extern sheet index
     * @param num extern sheet index
     * @return sheet name
     */
    public String findSheetNameFromExternSheet(short num){
        String result="";

        short indexToSheet = externSheet.getREFRecordAt(num).getIndexToFirstSupBook();
        if (indexToSheet>-1) { //error check, bail out gracefully!
            result = getSheetName(indexToSheet);
        }

        return result;
    }

    /**
     * Finds the sheet index for a particular external sheet number.
     * @param externSheetNumber     The external sheet number to convert
     * @return  The index to the sheet found.
     */
    public int getSheetIndexFromExternSheetIndex(int externSheetNumber)
    {
        if (externSheetNumber >= externSheet.getNumOfREFStructures())
            return -1;
        else
            return externSheet.getREFRecordAt(externSheetNumber).getIndexToFirstSupBook();
    }

    /** returns the extern sheet number for specific sheet number ,
     *  if this sheet doesn't exist in extern sheet , add it
     * @param sheetNumber sheet number
     * @return index to extern sheet
     */
    public short checkExternSheet(int sheetNumber){

        int i = 0;
        boolean flag = false;
        short result = 0;

        if (externSheet == null) {
            externSheet = createExternSheet();
        }

        //Trying to find reference to this sheet
        while (i < externSheet.getNumOfREFStructures() && !flag){
            ExternSheetSubRecord record = externSheet.getREFRecordAt(i);

            if (record.getIndexToFirstSupBook() ==  sheetNumber &&
            record.getIndexToLastSupBook() == sheetNumber){
                flag = true;
                result = (short) i;
            }

            ++i;
        }

        //We Havent found reference