Retrieve Integer Values from Excel Using Java

Retrieve INTEGER values from excel using JAVA

Change:

System.out.println(resultRow.getCell(0));

To:

System.out.println(new DataFormatter().formatCellValue(resultRow.getCell(0)));

Explanation:

apache-poi provides for DataFormatter class as utility to leverage the format of the content as it appears on the excel. You can choose custom formats too, a simple example would be (cell is reference to your XSSFCell object):

Excel sheet looks like:

Code:

System.out.println(new DataFormatter().formatCellValue(cell));

The above line would print:

50%
$ 1,200
12/21/14
9886605446

Whereas your normal print would interpret it differently:

0.5
1200.0
21-Dec-2014
9.886605446E9

How do i retrieve integer values from excel using Java

If you store the long integer values then the excel will compress and store them so you will see values like Below :

If you don't want to store the values as integers or if you want to store/retrieve the values as it is and if you don't want to change your code then you can do like below :

Append an ' symbol before the number like above. For example, if the number is 118995099890796 then you do like this '118995099890796. It means, it will treat that as a string and you can read that value using getStringCellValue() method.

I hope it helps...

how to get integer value from Ms Excel to java

Use the function getNumericCellValue(). See the documentation of HSSFCell for more information.

How to extract only the integer value instead of the Double value when reading data from Excel using Java

I recommend the following way, which does not try to cast an HSSFCell to an Integer/int:

// get the cell as object (this is NOT a number, instead, it contains one)
HSSFCell numberCell = sheet.getRow(1).getCell(26);
// get the cell value as double (there is no direct integer version)
double cellValue = numberCell.getNumericCellValue();
// cast the value to an int
int number = (int) cellValue;

How to get Integer value from excel and store in Soap UI properties step

As Axel Richter said, use DataFormatter.formatCellValue to correct the value from the source. Or you can use an abstraction script that works very well with Groovy that handles that for you.

As you said, SoapUI only uses string for property values. If you want to correct it on the receiving/use side, you can use this:

def propVal = '200.0'

assert propVal instanceof java.lang.String

println propVal.toFloat().toInteger()
assert propVal.toFloat().toInteger() instanceof java.lang.Integer

It is generally better to correct the format at source (before storing it in a property).

Using Apache POI how retrieve a numeric cell value as an integer

Answering my own question, as I have got the solution and I hope this might be helpful to others :)

DataFormat format = sheet1.getWorkbook().createDataFormat();
            CellStyle costprice = bulkUploadSpreadSheet.getWorkbook().createCellStyle();
            costprice.setDataFormat(format.getFormat("#"));

Now you can apply the costprice cell style to the desired cell. Please do let me know if you any other solution for this. All the best.

Read integer from numeric cell using Apache POI in java

You can read int value as string apache poi using simple steps

First count rows in sheets using below method

private  int getRowCount(Sheet currentSheet) {
        int rowCount = 0;
        Iterator<Row> rowIterator = currentSheet.iterator();

        while(rowIterator.hasNext()) {  
            Row row = rowIterator.next();

            if(row == null || row.getCell(0) == null || row.getCell(0).getStringCellValue().trim().equals("") || row.getCell(0).toString().trim().equals("") 
                    || row.getCell(0).getCellType()==Cell.CELL_TYPE_BLANK){
                break;
            }
            else
                rowCount=rowCount + 1;
        }       
        return rowCount;
    }

Then use below code in your method

    Workbook workbook = WorkbookFactory.create(new File("c:/test.xls");
    Sheet marksSheet = (Sheet) workbook.getSheet("Sheet1");
            int zoneLastCount = 0;
            if(marksSheet !=null ) {
                zoneLastCount = getRowCount(marksSheet);
            }
    int zone = zoneLastCount-1;
    int column=1

    for(int i = 0; i < zone; i++) {         
        Row currentrow = marksSheet.getRow(i);
        double year = Double.parseDouble(currentrow.getCell(columnno).toString());
        int year1 = (int)year;
        String str = String.valueOf(year1);
    }

Apache POI - get number as integer

Excel stores almost all numbers in the file format as floating point values, which is why POI will give you back a double for a numeric cell as that's what was really there

I believe, though it's not quite clear from your question, that what you want to do is get a String object in Java that contains the number as it would look in Excel? i.e. apply the formatting rules applied to the cell to the raw number, and give you back the formatted string?

If so, you want to do exactly the same thing as in my answer here. To quote:

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

Edit And for those of you who apparently find clicking through to the JavaDocs to be just that little bit too much work..., you need to use the DataFormatter.formatCellValue(Cell) method. If iterating, you'd do something along the lines of:

Workbook workbook = WorkbookFactory.create(new File("input.xlsx"));
DataFormatter formatter = new DataFormatter();
Sheet s = workbook.getSheetAt(0);
for (Row r : s) {
  for (Cell c : r) {
     System.out.println(formatter.formatCellValue(c));
  }
}

---

Related Topics