POI Appending .0 while reading numeric data from excel

Question

I am using POI HSSF to read excel data and I am using JUnit to check the data against database proc RefCursor.

The Junit test fails as the numeric data from the Refcursor for example 100 are compared against the data in the excel sheet 100 but it fails as the POI reads it as 100.0.

        InputStream fileInputStream = Testdb.class.getClassLoader().getResourceAsStream(fileName);
        //retrieve number of columns and rows
        int numRows=0, numCols=0, i, j, minColIndex=0, maxColIndex=0;
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet hssfSheet = workBook.getSheetAt(0);
        Iterator rowIterator = hssfSheet.rowIterator();
        while (rowIterator.hasNext())
        {
            numRows++;
            HSSFRow hssfRow = (HSSFRow) rowIterator.next();
            Iterator iterator = hssfRow.cellIterator();
            List cellTempList = new ArrayList();
            if (numRows == 1)
            {
                minColIndex = hssfRow.getFirstCellNum();
                maxColIndex = hssfRow.getLastCellNum();
                numCols = maxColIndex;
            }
            for(int colIndex = minColIndex; colIndex < maxColIndex; colIndex++)
            {
                HSSFCell hssfCell = hssfRow.getCell(colIndex);
                cellTempList.add(hssfCell);

            }
            cellDataList.add(cellTempList);
        }


        String expected[][] = new String[numRows][numCols];
        String[] tableColumns = new String[numCols];
        System.out.println("Rows : " + numRows + "Columns : " + numCols);
        System.out.println("Min Col Index : " +minColIndex + "Max Col Index : " + maxColIndex);
        for (i=0; i<numRows; i++)
        {
            List cellTempList = (List) cellDataList.get(i);
            for (j=0; j < numCols; j++)
            {
                HSSFCell hssfCell = (HSSFCell) cellTempList.get(j);
                if (i == 0)
                {
                    tableColumns[j] = hssfCell.toString();
                    System.out.print(tableColumns[j] + "\t");
                }
                else
                {
                    if(hssfCell != null)
                    {
                        expected[i-1][j] = hssfCell.toString();
                    }
                    else
                    {
                        expected[i-1][j] = null;
                    }
                    System.out.print(expected[i-1][j] + "\t");
                }
            }
            System.out.println();
        }

This is a generic framework program which I am building so the framework should be intelligent enough to disregard the ".0". Any inputs on how to resolve this?

Don't treat everything as a String. Use the most appropriate type, e.g. BigDecimal, Integer. — David Grant, Oct 09 '12 at 13:19
The excel data wouldn't be specific to a table, it may contain data for any refcursor, so cannot pre-determine the contents of the column and the data type. When I was browsing the net, I found that DataFormatter can be used to fix the issue, but not sure what needs to be done here. — user1669327, Oct 09 '12 at 13:27
A system smart enough to know that a String with a trailing ".0" should be ignored doesn't sound smart, it sounds broken :) — David Grant, Oct 09 '12 at 13:33
I am quite new to Junit and POI, I can make the program smart enough but that would involve incorporating logic which would make the program complex, I don't want to re-invent the wheel in case if there is already an existing functionality in the POI which does it for us. — user1669327, Oct 09 '12 at 13:40
possible duplicate of [returning decimal instead of string (POI jar)](http://stackoverflow.com/questions/12526310/returning-decimal-instead-of-string-poi-jar) — Gagravarr, Oct 09 '12 at 14:16

score 7 · Accepted Answer · edited May 23 '17 at 12:13

This is virtually identical to a number of other questions here, such as returning decimal instead of string (POI jar)

The answer is the same as the one I gave here:

POI is giving you the exact value that Excel has stored in the File. Generally, if you write a number in an Excel cell, Excel will store that as a number with formatting. POI provides support to do that formatting for you if you want it (most people don't - they want the numbers as numbers so they can use them)

The class you're looking for is DataFormatter. Your code would be something like

 DataFormatter fmt = new DataFormatter();
 for (Row r : sheet) {
    for (Cell c : r) {
       CellReference cr = new CellRefence(c);
       System.out.println("Cell " + cr.formatAsString() + " is " + 
                          fmt.formatCellValue(c) );
    }
 }

score 1 · Answer 2 · answered May 31 '17 at 05:46

1

Hi my solution was just to put the symbol:

in front of every number. Then the number is processed as text.

After you do that you would see little green triangle and warning:

For me this is not a problem, because it works.

answered May 31 '17 at 05:46

makkasi

6,328
4
45
60

POI Appending .0 while reading numeric data from excel

2 Answers2

Linked

Related