hssfworkbook.java

java 读写word excel ppt

        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 indexToSheet = r.getEqualsToIndexToSheet() -1;
            if(indexToSheet > -1) { //ignore "GLOBAL" name records
                int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(indexToSheet);
                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;
    }

    /**
     * Finds a font that matches the one with the supplied attributes
     */
    public HSSFFont findFont(short boldWeight, short color, short fontHeight,
                             String name, boolean italic, boolean strikeout,
                             short typeOffset, byte underline)
    {
//        System.out.println( boldWeight + ", " + color + ", " + fontHeight + ", " + name + ", " + italic + ", " + strikeout + ", " + typeOffset + ", " + underline );
        for (short i = 0; i < workbook.getNumberOfFontRecords(); i++)
        {
            if (i == 4)
                continue;

            FontRecord font = workbook.getFontRecordAt(i);
            HSSFFont hssfFont = new HSSFFont(i, font);
//            System.out.println( hssfFont.getBoldweight() + ", " + hssfFont.getColor() + ", " + hssfFont.getFontHeight() + ", " + hssfFont.getFontName() + ", " + hssfFont.getItalic() + ", " + hssfFont.getStrikeout() + ", " + hssfFont.getTypeOffset() + ", " + hssfFont.getUnderline() );
            if (hssfFont.getBoldweight() == boldWeight
                    && hssfFont.getColor() == color
                    && hssfFont.getFontHeight() == fontHeight
                    && hssfFont.getFontName().equals(name)
                    && hssfFont.getItalic() == italic
                    && hssfFont.getStrikeout() == strikeout
                    && hssfFont.getTypeOffset() == typeOffset
                    && hssfFont.getUnderline() == underline)
            {
//                System.out.println( "Found font" );
                return hssfFont;
            }
        }

//        System.out.println( "No font found" );
        return null;
    }

    /**
     * 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();
    }

    /**
     * get the cell style object at the given index
     * @param idx  index within the set of styles
     * @return HSSFCellStyle object at the index
     */

    public HSSFCellStyle getCellStyleAt(short idx)
    {
        ExtendedFormatRecord xfr = workbook.getExFormatAt(idx);
        HSSFCellStyle style = new HSSFCellStyle(idx, xfr);

        return style;
    }

    /**
     * Method write - write out this workbook to an Outputstream.  Constructs
     * a new POI POIFSFileSystem, passes in the workbook binary representation  and
     * writes it out.
     *
     * @param stream - the java OutputStream you wish to write the XLS to
     *
     * @exception IOException if anything can't be written.
     * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
     */

    public void write(OutputStream stream)
            throws IOException
    {
        byte[] bytes = getBytes();
        POIFSFileSystem fs = new POIFSFileSystem();

        fs.createDocument(new ByteArrayInputStream(bytes), "Workbook");

        if (preserveNodes) {
            List excepts = new ArrayList(1);
            excepts.add("Workbook");
            copyNodes(this.poifs,fs,excepts);
        }
        fs.writeFilesystem(stream);
        //poifs.writeFilesystem(stream);
    }

    /**
     * Method getBytes - get the bytes of just the HSSF portions of the XLS file.
     * Use this to construct a POI POIFSFileSystem yourself.
     *
     *
     * @return byte[] array containing the binary representation of this workbook and all contained
     *         sheets, rows, cells, etc.
     *
     * @see org.apache.poi.hssf.model.Workbook
     * @see org.apache.poi.hssf.model.Sheet
     */

    public byte[] getBytes()
    {
        if (log.check( POILogger.DEBUG ))
            log.log(DEBUG, "HSSFWorkbook.getBytes()");

        // before getting the workbook size we must tell the sheets that
        // serialization is about to occur.
        for (int k = 0; k < sheets.size(); k++)
            ((HSSFSheet) sheets.get(k)).getSheet().preSerialize();

        int wbsize = workbook.getSize();

        // log.debug("REMOVEME: old sizing method "+workbook.serialize().length);
        // ArrayList sheetbytes = new ArrayList(sheets.size());
        int totalsize = wbsize;

        for (int k = 0; k < sheets.size(); k++)
        {
            workbook.setSheetBof(k, totalsize);
            totalsize += ((HSSFSheet) sheets.get(k)).getSheet().getSize();
        }


/*        if (totalsize < 4096)
        {
            totalsize = 4096;
        }*/
        byte[] retval = new byte[totalsize];
        int pos = workbook.serialize(0, retval);

        // System.arraycopy(wb, 0, retval, 0, wb.length);
        for (int k = 0; k < sheets.size(); k++)
        {

            // byte[] sb = (byte[])sheetbytes.get(k);
            // System.arraycopy(sb, 0, retval, pos, sb.length);
            int len = ((HSSFSheet) sheets.get(k)).getSheet().serialize(pos,
                                retval);
            pos += len;   // sb.length;
        }
/*        for (int k = pos; k < totalsize; k++)
        {
            retval[k] = 0;
        }*/
        return retval;
    }

    /** @deprecated Do not call this method from your applications. Use the methods
     *  available in the HSSFRow to add string HSSFCells
     */
    public int addSSTString(String string)
    {
        return workbook.addSSTString(new UnicodeString(string));
    }

    /** @deprecated Do not call this method from your applications. Use the methods
     *  available in the HSSFRow to get string HSSFCells
     */
    public String getSSTString(int index)
    {
        return workbook.getSSTString(index).getString();
    }

    Workbook getWorkbook()
    {
        return workbook;
    }

    /** gets the total number of named ranges in the workboko
     * @return number of named ranges
     */
    public int getNumberOfNames(){
        int result = names.size();
        return result;
    }

    /** gets the Named range
     * @param index position of the named range
     * @return named range high level
     */
    public HSSFName getNameAt(int index){
        HSSFName result = (HSSFName) names.get(index);

        return result;
    }

    /** gets the named range name
     * @param index the named range index (0 based)
     * @return named range name
     */
    public String getNameName(int index){
        String result = getNameAt(index).getNameName();

        return result;
    }

	/**
	 * 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
	 */
	public void setPrintArea(int sheetIndex, String reference)
	{
		NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);