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?

Answer 1

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) );
    }
 }

Answer 2

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.