My approach to export QTableView data to a Microsoft Excel file

[venomj]

My approach to export QTableView data to a Microsoft Excel file

Hi all. I’m from Beijing, China. I started using Qt 3 months ago. Before that I worked with MFC for more than 10 years. For me, programming with Qt is a whole new exiting experience. I want to thank all Qt developers for making this ‘cute’ library available.

When I use QTableView to present data of a QSqlQueryModel, I think it’s necessary that the data can be exported to a Microsoft Excel file. So other people can add notes, print and copy the data easily. Since I’ve done the same thing before in MFC, I think maybe I can port it to Qt.

After getting familiar with QtSql, it only took me 2 hours to finish the class “ExportExcelObject”. It’s easy to use.

// 1. declare an object 
// – fileName contains the path to the Excel file
// – sheetName is the name of the Excel sheet
// – tableView is the pointer to a QTableView
ExportExcelObject obj(fileName, sheetName, tableView);
 
        // 2. define fields (columns) to the Excel sheet file
// – the first parameter is referred to the column number in QTableView
// – the second parameter is the name of the column of the Excel sheet
// – the third parameter is the type of this column, you can use char(x)  (max(x) is 255)
//  int, datetime, etc
         obj.addField(1, tr("name"), "char(60)");
         obj.addField(2, tr("ID"), "int");
obj.addField(3, tr("time"), " datetime ");
 
// 3. connect the signal “exportedRowCount” to a progress widget to show the exporting 
// progress if necessary, this makes the exporting progress more user-friendly
connect(&obj, SIGNAL(exportedRowCount(int)), progressBar, SLOT(setValue(int)));
 
         // 4. do the work
         int retVal = obj.export2Excel();
if(retVal > 0)
{//done
}
else
{//something wrong
[FONT=Calibri]          }[/FONT]

// 1. declare an object 
// – fileName contains the path to the Excel file
// – sheetName is the name of the Excel sheet
// – tableView is the pointer to a QTableView
ExportExcelObject obj(fileName, sheetName, tableView);
 
        // 2. define fields (columns) to the Excel sheet file
// – the first parameter is referred to the column number in QTableView
// – the second parameter is the name of the column of the Excel sheet
// – the third parameter is the type of this column, you can use char(x)  (max(x) is 255)
//  int, datetime, etc
         obj.addField(1, tr("name"), "char(60)");
         obj.addField(2, tr("ID"), "int");
obj.addField(3, tr("time"), " datetime ");
 
// 3. connect the signal “exportedRowCount” to a progress widget to show the exporting 
// progress if necessary, this makes the exporting progress more user-friendly
connect(&obj, SIGNAL(exportedRowCount(int)), progressBar, SLOT(setValue(int)));
 
         // 4. do the work
         int retVal = obj.export2Excel();
if(retVal > 0)
{//done
}
else
{//something wrong
[FONT=Calibri]          }[/FONT]

To copy to clipboard, switch view to plain text mode 

How does it work?
1. Treat an Excel file as a database
Use the DSN string below to create an Excel file:

QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN=''; FIRSTROWHASNAMES=1; READONLY=FALSE;CREATE_DB=\"%1\";DBQ=%2").
arg(excelFilePath).arg(excelFilePath);

QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN=''; FIRSTROWHASNAMES=1; READONLY=FALSE;CREATE_DB=\"%1\";DBQ=%2").
arg(excelFilePath).arg(excelFilePath);

To copy to clipboard, switch view to plain text mode 

You don’t need to know the detail. {Microsoft Excel Driver (*.xls)} will do all the work.

2. Treat a sheet as a table
Just create an Excel sheet using SQL clause “CREATE TABLE” as you are creating a database table.

3. Insert data to the table
You know what to do.

4. Unicode support
Yes. Both the column names and contents support Unicode.

excelexport.png
I created a sample project (with Qt4.6.0) to demonstrate the code. Download hereTestTableView.zip(It's been compressed by WinRAR).
It’s been tested on WinXP/Vista/7. It does NOT require an installed Excel because the driver is supported by Windows since Windows 2000.