引
在实际工作场景,合并同一表头的表格,之后再根据合并表格中的某一字段拆分是十分常见的工作流程,但是当前 Excel 并没有比较简单的解决方案,为了解决这一问题,笔者编写了对应的Python小工具,并使用 PyQt5 开发了可视化界别,代码如下:
import sys
import os
import pandas as pd
from PyQt5.QtWidgets import QApplication, QWidget, QLabel, QPushButton, \
QGridLayout, QVBoxLayout, QHBoxLayout, QFileDialog, QComboBox , QMessageBox
class CombineAndSplit(QWidget):
def __init__(self):
super(CombineAndSplit, self).__init__()
#设置标题
self.setWindowTitle("Excel合并拆分工具")
#界面大小
self.resize(400, 200)
#界面设置,布置网格
self.grid_layout = QGridLayout()
self.h_layout = QHBoxLayout()
self.v_layout = QVBoxLayout()
# 功能选择按钮
self.choose_Button = QPushButton('合并数据并拆分', self)
self.choose_Button.clicked.connect(self.change1)
# 打开文件夹或文件
self.open_button = QPushButton('打开文件夹', self)
self.open_button.clicked.connect(self.open)
# 显示文件路径或文件名
self.file = QLabel()
self.file.setWordWrap(True)
#下拉框设置
self.combox = QComboBox()
self.combox.addItem('含拆分模式请选择字段拆分')
# 保存位置按钮
self.save = QPushButton('保存文件夹位置',self)
self.save.clicked.connect(self.savefile)
#保存位置显示
self.savefile_path = QLabel()
self.savefile_path.setWordWrap(True)
#增加执行按钮
self.go = QPushButton('执行',self)
self.go.clicked.connect(self.pandas_go)
#按钮位置安排
self.grid_layout.addWidget(self.choose_Button, 0,0,1,2)
self.grid_layout.addWidget(self.open_button, 1,0,1,2)
self.grid_layout.addWidget(self.file, 2,0,1,2)
self.grid_layout.addWidget(self.save, 3,0,1,1)
self.grid_layout.addWidget(self.savefile_path,3,1,1,1)
self.grid_layout.addWidget(self.combox, 4,0,1,1)
self.grid_layout.addWidget(self.go, 4,1,1,1)
self.v_layout.addLayout(self.grid_layout)
self.setLayout(self.v_layout)
# 功能按钮变化
def change1(self):
if self.choose_Button.text() == '合并数据并拆分':
self.choose_Button.setText('合并数据')
self.combox.clear()
self.combox.addItem('含拆分模式请选择字段拆分')
elif self.choose_Button.text() == '合并数据':
self.choose_Button.setText('拆分数据')
self.open_button.setText('打开Excel文件')
self.combox.clear()
self.combox.addItem('含拆分模式请选择字段拆分')
else:
self.choose_Button.setText('合并数据并拆分')
self.open_button.setText('打开文件夹')
def open(self):
if self.open_button.text() == '打开文件夹':
directory1 = QFileDialog.getExistingDirectory(self,"选取文件夹","./")
self.file.setText(directory1)
if self.choose_Button.text() == '合并数据':
self.combox.clear()
self.combox.addItem('无需选择拆分字段')
elif self.choose_Button.text() == '合并数据并拆分':
# 读取表头字段
fileList = os.listdir(directory1)
if len(fileList)>1:
temp = pd.read_excel(directory1 + '/' + fileList[0] ,dtype=str)
headlist = list(temp.head(0))
self.combox.clear()
self.combox.addItems(headlist)
return directory1
else:
directory1 = QFileDialog.getOpenFileNames(self,'选取文件',"./","EXcel Files (*.xlsx;*.xls)")
self.file.setText(";".join(directory1[0]))
#self.combox.addItem('请选择拆分字段')
# 读取表头字段
if directory1[0]:
temp = pd.read_excel(directory1[0][0],dtype=str)
headlist = list(temp.head(0))
self.combox.clear()
self.combox.addItems(headlist)
return directory1
def savefile(self):
directory2 = QFileDialog.getExistingDirectory(self,"选取文件夹","./")
self.savefile_path.setText(directory2)
return directory2
def pandas_go(self):
def save_file(df, Keyword):
headname = list(df[Keyword].unique())[0]
df.to_excel(self.savefile_path.text() +'/'+headname+".xlsx", index=None)
def save_file1(df, Keyword, TableName):
headname = list(df[Keyword].unique())[0]
df.to_excel(self.savefile_path.text() +'/'+TableName+headname+".xlsx", index=None)
if self.choose_Button.text() == '合并数据并拆分' and self.file.text() and self.combox.currentText() and self.savefile_path.text():
filelist1 = os.listdir(self.file.text())
main = pd.DataFrame()
for file in filelist1:
try:
sub = pd.read_excel(self.file.text() + '/' + file)
main = pd.concat([main,sub])
except:
print('存在非 Excel 文件')
Keyword = self.combox.currentText()
main.groupby(Keyword).apply(save_file,(Keyword))
QMessageBox.information(self, '消息', '已完成合并拆分')
elif self.choose_Button.text() == '合并数据' and self.file.text() and self.savefile_path.text():
filelist1 = os.listdir(self.file.text())
main = pd.DataFrame()
for file in filelist1:
try:
sub = pd.read_excel(self.file.text() + '/' + file)
main = pd.concat([main,sub])
except:
print('存在非 Excel 文件')
main.to_excel(self.savefile_path.text() +'/合并结果.xlsx', index=None)
QMessageBox.information(self, '消息', '已完成合并')
elif self.choose_Button.text() == '拆分数据' and self.file.text() and self.combox.currentText() and self.savefile_path.text():
for file in self.file.text().split(';'):
main = pd.read_excel(file)
Keyword = self.combox.currentText()
TableName_file = file.split('/')[-1].split('.')[0]
main.groupby(Keyword).apply(save_file1,Keyword = Keyword,TableName =TableName_file)
QMessageBox.information(self, '消息', '已完成拆分')
if __name__ == '__main__':
app = QApplication(sys.argv)
demo = CombineAndSplit()
demo.show()
sys.exit(app.exec_())