Reading date columns from Excel

Question

I am trying to read date cell from Excel file:

private string GetDateValueFromRowOrNull(ISheet worksheet, int row, int column, StringBuilder exceptionMessage, CellType? cellType = null)
{
    var cell = worksheet.GetRow(row).GetCell(column);
    if (cell == null)
    {
        return string.Empty;
    }

    if (cellType != null)
    {
        cell.SetCellType(cellType.Value);
    }

    var cellValue = worksheet.GetRow(row).GetCell(column).DateCellValue;

    if (cellValue != null)
    {
        return cellValue;
    }

    return String.Empty;
}

But I am getting an error while trying to return the cellValue:

Can not implicitly convert type System.DateTime to string

if (cellValue != null)
{
    return cellValue;
}

I am using NPOI for the Excel functions.

When you return CellValue, this has a DateTime format. You need to write << return CellValue.ToString(); >> in order to provide a string as return format of your method. — Fredy, Mar 07 '22 at 13:51

score 4 · Answer 1 · edited Mar 07 '22 at 15:51

4

You can read the datetime value as string from excel then convert it to a DateTime object.

Sample converter code:

public static DateTime GetDateTime(string day, string month, string year)
{
    try
    {
        return Convert.ToDateTime(string.Format("{0} {1}, {2}", (object)day, (object)month, (object)year));
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

edited Mar 07 '22 at 15:51

AlexB

7,302
12
56
74

answered Mar 07 '22 at 14:14

Tafsir Ahamed

930
4
15

The value is already a DateTime (`worksheet.GetRow(row).GetCell(column).DateCellValue;`). There is no need to do this. The issue is that OP needs to decide if a string is needed or a DateTime is needed. – Crowcoder Mar 07 '22 at 15:59
I removed the following code: `return String.Empty;` – IpadNF Mar 07 '22 at 16:03

score 0 · Answer 2 · edited Mar 07 '22 at 18:47

0

I have sorted it. @Fredy’s comment was right.

var cellValue = worksheet.GetRow(row).GetCell(column).DateCellValue;

    if (cellValue != null)
    {
        return cellValue.ToString();
    }

edited Mar 07 '22 at 18:47

Jeremy Caney

7,102
69
48
77

answered Mar 07 '22 at 16:11

IpadNF

1
2

score 0 · Answer 3 · answered Mar 07 '22 at 16:13

If you require a DateTime to be returned from your method you need to change the method signature to return DateTime instead of string. Given the possibility of a null value, I also suggest you actually return a nullable date and make sure callers of this method handle nulls the same way you were expecting them to handle empty string:

private DateTime? GetDateValueFromRowOrNull(ISheet worksheet, int row, int column, StringBuilder exceptionMessage, CellType? cellType = null)
{
    var cell = worksheet.GetRow(row).GetCell(column);
    if (cell == null)
    {
        return null;
    }

    if (cellType != null)
    {
        cell.SetCellType(cellType.Value);
    }

    var cellValue = worksheet.GetRow(row).GetCell(column).DateCellValue;

    if (cellValue != null)
    {
        return cellValue;
    }

    return null;
}

Reading date columns from Excel

3 Answers3