_variant_t vItem = pRange->Item[ iRow ][ iCol ]; [modified]

Lost User

What exactly is the problem? You say that you are reading a string in the form "12:10:59". To convert this to a time value you need to split it into its component parts ("12", "10" and "59"), convert each substring to an integer, and then combine with a simple formula to give you the total number of seconds.

MsmVc

No my problem is this. If i am reading "12:00:12" from excel then through this code

_variant_t vItem = pRange->Item[ 1 ][ iColumn ];
_bstr_t bstrText( vItem );

i am getting 0.500138888888889 now what i do to get "12:00:12" from excel.

Lost User

MsmVc wrote:

i am getting 0.500138888888889

This is the internal Excel date and time format. I cannot remember the exact details but I think the datetime is in the form D.S, where D is the number of days since the 'epoch'*, and S is number of seconds since midnight divided by 86,400 (the total number of seconds in a day). So in your case to get the time we multiply 0.500138888888889 by 86,400 which gives 43,212 or 12 hours, 0 minutes and 12 seconds. *epoch is the base date from which counting starts. I think Excel may start at year zero.

MsmVc

Thanks for nice and smart concept.But my problem is something different. See i am getting values from Excel "0.500138888888889" this data can be Time format or double format.So how can i format this data.I think i need to identify data type of excel before format(Change). Please help me

MsmVc

i think need to post some more code.

