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

Thank You

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

mrjj

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

Thank You

This post is deleted!

Thank You

@mrjj

I can do two things

Export to excel file
2.Export to pdf

Main aim is to make it ready for printing purpose

JonB

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

Thank You

@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

JonB

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

Thank You

@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++

JonB

@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++"?

Thank You

@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

JonB

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

Thank You

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

JonB

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

Thank You

@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

mrjj

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

Thank You

@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

mrjj

@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

JonB

@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... :)

mrjj

@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 :)

J.Hilk

@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