NPOI - How to distinguish datetime-formatted numeric Excel-cells (c#)

Question

Background

I have an Excel-file (xlxs) with a number of datetime and decimals that I want to convert into a two-dimensional string-array. The strings in the array should look exactly as the user has entered them into the excel. To do this I'm using NPOI version 2.2.1 with C#.

Note that column A in the example is formatted as a date in Excel!

COLUMN A     COLUMN B
2016-07-20   -46,95
2016-07-20   283,59
2016-07-20   -46,95
2016-07-20   52194,64

I have a generic method ConvertCellToString() that converts a cell into it's proper string representation:

    private string GetCellAsString(ICell cell)
    {
        switch (cell.CellType)
        {
            case CellType.Boolean:
                return cell.BooleanCellValue.ToString();
            case CellType.Error:
                return cell.ErrorCellValue.ToString();
            case CellType.Formula:
                return cell.CellFormula.ToString();
            //ALL CELLS IN THE EXEMPEL ARE NUMERIC AND GOES HERE
            case CellType.Numeric:
                return cell.NumericCellValue.ToString();
            case CellType.String:
                return cell.StringCellValue.ToString();
            case CellType.Blank:
            case CellType.Unknown:
            default:
                return "";
        }
    }

However, since even the datetime cells are numeric cells, this solution results in all the dates below being incorrectly represented as numbers rather than dates ("21672" instead of "2016-07-20" etc)

The method below needs to distinguish numeric cells with datetimes vs those with numbers. Is there any way of doing this with NPOI? I'd rather not resort to parsing the string, especially since NPIOs cell.ToString() returns the horrible "2016-jan-20" format if it's a date.

I'm pretty much stuck here so help would be greatly appreciated! There must be some obvious best practice for this that I just can't find!

Should be noted that I don't have any control over how the Excel is formatted. They are being sent to us from various customers, some of which use different formatting, and the above method is from a generic library we use to stringify all our excels. — user1531921, Aug 10 '16 at 11:44
[DateUtils](https://npoi.codeplex.com/SourceControl/latest#NPOI/SS/UserModel/DateUtil.cs) has a `public static bool IsCellDateFormatted(Cell cell)` which returns true if the cell is date formatted. I've never used NPOI but this is also the approach doing this task using apache poi. — Axel Richter, Aug 10 '16 at 12:07
It seems to have done the trick! If you post an answer, I can mark it the correct one! — user1531921, Aug 10 '16 at 13:36
I can't post C# code since I've never wrote some C#.;-). But you can answer your own question with the exact code which solve this problem. It is a common problem. So this will help much. — Axel Richter, Aug 10 '16 at 13:56

score 21 · Accepted Answer · answered Aug 23 '16 at 10:42

21

Turns out the DateUtil.IsCellDateFormatted() can be used as such:

            case CellType.Numeric:
                {
                    return DateUtil.IsCellDateFormatted(cell) 
                        ? cell.DateCellValue.ToString() 
                        : cell.NumericCellValue.ToString();
                }

answered Aug 23 '16 at 10:42

user1531921

1,372
4
18
36

1

We had some regression after updating NPOI library. This solution worked and seem more agnostic about Date formatting. Thanks for the tips! – Guillaume ZAHRA Nov 03 '16 at 09:28

NPOI - How to distinguish datetime-formatted numeric Excel-cells (c#)

1 Answers1