Export PDF file from Excel template with Qt and QAxObject

falcon1990

The project I am currently working on is to export an Excel file to PDF.

The Excel file is a "Template" that allows the generation of graphs. The goal is to fill some cells of the Excel file so that the graphs are generated and then to export the file in PDF.

I use Qt in C++ with the QAxObject class and all the data writing process works well but it's the PDF export part that doesn't.

The problem is that the generated PDF file also contains the data of the graphs while these data are not included in the print area of the Excel template.

The PDF export is done with the "ExportAsFixedFormat" function which has as a parameter the possibility to ignore the print area that is "IgnorePrintAreas" at position 5. Even if I decide to set this parameter to "false", so not to ignore the print area and therefore to take into account the print area, this does not solve the problem and it produces the same result as if this parameter was set to "true".

I tried to vary the other parameters, to change the type of data passed in parameter or not to use any parameter but it does not change anything to the obtained result which is always the same.

Here is the link to the "documentation" of the export command "ExportAsFixedFormat": https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.exportasfixedformat

I give you a simplified version of the command suite that is executed in the code:

Rapport::Rapport(QObject *parent) : QObject(parent)
{
        //Create the template from excel file
        QString pathTemplate = "/ReportTemplate_FR.xlsx"
        QString pathReporter = "/Report"
        this->path = QDir(QDir::currentPath() + pathReporter + pathTemplate);
        QString pathAbsolute(this->path.absolutePath().replace("/", "\\\\")); 

        //Create the output pdf file path
        fileName = QString("_" + QDateTime::currentDateTime().toString("yyyyMMdd-HHmmssff") + "_Report");
        QString pathDocument = QStandardPaths::writableLocation(QStandardPaths::DocumentsLocation).append("/").replace("/", "\\\\");
        QString exportName(pathDocument + fileName + ".pdf");

        //Create the QAxObjet that is linked to the excel template
        this->excel = new QAxObject("Excel.Application");

        //Create the QAxObject « sheet » who can accepte measure data
        QAxObject* workbooks = this->excel->querySubObject("Workbooks");
        QAxObject* workbook = workbooks->querySubObject("Add(const QString&)", pathAbsolute);
        QAxObject* sheets = workbook->querySubObject("Worksheets");
        QAxObject* sheet = sheets->querySubObject("Item(int)", 3);

        //Get some data measure to a list of Inner class Measurement
        QList<Measurement*> actuMeasure = this->getSomeMeasure() ; //no need to know how it’s work…

        //Create a 2 dimentional QVector to be able to place data on the table where we want (specific index) 
        QVector<QVector<QVariant>> vCells(actuMeasure.size());
        for(int i = 0; i < vCells.size(); i++)
                vCells[i].resize(6);

        //Fill the 2 dimentional QVector with data measure
        int row = 0;
        foreach(Measurement* m, actuMeasure)
        {
                vCells[row][0] = QVariant(m->x);
                vCells[row][1] = QVariant(m->y1);
                vCells[row][2] = QVariant(m->y2);
                vCells[row][3] = QVariant(m->y3);
                vCells[row][4] = QVariant(m->y4);
                vCells[row][5] = QVariant(m->y5);
                row++;
        }

        //Transform the 2 dimentional QVector on a QVariant object
        QVector<QVariant> vvars;
        QVariant var;
        for(int i = 0; i < actuMeasure.size(); i++)
                vvars.append(QVariant(vCells[i].toList()));
        var = QVariant(vvars.toList());

        //Set the QVariant object that is the data measure on the excel file
        sheet->querySubObject("Range(QString)", "M2:AB501")->setProperty("Value", var);

        //Set the fileName on the page setup (not relevant for this example)
        sheet->querySubObject("PageSetup")->setProperty("LeftFooter", QVariant(fileName));

        //Export to PDF file with options – NOT WORKING !!!
        workbook->dynamicCall("ExportAsFixedFormat(const QVariant&, const QVariant&, const QVariant&, const QVariant&, const QVariant&)", QVariant(0), QVariant(exportName), QVariant(0), QVariant(false), QVariant(false));

        //Close
        workbooks->dynamicCall("Close()");
        this->excel->dynamicCall("Quit()");
}

I already asked my question on stackoverflow but no answer:

A this point I really need help to find a way to solve this problem.

I also wonder if this is not a bug of the QAxObject class.

mrjj

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

I also wonder if this is not a bug of the QAxObject class.

Hi
you could try manually export from Excel using that function and see if it then works.

Also docs says " If set to False, uses the print areas set when publishing."

but did you set any print areas ? In the sheet.

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.