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?

Answer 1

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

Answer 2

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 

Answer 3

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("MM/dd/yyyy");
          Date date = cell.getDateCellValue();
          cellValue = df.format(date);
       }
          System.out.println(cellValue);
    }

Answer 4

Apache Poi has a DateUtil.isCellDateFormatted(XSSFCell) it works great.

Object objData = switch (cell.getCellType()){
   case NUMERIC ->{
      if(DateUtil.isCellDateFormatted(cell)){
         yield cell.getDateCellValue();
      }else{
         yield cell.getNumericCellValue();
      }
  } //The rest of the cellTypes need to be implemented.
}

objData can now be tested for Date or Double.

Answer 5

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

Answer 6

You need the DateUtils: see this article for details.

Or, better yet, use Andy Khan's JExcel instead of POI.

Answer 7

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

Answer 8

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

Answer 9

Try this code.

XSSFWorkbook workbook = new XSSFWorkbook(new File(result));
    XSSFSheet sheet = workbook.getSheetAt(0);

    // Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        // For each row, iterate through all the columns
        Iterator<Cell> 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.

Answer 10

 public void readExcel() {
        try {
            String path = String.valueOf(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS));
            File file = new File(path + "/myexcelsheet.xlsx");
            FileInputStream fileInputStream = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fileInputStream);
            XSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rowIter = sheet.iterator();
            int rowno = 1;
            if (rowno !=0){
                while (rowIter.hasNext()) {
                    Row row = rowIter.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    textView4.append("\n");
                    for (int i = 0; i < row.getRowNum(); i++) {
                        while (cellIterator.hasNext()) {
                            Cell cell = cellIterator.next();
                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_STRING:    //field that represents string cell type
                                    textView4.append(cell.getStringCellValue() + "" + "\t\t" + "");

                                break;
                                case Cell.CELL_TYPE_NUMERIC:    //field that represents number cell type
                                    if (DateUtil.isCellDateFormatted(cell)){
                                        DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                                        Date date = cell.getDateCellValue();
                                        textView4.append(dateFormat.format(date) + "" + "\t\t" + "");
                                    }else{
                                        textView4.append(cell.getNumericCellValue() + "" + "\t\t" + "");
                                    }
                                break;
                            }
                            textView4.append("");
                        }
                        i++;
                    }
                }
            }
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }