Open Excel in VC++ and write data - Page 3
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 3 of 6 FirstFirst 123456 LastLast
Results 31 to 45 of 78

Thread: Open Excel in VC++ and write data

  1. #31
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  2. #32
    Join Date
    Dec 2006
    Posts
    10

    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..

  3. #33
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  4. #34
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    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
    In theory, there is no difference between theory and paractice; 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

  5. #35
    Join Date
    Dec 2006
    Posts
    10

    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.

  6. #36
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  7. #37
    Join Date
    Dec 2006
    Posts
    10

    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.

  8. #38
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  9. #39
    Join Date
    Apr 2005
    Posts
    23

    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

  10. #40
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  11. #41
    Join Date
    Dec 2006
    Posts
    10

    Re: Open Excel in VC++ and write data

    Hi Tom;

    How can i give Font and BackGround color?

    Thanks for your help.

  12. #42
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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.

  13. #43
    Join Date
    Apr 2005
    Posts
    23

    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!

  14. #44
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    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 11:58 AM.
    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.

  15. #45
    Join Date
    Apr 2005
    Posts
    23

    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.

Page 3 of 6 FirstFirst 123456 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center