Tough Date conversion problem.. importing from excel

jereviscious

Hi all - Last resort time.

I'm importing data from a spreadsheet that I receive from one of my vendor
using interop.excel.

The date field in excel is entered as 4/7/2006, but when I retrieve the
value, it returns a double 38449.0.

I can't figure out how to convert this value into a proper date. I have no
Idea what excel is doing with this date. Also, when I enter in 38449 into
that SAME column in excel it DOES convert it to 4/7/2006.

Here is some code in case anyone is interested in what I'm doing. The last
line is where its blowing up. Look for Convert.ToDateTime...

--------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application excel = null;

Microsoft.Office.Interop.Excel.Workbook wb = null;

Microsoft.Office.Interop.Excel.Worksheet wks=null;

Microsoft.Office.Interop.Excel.Range rng = null;

int intRows;

object[,] data = null;

object missing = Type.Missing;

System.Data.DataTable
dtMedicalInvoices=dsMedicalInvoices1.Medical_Sales _Tracing_Invoices;

System.Data.DataTable
dtMedicalBranches=dsMedicalBranches1.Medical_Sales _Tracing_Distributor_Branches;

try

{

excel = new Microsoft.Office.Interop.Excel.Application();

wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);

//excel.Visible = true;

wb.Activate();

}

catch (COMException ex)

{

MessageBox.Show("Error accessing Excel: " + ex.ToString());

}

catch (Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}

wks = (Worksheet)wb.ActiveSheet;

intRows = wks.UsedRange.Rows.Count;

for (int I=2;I<=intRows;I++)

{

//Range of the columns in the worksheet I to AB

// Don't get confused between int I and the worksheet column I

rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());

data = (object[,])rng.Value2;

DataRow x=dtMedicalInvoices.NewRow();

x[0] = Convert.ToDateTime(data[1,2]);

}

Aug 31 '06 #1

Andy Bates

The following will solve your problem:

double d = 38449.0;
DateTime dt = DateTime.FromOADate(d);
// dt is now a .NET date time.

Basically it's passing it as an OLE Automation Date value. The above static
method will convert it into a DateTime structure.

HTH

- Andy
"jereviscious" <he**@there.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

Hi all - Last resort time.

I'm importing data from a spreadsheet that I receive from one of my vendor
using interop.excel.

The date field in excel is entered as 4/7/2006, but when I retrieve the
value, it returns a double 38449.0.

I can't figure out how to convert this value into a proper date. I have no
Idea what excel is doing with this date. Also, when I enter in 38449 into
that SAME column in excel it DOES convert it to 4/7/2006.

Here is some code in case anyone is interested in what I'm doing. The last
line is where its blowing up. Look for Convert.ToDateTime...

--------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application excel = null;

Microsoft.Office.Interop.Excel.Workbook wb = null;

Microsoft.Office.Interop.Excel.Worksheet wks=null;

Microsoft.Office.Interop.Excel.Range rng = null;

int intRows;

object[,] data = null;

object missing = Type.Missing;

System.Data.DataTable
dtMedicalInvoices=dsMedicalInvoices1.Medical_Sales _Tracing_Invoices;

System.Data.DataTable
dtMedicalBranches=dsMedicalBranches1.Medical_Sales _Tracing_Distributor_Branches;

try

{

excel = new Microsoft.Office.Interop.Excel.Application();

wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);

//excel.Visible = true;

wb.Activate();

}

catch (COMException ex)

{

MessageBox.Show("Error accessing Excel: " + ex.ToString());

}

catch (Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}

wks = (Worksheet)wb.ActiveSheet;

intRows = wks.UsedRange.Rows.Count;

for (int I=2;I<=intRows;I++)

{

//Range of the columns in the worksheet I to AB

// Don't get confused between int I and the worksheet column I

rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());

data = (object[,])rng.Value2;

DataRow x=dtMedicalInvoices.NewRow();

x[0] = Convert.ToDateTime(data[1,2]);

}

Aug 31 '06 #2

jereviscious

Beautiful, Thanks.
"Andy Bates" <an**@ussdev.comwrote in message
news:e1**************@TK2MSFTNGP05.phx.gbl...

The following will solve your problem:

double d = 38449.0;
DateTime dt = DateTime.FromOADate(d);
// dt is now a .NET date time.

Basically it's passing it as an OLE Automation Date value. The above
static method will convert it into a DateTime structure.

HTH

- Andy
"jereviscious" <he**@there.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

>Hi all - Last resort time.

I'm importing data from a spreadsheet that I receive from one of my
vendor
using interop.excel.

The date field in excel is entered as 4/7/2006, but when I retrieve the
value, it returns a double 38449.0.

I can't figure out how to convert this value into a proper date. I have
no
Idea what excel is doing with this date. Also, when I enter in 38449 into
that SAME column in excel it DOES convert it to 4/7/2006.

Here is some code in case anyone is interested in what I'm doing. The
last
line is where its blowing up. Look for Convert.ToDateTime...

--------------------------------------------------------------
Microsoft.Office.Interop.Excel.Application excel = null;

Microsoft.Office.Interop.Excel.Workbook wb = null;

Microsoft.Office.Interop.Excel.Worksheet wks=null;

Microsoft.Office.Interop.Excel.Range rng = null;

int intRows;

object[,] data = null;

object missing = Type.Missing;

System.Data.DataTable
dtMedicalInvoices=dsMedicalInvoices1.Medical_Sale s_Tracing_Invoices;

System.Data.DataTable
dtMedicalBranches=dsMedicalBranches1.Medical_Sale s_Tracing_Distributor_Branches;

try

{

excel = new Microsoft.Office.Interop.Excel.Application();

wb = excel.Workbooks.Open(FilePath, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing);

//excel.Visible = true;

wb.Activate();

}

catch (COMException ex)

{

MessageBox.Show("Error accessing Excel: " + ex.ToString());

}

catch (Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}

wks = (Worksheet)wb.ActiveSheet;

intRows = wks.UsedRange.Rows.Count;

for (int I=2;I<=intRows;I++)

{

//Range of the columns in the worksheet I to AB

// Don't get confused between int I and the worksheet column I

rng = wks.get_Range("I"+(string)I.ToString(),"AB"+(strin g)I.ToString());

data = (object[,])rng.Value2;

DataRow x=dtMedicalInvoices.NewRow();

x[0] = Convert.ToDateTime(data[1,2]);

}

Aug 31 '06 #3