widget.cpp
QString fileName = QFileDialog::getSaveFileName(this, tr("Excel file"), qApp->applicationDirPath (),
tr("Excel Files (*.xls)"));
if (fileName.isEmpty())
return;
ExportExcelObject obj(fileName, "mydata", ui->tableView);
// you can change the column order and
// choose which colum to export
obj.addField(0, "colum1", "char(20)");
obj.addField(1, "colum2", "char(20)");
obj.addField(2, "colum3", "char(20)");
obj.addField(3, "colum4", "char(20)");
obj.addField(4, "colum5", "char(20)");
obj.addField(5, "colum6", "char(20)");
obj.addField(6, "colum7", "char(20)");
obj.addField(7, "colum8", "char(20)");
int retVal = obj.export2Excel();
if( retVal > 0)
{
QMessageBox::information(this, tr("Done"),
QString(tr("%1 records exported!")).arg(retVal)
);
}
exportexcelobject.cpp
#include "exportexcelobject.h"
#include <QSqlQuery>
#include <QtDebug>
#include <QVariant>
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;
}exportexcelobject.h
// Copyright (c) 2010 Joel Gao <joel_gao@sina.com>
// All rights reserved.
//
// Redistribution and use in source and binary forms, with or without
// modification, are permitted provided that the following conditions
// are met:
//
// 1. Redistributions of source code must retain the above copyright
// notice, this list of conditions and the following disclaimer.
//
// 2. Redistributions in binary form must reproduce the above copyright
// notice, this list of conditions and the following disclaimer in the
// documentation and/or other materials provided with the distribution.
//
// THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
// "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
// A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
// OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
// SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
// LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
// DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
// THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
// (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
// OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
///////////////////////////////////////////////////////////////////////////////
#ifndef EXPORTEXCELOBJECT_H
#define EXPORTEXCELOBJECT_H
#include <QObject>
#include <QTableView>
#include <QStringList>
#include <QSqlDatabase>
class EEO_Field
{
public:
EEO_Field(const int ic, const QString &sf, const QString &st):
iCol(ic),sFieldName(sf),sFieldType(st){};
int iCol;
QString sFieldName;
QString sFieldType;
};
class ExportExcelObject : public QObject
{
Q_OBJECT
public:
ExportExcelObject(const QString &filepath, const QString &sheettitle,
QTableView *tableview):excelFilePath(filepath),
sheetName(sheettitle), tableView(tableview){};
~ExportExcelObject() {QSqlDatabase::removeDatabase("excelexport");};
public:
void setOutputFilePath(const QString &spath) {excelFilePath = spath;};
void setOutputSheetTitle(const QString &ssheet) {sheetName = ssheet;};
void setTableView(QTableView *tableview) {tableView = tableview;};
void addField(const int iCol, const QString &fieldname, const QString &fieldtype)
{fieldList << new EEO_Field(iCol, fieldname, fieldtype);};
void removeAllFields()
{while (!fieldList.isEmpty()) delete fieldList.takeFirst();};
int export2Excel();
signals:
void exportedRowCount(int row);
private:
QString excelFilePath;
QString sheetName;
QTableView *tableView;
QList<EEO_Field *> fieldList;
};
#endif // EXPORTEXCELOBJECT_H