convert excel double cell type to string type using POI

Question

I am reading an excel which has some cells as double type. I want to read and print them as strings.

My sample excel file has following data.

Date|City|Credit|Debit|Balance|Type 10-01-2019|New Delhi|1000000.00||1000000.00|CR 10-01-2019|Mumbai|50000000.00|40000000.00|10000000.00|CR 10-01-2019|Chennai||200000.00|200000.00|DR 10-01-2019|Kolkatta||10000.00|10000.00|DR 10-01-2019|Srinagar|100000000.00|40000000.00|60000000.00|CR

I want to print double amount as strings.

I have tried following code:

public static void main(String[] args) throws  FileNotFoundException{
        FileInputStream CA = new FileInputStream(new File("C:/Java projects/RBI/RBI.xlsx"));

        public static void main(String[] args) throws  FileNotFoundException{
        FileInputStream CA = new FileInputStream(new File("C:/Java projects/RBI/RBI.xlsx"));

    try
    {
        XSSFWorkbook CAworkbook = new XSSFWorkbook(CA);
        XSSFSheet sheet = CAworkbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        ArrayList<String> CA1 = new ArrayList<String>();

        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            CA1.add( row.getCell(1) + "|" +row.getCell(2)+ "|" +row.getCell(3)+ "|" + row.getCell(4)+ "|" +row.getCell(5)+ "|" + row.getCell(6));   
            System.out.println( row.getCell(1) + "|" +row.getCell(2)+ "|" +row.getCell(3)+ "|" + row.getCell(4)+ "|" +row.getCell(5)+ "|" + row.getCell(6)); 
        }

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

}

Output is as under:-

10-01-2019|New Delhi|1000000.0||1000000.0|CR 10-01-2019|Mumbai|5.0E7|4.0E7|1.0E7|CR 10-01-2019|Chennai||200000.0|200000.0|DR 10-01-2019|Kolkatta||10000.0|10000.0|DR 10-01-2019|Srinagar|1.0E8|4.0E7|6.0E7|CR

What's wrong with [following the approaches in the Apache POI documentation](http://poi.apache.org/components/spreadsheet/quick-guide.html#CellContents)? — Gagravarr, Jan 11 '19 at 04:11

score 1 · Answer 1 · answered Jan 10 '19 at 14:22

You have several possibilities of making a String out of your double, but not all of them always keep the regular decimal representation.

I will take the first of your numbers which gets shown in exponential representation as the example number.

public static void main(String[] args) {
    // your number that causes problems
    double a = 100000000.00;
    // just printing it will give you 1.0E8, not desired
    System.out.println(a);
    // using String.valueOf will output 1.0E8, not desired
    System.out.println(String.valueOf(a));
    // same thing if you box it and use toString(): 1.0E8, not desired
    System.out.println(new Double(a).toString());
    /* 
     * first operation that comes nearer to the desired output
     * gives 100000000.000000, better, but not desired
     */
    System.out.println(String.format("%f", a));
    // applying a specific formatting...
    DecimalFormat decimalFormat = new DecimalFormat("#.00#");
    // ... will give you the output you want to have, which is 100000000.00
    System.out.println(decimalFormat.format(a));
}

The conclusion is to apply a desired formatting before you create a String representation of a double. My recommendation is to create a DecimalFormat once (maybe even as a class attribute or constant) and apply it in the iteration over the cells when creating String variables to be used in further operations. By doing so, you don't have to repeat calls to the cell iterator which makes your code easier to read:

    DecimalFormat decimalFormat = new DecimalFormat("#.00#")
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        String date = row.getCell(1);
        String city = row.getCell(2);
        // now apply the DecimalFormat
        String credit = decimalFormat.format(row.getCell(3));
        String debit = decimalFormat.format(row.getCell(4));
        String balance = decimalFormat.format(row.getCell(5));
        String type = row.getCell(6);

        /*
         * also you might want to have a single String concatenation 
         * of the other Strings if you use it more than once
         */
        String line = date + "|" + city + "|" + credit + "|" + debit 
                          + "|" + balance + "|" + type;

        CA1.add(line);   
        System.out.println(line); 
    }

important: DecimalFormat seems to depend on the system default for decimal representation because it outputs 100000000,00 (mind the comma) in my IDE, which is located on a European Windows installation.

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

If the problem is in the format of doubles that you have got, then just use String.format("%.2f", row.getCell(n)) instead of just row.getCell(n) It's not a problem of the POI framework but metter of the Java data formatting. Please follow this or this to get more info about the topic

UPD Actually row.getCell(n) returns a XSSFCell instance so to correctly and explicitly get a value you should use row.getCell(n).getNumericCellValue() method. Or you can just call a row.getCell(n).getStringCellValue() to get a string value directly

score 0 · Answer 3 · answered Feb 22 '21 at 19:17

0

The simple solution would be

BigDecimal.valueOf( cell.getNumericCellValue()).toPlainString();

answered Feb 22 '21 at 19:17

Swapnika

480
4
14

convert excel double cell type to string type using POI

3 Answers3