Open Excel in VC++ and write data
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 78

Thread: Open Excel in VC++ and write data

Hybrid View

  1. #1
    Join Date
    Nov 2003
    Posts
    107

    Thumbs down Open Excel in VC++ and write data

    I have a program which retrieves data from a SQL server. I want to write this data to an excel sheet and draw graphs using this data.
    As of now I wrote the output of my program in csv format, so after I run the program it creates an excel file.
    But how can I make my program open an excel sheet, write data to it, generate the graphs.
    Basically my output should be an excel file with data and graphs in it.
    Any kind of help is appreciated.
    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    The place to start is
    http://msdn.microsoft.com/library/de...sofficedev.asp

    Try the tutorial examples at the MSDN site.

    Next try searching in the c++ forums using Excel or automation as search terms.
    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.

  3. #3
    Join Date
    Nov 2003
    Posts
    107

    Excel Automation

    Thanks a lot. That was helpful.
    Now I have another problem. ....
    I could open an excel application, create a new workbook and write data, (Strings and integers) to it. But I wanted to know how I could draw graphs with this data.
    I recorded a macro in excel and I have the VB code to do what I want, but please tell me how I should write this VB code in C++
    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    For an example of creating an xy chart:
    http://www.codeguru.com/forum/showth...light=Chart%2A
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    107
    Tom,

    Thanks for your reply, but I am not using MFC's to automate excel. I wrote the Automation code in straight C++ with help from Microsoft Knowledgebase. Can you please guide me on how to draw charts with this.....
    I could not find any help in the MS knowledgebase.
    Appreciate your help.

  6. #6
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    I'm attaching a bare bones ( sphaghetti code without comments )example of creating an xy chart based on the example of automation without using the type library on MSDN.

    I created anexcel file d:\junk.xls that had x data in a1-a7 and
    y data in b1-b7. You will have to do so too if you try this program.

    I first tried to use the chart wizard but eventually gave up and created the plot directly.
    Attached Files Attached Files
    Last edited by Tom Frohman; November 18th, 2003 at 09:06 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.

  7. #7
    Join Date
    Nov 2003
    Posts
    107
    Tom,

    Thankyou very much. That was a great help I could build bar and pie charts for my data with your help. But where do I get more information about the code. Which is the right place to read about what each statement is doing.

    Again Thanks a bunch.

  8. #8
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    Each time I do something in Automation the first thing I do is record a VBA macro and then translate it into C++.
    I look up the functions that VBA used and the VBA help to get descriptions of the functions and the constants needed. The object inspector of the VBA editor (F2) is also a valuable resource. You can look up the classes and their members.

    To find the methods and the arguments specifically for C, I look in the excel9.olb type library using OLEView.
    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. #9
    Join Date
    Nov 2003
    Posts
    107
    That was helpful. I figured that out by looking at your code, but I thought may be there was a fast rule to do this.
    I have another question, I tried recording macros in VB, but it does not seem to record all the things I do. Like for example, when I recorded a macro for drawing the graphs, I set the values to be seen on the top of the bar, but I could not see the code for that in the macro.

  10. #10
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    Originally posted by gurupot
    That was helpful. I figured that out by looking at your code, but I thought may be there was a fast rule to do this.
    I have another question, I tried recording macros in VB, but it does not seem to record all the things I do. Like for example, when I recorded a macro for drawing the graphs, I set the values to be seen on the top of the bar, but I could not see the code for that in the macro.
    Yes, I've seen that happen with macros. I don't know a way around it.
    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. #11
    Join Date
    Nov 2003
    Posts
    107
    I appreciate your help earlier.
    I could successfully write data and graphs to excel, but when I try to save it, its giving me an errror. The strange thing is, it worked first and after a few days it showed me this error. Here is my code, please let me know if you can help.

    //Get the active workbook
    IDispatch *pXlActiveWorkBook;
    {
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveWorkbook", 0);
    pXlActiveWorkBook = result.pdispVal;
    }

    //Save the work book.
    {
    VARIANT result;
    VariantInit(&result);
    VARIANT fname;
    fname.vt = VT_BSTR;
    fname.bstrVal=::SysAllocString(L"C:\\output.xls");
    VARIANT fformat;
    fformat.vt = VT_I4;
    fformat.lVal= -4143;
    AutoWrap(DISPATCH_METHOD, &result, pXlActiveWorkBook, L"SaveAs", 2, fname, fformat);
    }

  12. #12
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862
    I tried out SaveAs and it caused an error for me too.

    I then dropped the format as -4143 is the default anyways and it worked.

    AutoWrap(DISPATCH_METHOD, &result, pXlActiveWorkBook, L"SaveAs", 1, fname);
    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. #13
    Join Date
    Apr 2005
    Posts
    23

    Re: Open Excel in VC++ and write data

    Hello, i want to ask if anybody knows the way to use in C++ the DISPATCH_METHOD with AUTOWRAP for the function "ChartWizard" with it's possible to put the tittle in a excel's chart.

    It would be something like that

    AutoWrap(DISPATCH_METHOD, &result, pXlChart, L"ChartWizard",...)

    but i don't know what's the variables and how to specify the tittle, x column tittle, y column tittle.

    I will be very grateful with any kind of help

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

    Re: Open Excel in VC++ and write data

    I should be able to cook up an example about 12 hours from now.
    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. #15
    Join Date
    Apr 2005
    Posts
    23

    Re: Open Excel in VC++ and write data

    Thankyou.

Page 1 of 2 12 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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center