Export QTableView data with a delegated column to CSV

[morteza ali ahmadi]

I have a QTableView which its model comes from a database table and has 2 columns as id and timestamp. Here is a sample of database table data:

id            timestamp
1             66,496,788,378,559,616
2             66,496,788,378,561,987

The timestamp field is in epochs format and I use my custom delegate to convert this filed values to "yyyy-MM-dd HH:mm:ss.zzz" format to paint QTableView. My custom delegate paint function is:

void TimeDgDelegate::paint(QPainter *painter, const QStyleOptionViewItem &option,
                             const QModelIndex &index) const
{
    if(!index.isValid())
    {
        QStyledItemDelegate::paint(painter, option, index);
        return;
    }

    double value = index.model()->data(index, Qt::EditRole).toDouble();
    double qd_ts = value*1.25/(256.0*256.0);

    qd_ts += 315964800000;

    QString str = QDateTime::fromMSecsSinceEpoch(qd_ts, Qt::LocalTime).toString("yyyy-MM-dd HH:mm:ss.zzz");

    if (option.state & QStyle::State_Selected)
        painter->fillRect(option.rect, option.palette.highlight());

    QApplication::style()->drawItemText(painter, option.rect, Qt::AlignHCenter|Qt::AlignVCenter,
                                        option.palette, true, str,
                                        (option.state&QStyle::State_Selected)?QPalette::HighlightedText:QPalette::WindowText );
}

So, my QTableView shows:

id            timestamp
1             2020-03-15 20:09:44.002
2             2020-03-15 20:09:45.431

Now I want to export my QTableView to a csv file. I want to see "yyyy-MM-dd HH:mm:ss.zzz" format in second column of my csv file but the following codes, produces a csv file with the original data format (epochs):

QAbstractItemModel* model = ui->tableView->model();
QString textData;
int rows = model->rowCount();
int columns = model->columnCount();

for (int i = 0; i < rows; i++) {
    for (int j = 0; j < columns; j++) {

            textData += model->data(model->index(i,j)).toString();
            textData += ", ";      
    }
    textData += "\n"; 
}

QFile csvFile("test.csv");
if(csvFile.open(QIODevice::WriteOnly | QIODevice::Truncate)) {

    QTextStream out(&csvFile);
    out << textData;

    csvFile.close();
}

It means that ui->tableView->model() returns the model with original data not delegated ones. How can I export the delegated text of my QTableView?

[JonB]

@morteza-ali-ahmadi said in Export QTableView data with a delegated column to CSV:

It means that ui->tableView->model() returns the model with original data not delegated ones.

This is because item delegates are a part of the data view, and have nothing to do with the model, which is what you are going through (correctly) in your export code.

I have had this issue in old discussions I raised in this forum. When told to use delegates for output string formatting, I have pointed out that this does not help if you want to export or otherwise use the string representation, and so I see/saw a particular string format as being useful at the model side for other purposes, not just the view side.

You have a couple of choices:

• My original approach was to produce the desired string format in the model's data(index, role=Qt::DisplayRole) method, so that is simple & easy to use wherever wanted, and would work for you here. However, I was roundly criticised by some experts, whom I have to respect, saying that is not the intention of Qt::DisplayRole. Others have still supported that approach.

• An alternative might be to define a user role for this, I don't know if experts would be happier with this.

• Otherwise you need to factor out the QDateTime::toString() portion of your delegate code and move it to some shared area accessible both to your delegate and your model-exporting code, and call it separately from both.

Incidentally, in your delegate code if you don't need all the drawItemText() call stuff you can simply assign the desired the desired text via option.text = str and call the base QStyledItemDelegate::paint().

[morteza ali ahmadi]

@JonB Thanks for your response. I prefer to use your original approach compare to the second and third choices, but how can I produce the desired string format in the models data(index, role=Qt::DisplayRole)?

[JonB]

@morteza-ali-ahmadi

QVariant MyModel::data(const QModelIndex &index, int role /*= Qt::DisplayRole*/) const
{
    if (!index.isValid())
        return QVariant();
    QVariant v = BaseModel::data(index, role);
    if (role == Qt::DisplayRole)
        if (isTimeStampColumn(index.column()))
        {
            double value = v.toDouble();
            ...
            return QDateTime::fromMSecsSinceEpoch(qd_ts, Qt::LocalTime).toString("yyyy-MM-dd HH:mm:ss.zzz");
        }
    return v;
}

If you want to correctly sort on this column, you ought have a sort role which returns the base data value, not the string.

