|
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]);
} | |
Share:
|
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]);
} | | |
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]);
} | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
3 posts
views
Thread by Don.Vonderburg@nospam.com |
last post: by
|
1 post
views
Thread by Richard Holliingsworth |
last post: by
|
3 posts
views
Thread by Mike Dundee |
last post: by
|
2 posts
views
Thread by ScardyBob |
last post: by
|
3 posts
views
Thread by Bharathi |
last post: by
| |
6 posts
views
Thread by teser3@hotmail.com |
last post: by
| |
3 posts
views
Thread by NEWSGROUPS |
last post: by
| | | | | | | | | | |