Why export excel in windows10 normal, in windows7 export will crash?

mrjj

Hi
Could be memory or other constraints. Could be platform differences
or even excel versions.
Without code, or a description of what you have, what you do, etc its
close to prophesy level to try to guess why your app crashes. 😃

duncan98

@mrjj

QJsonObject data_object=post_to_object("instore/get_page");
QJsonArray data_array=data_object.value("list").toArray();
QAxObject *excel = new QAxObject("Excel.Application");
    excel->setProperty("Visible", false);
    QAxObject *workbooks=excel->querySubObject("WorkBooks");
    workbooks->dynamicCall("Add"); // Add new workbook
    QAxObject *workbook=excel->querySubObject("ActiveWorkBook");
    QAxObject* sheets=workbook->querySubObject("WorkSheets");

    QAxObject *sheet = sheets->querySubObject( "Item( int )",1);
    QAxObject *cell_title = sheet->querySubObject( "Cells( int, int )", 1,1);

    cell_title->dynamicCall("SetValue(QString)","title");

    int column=0;
    QAxObject *header_1 = sheet->querySubObject( "Cells( int, int )", 2,++column);
    header_1->dynamicCall("SetValue(QString)","sn");

    QAxObject *header_2 = sheet->querySubObject( "Cells( int, int )", 2,++column);
	header_2->dynamicCall("SetValue(QString)","supplier");

    QAxObject *header_3 = sheet->querySubObject( "Cells( int, int )", 2,++column);
    header_3->dynamicCall("SetValue(QString)","operater");

    QAxObject *header_4 = sheet->querySubObject( "Cells( int, int )", 2,++column);
    header_4->dynamicCall("SetValue(QString)","writer");

    QAxObject *header_5 = sheet->querySubObject( "Cells( int, int )", 2,++column);
    header_5->dynamicCall("SetValue(QString)","total");

    QAxObject *header_6 = sheet->querySubObject( "Cells( int, int )", 2,++column);
    header_6->dynamicCall("SetValue(QString)","remak");

    QJsonObject obj_row;
    for(int i=0;i<data_array.size();i++){
        obj_row=data_array.at(i).toObject();
        int column=0;
        QAxObject *cell_1=nullptr;QAxObject *cell_2=nullptr;QAxObject *cell_3=nullptr;QAxObject *cell_4=nullptr;
        QAxObject *cell_5=nullptr;QAxObject *cell_6=nullptr;QAxObject *cell_7=nullptr;QAxObject *cell_8=nullptr;
        cell_1 = sheet->querySubObject( "Cells( int, int )", i+3,++column);//1
        cell_1->dynamicCall("SetValue(QString)",obj_row.value("sn").toString());

		cell_2 = sheet->querySubObject( "Cells( int, int )", i+3,++column);//2
		cell_2->dynamicCall("SetValue(QString)",obj_row.value("supplier").toString());

        cell_3 = sheet->querySubObject( "Cells( int, int )",i+3,++column);//3
        cell_3->dynamicCall("SetValue(QString)",obj_row.value("operater").toString());
        cell_4 = sheet->querySubObject( "Cells( int, int )",i+3,++column);//4
        cell_4->dynamicCall("SetValue(QString)",obj_row.value("writer").toString());
        cell_5 = sheet->querySubObject( "Cells( int, int )",i+3,++column);//5
        cell_5->dynamicCall("SetValue(QString)",obj_row.value("total").toString());
        cell_6 = sheet->querySubObject( "Cells( int, int )",i+3,++column);//6
        cell_6->dynamicCall("SetValue(QString)",obj_row.value("remark").toString());
    }


    QAxObject *merge_range = sheet->querySubObject("Range(const QString&)", "A1:G1");
    merge_range->setProperty("HorizontalAlignment", -4108);
    merge_range->setProperty("VerticalAlignment", -4108);
    merge_range->setProperty("WrapText", true);
    merge_range->setProperty("MergeCells", true);

    QAxObject *used_range = sheet->querySubObject("UsedRange");
    QAxObject *rows = used_range->querySubObject("Rows");
    QString row_count = rows->property("Count").toString();


    QAxObject *columns = used_range->querySubObject("Columns");
    columns->dynamicCall("AutoFit");


    QAxObject *border = used_range->querySubObject("Borders");
    border->setProperty("Color", QColor(111,156,207));

    QAxObject *range_header = sheet->querySubObject("Range(const QString&)", "A2:G2");
    QAxObject *interior_header = range_header->querySubObject("Interior");
    interior_header->setProperty("Color", QColor(224,238,255));
    QAxObject *font_header = range_header->querySubObject("Font");
    font_header->setProperty("Color", QColor(0,78,161));

    QString file_name="instore";
    QString file_path=QFileDialog::getSaveFileName(nullptr,"save file",file_name,"(*.xlsx);;(*.xls);;(*.csv)");
    file_path.replace("/","\\");
    workbook->dynamicCall("SaveAs(QString&)",file_path);
    workbook->dynamicCall("Close (Boolean)",false);
    excel->dynamicCall("Quit(void)");