Remember from what I said above, don't blame me if one of the expert critics --- @VRonin, IIRC, though I might be mistaken --- calls for your beheading for using this approach... ;-)

[VRonin]

What I would do:
In the delegate, don't reimplement paint(), but displayText() (it's also a lot easier than what you did):

 class TimeDgDelegate : public QStyledItemDelegate{
Q_OBJECT
public:
using QStyledItemDelegate::QStyledItemDelegate;
QString displayText(const QVariant &value, const QLocale &locale) const override{
double value = value.toDouble();
double qd_ts = value*1.25/(256.0*256.0);
qd_ts += 315964800000;
return locale.toString(QDateTime::fromMSecsSinceEpoch(qd_ts, Qt::LocalTime),QStringLiteral("yyyy-MM-dd HH:mm:ss.zzz"));
}
};

Then your csv export would become:

for (int i = 0; i < rows; i++) {
    for (int j = 0; j < columns; j++) {
        const QModelIndex idx = model->index(i,j);
        textData += ui->tableView->itemDelegate(idx)->displayText(model->data(idx),locale()) + QLatin1Char(',');
    }
    textData += QLatin1Char('\n'); 
}

P.S.
Probably doesn't apply here but generally csv exporting requires you to escape , and " characters in the data

[morteza ali ahmadi]

@JonB Thank you again for your codes.

[JonB]

@VRonin
Thanks for posting. I forget about just QStyledItemDelegate::displayText(), that's a good one to remember.

Assuming you are indeed the expert poster in the past about use of Qt::DisplayRole I recall, and who called for my execution :) You don't like that use of the model/display role, and now I feel similarly uncomfortable about your use of anything QStyledItemDelegate, ui->tableView->itemDelegate(idx), when you are exporting model data to CSV. Your export requires access to a UI table view, but the format for CSV export has nothing to do with any model views, and there is no reason to suppose exporting code would have any UI access at all, or even be a GUI application. IMHO. We're probably not going to agree ;-)

[morteza ali ahmadi]

@VRonin Thank you very much, yes, it could solve my problem with a better implementation.

[VRonin]

@JonB said in Export QTableView data with a delegated column to CSV:

Your export requires access to a UI table view, but the format for CSV export has nothing to do with any model views, and there is no reason to suppose exporting code would have any UI access at all, or even be a GUI application

I think we actually agree on this one. The clean way here is to have a function that handles the conversion of data from double to QDate. The delegate will call it and then format it as a string to display, the csv serialiser will call it and then save the QDate to csv.

Assuming you are indeed the expert poster in the past about use of Qt::DisplayRole I recall, and who called for my execution

This is a very corner case in which your approach doesn't do much harm and can be used. if, however, the date format was dd-MM-yyyy HH:mm:ss.zzz then your solution would break sorting in the table (as it would sort as string, not as dates)

[JonB]

@VRonin said in Export QTableView data with a delegated column to CSV:

I think we actually agree on this one.

Amazing :)

This is a very corner case in which your approach doesn't do much harm

It is good to know that at worst I am "harmless" ;-)

The clean way here is to have a function that handles the conversion of data from double to QDate. The delegate will call it and then format it as a string to display, the csv serialiser will call it and then save the QDate to csv.

I suggested that as my 3rd alternative.

then your solution would break sorting in the table (as it would sort as string, not as dates)

Absolutely, and I thought I typed into my answer about if you did this you need to use a different sort role if you sort, but maybe I deleted that. But yes people should bear this in mind.

[morteza ali ahmadi]

@VRonin Does your solution convert epochs format to string one twice? i.e. for each database record in your solution,

• Conversion with displayText function is used to display "yyyy-MM-dd HH:mm:ss.zzz" format in QTableView and,

• Conversion with displayText function is used to store "yyyy-MM-dd HH:mm:ss.zzz" format in CSV.

Is it right?

[VRonin]

correct, you are basically using displayText as a fancy toString().

Also, just to be clear, the delegate repaints on demand and doesn't cache the displayText so it's actually calculated every time a repaints happens

[morteza ali ahmadi]

@VRonin, So I think the combination of your solution and the third solution of @JonB is the best choice. It means that, I can override displayText() instead of paint() and use a function X within that which handles the conversion of data from double to "dd-MM-yyyy HH:mm:ss.zzz". Also, the X function can be used in export to CSV procedure.

@VRonin and @JonB, do you agree with me?

[JonB]

@morteza-ali-ahmadi Yes.