Extract Data from excel using QODBC return empty string

AmrCoder

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

VRonin

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

AmrCoder

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

VRonin

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

AmrCoder

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

AmrCoder

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