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 slotvoid 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/ useselect * 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 fileinclude($$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