Quantcast

error when get numeric value

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

error when get numeric value

Andrey Rogov-2
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: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: error when get numeric value

Mark Beardsley
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
Loading...