wxAutoExcel

This forum is reserved for everything you want to talk about. It could be about programming, opinions, open source programs, development in general, or just cool stuff to share!
Post Reply
charles_5577
In need of some credit
In need of some credit
Posts: 7
Joined: Mon Apr 25, 2022 9:18 am

wxAutoExcel

Post by charles_5577 »

I am not sure that this is the right forum for this question. I have been using wxWidgets with CB for some years and find it very usefull. My question relates more to wxAutoExcel than wxWidgets.
I downloaded and built wxAutoExcel using a CB project. After figuring out all of the compile flags to use I was able to start using the library. For my application I want to copy alot of data into an Excel worksheet and then create a scatter plot. I was able to:

Create the wxExcelApplication, a Workbook, a Worksheet, copy data into the worksheet, create a scatter plot and change the parameters of the two primary axes. So far all is OK.

The next thing I wanted to do was plot one of the series on a secondary axis. I selected the series with
wxExcelSeries stddev = chart.FullSeriesCollection(3);
I could not find a function for changing the series "AxisGroup" property to xlSecondary.
I tried stddev.InvokePutProperty(wxS("AxisGroup"), xlSecondary) but that did not work.
Does anyone know how to do this?

The second problem was being able to change the parameters of the secondary axis.
I tried to get the axis with:
XlAxisGroup mm = xlSecondary;
axis = chart.Axes(xlValue, (XlAxisGroup *) &mm);
This did not work.
Again, does anyone know how to do this?

Any help for this will be greatly apprecated.
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Re: wxAutoExcel

Post by PB »

(I am the author of wxAutoExcel)
charles_5577 wrote: Tue Jul 05, 2022 9:07 am I am not sure that this is the right forum for this question.
The place is correct but you should have posted it in the wxCode forum section, as instructed in wxAutoExcel documentation.
charles_5577 wrote: Tue Jul 05, 2022 9:07 am I downloaded and built wxAutoExcel using a CB project. After figuring out all of the compile flags to use I was able to start using the library.
This is unfortunate, I thought I documented how to build and add wxAutoExcel to a project in detail and no "figuring out" was needed. What did you miss in the docs?
charles_5577 wrote: Tue Jul 05, 2022 9:07 am I could not find a function for changing the series "AxisGroup" property to xlSecondary.
This is because I forgot to add the setter, I will add it soon.
charles_5577 wrote: Tue Jul 05, 2022 9:07 am I tried stddev.InvokePutProperty(wxS("AxisGroup"), xlSecondary) but that did not work.
Does anyone know how to do this?
The Invoke*() methods are not meant to be used from the user code and are thus protected. You need to obtain the underlying wxAutomationObject from wxAutoExcel one if you want to manipulate it:

Code: Select all

// move series 2 to the secondary axis
wxExcelSeries                   series = chart.FullSeriesCollection(2);
wxSharedPtr<wxAutomationObject> seriesAO = series.GetAutomationObject_();

seriesAO->PutProperty("AxisGroup", xlSecondary);
It seems to work quite well:
wxautoexcel-chart-secondary-axis.png
wxautoexcel-chart-secondary-axis.png (20.82 KiB) Viewed 4880 times
The chart above was produced with (error checking mostly omitted for brevity)

Code: Select all

#include <wx/wx.h>
#include <wx/msw/ole/oleutils.h>

#include <wx/wxAutoExcel.h>

using namespace wxAutoExcel;

