How do i retrieve integer values from excel using Java

Question

My Excel file has Telephone numbers and Integer like 0772354573 and 56674 but my output is like 07723545E9 and 56674.0, Instead i need my output to be like as defined in the excel as 0772354573 and 56674.

Below is the Getcell data class im using which i downloaded from an external source.

public String getCellData(String sheetName,String colName,int rowNum){
    try{
        if(rowNum <=0)
            return "";

        int index = workbook.getSheetIndex(sheetName);
        int col_Num=-1;
        if(index==-1)
            return "";

        sheet = workbook.getSheetAt(index);
        row=sheet.getRow(0);
        for(int i=0;i<row.getLastCellNum();i++){
            //System.out.println(row.getCell(i).getStringCellValue().trim());
            if(row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
                col_Num=i;
        }
        if(col_Num==-1)
            return "";

        sheet = workbook.getSheetAt(index);
        row = sheet.getRow(rowNum-1);
        if(row==null)
            return "";
        cell = row.getCell(col_Num);

        if(cell==null)
            return "";
        //System.out.println(cell.getCellType());
        if(cell.getCellType()==CellType.STRING)
            return cell.getStringCellValue();
        else if(cell.getCellType()==CellType.NUMERIC || cell.getCellType()==CellType.FORMULA ){

            String cellText  = String.valueOf(cell.getNumericCellValue());
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                // format in form of M/D/YY
                double d = cell.getNumericCellValue();

                Calendar cal =Calendar.getInstance();
                cal.setTime(HSSFDateUtil.getJavaDate(d));
                cellText =
                        (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
                cellText = cal.get(Calendar.DAY_OF_MONTH) + "/" +
                        cal.get(Calendar.MONTH)+1 + "/" +
                        cellText;

                //System.out.println(cellText);

            }



            return cellText;
        }else if(cell.getCellType()==CellType.BLANK)
            return "";
        else
            return String.valueOf(cell.getBooleanCellValue());

    }
    catch(Exception e){

        e.printStackTrace();
        return "row "+rowNum+" or column "+colName +" does not exist in xls";
    }
}

Ali · Accepted Answer · 2019-02-15 08:44:30Z

3

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...

answered Feb 15, 2019 at 8:44

Ali

1,6891 gold badge7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dark Knight · Accepted Answer · 2019-02-15 07:01:38Z

0

Tough not ideal, but i would suggest to convert Number cell to String cell before reading cell value. And then parse value into int value;

if(cell.getCellType()==CellType.NUMERIC) {
 cell.setCellType(Cell.CELL_TYPE_STRING);
 String cellValue=cell.getStringCellValue();
 int numericValue=Integer.parseInt(cellValue);
}

answered Feb 15, 2019 at 7:01

Dark Knight

8,3574 gold badges41 silver badges58 bronze badges

1 Comment

Ijaazops Over a year ago

i havent tried this. since the above method is working

Collectives™ on Stack Overflow

How do i retrieve integer values from excel using Java

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related