Import DateTime formated cell using Spreadsheetgear

Question

I am using spreadsheetgear to read an existing Excel file and import the data into a SQL Server database. Everything works fine until I need to import a cell that is formatted as a date - "*3/14/2001". I am trying to import this field into a DateTime field in the database. Not sure how to go about doing this?

This is the import routine -

fileInformation.DateReleased = worksheet.Cells["B20"].Text;
fileInformation.DateRequired = worksheet.Cells["B21"].Text;

public DateTime DateReleased { get; set; }
public DateTime DateRequired { get; set; }

What have you come across, any errors? – fujiFX Jul 28 '15 at 23:23 — fujiFX, Jul 28 '15 at 23:23

score 5 · Accepted Answer · edited May 08 '18 at 21:48

Dates/times are stored in SpreadsheetGear (and Excel) as a double representing a date/time serial number. You can read more about these serial dates here if you want. The fact that they show up as a "date" in the cell is simply a function of the IRange.NumberFormat applied to the cell ("m/d/yyyy" etc). Your use of IRange.Text is returning the "formatted" value of the cell--as a string. To get an actual .NET DateTime value from a cell, you can use the IWorkbook.NumberToDateTime(...) helper method. Example:

// Need the IWorkbook for which your "worksheet" object belongs to.
IWorkbook workbook = worksheet.Workbook;

// This code assumes there is *always* a date (double) value in this cell.  You may want 
// to do some additional error checking to ensure B20 actually has a number in it.  
// Otherwise the cast will give you issues.
DateTime dateReleased = workbook.NumberToDateTime((double)worksheet.Cells["B20"].Value);
fileInformation.DateReleased = dateReleased;

DateTime dateRequired = workbook.NumberToDateTime((double)worksheet.Cells["B21"].Value);
fileInformation.DateRequired = dateRequired

Import DateTime formated cell using Spreadsheetgear

1 Answers1