Issue with worksheet_write_datetime() not producing any output

[yanniupai]

Hi All:

I write a demo to test the "datetime write" with Qt 5.9.2 in Windows, but it is error. i am sure the the "lxw_datetime tmp_dt" is OK, source code like below:

    QVariant tmp_qvar;
    QDate tmp_qdt;
    QTime tmp_qtm;
    lxw_datetime tmp_dt;
    lxw_workbook  *workbook  = workbook_new("C:\\Users\\Lucas\\Desktop\\myexcel.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    for(int i=0;i<=md->rowCount();i++)
    {
        for(int j=0;j<md->columnCount();j++)
        {
            tmp_qvar = md->data(md->index(i,j));
            switch (tmp_qvar.type())
            {
            case QMetaType::QDateTime:
                //worksheet_write_string(worksheet, i, j, tmp_qvar.toDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz").toLatin1().data(), NULL);
                tmp_qdt = tmp_qvar.toDate();
                tmp_qtm = tmp_qvar.toTime();
                tmp_dt.year = tmp_qdt.year();
                tmp_dt.month = tmp_qdt.month();
                tmp_dt.day = tmp_qdt.day();
                tmp_dt.hour = tmp_qtm.hour();
                tmp_dt.min = tmp_qtm.minute();
                tmp_dt.sec = tmp_qtm.second();
                worksheet_write_datetime(worksheet, i, j, &tmp_dt, NULL);
                break;
            default:
                worksheet_write_string(worksheet, i, j, tmp_qvar.toByteArray().data(), NULL);
                break;
            }
        }

The result is like below: myexcel.xlsx

But I write the datetime with string is OK, like below:

    worksheet_write_string(worksheet, i, j, tmp_qvar.toDateTime().toString("yyyy-MM-dd hh:mm:ss.zzz").toLatin1().data(), NULL);

[jmcnamara]

I can't compile a Qt program but I'd guess that the issue is that tmp_dt isn't initialized:

    lxw_datetime tmp_dt;

Try this instead:

    lxw_datetime tmp_dt = {0};

Or whatever keeps your compiler happy.

If that isn't the issue then print out the fields of tmp_dt before calling worksheet_write_datetime() and attach the results here.

[jmcnamara]

I'd guess that the issue is that tmp_dt isn't initialized:

Actually, this probably isn't an issue. I thought that the struct had a hidden field but it doesn't. So your field by field assignment is probably okay. I'll look a bit deeper...

[jmcnamara]

Looking at your example again I see that you didn't apply a number format to worksheet_write_datetime(). A number format for a date is required, see the docs for worksheet_write_datetime():

The format parameter should be used to apply formatting to the cell using a Format object as shown above. Without a date format the datetime will appear as a number only.

See also the Working with Dates and Times section of the docs.

[yanniupai]

I add the code as you guide like below: lxw_format *format = workbook_add_format(workbook); format_set_num_format(format, "yyyy-MM-dd hh:mm:ss");

The result is OK. Thanks for you.

Can we add the millisecond and week to the struct lxw_datetime? Our industrial control project needs it. And can we add the function as "worksheet_write_stringArray" to improve the writing efficiency?

[jmcnamara]

The result is OK. Thanks for you.

Great. I'll close the issue.

Can we add the millisecond and week to the struct lxw_datetime?

The seconds member in struct lxw_datetime is a double so you can add milliseconds to that as a fraction of a second.

#include "xlsxwriter.h"

int main() {

    /* A datetime to display. */
    lxw_datetime datetime = {2013, 2, 28, 12, 0, 45.678};

    /* Create a new workbook and add a worksheet. */
    lxw_workbook  *workbook  = workbook_new("date_and_times02.xlsx");
    lxw_worksheet *worksheet = workbook_add_worksheet(workbook, NULL);

    /* Add a format with date formatting. */
    lxw_format    *format    = workbook_add_format(workbook);
    format_set_num_format(format, "ss.000");

    /* Write the datetime with formatting. */
    worksheet_write_datetime(worksheet, 0, 0, &datetime, format);

    return workbook_close(workbook);
}

Output:

And can we add the function as "worksheet_write_stringArray" to improve the writing efficiency?

Do you mean to write an array of strings like this:

char *strings[] = {"foo", "bar", "baz"};

I think functions like that are best handled by the user in their application code. They are supported in the dynamic language versions of this library (in Python, Perl and Lua) but would require a separate function for each data type in the C version. So that probably won't be added.