Parsing DateTime from ExcelSheet

Question

I'm looking for a bit of advice.

I'm writing a class to automatically extract information from an excel spreadsheet (using NPOI api) in C#, and then import this information into a database. I've got it all working fine except for one small issue.

One of the spreadsheets I'm working with, contains the date in the following format: 04/01/2011 04:43:28.

When I ran the web application, I got the following error message:

String was not recognised as a valid DateTime

So I debugged through the code after this, and it turns out that the date is being read in as: 40546.0151388889, so it is being formattted back to a number.

I'm unsure how to overcome this issue and I was hoping someone could point me in the right direction?

Here is an exerpt from my code:

using (FileStream fs = File.Open(filename, FileMode.Open, FileAccess.Read))
                    {
                            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);

                            HSSFSheet sheet = templateWorkbook.GetSheetAt(w);
                            HSSFRow row = null;


                            for (int i = 1; i <= sheet.LastRowNum; i++)
                            {
                                FTPSalesDetails t = null;
                                int currentColumn = 0;

                                try
                                {
                                    ModelContainer ctn = new ModelContainer();

                                    row = sheet.GetRow(i);

                                    if (row == null)
                                    {
                                        continue;
                                    }

                                    t = new FTPSalesDetails
                                    {
                                        RowNumber = i,
                                        InvoiceDate = GetCellValue(row.GetCell(0)),
                                        NetUnitsSold = GetCellValue(row.GetCell(2)),
                                        ProductCode = GetCellValue(row.GetCell(5))
                                    };


                                    int Qty = int.Parse(t.NetUnitsSold);
                                    // Do a Loop for net units sold.
                                    for (int x = 0; x < Qty; x++)
                                    {
                                        ItemSale ts = new ItemSale
                                        {
                                            ItemID = GetItemID(t.ProductCode),
                                            RetailerID = GetRetailerID("Samsung"),
                                            DateSold = DateTime.Parse(t.InvoiceDate),
                                        };

                                        ctn.AddToItemSales(ts);
                                        ctn.SaveChanges();
                                    }
                                }

                                ....


                                private string GetCellValue(HSSFCell cell)
                                {
                                    string ret = null;

                                    if (cell == null)
                                    {
                                        return ret;
                                    }

                                    switch (cell.CellType)
                                    {
                                        case HSSFCell.CELL_TYPE_BOOLEAN:
                                            ret = cell.BooleanCellValue.ToString();
                                            break;
                                        case HSSFCell.CELL_TYPE_NUMERIC:
                                            ret = cell.NumericCellValue.ToString();
                                            break;
                                        case HSSFCell.CELL_TYPE_STRING:
                                            ret = cell.StringCellValue;
                                            break;
                                    }
                                    return ret;
                                }

Answer 1

use DateTime.FromOADate(cellValue)

(you might need to do (cellValue - 1) because of a bug in excel date calculation)