Reading Excel dates into C# come out as "weird" ints - help?

[sherifffruitfly]

Hi,

I'm using an adaptation of excel-reading code that's all over the
internet - I don't much like or understand it, but it has worked for me
in the past.... beggars can't be choosers... :
Excel.Application excelObj = new Excel.Application();
Excel.Workbook theWorkbook = excelObj.Workbooks.Open(path+filename,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, true, true);
Excel.Sheets sheets = theWorkbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

//This is an incredibly ghetto solution.
//Need to learn more about the Excel api
//and make this code reasonable.
int row = 2;
while (row<100)
{
string xlValue = worksheet.get_Range("A" +
row.ToString(), "A" + row.ToString()).Cells.Value2.ToString();
MessageBox.Show("Here you go: " + xlValue);
row++;
}

The file being read has its first column a bunch of dates (when you
look at it in excel). Here are the first few values of the column in
Excel, along with the first couple contents of the MessageBox above:

Excel MessageBox
11/30/2002 - 37590
12/31/2002 - 37621
1/31/2003 - 37652
2/28/2003 - 37680
3/31/2003 - 37711
4/30/2003 - 37741
5/31/2003 - 37772
6/30/2003 - 37802
7/31/2003 - 37833

I say these ints are "weird" because when I try to cast xlValue above
as an int, I get a "cast not valid" error. WTF? Same deal when I try to
cast xlValue as a DateTime.

All I want to do is search the date column for a particular date and
then get some values out of other columns once a date-match is found.

What can I do here?

Thanks for any advice,

cdj

[Ciaran O''Donnell]

the xl date is the number of days since the 01/01/1900 00:00:00 (on a mac its
1904) so the best way to get the date is to use the Text property of the cell
which will give you the string displayed in it (you can then DateTime.Parse
this) or
new DateTime(1900,1,1).AddDays(xlValue);

I think the casting issue is becuase its a double or a decimal or something
as it can have .000's. Look at is quick watch and it should tell you the
underlying type. If all else fails,
double xlValued = 0.0;
Double.TryParse(xlValue, NumberStyles.Any, out xlValued);

HTH

Ciaran O'Donnell
"sherifffruitfly" wrote:

Hi,

I'm using an adaptation of excel-reading code that's all over the
internet - I don't much like or understand it, but it has worked for me
in the past.... beggars can't be choosers... :
Excel.Application excelObj = new Excel.Application();
Excel.Workbook theWorkbook = excelObj.Workbooks.Open(path+filename,
0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false,
false, 0, true, true, true);
Excel.Sheets sheets = theWorkbook.Worksheets;
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);

//This is an incredibly ghetto solution.
//Need to learn more about the Excel api
//and make this code reasonable.
int row = 2;
while (row<100)
{
string xlValue = worksheet.get_Range("A" +
row.ToString(), "A" + row.ToString()).Cells.Value2.ToString();
MessageBox.Show("Here you go: " + xlValue);
row++;
}

The file being read has its first column a bunch of dates (when you
look at it in excel). Here are the first few values of the column in
Excel, along with the first couple contents of the MessageBox above:

Excel MessageBox
11/30/2002 - 37590
12/31/2002 - 37621
1/31/2003 - 37652
2/28/2003 - 37680
3/31/2003 - 37711
4/30/2003 - 37741
5/31/2003 - 37772
6/30/2003 - 37802
7/31/2003 - 37833

I say these ints are "weird" because when I try to cast xlValue above
as an int, I get a "cast not valid" error. WTF? Same deal when I try to
cast xlValue as a DateTime.

All I want to do is search the date column for a particular date and
then get some values out of other columns once a date-match is found.

What can I do here?

Thanks for any advice,

cdj

[sherifffruitfly]

Ciaran O''Donnell wrote:

the xl date is the number of days since the 01/01/1900 00:00:00 (on a mac its
1904) so the best way to get the date is to use the Text property of the cell
which will give you the string displayed in it (you can then DateTime.Parse
this) or
new DateTime(1900,1,1).AddDays(xlValue);

I think the casting issue is becuase its a double or a decimal or something
as it can have .000's. Look at is quick watch and it should tell you the
underlying type. If all else fails,
double xlValued = 0.0;
Double.TryParse(xlValue, NumberStyles.Any, out xlValued);

HTH

Thanks! It helped immensely! It's bizarre that you still have to cast
the cell's Text property as as string, but whatever.

Is it just me, or is dealing with an Excel sheet in c# incredibly
arcane?

Thanks again for your help, and the background info!

cdj