Fix cell.getRichStringCellValue() for formula cells with string results

[poi.git] / src / java / org / apache / poi / hssf / usermodel / HSSFCell.java

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */



/*
 * Cell.java
 *
 * Created on September 30, 2001, 3:46 PM
 */
package org.apache.poi.hssf.usermodel;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;

import org.apache.poi.hssf.model.FormulaParser;
import org.apache.poi.hssf.model.Sheet;
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.record.*;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import org.apache.poi.hssf.record.formula.Ptg;

/**
 * High level representation of a cell in a row of a spreadsheet.
 * Cells can be numeric, formula-based or string-based (text).  The cell type
 * specifies this.  String cells cannot conatin numbers and numeric cells cannot
 * contain strings (at least according to our model).  Client apps should do the
 * conversions themselves.  Formula cells have the formula string, as well as 
 * the formula result, which can be numeric or string. 
 * <p>
 * Cells should have their number (0 based) before being added to a row.  Only
 * cells that have values should be added.
 * <p>
 *
 * @author  Andrew C. Oliver (acoliver at apache dot org)
 * @author  Dan Sherman (dsherman at isisph.com)
 * @author  Brian Sanders (kestrel at burdell dot org) Active Cell support
 * @author  Yegor Kozlov cell comments support
 * @version 1.0-pre
 */

public class HSSFCell
{

    /**
     * Numeric Cell type (0)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_NUMERIC           = 0;

    /**
     * String Cell type (1)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_STRING            = 1;

    /**
     * Formula Cell type (2)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_FORMULA           = 2;

    /**
     * Blank Cell type (3)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_BLANK             = 3;

    /**
     * Boolean Cell type (4)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_BOOLEAN           = 4;

    /**
     * Error Cell type (5)
     * @see #setCellType(int)
     * @see #getCellType()
     */

    public final static int          CELL_TYPE_ERROR             = 5;
    public final static short        ENCODING_UNCHANGED          = -1;
    public final static short        ENCODING_COMPRESSED_UNICODE = 0;
    public final static short        ENCODING_UTF_16             = 1;
    private int                      cellType;
    private HSSFRichTextString       stringValue;
    private short                    encoding = ENCODING_UNCHANGED;
    private HSSFWorkbook             book;
    private Sheet                    sheet;
    private CellValueRecordInterface record;
    private HSSFComment              comment;

    /**
     * Creates new Cell - Should only be called by HSSFRow.  This creates a cell
     * from scratch.
     * <p>
     * When the cell is initially created it is set to CELL_TYPE_BLANK. Cell types
     * can be changed/overwritten by calling setCellValue with the appropriate
     * type as a parameter although conversions from one type to another may be
     * prohibited.
     *
     * @param book - Workbook record of the workbook containing this cell
     * @param sheet - Sheet record of the sheet containing this cell
     * @param row   - the row of this cell
     * @param col   - the column for this cell
     *
     * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short)
     */