mrjj

@duncan98
Is it same excel version?
My guess would be on that so some of the calls fails and then being used.

Can you debug it on win 7 32 bit ?

else you need to use other methods to find out WHERE it crashes, in which line.

duncan98

@mrjj
I mean, excel crashed when I was trying to save it. It's not open yet

mrjj

@duncan98
you mean this part ?
QString file_path=QFileDialog::getSaveFileName(nullptr,"save file",file_name,"(.xlsx);;(.xls);;(*.csv)");

duncan98

@mrjj
In debugging, I found the same code that workbooks created in windows10 but not in windows7

JonB

@duncan98
Since QAxObject is only a thin layer over accessing ActiveX objects, I would look in the Excel VBA model for differences rather than in Qt. Did you try your code from within Excel macros/VBA to see how it works quite outside Qt?

duncan98

@JonB
I have checked that the vba syntax is correct, otherwise it will not succeed in windows10, but I don't know if other components are needed in windows7

duncan98

The application output shows:
QAxBase::setControl: requested control Excel.Application could not be instantiated
QAxBase::dynamicCallHelper: Object is not initialized, or initialization failed

JonB

@duncan98 said in Why export excel in windows10 normal, in windows7 export will crash?:

QAxBase::setControl: requested control Excel.Application could not be instantiated

This is the error message which matters. It indicates Excel.Application could not be instantiated.

You can Google for QAxBase::setControl: requested control Excel.Application could not be instantiated to see others reporting similar. One suggestion is:

The control requested in QAxBase::setControl() is not installed on this system, or is not accessible for the current user.

The control might require administrator rights, or a license key. If the control is licensed, pass the license key to QAxBase::setControl as documented.

Your issue seems to be reported in https://stackoverflow.com/questions/67901747/qaxwidget-is-in-use-and-an-error-occurs-when-reading-excel-in-qt-creator, I don't know whether you are the author of that question.

One small thought is: how do you create/what kind of Qt application?

You might try finding and passing the desired GUID/CLSID in place of Excel.Application, just in case.

I believe PowerShell allows you to do Automation? I would try that, or something similar, to verify you can connect successfully from a non-Qt application on the failing machine.

duncan98

@JonB
The problem has been found. However, it cannot be initialized without software installed, which is more limited

mrjj

@duncan98
So the reason it was not working was that you did not have excel installed ?

duncan98

@mrjj
Yes, this problem is very strange. You must install Microsoft Excel, but you can't install other Excel software

mrjj

@duncan98
Well its clear it needs Excel.
But what do you mean by
"l, but you can't install other Excel software"

You mean rest of the office or what other software ?

duncan98

@mrjj
I mean you can't initialize it with any other Excel processor installed.
Can you take a look at this for me? Thank you
https://forum.qt.io/topic/128391

mrjj

@duncan98
So you mean with another version of Excel installed, the same code no longer works?

duncan98

@mrjj
I haven't tried this

mrjj

@duncan98
Ehh
So what did you mena by "I mean you can't initialize it with any other Excel processor installed."

other Excel processor = `????

JonB

@duncan98 said in Why export excel in windows10 normal, in windows7 export will crash?:

Yes, this problem is very strange. You must install Microsoft Excel

To use ActiveX of course you have to have the target application installed! It would help if you had mentioned this, the problem has nothing to do with Windows 7 vs Windows 10 and would have shown up if you had tried communicating with it from any other non-Qt application, as I suggested you do.

duncan98

@JonB
Can this Excel component be referenced independently? I want to package it into the installer