Open Excel in VC++ and write data

[Tom Frohman]

Using the Non-MFC methods I set the number format to three places, change the font to bold and set underline.

IDispatch *pXlRange1;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


{  
VARIANT frmt;
frmt.vt = VT_BSTR;
frmt.bstrVal = ::SysAllocString(L"0.000");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"NumberFormat", 1, frmt);


}



IDispatch *pXlFont;
	{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;


	}


	{
VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Bold", 1,xprop);
	}

	{
VARIANT result;
VARIANT xprop;
xprop.vt=VT_I4;
xprop.lVal=2;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Underline", 1,xprop);
	}

[neyadakim]

Hi Tom,

Thanks but there is an error when calling AutoWrap function. GetIDsOfNames is generate error.

IDispatch *pXlFont;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}

What will be cause for this?
And also how can ı set cell widht and height?

Thanks..

[Tom Frohman]

I don't know. The code worked for me. I'd have to see your code leading up to and including the get Font part. I still probably might not be able to tell.

What version of Excel are you using? I was using Excel 2000.

[TheCPUWizard]

Tom would you be able to duplicate these samples for Lotus-123??

<ducking and running>

ps: Seriously, this is something I think you should put togeter as a quick article here on CG.

[neyadakim]

Hi Tom,

I solved the problem, thank again.
I need some extra info about excel automation that
how can i set width,height,format( is general,text,numeric), Wrap Text property,font size and type, and alignment properties.

If you know some way i'll be pleased to read you.

[Tom Frohman]

Sorry, I've been away on Christmas vacation the last 11 days. You've probably answered these questions long ago but:

1) I don't know how to set height and width of the font independently. The font only has a Size property.

{  
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"WrapText", 1, xprop);

}
//left=-4131 center=-4128 right= -4152 general=1
{  
VARIANT xprop;
xprop.vt=VT_I4;
xprop.lVal=-4131L;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlRange1, L"HorizontalAlignment", 1, xprop);
}

IDispatch *pXlFont;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}

{  
VARIANT nme;
nme.vt = VT_BSTR;
nme.bstrVal = ::SysAllocString(L"Brush Script");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Name", 1,nme);

}

{  
VARIANT hgt;
hgt.vt = VT_I4;
hgt.lVal=20;
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Size",1,hgt);
//I don't know how to set height and width independently
}

[neyadakim]

Hi Tom;

I want to freeze the first row when i export data to excel. It can be done in VB by "FreezePanes" property set to TRUE.
I cant use it in my Excel Automation. It gives error.
How can i use "FreezePanes" in our Excel Automation.

Thanks.

[Tom Frohman]

Using the methods of this thread starting with the XlApp dispatch, To freeze the first row and column select the B2 cell.

To freeze the first row select the entire second row.
To freeze the first column select the entire B column.

IDispatch *pXlWindows;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Windows", 0);
pXlWindows = result.pdispVal;
}
	
IDispatch *pXlWindow;
{
VARIANT result;
VARIANT xwind;
xwind.vt=VT_I4;
xwind.lVal=1;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlWindows, L"Item", 1,xwind);
pXlWindow = result.pdispVal;
}

IDispatch *pXlRange2;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"B2");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1,range);

pXlRange2 = result.pdispVal;
}

{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_METHOD, &result, pXlRange2, L"Select", 0);
}	



{
VARIANT result;
VARIANT xprop;
xprop.vt=VT_BOOL;
xprop.boolVal=TRUE;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlWindow, L"FreezePanes", 1,xprop);
}

To use MFC you would use some variant on

Windows wnds;
wnds=app.GetWindows();
Window wnd;
wnd=wnds.GetItem(COleVariant(1L));
Range rng;
rng=sheet.GetRange(COleVariant("B2"),COleVariant("B2"));
rng.Select();
wnd.SetFreezePanes(COleVariant((short)TRUE));

The method names might be a little different depending on what version of Visual C++ you are using.

[minagu]

Hi, does anybady know if there's any function or way to free all the memory used in c++ till that moment? i'm having problems with a program which ends after using during a period of time (not short but not long) and i've revised too many times the code and i think that i'm freeing well, but the program ends at different places without a reasonable explanation, and it smells me that it could be a memory problem, out of memory. Thanks

[Tom Frohman]

If I were you, I would delete your previous post and then repost it in the Visual C++ forum as a new thread. It is a different topic and the title of this thread will kind of stop people from reading it.

Tom

[neyadakim]

Hi Tom;

How can i give Font and BackGround color?

Thanks for your help.

[Tom Frohman]

Below I set the font color to red and the background of the cell to green.

IDispatch *pXlRange1;
{  
VARIANT range;
range.vt = VT_BSTR;
range.bstrVal = ::SysAllocString(L"C1:C3");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, range);

pXlRange1 = result.pdispVal;
}


IDispatch *pXlInside;
{  

VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Interior", 0);

pXlInside = result.pdispVal;
}
{  
VARIANT color;
color.vt = VT_I4;
color.lVal=RGB(0,255,0);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlInside, L"Color",1,color);

}



IDispatch *pXlFont;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange1, L"Font", 0);
pXlFont = result.pdispVal;
}


{  
VARIANT color;
color.vt = VT_I4;
color.lVal=RGB(255,0,0);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlFont, L"Color",1,color);

}

[minagu]

Tom have you looked if after using Autowrap functions and doing things creating a xls file, do you have memory RAM problems, because is exactly what it happens to me. The Ram use increases without reason, and i don't know why. And i think that is not the best when a .exe is running, and that's becaues it crash after a running time, there's no memory!

[Tom Frohman]

The truth? Okay, I haven't experienced the memory problems because I use the MFC based method of Automation based on importing a type library.

I didn't even start this example. It didn't come from this thread. I got the core code off of somebody else who was having trouble making it work.

I didn't do anything like clearing the Variants with VariantClear() and I didn't release all of the Dispatchs. Couldn't actually say these are necessary. Could be they are necessary.
VariantClear
SysFreeString

[minagu]

My case is not usual, because i want to test how long last the program could be, but my question is if you, watching the task manager, see the same that i see, that is that since the "Excel.exe" appears in the list (task manager) the ram memory begins to increase, and when the "Excel.exe" disappears(it has been deleted the excel class) the ram memory doesn't come back to the previous state. The only reason i could imagine is that Autowraps function consumes too much ram memory, and i would want to know if anyone could see something similar in the task manager, and if there's any way to stop that increase of ram use, because i think that the normal should be that after creating the excel, the aplication's ram consumption would come back to the previous stat once that has ended the job.

Because if you only want to run it once there's no problem, but if you want to do it more times the ram memory is a real problem.