POI追加0.0一边阅读从Excel的数值数据 [英] 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.

从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?

推荐答案

这几乎是相同的其他一些问题在这里，如<一个href=\"http://stackoverflow.com/questions/12526310/returning-decimal-instead-of-string-poi-jar\">returning小数，而不是字符串（POI JAR）

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

答案是一样的<一个href=\"http://stackoverflow.com/questions/12526310/returning-decimal-instead-of-string-poi-jar/12527519#12527519\">one我给这里：

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 。您code会是这样

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追加0.0一边阅读从Excel的数值数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！