python win32 qtablewidget 기존 엑셀파일 import 수정 export

https://www.youtube.com/watch?v=55kQL-42Nzo&t=335s 

https://wikidocs.net/156263

4) win32com 통해 엑셀 다루기 - Sheet 제어

qtablewidget_import_export.py--------------------

from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox
from PyQt5.QtCore import Qt
import pandas as pd # pip install pandas
import win32com.client
import warnings
warnings.simplefilter("ignore", UserWarning)
sys.coinit_flags = 2
import pywinauto
import pygetwindow as gw
# from PyQt5.QtGui import QGuiApplication
class MyApp(QWidget):
    def __init__(self):
        super().__init__()
        self.window_width, self.window_height = 700, 500
        self.resize(self.window_width, self.window_height)
        self.excel_file_dir = r"d:\python\vscode\PyQt5\★전체장부.xlsm"
        # self.excel_file_dir_export = r"d:\python\vscode\PyQt5\전체장부.xlsm"
        worksheet_name = "데이터"
        layout = QVBoxLayout()
        self.setLayout(layout)
        self.table = QTableWidget()
        layout.addWidget(self.table)
        self.button = QPushButton('&Load Data')
        self.button.clicked.connect(lambda _, xl_path=self.excel_file_dir, sheet_name=worksheet_name: self.loadExcelData(xl_path, sheet_name))
        layout.addWidget(self.button)
        self.button = QPushButton('&Export To Excel', clicked=self.exportToExcel)
        # self.button.clicked.connect(lambda _, xl_path=excel_file_dir_export, sheet_name=worksheet_name: self.exportToExcel(xl_path, sheet_name))
        layout.addWidget(self.button)
    def loadExcelData(self, excel_file_dir, worksheet_name):
        df = pd.read_excel(excel_file_dir, worksheet_name)
        if df.size == 0:
            return
        df.fillna('',inplace=True)
        self.table.setRowCount(df.shape[0])
        self.table.setColumnCount(df.shape[1])
        self.table.setHorizontalHeaderLabels(df.columns)
        # returns pandas array object
        for row in df.iterrows():
            values = row[1]
            for col_index, value in enumerate(values):
                if isinstance(value, (float, int)):
                    value = '{0:0,.0f}'.format(value)
                tableItem = QTableWidgetItem(str(value))
                self.table.setItem(row[0], col_index, tableItem)
        # self.table.setColumnWidth(2, 300)
        QMessageBox.about(self,'데이터 가져오기','데이터 가져오기 완료')
    # def exportToExcel(self, excel_file_dir_export, worksheet_name):
    def exportToExcel(self):
        xl = win32com.client.Dispatch('Excel.Application')
        xl.Visible = True
        # excel_file_dir_export = self.excel_file_dir_export
        wb = xl.Workbooks.Open(self.excel_file_dir)
        # ws = wb.Worksheets.Add()
        ws = wb.Worksheets('데이터')
        # ws = wb.Worksheets(worksheet_name)
        # ws.name = 'Export'
        # win = gw.getWindowsWithTitle('전체')[0]
        # # print(win)
        # if win.isActive == False:
        #     pywinauto.application.Application().connect(handle=win._hWnd).top_window().set_focus()
        # win.activate()
        # ws.activate()
        # create a new excel worksheet
        rows = []
        columnHeaders = []
        # retrieve columns label
        # for j in range(self.table.model().columnCount()):
        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.model().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
        QMessageBox.about(self,'데이터 저장','데이터 저장 완료')
if __name__ == '__main__':
    # don't auto scale when drag app to a different monitor
    # QGuiApplication.setHighDpiScaleFactorRoundingPolicy(Qt.HighDpiScaleFactorRoundingPolicy.passThrough)
    app = QApplication(sys.argv)
    app.setStyleSheet('''
        QWidget {
            font-size: 17px;
        }
    ''')
    myApp = MyApp()
    myApp.show()
    try:
        sys.exit(app.exec())
    except SystemExit:
        print('Closing Window...')