0

i have an excel to upload which has a column material id.That column contains alphanumeric values,But when i am trying to upload this file value the uploaded column in the jsp page is taking an unexpected E and also taking decimal of that number i am using apache POI to parse the excel.Here i have done so far

while(rowIterator.hasNext()) {
                boolean isValidentry=true;
                System.out.println("Row number =" + (i+1));
                Row row = rowIterator.next();
                CategoryObject categoryObject = new CategoryObject();
                categoryObject.setCode("");
                if(row.getCell(0)!=null && !"".equals(row.getCell(0).toString().trim())){       //Part Number
                    System.out.println("Get Cell 0 >>>>" +row.getCell(0).toString());
                    categoryObject.setCode(row.getCell(0).toString().trim());

enter image description here

The problem is that I'm getting 3.0E9 where I need 3000000000.

categoryObject.setCode is taken as String field.This column is responsible for the material id.

lucifer
  • 2,297
  • 18
  • 58
  • 100
  • You do not even say how you upload ! And is the problem in parsing the file, or in displaying in JSP ? – Serge Ballesta Aug 20 '14 at 05:44
  • i have problem in getting the value which i upload to the jsp – lucifer Aug 20 '14 at 05:55
  • can i prevent this ?? please help – lucifer Aug 20 '14 at 06:14
  • You must first write a good question with relevant infos. Read (or carefully read again [How do I ask a good question?](http://stackoverflow.com/help/how-to-ask) and update your question with elements on how you upload the file and how you access to it. I really cannot understand what happens with so few informations. – Serge Ballesta Aug 20 '14 at 06:22
  • i am sorry for my unclear question.actauly the problem is that when i am trying to upload a value form the cell for ex 3000000000 the parsing is done as 3.0E9 for it.This is the problem..... – lucifer Aug 20 '14 at 06:25
  • Ok, so I understand the problem is with the parsing done with Apache POI. Unfortunately I do not use it, and won't be able to help you :-( But I've proposed you an edit to your post. Feel free to edit it again if what I wrote is not exactly what you would have said. – Serge Ballesta Aug 20 '14 at 06:45
  • use cell.getRawValue() for numeric type to get string : https://stackoverflow.com/questions/13563747/p/58442764 – Criss Oct 18 '19 at 01:46

2 Answers2

2

Numbers in Excel are (except for a few edge cases) stored as floating point numbers. Large floating point numbers in Java, when formatted as a string, are printed in Scientific notation

Assuming what you really wanted was "give me a string that looks like what Excel shows for this cell", then do not call cell.toString(). This will not give you what you want in most cases

Instead, you need to use the DataFormatter class, which provides methods which read the Excel format rules applied to a cell, then re-creates (as best it can) those in Java

Your code should be:

DataFormatter fmt = new DataFormatter();
for (Row row : sheet) {
     int rowNumber = row.getRowNum() + 1;
     System.out.println("Row number =" + rowNumber);
     CategoryObject categoryObject = new CategoryObject();
     categoryObject.setCode("");

     Cell cell = row.getcell(0, Row.RETURN_BLANK_AS_NULL);
     if(cell!=null) {
          String cellValue = fmt.formatCellValue(cell);
          if (! cellValue.trim().isEmpty()) {
             System.out.println("Get Cell 0 >>>>" + cellValue);
             categoryObject.setCode(cellValue);
          }
     }
 }

You may notice I've also fixed a bunch of other stuff as well....!

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
1

use BigDecimal to prevent E notation from number

BigDecimal bDec = new BigDecimal("3.0E9");
System.out.println(bDec.toPlainString());

output:

3000000000

running example

Abhishek Nayak
  • 3,732
  • 3
  • 33
  • 64
  • Or don't do silly double to string conversions in the first place... (The OP is basically calling toString on a double, hence getting scientific notation to start with) – Gagravarr Aug 20 '14 at 12:54