Qt & Excel xlsx outputs

Touchoco · 20 Jan 2021, 09:05

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

JonB · 21 Jan 2021, 06:58

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

Touchoco · 21 Jan 2021, 07:16

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

JonB · 21 Jan 2021, 11:05

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

Touchoco · 21 Jan 2021, 11:13

@JonB I tried that a long time ago. It didn't solve the problem : error message :(

JonB · 21 Jan 2021, 14:29

@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/ ?

Touchoco · 21 Jan 2021, 14:50

@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 ;
}

JonB · 21 Jan 2021, 16:08

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

J.Hilk · 21 Jan 2021, 16:33

@Touchoco are you aware of this wiki page?

https://wiki.qt.io/Handling_Microsoft_Excel_file_format

contains all you need to know + examples

Touchoco · J JonB 21 Jan 2021, 14:50

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

Touchoco · 21 Jan 2021, 17:29

@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

JonB · 21 Jan 2021, 18:35

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

Touchoco · 21 Jan 2021, 18:39

@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.Hilk · 21 Jan 2021, 18:44

@Touchoco stupid question, but important

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

JonB · 22 Jan 2021, 08:29

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

Touchoco · 22 Jan 2021, 08:20

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

JonB · wrote on 22 Jan 2021, 08:20

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

Touchoco · 22 Jan 2021, 08:44

@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")

Christian Ehrlicher · 25 Jan 2021, 15:07

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.

JonB · 22 Jan 2021, 08:55

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