Results 1 to 17 of 17

Thread: My approach to export QTableView data to a Microsoft Excel file

  1. #1
    Join Date
    Jan 2010
    Posts
    5
    Thanked 7 Times in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default 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.

    Qt Code:
    1. // 1. declare an object
    2. // – fileName contains the path to the Excel file
    3. // – sheetName is the name of the Excel sheet
    4. // – tableView is the pointer to a QTableView
    5. ExportExcelObject obj(fileName, sheetName, tableView);
    6.  
    7. // 2. define fields (columns) to the Excel sheet file
    8. // – the first parameter is referred to the column number in QTableView
    9. // – the second parameter is the name of the column of the Excel sheet
    10. // – the third parameter is the type of this column, you can use char(x) (max(x) is 255)
    11. // int, datetime, etc
    12. obj.addField(1, tr("name"), "char(60)");
    13. obj.addField(2, tr("ID"), "int");
    14. obj.addField(3, tr("time"), " datetime ");
    15.  
    16. // 3. connect the signal “exportedRowCount” to a progress widget to show the exporting
    17. // progress if necessary, this makes the exporting progress more user-friendly
    18. connect(&obj, SIGNAL(exportedRowCount(int)), progressBar, SLOT(setValue(int)));
    19.  
    20. // 4. do the work
    21. int retVal = obj.export2Excel();
    22. if(retVal > 0)
    23. {//done
    24. }
    25. else
    26. {//something wrong
    27. [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:
    Qt Code:
    1. QString dsn = QString("DRIVER={Microsoft Excel Driver (*.xls)};DSN=''; FIRSTROWHASNAMES=1; READONLY=FALSE;CREATE_DB=\"%1\";DBQ=%2").
    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.

  2. The following 7 users say thank you to venomj for this useful post:

    alizadeh91 (10th March 2013), jcyangzh (14th August 2012), qks1 (7th July 2013), setoy (17th January 2013), spartakuskus (18th October 2012), warcraff123456 (24th February 2013)

  3. #2
    Join Date
    Aug 2008
    Location
    Ukraine, Krivoy Rog
    Posts
    1,963
    Thanked 370 Times in 336 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    I think the easiest what to reach this, it's to save you QTableView representation in csv format.
    Qt Assistant -- rocks!
    please, use tags [CODE] & [/CODE].

  4. #3
    Join Date
    Jan 2010
    Posts
    5
    Thanked 7 Times in 1 Post
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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.

  5. #4
    Join Date
    Jul 2010
    Location
    Tampere, Finland
    Posts
    10
    Thanks
    1
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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.

  6. #5
    Join Date
    Sep 2010
    Posts
    1
    Qt products
    Qt4
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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

  7. #6
    Join Date
    Feb 2014
    Posts
    1
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Thumbs up Re: My approach to export QTableView data to a Microsoft Excel file

    this's cool. good stuff

  8. #7
    Join Date
    Dec 2015
    Posts
    1
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded Qt Jambi PyQt3 PyQt4
    Platforms
    MacOS X Unix/X11 Windows Android

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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

  9. #8
    Join Date
    Mar 2016
    Posts
    3
    Qt products
    Qt5

    Default Re: My approach to export QTableView data to a Microsoft Excel file

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

  10. #9
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,229
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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.

  11. #10
    Join Date
    Mar 2016
    Posts
    3
    Qt products
    Qt5

    Default Re: My approach to export QTableView data to a Microsoft Excel file

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

  12. #11
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    Quote Originally Posted by thietnguu View Post
    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,
    _

  13. #12
    Join Date
    Mar 2016
    Posts
    3
    Qt products
    Qt5

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    on ubuntu do you know how export to excell?

  14. #13
    Join Date
    Jan 2006
    Location
    Graz, Austria
    Posts
    8,416
    Thanks
    37
    Thanked 1,544 Times in 1,494 Posts
    Qt products
    Qt3 Qt4 Qt5
    Platforms
    Unix/X11 Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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,
    _

  15. #14
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,229
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  16. #15
    Join Date
    Jan 2020
    Posts
    1
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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?

  17. #16
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,229
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  18. #17
    Join Date
    Nov 2006
    Location
    Dresden, Germany
    Posts
    108
    Thanks
    9
    Thanked 12 Times in 10 Posts
    Qt products
    Qt4
    Platforms
    MacOS X Unix/X11 Windows

    Default Re: My approach to export QTableView data to a Microsoft Excel file

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

  19. The following user says thank you to ghorwin for this useful post:

    d_stranz (28th April 2020)

Similar Threads

  1. Replies: 3
    Last Post: 1st February 2011, 12:57
  2. How to read Excel file with Qt
    By HelloDan in forum Qt Programming
    Replies: 3
    Last Post: 13th May 2009, 21:27
  3. How to export data from QTableView to Ms Excell
    By joseph in forum Qt Programming
    Replies: 2
    Last Post: 19th February 2009, 15:19
  4. How to copy from Excel and paste to QTableView
    By sms in forum Qt Programming
    Replies: 5
    Last Post: 7th February 2009, 04:58
  5. Replies: 3
    Last Post: 12th June 2008, 12:59

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.