Issues with QDateTime handling

[kage-p]

Hi,
I have identified some possible issues with QDateTime handling. In the following test code I can construct a document in memory and write/read strings, but for QDateTime the values are different (it works fine for strings):

    QXlsx::Document doc;
    doc.addSheet("TestWorksheet");

    QVariant expected = QDateTime::currentDateTimeUtc();
    assert(doc.write("A1", expected));

    QVariant actual;
    auto cell = doc.cellAt("A1"); // get cell pointer.
    if ( cell != nullptr)
        actual = cell->readValue();

    qDebug() << (actual == expected ? "Values are the same" : "Values are Not the same");
    qDebug() << "Expected:" << expected.toString();
    qDebug() << "Actual:" << actual.toString();

Result:
Values are Not the same
Expected: "2019-10-11T09:22:47.233Z"
Actual: "43749.390824456015"

This is not an issue for me as I'm writing the document only, but it did throw me a bit.

Also note in the following test code I write to a file and then read it back again but the QDateTime is now empty:

    QString fileName = QUuid::createUuid().toString() + ".xlsx";
    QFile::remove(fileName);

    auto docW = new QXlsx::Document(fileName);
    docW->addSheet("TestWorksheet");

    QVariant expected = QDateTime::currentDateTimeUtc();
    assert(docW->write("A1", expected));

    docW->save();
    delete docW;

    auto docR = new QXlsx::Document(fileName);
    docR->load();

    QVariant actual;
    auto cell = docR->cellAt("A1"); // get cell pointer.
    if ( cell != nullptr)
        actual = cell->readValue();

    auto expectedTime = expected.toDateTime();
    auto actualTime = actual.toDateTime();
    qDebug() << (actualTime == expectedTime ? "Values are the same" : "Values are Not the same");
    qDebug() << "Expected:" << expectedTime.toString(Qt::DateFormat::ISODate);
    qDebug() << "Actual:" << actualTime.toString(Qt::DateFormat::ISODate);

    delete docR;
    QFile::remove(fileName);

Result:
Values are Not the same
Expected: "2019-10-11T09:22:27Z"
Actual: ""

I tracked this issue down the Cell::dateTime() method where dValue is always 0 because the variant is identified as a QDateTime and not a number.

It seems that QDateTime handling is broken; obviously it can be worked around by converting to strings, but this is a pain if serializing a QObject.

Thank you.
Kevin

[j2doll]

1️⃣ Note

• Some code is updated in v1.3.37.
• The type of date and time consists of date&time, date, and time.

2️⃣ First example

code

using namespace QXlsx;

Document doc;
doc.addSheet("TestWorksheet");

QVariant expected = QDateTime::currentDateTimeUtc();
assert(doc.write("A1", expected));

QVariant actual;
auto cell = doc.cellAt("A1"); // get cell pointer.
if ( cell != nullptr)
    actual = cell->readValue();

qDebug() << (actual == expected ? "Values are the same" : "Values are Not the same");
qDebug() << "Expected:" << expected.toString() << expected.type();
qDebug() << "Actual:" << actual.toString() << actual.type();

doc.saveAs("EA.xlsx");

output

Values are Not the same
Expected: "2019-10-11T12:24:57.422Z" QVariant::QDateTime
Actual: "43749.89233127315" QVariant::double

• expected type is QVariant::QDateTime type, and actual type is QVariant::double.
• Because the two values are different types, they cannot be compared.

Open file in Excel (EA.xlsx)

3️⃣ Second example

code

QString fileName = QUuid::createUuid().toString() + ".xlsx";
QFile::remove(fileName);

auto docW = new QXlsx::Document(fileName);
docW->addSheet("TestWorksheet");

QVariant expected = QDateTime::currentDateTimeUtc();
assert(docW->write("A1", expected));

docW->save();
delete docW;

auto docR = new QXlsx::Document(fileName);
docR->load();

QVariant actual;
auto cell = docR->cellAt("A1"); // get cell pointer.
if ( cell != nullptr)
    actual = cell->readValue();

auto expectedTime = expected.toDateTime();
auto actualTime = actual.toDateTime();
qDebug() << (actualTime == expectedTime ? "Values are the same" : "Values are Not the same");
qDebug() << "Expected:" << expectedTime.toString(Qt::DateFormat::ISODate);
qDebug() << "Actual:" << actualTime.toString(Qt::DateFormat::ISODate);

delete docR;
QFile::remove(fileName);

output

expected.type() :  QVariant::QDateTime
actual.type() :  QVariant::QTime
Values are Not the same
Expected: "2019-10-11T12:38:23Z"
Actual: ""

• expected type is QDateTime, and actual type is QTime.
• Becuase actualTime is QTime, auto actualTime = actual.toDateTime(); can not be converted to QDateTime.

I will keep this issue.

[j2doll]

I fixed some code. aaebf96

Try the test.

[j2doll]

More example

• Code

using namespace QXlsx;

Document doc;

doc.write( "A1", QVariant(QDateTime::currentDateTimeUtc()) );
doc.write( "A2", QVariant(double(10.5)) );
doc.write( "A3", QVariant(QDate(2019, 10, 9)) );
doc.write( "A4", QVariant(QTime(10, 9, 5)) );
doc.write( "A5", QVariant((int) 40000) );

qDebug() << "doc.read()";
qDebug() << doc.read( 1, 1 ).type() << doc.read( 1, 1 );
qDebug() << doc.read( 2, 1 ).type() << doc.read( 2, 1 );
qDebug() << doc.read( 3, 1 ).type() << doc.read( 3, 1 );
qDebug() << doc.read( 4, 1 ).type() << doc.read( 4, 1 );
qDebug() << doc.read( 5, 1 ).type() << doc.read( 5, 1 );
qDebug() << "\n";

qDebug() << "doc.cellAt()->value()";
qDebug() << doc.cellAt( 1, 1 )->value().type() << doc.cellAt( 1, 1 )->value();
qDebug() << doc.cellAt( 2, 1 )->value().type() << doc.cellAt( 2, 1 )->value();
qDebug() << doc.cellAt( 3, 1 )->value().type() << doc.cellAt( 3, 1 )->value();
qDebug() << doc.cellAt( 4, 1 )->value().type() << doc.cellAt( 4, 1 )->value();
qDebug() << doc.cellAt( 5, 1 )->value().type() << doc.cellAt( 5, 1 )->value();

doc.saveAs("datetime.xlsx");

• Output

doc.read()
QVariant::QDateTime QVariant(QDateTime, QDateTime(2019-10-12 01:25:59.047 대한민국 표준시 Qt::LocalTime))
QVariant::double QVariant(double, 10.5)
QVariant::QDate QVariant(QDate, QDate("2019-10-09"))
QVariant::QTime QVariant(QTime, QTime("10:09:05.000"))
QVariant::double QVariant(double, 40000)


doc.cellAt()->value()
QVariant::double QVariant(double, 43750.1)
QVariant::double QVariant(double, 10.5)
QVariant::double QVariant(double, 43747)
QVariant::double QVariant(double, 0.422975)
QVariant::double QVariant(double, 40000)