Bug 50697 - Problem with reading time field from excel
Summary: Problem with reading time field from excel
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: POI Overall (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords: APIBug
Depends on:
Blocks:
 
Reported: 2011-02-01 02:53 UTC by trainees
Modified: 2011-02-01 10:29 UTC (History)
1 user (show)



Attachments
sample data (276.00 KB, application/octet-stream)
2011-02-01 05:18 UTC, trainees
Details
Excel file which i am trying to read (276.00 KB, application/octet-stream)
2011-02-01 05:20 UTC, trainees
Details

Note You need to log in before you can comment on or make changes to this bug.
Description trainees 2011-02-01 02:53:45 UTC
when iam trying to read time data from xls file using poi excel reader i get the value something like "31-dec-1899".This is the problem what iam facing.Expecting reply soon.Thanks in advance.

From

Renjith.R
Comment 1 Nick Burch 2011-02-01 04:46:20 UTC
Times and dates are stored the same, which is an offset from either 1900 or 1904. Either use time specific methods, or just fetch the number which is a floating point number of days
Comment 2 trainees 2011-02-01 05:18:26 UTC
Created attachment 26586 [details]
sample data

There is field named as Time .The datas of that field we are trying to read
Comment 3 trainees 2011-02-01 05:20:33 UTC
Created attachment 26587 [details]
Excel file which i am trying to read

please check the column titled 'time' in the attached file.
Comment 4 trainees 2011-02-01 05:26:41 UTC
This is the function i am using. When it reads the 'time' field in the attached excel, the data obtained is '31-Dec-1899' instead of actual data. How can we get the actual data?


public void displayFromExcel(String xlsPath, String sheetName) {
        System.out.println("In POIExcelReader :: method ===========displayFromExcel");
        InputStream inputStream = null;

        try {
            inputStream = new FileInputStream(xlsPath);
        } catch (FileNotFoundException e) {
            System.out.println("File not found in the specified path.");
            e.printStackTrace();
        }

        POIFSFileSystem fileSystem = null;

        try {
            fileSystem = new POIFSFileSystem(inputStream);

             this.workBook = new HSSFWorkbook(fileSystem);
             this. sheet = workBook.getSheetAt(0);
            if (!sheetName.equals("")) {
                sheet = workBook.getSheet(sheetName);
            }
            if(sheet != null){
           Iterator<Row> rows = sheet.rowIterator();
//Iterator<HSSFRow> rows     = sheet.rowIterator ();
            this.excelData = new ArrayList<ArrayList>();
            this.excelRows = sheet.getPhysicalNumberOfRows();
                  
            ///

//            System.out.println("the total number of rows are" + rows);

            int cols = 0; // No of columns
            int tmp = 0;

            // This trick ensures that we get the data properly even if it doesn't start from first few rows
            for (int i = 0; i < 10 || i < this.excelRows; i++) {
                Row erow = sheet.getRow(i);
            //  System.out.println("get cell "+erow.getCell(0));
                if (erow != null) {
                    tmp = sheet.getRow(i).getPhysicalNumberOfCells();
                    //out.println("tmp value"+tmp);
                    if (tmp > cols) {
                        cols = tmp;
                    }

                }
            }
            this.excelCols = cols;
            this.excelColDataType = new ArrayList();
            this.excelColDataSize = new ArrayList();

            for (int c = 0; c < cols; c++) {
                this.excelColDataType.add("Varchar");
                this.excelColDataSize.add("0");
            }
            CommonFunctions commonFunctions = new CommonFunctions();
            int testrow=0;
            while (rows.hasNext()) {
//HSSFRow row = rows.next ();
                Row row = rows.next();
                ArrayList rowData = new ArrayList(cols);
             
// display row number in the console.
//                System.out.println("Row No.: " + row.getRowNum());

// once get a row its time to iterate through cells.
//Iterator<HSSFCell> cells = row.cellIterator ();
                Iterator<Cell> cells = row.cellIterator();
                int c = 0;
              
                while (cells.hasNext()) {
//HSSFCell cell = cells.next ();
                    Cell cell = cells.next();

                  // System.out.println("Cell No.: " + cell.getColumnIndex());//.getCellNum ());

                    if (cell != null && cell.toString() !="")
                    {
                       if(cell.getCellType() ==Cell.CELL_TYPE_NUMERIC )//Lilu added on 2010 Dec 9
                       {
                        rowData.add(String.valueOf((long)cell.getNumericCellValue()));
                        }
                       else
                       {
                           rowData.add(cell.toString());
                        }
//                   
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC: {
                                break;
                            }

                            case Cell.CELL_TYPE_STRING: {
                             
                                RichTextString richTextString = cell.getRichStringCellValue();
//                                System.out.println("String value: " + richTextString.getString());

                                break;
                            }

                            default: {

                                // types other than String and Numeric.
                                System.out.println("Type not supported.");

                                break;
                            }
                        }
                    } else {
                        rowData.add("");
                    }
                    c++;
                }
               // System.out.println("rowData "+rowData);
                this.excelData.add(rowData);
              
            }
            }
//            System.out.println("Excel Data Size read: " + this.excelData.size() + "~~~~" + this.excelData);
//            System.out.println("Excel Data Type size: " + this.excelColDataType.size());
//            System.out.println("Excel Data Type read: " + this.excelColDataType);
//            System.out.println("Excel Data Length read: " + this.excelColDataSize);
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("Error in POIExcelReader :: method ==============displayFromExcel=====>"+e.getMessage()+e.getCause());
        }
    }
Comment 5 Nick Burch 2011-02-01 06:37:43 UTC
Just read the "time" cell as a number - it's fractions of a day so 12 hours is stored as 0.5

Or, read it as a date, and just look at the time part
Comment 6 David Fisher 2011-02-01 10:29:57 UTC
Additionally Bugzilla is NOT the place to have a discussion about using the API. The user list is the place.