class MyApp : public wxApp
{
public:
    bool OnInit() override
    {
        wxExcelApplication app = wxExcelApplication::CreateInstance();
        if ( !app )
        {
            wxLogError("Failed to create an instance of MS Excel application.");
            return false;
        }
        app.SetVisible(true);

        wxExcelWorkbook  workbook = app.GetWorkbooks().Add();
        wxExcelWorksheet worksheet = workbook.GetWorksheets()[1];
        wxExcelRange     range;
        wxVariant        variant;

        // x axis values
        range = worksheet.GetRange("A1:C1");
        variant.ClearList();
        variant.Append(1);
        variant.Append(2);
        variant.Append(3);
        range = variant;

        // series 1 values
        range = worksheet.GetRange("A2:C2");
        variant.ClearList();
        variant.Append(10);
        variant.Append(11);
        variant.Append(12);
        range = variant;

        // series 2 values
        range = worksheet.GetRange("A3:C3");
        variant.ClearList();
        variant.Append(100);
        variant.Append(200);
        variant.Append(300);
        range = variant;

        // the chart will automagically use data above
        wxExcelChart chart = worksheet.GetShapes().AddChart(xlXYScatter, 1, 100, 250, 250).GetChart();
        if ( !chart )
        {
            wxLogError("Could not add chart.");
            return false;
        }

        // move series 2 to the secondary axis
        wxExcelSeries                   series = chart.FullSeriesCollection(2);
        wxSharedPtr<wxAutomationObject> seriesAO = series.GetAutomationObject_();

        seriesAO->PutProperty("AxisGroup", xlSecondary);

        // set secondary Y axis title
        wxExcelAxis      secondaryAxis = chart.Axes(xlValue, WXAEEP(xlSecondary));
        wxExcelAxisTitle axisTitle;

        secondaryAxis.SetHasTitle(true);
        axisTitle = secondaryAxis.GetAxisTitle();
        axisTitle.SetCaption("Y2");

        return false;
    };
};
wxIMPLEMENT_APP(MyApp);
charles_5577 wrote: Tue Jul 05, 2022 9:07 am The second problem was being able to change the parameters of the secondary axis.
I tried to get the axis with:
XlAxisGroup mm = xlSecondary;
axis = chart.Axes(xlValue, (XlAxisGroup *) &mm);
This did not work.
I strongly recommend you take a good look at the bundled samples, or at least the chart one. They show how to use optional enum parameters.
Anyway, you can see a code manipulating the secondary axis in the code above. Just make sure there actually is a secondary axis before you try to access it.

EDIT
I have added the missing method, so if you update wxAutoExcel to the current version, it would be possible to do just

Code: Select all

// move series 2 to the secondary axis
wxExcelSeries series = chart.FullSeriesCollection(2);

series.SetAxisGroup(xlSecondary);
as expected.
charles_5577
In need of some credit
In need of some credit
Posts: 7
Joined: Mon Apr 25, 2022 9:18 am

Re: wxAutoExcel

Post by charles_5577 »

Hello author of wxAutoExcel,

Many thanks for the reply. I will try your solutions ASAP.
You had some questions also.

I should have read the documentation further for the right forum. Very sorry.

I built the wxAutoExcel by starting a new dll project in CB. I imported all the source and header files. I used the compiler flags (options) that you recomend but also my build profile. The library easily built.

The diffucult part was getting an application to build. I finally looked at the "flags" files for compiling the samples. It turns out that the flag -DwxNO_UNSAFE_WXSTRING_CONV was necessary for building applications. Maybe I am wrong but I do not think that I saw this in the docmentation. Anyway my application built and ran. If I am wrong many applogies.

I tried to use the Invoke method out of desperation.

Finally, not being an expert at doxygen, I tried to compile the documentation. Works fine, except that the documentation for charts and similar classes are not produced. I think that this because of the options for building the libraries allow exclusion of some of these classes.

I congratulate the authors of wxWidgets and people such as yourself for writing wxAutoExcel. I think that writing exaustive documentation probably would require more time than the code itself. That is where the forum helps.

I will try your solutions tommorrow. I am sure they will work.

Looking foward to a wxAutoWord library.

Best Regards
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Re: wxAutoExcel

Post by PB »

charles_5577 wrote: Tue Jul 05, 2022 3:17 pm The diffucult part was getting an application to build. I finally looked at the "flags" files for compiling the samples. It turns out that the flag -DwxNO_UNSAFE_WXSTRING_CONV was necessary for building applications. Maybe I am wrong but I do not think that I saw this in the docmentation.
I don't think wxNO_UNSAFE_WXSTRING_CONV is necessary to use. IIRC, the define was introduced many years after I wrote wxAutoExcel. In fact, I built wxWidgets without this define, wxAutoExcel with this define and the test application without this define. All this with MSVC but it should not matter. wxNO_UNSAFE_WXSTRING_CONV should affect only the code where it is set when building, not linking. It is quite odd that you need to set it for your project.
charles_5577 wrote: Tue Jul 05, 2022 3:17 pm Finally, not being an expert at doxygen, I tried to compile the documentation. Works fine, except that the documentation for charts and similar classes are not produced. I think that this because of the options for building the libraries allow exclusion of some of these classes.
I am no expert on doxygen (or anything else, actually) myself as proven by those classes missing from the documentation, caused by me setting the defines in Doxyfile incorrectly. I actually noticed this after all these years just now and fixed it barely half an hour ago: Better late than never. For example, here is the wxExcelSeries::SetAxisGroup() documentation, perhaps not very useful but better than nothing.
charles_5577 wrote: Tue Jul 05, 2022 3:17 pm Looking foward to a wxAutoWord library.
I am certainly not the one to write it. IMO, Excel is by far the most automated application, so that is why there is a bunch of libraries automating it (or reading/writing its files). You will probably need to use wxAutomationObject directly, as shown e.g. here: viewtopic.php?p=172845#p172845

