hssfsheet.java

介绍java核心技术的pio编程方法以及基本概念

    public void setDisplayFormulas(boolean show) {
        sheet.setDisplayFormulas(show);
    }

    /**
     * Returns if formulas are displayed.
     * @return whether formulas are displayed
     */
    public boolean isDisplayFormulas() {
    	return sheet.isDisplayFormulas();
    }

    /**
     * Sets whether the RowColHeadings are shown in a viewer.
     * @param show whether to show RowColHeadings or not
     */
    public void setDisplayRowColHeadings(boolean show) {
        sheet.setDisplayRowColHeadings(show);
    }

    /**
     * Returns if RowColHeadings are displayed.
     * @return whether RowColHeadings are displayed
     */
    public boolean isDisplayRowColHeadings() {
    	return sheet.isDisplayRowColHeadings();
    }
    
    /**
     * Sets a page break at the indicated row
     * @param row FIXME: Document this!
     */
    public void setRowBreak(int row) {
    	validateRow(row);
    	sheet.setRowBreak(row, (short)0, (short)255);
    }

    /**
     * Determines if there is a page break at the indicated row
     * @param row FIXME: Document this!
     * @return FIXME: Document this!
     */
    public boolean isRowBroken(int row) {
    	return sheet.isRowBroken(row);
    }
    
    /**
     * Removes the page break at the indicated row
     * @param row
     */
    public void removeRowBreak(int row) {
    	sheet.removeRowBreak(row);
    }
    
    /**
     * Retrieves all the horizontal page breaks
     * @return all the horizontal page breaks, or null if there are no row page breaks
     */
    public int[] getRowBreaks(){
    	//we can probably cache this information, but this should be a sparsely used function
    	int count = sheet.getNumRowBreaks();
    	if (count > 0) {
    	  int[] returnValue = new int[count];
    	  Iterator iterator = sheet.getRowBreaks();
    	  int i = 0;
    	  while (iterator.hasNext()) {
    		PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
    		returnValue[i++] = (int)breakItem.main;
    	  }
    	  return returnValue;
    	}
    	return null;
    }

    /**
     * Retrieves all the vertical page breaks
     * @return all the vertical page breaks, or null if there are no column page breaks
     */
    public short[] getColumnBreaks(){
    	//we can probably cache this information, but this should be a sparsely used function 
    	int count = sheet.getNumColumnBreaks();
    	if (count > 0) {
    	  short[] returnValue = new short[count];
    	  Iterator iterator = sheet.getColumnBreaks();
    	  int i = 0;
    	  while (iterator.hasNext()) {
    		PageBreakRecord.Break breakItem = (PageBreakRecord.Break)iterator.next();
    		returnValue[i++] = breakItem.main;
    	  }
    	  return returnValue;
    	}
    	return null;
    }
    
    
    /**
     * Sets a page break at the indicated column
     * @param column
     */
    public void setColumnBreak(short column) {
    	validateColumn(column);
    	sheet.setColumnBreak(column, (short)0, (short)65535);
    }

    /**
     * Determines if there is a page break at the indicated column
     * @param column FIXME: Document this!
     * @return FIXME: Document this!
     */
    public boolean isColumnBroken(short column) {
    	return sheet.isColumnBroken(column);
    }
    
    /**
     * Removes a page break at the indicated column
     * @param column
     */
    public void removeColumnBreak(short column) {
    	sheet.removeColumnBreak(column);
    }
    
    /**
     * Runs a bounds check for row numbers
     * @param row
     */
    protected void validateRow(int row) {
    	if (row > 65535) throw new IllegalArgumentException("Maximum row number is 65535");
    	if (row < 0) throw new IllegalArgumentException("Minumum row number is 0");
    }
    
    /**
     * Runs a bounds check for column numbers
     * @param column
     */
    protected void validateColumn(short column) {
    	if (column > 255) throw new IllegalArgumentException("Maximum column number is 255");
    	if (column < 0)	throw new IllegalArgumentException("Minimum column number is 0");
    }

    /**
     * Aggregates the drawing records and dumps the escher record hierarchy
     * to the standard output.
     */
    public void dumpDrawingRecords(boolean fat)
    {
        sheet.aggregateDrawingRecords(book.getDrawingManager(), false);

        EscherAggregate r = (EscherAggregate) getSheet().findFirstRecordBySid(EscherAggregate.sid);
        List escherRecords = r.getEscherRecords();
        PrintWriter w = new PrintWriter(System.out);
        for ( Iterator iterator = escherRecords.iterator(); iterator.hasNext(); )
        {
            EscherRecord escherRecord = (EscherRecord) iterator.next();
            if (fat)
                System.out.println(escherRecord.toString());
            else
                escherRecord.display(w, 0);
        }
        w.flush();
    }

    /**
     * Creates the top-level drawing patriarch.  This will have
     *  the effect of removing any existing drawings on this
     *  sheet.
     * This may then be used to add graphics or charts
     * @return  The new patriarch.
     */
    public HSSFPatriarch createDrawingPatriarch()
    {
        // Create the drawing group if it doesn't already exist.
        book.createDrawingGroup();

        sheet.aggregateDrawingRecords(book.getDrawingManager(), true);
        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
        HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
        agg.clear();     // Initially the behaviour will be to clear out any existing shapes in the sheet when
                         // creating a new patriarch.
        agg.setPatriarch(patriarch);
        return patriarch;
    }
    
    /**
     * Returns the top-level drawing patriach, if there is
     *  one.
     * This will hold any graphics or charts for the sheet.
     * WARNING - calling this will trigger a parsing of the
     *  associated escher records. Any that aren't supported
     *  (such as charts and complex drawing types) will almost
     *  certainly be lost or corrupted when written out. Only
     *  use this with simple drawings, otherwise call
     *  {@link HSSFSheet#createDrawingPatriarch()} and
     *  start from scratch!
     */
    public HSSFPatriarch getDrawingPatriarch() {
    	book.findDrawingGroup();
    	
    	// If there's now no drawing manager, then there's
    	//  no drawing escher records on the workbook
    	if(book.getDrawingManager() == null) {
    		return null;
    	}
    	
    	int found = sheet.aggregateDrawingRecords(
    			book.getDrawingManager(), false
    	);
    	if(found == -1) {
    		// Workbook has drawing stuff, but this sheet doesn't
    		return null;
    	}
    	
    	// Grab our aggregate record, and wire it up
        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
        HSSFPatriarch patriarch = new HSSFPatriarch(this, agg);
        agg.setPatriarch(patriarch);
        
        // Have it process the records into high level objects
        //  as best it can do (this step may eat anything
        //  that isn't supported, you were warned...)
        agg.convertRecordsToUserModel();
        
        // Return what we could cope with
        return patriarch;
    }

    /**
     * Expands or collapses a column group.
     *
     * @param columnNumber      One of the columns in the group.
     * @param collapsed         true = collapse group, false = expand group.
     */
    public void setColumnGroupCollapsed( short columnNumber, boolean collapsed )
    {
        sheet.setColumnGroupCollapsed( columnNumber, collapsed );
    }

    /**
     * Create an outline for the provided column range.
     *
     * @param fromColumn        beginning of the column range.
     * @param toColumn          end of the column range.
     */
    public void groupColumn(short fromColumn, short toColumn)
    {
        sheet.groupColumnRange( fromColumn, toColumn, true );
    }

    public void ungroupColumn( short fromColumn, short toColumn )
    {
        sheet.groupColumnRange( fromColumn, toColumn, false );
    }

    public void groupRow(int fromRow, int toRow)
    {
        sheet.groupRowRange( fromRow, toRow, true );
    }

    public void ungroupRow(int fromRow, int toRow)
    {
        sheet.groupRowRange( fromRow, toRow, false );
    }

    public void setRowGroupCollapsed( int row, boolean collapse )
    {
        sheet.setRowGroupCollapsed( row, collapse );
    }

    /**
     * Sets the default column style for a given column.  POI will only apply this style to new cells added to the sheet.
     *
     * @param column the column index
     * @param style the style to set
     */
    public void setDefaultColumnStyle(short column, HSSFCellStyle style) {
	sheet.setColumn(column, new Short(style.getIndex()), null, null, null, null);
    }

    /**
     * Adjusts the column width to fit the contents.
     *
     * This process can be relatively slow on large sheets, so this should
     *  normally only be called once per column, at the end of your
     *  processing.
     *
     * @param column the column index
     */
    public void autoSizeColumn(short column) {
        AttributedString str;
        TextLayout layout;
        /**
         * Excel measures columns in units of 1/256th of a character width
         * but the docs say nothing about what particular character is used.
         * '0' looks to be a good choice.
         */
        char defaultChar = '0';
       
        /**
         * This is the multiple that the font height is scaled by when determining the
         * boundary of rotated text.
         */
        double fontHeightMultiple = 2.0;
       
        FontRenderContext frc = new FontRenderContext(null, true, true);

        HSSFWorkbook wb = new HSSFWorkbook(book);
        HSSFFont defaultFont = wb.getFontAt((short) 0);

        str = new AttributedString("" + defaultChar);
        copyAttributes(defaultFont, str, 0, 1);
        layout = new TextLayout(str.getIterator(), frc);
        int defaultCharWidth = (int)layout.getAdvance();

        double width = -1;
        for (Iterator it = rowIterator(); it.hasNext();) {
            HSSFRow row = (HSSFRow) it.next();
            HSSFCell cell = row.getCell(column);

            boolean isCellInMergedRegion = false;
            for (int i = 0 ; i < getNumMergedRegions() && ! isCellInMergedRegion; i++) {
                isCellInMergedRegion = getMergedRegionAt(i).contains(row.getRowNum(), column);
            }

            if (cell == null | isCellInMergedRegion) continue;

            HSSFCellStyle style = cell.getCellStyle();
            HSSFFont font = wb.getFontAt(style.getFontIndex());

            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                HSSFRichTextString rt = cell.getRichStringCellValue();
                String[] lines = rt.getString().split("\\n");
                for (int i = 0; i < lines.length; i++) {
                    String txt = lines[i] + defaultChar;
                    str = new AttributedString(txt);
                    copyAttributes(font, str, 0, txt.length());

                    if (rt.numFormattingRuns() > 0) {
                        for (int j = 0; j < lines[i].length(); j++) {
                            int idx = rt.getFontAtIndex(j);
                            if (idx != 0) {
                                HSSFFont fnt = wb.getFontAt((short) idx);
                                copyAttributes(fnt, str, j, j + 1);
                            }
                        }
                    }

                    layout = new TextLayout(str.getIterator(), frc);
                    if(style.getRotation() != 0){
                        /*
                         * Transform the text using a scale so that it's height is increased by a multiple of the leading,
                         * and then rotate the text before computing the bounds. The scale results in some whitespace around
                         * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
                         * is added by the standard Excel autosize.
                         */
                        AffineTransform trans = new AffineTransform();
                        trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
                        trans.concatenate(
                        AffineTransform.getScaleInstance(1, fontHeightMultiple)
                        );
                        width = Math.max(width, layout.getOutline(trans).getBounds().getWidth() / defaultCharWidth);
                    } else {
                        width = Math.max(width, layout.getBounds().getWidth() / defaultCharWidth);
                    }
                }
            } else {
                String sval = null;
                if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                    HSSFDataFormat dataformat = wb.createDataFormat();
                    short idx = style.getDataFormat();
                    String format = dataformat.getFormat(idx).replaceAll("\"", "");
                    double value = cell.getNumericCellValue();
                    try {
                        NumberFormat fmt;
                        if ("General".equals(format))
                            sval = "" + value;
                        else
                        {
                            fmt = new DecimalFormat(format);
                            sval = fmt.format(value);
                        }
                    } catch (Exception e) {
                        sval = "" + value;
                    }
                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                    sval = String.valueOf(cell.getBooleanCellValue());
                }
                if(sval != null) {
                    String txt = sval + defaultChar;
                    str = new AttributedString(txt);
                    copyAttributes(font, str, 0, txt.length());

                    layout = new TextLayout(str.getIterator(), frc);
                    if(style.getRotation() != 0){
                        /*
                         * Transform the text using a scale so that it's height is increased by a multiple of the leading,
                         * and then rotate the text before computing the bounds. The scale results in some whitespace around
                         * the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
                         * is added by the standard Excel autosize.
                         */
                        AffineTransform trans = new AffineTransform();
                        trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
                        trans.concatenate(
                        AffineTransform.getScaleInstance(1, fontHeightMultiple)
                        );
                        width = Math.max(width, layout.getOutline(trans).getBounds().getWidth() / defaultCharWidth);
                    } else {
                        width = Math.max(width, layout.getBounds().getWidth() / defaultCharWidth);
                    }
                }
            }

            if (width != -1) {
                if (width > Short.MAX_VALUE) { //width can be bigger that Short.MAX_VALUE!
                     width = Short.MAX_VALUE;
                }
                sheet.setColumnWidth(column, (short) (width * 256));
            }
        }
    }

    /**
     * Copy text attributes from the supplied HSSFFont to Java2D AttributedString
     */
    private void copyAttributes(HSSFFont font, AttributedString str, int startIdx, int endIdx){
        str.addAttribute(TextAttribute.FAMILY, font.getFontName(), startIdx, endIdx);
        str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
        if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD, startIdx, endIdx);
        if (font.getItalic() ) str.addAttribute(TextAttribute.POSTURE, TextAttribute.POSTURE_OBLIQUE, startIdx, endIdx);
        if (font.getUnderline() == HSSFFont.U_SINGLE ) str.addAttribute(TextAttribute.UNDERLINE, TextAttribute.UNDERLINE_ON, startIdx, endIdx);
    }

    /**
     * Returns cell comment for the specified row and column
     *
     * @return cell comment or <code>null</code> if not found
     */
     public HSSFComment getCellComment(int row, int column){
        // Don't call findCellComment directly, otherwise
        //  two calls to this method will result in two
        //  new HSSFComment instances, which is bad
        HSSFRow r = getRow(row);
        if(r != null) {
            HSSFCell c = r.getCell((short)column);
            if(c != null) {
                return c.getCellComment();
            } else {
                // No cell, so you will get new
                //  objects every time, sorry...
                return HSSFCell.findCellComment(sheet, row, column);
            }
        }
        return null;
    }

}