Extra decimal places in excel reading

Question

I've been writing a little program for my mother, in which I have to display a list of prices based on the work they ask her to do. Since she needs all the decimals in the price I set the Cell Format on Excel to have 7 decimals. The problem is that debugging my program, when I go reading a cell with a price in it, it adds several zeros after the number which are not actually written on the cell. I can't understand what's going on and I'm using OpenOffice saving the file as an Excel 97/2003 file, so as "listino.xls". Here's the code I'm using to get the title of the work and the price:

public Lavorazione[] creaLavorazioni(Lavorazione[] lavorazioni){ //create Work

    char[] stringa = new char[8];
    double prezzo = 0;
    String prezzostringa = "";

    for(int righe=1;righe<workbookR.getSheet(0).getRows();righe++){ //until the rows of
//the excel table end

        try{
            lavorazioni[righe-1].setLavorazione(l.LeggiCella(listino, 0, righe));
            stringa = l.LeggiCella(listino, 2, righe).toCharArray();
            for(int i=0;i<stringa.length;i++){ //getting number and switching ","
//with "."
                if(stringa[i]==',')
                    stringa[i]='.';

                prezzostringa += stringa[i]; //Creating the price string
            }

            prezzostringa=prezzostringa.substring(0, 8); // deleting extra zeros, just
//in case
            prezzo = Double.parseDouble(prezzostringa); //casting to double
            lavorazioni[righe-1].setPrezzo(prezzo); //setting in lavorazioni.prezzo

        }catch(Exception e){
            System.out.println("è successo qualcosa in creaLavorazioni!");
        }
        prezzostringa=""; //resetting for the next cycle
    }

    return lavorazioni;
}

the class Lavorazione is made by me and it's this:

public final class Lavorazione {

private String lavorazione; //name of the work to do
private double prezzo; //price of it

public Lavorazione(String lav, double costo){
    this.setLavorazione(lav);
    this.setPrezzo(costo);
}

public String getLavorazione() {
    return lavorazione;
}

public void setLavorazione(String lavorazione) {
    this.lavorazione = lavorazione;
}

public double getPrezzo() {
    return prezzo;
}

public void setPrezzo(double prezzo) {
    this.prezzo = prezzo;
}

So it just has the name of the work and the relative price. In a few words, when there's "0,05423" in the cell, it writes "0.05423000000000001". Can anyone help me about this? Is using the java.util.Locale a good idea? If you need more info, ask me please.

EDIT: I'm using the jxl api.

*""0,05423" in the cell, it writes "0.05423000000000001"."* Seems it needs a [`NumberFormat`](https://docs.oracle.com/javase/8/docs/api/java/text/NumberFormat.html) for the output. Possibly related [What Every Computer Scientist Should Know About Floating-Point Arithmetic](http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html). — Andrew Thompson, Apr 05 '15 at 07:10
@bot I tried using the BigDecimal but it doesn't solve the problem, it just gets it harder to deal with. — ServantGrunt, Apr 05 '15 at 19:18
@ErwinBolwidt it's useful but it doesn't tell me how I should deal with this, since it only explains why it happens. — ServantGrunt, Apr 05 '15 at 21:09
@AndrewThompson it's really long and I'm gonna read it, thanks for linking. Anyway, a friend suggested to take a look at the java.util.Locale so I'm gonna check. What do you people think about it? — ServantGrunt, Apr 05 '15 at 21:10

ServantGrunt · Accepted Answer · 2015-04-06T08:24:42.117

I found the solution. The problem was in the LeggiExcel class in which I did this:

public String LeggiCella(Sheet foglio, int col, int riga) {

        Cell a = foglio.getCell(col, riga);
        String contenuto = a.getContents();

        if (a.getType() == CellType.NUMBER){ 
            contenuto = Double.toString(LeggiCellaNumerica(a));
            } 

        return contenuto;
    }

    public double LeggiCellaNumerica(Cell a){
        double num = 0;

        NumberCell nc = (NumberCell) a; 
        num = nc.getValue();

        return num;
    }

So it would check if the CellType was a NumberCell and then get the value, returning it as a double. I would then cast it to string in the LeggiCella method and return it as a String. It looks like setting the Cell Format in the Excel file as 7 decimals would make it go mad in the approximation of the number, so it would set 8 or 9 zeros at the end. I now have deleted the NumberCell check and just got the value of the cell returning it as a String, and it works correctly. Thanks everyone who's helped me finding the solution.

Extra decimal places in excel reading

1 Answers1