    //protected HSSFCell(Workbook book, Sheet sheet, short row, short col)
    protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row, short col)
    {
        checkBounds(col);
        stringValue  = null;
        this.book    = book;
        this.sheet   = sheet;

        // Relying on the fact that by default the cellType is set to 0 which
        // is different to CELL_TYPE_BLANK hence the following method call correctly
        // creates a new blank cell.
        short xfindex = sheet.getXFIndexForColAt(col);
        setCellType(CELL_TYPE_BLANK, false, row, col,xfindex);
    }

    /**
     * Creates new Cell - Should only be called by HSSFRow.  This creates a cell
     * from scratch.
     *
     * @param book - Workbook record of the workbook containing this cell
     * @param sheet - Sheet record of the sheet containing this cell
     * @param row   - the row of this cell
     * @param col   - the column for this cell
     * @param type  - CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_FORMULA, CELL_TYPE_BLANK,
     *                CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR
     *                Type of cell
     * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short,int)
     */

    //protected HSSFCell(Workbook book, Sheet sheet, short row, short col,
    protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row, short col,
                       int type)
    {
        checkBounds(col);
        cellType     = -1; // Force 'setCellType' to create a first Record
        stringValue  = null;
        this.book    = book;
        this.sheet   = sheet;
        
        short xfindex = sheet.getXFIndexForColAt(col);
        setCellType(type,false,row,col,xfindex);
    }

    /**
     * Creates an HSSFCell from a CellValueRecordInterface.  HSSFSheet uses this when
     * reading in cells from an existing sheet.
     *
     * @param book - Workbook record of the workbook containing this cell
     * @param sheet - Sheet record of the sheet containing this cell
     * @param cval - the Cell Value Record we wish to represent
     */

    //protected HSSFCell(Workbook book, Sheet sheet, short row,
    protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row,
                       CellValueRecordInterface cval)
    {
        record      = cval;
        cellType    = determineType(cval);
        stringValue = null;
        this.book   = book;
        this.sheet  = sheet;
        switch (cellType)
        {
            case CELL_TYPE_STRING :
                stringValue = new HSSFRichTextString(book.getWorkbook(), (LabelSSTRecord ) cval);
                break;

            case CELL_TYPE_BLANK :
                break;

            case CELL_TYPE_FORMULA :
                stringValue=new HSSFRichTextString(((FormulaRecordAggregate) cval).getStringValue());
                break;
        }
        ExtendedFormatRecord xf = book.getWorkbook().getExFormatAt(cval.getXFIndex());

        setCellStyle(new HSSFCellStyle(( short ) cval.getXFIndex(), xf, book));
    }

    /**
     * private constructor to prevent blank construction
     */
    private HSSFCell()
    {
    }

    /**
     * used internally -- given a cell value record, figure out its type
     */
    private int determineType(CellValueRecordInterface cval)
    {
        Record record = ( Record ) cval;
        int    sid    = record.getSid();
        int    retval = 0;

        switch (sid)
        {

            case NumberRecord.sid :
                retval = HSSFCell.CELL_TYPE_NUMERIC;
                break;

            case BlankRecord.sid :
                retval = HSSFCell.CELL_TYPE_BLANK;
                break;

            case LabelSSTRecord.sid :
                retval = HSSFCell.CELL_TYPE_STRING;
                break;

            case FormulaRecordAggregate.sid :
                retval = HSSFCell.CELL_TYPE_FORMULA;
                break;

            case BoolErrRecord.sid :
                BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;

                retval = (boolErrRecord.isBoolean())
                         ? HSSFCell.CELL_TYPE_BOOLEAN
                         : HSSFCell.CELL_TYPE_ERROR;
                break;
        }
        return retval;
    }
    
    /**
     * Returns the Workbook that this Cell is bound to
     * @return
     */
    protected Workbook getBoundWorkbook() {
        return book.getWorkbook();
    }

    /**
     * Set the cell's number within the row (0 based).
     * @param num  short the cell number
     * @deprecated Doesn't update the row's idea of what cell this is, use {@link HSSFRow#moveCell(HSSFCell, short)} instead
     */
    public void setCellNum(short num)
    {
        record.setColumn(num);
    }
    
    /**
     * Updates the cell record's idea of what
     *  column it belongs in (0 based)
     * @param num the new cell number
     */
    protected void updateCellNum(short num)
    {
        record.setColumn(num);
    }

    /**
     *  get the cell's number within the row
     * @return short reperesenting the column number (logical!)
     */

    public short getCellNum()
    {
        return record.getColumn();
    }

    /**
     * set the cells type (numeric, formula or string)
     * @see #CELL_TYPE_NUMERIC
     * @see #CELL_TYPE_STRING
     * @see #CELL_TYPE_FORMULA
     * @see #CELL_TYPE_BLANK
     * @see #CELL_TYPE_BOOLEAN
     * @see #CELL_TYPE_ERROR
     */

    public void setCellType(int cellType)
    {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        setCellType(cellType, true, row, col, styleIndex);
    }

    /**
     * sets the cell type. The setValue flag indicates whether to bother about
     *  trying to preserve the current value in the new record if one is created.
     *  <p>
     *  The @see #setCellValue method will call this method with false in setValue
     *  since it will overwrite the cell value later
     *
     */

    private void setCellType(int cellType, boolean setValue, int row,short col, short styleIndex)
    {

        // if (cellType == CELL_TYPE_FORMULA)
        // {
        // throw new RuntimeException(
        // "Formulas have not been implemented in this release");
        // }
        if (cellType > CELL_TYPE_ERROR)
        {
            throw new RuntimeException("I have no idea what type that is!");
        }
        switch (cellType)
        {

            case CELL_TYPE_FORMULA :
                FormulaRecordAggregate frec = null;

                if (cellType != this.cellType)
                {
                    frec = new FormulaRecordAggregate(new FormulaRecord(),null);
                }
                else
                {
                    frec = ( FormulaRecordAggregate ) record;
                }
                frec.setColumn(col);
                if (setValue)
                {
                    frec.getFormulaRecord().setValue(getNumericCellValue());
                }
                frec.setXFIndex(styleIndex);
                frec.setRow(row);
                record = frec;
                break;

            case CELL_TYPE_NUMERIC :
                NumberRecord nrec = null;

                if (cellType != this.cellType)
                {
                    nrec = new NumberRecord();
                }
                else
                {
                    nrec = ( NumberRecord ) record;
                }
                nrec.setColumn(col);
                if (setValue)
                {
                    nrec.setValue(getNumericCellValue());
                }
                nrec.setXFIndex(styleIndex);
                nrec.setRow(row);
                record = nrec;
                break;

            case CELL_TYPE_STRING :
                LabelSSTRecord lrec = null;

                if (cellType != this.cellType)
                {
                    lrec = new LabelSSTRecord();
                }
                else
                {
                    lrec = ( LabelSSTRecord ) record;
                }
                lrec.setColumn(col);
                lrec.setRow(row);
                lrec.setXFIndex(styleIndex);
                if (setValue)
                {
                    if ((getStringCellValue() != null)
                            && (!getStringCellValue().equals("")))
                    {
                        int sst = 0;

                        UnicodeString str = getRichStringCellValue().getUnicodeString();
//jmh                        if (encoding == ENCODING_COMPRESSED_UNICODE)
//jmh                        {
//                      jmh                            str.setCompressedUnicode();
//                      jmh                        } else if (encoding == ENCODING_UTF_16)
//                      jmh                        {
//                      jmh                            str.setUncompressedUnicode();
//                      jmh                        }
                        sst = book.getWorkbook().addSSTString(str);
                        lrec.setSSTIndex(sst);
                        getRichStringCellValue().setUnicodeString(book.getWorkbook().getSSTString(sst));
                    }
                }
                record = lrec;
                break;

            case CELL_TYPE_BLANK :
                BlankRecord brec = null;

                if (cellType != this.cellType)
                {
                    brec = new BlankRecord();
                }
                else
                {
                    brec = ( BlankRecord ) record;
                }
                brec.setColumn(col);

                // During construction the cellStyle may be null for a Blank cell.
                brec.setXFIndex(styleIndex);
                brec.setRow(row);
                record = brec;
                break;

            case CELL_TYPE_BOOLEAN :
                BoolErrRecord boolRec = null;

                if (cellType != this.cellType)
                {
                    boolRec = new BoolErrRecord();
                }
                else
                {
                    boolRec = ( BoolErrRecord ) record;
                }
                boolRec.setColumn(col);
                if (setValue)
                {
                    boolRec.setValue(convertCellValueToBoolean());
                }
                boolRec.setXFIndex(styleIndex);
                boolRec.setRow(row);
                record = boolRec;
                break;

            case CELL_TYPE_ERROR :
                BoolErrRecord errRec = null;

                if (cellType != this.cellType)
                {
                    errRec = new BoolErrRecord();
                }
                else
                {
                    errRec = ( BoolErrRecord ) record;
                }
                errRec.setColumn(col);
                if (setValue)
                {
                    errRec.setValue(getErrorCellValue());
                }
                errRec.setXFIndex(styleIndex);
                errRec.setRow(row);
                record = errRec;
                break;
        }
        if (cellType != this.cellType && 
            this.cellType!=-1 )  // Special Value to indicate an uninitialized Cell
        {
            int loc = sheet.getLoc();

            sheet.replaceValueRecord(record);
            sheet.setLoc(loc);
        }
        this.cellType = cellType;
    }

    /**
     * get the cells type (numeric, formula or string)
     * @see #CELL_TYPE_STRING
     * @see #CELL_TYPE_NUMERIC
     * @see #CELL_TYPE_FORMULA
     * @see #CELL_TYPE_BOOLEAN
     * @see #CELL_TYPE_ERROR
     */

    public int getCellType()
    {
        return cellType;
    }

    /**
     * set a numeric value for the cell
     *
     * @param value  the numeric value to set this cell to.  For formulas we'll set the
     *        precalculated value, for numerics we'll set its value. For other types we
     *        will change the cell to a numeric cell and set its value.
     */
    public void setCellValue(double value)
    {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        if ((cellType != CELL_TYPE_NUMERIC) && (cellType != CELL_TYPE_FORMULA))
        {
            setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
        }
        
        // Save into the apropriate record
        if(record instanceof FormulaRecordAggregate) {
                (( FormulaRecordAggregate ) record).getFormulaRecord().setValue(value);
        } else {
                (( NumberRecord ) record).setValue(value);
        }
    }

    /**
     * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
     * a date.
     *
     * @param value  the date value to set this cell to.  For formulas we'll set the
     *        precalculated value, for numerics we'll set its value. For other types we
     *        will change the cell to a numeric cell and set its value.
     */
    public void setCellValue(Date value)
    {
        setCellValue(HSSFDateUtil.getExcelDate(value, this.book.getWorkbook().isUsing1904DateWindowing()));
    }

    /**
     * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
     * a date.
     * 
     * This will set the cell value based on the Calendar's timezone. As Excel
     * does not support timezones this means that both 20:00+03:00 and
     * 20:00-03:00 will be reported as the same value (20:00) even that there
     * are 6 hours difference between the two times. This difference can be
     * preserved by using <code>setCellValue(value.getTime())</code> which will
     * automatically shift the times to the default timezone.
     *
     * @param value  the date value to set this cell to.  For formulas we'll set the
     *        precalculated value, for numerics we'll set its value. For othertypes we
     *        will change the cell to a numeric cell and set its value.
     */
    public void setCellValue(Calendar value)
    {
        setCellValue( HSSFDateUtil.getExcelDate(value, this.book.getWorkbook().isUsing1904DateWindowing()) );
    }

    /**
     * set a string value for the cell. Please note that if you are using
     * full 16 bit unicode you should call <code>setEncoding()</code> first.
     *
     * @param value  value to set the cell to.  For formulas we'll set the formula
     * string, for String cells we'll set its value.  For other types we will
     * change the cell to a string cell and set its value.
     * If value is null then we will change the cell to a Blank cell.
     * @deprecated Use setCellValue(HSSFRichTextString) instead.
     */

    public void setCellValue(String value)
    {
      HSSFRichTextString str = new HSSFRichTextString(value);
      setCellValue(str);
    }

    /**
     * set a string value for the cell. Please note that if you are using
     * full 16 bit unicode you should call <code>setEncoding()</code> first.
     *
     * @param value  value to set the cell to.  For formulas we'll set the formula
     * string, for String cells we'll set its value.  For other types we will
     * change the cell to a string cell and set its value.
     * If value is null then we will change the cell to a Blank cell.
     */
    public void setCellValue(HSSFRichTextString value) {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        if (value == null) {
            setCellType(CELL_TYPE_BLANK, false, row, col, styleIndex);
            return;
        }
        if (cellType == CELL_TYPE_FORMULA) {
            // Set the 'pre-evaluated result' for the formula 
            // note - formulas do not preserve text formatting.
            FormulaRecordAggregate fr = (FormulaRecordAggregate) record;
            
            // Save the string into a String Record, creating
            //  one if required
            StringRecord sr = fr.getStringRecord();
            if(sr == null) {
                // Wasn't a string before, need a new one
                sr = new StringRecord();
                fr.setStringRecord(sr);
            }
            
            // Save, loosing the formatting
            sr.setString(value.getString());
            // Update our local cache to the un-formatted version
            stringValue = new HSSFRichTextString(sr.getString());
            
            // All done
            return;
        }

        // If we get here, we're not dealing with a formula,
        //  so handle things as a normal rich text cell
        
        if (cellType != CELL_TYPE_STRING) {
            setCellType(CELL_TYPE_STRING, false, row, col, styleIndex);
        }
        int index = 0;

        UnicodeString str = value.getUnicodeString();
        index = book.getWorkbook().addSSTString(str);
        (( LabelSSTRecord ) record).setSSTIndex(index);
        stringValue = value;
        stringValue.setWorkbookReferences(book.getWorkbook(), (( LabelSSTRecord ) record));
        stringValue.setUnicodeString(book.getWorkbook().getSSTString(index));
    }

    public void setCellFormula(String formula) {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        //Workbook.currentBook=book;
        if (formula==null) {
            setCellType(CELL_TYPE_BLANK,false,row,col,styleIndex);
        } else {
            setCellType(CELL_TYPE_FORMULA,false,row,col,styleIndex);
            FormulaRecordAggregate rec = (FormulaRecordAggregate) record;
            FormulaRecord frec = rec.getFormulaRecord();
            frec.setOptions(( short ) 2);
            frec.setValue(0);
            
            //only set to default if there is no extended format index already set
            if (rec.getXFIndex() == (short)0) rec.setXFIndex(( short ) 0x0f);
            FormulaParser fp = new FormulaParser(formula, book);
            fp.parse();
            Ptg[] ptg  = fp.getRPNPtg();
            int   size = 0;

            // clear the Ptg Stack
            for (int i=0, iSize=frec.getNumberOfExpressionTokens(); i<iSize; i++) {
                frec.popExpressionToken();
            }

            // fill the Ptg Stack with Ptgs of new formula
            for (int k = 0; k < ptg.length; k++) {
                size += ptg[ k ].getSize();
                frec.pushExpressionToken(ptg[ k ]);
            }
            rec.getFormulaRecord().setExpressionLength(( short ) size);
            //Workbook.currentBook = null;
        }
    }

    public String getCellFormula() {
        //Workbook.currentBook=book;
        String retval = FormulaParser.toFormulaString(book, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression());
        //Workbook.currentBook=null;
        return retval;
    }


    /**
     * get the value of the cell as a number.  For strings we throw an exception.
     * For blank cells we return a 0.
     */

    public double getNumericCellValue()
    {
        if (cellType == CELL_TYPE_BLANK)
        {
            return 0;
        }
        if (cellType == CELL_TYPE_STRING)
        {
            throw new NumberFormatException(
                "You cannot get a numeric value from a String based cell");
        }
        if (cellType == CELL_TYPE_BOOLEAN)
        {
            throw new NumberFormatException(
                "You cannot get a numeric value from a boolean cell");
        }
        if (cellType == CELL_TYPE_ERROR)
        {
            throw new NumberFormatException(
                "You cannot get a numeric value from an error cell");
        }
        if(cellType == CELL_TYPE_NUMERIC)
        {
          return ((NumberRecord)record).getValue();
        }
        if(cellType == CELL_TYPE_FORMULA)
        {
          return ((FormulaRecordAggregate)record).getFormulaRecord().getValue();
        }
        throw new NumberFormatException("Unknown Record Type in Cell:"+cellType);
    }

    /**
     * get the value of the cell as a date.  For strings we throw an exception.
     * For blank cells we return a null.
     */
    public Date getDateCellValue()
    {
        if (cellType == CELL_TYPE_BLANK)
        {
            return null;
        }
        if (cellType == CELL_TYPE_STRING)
        {
            throw new NumberFormatException(
                "You cannot get a date value from a String based cell");
        }
        if (cellType == CELL_TYPE_BOOLEAN)
        {
            throw new NumberFormatException(
                "You cannot get a date value from a boolean cell");
        }
        if (cellType == CELL_TYPE_ERROR)
        {
            throw new NumberFormatException(
                "You cannot get a date value from an error cell");
        }
        double value=this.getNumericCellValue();
        if (book.getWorkbook().isUsing1904DateWindowing()) {
            return HSSFDateUtil.getJavaDate(value,true);
        }
        else {
            return HSSFDateUtil.getJavaDate(value,false);
        }
    }

    /**
     * get the value of the cell as a string - for numeric cells we throw an exception.
     * For blank cells we return an empty string.
     * For formulaCells that are not string Formulas, we return empty String
     * @deprecated Use the HSSFRichTextString return
     */

    public String getStringCellValue()
    {
      HSSFRichTextString str = getRichStringCellValue();
      return str.getString();
    }

    /**
     * get the value of the cell as a string - for numeric cells we throw an exception.
     * For blank cells we return an empty string.
     * For formulaCells that are not string Formulas, we return empty String
     */

    public HSSFRichTextString getRichStringCellValue()
    {
        if (cellType == CELL_TYPE_BLANK)
        {
            return new HSSFRichTextString("");
        }
        if (cellType == CELL_TYPE_NUMERIC)
        {
            throw new NumberFormatException(
                "You cannot get a string value from a numeric cell");
        }
        if (cellType == CELL_TYPE_BOOLEAN)
        {
            throw new NumberFormatException(
                "You cannot get a string value from a boolean cell");
        }
        if (cellType == CELL_TYPE_ERROR)
        {
            throw new NumberFormatException(
                "You cannot get a string value from an error cell");
        }
        if (cellType == CELL_TYPE_FORMULA) 
        {
            if (stringValue==null) return new HSSFRichTextString("");
        }
        return stringValue;
    }

    /**
     * set a boolean value for the cell
     *
     * @param value the boolean value to set this cell to.  For formulas we'll set the
     *        precalculated value, for booleans we'll set its value. For other types we
     *        will change the cell to a boolean cell and set its value.
     */

    public void setCellValue(boolean value)
    {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        if ((cellType != CELL_TYPE_BOOLEAN ) && ( cellType != CELL_TYPE_FORMULA))
        {
            setCellType(CELL_TYPE_BOOLEAN, false, row, col, styleIndex);
        }
        (( BoolErrRecord ) record).setValue(value);
    }

    /**
     * set a error value for the cell
     *
     * @param value the error value to set this cell to.  For formulas we'll set the
     *        precalculated value ??? IS THIS RIGHT??? , 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 value)
    {
        int row=record.getRow();
        short col=record.getColumn();
        short styleIndex=record.getXFIndex();
        if (cellType != CELL_TYPE_ERROR) {
            setCellType(CELL_TYPE_ERROR, false, row, col, styleIndex);
        }
        (( BoolErrRecord ) record).setValue(value);
    }
    /**
     * 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()
    {
        if (cellType == CELL_TYPE_BOOLEAN)
        {
            return (( BoolErrRecord ) record).getBooleanValue();
        }
        if (cellType == CELL_TYPE_BLANK)
        {
            return false;
        }
        throw new NumberFormatException(
            "You cannot get a boolean value from a non-boolean cell");
    }

    /**
     * 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()
    {
        if (cellType == CELL_TYPE_ERROR)
        {
            return (( BoolErrRecord ) record).getErrorValue();
        }
        if (cellType == CELL_TYPE_BLANK)
        {
            return ( byte ) 0;
        }
        throw new NumberFormatException(
            "You cannot get an error value from a non-error cell");
    }

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

    /**
     * used for internationalization, currently -1 for unchanged, 0 for compressed unicode or 1 for 16-bit
     *
     * @see #ENCODING_UNCHANGED
     * @see #ENCODING_COMPRESSED_UNICODE
     * @see #ENCODING_UTF_16
     *
     * @return -1, 1 or 0 for unchanged, compressed or uncompressed (used only with String type)
     * 
     * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
     */
    public short getEncoding()
    {
        return encoding;
    }

    /**
     * set the encoding to either 8 or 16 bit. (US/UK use 8-bit, rest of the western world use 16bit)
     *
     * @see #ENCODING_UNCHANGED
     * @see #ENCODING_COMPRESSED_UNICODE
     * @see #ENCODING_UTF_16
     *
     * @param encoding either ENCODING_COMPRESSED_UNICODE (0) or ENCODING_UTF_16 (1)
     * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
     */

    public void setEncoding(short encoding)
    {
        this.encoding = encoding;
    }

    /**
     * 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.setActiveCellRow(row);
        this.sheet.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 "#ERR"+getErrorCellValue();
                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
     *
     * @param comment comment associated with this cell
     */
    public void setCellComment(HSSFComment comment){
        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, record.getRow(), record.getColumn());
        }
        return comment;
    }

    /**
     * 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(); ) {
           Record rec = ( Record ) 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.getRecords().iterator(); it.hasNext(); ) {
            Record rec = ( Record ) 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.findFirstRecordLocBySid( EOFRecord.sid );
        sheet.getRecords().add( eofLoc, link.record );
    }
}