How to read Excel cell having Date with Apache POI

Question

I m using Apache POI 3 6  I want to read an excel file which has a date like this 8 23 1991    switch  cell getCellType                         case HSSFCell CELL TYPE NUMERIC       value    NUMERIC value     cell getNumericCellValue         break                But it takes the numeric value type and returns the value like this 33473 0   I ve tried to use Numeric Cell Type although with no luck   dbltemp row getCell c  Row CREATE NULL AS BLANK  getNumericCellValue     if  c    6    c    9        strTemp  new String dbltemp toString   trim          long tempDate   Long parseLong strTemp       Date date   new Date tempDate        strVal   date toString        How can I fix my problem

User · Answer

Yes  I understood your problem  If is difficult to identify cell has Numeric or Data value   If you want data in format that shows in Excel  you just need to format cell using DataFormatter class   DataFormatter dataFormatter   new DataFormatter    String cellStringValue   dataFormatter formatCellValue row getCell 0    System out println   Is shows data as show in Excel file    cellStringValue       Here it automcatically format data based on that cell format     No need for extra efforts

User · Answer

You can use CellDateFormatter to fetch the Date in the same format as in excel cell  See the following code   CellValue cv   formulaEv evaluate cell   double dv   cv getNumberValue    if  HSSFDateUtil isCellDateFormatted cell         Date date   HSSFDateUtil getJavaDate dv        String df   cell getCellStyle   getDataFormatString         strValue   new CellDateFormatter df  format date

User · Answer

Try this code   XSSFWorkbook workbook   new XSSFWorkbook new File result        XSSFSheet sheet   workbook getSheetAt 0           Iterate through each rows one by one     Iterator lt Row gt  rowIterator   sheet iterator        while  rowIterator hasNext              Row row   rowIterator next               For each row  iterate through all the columns         Iterator lt Cell gt  cellIterator   row cellIterator             while  cellIterator hasNext                  Cell cell   cellIterator next                switch  cell getCellType                  case Cell CELL TYPE NUMERIC                  if  cell getNumericCellValue      0                          Get date                     Date date   row getCell 0  getDateCellValue                             Get datetime                     cell getDateCellValue                         System out println date getTime                                       break                                  Hope is help

User · Answer

NOTE  HSSFDateUtil is deprecated  If you know which cell i e  column position say 0 in each row is going to be a date  you can go for  row getCell 0  getDateCellValue   directly  http   poi apache org apidocs org apache poi hssf usermodel HSSFCell html getDateCellValue    UPDATE  Here is an example - you can apply this in your switch case code above  I am checking and printing the Numeric as well as Date value  In this case the first column in my sheet has dates  hence I use row getCell 0    You can use the if  HSSFDateUtil isCellDateFormatted    code block directly in your switch case   if  row getCell 0  getCellType      HSSFCell CELL TYPE NUMERIC      System out println   Row No       row getRowNum                   row getCell 0  getNumericCellValue          if  HSSFDateUtil isCellDateFormatted row getCell 0              System out println   Row No       row getRowNum                        row getCell 0  getDateCellValue               The output is   Row No   0 39281 0 Row No   0 Wed Jul 18 00 00 00 IST 2007 Row No   1 39491 0 Row No   1 Wed Feb 13 00 00 00 IST 2008 Row No   2 39311 0 Row No   2 Fri Aug 17 00 00 00 IST 2007

User · Answer

For reading date cells this method has proven to be robust so far   private LocalDate readCellAsDate final Row row  final int pos        if  pos    -1            return null            final Cell cell   row getCell pos - 1       if  cell    null            cell setCellType CellType NUMERIC         else           return null            if  DateUtil isCellDateFormatted cell             try               return cell getDateCellValue   toInstant   atZone ZoneId systemDefault    toLocalDate              catch  final NullPointerException e                logger error e getMessage                 return null                      return null

User · Answer

import java text DateFormat  import java text SimpleDateFormat  import java util Date  import org apache poi ss usermodel Cell  import org apache poi ss usermodel Row  import org apache poi ss usermodel CellType  import org apache poi hssf usermodel HSSFDateUtil    Row row   sheet getRow 0   Cell cell   row getCell 0   if cell getCellTypeEnum      CellType NUMERIC  cell getCellTypeEnum      CellType FORMULA              String cellValue String valueOf cell getNumericCellValue          if HSSFDateUtil isCellDateFormatted cell                     DateFormat df   new SimpleDateFormat  quot MM dd yyyy quot              Date date   cell getDateCellValue              cellValue   df format date                      System out println cellValue

User · Answer

You need the DateUtils  see this article for details   Or  better yet  use Andy Khan s JExcel instead of POI

User · Answer

If you know the cell number  then i would recommend using getDateCellValue   method Here s an example for the same that worked for me -  java util Date date    row getCell   getDateCellValue    System out println date

[java] How to read Excel cell having Date with Apache POI?

Examples related to java

Examples related to apache

Examples related to excel

Examples related to apache-poi