My approach to export QTableView data to a Microsoft Excel file

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.

Code:

---

// 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]

---

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

Code:

---

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.

Attachment 4318
I created a sample project (with Qt4.6.0) to demonstrate the code. Download hereAttachment 4319(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.

I think the easiest what to reach this, it's to save you QTableView representation in csv format. :)

PS: I hope this piece of code can give hint to people who want to "read" data from an Excel file directly. I think it's absolutely possible with QODBC.

venomj definitely has an interesting approach.
I've been struggling to create a program that first reads an excel file or certain database and then exports a processed and formatted excel file.
So far I've been using the QAxObject approach, which is really slow, so I was wondering if it is possible to format the excel file with venomj's approach? I suppose not, but correct me if I am wrong.

Hello,
i tried your project that i found very interesting but when i push the export button and enter a filename, nothing happens.
Do you know why ?

I'm under Windows 7

this's cool. good stuff

In this application we need to create a new database that is directly export into excel file, there is no storage for db file...it is good. but please tell me , how to export existing database file into excel file?

Thanks

In this application we need to create a new database that is directly export into excel file, there is no storage for db file...it is good. but please tell me , how to export existing database file into excel file?

Thanks

Hi All !
i create success a project export to excel on window. but when i work on ubuntu not success. Help me please!

How does the export work? Does it use Qt's ActiveX interface to MS Excel COM? That does not work on any platform except Windows.

i created a project as example " TestTableView" of Venomj. it runs normal on Windows.but not runs on Ubuntu

Quote:

---

Originally Posted by thietnguu

i created a project as example " TestTableView" of Venomj. it runs normal on Windows.but not runs on Ubuntu

---

That example uses ActiveX to remote control an Excel instance. Which, as d_stranz said, obviously only works when there is ActiveX and Excel on the system.
Microsoft has not released either for Linux yet.

Cheers,
_

on ubuntu do you know how export to excell?

Depends on what you need.

If you just have a table of values you could write a simple CSV file.
Excel can import these.

Cheers,
_

Take a look at libXL. There are versions for C, C++ and other languages, and is portable to Windows, linux, Mac, and iOS. It does not require Excel, but can produce xls, xlsx, and xlsm formats that can be opened as workbooks in Excel.

This a commercial library (it isn't free). You can download a demo version that has limited capability if you want to try it out.

There might be open-source libraries available, but I haven't looked.

I'm sorry, but the compressed file attached to the body is empty. Can you upload the full contents of the compressed file or source code again?

This is a 10 year old post, and the last time the OP posted anything to this forum was also 10 years ago. If the zip file is no longer accessible, then it is very unlikely it will be re-posted. The OP is long gone from this forum.

Try searching using Google for Excel export libraries. You can also simply write the table out as a CSV file, which Excel or any other spreadsheet program can import.

Take this: https://github.com/tfussell/xlnt - open source and works well (no macro xlsm files, as far as I know).