# Qt & Excel xlsx outputs

• 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 ?

Here is my code :

//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 • @Touchoco said in Qt & Excel xlsx outputs: db.setDatabaseName("{Microsoft Excel Driver (.xls,.xlsx,.xlsm,.xlsb)};DBQ=" + QString("C:\Users\User\Desktop\file.xlsx")); Start by learning to write C++ string literals correctly. You even do so correctly later on in your code, so it's not like you don't know.... • @JonB Thanks for your reply. How would you do it ? I tried a lot of different ways to write it but none of those worked. • @Touchoco "C:\Users\User\Desktop\file.xlsx" is an incorrect C++ string literal, for what you intend. Look at your own posted code for where you use the same literal string correctly elsewhere. • @JonB I tried that a long time ago. It didn't solve the problem : error message :( • @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/ ? • @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 ; } • @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. • @Touchoco are you aware of this wiki page? https://wiki.qt.io/Handling_Microsoft_Excel_file_format contains all you need to know + examples • @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. • @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); • @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. • @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"); • @Touchoco stupid question, but important you do not have that file open with excel, while you try to open it via Qt, right ? • @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.... • @J-Hilk I closed all Excel files to be sure. That's not a stupid question ^^ • @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...? • @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") • 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. • @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. • @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 ); • @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. I also tried to compile my program with MinGw 32 bit : the error remain unchanged. • @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())
{
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 ;
}

• @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).

• @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.