Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Generating embedded charts via Qt ActiveX
Forum Updated to NodeBB v4.3 + New Features

Generating embedded charts via Qt ActiveX

Scheduled Pinned Locked Moved Solved General and Desktop
qaxobjectactivexexcelcharts
24 Posts 3 Posters 11.5k Views 2 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • M m.sue
    2 Nov 2016, 13:30

    Hi,
    the code looks like Excel is the one that does the work. You just say what Excel should do. So the commands you send are Excel-commands which should be defined in the Excel documentation. QQxObject just enables the communication with Excel but does not produce the actual charts. You could probably do the same with the same commands using PowerShell.
    -Michael.

    A Offline
    A Offline
    alizadeh91
    wrote on 2 Nov 2016, 13:32 last edited by
    #3

    @m.sue Yes and I assumed that maybe someone has experienced with that since I'm not.

    1 Reply Last reply
    0
    • M Offline
      M Offline
      m.sue
      wrote on 2 Nov 2016, 13:43 last edited by m.sue 11 Feb 2016, 13:46
      #4

      How about this: It's for VBA but you should be able to get the information of what is possible and how to do it: https://msdn.microsoft.com/library/office/ff820756.aspx

      1 Reply Last reply
      2
      • A Offline
        A Offline
        alizadeh91
        wrote on 2 Nov 2016, 13:46 last edited by
        #5

        I'm just looking for some hints since I'm quite new in this area. The link you have sent is not english!!!

        M 1 Reply Last reply 2 Nov 2016, 13:56
        0
        • M Offline
          M Offline
          m.sue
          wrote on 2 Nov 2016, 13:48 last edited by
          #6

          Now it is :-)

          1 Reply Last reply
          0
          • A Offline
            A Offline
            alizadeh91
            wrote on 2 Nov 2016, 13:55 last edited by
            #7

            That didn't help. I'm little confused.What are those APIs in the code I've posted? I'll be really appreciated if anybody help

            1 Reply Last reply
            0
            • A alizadeh91
              2 Nov 2016, 13:46

              I'm just looking for some hints since I'm quite new in this area. The link you have sent is not english!!!

              M Offline
              M Offline
              mrjj
              Lifetime Qt Champion
              wrote on 2 Nov 2016, 13:56 last edited by
              #8

              @alizadeh91

              What is the difference between the chart object you get with this code and the
              one you refer to as embedded?
              It seems the same
              as
              http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

              You add chart
              AxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
              You set type
              chart->setProperty("Chart Type", 73);

              The API is for the programming of Excel. Its Automation API.

              A 1 Reply Last reply 2 Nov 2016, 13:59
              2
              • M mrjj
                2 Nov 2016, 13:56

                @alizadeh91

                What is the difference between the chart object you get with this code and the
                one you refer to as embedded?
                It seems the same
                as
                http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

                You add chart
                AxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
                You set type
                chart->setProperty("Chart Type", 73);

                The API is for the programming of Excel. Its Automation API.

                A Offline
                A Offline
                alizadeh91
                wrote on 2 Nov 2016, 13:59 last edited by
                #9

                @mrjj Thanks, The chart that is created is a 'worksheet chart' which is a worksheet which is dedicated to a fullscreen chart. I want to create a embedded chart in a ordinary worksheet.

                M 1 Reply Last reply 2 Nov 2016, 14:07
                0
                • A alizadeh91
                  2 Nov 2016, 13:59

                  @mrjj Thanks, The chart that is created is a 'worksheet chart' which is a worksheet which is dedicated to a fullscreen chart. I want to create a embedded chart in a ordinary worksheet.

                  M Offline
                  M Offline
                  mrjj
                  Lifetime Qt Champion
                  wrote on 2 Nov 2016, 14:07 last edited by
                  #10

                  @alizadeh91
                  Hmm
                  I think it comes from the fact we create it
                  QAxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
                  on the workbook

                  I wonder if we can ask worksheet->querySubObject("Charts")xxx
                  so its added to the collection of the sheet.
                  Or
                  "ActiveSheet"-->querySubObject("Charts")

                  If you see here
                  https://msdn.microsoft.com/en-us/library/office/ff194426.aspx

                  So I think its about what collection you put it in.

                  A 1 Reply Last reply 2 Nov 2016, 14:17
                  2
                  • M mrjj
                    2 Nov 2016, 14:07

                    @alizadeh91
                    Hmm
                    I think it comes from the fact we create it
                    QAxObject *chart = workbook->querySubObject("Charts")->querySubObject("Add");
                    on the workbook

                    I wonder if we can ask worksheet->querySubObject("Charts")xxx
                    so its added to the collection of the sheet.
                    Or
                    "ActiveSheet"-->querySubObject("Charts")

                    If you see here
                    https://msdn.microsoft.com/en-us/library/office/ff194426.aspx

                    So I think its about what collection you put it in.

                    A Offline
                    A Offline
                    alizadeh91
                    wrote on 2 Nov 2016, 14:17 last edited by
                    #11

                    @mrjj Thanks again, But I have question in my mind and there are some gaps in my understandings. What is the differences between VBA sample codes in your link and the Activex codes ? How should I convert them to c++? Should I just follow the patterns and guess them?!

                    M 1 Reply Last reply 2 Nov 2016, 14:22
                    0
                    • A alizadeh91
                      2 Nov 2016, 14:17

                      @mrjj Thanks again, But I have question in my mind and there are some gaps in my understandings. What is the differences between VBA sample codes in your link and the Activex codes ? How should I convert them to c++? Should I just follow the patterns and guess them?!

                      M Offline
                      M Offline
                      mrjj
                      Lifetime Qt Champion
                      wrote on 2 Nov 2016, 14:22 last edited by
                      #12

                      @alizadeh91
                      They are using the same model. The syntax is just different. With Qt you are running it via
                      a Automation server which VBA also does but its just more natural looking.

                      So even syntax not the same, the object its uses and often also actual structure is the same.

                      So if you know how to get a embedded chart in VBA, the same will work for Qt.

                      Therefore an understanding of the model (of Excel) is needed to use the Qt code.

                      A 1 Reply Last reply 2 Nov 2016, 14:24
                      1
                      • M mrjj
                        2 Nov 2016, 14:22

                        @alizadeh91
                        They are using the same model. The syntax is just different. With Qt you are running it via
                        a Automation server which VBA also does but its just more natural looking.

                        So even syntax not the same, the object its uses and often also actual structure is the same.

                        So if you know how to get a embedded chart in VBA, the same will work for Qt.

                        Therefore an understanding of the model (of Excel) is needed to use the Qt code.

                        A Offline
                        A Offline
                        alizadeh91
                        wrote on 2 Nov 2016, 14:24 last edited by alizadeh91 11 Feb 2016, 14:25
                        #13

                        @mrjj Thanks, Could u give me an example (some codes for VBA and Qt which are equivalent). I need a start point to start learning it

                        M 1 Reply Last reply 2 Nov 2016, 14:59
                        1
                        • A alizadeh91
                          2 Nov 2016, 14:24

                          @mrjj Thanks, Could u give me an example (some codes for VBA and Qt which are equivalent). I need a start point to start learning it

                          M Offline
                          M Offline
                          mrjj
                          Lifetime Qt Champion
                          wrote on 2 Nov 2016, 14:59 last edited by
                          #14

                          @alizadeh91
                          Sadly its not just a 1 to 1 convert.

                          VBA:
                          Sub charObj()
                          Dim myChartObject As ChartObject
                          Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
                          Width:=400, Height:=300)
                          End Sub

                          We can see it adds to ChartObjects of the active sheet. So we know it also has a collection and not
                          just the workbook.

                          in Qt we do
                          QAxObject *workbook = excel->querySubObject("Workbooks")->querySubObject("Add");
                          QAxObject *worksheet = workbook->querySubObject("Worksheets(1)");
                          Which gives us the sheet.

                          There should also be a way to get ActiveSheet like VBA.

                          A 1 Reply Last reply 2 Nov 2016, 15:56
                          2
                          • M mrjj
                            2 Nov 2016, 14:59

                            @alizadeh91
                            Sadly its not just a 1 to 1 convert.

                            VBA:
                            Sub charObj()
                            Dim myChartObject As ChartObject
                            Set myChartObject = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, _
                            Width:=400, Height:=300)
                            End Sub

                            We can see it adds to ChartObjects of the active sheet. So we know it also has a collection and not
                            just the workbook.

                            in Qt we do
                            QAxObject *workbook = excel->querySubObject("Workbooks")->querySubObject("Add");
                            QAxObject *worksheet = workbook->querySubObject("Worksheets(1)");
                            Which gives us the sheet.

                            There should also be a way to get ActiveSheet like VBA.

                            A Offline
                            A Offline
                            alizadeh91
                            wrote on 2 Nov 2016, 15:56 last edited by alizadeh91 11 Feb 2016, 15:57
                            #15

                            @mrjj Ok, I've got it and from this I've found that I have to write below code to add a embedded chart in a worksheet:

                            worksheet->querySubObject("ChartObjects")->querySubObject("Add")
                            

                            But it gives following error:

                            QAxBase: Error calling IDispatch member ChartObjects: Member not found
                            

                            I wish there was some examples in web but there is nothing...

                            M 1 Reply Last reply 2 Nov 2016, 15:59
                            0
                            • A alizadeh91
                              2 Nov 2016, 15:56

                              @mrjj Ok, I've got it and from this I've found that I have to write below code to add a embedded chart in a worksheet:

                              worksheet->querySubObject("ChartObjects")->querySubObject("Add")
                              

                              But it gives following error:

                              QAxBase: Error calling IDispatch member ChartObjects: Member not found
                              

                              I wish there was some examples in web but there is nothing...

                              M Offline
                              M Offline
                              mrjj
                              Lifetime Qt Champion
                              wrote on 2 Nov 2016, 15:59 last edited by
                              #16

                              @alizadeh91
                              Well

                              I had luck with
                              QAxObject* test = workbook->querySubObject("ActiveSheet");
                              QAxObject* charts = test->querySubObject("ChartObjects");
                              QList<QVariant> f; f << 200 << 400 << 200 << 200;
                              QVariant ch = charts->dynamicCall("Add(int, int, int, int)", f );

                              Added Chart to first sheet

                              Now is issue how to get the chart to set it up.

                              Not sure what Add returns.
                              IF we could get handle to the new chart, rest of code be the same and it would be embedded.

                              A 1 Reply Last reply 2 Nov 2016, 16:01
                              1
                              • M mrjj
                                2 Nov 2016, 15:59

                                @alizadeh91
                                Well

                                I had luck with
                                QAxObject* test = workbook->querySubObject("ActiveSheet");
                                QAxObject* charts = test->querySubObject("ChartObjects");
                                QList<QVariant> f; f << 200 << 400 << 200 << 200;
                                QVariant ch = charts->dynamicCall("Add(int, int, int, int)", f );

                                Added Chart to first sheet

                                Now is issue how to get the chart to set it up.

                                Not sure what Add returns.
                                IF we could get handle to the new chart, rest of code be the same and it would be embedded.

                                A Offline
                                A Offline
                                alizadeh91
                                wrote on 2 Nov 2016, 16:01 last edited by
                                #17

                                @mrjj I don't know why it gives that error that the member is not found (ChartObjects) :(

                                M 1 Reply Last reply 2 Nov 2016, 16:17
                                0
                                • A alizadeh91
                                  2 Nov 2016, 16:01

                                  @mrjj I don't know why it gives that error that the member is not found (ChartObjects) :(

                                  M Offline
                                  M Offline
                                  mrjj
                                  Lifetime Qt Champion
                                  wrote on 2 Nov 2016, 16:17 last edited by
                                  #18

                                  @alizadeh91
                                  Because it must be via ActiveSheet or else it dont know it , i think.
                                  Or it dont know Add() that way.
                                  That is the frustrating part. Finding out where it keeps the stuff you want.

                                  A 1 Reply Last reply 3 Nov 2016, 19:00
                                  1
                                  • M mrjj
                                    2 Nov 2016, 16:17

                                    @alizadeh91
                                    Because it must be via ActiveSheet or else it dont know it , i think.
                                    Or it dont know Add() that way.
                                    That is the frustrating part. Finding out where it keeps the stuff you want.

                                    A Offline
                                    A Offline
                                    alizadeh91
                                    wrote on 3 Nov 2016, 19:00 last edited by
                                    #19

                                    @mrjj I've changed the code as below and this codes works:

                                    QAxObject* excel = new QAxObject( "Excel.Application", 0 );
                                        QAxObject* workbooks = excel->querySubObject( "Workbooks" );
                                        QAxObject* workbook = workbooks->querySubObject( "Open(const QString&)", "D:\\test.xls" );
                                        QAxObject* sheets = workbook->querySubObject( "Worksheets" );
                                        QAxObject* sheet = sheets->querySubObject( "Item( int )", 1 );
                                        QAxObject* chart = sheet->querySubObject("ChartObjects(int)",1)->querySubObject("Chart");
                                    

                                    As seen, ChartObjects(int) has a member called 'Chart' and the code works. So it seems that ChartObjects is not defined by itself or something like that.

                                    1 Reply Last reply
                                    1
                                    • M Offline
                                      M Offline
                                      mrjj
                                      Lifetime Qt Champion
                                      wrote on 4 Nov 2016, 07:40 last edited by mrjj 11 Apr 2016, 07:41
                                      #20

                                      Ah so ("ChartObjects(int)",1)->querySubObject("Chart");
                                      gives the first chart.
                                      Good work.
                                      Thank you for reporting back.

                                      A 1 Reply Last reply 4 Nov 2016, 13:33
                                      1
                                      • M mrjj
                                        4 Nov 2016, 07:40

                                        Ah so ("ChartObjects(int)",1)->querySubObject("Chart");
                                        gives the first chart.
                                        Good work.
                                        Thank you for reporting back.

                                        A Offline
                                        A Offline
                                        alizadeh91
                                        wrote on 4 Nov 2016, 13:33 last edited by
                                        #21

                                        @mrjj But still having problem with adding new embedded chart!!!

                                        M 1 Reply Last reply 4 Nov 2016, 13:57
                                        0
                                        • A alizadeh91
                                          4 Nov 2016, 13:33

                                          @mrjj But still having problem with adding new embedded chart!!!

                                          M Offline
                                          M Offline
                                          mrjj
                                          Lifetime Qt Champion
                                          wrote on 4 Nov 2016, 13:57 last edited by
                                          #22

                                          @alizadeh91

                                          Did you try the test code ?.

                                          It inserts embedded for me.

                                          QAxObject* test = workbook->querySubObject("ActiveSheet");
                                          QAxObject* charts = test->querySubObject("ChartObjects");
                                          QList<QVariant> f; f << 200 << 400 << 200 << 200;
                                          charts->dynamicCall("Add(int, int, int, int)", f );

                                          I didnt check if
                                          ("ChartObjects(int)",1)->querySubObject("Chart"); will give you that.

                                          From docs, the Add should return the chart object but I could not get it to cast to
                                          QAxObject* .

                                          A 1 Reply Last reply 4 Nov 2016, 14:01
                                          3

                                          12/24

                                          2 Nov 2016, 14:22

                                          • Login

                                          • Login or register to search.
                                          12 out of 24
                                          • First post
                                            12/24
                                            Last post
                                          0
                                          • Categories
                                          • Recent
                                          • Tags
                                          • Popular
                                          • Users
                                          • Groups
                                          • Search
                                          • Get Qt Extensions
                                          • Unsolved