hssfsheet.java

java 读写word excel ppt

     */
    public void shiftRows( int startRow, int endRow, int n ) {
		shiftRows(startRow, endRow, n, false, false);
    }

    /**
     * Shifts rows between startRow and endRow n number of rows.
     * If you use a negative number, it will shift rows up.
     * Code ensures that rows don't wrap around
     *
     * <p>
     * Additionally shifts merged regions that are completely defined in these
     * rows (ie. merged 2 cells on a row to be shifted).
     * <p>
     * TODO Might want to add bounds checking here
     * @param startRow the row to start shifting
     * @param endRow the row to end shifting
     * @param n the number of rows to shift
     * @param copyRowHeight whether to copy the row height during the shift
     * @param resetOriginalRowHeight whether to set the original row's height to the default
     */
    public void shiftRows( int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight)
    {
        int s, e, inc;
        if ( n < 0 )
        {
            s = startRow;
            e = endRow;
            inc = 1;
        }
        else
        {
            s = endRow;
            e = startRow;
            inc = -1;
        }

        shiftMerged(startRow, endRow, n, true);
        sheet.shiftRowBreaks(startRow, endRow, n);
			
        for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
        {
            HSSFRow row = getRow( rowNum );
            HSSFRow row2Replace = getRow( rowNum + n );
            if ( row2Replace == null )
                row2Replace = createRow( rowNum + n );

            HSSFCell cell;




	    // Removes the cells before over writting them.
            for ( short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++ )
            {
                cell = row2Replace.getCell( col );
                if ( cell != null )
                    row2Replace.removeCell( cell );
            }
	    if (row == null) continue; // Nothing to do for this row
	    else {
		if (copyRowHeight) {
		    row2Replace.setHeight(row.getHeight());
		}

		if (resetOriginalRowHeight) {
		    row.setHeight((short)0xff);
		}
	    }
            for ( short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++ )
            {
                cell = row.getCell( col );
                if ( cell != null )
                {
                    row.removeCell( cell );
                    CellValueRecordInterface cellRecord = cell.getCellValueRecord();
                    cellRecord.setRow( rowNum + n );
                    row2Replace.createCellFromRecord( cellRecord );
                    sheet.addValueRecord( rowNum + n, cellRecord );
                }
            }
        }
        if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
        if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
    }

    protected void insertChartRecords( List records )
    {
        int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
        sheet.getRecords().addAll( window2Loc, records );
    }

    /**
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
     * @param colSplit      Horizonatal position of split.
     * @param rowSplit      Vertical position of split.
     * @param topRow        Top row visible in bottom pane
     * @param leftmostColumn   Left column visible in right pane.
     */
    public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
    {
        if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255");
        if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535");
        if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
        if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
        getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
    }

    /**
     * Creates a split (freezepane). Any existing freezepane or split pane is overwritten.
     * @param colSplit      Horizonatal position of split.
     * @param rowSplit      Vertical position of split.
     */
    public void createFreezePane( int colSplit, int rowSplit )
    {
        createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
    }

    /**
     * Creates a split pane. Any existing freezepane or split pane is overwritten.
     * @param xSplitPos      Horizonatal position of split (in 1/20th of a point).
     * @param ySplitPos      Vertical position of split (in 1/20th of a point).
     * @param topRow        Top row visible in bottom pane
     * @param leftmostColumn   Left column visible in right pane.
     * @param activePane    Active pane.  One of: PANE_LOWER_RIGHT,
     *                      PANE_UPPER_RIGHT, PANE_LOWER_LEFT, PANE_UPPER_LEFT
     * @see #PANE_LOWER_LEFT
     * @see #PANE_LOWER_RIGHT
     * @see #PANE_UPPER_LEFT
     * @see #PANE_UPPER_RIGHT
     */
    public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
    {
        getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
    }
    
    /**
     * Returns the information regarding the currently configured pane (split or freeze).
     * @return null if no pane configured, or the pane information.
     */
    public PaneInformation getPaneInformation() {
      return getSheet().getPaneInformation();
    }

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

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

    /**
     * Sets whether the formulas are shown in a viewer.
     * @param show whether to show formulas or not
     */
    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());

        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 toplevel drawing patriarch.  This will have the effect of
     * removing any existing drawings on this sheet.
     *
     * @return  The new patriarch.
     */
    public HSSFPatriarch createDrawingPatriarch()
    {
        // Create the drawing group if it doesn't already exist.
        book.createDrawingGroup();

        sheet.aggregateDrawingRecords(book.getDrawingManager());
        EscherAggregate agg = (EscherAggregate) sheet.findFirstRecordBySid(EscherAggregate.sid);
        HSSFPatriarch patriarch = new HSSFPatriarch(this);
        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;
    }

    /**
     * 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 fromCol the starting column index
     * @param column the column index
     */
    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.
     *
     * @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 a good choice.
         */
        char defaultChar = '0';
       
        FontRenderContext frc = new FontRenderContext(null, true, true);

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

        str = new AttributedString("" + defaultChar);
        str.addAttribute(TextAttribute.FAMILY, defaultFont.getFontName());
        str.addAttribute(TextAttribute.SIZE, new Float(defaultFont.getFontHeightInPoints()));
        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);
            if (cell == null) 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++) {
                    str = new AttributedString(lines[i] + defaultChar);
                    str.addAttribute(TextAttribute.FAMILY, font.getFontName());
                    str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
                    if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) str.addAttribute(TextAttribute.WEIGHT, TextAttribute.WEIGHT_BOLD);
                    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);
                                str.addAttribute(TextAttribute.FAMILY, fnt.getFontName(), j, j + 1);
                                str.addAttribute(TextAttribute.SIZE, new Float(fnt.getFontHeightInPoints()), j, j + 1);
                            }
                        }
                    }
                    layout = new TextLayout(str.getIterator(), frc);
                    width = Math.max(width, layout.getAdvance() / 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))
                            fmt = new DecimalFormat();
                        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());
                }

                str = new AttributedString(sval + defaultChar);
                str.addAttribute(TextAttribute.FAMILY, font.getFontName());
                str.addAttribute(TextAttribute.SIZE, new Float(font.getFontHeightInPoints()));
                layout = new TextLayout(str.getIterator(), frc);
                width = Math.max(width, layout.getAdvance() / defaultCharWidth);
            }

            if (width != -1) {
                sheet.setColumnWidth(column, (short) (width * 256));
            }
        }
    }

    /**
     * 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){
        return HSSFCell.findCellComment(sheet, row, column);
    }

}