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
– David Grant, Commented Oct 9, 2012 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
– user1669327, Commented Oct 9, 2012 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
– David Grant, Commented Oct 9, 2012 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
– user1669327, Commented Oct 9, 2012 at 13:40
possible duplicate of returning decimal instead of string (POI jar) — Gagravarr
– Gagravarr, Commented Oct 9, 2012 at 14:16

Community · Accepted Answer · 2017-05-23 12:13:51Z

7

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

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Oct 9, 2012 at 14:17

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

makkasi · Accepted Answer · 2017-05-31 05:46:38Z

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, 2017 at 5:46

makkasi

7,3574 gold badges49 silver badges65 bronze badges

Collectives™ on Stack Overflow

POI Appending .0 while reading numeric data from excel

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related