error when get numeric value

[Andrey Rogov <an...@gmail.com>]

I have a task to read a number from the cell of file Excel.
I analyse the type of cell at first, then read information.
But, there is the following.
There is a number type and number value in a cell.
type HSSFCell.CELL_TYPE_NUMERIC ignored, a string ( HSSFCell.CELL_TYPE_STRING )
type is recognized and I get an error when reading the value.

if ( HSSFCell.CELL_TYPE_NUMERIC == 1 )
{
	fvalue = ( float ) cell.getNumericCellValue() ;
}
else if( HSSFCell.CELL_TYPE_STRING  == 1 )
{
		
   try {
	svalue = getStringCellValue( cell ) ;
	svalue = getStringForDecimal ( svalue ) ;
	fvalue = StringToDecimal( svalue ) ;
   }
   catch ( Exception e )
   {
	fvalue = ( float ) cell.getNumericCellValue() ;								
   }					
}


java.lang.IllegalStateException: Cannot get a text value from a numeric cell
at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:620)
at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:699)
at jobs.com.konus.bean.Configproperty.getStringCellValue(Configproperty.java:554)
at jobs.com.konus.bean.Configproperty.getFloatCellValue(Configproperty.java:497)


Prompt please as correctly to read the numeric data type.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: error when get numeric value

[MSB <ma...@tiscali.co.uk>]

Morning Andrey

Your if statements are coded incorrectly, you are testing the value of a
constant against a numeric literal rather than the cell stype against a
constant.

Try something like this, assuming that the variable cell is an HSSFCell
object that contains a reference to a cell you have read from a worksheet;

int cellType = cell.getCellType();
if ( cellType == HSSFCell.CELL_TYPE_NUMERIC )
{
	fvalue = ( float ) cell.getNumericCellValue() ;
}
else if( cellType == HSSFCell.CELL_TYPE_STRING  )
{
		
   try {
	svalue = getStringCellValue( cell ) ;
	svalue = getStringForDecimal ( svalue ) ;
	fvalue = StringToDecimal( svalue ) ;
   }
   catch ( Exception e )
   {
      fvalue = ( float ) cell.getNumericCellValue();
   }					
}

Also, you could easilly replace the if.....else construct with a switch
statement, like this;

switch(cellType) {
    case HSSFCell.CELL_TYPE_NUMERIC:
        fvalue = ( float ) cell.getNumericCellValue() ;
        break;
    case HSSFCell.CELL_TYPE_STRING:
        ......
}

and so on to catch all of the different cell types.

Not to sure how comfortable you are with Excel but you also need to remember
that there is a difference between the cells contents and the value the user
sees when viewing the worksheet - think of dates for example. If all you
need is a numeric value to perform a caculation then the apearance of the
value may not matter, but if it does, look at the HSSFDataFormatter class.


Andrey Rogov-2 wrote:
> 
> I have a task to read a number from the cell of file Excel.
> I analyse the type of cell at first, then read information.
> But, there is the following.
> There is a number type and number value in a cell.
> type HSSFCell.CELL_TYPE_NUMERIC ignored, a string (
> HSSFCell.CELL_TYPE_STRING )
> type is recognized and I get an error when reading the value.
> 
> if ( HSSFCell.CELL_TYPE_NUMERIC == 1 )
> {
> 	fvalue = ( float ) cell.getNumericCellValue() ;
> }
> else if( HSSFCell.CELL_TYPE_STRING  == 1 )
> {
> 		
>    try {
> 	svalue = getStringCellValue( cell ) ;
> 	svalue = getStringForDecimal ( svalue ) ;
> 	fvalue = StringToDecimal( svalue ) ;
>    }
>    catch ( Exception e )
>    {
> 	fvalue = ( float ) cell.getNumericCellValue() ;								
>    }					
> }
> 
> 
> java.lang.IllegalStateException: Cannot get a text value from a numeric
> cell
> at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:620)
> at
> org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:699)
> at
> jobs.com.konus.bean.Configproperty.getStringCellValue(Configproperty.java:554)
> at
> jobs.com.konus.bean.Configproperty.getFloatCellValue(Configproperty.java:497)
> 
> 
> Prompt please as correctly to read the numeric data type.
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
> 
> 
> 

-- 
View this message in context: http://www.nabble.com/error-when-get-numeric-value-tp23471242p23477822.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org