How to format the values which comes from excel using java

Question

I have a excel file with some values like :

**Status Code** **Method Name**
400                  createRequest
401                  testRequest
402                  mdm
403                  fileUpload

and the following code to read and print the data[Late on i will put them in HashMap]

package com.poc.excelfun;

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

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ReadExcelData {
    public static void main(String[] args) {
        try {

            FileInputStream file = new FileInputStream(new File("attachment_status.xls"));

            //Get the workbook instance for XLS file 
            HSSFWorkbook workbook = new HSSFWorkbook(file);

            //Get first sheet from the workbook
            HSSFSheet sheet = workbook.getSheetAt(0);

            //Iterate through each rows from first sheet
            Iterator<Row> rowIterator = sheet.iterator();
            while(rowIterator.hasNext()) {
                Row row = rowIterator.next();

                //For each row, iterate through each columns
                Iterator<Cell> cellIterator = row.cellIterator();
                while(cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch(cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(cell.getBooleanCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "\t\t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
            /*
             * The following code to create a new work book with the value fetched from the given work book
             * FileOutputStream out = 
                new FileOutputStream(new File("attachment_status_new.xls"));
            workbook.write(out);
            out.close();*/

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

but the above code returns the following :

Status Code     Method Name     
400.0       createRequest       
401.0       testRequest     
402.0       mdm     
403.0       fileUpload

If wee the out put the status code comes with .0 but i want to get only 400 not with .0

How to do this.

I have used poi for excel manipulations.

Best Regards Anto

You could use subString or regex to format the output. Or try to cast the Numeric to an Integer — Kuchi, May 09 '13 at 08:37

score 1 · Accepted Answer · answered May 09 '13 at 08:35

1

cell.getNumericCellValue() returns double vaule cast it into int the decimal point will be ignored.

answered May 09 '13 at 08:35

seenukarthi

8,241
10
47
68

Thanks for that +1, will mark this as answer after some time since it does not allow me to accept. – Java Questions May 09 '13 at 08:41
I have a question again, how to put them in key pair value like `Method name and corresponding status code for the method name`? – Java Questions May 09 '13 at 08:43
@Anto: Your last comment has nothing to do with the original question. Other people can use this site better if different problems are not mixed into one question. See the FAQ for this. So please formulate another question. – A.H. May 09 '13 at 08:45
Hey Karthikeyan, Just would like to talk with you, I am also from Tamilnadu, Would you please share your email address? – Java Questions May 09 '13 at 08:57
It's not a good idea to post email id here since it is open to every one. – seenukarthi May 09 '13 at 09:42

score 1 · Answer 2 · edited May 23 '17 at 10:32

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.

While you could just cast that to an int, that'd just be specific to the one case, and not general. What I think you probably want (though it's not quite clear from your question) is to get a String in Java that contains the number as it would look in Excel? So your cell containing 400.0 and a format rule of integer would come back as the string "400".

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.

How to format the values which comes from excel using java

2 Answers2