EDIT
I built wxAutoExcel as 64-bit Debug DLL using CMake-generated GCC (v12.1) makefile. I then generated Code::Blocks project with its wxWidgets template wizard (using wxWidgets 3.2) and kept only a single .cpp file in the project, the content of which I replaced with the code from wxAutoExcel chart sample. I then added wxAutoExcel to a project, setting only the compiler include and linker paths and adding the library to the library list.

The build succeeded, no wxNO_UNSAFE_WXSTRING_CONV needed:
-------------- Build: Debug in test-wxAutoExcel (compiler: msys2-mingw-w64-x86_64)---------------

windres.exe -ID:\Dev\Desktop\!Lib\wxWidgets-GIT\include -ID:\Dev\Desktop\!Lib\wxWidgets-GIT\lib\gcc_dll\mswud -DwxUSE_DPI_AWARE_MANIFEST=2 -J rc -O coff -i C:\dev\cb-tests\TEST-W~2\resource.rc -o obj\Debug\resource.res
g++.exe -pipe -mthreads -D__GNUWIN32__ -D__WXMSW__ -DWXUSINGDLL -DwxUSE_UNICODE -Wall -g -D__WXDEBUG__ -ID:\Dev\Desktop\!Lib\wxWidgets-GIT\include -ID:\Dev\Desktop\!Lib\wxWidgets-GIT\lib\gcc_dll\mswud -ID:\Dev\Desktop\!Lib\wxAutoExcel\include -c C:\dev\cb-tests\test-wxAutoExcel\test_wxAutoExcelApp.cpp -o obj\Debug\test_wxAutoExcelApp.o
g++.exe -LD:\Dev\Desktop\!Lib\wxWidgets-GIT\lib\gcc_dll -LD:\Dev\Desktop\!Lib\wxAutoExcel\lib\gcc_dll -o bin\Debug\test-wxAutoExcel.exe obj\Debug\test_wxAutoExcelApp.o obj\Debug\resource.res -mthreads -lwxAutoExcel20d -lwxmsw32ud_core -lwxbase32ud -mwindows
Output file is bin\Debug\test-wxAutoExcel.exe with size 1.11 MB
Process terminated with status 0 (0 minute(s), 4 second(s))
0 error(s), 0 warning(s) (0 minute(s), 4 second(s))
charles_5577
In need of some credit
In need of some credit
Posts: 7
Joined: Mon Apr 25, 2022 9:18 am

Re: wxAutoExcel

Post by charles_5577 »

Hi PB,
I believe everthing works fine now. It is no longer a programming problem now but rather finding the the right class, method or property to do what I want. Thanks for the help.
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Re: wxAutoExcel

Post by PB »

charles_5577 wrote: Sat Jul 09, 2022 9:13 am rather finding the the right class, method or property to do what I want.
I still recommend what I wrote in the docs: Use the Excel macro recorder to see what code matches user actions and then "translate" the resulting VBA into wxAutoExcel calls.
charles_5577
In need of some credit
In need of some credit
Posts: 7
Joined: Mon Apr 25, 2022 9:18 am

Re: wxAutoExcel

Post by charles_5577 »

Just to let you know. I now have everything for wxAutoExcel that I need. This automation saves me hours for writing reports for my clients.

As mentioned it would be nice to have a wxAutoWord. I have started trying to automate Word using the wxAutomationObject and wxVariant classes but have run ino some difficulties. I think I will start a new topic on this.
PB
Part Of The Furniture
Part Of The Furniture
Posts: 4193
Joined: Sun Jan 03, 2010 5:45 pm

Re: wxAutoExcel

Post by PB »

In hindsight, I think using wxAutomationObject for wxAutoExcel was a mistake.

It allowed a quicker start but the API is somewhat limited, for example, there is no way to use methods with parameters passed by reference (TBH, I run only into few such methods) or propagate the actual error to the user code.
Post Reply