Source Code: Export QTableWidget Data To Excel using pywin32 | PyQt6 Tutorial

Buy Me a Coffee? Your support is much appreciated!

PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn

Source Code:

import sys
from PyQt6.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, QPushButton, \
                            QHBoxLayout, QVBoxLayout
from PyQt6.QtCore import Qt
import win32com.client as win32


class MyApp(QWidget):
    def __init__(self):
        super().__init__()
        self.window_width, self.window_height = 700, 500
        self.resize(self.window_width, self.window_height)

        layout = QVBoxLayout()
        self.setLayout(layout)

        self.table = QTableWidget()
        layout.addWidget(self.table)

        self.button = QPushButton('&Export To Excel', clicked=self.exportToExcel)
        layout.addWidget(self.button)

        self.loadData()

    def exportToExcel(self):
        xlApp = win32.Dispatch('Excel.Application')
        xlApp.Visible = True

        # create a new excel workbook
        wb = xlApp.Workbooks.Add()

        # create a new excel worksheet
        ws = wb.worksheets.add
        ws.name = 'Excel Dummy Data'

        rows = []
        columnHeaders = []

        # retrieve columns label
        for j in range(self.table.model().columnCount()):
            columnHeaders.append(self.table.horizontalHeaderItem(j).text())

        # retrieve table content
        for row in range(self.table.rowCount()):
            record = []
            for col in range(self.table.columnCount()):
                record.append(self.table.item(row, col).text())
            rows.append(record)

        # insert table content to Excel
        ws.Range(
            ws.cells(2, 1),
            ws.cells(len(rows)+1, len(columnHeaders))
        ).value = rows

        # insert column labels To Excel
        ws.Range(
            ws.cells(1, 1),
            ws.cells(1, len(columnHeaders))
        ).value = columnHeaders

    def loadData(self):
        self.headerLabels = list("ABCDEFGHIJKLMN")

        maxRows = 10000
        self.table.setRowCount(maxRows)
        self.table.setColumnCount(len(self.headerLabels))
        self.table.setHorizontalHeaderLabels(self.headerLabels)

        # inserting data
        for row in range(maxRows):
            for col in range(len(self.headerLabels)):
                itm = QTableWidgetItem('Cell {0}{1}'.format(self.headerLabels[col], row + 1))
                self.table.setItem(row, col, itm)

        self.table.resizeColumnsToContents()
        self.table.resizeRowsToContents()


if __name__ == '__main__':
    # don't auto scale when drag app to a different monitor.
    # QGuiApplication.setHighDpiScaleFactorRoundingPolicy(Qt.HighDpiScaleFactorRoundingPolicy.PassThrough)
    
    excel_file_path = 'data.xlsx'
    worksheet_name = 'Sales'

    app = QApplication(sys.argv)
    app.setStyleSheet('''
        QWidget {
            font-size: 17px;
        }
    ''')
    
    myApp = MyApp()
    myApp.show()

    try:
        sys.exit(app.exec())
    except SystemExit:
        print('Closing Window...')