Export table view to .xls file



  • I'd like to export a tableview contents to a .xls file.
    It works fine in my computer. However, when a final application is released. The others can't export the table to an excel document. I add "QT += sql" in .pro file. Any one know what I missed to make others can also export the table to exel file? For example, i want to export the table to a file named test1.xls, i choose the folder and clic save, it words fine by my computer. But if i try this in another computer there is no test1.xls created by application. I don't what is the diffrence between the two situation. Anyone got an idea?

    Here is part of my code for exportation.

    class EEO_Field
    {
    public:
        EEO_Field(const int ic, const QString &sf, const QString &st); /*! Constructeur*/
        ~EEO_Field();       /*! Destructeur*/
        int     iCol;       /*! Numéro de colone*/
        QString sFieldName; /*! le nom pour l'entete*/
        QString sFieldType; /*! le type de données*/
    };
    
    /** @class ExportExcelObject
      *@brief classe Exporter le tableau en EXCEL
    */
    class ExportExcelObject : public QObject
    {
        Q_OBJECT
    public:
        ExportExcelObject(const QString &filepath, const QString &sheettitle,
                          QTableView *tableview);
        ~ExportExcelObject();                   
        void setOutputFilePath(const QString &spath);   
        void setOutputSheetTitle(const QString &ssheet);
        void setTableView(QTableView *tableview);     
        void addField(const int iCol,
                      const QString &fieldname,
                      const QString &fieldtype);      
        void removeAllFields();                        
        int export2Excel();                          
    
    signals:
        void exportedRowCount(int row); 
    
    private:
        QString excelFilePath;       
        QString sheetName;          
        QTableView *tableView;         
        QList<EEO_Field *> fieldList;   /
    };
    int ExportExcelObject::export2Excel()
    {
        if(fieldList.size() <= 0)
        {
            qDebug() << "ExportExcelObject::export2Excel failed: No fields defined.";
            return -1;
        }
    
        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC", "excelexport");
        if(!db.isValid())
        {
            qDebug() << "ExportExcelObject::export2Excel failed: QODBC not supported.";
            return -2;
        }
        /* set the dsn string*/
        QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%1\";DBQ=%2").
                      arg(excelFilePath).arg(excelFilePath);
        db.setDatabaseName(dsn);
        if(!db.open())
        {
            qDebug() << "ExportExcelObject::export2Excel failed: Create Excel file failed by DRIVER={Microsoft Excel Driver (*.xls)}.";
            //QSqlDatabase::removeDatabase("excelexport");
            return -3;
        }
    
        QSqlQuery query(db);
    
        /* drop the table if it's already exists */
        QString s, sSql = QString("DROP TABLE [%1] (").arg(sheetName);
        query.exec(sSql);
    
        /* create the table (sheet in Excel file) */
        sSql = QString("CREATE TABLE [%1] (").arg(sheetName);
        for (int i = 0; i < fieldList.size(); i++)
        {
            s = QString("[%1] %2").arg(fieldList.at(i)->sFieldName).arg(fieldList.at(i)->sFieldType);
            sSql += s;
            if(i < fieldList.size() - 1)
                sSql += " , ";
        }
    
        sSql += ")";
        query.prepare(sSql);
    
        if(!query.exec())
        {
            qDebug() << "ExportExcelObject::export2Excel failed: Create Excel sheet failed.";
            //db.close();
            //QSqlDatabase::removeDatabase("excelexport");
            return -4;
        }
    
        /* add all rows */
        sSql = QString("INSERT INTO [%1] (").arg(sheetName);
        for (int i = 0; i < fieldList.size(); i++)
        {
            sSql += fieldList.at(i)->sFieldName;
            if(i < fieldList.size() - 1)
                sSql += " , ";
        }
        sSql += ") VALUES (";
        for (int i = 0; i < fieldList.size(); i++)
        {
            sSql += QString(":data%1").arg(i);
            if(i < fieldList.size() - 1)
                sSql += " , ";
        }
        sSql += ")";
    
        qDebug() << sSql;
    
        int r, iRet = 0;
        for(r = 0 ; r < tableView->model()->rowCount() ; r++)
        {
            query.prepare(sSql);
            for (int c = 0; c < fieldList.size(); c++)
            {
                query.bindValue(QString(":data%1").arg(c), tableView->model()->data(tableView->model()->index(r, fieldList.at(c)->iCol)));
            }
    
            if(query.exec())
                iRet++;
    
            if(r % 10 == 0)
                emit exportedRowCount(r);
        }
    
        emit exportedRowCount(r);
    
        return iRet;
    }
    
    
    


  • Looks like you are not deploying the ODBC plugin.
    What to do depends on your operating system. if you are on windows you can use windeployqt to release the application correctly

    Edit:
    you should also be careful as fieldList.at(i)->sFieldName can contain (accidental) SQL injection. for example if one of the field names contains -- it will break your query



  • @VRonin said:

    ODBC plugin

    Yes, you are right. It lacks of some dll in the sqldrivers folder in the plugin. Now it works. Thank you.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.