-
December 22nd, 2006, 09:07 AM
#31
Re: Open Excel in VC++ and write data
Using the Non-MFC methods I set the number format to three places, change the font to bold and set underline.
Code:
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);
}
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
December 22nd, 2006, 10:48 AM
#32
Re: Open Excel in VC++ and write data
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..
-
December 22nd, 2006, 06:12 PM
#33
Re: Open Excel in VC++ and write data
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.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
December 22nd, 2006, 06:24 PM
#34
Re: Open Excel in VC++ and write data
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.
TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
2008, 2009,2010
In theory, there is no difference between theory and practice; in practice there is.
* Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
* How NOT to post a question here
* Of course you read this carefully before you posted
* Need homework help? Read this first
-
December 23rd, 2006, 03:41 AM
#35
Re: Open Excel in VC++ and write data
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.
-
January 2nd, 2007, 08:27 AM
#36
Re: Open Excel in VC++ and write data
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.
Code:
{
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
}
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
January 30th, 2007, 04:54 AM
#37
Re: Open Excel in VC++ and write data
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.
-
January 30th, 2007, 08:10 AM
#38
Re: Open Excel in VC++ and write data
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.
Code:
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
Code:
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.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
February 16th, 2007, 03:58 AM
#39
Re: Open Excel in VC++ and write data
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
-
February 19th, 2007, 07:45 AM
#40
Re: Open Excel in VC++ and write data
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
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
February 20th, 2007, 08:47 AM
#41
Re: Open Excel in VC++ and write data
Hi Tom;
How can i give Font and BackGround color?
Thanks for your help.
-
February 21st, 2007, 08:10 AM
#42
Re: Open Excel in VC++ and write data
Below I set the font color to red and the background of the cell to green.
Code:
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);
}
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
February 22nd, 2007, 08:04 AM
#43
Re: Open Excel in VC++ and write data
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!
-
February 22nd, 2007, 12:45 PM
#44
Re: Open Excel in VC++ and write data
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
Last edited by Tom Frohman; February 22nd, 2007 at 12:58 PM.
Verere testudinem! (Fear the turtle)
Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein
Robots are trying to steal my luggage.
-
February 23rd, 2007, 07:07 AM
#45
Re: Open Excel in VC++ and write data
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|