venomj
23rd February 2010, 08:35
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
}
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);
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.
4318
I created a sample project (with Qt4.6.0) to demonstrate the code. Download here4319(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.
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
}
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);
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.
4318
I created a sample project (with Qt4.6.0) to demonstrate the code. Download here4319(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.