Important: Please read the Qt Code of Conduct - https://forum.qt.io/topic/113070/qt-code-of-conduct

How can I export QTableView to Excel file (*.xlsx)



  • I have QTableView named table

    I want to export data in that table to filename say "c:\t.xlsx"
    How can I do it
    I did see many blogs and question on the same topic but it didn't work.

    https://forum.qt.io/topic/70027/export-table-view-to-xls-file
    https://forum.qt.io/topic/24877/how-can-i-export-to-excel-from-qt-code


  • Lifetime Qt Champion

    Hi
    First of all , it must it be xlsx ?
    a text CSV file is not enough? (excel can read that and it super easy to generate)

    If you must write that format, i would use something like
    https://github.com/QtExcel/Qlibxlsxwriter



  • This post is deleted!


  • @mrjj

    I can do two things

    Export to excel file
    2.Export to pdf
    

    Main aim is to make it ready for printing purpose



  • @Thank-You
    Still depends on how much control over the printed output you wish to exert. For example, exporting to a .csv is *much * easier, but you'll end up with just column data imported into Excel, not, say, column widths, or font bolding or whatever. Only you know how much you need.

    BTW, do you know that you want to export to Excel to do printing? When I wanted to print a QTableView, although I offered an option to export to Excel I actually wrote code to just print off the QTableView inside Qt program. By "converting" it to a QTextDocument, which gives you HTML/PDF printing without external programs. I was quite happy with that result, without resorting to Excel or actual PDF commands. But again depends on just what you wish to achieve.



  • @JonB
    can you share the code with me
    which you used for printing whole records

    I once tried using the QPixMap which only prints the data that are currently shown in the screen (not all columns in table view but only columns that are visible)

    Ya I said that I want to get the tableview printed which shows all data and all columns



  • @Thank-You
    It was quite a while ago, while I was writing in Python/PyQt5. (Not that that matters, you can convert to C++ if that's what you're using.) It looks like I wrote it to take a QAbstractItemModel, the QTableView.model(), which if you think about it makes sense. I am wanting to export the model, not how the particular QTableView happens to look, which is also what you need if you want to export to e.g. Word/Excel/CSV. Which I also allow. But the QTextDocument exporter looks like the following. I have omitted some optional extras, to keep the code down. It may not be everything, but should give you an idea of how to do it which you can enhance/adapt.

    Exporter:

    class TableModelExporter(QObject):
        def __init__(self, model: QAbstractItemModel, parent: QObject=None):
            super().__init__(parent)
    
            self.model = model
    
        def createTextDocument(self) -> JTextDocument:
            doc = JTextDocument(self)
            font = QFont("Verdana", 10)
            doc.setDefaultFont(font)
    
            doc.setDocumentMargin(10)
            cursor = QTextCursor(doc)
    
            cursor.movePosition(QTextCursor.Start)
            table = doc.insertTableForModel(cursor, self.model)
    
            cursor.movePosition(QTextCursor.End)
    
            return doc
    

    QTextDocument creator (omitting support methods like boldCharFormat(), setTextCursorHAlign() etc. which should be obvious):

    class JTextDocument(QTextDocument):
    
        def insertTableForModel(self, cursor: QTextCursor, model: QAbstractItemModel) -> QTextTable:
            rowCnt = model.rowCount()
            colCnt = model.columnCount()
            tableFormat = QTextTableFormat()
            tableFormat.setCellSpacing(0)
            tableFormat.setCellPadding(1)
            table = cursor.insertTable(rowCnt + 1, colCnt, tableFormat)
    
            headerFormat = self.boldCharFormat()
            for col in range(colCnt):
                text = model.headerData(col, Qt.Horizontal)
                headerCell = table.cellAt(0, col)
                cursor = headerCell.firstCursorPosition()
                self.setTextCursorHAlign(cursor, Qt.AlignHCenter)
                cursor.insertText(text, headerFormat)
    
            for row in range(rowCnt):
                for col in range(colCnt):
                    val = model.data(model.index(row, col), Qt.DisplayRole)
                    val, alignment, colour = self.textAlignAndColourForValue(val)
                    alignment = model.data(model.index(row, col), Qt.TextAlignmentRole)
                    colour = model.data(model.index(row, col), Qt.ForegroundRole)
                    font = model.data(model.index(row, col), Qt.FontRole)
                    bold = font.bold() if font else False
                    cell = table.cellAt(row + 1, col)
                    cursor = cell.firstCursorPosition()
                    if alignment is not None:
                        self.setTextCursorHAlign(cursor, alignment)
                    if colour is not None:
                        self.setTextCursorColour(cursor, colour)
                    format = self.boldCharFormat() if bold else QTextCharFormat()
                    cursor.insertText(val, format)
    
            return table
    

    You can then do what you like with the resulting QTextDocument/HTML. For PDF printing I went via a QWebEnginePage previewer with a print/saveAs Pdf(), there may be other ways now to convert to PDF.



  • @JonB
    Sorry I can't understand python at all

    Can you send me link where there is information for printing all the data in C++



  • @Thank-You said in How can I export QTableView to Excel file (*.xlsx):

    Sorry I can't understand python at all

    Well that's a shame, because when I was doing Python Qt I had to adapt all examples from C++, which I managed. The important part here is really just https://doc.qt.io/qt-5/qtexttableformat.html. The example code is self-explanatory, and dead easy to convert, IMHO. Up to you what effort you want to make.

    Can you send me link where there is information for printing all the data in C++

    What information for "printing all the data in C++"?



  • @JonB Ya its bad
    But I don't want to learn python in any way
    I don't like it at all and mainly while using QT at least for now

    I mean if you know about c++ code for printing QTableView then send me link
    I searched today for many hours
    It would be very helpful



  • @Thank-You
    Since I wrote the code I gave you myself, I'm hardly going to know about some other C++ equivalent. You don't have to know any Python to figure it. Best of luck.



  • @JonB Can you post the way of exporting to .csv files



  • @Thank-You
    No, it's in Python too.



  • @JonB Do you know any method to export to any file (.xlsx or .csv or .pdf) or simply print
    https://stackoverflow.com/questions/3147030/qtableview-printing
    This link worked but
    I have tableview with 30 columns
    So Data break there

    Its so important
    I am continiously searching this for whole day
    Please help me


  • Lifetime Qt Champion

    @Thank-You
    Hi
    It's quite different to export to CSV versus actually printing to a pdf as with
    CSV file the number of columns doesn't matter but with actual printing, you have the page limits to consider.

    So do you need actual printing (PDF) or is data export just fine ? ( CSV)
    With CSV you will get a file which you have to print using excel or other app than can import CSV file.



  • @mrjj Ok its fine but I want to export
    https://stackoverflow.com/questions/3147030/qtableview-printing
    This actually worked but all the data do not fit here

    If You know how to do it then suggest me
    Else suggest me to export in .csv file


  • Lifetime Qt Champion

    @Thank-You
    Hi
    For actual printing, your only option is to reduce the font size to try to make it fit to paper.
    Maybe also set it to landscape mode. But it sounds like it really cant fit if you have 30.
    Page wrapping on width is hard with a table. (IMHO)

    For export to CSV. you can use
    https://github.com/VSRonin/QtModelUtilities
    it has a nice exporter
    https://vsronin.github.io/QtModelUtilities/class_csv_model_serialiser.html



  • @mrjj said in How can I export QTableView to Excel file (*.xlsx):

    For export to CVS. you can use

    CVS is a US pharmacy chain, don't think they accept any exported files... ;-)

    Same thing as I seem to mistype Excel XLS as XSL... :)


  • Lifetime Qt Champion

    @JonB
    hehe thx
    Yes I always swap those letters for some odd reason.
    And to make it more ironic, Excel uses tabs for its COMMA separated files pr default :)


  • Moderators

    @mrjj I believe this is due to internalizations, as commas are used (in some regions) as decimal point where as you have a hard time typing a tab in excel in the first place :D


  • Lifetime Qt Champion

    @J-Hilk
    hehe yes im pretty sure it has a very valid reason and function and was more aiming
    at the ironic part, that format didn't really follow the name :)


Log in to reply