Reading Column values from Excel as string return as decimal?

Question

I have a requirement to read Excel file particular column values.

I unable to read the cell values as they are.

My code is

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;


public class ExcelSheetColumn 
{
public static void main(String[] args) throws InvalidFormatException, FileNotFoundException, IOException
{
    Workbook wb = WorkbookFactory.create(new FileInputStream("C:/Users/Pradeep.HALCYONTEKDC/Desktop/My Data/ExcelFiles/XLS1.xls"));
    Sheet sheet = wb.getSheet("Retail - All");
    Row row=null;
    Cell cell ;
    for (int j=0; j< sheet.getLastRowNum()+1; j++)
    {
        row = sheet.getRow(j);

        cell = row.getCell(0);
        if(cell!=null)
        {
            int type = cell.getCellType();
            if (type == HSSFCell.CELL_TYPE_STRING)
              System.out.println(cell.getRichStringCellValue().toString());
            else if (type == HSSFCell.CELL_TYPE_NUMERIC)
                System.out.println(cell.getNumericCellValue());
            else if (type == HSSFCell.CELL_TYPE_BOOLEAN)
                System.out.println( cell.getBooleanCellValue());
            else if (type == HSSFCell.CELL_TYPE_BLANK)
                System.out.println(cell.getColumnIndex() + "] = BLANK CELL");
       }
    }
}
}

Excel column values are

79
999861
999861
https://staging.adpcreditsolutions.com
/index/contract_decision
9200278
2011/01/17
79
5032944200
[email protected]
1979/12/31
0.00
0.00

But the output I am getting is

79.0
999861.0
999861.0
https://staging.adpcreditsolutions.com
/index/contract_decision
9200278.0
17-Jan-2011
79.0
5.0329442E9
[email protected]
31-Dec-1979
0.0
0.0

How to read code my above program to read the excel column values as they are? Please help me!!

if (type == HSSFCell.CELL_TYPE_NUMERIC)
            {
                String stringValue=""+cell.getNumericCellValue();
                String[] splitValue=stringValue.split(".0");
                System.out.println(splitValue[0]);
            }

Suresh Atta · Accepted Answer · 2013-05-10 09:23:13Z

1

 System.out.println(cell.toString());//here is the problem

toString() method returns a string representation of the object. In general, the toString method returns a string that "textually represents" this object.

Use cell.getStringCellValue()

instead

cell.toString()

And propor usage needed.

For numeric values you have to use

getNumericCellValue() and put a condition there

if(cell!=null)
        {
            int type = cell.getCellType();
            if (type == HSSFCell.CELL_TYPE_STRING)
              System.out.println(cell.getRichStringCellValue().toString());
            else if (type == HSSFCell.CELL_TYPE_NUMERIC)

              String[] splits = String.valueOf(cell.getNumericCellValue()).split(".");

           System.out.println(splits[0]);
            else if (type == HSSFCell.CELL_TYPE_BOOLEAN)
                System.out.println( cell.getBooleanCellValue());
            else if (type == HSSFCell.CELL_TYPE_BLANK)
                System.out.println(cell.getColumnIndex() + "] = BLANK CELL");
        }

edited May 10, 2013 at 9:23

answered May 10, 2013 at 7:36

Suresh Atta

122k38 gold badges206 silver badges315 bronze badges

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

2 Comments

Suresh Atta Over a year ago

After splitting also you are getting the same issue?

Suresh Atta Over a year ago

Nope.Please tell what is your requirment.If you don't want to split then print as it is

Amol Patil · Accepted Answer · 2013-08-26 09:15:52Z

0

You can not read String value from numeric field. it throws exception.

answered Aug 26, 2013 at 9:15

Amol Patil

2481 gold badge3 silver badges7 bronze badges

Comments

Ole Pannier · Accepted Answer · 2021-08-05 14:50:20Z

0

This is working for me. try this

 if (currentCell.getCellType().toString().contentEquals("NUMERIC")) {
        
   value = String.valueOf(currentCell.getNumericCellValue());
   value = value.endsWith(".0") ? value.replace(".0", "") : value;
}

edited Aug 5, 2021 at 14:50

Ole Pannier

3,7699 gold badges26 silver badges35 bronze badges

answered Aug 5, 2021 at 10:20

Shankar Patil SP

113 bronze badges

Collectives™ on Stack Overflow

Reading Column values from Excel as string return as decimal?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related