Export PDF file from Excel template with Qt and QAxObject

[falcon1990]

@mrjj
Hi and thank you for your answer.

One thing I didn't say.
This exportation is already done in C# with the "Microsoft.Office.Interop.Excel" and that works quite well.

In C# I use, only for the export part, this function:

Excel.Workbook.Value.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, filepath + ".pdf");

So yes, the print areas are defined in the Excel file template.

Now I have to do it in Qt and not in C# anymore.

[mrjj]

@falcon1990
Ok and with Qt, it doesn't matter if true or false. always the same result ?

Could be something with QAxObject but not sure how we can check it.

[JonB]

@falcon1990
You can do your export command from Qt with QAxObject. Somebody asked about this recently and I recall replying. Use the forum Search for ExportAsFixedFormat.

[falcon1990]

@mrjj
Yes it doesn't matter if true or false for options "IncludeDocProperties" and/or "IgnorePrintAreas". Result from "ExportAsFixedFormat" is the same...

@JonB
Thank for your answer.
You talking about that ?
"ExportAsFixedFormat" is what I use in my code as you can see in my first post on the "code" section.

workbook->dynamicCall("ExportAsFixedFormat(const QVariant&, const QVariant&, const QVariant&, const QVariant&, const QVariant&)", QVariant(0), QVariant(exportName), QVariant(0), QVariant(false), QVariant(false));

In fact it's the same result with that:

workbook->dynamicCall("ExportAsFixedFormat(int, const QString&, int, BOOL, BOOL)", 0, exportName, 0, false, false);

[JonB]

@falcon1990
Yes indeed I was referring to the post you found. Only now do I notice that you seem to have already done the work to adapt to Qt, I just noticed your

Now I have to do it in Qt and not in C# anymore.

I do not have any experience on the particular issue you mention, here and at stackoverflow. It is presumably not a Qt issue but rather an Excel/VBA one. I can only suggest that if you can achieve what you want in Excel you could try recording a macro and looking at the code.

[falcon1990]

@JonB
Has I said, the PDF file is exported with corrects data and graphic are rightly generated.
The problem I can't solve is that the option to not export what is not in the print area is taken into account. Because right now I have a chart exported as a PDF with the full table of data that takes 40 pages....
Anyway, thanks for your help. :-)

[falcon1990]

Still need help....

[JonB]

@falcon1990
I don't know how we can help you here. I don't see this has anything to do with Qt, and the peeps here are not Excel/VBA experts.

I think you should try to do whatever you are wanting inside Excel, via its VBA scripting. Then if you cannot get it to do what you want it's an Excel issue, and if you can you can show what works in VBA and we can see if there is a problem via QAxObject. Which as I say I do not think will be the case.

[falcon1990]

@JonB
Thanks for your answer once again.
I disagree with you about QAxObject, I think that is where the problem is coming from.
If it works in C#, there is no reason for the problem to come from Excel/VBA...
I will try to make a small example that I can post on this topic to show the error.

[hskoglund]

Hi, I managed to dig up some old code and cobble together an Excel print area test program:

    QStringList sl =
    {"Alpha","Bravo","Charlie","Delta","Echo","Foxtrot","Golf","Hotel","India",
     "Juliet","Kilo","Lima","Mike","November","Oscar","Papa","Qt","Romeo",
     "Sierra","Tango","Uniform","Victor","Whiskey","X-Ray","Yankee","Zulu"};

// launch Excel
    auto excel    = new QAxObject("Excel.Application");
    excel->dynamicCall("SetVisible(true)");
    auto workbook = excel->querySubObject("Workbooks")->querySubObject("Add");
    auto sheet    = workbook->querySubObject("Worksheets")->querySubObject("Item(1)");

// set some data
    int r = 0;
    for (auto s : sl)
    {
        sheet->querySubObject("Cells(int,int)",++r,1)->setProperty("Value",s);
        sheet->querySubObject("Cells(int,int)",r,2)->setProperty("Value",r);
    }

// set the print area from Juliet column 1 to Romeo column 2
    QString pa = "$A$10:$B$18";
    sheet->querySubObject("PageSetup")->setProperty("PrintArea",pa);

// check that we have print area set correctly
    qDebug() << sheet->querySubObject("PageSetup")->property("PrintArea");

// export to PDF
    bool bIgnorePrintAreas = false;  // <-- works for either true or false
    QString pdfFile = "test.pdf";
    workbook->dynamicCall("ExportAsFixedFormat(int,QString,int,int,int)",0,pdfFile,0,0,bIgnorePrintAreas);

I just tried, if I set to bIgnorePrintAreas to false I get just the Juliet to Romeo rows, and if I set it to true, I get all the 26 rows.

One pitfall as I remember it, was that the definition of the print area sometimes disappeared, you could try with my qDebug() line above to check that your print area is still defined when you load the Excel file (this could explain why you get your 40 pages regardless :-)

[falcon1990]

@hskoglund
Thank you very much for your answer, you rock !
Finaly, with the "setProperty" of "PrintArea" it works.
When I was printing the message from print area property I had a empty print area like: QVariant(QString, "").
Then when I set the print area the message change like: QVariant(QString, "$A$1:$J$53").
Also pay attention to the "ExportAsFixedFormat" parameter that for the "IgnorePrintAreas" should be to "FALSE" like this: workbook->dynamicCall("ExportAsFixedFormat(int, const QString&, int, BOOL, BOOL)", 0, exportName, 0, false, false);

[JonB]

@falcon1990 said in Export PDF file from Excel template with Qt and QAxObject:

I disagree with you about QAxObject, I think that is where the problem is coming from.

So now that you have found the issue was that you needed to do some setProperty() call, for whatever reason, as I suggested, but you disagreed, the problem is not in Qt's QAxObject. It is usually better not to assume there will be a bug in Qt's supplied code.

[falcon1990]

@JonB
It is usually better to read correctly...
Finally the problem comes from QAxObject which I didn't know I had to define the print area and doesn't come from the Excel file or the VBA script.....
You said:

• I think you should try to do whatever you are wanting inside Excel, via its VBA scripting.

And I simply said:

• If it works in C#, there is no reason for the problem to come from Excel/VBA...

So yes, I'm still disagree with you, but problem is solved.
Thank you for your help anyway.