QAxObject and Excel

[Arash_Vsh]

@hskoglund Hello, I have used your code to read an excel file by using QAxobject and it worked perfect, but in my case, the chart has many merged cells. I need to know the number of rows which a merged cell occupies. What should I do? Thank you so much.

[JonB]

@Arash_Vsh
Qt QAxObject just lets you interact with ActiveX/Excel etc. It does not document what you can do in them. You must read Excel VBA documentation/examples for any questions like this, e.g. start from https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model

[Ketan__Patel__0011]

If you want to read any excel file then use QSqlDatabase For It

my personal experience saying that QAxObject take Lot's of time for read any kind of Excel file
And QAxObject Very complicated Concept

you can see my code for Read Any Kind Of Excel File

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection");
    db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString(FilePath) + "");
    if(db.open())
        qDebug() << "Excel Is Connected";
    else
        qDebug() << "Excel Is Not Connected";

    QSqlQuery query(db);
    query.prepare("SELECT * FROM [" + QString("Sheet1") + "$]"); // Select range, place A1:B5 after $
    if(query.exec())
    {
        while (query.next())
        {
                   ///// Your Code
        }
    }

for this You must have to Install Mysql Driver For Excel

[JonB]

@Ketan__Patel__0011
If one does it this way, the issue then is where do you find details on what tables/columns/links/operations etc. can you perform?

I can see one might start by exploring down from https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-excel-driver-programming-considerations . Does that sound right, or do you have a better resource for what/how you can do to Excel across SQL/ODBC?

[Ketan__Patel__0011]

@JonB

Yes i can perform all are the operation like select Particular Columns or etc..

In My case i am using this Solution And it give me complate solution

You can see

@Ketan__Patel__0011 said in QAxObject and Excel:

query.prepare("SELECT * FROM [" + QString("Sheet1") + "$]"); // Select range, place A1:B5 after $

Here I am using "Sheet1" As My table and i am fatching All Columns data of My Table
So You can simply Get the Data from pass columns index like this "Follow My code"

    if(query.exec())
    {
        while (query.next())
        {
            QString column1= query.value(0).toString();
            QString column2 = query.value(1).toString();
            QString column3 = query.value(2).toString();
            QString column4 = query.value(3).toString();
        }
    }

[JonB]

@Ketan__Patel__0011
Yes indeed, for an example of "select some adjacent columns and fetch their values".

But have a look at, say, https://docs.microsoft.com/en-us/sql/odbc/microsoft/sqlgetinfo-returned-values-for-excel?view=sql-server-ver15. Or, how do you set up an Excel Pivot table? What about the manipulations available of all the various object types described in the VBA Excel reference? That sort of thing is what I was thinking one needs documentation for.

[Ketan__Patel__0011]

@JonB

I have not work with Some Advanced level.
So I can't explain much more for it.

but you can use this concept for read simple excel file and fatch the data from your excel sheet table.

And I Will Start the work on Excel Pivot table and other Advanced concepts.
Then i will drop the solution.

[JonB]

@Ketan__Patel__0011
Hi Ketan.
No need for you to actually find solutions to what I wrote, they are just examples of what a user might want. Your suggestion of using SQL/ODBC instead of ActiveX is interesting. All I was saying is that if the OP wishes to go down that route they might use https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-excel-driver-programming-considerations as a starting point for documentation. That was all.

[Ketan__Patel__0011]

@JonB

Thank you for your complement

I Didn't get the any message from User(newbieQTDev ) so he Actually want Solution for Excel file Reading using ActiveX Then i Will Drop the solution for it.

I have both way solution for it But SQL/ODBC Nice And easy way for Excel file reading.

And you are Agree with my Concept then Mark My Post As The Correct Answer.

[JonB]

@Ketan__Patel__0011 said in QAxObject and Excel:

Actually want Solution for Excel file Reading using ActiveX Then i Will Drop the solution for it

Even if he does want only ActiveX, do not drop your SQL/ODBC solution here! It is a very useful alternative way of going about things, depending on what is wanted. Even if it is not right for this user, it is useful to read for others coming to this topic :)

[Ketan__Patel__0011]

@JonB

So Should I Delete My Post ?

[JonB]

@Ketan__Patel__0011
Noooo!!! Not at all, I am saying your post is very useful. Maybe not to this OP, but potentially anyway to others. It is a good post :)

[ODБOï]

hi
@Ketan__Patel__0011 said in QAxObject and Excel:

So Should I Delete My Post ?

No, don't delete, i belive the OP is able to chose the right solution for what he needs

[Ketan__Patel__0011]

@LeLev

Okay i will Restore The Post

Sorry To Say But What Is Meaning Of

@LeLev said in QAxObject and Excel:

OP
?

[JonB]

@Ketan__Patel__0011
I didn't see you had actually deleted your useful post! Yes, please do restore it!

OP == "Original Poster", the person who starts a thread.

[ODБOï]

@Ketan__Patel__0011 the person who created this thread, (Original Poster )

[Ketan__Patel__0011]

@LeLev

Okay i got it Thank You

[Ketan__Patel__0011]

@JonB

I Restored it

[Morteza_Mahmoudi]

@Ketan__Patel__0011 I agree with you. AxObject is very slow especially in Save Data.

[Ketan__Patel__0011]

@Morteza_Mahmoudi

Happy To Help