Why format changes while reading data from excel file using poi api java?

Question

I have an excel sheet, While reading the format changes like following

2 comes like 2.0, 1189736455 comes like 1.18973645E8 in Exponential format..

Why this happens, can anybody please help.. Thanks in advance...

here is my sample code Sheet_numUI and Sheet_numDB are the number of sheet form two files... Generally here I read from a file and compare with some value which I stored in a list and the result I store in another excel file... But while getting data from second file that is workBookUI (in my program) I'm not getting expected values...

the following are the files : reader enter image description here and output file is :

   if(Sheet_numUI==Sheet_numDB){
            for(int i=0;i<Sheet_numUI;i++){
                Sheet_nameDB=workBookDB.getSheetAt(i).getSheetName();
                Sheet_nameUI=workBookUI.getSheetAt(i).getSheetName();
                ResultXLS_Reader =  new OMEGA_XLSX_Reader(file.getAbsolutePath());
                ResultXLS_Reader.addSheet1(Sheet_nameDB);
                counter=4;
                DBCol=readerDB.getColumnCount(Sheet_nameDB);
                DBRow=readerDB.getRowCount(Sheet_nameDB);

                UICol=readerUI.getColumnCount(Sheet_nameUI);
                UIRow=readerUI.getRowCount(Sheet_nameUI);
                for(int y=0;y<DBRow;y++){
                    for (int x=0;x<DBCol;x++){
                        if(y==0){
                            val=readerDB.getCellData(Sheet_nameDB, x, y+1);
                            ResultXLS_Reader.setCellDataStringColorLightGreen(Sheet_nameDB, x+1, y+1,val);
                        }else{
                            val=readerDB.getCellData(Sheet_nameDB, x, y+1);
                            ResultXLS_Reader.setCellData(Sheet_nameDB, x+1, y+1,val);
                            list.add(val);

                        }
                        System.out.println("List : "+ list);
                    }
                }
//              data insertion over
                compVal=(String) map.get(Sheet_nameDB);
                for(int k=0;k<=UIRow;k++){
                    for(int l=0;l<UICol;l++){
                        val=readerUI.getCellData(Sheet_nameUI, l, k);
                        if(val.equalsIgnoreCase(compVal)){

                            completeRow=readerUI.getRow(Sheet_nameUI, k-1);
                            for(Cell cell: completeRow){
                                list2.add(cell);
                            }
                            if(list2 != null && list2.size() > 0){
                            for(int m=0;m<list2.size();m++){

                                String val1=list.get(m).toString();
                                String val2=list2.get(m).toString();

                                if(val1.equalsIgnoreCase(val2)){
                                    ResultXLS_Reader.setCellDataColorLightGreen(Sheet_nameDB,m+1,counter, val1);

                                }else{
                                    ResultXLS_Reader.setCellDataColorRed(Sheet_nameDB, m+1,counter, val2);
                                }
                            }
                            counter=counter+1;
                        }
                            list2.clear();
                        }

                    }
                }
                list.clear();
                counter =0;
            }

        }

Please provide more details, i.e. sample of code you use to read the values. Also where does it come misformatted, is that when you're trying to read the values? — Norbert Radyk, Jun 03 '14 at 12:31

score 1 · Accepted Answer · edited May 23 '17 at 12:27

I'm assuming you're using the cell method .getNumericCellValue() which returns a double - a double will always print at least 1 decimal. As for the exponential format, that is just how large doubles print. See this question for how to deal with printing large doubles without scientific notation: How to print double value without scientific notation using Java?

Also, if your goal is printing the results back out into another spreadsheet with POI, you can create a cell style to format the numbers you put into a cell. For example:

DataFormat format = workbook.createDataFormat();
CellStyle number = workbook.createCellStyle();
number.setDataFormat(format.getFormat("#,##0"));

score 0 · Answer 2 · answered Sep 12 '22 at 13:15

Try this function . You need to call this function for the expected value

public static String convertNumberToString(Object obj) {
    if (obj == null)
        return null;
    try {

        return new BigDecimal(obj.toString()).toBigInteger().toString();
    } catch (NumberFormatException nfe) {
        return obj.toString();
    }
}

you should be able to get the proper string

Why format changes while reading data from excel file using poi api java?

2 Answers2