POI 从 excel 读取数字数据时附加 .0 [英] POI Appending .0 while reading numeric data from excel

问题描述

我使用 POI HSSF 读取 excel 数据，我使用 JUnit 根据数据库 proc RefCursor 检查数据.

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

Junit 测试失败，因为将来自 Refcursor 的数字数据(例如 100)与 Excel 表 100 中的数据进行比较，但它失败了，因为 POI 将其读取为 100.0.

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

这是我正在构建的通用框架程序，因此该框架应该足够智能以忽略.0".有关如何解决此问题的任何意见?

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?

推荐答案

这实际上与此处的许多其他问题相同，例如 返回十进制而不是字符串(POI jar)

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

答案与 我在这里给出的答案相同:

POI 为您提供 Excel 存储在文件中的确切值.通常，如果您在 Excel 单元格中写入数字，Excel 会将其存储为带有格式的数字.如果您愿意，POI 支持为您进行格式化(大多数人不这样做 - 他们希望数字作为数字以便他们可以使用它们)

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)

您要查找的类是 DataFormatter.您的代码将类似于

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

这篇关于POI 从 excel 读取数字数据时附加 .0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！