hssfcell.java

来自「EXCEL read and write」· Java 代码 · 共 1,104 行 · 第 1/3 页

     * @param errorCode the error value to set this cell to.  For formulas we'll set the
     *        precalculated value , for errors we'll set
     *        its value. For other types we will change the cell to an error
     *        cell and set its value.
     */
    public void setCellErrorValue(byte errorCode) {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        switch (cellType) {
            default:
                setCellType(CELL_TYPE_ERROR, false, row, col, styleIndex);
            case CELL_TYPE_ERROR:
                (( BoolErrRecord ) record).setValue(errorCode);
                break;
            case CELL_TYPE_FORMULA:
                ((FormulaRecordAggregate)record).getFormulaRecord().setCachedResultErrorCode(errorCode);
                break;
        }
    }
    /**
     * Chooses a new boolean value for the cell when its type is changing.<p/>
     *
     * Usually the caller is calling setCellType() with the intention of calling
     * setCellValue(boolean) straight afterwards.  This method only exists to give
     * the cell a somewhat reasonable value until the setCellValue() call (if at all).
     * TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
     */
    private boolean convertCellValueToBoolean() {

        switch (cellType) {
            case CELL_TYPE_BOOLEAN:
                return (( BoolErrRecord ) record).getBooleanValue();
            case CELL_TYPE_STRING:
                return Boolean.valueOf(((StringRecord)record).getString()).booleanValue();
            case CELL_TYPE_NUMERIC:
                return ((NumberRecord)record).getValue() != 0;

            // All other cases convert to false
            // These choices are not well justified.
            case CELL_TYPE_FORMULA:
                // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
            case CELL_TYPE_ERROR:
            case CELL_TYPE_BLANK:
                return false;
        }
        throw new RuntimeException("Unexpected cell type (" + cellType + ")");
    }

    /**
     * get the value of the cell as a boolean.  For strings, numbers, and errors, we throw an exception.
     * For blank cells we return a false.
     */
    public boolean getBooleanCellValue() {

        switch(cellType) {
            case CELL_TYPE_BLANK:
                return false;
            case CELL_TYPE_BOOLEAN:
                return (( BoolErrRecord ) record).getBooleanValue();
            default:
                throw typeMismatch(CELL_TYPE_BOOLEAN, cellType, false);
            case CELL_TYPE_FORMULA:
                break;
        }
        FormulaRecord fr = ((FormulaRecordAggregate)record).getFormulaRecord();
        checkFormulaCachedValueType(CELL_TYPE_BOOLEAN, fr);
        return fr.getCachedBooleanValue();
    }

    /**
     * get the value of the cell as an error code.  For strings, numbers, and booleans, we throw an exception.
     * For blank cells we return a 0.
     */
    public byte getErrorCellValue() {
        switch(cellType) {
            case CELL_TYPE_ERROR:
                return (( BoolErrRecord ) record).getErrorValue();
            default:
                throw typeMismatch(CELL_TYPE_ERROR, cellType, false);
            case CELL_TYPE_FORMULA:
                break;
        }
        FormulaRecord fr = ((FormulaRecordAggregate)record).getFormulaRecord();
        checkFormulaCachedValueType(CELL_TYPE_ERROR, fr);
        return (byte) fr.getCachedErrorValue();
    }

    /**
     * set the style for the cell.  The style should be an HSSFCellStyle created/retreived from
     * the HSSFWorkbook.
     *
     * @param style  reference contained in the workbook
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle()
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
     */

    public void setCellStyle(HSSFCellStyle style)
    {
        // Verify it really does belong to our workbook
        style.verifyBelongsToWorkbook(book);

        // Change our cell record to use this style
        record.setXFIndex(style.getIndex());
    }

    /**
     * get the style for the cell.  This is a reference to a cell style contained in the workbook
     * object.
     * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
     */
    public HSSFCellStyle getCellStyle()
    {
      short styleIndex=record.getXFIndex();
      ExtendedFormatRecord xf = book.getWorkbook().getExFormatAt(styleIndex);
      return new HSSFCellStyle(styleIndex, xf, book);
    }

    /**
     * Should only be used by HSSFSheet and friends.  Returns the low level CellValueRecordInterface record
     *
     * @return CellValueRecordInterface representing the cell via the low level api.
     */

    protected CellValueRecordInterface getCellValueRecord()
    {
        return record;
    }

    /**
     * @throws RuntimeException if the bounds are exceeded.
     */
    private void checkBounds(int cellNum) {
      if (cellNum > 255) {
          throw new RuntimeException("You cannot have more than 255 columns "+
                    "in a given row (IV).  Because Excel can't handle it");
      }
      else if (cellNum < 0) {
          throw new RuntimeException("You cannot reference columns with an index of less then 0.");
      }
    }

    /**
     * Sets this cell as the active cell for the worksheet
     */
    public void setAsActiveCell()
    {
        int row=record.getRow();
        short col=record.getColumn();
        this.sheet.getSheet().setActiveCellRow(row);
        this.sheet.getSheet().setActiveCellCol(col);
    }

    /**
     * Returns a string representation of the cell
     *
     * This method returns a simple representation,
     * anthing more complex should be in user code, with
     * knowledge of the semantics of the sheet being processed.
     *
     * Formula cells return the formula string,
     * rather than the formula result.
     * Dates are displayed in dd-MMM-yyyy format
     * Errors are displayed as #ERR&lt;errIdx&gt;
     */
    public String toString() {
        switch (getCellType()) {
            case CELL_TYPE_BLANK:
                return "";
            case CELL_TYPE_BOOLEAN:
                return getBooleanCellValue()?"TRUE":"FALSE";
            case CELL_TYPE_ERROR:
                return ErrorEval.getText((( BoolErrRecord ) record).getErrorValue());
            case CELL_TYPE_FORMULA:
                return getCellFormula();
            case CELL_TYPE_NUMERIC:
                //TODO apply the dataformat for this cell
                if (HSSFDateUtil.isCellDateFormatted(this)) {
                    DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
                    return sdf.format(getDateCellValue());
                } else {
                    return  getNumericCellValue() + "";
                }
            case CELL_TYPE_STRING:
                return getStringCellValue();
            default:
                return "Unknown Cell Type: " + getCellType();
        }
    }

    /**
     * Assign a comment to this cell. If the supplied
     *  comment is null, the comment for this cell
     *  will be removed.
     *
     * @param comment comment associated with this cell
     */
    public void setCellComment(HSSFComment comment){
        if(comment == null) {
            removeCellComment();
            return;
        }

        comment.setRow((short)record.getRow());
        comment.setColumn(record.getColumn());
        this.comment = comment;
    }

    /**
     * Returns comment associated with this cell
     *
     * @return comment associated with this cell
     */
     public HSSFComment getCellComment(){
        if (comment == null) {
            comment = findCellComment(sheet.getSheet(), record.getRow(), record.getColumn());
        }
        return comment;
    }

    /**
     * Removes the comment for this cell, if
     *  there is one.
     * WARNING - some versions of excel will loose
     *  all comments after performing this action!
     */
    public void removeCellComment() {
        HSSFComment comment = findCellComment(sheet.getSheet(), record.getRow(), record.getColumn());
        this.comment = null;

        if(comment == null) {
            // Nothing to do
            return;
        }

        // Zap the underlying NoteRecord
        List sheetRecords = sheet.getSheet().getRecords();
        sheetRecords.remove(comment.getNoteRecord());

        // If we have a TextObjectRecord, is should
        //  be proceeed by:
        // MSODRAWING with container
        // OBJ
        // MSODRAWING with EscherTextboxRecord
        if(comment.getTextObjectRecord() != null) {
            TextObjectRecord txo = comment.getTextObjectRecord();
            int txoAt = sheetRecords.indexOf(txo);

            if(sheetRecords.get(txoAt-3) instanceof DrawingRecord &&
                sheetRecords.get(txoAt-2) instanceof ObjRecord &&
                sheetRecords.get(txoAt-1) instanceof DrawingRecord) {
                // Zap these, in reverse order
                sheetRecords.remove(txoAt-1);
                sheetRecords.remove(txoAt-2);
                sheetRecords.remove(txoAt-3);
            } else {
                throw new IllegalStateException("Found the wrong records before the TextObjectRecord, can't remove comment");
            }

            // Now remove the text record
            sheetRecords.remove(txo);
        }
    }

    /**
     * Cell comment finder.
     * Returns cell comment for the specified sheet, row and column.
     *
     * @return cell comment or <code>null</code> if not found
     */
    protected static HSSFComment findCellComment(Sheet sheet, int row, int column){
        HSSFComment comment = null;
        HashMap txshapes = new HashMap(); //map shapeId and TextObjectRecord
        for (Iterator it = sheet.getRecords().iterator(); it.hasNext(); ) {
           RecordBase rec = (RecordBase) it.next();
           if (rec instanceof NoteRecord){
               NoteRecord note = (NoteRecord)rec;
               if (note.getRow() == row && note.getColumn() == column){
                   TextObjectRecord txo = (TextObjectRecord)txshapes.get(new Integer(note.getShapeId()));
                   comment = new HSSFComment(note, txo);
                   comment.setRow(note.getRow());
                   comment.setColumn(note.getColumn());
                   comment.setAuthor(note.getAuthor());
                   comment.setVisible(note.getFlags() == NoteRecord.NOTE_VISIBLE);
                   comment.setString(txo.getStr());
                   break;
               }
           } else if (rec instanceof ObjRecord){
               ObjRecord obj = (ObjRecord)rec;
               SubRecord sub = (SubRecord)obj.getSubRecords().get(0);
               if (sub instanceof CommonObjectDataSubRecord){
                   CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord)sub;
                   if (cmo.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_COMMENT){
                       //find the nearest TextObjectRecord which holds comment's text and map it to its shapeId
                       while(it.hasNext()) {
                           rec = ( Record ) it.next();
                           if (rec instanceof TextObjectRecord) {
                               txshapes.put(new Integer(cmo.getObjectId()), rec);
                               break;
                           }
                       }

                   }
               }
           }
        }
        return comment;
   }

    /**
     * Returns hyperlink associated with this cell
     *
     * @return hyperlink associated with this cell or null if not found
     */
    public HSSFHyperlink getHyperlink(){
        for (Iterator it = sheet.getSheet().getRecords().iterator(); it.hasNext(); ) {
            RecordBase rec = (RecordBase) it.next();
            if (rec instanceof HyperlinkRecord){
                HyperlinkRecord link = (HyperlinkRecord)rec;
                if(link.getFirstColumn() == record.getColumn() && link.getFirstRow() == record.getRow()){
                    return new HSSFHyperlink(link);
                }
            }
        }
        return null;
    }

    /**
     * Assign a hypelrink to this cell
     *
     * @param link hypelrink associated with this cell
     */
    public void setHyperlink(HSSFHyperlink link){
        link.setFirstRow(record.getRow());
        link.setLastRow(record.getRow());
        link.setFirstColumn(record.getColumn());
        link.setLastColumn(record.getColumn());

        switch(link.getType()){
            case HSSFHyperlink.LINK_EMAIL:
            case HSSFHyperlink.LINK_URL:
                link.setLabel("url");
                break;
            case HSSFHyperlink.LINK_FILE:
                link.setLabel("file");
                break;
            case HSSFHyperlink.LINK_DOCUMENT:
                link.setLabel("place");
                break;
        }

        int eofLoc = sheet.getSheet().findFirstRecordLocBySid( EOFRecord.sid );
        sheet.getSheet().getRecords().add( eofLoc, link.record );
    }
    /**
     * Only valid for formula cells
     * @return one of ({@link #CELL_TYPE_NUMERIC}, {@link #CELL_TYPE_STRING},
     *     {@link #CELL_TYPE_BOOLEAN}, {@link #CELL_TYPE_ERROR}) depending
     * on the cached value of the formula
     */
    public int getCachedFormulaResultType() {
        if (this.cellType != CELL_TYPE_FORMULA) {
            throw new IllegalStateException("Only formula cells have cached results");
        }
        return ((FormulaRecordAggregate)record).getFormulaRecord().getCachedResultType();
    }
}