Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. Qt & Excel xlsx outputs
Forum Updated to NodeBB v4.3 + New Features

Qt & Excel xlsx outputs

Scheduled Pinned Locked Moved Solved General and Desktop
excelqt5.15.1xlsxqsqlerrorsetdatabasename
25 Posts 4 Posters 7.3k Views
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J JonB
    21 Jan 2021, 11:13

    @Touchoco
    Maybe you still get the error message for another reason, but that does not alter the fact that string you had/have is wrong. So I don't see how you will get anywhere at all with an incorrect literal string. I said that was where to start from.

    Where did you get your exact proposed Excel/ODBC connection string from? Yours appears to be for Microsoft Excel 2007 ODBC Driver. I would at least try a more recent one, e.g. from https://www.connectionstrings.com/excel/ ?

    T Offline
    T Offline
    Touchoco
    wrote on 21 Jan 2021, 14:29 last edited by Touchoco
    #7

    @JonB The page looked interesting, but I still can't figure it out. I tried with xls or xlsx, Dbq or Excel file, ...

    Result :
    QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Nom de source de données trop long")
    "Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;"

    Code :
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    //QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;");
    QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Excel File=D:\sabin\Documents\test.xls;");
    db.setDatabaseName(connectionString);
    if(db.open())
    {
    //Not happening any time soon...
    }
    else
    {
    qDebug()<<db.lastError();
    qDebug() << connectionString ;
    }

    J 1 Reply Last reply 21 Jan 2021, 14:50
    0
    • T Touchoco
      21 Jan 2021, 14:29

      @JonB The page looked interesting, but I still can't figure it out. I tried with xls or xlsx, Dbq or Excel file, ...

      Result :
      QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Nom de source de données trop long")
      "Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;"

      Code :
      QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
      //QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Dbq=D:\sabin\Documents\test.xls;");
      QString connectionString("Driver = {Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};Excel File=D:\sabin\Documents\test.xls;");
      db.setDatabaseName(connectionString);
      if(db.open())
      {
      //Not happening any time soon...
      }
      else
      {
      qDebug()<<db.lastError();
      qDebug() << connectionString ;
      }

      J Offline
      J Offline
      JonB
      wrote on 21 Jan 2021, 14:50 last edited by
      #8

      @Touchoco
      Whether it is the issue, as I have already said your C++ literal string is wrong, just as before. Please read up on C++. There is no point my keep telling you the same thing and you do not act on it, so I leave you to it.

      T 1 Reply Last reply 21 Jan 2021, 16:08
      0
      • T Touchoco
        20 Jan 2021, 08:00

        Hello,

        I need to generate Excel output file. Those xlsx file will be used to prove user's activities. I would like to :
        1- Open a template
        2 - Save the document with a new name
        3 - Change values
        4 - Save the document

        So far, I don't undestrand why I can't access the template file. I get the following error : "QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Nom de source de données trop long")".

        I tried a lot of different things after researching in forums. But nothing works. I still get the same error message or worse (driver not available). What did I miss ? How can I manage this output ?

        Thanks for your help.

        Here is my code :

        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
        //db.setDatabaseName("DRIVER={Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};DBQ=" + QString ("C:\Users\User\Desktop\file.xlsx") );
        //QString currentPath(QDir::currentPath());
        //qDebug() << currentPath;
        //db.setDatabaseName("Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)};DBQ=" + QString ("C:\Users\User\Desktop\file.xlsx;Trusted_Connection=Yes;"));
        //DBQ=D:/sabin/Documents/file.xlsx
        //db.setDatabaseName("Microsoft Excel Driver (
        .xls, .xlsx, .xlsm, .xlsb);FIL=Excel 12.0;DBQ=C:\Users\User\Desktop\file.xlsx");
        //db.setDatabaseName("DRIVER={Microsoft Excel Driver (
        .xls,
        .xlsx,
        .xlsm,
        .xlsb)};DBQ=" + QString("C:\Users\User\Desktop\file.xlsx"));
        db.setDatabaseName("{Microsoft Excel Driver (
        .xls,
        .xlsx,.xlsm,.xlsb)};DBQ=" + QString("C:\Users\User\Desktop\file.xlsx"));
        if(db.open())
        {
        QSqlQuery query("select * from [" + QString("Infos_adm") + "$A1:B10]");

            while (query.next())
            {
                //reading columns
                QString column1= query.value(0).toString();
                qDebug()<<column1;
            }
            db.close();
            QString dbConnectionName(db.connectionName());
            db.~QSqlDatabase();
            QSqlDatabase::removeDatabase(dbConnectionName);
        }
        else
        {
            qDebug()<<db.lastError();
            qDebug() << "DRIVER={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};DBQ=" + QString ("C:\\Users\\User\\Desktop\\file.xlsx") ;
        }
        

        Setup :

        • Qt Creator 4.12.4
        • Qt 5.15.1
        • Excel 2016
        • compiler Desktop Qt 5.15.1 MinGW 64_bit
        • c++11
        J Offline
        J Offline
        J.Hilk
        Moderators
        wrote on 21 Jan 2021, 14:52 last edited by
        #9

        @Touchoco are you aware of this wiki page?

        https://wiki.qt.io/Handling_Microsoft_Excel_file_format

        contains all you need to know + examples


        Be aware of the Qt Code of Conduct, when posting : https://forum.qt.io/topic/113070/qt-code-of-conduct


        Q: What's that?
        A: It's blue light.
        Q: What does it do?
        A: It turns blue.

        T 1 Reply Last reply 21 Jan 2021, 16:33
        1
        • J JonB
          21 Jan 2021, 14:50

          @Touchoco
          Whether it is the issue, as I have already said your C++ literal string is wrong, just as before. Please read up on C++. There is no point my keep telling you the same thing and you do not act on it, so I leave you to it.

          T Offline
          T Offline
          Touchoco
          wrote on 21 Jan 2021, 16:08 last edited by
          #10

          @JonB Maybe because I don't see what's wrong in it. We all begin one day. I tried a set of thing and keep what works. In this case nothing.

          1 Reply Last reply
          0
          • J J.Hilk
            21 Jan 2021, 14:52

            @Touchoco are you aware of this wiki page?

            https://wiki.qt.io/Handling_Microsoft_Excel_file_format

            contains all you need to know + examples

            T Offline
            T Offline
            Touchoco
            wrote on 21 Jan 2021, 16:33 last edited by
            #11

            @J-Hilk Thank you for your answer. Yes, that's one of the first I got. It is still in my browser favorites.

            I had to adapt it to avoid this error message :
            QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long and driver name not specified")

            Now I got this error message :
            QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

            One problem solved, one left to find in :

            QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
            QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));
            db.setDatabaseName(connectionString);
            

            Other sources I found :
            Helper byte
            QT Excel - Open to Read/Write
            Ouvrir et communiquer avec Excel dans mon application Qt
            Handling Microsoft Excel file format
            Using ActiveX Object in Qt

            J 1 Reply Last reply 21 Jan 2021, 17:29
            0
            • T Touchoco
              21 Jan 2021, 16:33

              @J-Hilk Thank you for your answer. Yes, that's one of the first I got. It is still in my browser favorites.

              I had to adapt it to avoid this error message :
              QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long and driver name not specified")

              Now I got this error message :
              QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

              One problem solved, one left to find in :

              QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
              QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));
              db.setDatabaseName(connectionString);
              

              Other sources I found :
              Helper byte
              QT Excel - Open to Read/Write
              Ouvrir et communiquer avec Excel dans mon application Qt
              Handling Microsoft Excel file format
              Using ActiveX Object in Qt

              J Offline
              J Offline
              JonB
              wrote on 21 Jan 2021, 17:29 last edited by JonB
              #12

              @Touchoco said in Qt & Excel xlsx outputs:

              QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));

              OK, at last you have a legitimate C++ literal string; like you had back in your original code in the qDebug() statement :)

              I don't know what you're doing wrong, or why that error message, which is obviously the clue. I've looked around but not getting any ideas. I don't think any of your reference links will help.

              Which isn't very helpful :( FWIW, I'd chop the filename off completely, i.e. try just

              QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");
              

              and then open() it. I know it's lacking a filename and will fail, but I'm interested in what error message you get for this one?

              Then I'd try the shortest file path:

              QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
              

              It doesn't even matter too much whether you make that file exist (though if you can make it, great [upon reflection, do make it so the file exists, just in case]), at this point I think the error message you're getting about "Datasource name too long" is where the problem is.

              BTW, you have installed the ODBC driver, haven't you? You can check which are installed under Windows.

              T 1 Reply Last reply 21 Jan 2021, 18:35
              0
              • J JonB
                21 Jan 2021, 17:29

                @Touchoco said in Qt & Excel xlsx outputs:

                QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\sabin\\Documents\\test.xls"));

                OK, at last you have a legitimate C++ literal string; like you had back in your original code in the qDebug() statement :)

                I don't know what you're doing wrong, or why that error message, which is obviously the clue. I've looked around but not getting any ideas. I don't think any of your reference links will help.

                Which isn't very helpful :( FWIW, I'd chop the filename off completely, i.e. try just

                QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");
                

                and then open() it. I know it's lacking a filename and will fail, but I'm interested in what error message you get for this one?

                Then I'd try the shortest file path:

                QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                

                It doesn't even matter too much whether you make that file exist (though if you can make it, great [upon reflection, do make it so the file exists, just in case]), at this point I think the error message you're getting about "Datasource name too long" is where the problem is.

                BTW, you have installed the ODBC driver, haven't you? You can check which are installed under Windows.

                T Offline
                T Offline
                Touchoco
                wrote on 21 Jan 2021, 18:35 last edited by
                #13

                @JonB

                I tried :

                 QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");
                

                The error message is interesting, still the same : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                I also tried with :

                QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                

                Error message : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                The ODBC driver is working for my SQL request on SQL Server :

                QSqlDatabase myDataBase = QSqlDatabase::addDatabase("QODBC");
                db.setDatabaseName(QString("DRIVER={SQL Server Native Client 11.0};SERVER=lpc:COMPUTERNAME\\SQLEXPRESS;Database=database_name;Trusted_Connection=Yes;"));
                myDataBase.setUserName("username");
                myDataBase.setPassword("userpassword");
                
                J 1 Reply Last reply 21 Jan 2021, 18:39
                0
                • T Touchoco
                  21 Jan 2021, 18:35

                  @JonB

                  I tried :

                   QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)}");
                  

                  The error message is interesting, still the same : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                  I also tried with :

                  QString connectionString("Driver = {Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                  

                  Error message : QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                  The ODBC driver is working for my SQL request on SQL Server :

                  QSqlDatabase myDataBase = QSqlDatabase::addDatabase("QODBC");
                  db.setDatabaseName(QString("DRIVER={SQL Server Native Client 11.0};SERVER=lpc:COMPUTERNAME\\SQLEXPRESS;Database=database_name;Trusted_Connection=Yes;"));
                  myDataBase.setUserName("username");
                  myDataBase.setPassword("userpassword");
                  
                  J Offline
                  J Offline
                  J.Hilk
                  Moderators
                  wrote on 21 Jan 2021, 18:39 last edited by
                  #14

                  @Touchoco stupid question, but important

                  you do not have that file open with excel, while you try to open it via Qt, right ?


                  Be aware of the Qt Code of Conduct, when posting : https://forum.qt.io/topic/113070/qt-code-of-conduct


                  Q: What's that?
                  A: It's blue light.
                  Q: What does it do?
                  A: It turns blue.

                  J T 2 Replies Last reply 21 Jan 2021, 18:44
                  1
                  • J J.Hilk
                    21 Jan 2021, 18:39

                    @Touchoco stupid question, but important

                    you do not have that file open with excel, while you try to open it via Qt, right ?

                    J Offline
                    J Offline
                    JonB
                    wrote on 21 Jan 2021, 18:44 last edited by JonB
                    #15

                    @J-Hilk
                    Remember that he gets "data source too long" even without specifying a file.

                    @Touchoco
                    What does, say, QString connectionString("Driver={Microsoft Excel Driver}"); produce?

                    P.S. This is a 100% longshot, but there's no example in the world which has the spaces you have in Driver = {. Try Driver={... just in case....

                    T 1 Reply Last reply 22 Jan 2021, 08:29
                    0
                    • J J.Hilk
                      21 Jan 2021, 18:39

                      @Touchoco stupid question, but important

                      you do not have that file open with excel, while you try to open it via Qt, right ?

                      T Offline
                      T Offline
                      Touchoco
                      wrote on 22 Jan 2021, 08:17 last edited by
                      #16

                      @J-Hilk I closed all Excel files to be sure. That's not a stupid question ^^

                      J 1 Reply Last reply 22 Jan 2021, 08:20
                      0
                      • T Touchoco
                        22 Jan 2021, 08:17

                        @J-Hilk I closed all Excel files to be sure. That's not a stupid question ^^

                        J Offline
                        J Offline
                        JonB
                        wrote on 22 Jan 2021, 08:20 last edited by JonB
                        #17

                        @Touchoco
                        Did you try my suggestion of Driver={...? I am wondering whether the space you have after the = is causing the parser not to respect your { starter for the { ... } data source name...?

                        1 Reply Last reply
                        0
                        • J JonB
                          21 Jan 2021, 18:44

                          @J-Hilk
                          Remember that he gets "data source too long" even without specifying a file.

                          @Touchoco
                          What does, say, QString connectionString("Driver={Microsoft Excel Driver}"); produce?

                          P.S. This is a 100% longshot, but there's no example in the world which has the spaces you have in Driver = {. Try Driver={... just in case....

                          T Offline
                          T Offline
                          Touchoco
                          wrote on 22 Jan 2021, 08:29 last edited by
                          #18

                          @JonB

                          I got differents messages by deplacing the space caracter.

                          With :

                          QString connectionString("Driver={Microsoft Excel Driver}");
                          

                          or

                          QString connectionString("Driver={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                          

                          or

                          QString connectionString("Driver={Microsoft Excel Driver(*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                          

                          or

                          QString connectionString("Driver={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                          

                          or

                          QString connectionString("Driver ={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                          

                          or

                          QString connectionString("Driver= {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                          

                          I got :
                          QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Data source not found and driver name not specified")

                          With :

                          QString connectionString("Driver = {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                          

                          I got :
                          QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                          todelete.png

                          J 1 Reply Last reply 22 Jan 2021, 08:44
                          0
                          • C Offline
                            C Offline
                            Christian Ehrlicher
                            Lifetime Qt Champion
                            wrote on 22 Jan 2021, 08:42 last edited by
                            #19

                            I assume you're using a 64Bit Qt - so do you also have installed a 64Bit Excel ODBC driver? You can check this by opening odbcad32.exe from <WinDir>\System32\ and take a look at your User DSNs if you can configure the Excel driver there.

                            Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                            Visit the Qt Academy at https://academy.qt.io/catalog

                            T 1 Reply Last reply 25 Jan 2021, 15:07
                            2
                            • T Touchoco
                              22 Jan 2021, 08:29

                              @JonB

                              I got differents messages by deplacing the space caracter.

                              With :

                              QString connectionString("Driver={Microsoft Excel Driver}");
                              

                              or

                              QString connectionString("Driver={Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                              

                              or

                              QString connectionString("Driver={Microsoft Excel Driver(*.xls,*.xlsx,*.xlsm,*.xlsb)};Dbq=" + QString("D:\\test.xls"));
                              

                              or

                              QString connectionString("Driver={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                              

                              or

                              QString connectionString("Driver ={Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                              

                              or

                              QString connectionString("Driver= {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                              

                              I got :
                              QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Data source not found and driver name not specified")

                              With :

                              QString connectionString("Driver = {Microsoft Excel Driver};Dbq=" + QString("D:\\test.xls"));
                              

                              I got :
                              QSqlError("0", "QODBC3: Unable to connect", "[Microsoft][Gestionnaire de pilotes ODBC] Datasource name too long")

                              todelete.png

                              J Offline
                              J Offline
                              JonB
                              wrote on 22 Jan 2021, 08:44 last edited by JonB
                              #20

                              @Touchoco
                              Well, I'm not 100%, but I do think we have found some improvement/difference. Only with extra spaces do you get Datasource name too long. Let's not do that, as none of the examples do, and nor does your working Native Client one.

                              The referenced pages gives:

                              To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :

                              QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection"); db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx")); if(db.open()) {

                              So, FWIW, let's stick to exactly that string, no changing in spacing or capitalization.

                              Meanwhile, I see @Christian-Ehrlicher has queried that you verify your ODBC Excel driver is working. I wonder whether you do not have this set up right, e.g. 32-bit only when you're using 64-bit. Like I said earlier you can check this from Windows, please follow what he has said about how to do that.

                              J T 2 Replies Last reply 22 Jan 2021, 08:55
                              2
                              • J JonB
                                22 Jan 2021, 08:44

                                @Touchoco
                                Well, I'm not 100%, but I do think we have found some improvement/difference. Only with extra spaces do you get Datasource name too long. Let's not do that, as none of the examples do, and nor does your working Native Client one.

                                The referenced pages gives:

                                To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :

                                QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection"); db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx")); if(db.open()) {

                                So, FWIW, let's stick to exactly that string, no changing in spacing or capitalization.

                                Meanwhile, I see @Christian-Ehrlicher has queried that you verify your ODBC Excel driver is working. I wonder whether you do not have this set up right, e.g. 32-bit only when you're using 64-bit. Like I said earlier you can check this from Windows, please follow what he has said about how to do that.

                                J Offline
                                J Offline
                                J.Hilk
                                Moderators
                                wrote on 22 Jan 2021, 08:55 last edited by
                                #21

                                @JonB your right

                                the issue is probably the use of Dbq instead of DBQ

                                A wokring database name from one of my projects:

                                db_XL.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + path );
                                

                                Be aware of the Qt Code of Conduct, when posting : https://forum.qt.io/topic/113070/qt-code-of-conduct


                                Q: What's that?
                                A: It's blue light.
                                Q: What does it do?
                                A: It turns blue.

                                1 Reply Last reply
                                0
                                • C Christian Ehrlicher
                                  22 Jan 2021, 08:42

                                  I assume you're using a 64Bit Qt - so do you also have installed a 64Bit Excel ODBC driver? You can check this by opening odbcad32.exe from <WinDir>\System32\ and take a look at your User DSNs if you can configure the Excel driver there.

                                  T Offline
                                  T Offline
                                  Touchoco
                                  wrote on 25 Jan 2021, 15:07 last edited by
                                  #22

                                  @Christian-Ehrlicher thank you for your help.

                                  I got the following screen but I don't understang how I could configure the Excel driver there.

                                  todelete2.png

                                  I also tried to compile my program with MinGw 32 bit : the error remain unchanged.

                                  1 Reply Last reply
                                  0
                                  • J JonB
                                    22 Jan 2021, 08:44

                                    @Touchoco
                                    Well, I'm not 100%, but I do think we have found some improvement/difference. Only with extra spaces do you get Datasource name too long. Let's not do that, as none of the examples do, and nor does your working Native Client one.

                                    The referenced pages gives:

                                    To read an Excel file with ODBC (tested on Windows 7 with QT 4.7.1 and Windows 10 with QT 5.7) :

                                    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "xlsx_connection"); db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("c:\\path\\to\\your\\file\\file.xlsx")); if(db.open()) {

                                    So, FWIW, let's stick to exactly that string, no changing in spacing or capitalization.

                                    Meanwhile, I see @Christian-Ehrlicher has queried that you verify your ODBC Excel driver is working. I wonder whether you do not have this set up right, e.g. 32-bit only when you're using 64-bit. Like I said earlier you can check this from Windows, please follow what he has said about how to do that.

                                    T Offline
                                    T Offline
                                    Touchoco
                                    wrote on 25 Jan 2021, 15:45 last edited by
                                    #23

                                    @JonB

                                    Thank you for your help.

                                    I worked ! Sticking with exactly the same spaces to the same places. And with 32 bits compiler.
                                    I tried to change to 64 bit compiler : KO. I tried with a very long and weird file name : it works.
                                    Driver and dbq can be write : DRIVER, DBQ, driver, dbq, Driver, Dbq.

                                    Here is the current working code :

                                    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
                                        QString connectionString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("D:\\sabin\\Documents\\C++ et C\\C++\\BaF\\BaF\\Templates\\TEST.xls"));
                                        db.setDatabaseName(connectionString);
                                        if(db.open())
                                        {
                                            QSqlQuery query("select * from [" + QString("Infos_adm") + "$A1:E20]");
                                    
                                            while (query.next())
                                            {
                                                //reading columns
                                                QString column1= query.value(0).toString();
                                                qDebug()<<column1;
                                            }
                                            qDebug()<<"done";
                                            db.close();
                                            QString dbConnectionName(db.connectionName());
                                            db.~QSqlDatabase();
                                            QSqlDatabase::removeDatabase(dbConnectionName);
                                        }
                                        else
                                        {
                                            qDebug()<<db.lastError();
                                            qDebug() << connectionString ;
                                        }
                                    
                                    
                                    C J 2 Replies Last reply 25 Jan 2021, 15:53
                                    0
                                    • T Touchoco
                                      25 Jan 2021, 15:45

                                      @JonB

                                      Thank you for your help.

                                      I worked ! Sticking with exactly the same spaces to the same places. And with 32 bits compiler.
                                      I tried to change to 64 bit compiler : KO. I tried with a very long and weird file name : it works.
                                      Driver and dbq can be write : DRIVER, DBQ, driver, dbq, Driver, Dbq.

                                      Here is the current working code :

                                      QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
                                          QString connectionString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("D:\\sabin\\Documents\\C++ et C\\C++\\BaF\\BaF\\Templates\\TEST.xls"));
                                          db.setDatabaseName(connectionString);
                                          if(db.open())
                                          {
                                              QSqlQuery query("select * from [" + QString("Infos_adm") + "$A1:E20]");
                                      
                                              while (query.next())
                                              {
                                                  //reading columns
                                                  QString column1= query.value(0).toString();
                                                  qDebug()<<column1;
                                              }
                                              qDebug()<<"done";
                                              db.close();
                                              QString dbConnectionName(db.connectionName());
                                              db.~QSqlDatabase();
                                              QSqlDatabase::removeDatabase(dbConnectionName);
                                          }
                                          else
                                          {
                                              qDebug()<<db.lastError();
                                              qDebug() << connectionString ;
                                          }
                                      
                                      
                                      C Offline
                                      C Offline
                                      Christian Ehrlicher
                                      Lifetime Qt Champion
                                      wrote on 25 Jan 2021, 15:53 last edited by
                                      #24

                                      @Touchoco said in Qt & Excel xlsx outputs:

                                      And with 32 bits compiler.
                                      I tried to change to 64 bit compiler : KO

                                      So my idea was wrong. Install a 64 Bit Excel if you want to use a 64Bit Qt (sad but true).

                                      Qt Online Installer direct download: https://download.qt.io/official_releases/online_installers/
                                      Visit the Qt Academy at https://academy.qt.io/catalog

                                      1 Reply Last reply
                                      0
                                      • T Touchoco
                                        25 Jan 2021, 15:45

                                        @JonB

                                        Thank you for your help.

                                        I worked ! Sticking with exactly the same spaces to the same places. And with 32 bits compiler.
                                        I tried to change to 64 bit compiler : KO. I tried with a very long and weird file name : it works.
                                        Driver and dbq can be write : DRIVER, DBQ, driver, dbq, Driver, Dbq.

                                        Here is the current working code :

                                        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
                                            QString connectionString("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("D:\\sabin\\Documents\\C++ et C\\C++\\BaF\\BaF\\Templates\\TEST.xls"));
                                            db.setDatabaseName(connectionString);
                                            if(db.open())
                                            {
                                                QSqlQuery query("select * from [" + QString("Infos_adm") + "$A1:E20]");
                                        
                                                while (query.next())
                                                {
                                                    //reading columns
                                                    QString column1= query.value(0).toString();
                                                    qDebug()<<column1;
                                                }
                                                qDebug()<<"done";
                                                db.close();
                                                QString dbConnectionName(db.connectionName());
                                                db.~QSqlDatabase();
                                                QSqlDatabase::removeDatabase(dbConnectionName);
                                            }
                                            else
                                            {
                                                qDebug()<<db.lastError();
                                                qDebug() << connectionString ;
                                            }
                                        
                                        
                                        J Offline
                                        J Offline
                                        JonB
                                        wrote on 25 Jan 2021, 16:51 last edited by
                                        #25

                                        @Touchoco
                                        Glad it works, but it's a shame to have to change over to 32-bit Qt app just to be able to talk ODBC to Excel.

                                        If you want to pursue this further I think it is a non-Qt issue. You would have to Google for something about how to ODBC from a 64-bit executable when I have such-and-such (probably 32-bit) Excel, or similar.

                                        1 Reply Last reply
                                        2

                                        16/25

                                        22 Jan 2021, 08:17

                                        • Login

                                        • Login or register to search.
                                        16 out of 25
                                        • First post
                                          16/25
                                          Last post
                                        0
                                        • Categories
                                        • Recent
                                        • Tags
                                        • Popular
                                        • Users
                                        • Groups
                                        • Search
                                        • Get Qt Extensions
                                        • Unsolved