exceltoexcel.py--------------------
from msilib.schema import ListView
import re
from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
QLineEdit, QLabel
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QColor, QBrush
import pandas as pd # pip install pandas
import win32com.client
import os
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import warnings
warnings.simplefilter("ignore", UserWarning)
sys.coinit_flags = 2
# import pywinauto
import pygetwindow as gw
from PyQt5.QtGui import QTextCursor
class MyApp(QWidget):
# class MyApp(QMainWindow):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 ######
layout_1 = QVBoxLayout()
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
# s_open_btn.setFixedSize(200, 30)
# s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
self.s_listwidget = QListWidget(self)
# self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.s_listwidget.setMaximumWidth(300)
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.s_listwidget)
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
# self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.t_listwidget.setMaximumWidth(300)
layout_1.addWidget(t_open_btn)
self.label2 = QLabel()
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
layout_main.addLayout(layout_1)
##### layout_2 ######
layout_2 = QVBoxLayout()
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
# 시그널 연결
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row)
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.fn_df_save(self.t_row)
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
# file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'G:\python\vscode\pay',
directory=r'G:\회사\인사\급여\2022급여\202209',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.file_path:
self.df_list = self.sheetData(self.file_path)
for sheet in self.df_list:
self.s_listwidget.addItem(sheet.name)
self.loadData(0)
# self.initTableWidget(0)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.t_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'E:\python\vscode\pay',
directory=r'B:\python\vscode\excel',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.t_file_path:
self.df_list_t = self.sheetData(self.t_file_path)
for sheet in self.df_list_t:
self.t_listwidget.addItem(sheet.name)
# self.loadData(0)
self.label2.setText(self.t_file_path)
def sheetData(self, file_path):
df_list = []
with pd.ExcelFile(file_path) as wb:
for indx, stn in enumerate(wb.sheet_names):
try:
df = pd.read_excel(wb, sheet_name=stn)
except Exception as e:
print('File read error:', e)
else:
df = df.fillna("")
df.name = stn
df_list.append(df)
return df_list
def loadData(self, row):
try:
self.table.clear()
xl_File = []
# df = pd.read_excel(self.file_path)
self.df = self.df_list[row]
# print(self.df)
if self.df.size == 0:
return
self.df.fillna('',inplace=True)
self.table.setRowCount(self.df.shape[0])
self.table.setColumnCount(self.df.shape[1])
self.table.setHorizontalHeaderLabels(self.df.columns)
for row in self.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)
# QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
except:
return
def fn_df_save(self, t_row):
try:
# self.table.clear()
xl_File = []
# df = pd.read_excel(self.file_path)
self.df_t = self.df_list_t[t_row]
if self.df.size == 0:
return
self.df.fillna('',inplace=True)
self.df.round({"추가수당":0,"급여계":0,"차감지급액":0})
# print(self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}).loc[0])
self.df = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
# print(self.t_listwidget.item(row).text())
# self.df.to_excel(self.t_file_path, sheet_name=self.t_listwidget.item(row).text(), index=False)
# self.df.to_excel(self.t_file_path, sheet_name="4대급여", index=False) # pandas 버전
# wb_s = openpyxl.load_workbook(self.file_path)
df_row_s = dataframe_to_rows(self.df, index=False) # 확인 버전
# ws_s = wb_s[self.s_listwidget.item(self.s_row).text()]
# print(list(df_row_s))
wb_t = openpyxl.load_workbook(self.t_file_path)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# print(ws.title)
# print(self.t_listwidget.item(row).text())
# ws = wb.get_sheet_by_name(self.t_listwidget.item(row).text())
# ws = wb.get_named_ranges(self.t_listwidget.item(row).text())
# # sheet['A1'].value = 'fixed'
# #해당 시트의 마지막 열, 마지막 행
# source data의
# s_row_max = self.df.shape[0]
# s_column_max = self.df.shape[1]
# s_row_max = ws_s.max_row
# s_column_max = ws_s.max_column
# t_row_max = self.df_t.shape[0]
# t_column_max = self.df_t.shape[1]
# t_row_max = ws_t.max_row
# t_column_max = ws_t.max_column
# print("행수:",row_max,"열수:",column_max)
# column_max = ws_s.max_column
# row_max = ws_s.max_row
# print(column_max,row_max)
#출처: https://ybworld.tistory.com/112 [투손플레이스:티스토리]
#열마다 행을 for loop문 진행
##################################################################
# wb_t.delete_rows(1,t_row_max)
# wb_t.delete_cols(1,t_column_max)
# wb_t.create_sheet(ws_t)
ws_t.delete_rows(1, ws_t.max_row) # 확인 버전
# for col_num in range(1, s_column_max+1):
# for row_num in range(1, s_row_max+1):
# #tempstr : cell값이 문자열이 아닌 경우를 감안하여 str로 바꿔줌
# tempstr = str(ws_s.cell(row = row_num, column = col_num).value)
# #문자열 함수 replace 사용(,를 빈칸으로)
# data = tempstr.replace(",","")
# #빈 셀의 경우 None이라는 문자열 타입이므로 제외하고 데이터 입력
# if data != "None":
# ws_t.cell(row = row_num, column = col_num).value = data
# for col_idx in range(1, ws_s.max_column):
# for row_idx in range(1, ws_s.max_row):
# ws_t.cell(column=col_idx, row=row_idx).value
for r_num, row in enumerate(df_row_s, 1): # 확인 버전
for c_num, value in enumerate(row, 1): # 확인 버전
ws_t.cell(row=r_num, column=c_num, value=value) # 확인 버전
# ws_t.cell(row=r_num, column=c_num, value=(df_row_s.cell(row = r_num, column = c_num).value))
# for row in self.df.iterrows():
# values = row[1]
# for col_index, value in enumerate(values):
# if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
wb_t.save(self.t_file_path) # 확인 버전
# wb_t.save("a.xlsx")
# print(self.t_file_path)
################################################################
# self.table.setRowCount(df.shape[0])
# self.table.setColumnCount(df.shape[1])
# self.table.setHorizontalHeaderLabels(df.columns)
# 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)
QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
except:
return
if __name__ == '__main__':
app = QApplication(sys.argv)
app.setStyleSheet('''
QWidget {
font-size: 17px;
}
QHeaderView::section::Horizontal {
background-color: rgb(191, 255, 0);
}
''')
myApp = MyApp()
myApp.show()
try:
sys.exit(app.exec())
except SystemExit:
print('Closing Window...')exceltoexcel.py--------------------반올림버전
from msilib.schema import ListView
import re
from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
QLineEdit, QLabel
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QColor, QBrush
import pandas as pd # pip install pandas
import win32com.client
import os
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import warnings
warnings.simplefilter("ignore", UserWarning)
sys.coinit_flags = 2
# import pywinauto
import pygetwindow as gw
from PyQt5.QtGui import QTextCursor
class MyApp(QWidget):
# class MyApp(QMainWindow):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 700, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 ######
layout_1 = QVBoxLayout()
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
# s_open_btn.setFixedSize(200, 30)
# s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
self.s_listwidget = QListWidget(self)
# self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.s_listwidget)
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
# self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
layout_1.addWidget(t_open_btn)
self.label2 = QLabel()
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
layout_main.addLayout(layout_1)
##### layout_2 ######
layout_2 = QVBoxLayout()
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
# 시그널 연결
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row)
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.fn_df_save(self.t_row)
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
# file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'G:\python\vscode\pay',
directory=r'G:\회사\인사\급여\2022급여\202209',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.file_path:
self.df_list = self.sheetData(self.file_path)
for sheet in self.df_list:
self.s_listwidget.addItem(sheet.name)
self.loadData(0)
# self.initTableWidget(0)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.t_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'E:\python\vscode\pay',
directory=r'B:\python\vscode\excel',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.t_file_path:
self.df_list_t = self.sheetData(self.t_file_path)
for sheet in self.df_list_t:
self.t_listwidget.addItem(sheet.name)
# self.loadData(0)
self.label2.setText(self.t_file_path)
def sheetData(self, file_path):
df_list = []
with pd.ExcelFile(file_path) as wb:
for indx, stn in enumerate(wb.sheet_names):
try:
df = pd.read_excel(wb, sheet_name=stn)
except Exception as e:
print('File read error:', e)
else:
df = df.fillna("")
df.name = stn
df_list.append(df)
return df_list
def loadData(self, row):
try:
self.table.clear()
xl_File = []
# df = pd.read_excel(self.file_path)
self.df = self.df_list[row]
# print(self.df)
if self.df.size == 0:
return
self.df.fillna('',inplace=True)
self.table.setRowCount(self.df.shape[0])
self.table.setColumnCount(self.df.shape[1])
self.table.setHorizontalHeaderLabels(self.df.columns)
for row in self.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)
# QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
except:
return
def fn_df_save(self, t_row):
try:
# self.table.clear()
xl_File = []
# df = pd.read_excel(self.file_path)
self.df_t = self.df_list_t[t_row]
if self.df.size == 0:
return
self.df.fillna('',inplace=True)
self.df.round({"추가수당":0,"급여계":0,"차감지급액":0})
# print(self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}).loc[0])
self.df = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
# print(self.t_listwidget.item(row).text())
# self.df.to_excel(self.t_file_path, sheet_name=self.t_listwidget.item(row).text(), index=False)
# wb_s = openpyxl.load_workbook(self.file_path)
df_row_s = dataframe_to_rows(self.df, index=False) #@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@여기수정요망
# ws_s = wb_s[self.s_listwidget.item(self.s_row).text()]
# print(list(df_row_s))
wb_t = openpyxl.load_workbook(self.t_file_path)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# print(ws.title)
# print(self.t_listwidget.item(row).text())
# ws = wb.get_sheet_by_name(self.t_listwidget.item(row).text())
# ws = wb.get_named_ranges(self.t_listwidget.item(row).text())
# # sheet['A1'].value = 'fixed'
# #해당 시트의 마지막 열, 마지막 행
# source data의
# s_row_max = self.df.shape[0]
# s_column_max = self.df.shape[1]
# s_row_max = ws_s.max_row
# s_column_max = ws_s.max_column
# t_row_max = self.df_t.shape[0]
# t_column_max = self.df_t.shape[1]
# t_row_max = ws_t.max_row
# t_column_max = ws_t.max_column
# print("행수:",row_max,"열수:",column_max)
# column_max = ws_s.max_column
# row_max = ws_s.max_row
# print(column_max,row_max)
#출처: https://ybworld.tistory.com/112 [투손플레이스:티스토리]
#열마다 행을 for loop문 진행
##################################################################
# wb_t.delete_rows(1,t_row_max)
# wb_t.delete_cols(1,t_column_max)
# wb_t.create_sheet(ws_t)
ws_t.delete_rows(1, ws_t.max_row)
# for col_num in range(1, s_column_max+1):
# for row_num in range(1, s_row_max+1):
# #tempstr : cell값이 문자열이 아닌 경우를 감안하여 str로 바꿔줌
# tempstr = str(ws_s.cell(row = row_num, column = col_num).value)
# #문자열 함수 replace 사용(,를 빈칸으로)
# data = tempstr.replace(",","")
# #빈 셀의 경우 None이라는 문자열 타입이므로 제외하고 데이터 입력
# if data != "None":
# ws_t.cell(row = row_num, column = col_num).value = data
# for col_idx in range(1, ws_s.max_column):
# for row_idx in range(1, ws_s.max_row):
# ws_t.cell(column=col_idx, row=row_idx).value
for r_num, row in enumerate(df_row_s, 1):
for c_num, value in enumerate(row, 1):
# ws_t.cell(row=r_num, column=c_num, value=(df_row_s.cell(row = r_num, column = c_num).value))
ws_t.cell(row=r_num, column=c_num, value=value)
# for row in self.df.iterrows():
# values = row[1]
# for col_index, value in enumerate(values):
# if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
wb_t.save(self.t_file_path)
# wb_t.save("a.xlsx")
# print(self.t_file_path)
################################################################
# self.table.setRowCount(df.shape[0])
# self.table.setColumnCount(df.shape[1])
# self.table.setHorizontalHeaderLabels(df.columns)
# 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)
QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
except:
return
if __name__ == '__main__':
app = QApplication(sys.argv)
app.setStyleSheet('''
QWidget {
font-size: 17px;
}
QHeaderView::section::Horizontal {
background-color: rgb(191, 255, 0);
}
''')
myApp = MyApp()
myApp.show()
try:
sys.exit(app.exec())
except SystemExit:
print('Closing Window...')exceltoexcelpandas.py--------------------원본 데이터를 잃지 않고 쓰고 싶을 때
from msilib.schema import ListView
import re
from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
QLineEdit, QLabel
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QColor, QBrush
import pandas as pd # pip install pandas
import win32com.client
import os
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import warnings
warnings.simplefilter("ignore", UserWarning)
sys.coinit_flags = 2
# import pywinauto
import pygetwindow as gw
from PyQt5.QtGui import QTextCursor
class MyApp(QWidget):
# class MyApp(QMainWindow):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 ######
layout_1 = QVBoxLayout()
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
# s_open_btn.setFixedSize(200, 30)
# s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
self.s_listwidget = QListWidget(self)
# self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.s_listwidget.setMaximumWidth(300)
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.s_listwidget)
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
# self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.t_listwidget.setMaximumWidth(300)
layout_1.addWidget(t_open_btn)
self.label2 = QLabel()
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
layout_main.addLayout(layout_1)
##### layout_2 ######
layout_2 = QVBoxLayout()
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
# 시그널 연결
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row)
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.fn_df_save(self.t_row)
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
# file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'G:\python\vscode\pay',
directory=r'G:\회사\인사\급여\2022급여\202209',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.file_path:
self.df_list = self.sheetData(self.file_path)
for sheet in self.df_list:
self.s_listwidget.addItem(sheet.name)
self.loadData(0)
# self.initTableWidget(0)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
self.t_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'E:\python\vscode\pay',
directory=r'B:\python\vscode\excel',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.t_file_path:
self.df_list_t = self.sheetData(self.t_file_path)
for sheet in self.df_list_t:
self.t_listwidget.addItem(sheet.name)
# self.loadData(0)
self.label2.setText(self.t_file_path)
def sheetData(self, file_path):
df_list = []
with pd.ExcelFile(file_path) as wb:
for indx, stn in enumerate(wb.sheet_names):
try:
df = pd.read_excel(wb, sheet_name=stn)
except Exception as e:
print('File read error:', e)
else:
df = df.fillna("")
df.name = stn
df_list.append(df)
return df_list
def loadData(self, row):
try:
self.table.clear()
xl_File = []
# df = pd.read_excel(self.file_path)
self.df = self.df_list[row]
# print(self.df)
if self.df.size == 0:
return
self.df.fillna('',inplace=True)
self.table.setRowCount(self.df.shape[0])
self.table.setColumnCount(self.df.shape[1])
self.table.setHorizontalHeaderLabels(self.df.columns)
for row in self.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)
# QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
except:
return
def fn_df_save(self, t_row):
try:
xl_File = []
self.df_t = self.df_list_t[t_row]
if self.df.size == 0:
return
# 원본
self.df.fillna('',inplace=True)
self.df = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
wb_t = openpyxl.load_workbook(self.t_file_path)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
wb_t.remove(wb_t[self.t_listwidget.item(t_row).text()]) # 타겟 시트 삭제
wb_t.save(filename=self.t_file_path)
book = openpyxl.load_workbook(self.t_file_path)
writer = pd.ExcelWriter(self.t_file_path, engine = 'openpyxl')
writer.book = book
# for row in self.df.iterrows():
# values = row[1]
# for col_index, value in enumerate(values):
# if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
self.df.to_excel(writer, sheet_name=self.t_listwidget.item(t_row).text(),index=False)
writer.save()
QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
except:
return
if __name__ == '__main__':
app = QApplication(sys.argv)
app.setStyleSheet('''
QWidget {
font-size: 17px;
}
QHeaderView::section::Horizontal {
background-color: rgb(191, 255, 0);
}
''')
myApp = MyApp()
myApp.show()
try:
sys.exit(app.exec())
except SystemExit:
print('Closing Window...')How to save a new sheet in an existing excel file, using Pandas?
I want to use excel files to store data elaborated with python. My problem is that I can't add sheets to an existing excel file. Here I suggest a sample code to work with in order to reach this iss...
stackoverflow.com
엄청 찾아 돌아다녔네요ㅠㅠ
xlsmtoxlsx.py--------------------
# importing openpyxl module
import openpyxl as xl;
# opening the source excel file
filename =r"E:\python\vscode\pay\급여s.xlsm"
wb1 = xl.load_workbook(filename)
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]
# opening the destination excel file
filename1 =r"E:\python\vscode\pay\급여1.xlsx" # 성공
# filename1 =r"E:\python\vscode\pay\급여1.xlsm" # 실패
wb2 = xl.load_workbook(filename1)
# ws2 = wb2.active
ws2 = wb2["4대급여"]
# calculate total number of rows and
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
# copying the cell values from source
# excel file to destination excel file
for i in range (1, mr + 1):
for j in range (1, mc + 1):
# reading cell value from source excel file
c = ws1.cell(row = i, column = j)
# writing the read value to destination excel file
ws2.cell(row = i, column = j).value = c.value
# saving the destination excel file
wb2.save(str(filename1))
# https://python.engineering/python-how-to-copy-data-from-one-excel-sheet-to-another/xlsmtoxlsm.py--------------------
# importing openpyxl module
import openpyxl as xl
# opening the source excel file
filename1 =r"B:\python\vscode\excel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename1)
# wb1 = xl.load_workbook(filename1, read_only=False, keep_vba=True)
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]
# opening the destination excel file
# filename1 =r"B:\python\vscode\excel\excel_data\급여x1.xlsx" # 성공
filename2 =r"B:\python\vscode\excel\excel_data\급여대장m1.xlsm" # 성공
wb2 = xl.load_workbook(filename2, read_only=False, keep_vba=True) # 성공
# ws2 = wb2.active
ws2 = wb2["4대급여"]
# calculate total number of rows and
# columns in source excel file
mr = ws1.max_row
mc = ws1.max_column
# copying the cell values from source
# excel file to destination excel file
for i in range (1, mr + 1):
for j in range (1, mc + 1):
# reading cell value from source excel file
c = ws1.cell(row = i, column = j)
# writing the read value to destination excel file
ws2.cell(row = i, column = j).value = c.value
# saving the destination excel file
wb2.save(str(filename2))
# https://python.engineering/python-how-to-copy-data-from-one-excel-sheet-to-another/https://stackoverflow.com/questions/17675780/how-to-save-xlsm-file-with-macro-using-openpyxl
How to save XLSM file with Macro, using openpyxl
I have .xlsm file with a Macro function. I'm loading it using openpyxl and write some data to the file and finally want to save as a different .xlsm file. To save the file as XLSM file I have used...
stackoverflow.com
xlsmtoxls.py--------------------
# importing openpyxl module
import openpyxl as xl
import xlrd
import xlwt
# opening the source excel file
filename1 =r"B:\python\vscode\excel\excel_data\급여대장m.xlsm"
wb1 = xl.load_workbook(filename1)
# ws1 = wb1.worksheets[0]
ws1 = wb1["4대급여"]
# print(ws1.title)
filename2 =r"B:\python\vscode\excel\excel_data\급여s1.xls" # 성공
wb2 = xlrd.open_workbook(filename2)
sh_name = "4대급여"
ws2 = wb2[sh_name]
# nrows = ws2.nrows
# ncols = ws2.ncols
nrows = ws1.max_row
ncols = ws1.max_column
# print(nrows)
# print(ws1.cell(row=1, column=1).value)
# mr = ws1.max_row
# mc = ws1.max_column
datadict = {}
for row_num in range(1,nrows+1):
# if row_num<=5:
# print(row_num)
# break
datadict[row_num] = {}
for col in range(1,ncols+1):
datadict[row_num][col] = ws1.cell(row=row_num, column=col).value
# datadict[row_num][col] = ws2.cell_value(row_num, col)
wb2 = xlwt.Workbook(encoding='utf-8') # utf-8 인코딩 방식의 workbook 생성
worksheetw = wb2.add_sheet(sh_name) # 시트 생성
for row_num in range(1,nrows+1):
for col in range(1,ncols+1):
worksheetw.write(row_num-1, col-1, datadict[row_num][col])
wb2.save(filename2)
# for i in range (1, mr + 1):
# for j in range (1, mc + 1):
# # reading cell value from source excel file
# c = ws1.cell(row = i, column = j)
# # writing the read value to destination excel file
# ws2.cell(row = i, column = j).value = c.value
# # saving the destination excel file
# wb2.save(str(filename2))
# https://python.engineering/python-how-to-copy-data-from-one-excel-sheet-to-another/문제점
xls 확장자
다른시트 다 없어지고 vba 매크로 없어짐
xls 확장자 지원 포기===
xlsx, xlsm 지원 버전
xlsmtoxlsx_toxlsm.py--------------------
# from msilib.schema import ListView
# import re
# from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
QLineEdit, QLabel
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QColor, QBrush
import pandas as pd # pip install pandas
# import win32com.client
import os
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
# import warnings
# warnings.simplefilter("ignore", UserWarning)
# sys.coinit_flags = 2
# import pywinauto
# import pygetwindow as gw
from PyQt5.QtGui import QTextCursor
class MyApp(QWidget):
# class MyApp(QMainWindow):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 ######
layout_1 = QVBoxLayout()
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
s_open_btn.setMaximumWidth(300)
# s_open_btn.setFixedSize(200, 30)
# s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
self.s_listwidget = QListWidget(self)
# self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.s_listwidget.setMaximumWidth(300)
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.s_listwidget)
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.setMaximumWidth(300)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
# self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.t_listwidget.setMaximumWidth(300)
layout_1.addWidget(t_open_btn)
self.label2 = QLabel()
self.label2.setMaximumWidth(300)
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
layout_main.addLayout(layout_1)
##### layout_2 ######
layout_2 = QVBoxLayout()
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
# 시그널 연결
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row)
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.fn_df_save(self.t_row)
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
# file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
file_filter = 'Excel File (*.xls* *.xls)'
self.s_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'G:\python\vscode\pay',
directory=r'E:\python\vscode\pay\excel_data',
filter=file_filter,
# initialFilter='Excel File (*.xls *.xlsx)'
initialFilter='Excel File (*.xls* *.xls)'
)
if self.s_file_path:
# 파일의 시트리스트
self.list_sheet_s = self.fn_listsheetData(self.s_file_path)
for sheet in self.list_sheet_s:
# print(sheet)
self.s_listwidget.addItem(sheet)
self.loadData(0)
# self.initTableWidget(0)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.t_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
directory=r'E:\python\vscode\pay\excel_data',
# directory=r'B:\python\vscode\excel',
filter=file_filter
# initialFilter='Excel File (*.xls *.xlsx)'
# initialFilter='Excel File (*.xls* *.xls)'
)
if self.t_file_path:
self.df_list_t = self.fn_listsheetData(self.t_file_path)
for sheet in self.df_list_t:
# self.t_listwidget.addItem(sheet.name)
self.t_listwidget.addItem(sheet)
# self.loadData(0)
self.label2.setText(self.t_file_path)
def fn_listsheetData(self, file_path):
self.wb_s = openpyxl.load_workbook(file_path)
# xlsm
# self.ws_s = self.wb_s["4대급여"]
self.ws_s = self.wb_s.worksheets[0]
# print(self.ws_s.title)
list_sheet_s = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_s.append(sh)
return list_sheet_s
# xlsx
# df_list = []
# with pd.ExcelFile(file_path) as wb:
# for indx, stn in enumerate(wb.sheet_names):
# try:
# df = pd.read_excel(wb, sheet_name=stn)
# except Exception as e:
# print('File read error:', e)
# else:
# df = df.fillna("")
# df.name = stn
# df_list.append(df)
# return df_list
def fn_listsheetData_t(self, file_path):
# xlsm
self.wb_t = openpyxl.load_workbook(file_path)
list_sheet_t = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_t.append(sh)
return list_sheet_t
def loadData(self, row):
try:
self.table.clear()
xl_File = []
self.ws_s = self.wb_s.worksheets[row] # xlsm
select_sheet_name = self.list_sheet_s[row]
# self.ws_s = self.wb_s.worksheets[0]
# self.df_s = pd.read_excel(self.wb_s, sheet_name=select_sheet_name)
self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name)
# self.ws_s = self.wb_s["4대급여"]
if self.df_s.size == 0:
return
self.df_s.fillna('',inplace=True)
self.table.setRowCount(self.df_s.shape[0])
self.table.setColumnCount(self.df_s.shape[1])
self.table.setHorizontalHeaderLabels(self.df_s.columns)
for row in self.df_s.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)
# QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
except:
return
def fn_df_save(self, t_row):
try:
# 파일의 시트리스트
self.list_sheet_t = self.fn_listsheetData_t(self.t_file_path)
xl_File = []
select_sheet_name_t = self.list_sheet_t[t_row]
# print(select_sheet_name_t)
# print(self.t_file_path)
ext_t = os.path.splitext(self.t_file_path)[1]
if ext_t == ".xlsx":
# self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name_t)
# self.df_t = self.df_list_t[t_row]
if self.df_s.size == 0:
return
# 원본
self.df_s.fillna('',inplace=True)
# self.df_s = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
wb_t = openpyxl.load_workbook(self.t_file_path)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
wb_t.remove(wb_t[self.t_listwidget.item(t_row).text()]) # 타겟 시트 삭제
wb_t.save(filename=self.t_file_path)
book = openpyxl.load_workbook(self.t_file_path)
writer = pd.ExcelWriter(self.t_file_path, engine = 'openpyxl')
writer.book = book
# for row in self.df.iterrows():
# values = row[1]
# for col_index, value in enumerate(values):
# if isinstance(value, (float, int)):
# value = '{0:0,.0f}'.format(value)
self.df_s.to_excel(writer, sheet_name=self.t_listwidget.item(t_row).text(),index=False)
writer.save()
else: # ext_t == ".xlsm":
# print(self.s_file_path)
# print(self.t_listwidget.item(self.t_row).text())
wb_s = openpyxl.load_workbook(self.s_file_path)
ws_s = wb_s[self.t_listwidget.item(t_row).text()]
wb_t = openpyxl.load_workbook(self.t_file_path, read_only=False, keep_vba=True)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# print(self.t_listwidget.item(t_row).text())
# calculate total number of rows and
# columns in source excel file
mr = ws_s.max_row
mc = ws_s.max_column
# copying the cell values from source
# excel file to destination excel file
for i in range (1, mr + 1):
for j in range (1, mc + 1):
# reading cell value from source excel file
c = ws_s.cell(row = i, column = j)
# print(c.value)
# writing the read value to destination excel file
ws_t.cell(row = i, column = j).value = c.value
# saving the destination excel file
wb_t.save(str(self.t_file_path))
QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
except:
return
if __name__ == '__main__':
app = QApplication(sys.argv)
app.setStyleSheet('''
QWidget {
font-size: 17px;
}
QHeaderView::section::Horizontal {
background-color: rgb(191, 255, 0);
}
''')
myApp = MyApp()
myApp.show()
try:
sys.exit(app.exec())
except SystemExit:
print('Closing Window...')exceltoexcel_xlsm02.py--------------------
# from msilib.schema import ListView
# import re
# from ssl import enum_certificates
import sys
from PyQt5.QtWidgets import QApplication, QWidget, QTableWidget, QTableWidgetItem, \
QHeaderView, QHBoxLayout, QVBoxLayout, QPushButton, QMessageBox, \
QFileDialog, QListWidget, QSizePolicy, QStyleFactory, \
QLineEdit, QLabel
from PyQt5.QtCore import Qt
from PyQt5.QtGui import QStandardItemModel, QStandardItem, QColor, QBrush
import pandas as pd # pip install pandas
# import win32com.client
import os
import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
# import warnings
# warnings.simplefilter("ignore", UserWarning)
# sys.coinit_flags = 2
# import pywinauto
# import pygetwindow as gw
from PyQt5.QtGui import QTextCursor
class MyApp(QWidget):
# class MyApp(QMainWindow):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 ######
layout_1 = QVBoxLayout()
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
s_open_btn.setMaximumWidth(300)
# s_open_btn.setFixedSize(200, 30)
# s_open_btn.setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
# s_open_btn.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Minimum)
self.s_listwidget = QListWidget(self)
# self.s_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.s_listwidget.setMaximumWidth(300)
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.s_listwidget)
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.setMaximumWidth(300)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
# self.t_listwidget.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Expanding)
self.t_listwidget.setMaximumWidth(300)
layout_1.addWidget(t_open_btn)
self.label2 = QLabel()
self.label2.setMaximumWidth(300)
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300; border-radius: 0px; ")
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
layout_main.addLayout(layout_1)
##### layout_2 ######
layout_2 = QVBoxLayout()
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
# 시그널 연결
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row)
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.fn_df_save(self.t_row)
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
# file_filter = 'Data file (*.xls *.csv *.dat);; Excel File (*.xls* *.xls)'
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
# file_filter = 'Excel File (*.xls* *.xls)'
file_filter = 'Excel File (*.xlsx *.xlsm)' # 실질적으로 표시되는 파일 형식
self.s_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
# directory=r'G:\python\vscode\pay',
directory=r'E:\python\vscode\pay\excel_data',
filter=file_filter,
# initialFilter='Excel File (*.xls* *.xls)' # 작동하는거랑 상관없음
)
if self.s_file_path:
# 파일의 시트리스트
self.list_sheet_s = self.fn_listsheetData(self.s_file_path)
for sheet in self.list_sheet_s:
# print(sheet)
self.s_listwidget.addItem(sheet)
self.loadData(0)
# self.initTableWidget(0)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
# file_filter = 'Excel File (*.xls* *.xls);;Data file (*.xls *.csv *.dat)'
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.t_file_path, ext = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
# directory=os.getcwd(),
directory=r'E:\python\vscode\pay\excel_data',
# directory=r'B:\python\vscode\excel',
filter=file_filter
# initialFilter='Excel File (*.xls *.xlsx)'
# initialFilter='Excel File (*.xls* *.xls)'
)
if self.t_file_path:
self.df_list_t = self.fn_listsheetData(self.t_file_path)
for sheet in self.df_list_t:
# self.t_listwidget.addItem(sheet.name)
self.t_listwidget.addItem(sheet)
# self.loadData(0)
self.label2.setText(self.t_file_path)
def fn_listsheetData(self, file_path):
self.wb_s = openpyxl.load_workbook(file_path)
# xlsm
# self.ws_s = self.wb_s["4대급여"]
self.ws_s = self.wb_s.worksheets[0]
# print(self.ws_s.title)
list_sheet_s = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_s.append(sh)
return list_sheet_s
# xlsx
# df_list = []
# with pd.ExcelFile(file_path) as wb:
# for indx, stn in enumerate(wb.sheet_names):
# try:
# df = pd.read_excel(wb, sheet_name=stn)
# except Exception as e:
# print('File read error:', e)
# else:
# df = df.fillna("")
# df.name = stn
# df_list.append(df)
# return df_list
def fn_listsheetData_t(self, file_path):
# xlsm
self.wb_t = openpyxl.load_workbook(file_path)
list_sheet_t = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_t.append(sh)
return list_sheet_t
def loadData(self, row):
try:
self.table.clear()
xl_File = []
self.ws_s = self.wb_s.worksheets[row] # xlsm
select_sheet_name = self.list_sheet_s[row]
# print(select_sheet_name)
# self.ws_s = self.wb_s.worksheets[0]
# self.df_s = pd.read_excel(self.wb_s, sheet_name=select_sheet_name)
self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name)
# print(self.df_s)
# self.ws_s = self.wb_s["4대급여"]
if self.df_s.size == 0:
return
self.df_s.fillna('',inplace=True)
self.table.setRowCount(self.df_s.shape[0])
self.table.setColumnCount(self.df_s.shape[1])
self.table.setHorizontalHeaderLabels(self.df_s.columns)
for row in self.df_s.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)
# QMessageBox.about(self,'원본파일열기','원본파일을 열었습니다.')
except:
return
def fn_df_save(self, t_row):
try:
# 파일의 시트리스트
self.list_sheet_t = self.fn_listsheetData_t(self.t_file_path)
xl_File = []
select_sheet_name_t = self.list_sheet_t[t_row]
# print(select_sheet_name_t)
# print(self.t_file_path)
ext_t = os.path.splitext(self.t_file_path)[1]
# if ext_t==".xlsx": # dataframe으로 처리하면 기존 양식 날아가서 작동안하게 함
# # self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name_t)
# # self.df_t = self.df_list_t[t_row]
# if self.df_s.size == 0:
# return
# # 원본
# self.df_s.fillna('',inplace=True)
# # self.df_s = self.df.round({"추가수당":0,"급여계":0,"차감지급액":0}) # 반올림
# wb_t = openpyxl.load_workbook(self.t_file_path)
# ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# wb_t.remove(wb_t[self.t_listwidget.item(t_row).text()]) # 타겟 시트 삭제
# wb_t.save(filename=self.t_file_path)
# book = openpyxl.load_workbook(self.t_file_path)
# writer = pd.ExcelWriter(self.t_file_path, engine = 'openpyxl')
# writer.book = book
# # for row in self.df.iterrows():
# # values = row[1]
# # for col_index, value in enumerate(values):
# # if isinstance(value, (float, int)):
# # value = '{0:0,.0f}'.format(value)
# self.df_s.to_excel(writer, sheet_name=self.t_listwidget.item(t_row).text(),index=False)
# writer.save()
# print(self.s_file_path)
# print(self.t_listwidget.item(self.t_row).text())
wb_s = openpyxl.load_workbook(self.s_file_path)
ws_s = wb_s[self.t_listwidget.item(t_row).text()]
if ext_t == ".xlsx":
wb_t = openpyxl.load_workbook(self.t_file_path)
else:
wb_t = openpyxl.load_workbook(self.t_file_path, read_only=False, keep_vba=True)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# print(self.t_listwidget.item(t_row).text())
# calculate total number of rows and
# columns in source excel file
mr = ws_s.max_row
mc = ws_s.max_column
# copying the cell values from source
# excel file to destination excel file
for i in range (1, mr + 1):
for j in range (1, mc + 1):
# reading cell value from source excel file
c = ws_s.cell(row = i, column = j)
# print(c.value)
# writing the read value to destination excel file
ws_t.cell(row = i, column = j).value = c.value
# saving the destination excel file
wb_t.save(str(self.t_file_path))
# else: # ext_t == ".xlsm":
# # print(self.s_file_path)
# # print(self.t_listwidget.item(self.t_row).text())
# wb_s = openpyxl.load_workbook(self.s_file_path)
# ws_s = wb_s[self.t_listwidget.item(t_row).text()]
# wb_t = openpyxl.load_workbook(self.t_file_path, read_only=False, keep_vba=True)
# ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# # print(self.t_listwidget.item(t_row).text())
# # calculate total number of rows and
# # columns in source excel file
# mr = ws_s.max_row
# mc = ws_s.max_column
# # copying the cell values from source
# # excel file to destination excel file
# for i in range (1, mr + 1):
# for j in range (1, mc + 1):
# # reading cell value from source excel file
# c = ws_s.cell(row = i, column = j)
# # print(c.value)
# # writing the read value to destination excel file
# ws_t.cell(row = i, column = j).value = c.value
# # saving the destination excel file
# wb_t.save(str(self.t_file_path))
QMessageBox.about(self,'파일 저장','파일 저장을 완료하였습니다.')
except:
return
if __name__ == '__main__':
app = QApplication(sys.argv)
app.setStyleSheet('''
QWidget {
font-size: 17px;
}
QHeaderView::section::Horizontal {
background-color: rgb(191, 255, 0);
}
''')
myApp = MyApp()
myApp.show()
try:
sys.exit(app.exec())
except SystemExit:
print('Closing Window...')pandas버전은 양식이 없어져서 주석처리로 비활성화하였습니다
xlsm→xlsx 시트 카피
xlsm→xlsm 시트 카피
import os
import pandas as pd
from PySide6.QtWidgets import (
QApplication, QWidget, QHBoxLayout, QVBoxLayout, QPushButton, QListWidget, QLabel,
QTableWidget, QTableWidgetItem, QFileDialog, QMessageBox
)
import openpyxl
class MyApp(QWidget):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 (좌측 레이아웃) ######
layout_1 = QVBoxLayout()
# 원본 엑셀 파일 열기 버튼
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
s_open_btn.setMaximumWidth(300)
self.s_listwidget = QListWidget(self)
self.s_listwidget.setMaximumWidth(300)
# 원본 파일 경로를 표시할 라벨 추가
self.label_s = QLabel()
self.label_s.setMaximumWidth(300)
self.label_s.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300;")
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.label_s) # 원본 파일 경로 라벨 추가
layout_1.addWidget(self.s_listwidget)
# 대상 엑셀 파일 선택 버튼
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.setMaximumWidth(300)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
self.t_listwidget.setMaximumWidth(300)
self.label2 = QLabel()
self.label2.setMaximumWidth(300)
self.label2.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: #FFC300;")
layout_1.addWidget(t_open_btn)
layout_1.addWidget(self.label2)
layout_1.addWidget(self.t_listwidget)
# 저장 버튼 추가
save_btn = QPushButton('저장', self)
save_btn.clicked.connect(self.fn_df_save)
layout_1.addWidget(save_btn) # layout_1에 저장 버튼 추가
layout_main.addLayout(layout_1)
##### layout_2 (우측 레이아웃) ######
layout_2 = QVBoxLayout()
# 원본/대상 구분 라벨 추가
self.label_table_type = QLabel("원본", self)
self.label_table_type.setStyleSheet("background-color: green; color: white; font-size: 16px; font-weight: bold;")
layout_2.addWidget(self.label_table_type)
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row, "원본")
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.loadData(self.t_row, "대상")
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.s_file_path, _ = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
filter=file_filter
)
if self.s_file_path:
# 시트 목록을 가져와서 리스트에 추가
self.list_sheet_s = self.fn_listsheetData(self.s_file_path)
for sheet in self.list_sheet_s:
self.s_listwidget.addItem(sheet)
# 첫 번째 시트를 자동 선택하고 배경을 노란색으로 변경
if self.list_sheet_s:
self.s_listwidget.setCurrentRow(0) # 첫 번째 시트 선택
self.loadData(0, "원본") # 첫 번째 시트 로드
# 선택된 원본 파일 경로 표시
self.label_s.setText(self.s_file_path)
def fn_t_clickOpenBtn(self):
self.label2.clear()
self.t_listwidget.clear()
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.t_file_path, _ = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
filter=file_filter
)
if self.t_file_path:
# 시트 목록을 가져와서 리스트에 추가
self.df_list_t = self.fn_listsheetData(self.t_file_path)
for sheet in self.df_list_t:
self.t_listwidget.addItem(sheet)
# 첫 번째 시트를 자동 선택
if self.df_list_t:
self.t_listwidget.setCurrentRow(0) # 첫 번째 시트 선택
self.label2.setText(self.t_file_path)
self.loadData(0, "대상") # 첫 번째 시트 로드
def fn_listsheetData(self, file_path):
self.wb_s = openpyxl.load_workbook(file_path)
list_sheet_s = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_s.append(sh)
return list_sheet_s
def loadData(self, row, table_type):
try:
self.table.clear()
if table_type == "원본":
select_sheet_name = self.list_sheet_s[row]
self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name)
self.df_s.fillna('', inplace=True)
self.label_table_type.setText("원본") # 원본 표시
self.label_table_type.setStyleSheet("background-color: green; color: white; font-size: 16px; font-weight: bold;") # 녹색 배경
else:
select_sheet_name = self.df_list_t[row]
self.df_s = pd.read_excel(self.t_file_path, sheet_name=select_sheet_name)
self.df_s.fillna('', inplace=True)
self.label_table_type.setText("대상") # 대상 표시
self.label_table_type.setStyleSheet("background-color: red; color: white; font-size: 16px; font-weight: bold;") # 빨간색 배경
self.table.setRowCount(self.df_s.shape[0])
self.table.setColumnCount(self.df_s.shape[1])
self.table.setHorizontalHeaderLabels(self.df_s.columns)
for row in self.df_s.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)
except Exception as e:
print(e)
return
def fn_df_save(self):
try:
t_row = self.t_listwidget.currentRow() # 현재 선택된 대상 시트 행
if t_row == -1:
QMessageBox.warning(self, "경고", "대상 시트를 선택하세요.")
return
self.list_sheet_t = self.fn_listsheetData(self.t_file_path)
select_sheet_name_t = self.list_sheet_t[t_row]
wb_s = openpyxl.load_workbook(self.s_file_path)
ws_s = wb_s[self.s_listwidget.item(self.s_listwidget.currentRow()).text()]
ext_t = os.path.splitext(self.t_file_path)[1]
if ext_t == ".xlsx":
wb_t = openpyxl.load_workbook(self.t_file_path)
else:
wb_t = openpyxl.load_workbook(self.t_file_path, read_only=False, keep_vba=True)
ws_t = wb_t[self.t_listwidget.item(t_row).text()]
# 원본 엑셀 파일의 제목 줄을 제외한 데이터 복사
mr = ws_s.max_row
mc = ws_s.max_column
# ws_t의 다음 빈 행 찾기
next_row = ws_t.max_row + 1
for i in range(2, mr + 1): # 제목 줄을 제외하기 위해 2부터 시작
for j in range(1, mc + 1):
c = ws_s.cell(row=i, column=j)
ws_t.cell(row=next_row, column=j).value = c.value
next_row += 1
wb_t.save(self.t_file_path) # 대상 파일 저장
# 대상 파일의 선택된 시트를 다시 로드하여 테이블 갱신
self.loadData(t_row, "대상")
QMessageBox.information(self, "성공", "저장이 완료되었습니다.")
except Exception as e:
print(e)
return
if __name__ == "__main__":
app = QApplication([])
window = MyApp()
window.show()
app.exec()
pyside6_exceltoexcelmodifysamefilesheet.py=============================
import os
import pandas as pd
from PySide6.QtWidgets import (
QApplication, QWidget, QHBoxLayout, QVBoxLayout, QPushButton, QListWidget, QLabel,
QTableWidget, QTableWidgetItem, QFileDialog, QMessageBox
)
import openpyxl
import datetime
class MyApp(QWidget):
def __init__(self):
super().__init__()
self.window_width, self.window_height = 1000, 500
self.resize(self.window_width, self.window_height)
##### layout_main ######
layout_main = QHBoxLayout()
self.setLayout(layout_main)
##### layout_1 (좌측 레이아웃) ######
layout_1 = QVBoxLayout()
# 원본 엑셀 파일 열기 버튼
s_open_btn = QPushButton('원본 엑셀 파일 열기', self)
s_open_btn.setMaximumWidth(300)
self.s_listwidget = QListWidget(self)
self.s_listwidget.setMaximumWidth(300)
# 원본 파일 경로를 표시할 라벨 추가
self.label_s = QLabel()
self.label_s.setMaximumWidth(300)
self.label_s.setText("원본파일 경로")
self.label_s.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: green;")
layout_1.addWidget(s_open_btn)
layout_1.addWidget(self.label_s) # 원본 파일 경로 라벨 추가
layout_1.addWidget(self.s_listwidget)
# 대상 엑셀 파일 선택 버튼
t_open_btn = QPushButton('대상 엑셀 파일 선택', self)
t_open_btn.setMaximumWidth(300)
t_open_btn.clicked.connect(self.fn_t_clickOpenBtn)
self.t_listwidget = QListWidget(self)
self.t_listwidget.setMaximumWidth(300)
self.label_t = QLabel()
self.label_t.setMaximumWidth(300)
self.label_t.setText("대상파일 경로")
self.label_t.setStyleSheet("color: #000000; border-style: solid; border-width: 2px; border-color: red;")
layout_1.addWidget(t_open_btn)
layout_1.addWidget(self.label_t)
layout_1.addWidget(self.t_listwidget)
# 저장 버튼 추가
save_btn = QPushButton('저장', self)
save_btn.clicked.connect(self.fn_df_save)
layout_1.addWidget(save_btn) # layout_1에 저장 버튼 추가
layout_main.addLayout(layout_1)
##### layout_2 (우측 레이아웃) ######
layout_2 = QVBoxLayout()
# 원본/대상 구분 라벨 추가
self.label_table_type = QLabel("원본", self)
self.label_table_type.setStyleSheet("background-color: green; color: white; font-size: 16px; font-weight: bold;")
layout_2.addWidget(self.label_table_type)
self.table = QTableWidget()
layout_2.addWidget(self.table)
layout_main.addLayout(layout_2)
s_open_btn.clicked.connect(self.fn_s_clickOpenBtn)
self.s_listwidget.itemDoubleClicked.connect(self.getItem_s)
self.t_listwidget.itemDoubleClicked.connect(self.getItem_t)
def getItem_s(self):
self.s_row = self.s_listwidget.currentRow()
self.loadData(self.s_row, "원본")
def getItem_t(self):
self.t_row = self.t_listwidget.currentRow()
self.loadData(self.t_row, "대상")
def fn_s_clickOpenBtn(self):
self.table.clear()
self.s_listwidget.clear()
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.s_file_path, _ = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
filter=file_filter
)
if self.s_file_path:
# 시트 목록을 가져와서 리스트에 추가
self.list_sheet_s = self.fn_listsheetData(self.s_file_path)
for sheet in self.list_sheet_s:
self.s_listwidget.addItem(sheet)
# 첫 번째 시트를 자동 선택하고 배경을 노란색으로 변경
if self.list_sheet_s:
self.s_listwidget.setCurrentRow(0) # 첫 번째 시트 선택
self.loadData(0, "원본") # 첫 번째 시트 로드
# 선택된 원본 파일 경로 표시
self.label_s.setText(self.s_file_path)
def fn_t_clickOpenBtn(self):
self.label_t.clear()
self.t_listwidget.clear()
file_filter = 'Excel File (*.xlsx *.xlsm)'
self.t_file_path, _ = QFileDialog.getOpenFileName(
parent=self,
caption='파일 열기',
filter=file_filter
)
if self.t_file_path:
# 시트 목록을 가져와서 리스트에 추가
self.df_list_t = self.fn_listsheetData(self.t_file_path)
for sheet in self.df_list_t:
self.t_listwidget.addItem(sheet)
# 첫 번째 시트를 자동 선택
if self.df_list_t:
self.t_listwidget.setCurrentRow(0) # 첫 번째 시트 선택
self.label_t.setText(self.t_file_path)
self.loadData(0, "대상") # 첫 번째 시트 로드
def fn_listsheetData(self, file_path):
self.wb_s = openpyxl.load_workbook(file_path)
list_sheet_s = []
with pd.ExcelFile(file_path) as wb:
for sh in wb.sheet_names:
list_sheet_s.append(sh)
return list_sheet_s
def loadData(self, row, table_type):
try:
self.table.clear()
if table_type == "원본":
select_sheet_name = self.list_sheet_s[row]
self.df_s = pd.read_excel(self.s_file_path, sheet_name=select_sheet_name)
# NaN 값을 빈 문자열로 변경하기 전에 문자열로 변환하여 dtype 문제를 해결
self.df_s = self.df_s.astype(str).replace('nan', '')
self.label_table_type.setText("원본") # 원본 표시
self.label_table_type.setStyleSheet("background-color: green; color: white; font-size: 16px; font-weight: bold;") # 녹색 배경
else:
select_sheet_name = self.df_list_t[row]
self.df_s = pd.read_excel(self.t_file_path, sheet_name=select_sheet_name)
# NaN 값을 빈 문자열로 변경하기 전에 문자열로 변환하여 dtype 문제를 해결
self.df_s = self.df_s.astype(str).replace('nan', '')
self.label_table_type.setText("대상") # 대상 표시
self.label_table_type.setStyleSheet("background-color: red; color: white; font-size: 16px; font-weight: bold;") # 빨간색 배경
self.table.setRowCount(self.df_s.shape[0])
self.table.setColumnCount(self.df_s.shape[1])
self.table.setHorizontalHeaderLabels(self.df_s.columns)
for row_index, row_data in self.df_s.iterrows():
for col_index, value in enumerate(row_data):
tableItem = QTableWidgetItem(str(value))
self.table.setItem(row_index, col_index, tableItem)
except Exception as e:
print(e)
return
def fn_df_save(self):
try:
# 원본 파일 경로가 없을 때 경고 메시지
if not self.label_s.text() or self.label_s.text() == "원본파일 경로":
QMessageBox.warning(self, "경고", "원본 파일을 선택하세요.")
return
# 대상 파일 경로가 없을 때 경고 메시지
if not self.label_t.text() or self.label_t.text() == "대상파일 경로":
QMessageBox.warning(self, "경고", "대상 파일을 선택하세요.")
return
t_row = self.t_listwidget.currentRow() # 현재 선택된 대상 시트 행
if t_row == -1:
QMessageBox.warning(self, "경고", "대상 시트를 선택하세요.")
return
# 원본과 대상 파일, 시트 정보 가져오기
self.list_sheet_t = self.fn_listsheetData(self.t_file_path)
# 선택된 원본 시트 이름과 대상 시트 이름
selected_sheet_s = self.s_listwidget.item(self.s_listwidget.currentRow()).text()
selected_sheet_t = self.t_listwidget.item(t_row).text()
# 원본과 대상 시트가 동일한 경우 경고 메시지
if self.s_file_path == self.t_file_path and selected_sheet_s == selected_sheet_t:
QMessageBox.warning(self, "경고", "같은 파일의 같은 시트는 데이터를 보낼 수 없습니다.")
return
wb_s = openpyxl.load_workbook(self.s_file_path)
ws_s = wb_s[selected_sheet_s]
ext_t = os.path.splitext(self.t_file_path)[1]
if ext_t == ".xlsx":
wb_t = openpyxl.load_workbook(self.t_file_path)
else:
wb_t = openpyxl.load_workbook(self.t_file_path, read_only=False, keep_vba=True)
ws_t = wb_t[selected_sheet_t]
# 원본 엑셀 파일의 제목 줄을 제외한 데이터 복사
mr = ws_s.max_row
mc = ws_s.max_column
# ws_t의 다음 빈 행 찾기
next_row = ws_t.max_row + 1
for i in range(2, mr + 1): # 제목 줄을 제외하기 위해 2부터 시작
for j in range(1, mc + 1):
c = ws_s.cell(row=i, column=j)
if isinstance(c.value, (datetime.date, datetime.datetime)):
# 날짜 형식만 유지해서 저장
ws_t.cell(row=next_row, column=j).value = c.value.strftime('%Y-%m-%d')
else:
ws_t.cell(row=next_row, column=j).value = c.value
next_row += 1
# 대상 파일 저장
wb_t.save(self.t_file_path)
wb_t.close()
wb_s.close()
# 대상 파일의 선택된 시트를 다시 로드하여 테이블 갱신
self.loadData(t_row, "대상")
QMessageBox.information(self, "성공", "저장이 완료되었습니다.")
except Exception as e:
print(e)
return
if __name__ == '__main__':
app = QApplication([])
window = MyApp()
window.show()
app.exec()'컴퓨터 > Python' 카테고리의 다른 글
| class101 timepercent 따라하기 (0) | 2022.10.03 |
|---|---|
| xlsxwriter dataframe image 넣기 (0) | 2022.09.30 |
| pyqt5 qlistwidget 바로바로 표시하기 (0) | 2022.09.23 |
| python 파일 합치기 (0) | 2022.09.14 |
| python selenium chromedriver 다운 안받아도 되네요 (0) | 2022.09.08 |