Extract Data from excel using QODBC return empty string



  • I use this code from qt wiki examples here
    this my code in the button press slot

    void MainWindow::on_pushButton_clicked()
    {
      QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
      db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString("D:\\Book1.xlsx"));
      if(db.open())
      {
      qDebug() << "Open"; // printed and it's open no problem
      QSqlQuery query("select '' from [" + QString("Sheet1") + "$]"); // Select range, place A1:B5 after $
      while (query.next()) // excuted
      {
      QString column1= query.value(0).toString();
      qDebug() << "value = " << column1; // get empty string ""
      }
      qDebug() << "Error = " << query.lastError().text(); // output empty string no error in the query !!
      }
      else {
      qDebug() << db.lastError().text();
      }
    }
    

    what is return is

    Open
    value = ""
    value = ""
    value = ""
    value = ""
    Error = " "
    

    the excel file contains data iam sure 4 * 3 in the excel file so what is wrong or make the example not work for me
    from the db error there is no error output so what cause this problem
    second i try this LibXL library here it's works great and the best library to use for excel but it's not free
    i ask if there is same library for free for read and write like this and support the xlsx excel file
    Thanks in advance



  • Not really sure why that blog suggests select ''' see https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/ use select * from [Sheet1$] (and make sure the sheet is actually called Sheet1)

    i ask if there is same library for free for read and write like this and support the xlsx excel file

    It's not perfect but does the basics: https://github.com/VSRonin/QtXlsxWriter

    If you have excel installed you can use QAxObject to handle it instead



  • Thank you for your reply @VRonin it solved by using select * from [Sheet1$] query
    but i have a problem with the project on github you give me when i try to use it in my project i add this
    to my .pro file

    include($$PWD/src/xlsx/qtxlsx.pri)
    

    i take the src folder and add it to my project folder and i use this code as a test to extract data from .xlsx file

    void MainWindow::on_pushButton_clicked()
    {
        using namespace QXlsx;
        QXlsx::Document xlsx("D:/Book1.xlsx");
        int row = xlsx.dimension().rowCount();
        int col = xlsx.dimension().columnCount();
        qDebug() << "row = " << row << " " << " col = " << col;
        for (int i = 0; i < row; i++) {
            for (int j = 0; j < col; j++) {
                std::cout << xlsx.read(i,j).toString().toStdString() << " ";
            }
            std::cout << std::endl;
        }
        fflush(stdout);
    }
    

    when i start to build i get alot of

    -1: error: undefined reference to `__imp__ZN12QApplicationC1ERiPPci'
    

    about 390 error so what i do wrong in the link part ?
    i try to use this project but i got another problem it was an error in QzipReader.cpp file as i rember but on another user on github but i can't solve it
    iam using
    Qt 5.7 Mingw
    Os : windows 10



  • you are using Usage 2 I imagine. could you try this basic project (link expires in 2 days) and see if you can compile it?

    P.S.
    I'm not a lawyer but since this approach breaks binary compatibility it might invalidate the LGPL license leaving you with either the GPL or commercial options only



  • it built fine both the new you sent to me and the one on github with MSVC 2015 and the application start and the data extracted!
    is there a problem with the mingw compiler with qt 5.7?
    when I build it with 5.6.2 it built but when I start the application crash!
    i don't know why too



  • sorry it was a problem with my qt compiler i built it again and it compiled Thank you @VRonin


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.