How to read Excel cell having Date with Apache POI?

How to Read Excel Cells with Dates using Apache POI

Are you having trouble reading Excel cells that contain dates using Apache POI? 😩 Don't worry, we've got you covered! In this guide, we'll walk you through common issues and provide easy solutions to help you fix this problem. Let's get started! 🚀

Problem Analysis

You mentioned that when using Apache POI 3.6, you are facing difficulties in reading an Excel file that contains a date like "8/23/1991". The code snippet you provided in your question demonstrates that you are currently retrieving the value as a numeric type, resulting in a value like "33473.0". Clearly, this is not what you're looking for. 😕

Solution

To solve this problem, you need to handle the cell as a date type instead of a numeric type. Apache POI provides a way to handle this using the DateUtil class. Let's modify your code to correctly handle the date values. 💡

switch (cell.getCellType()) {

    // ...

    case HSSFCell.CELL_TYPE_NUMERIC:
        if (DateUtil.isCellDateFormatted(cell)) {
            value = "DATE value=" + cell.getDateCellValue();
        } else {
            value = "NUMERIC value=" + cell.getNumericCellValue();
        }
        break;

    // ...
}

By using the DateUtil.isCellDateFormatted(cell) method, we can determine whether the cell contains a date value. If it does, we can retrieve the date value using the cell.getDateCellValue() method. Otherwise, we fall back to treating it as a numeric value. This way, you will get the desired output for both date and numeric cells. 🎉

Example

Now, let's see this solution in action by applying it to your specific code example. 🏋️‍♂️

dbltemp = row.getCell(c, Row.CREATE_NULL_AS_BLANK).getNumericCellValue();

if (c == 6 || c == 9) {
    if (DateUtil.isCellDateFormatted(cell)) {
        Date date = cell.getDateCellValue();
        SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
        strVal = sdf.format(date);
    } else {
        strVal = String.valueOf(dbltemp);
    }
}

In the modified code, we introduced a new SimpleDateFormat object to format the date as "MM/dd/yyyy". This will ensure the date is presented in the desired format.

Conclusion

Congratulations! 🎉 You have successfully resolved your problem of reading Excel cells containing dates using Apache POI. Now you can confidently handle both date and numeric values in your spreadsheet files. If you have any further questions or face any other challenges, feel free to reach out to us. We're here to help! 💪

Call to Action

If you found this guide helpful, why not share it with your fellow programmers? Sharing is caring, after all! Click the buttons below to share this blog post on your favorite social media platforms. Let's help more developers solve this date reading problem! 👨‍💻🌟