Reading values from Excel datasheet gives 0 on blank field

[Jendker]

Hello everyone,

I am struggling with one issue with reading the variable values from Excel file.

I am doing it like this (following this tutorial):

void Variables::populateVariables()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "variables_config_connection");
    db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString(".//shared//variables_config.xls"));
    if(db.open())
    {
        QSqlQuery query("select * from [Sheet1$]", db);
        while (query.next())
        {
            QString name = query.value(1).toString();
            QString address = query.value(2).toString();
            QString type = query.value(3).toString();
            QString start_value = query.value(4).toString();
            QVariant dummy = query.value(4);

            if (name == "" || address == "" || type == "")
            {
                continue;
            }

            addVariable(name, address, type, start_value);
        }
    }
    db.close();
}

The variables are set one-by-one with addVariable. Here I am giving the user possibility to set the initial value for variable in the Excel file in the corresponding column (colun with id 4). If none value is set (field is blank) it should set the initial value taken from QSettings (retrieves the last value from last run). The problem is, that I am not able to differentiate if the field was blank or the value 0 was given. In both cases the QVariant 0.0 (double) for the field will be retrieved (here variable 'dummy', checked with CDB debugger). Is there any solution here or should I look for a different way to retrieve data from Excel file to avoid this?

[hskoglund]

Hi, to be able to detect empty cells you can try to open the Excel file with Extended Properties, like this:

...
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "variables_config_connection");
db.setDatabaseName("DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" + QString(".//shared//variables_config.xls;Extended Properties=\"HDR=NO;IMEX=1\""));

That should allow query.value(4); to return a QString-flavored QVariant instead of a double(0.0)..

[Jendker]

@hskoglund Thank you for your answer!

But unfortunately it does not seem to work... I am still getting 0.0 QVariant (double) in the debugger for

query.value(4);

Do I have to define the Excel version similar to this here

[JonB]

@Jendker
There are two ways a QVariant can (appear) to have the value "0": it can be valid and have value zero, or it can be "invalid" or "null" and will be treated as having value zero.

Before you do anything else, on your value check QVariant::isValid() and QVariant::isNull() to see whether one of those returns true if & only if you're looking at a blank cell as opposed to one containing 0.0?

[Jendker]

@JonB

Thank you for your reply! But unfortunately I am getting QVariant::isValid true in both cases and QVariant::isNull false also in both cases :(

Maybe I will have to live with it and just try to find a different way of getting this input from user.

[hskoglund]

Maybe you can achieve your goal using another approach in opening Excel files in Qt, instead of using ODBC you can use COM (QAxObject), see this example

[Jendker]

@hskoglund

Thank you! I will use it or one of the libraries listed here.