前段时间工作中遇到需要将多个excel合并,且每个excel都有多个sheet页
在CSDN也没搜到特别简洁的代码,其实就两个循环的事情
不过还好,站在巨人的肩膀上看的远,就集各家所长,为己所用
顺带写了带图形界面的,顺带用PyQt5打了个包,上代码
- import sys
- from PyQt5 import QtCore, QtWidgets, QtGui
- from PyQt5.QtWidgets import *
- import pandas as pd
- import os
- import xlrd
- def excel_merge(path, col, out):
- fns = os.listdir(path)
- dfs = pd.DataFrame()
- for fn in fns:
- excel_path = path + fn
- wb = xlrd.open_workbook(excel_path)
- sheets = wb.sheets()
- for sheet in sheets:
- df = pd.read_excel(excel_path, sheet_name=sheet, index=False)
- new_df = df.loc[:, col]
- dfs = dfs.append(new_df)
- dfs.dropna(axis=0, how='any', inplace=True)
- dfs.to_excel(out, index=None)
- class Test():
- def window(self, w):
- # 设置主窗口的坐标和大小
- w.setGeometry(500, 300, 800, 300)
- w.setWindowTitle('Excel文件批量合并')
- w.setWindowIcon(QtGui.QIcon('1.jpg'))
- # 设置文本和输入框的 坐标以及大小
- self.intext = QtWidgets.QLabel(w)
- self.intext.setGeometry(QtCore.QRect(60, 20, 120, 45))
- self.intext.setText('源文件路径:')
- self.inbtn = QtWidgets.QLineEdit(w)
- self.inbtn.setGeometry(QtCore.QRect(200, 30, 500, 30))
- # 设置文本和输入框的 坐标以及大小
- self.outext = QtWidgets.QLabel(w)
- self.outext.setGeometry(QtCore.QRect(60, 70, 120, 45))
- self.outext.setText('新文件路径:')
- self.outbtn = QtWidgets.QLineEdit(w)
- self.outbtn.setGeometry(QtCore.QRect(200, 80, 500, 30))
- # 设置文本和输入框的 坐标以及大小
- self.coltext = QtWidgets.QLabel(w)
- self.coltext.setGeometry(QtCore.QRect(60, 120, 120, 45))
- self.coltext.setText('需保留的列:')
- self.colbtn = QtWidgets.QLineEdit(w)
- self.colbtn.setGeometry(QtCore.QRect(200, 130, 500, 30))
- # 添加按钮坐标 和大小, 绑定按钮点击事件
- self.subtn = QtWidgets.QPushButton(w)
- self.subtn.setGeometry(QtCore.QRect(350, 200, 100, 30))
- self.subtn.setText('执行合并')
- self.subtn.clicked.connect(self.merge)
- self.subtn.clicked.connect(w.close)
- w.show()
- def merge(self):
- path = self.inbtn.text()
- col = list(self.colbtn.text().split(','))
- out = self.outbtn.text()
- excel_merge(path, col, out)
- # 设置警告弹窗内容
- app = QApplication(sys.argv)
- msg_box = QMessageBox(QMessageBox.Warning, '通知', '合并完成')
- app.exit(msg_box.exec_())
- if __name__ == '__main__':
- app = QtWidgets.QApplication(sys.argv)
- w = QtWidgets.QWidget()
- ui = Test()
- ui.window(w)
- sys.exit(app.exec_())
联系客服