try
{
.
CoInitialize(NULL);

while ( m\_list.DeleteColumn( 0 ) );
HRESULT hr;

CFileDialog fileDlg(TRUE, \_T("xls"), NULL, OFN\_PATHMUSTEXIST|OFN\_OVERWRITEPROMPT,\_T("Excel Files|\*.xls;\*.xlsx|"));
if (fileDlg.DoModal () == IDCANCEL) 
	return;

CString OpenFile = fileDlg.GetPathName ();

// Load the Excel application in the background.
Excel::\_ApplicationPtr pApplication;
if (FAILED( pApplication.CreateInstance( \_T("Excel.Application") ) ) )
{
	
	return;
}

\_variant\_t	varOption( (long) DISP\_E\_PARAMNOTFOUND, VT\_ERROR );

Excel::\_WorkbookPtr pBook = pApplication->Workbooks->Open( (\_bstr\_t)OpenFile, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption, varOption );
if ( pBook == NULL )
{
		pBook->Close( VARIANT\_FALSE );

// Need to quit, otherwise Excel remains active and locks the .xls file.
pApplication->Quit( );
	Errorf( \_T("Failed to open Excel file!") );
	return;
}


for(int i=1;i<4;i++)
{
	Excel::\_WorksheetPtr pSheet = pBook->Sheets->Item\[ i \];
	
	\_bstr\_t na=pSheet->GetName();
			
	
if ( pSheet == NULL )
{
	
	return;
}

// Load the column headers.
Excel::RangePtr pRange = pSheet->GetRange( \_bstr\_t( \_T("A1") ), \_bstr\_t( \_T("Z1" ) ) );



if ( pRange == NULL )
{
	Errorf( \_T("Failed to get header cell range( A1:iv )!") );
	return;
}

int	iColumns = 0;

for ( int iColumn = 1; iColumn < 26; ++iColumn )
{
	\_variant\_t	vItem = pRange->Item\[ 1 \]\[ iColumn \];
	\_bstr\_t		bstrText( vItem );

	if ( bstrText.length( ) == 0 )
		break;

	m\_list.InsertColumn(iColumns++,bstrText,LVCFMT\_LEFT,170 );
}

// Load the rows (up to the first blank one).
pRange = pSheet->GetRange( \_bstr\_t( \_T("A2") ), \_bstr\_t( \_T("Z16384" ) ) );


int iColumn=0;
for ( int iRow = 1; ; ++iRow )
{
	for (  iColumn = 1; iColumn <= iColumns; ++iColumn )
	{
		\_variant\_t	vItem = pRange->Item\[ iRow  \]\[ iColumn \];

//Here i am getting values.
_bstr_t bstrText( vItem );

		if ( bstrText.length( ) == 0 )
			break;

		if ( iColumn == 1 )
			m\_list.InsertItem( iRow - 1, bstrText );
		else
			m\_list.SetItemText( iRow - 1, iColumn - 1, bstrText );
	}

	if ( iColumn == 1 )
		break;
}

// Make it all look pretty.
for ( int iColumn = 1; iColumn <= iColumns; ++iColumn )
	m\_list.SetColumnWidth( iColumn, LVSCW\_AUTOSIZE\_USEHEADER );
}
// Don't

Lost User

MsmVc wrote:

I think i need to identify data type of excel before format(Change).

Yes I guess that's right. Unfortunately I cannot help with that as I am not sure how to get the information from Excel as to what type of information is held in a particular cell. I suspect that there is no way; if you write a number in an Excel cell, you can format it any way you like. However, you are the only person that knows what that number represents, all Excel knows is how you want it to be displayed on the screen!

MsmVc

Plz help me..

Franck Paquier

Good morning I agree with Richard. You know your excel document. so if you expect to find time cells inside, you should detect that when reading it. I think you could try to retreive from excel the cell format. detect the date time mode and react accordingly. if you cannot find the cell format then if i remeber well you can retreive the value of your cell as a string. in that case i would post analyze it and if something like "07:07:13" is detected i will do the tranformation for a time. bye the way did you try to analyze the type of your read vItem to see what is in the BSTR value for a date time cell ? Regards FRanck.

MsmVc

Good morning Thank for reply. i will do the tranformation a time I am waiting for you response. BSTR value for a date time cell ? i try to do that but didn't find through this code.

_variant_t vItem = pRange->Item[ 1 ][ iColumn ];

	COleDateTime dt;
	 switch (vItem.vt )
	{

	     case VT\_DATE:
		{

		dt = vItem.date;

		break;

		}
		case VT\_R8:

		{

AfxMessageBox("dOUBLE");

		break;

		}

		case VT\_BSTR:

		{
			\_bstr\_t		bstrText( vItem );

		//tmpData =(CString)val.bstrVal;
		//
		//strData+=tmpData;

		break;

		}

		case VT\_EMPTY:

		{

		TRACE("\\t\\t<empty>");

		break;

		}
	}

cursor jump out side the case.

Franck Paquier

Hi If your switch detect nothing i guess it is because the date time is really stored as a double inside excel. Your call:

pRange->Item [1][iColumn]

always return the value as a double at least for date and time. To by-pass that i would try to retreive the Text of the cell which is what the cell show. Text if from VBA doc but it should exist the same entry point in automation. In that case i am pretty sure you will recieved the 10:23:00 value. In the web site above there is an explanation about how to translate an excel date time: http://www.cpearson.com/excel/datetime The last soulution: If your date time cell is at a fixed place then just retrieve the value and translate as explain in the above link. Regards Franck

MsmVc

i think it's my fault to not describe proper. Date and time cell is not fixed.If i know this cell have date time then i easy convert it. My problem is how to know data time format.

Franck Paquier

Hi I think like you do yourself when you watch an excel spread sheet. Either you retrieve the Text dispalyed by the cell and you recognize that it is a time. or you right click on the cell and look at the format of the cell. and you see something like dd:mm:ss Right now in your code you just query the double value of the cell it is not enough. You have to explore your cell deeper in order to be able to make a decision. To do that you can: Getting the STRING/TEXT displayed by the cell. and or the FORMAT associated with the cell. Regards.

MsmVc

Respected Sir i know that all things to check right click on cell get format. explore your cell deeper in order to be able to make a decision i am same thing last 3 or 4 post.I don't have idea to got the cell format.Try to understand my problem.Plz help me

Franck Paquier

code bellow will return you something like: "12:56:32" for a cell containing time.

Excel::RangePtr pRange = pSheet->GetRange( _bstr_t(_T("A1") ) );
if ( pRange == NULL ){
return;
}

_bstr_t sText= pRange->GetText(); it exist also something called CellFormat. but i cannot find how to call it. Sorry for the short answer but i do not have good code sample with me right now. regards

Franck Paquier

i wanted to type

Excel::RangePtr pRange = pSheet->GetRange( _bstr_t(_T("A1") ) );
if ( pRange == NULL ){
return;
}

_bstr_t sText= pRange->GetText();

MsmVc

thanks i am trying to do through code.

_variant_t vItem = pRange-&gt;Item[ iRow ][ iCol ]; [modified]

_variant_t vItem = pRange->Item[ iRow ][ iCol ]; [modified]