Accessing Excel object in windows c# application

[Guest]

Hi Folks,

I have a question regarding my windows c# application.
This application just reads MS Excel file and puts the data in to sql server
database.
In that excel file ,it has one named cell as "OutputStampTime".
I am facing problem while accessing this cell value.
When I open excel, I can see the value as "6:49 AM" . But when I click on
that cell
in the formula bar I am seeing as 01/27/2005 06:49:47 AM.

When I tried to read that value using c# code I am getting that value as
06:49 AM (If I use range Text property) or "38380.2845648148" (If I use
range value property).
I want to get exact time from this cell . (i.e as 06:49:47 AM).
How can I convert 38380.2845648148 to corresponding time in c#???
In Excel if you format the same cell as general number it is showing as
38380.2845648148 .

But How can I programatically format that value using c#,Excel code??
This excel file which I am reading is protected. we don't know password to
unprotect it.

The following is the sample code....




Excel.Range rng1,rng2,rng3,rng4;
Excel.Worksheet worksheet = new Excel.Worksheet() ;
Excel.Sheets sheets;
Excel.Workbook theWorkbook;
int result;
string SheetName;
string sTime1;


Excel.Application ExcelObj = null;
ExcelObj = new Excel.Application();

theWorkbook =ExcelObj.Workbooks.Open("C:\\TestExcel1.xls", 0, true,5,"",
"", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);

sheets = theWorkbook.Worksheets;

for (int i=1; i<=sheets.Count; i++)
{
worksheet = (Excel.Worksheet) theWorkbook.Worksheets.get_Item(i);
SheetName = worksheet.Name.Trim();
result = string.Compare(SheetName,"Sheet1",true);
if (result == 0)
{
break;
}
}



rng1 = worksheet.get_Range("OutputStampDate","OutputStampDate");
sTime1 = rng1.Text.ToString();

//"38380.2845648148" {38380.284564814814} [System.Double]:
theWorkbook.Close((System.Boolean)false,System.Reflection.Missing.Value,
System.Reflection.Missing.Value );



How can I convert double value to datetime value in c#?
I tried several options of using string.format and convert.ToDateTime but
NO Luck.

I appreciate If some one through light on this and direct me to some sample
codes..

Thanking you
Kumar

 

[Charles Maxson]

Kumar,

You can make the conversion on the Excel side using built-in Excel
functions. Once you get the Range object, use the WorksheetFunction "Text"
from Excel's OM to get the exact time.

rng1 = worksheet.get_Range("OutputStampDate","OutputStampDate");
sTime1 = ExcelObj.Application.WorksheetFunction.Text(rng1.Text,@"HHD:SS
AM/PM");

DateTime dtTime = DateTime.Parse(sTime1);

Try and see if that works for you

 

[Guest]

Hi Charles,

Thank You very much for your help.
The following syntax is worked for me.
sTime1 = ExcelObj.Application.WorksheetFunction.Text(rng1.Value,@"HH:MM:SS
AM/PM");

I almost worked for 6 hours on this problem yesterday.
I appreciate for all your help.

Thanking you
Kumar

Kumar,

You can make the conversion on the Excel side using built-in Excel
functions. Once you get the Range object, use the WorksheetFunction "Text"
from Excel's OM to get the exact time.

rng1 = worksheet.get_Range("OutputStampDate","OutputStampDate");
sTime1 = ExcelObj.Application.WorksheetFunction.Text(rng1.Text,@"HHD:SS
AM/PM");

DateTime dtTime = DateTime.Parse(sTime1);

Try and see if that works for you

--

Charles
www.officezealot.com

Hi Folks,

I have a question regarding my windows c# application.
This application just reads MS Excel file and puts the data in to sql
server
database.
In that excel file ,it has one named cell as "OutputStampTime".
I am facing problem while accessing this cell value.
When I open excel, I can see the value as "6:49 AM" . But when I click on
that cell
in the formula bar I am seeing as 01/27/2005 06:49:47 AM.

When I tried to read that value using c# code I am getting that value as
06:49 AM (If I use range Text property) or "38380.2845648148" (If I use
range value property).
I want to get exact time from this cell . (i.e as 06:49:47 AM).
How can I convert 38380.2845648148 to corresponding time in c#???
In Excel if you format the same cell as general number it is showing as
38380.2845648148 .

But How can I programatically format that value using c#,Excel code??
This excel file which I am reading is protected. we don't know password to
unprotect it.

The following is the sample code....




Excel.Range rng1,rng2,rng3,rng4;
Excel.Worksheet worksheet = new Excel.Worksheet() ;
Excel.Sheets sheets;
Excel.Workbook theWorkbook;
int result;
string SheetName;
string sTime1;


Excel.Application ExcelObj = null;
ExcelObj = new Excel.Application();

theWorkbook =ExcelObj.Workbooks.Open("C:\\TestExcel1.xls", 0, true,5,"",
"", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true);

sheets = theWorkbook.Worksheets;

for (int i=1; i<=sheets.Count; i++)
{
worksheet = (Excel.Worksheet) theWorkbook.Worksheets.get_Item(i);
SheetName = worksheet.Name.Trim();
result = string.Compare(SheetName,"Sheet1",true);
if (result == 0)
{
break;
}
}



rng1 = worksheet.get_Range("OutputStampDate","OutputStampDate");
sTime1 = rng1.Text.ToString();

//"38380.2845648148" {38380.284564814814} [System.Double]:
theWorkbook.Close((System.Boolean)false,System.Reflection.Missing.Value,
System.Reflection.Missing.Value );



How can I convert double value to datetime value in c#?
I tried several options of using string.format and convert.ToDateTime
but
NO Luck.

I appreciate If some one through light on this and direct me to some
sample
codes..

Thanking you
Kumar