python EXcel表整合（自动办公）

收到任务，方便领导快速查看每个人的日程安排。比如每个工程师都有一个自己的表格记录自己的日程安排，领导查看每个人的日程安排需要一一打开所有工程师的日程安排表才能知道每个人的信息，而且非常不直观。
这里介绍下利用python解决实际办公问题：
这里用到pandas 库处理Excel，pyqt5做图形界面方便使用操作。
pyqt界面代码：

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'FSE.ui'
#
# Created by: PyQt5 UI code generator 5.10
#
# WARNING! All changes made in this file will be lost!

from PyQt5 import QtCore, QtGui, QtWidgets
from read_excel import *
from PyQt5.QtWidgets import *
import sys
import threading
from PyQt5.QtCore import *

import image_rc

class Ui_WB(QWidget):
    signal = pyqtSignal(str, str)
    def setupUi(self, WB):
        WB.setObjectName("WB")
        WB.resize(420, 239)
        icon = QtGui.QIcon()
        icon.addPixmap(QtGui.QPixmap(":/icon/SKF.ico"), QtGui.QIcon.Normal, QtGui.QIcon.Off)
        WB.setWindowIcon(icon)
        self.mainwindows = WB
        self.centralwidget = QtWidgets.QWidget(WB)
        self.signal.connect(self.show_Message)
        self.centralwidget.setObjectName("centralwidget")
        self.horizontalLayout = QtWidgets.QHBoxLayout(self.centralwidget)
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.gridLayout = QtWidgets.QGridLayout()
        self.gridLayout.setObjectName("gridLayout")
        self.lineEdit_4 = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit_4.setMaximumSize(QtCore.QSize(16777215, 16777215))
        self.lineEdit_4.setObjectName("lineEdit_4")
        self.gridLayout.addWidget(self.lineEdit_4, 1, 1, 1, 1, QtCore.Qt.AlignLeft)
        self.label_5 = QtWidgets.QLabel(self.centralwidget)
        self.label_5.setObjectName("label_5")
        self.gridLayout.addWidget(self.label_5, 0, 0, 1, 1, QtCore.Qt.AlignRight)
        self.dateEdit_4 = QtWidgets.QDateEdit(self.centralwidget)
        self.dateEdit_4.setMaximumSize(QtCore.QSize(120, 16777215))
        self.dateEdit_4.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 12, 31), QtCore.QTime(0, 0, 0)))
        self.dateEdit_4.setObjectName("dateEdit_4")
        self.gridLayout.addWidget(self.dateEdit_4, 3, 1, 1, 1, QtCore.Qt.AlignLeft)
        self.label_6 = QtWidgets.QLabel(self.centralwidget)
        self.label_6.setObjectName("label_6")
        self.gridLayout.addWidget(self.label_6, 1, 0, 1, 1, QtCore.Qt.AlignRight)
        self.pushButton_4 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_4.setMaximumSize(QtCore.QSize(20, 20))
        self.pushButton_4.setObjectName("pushButton_4")
        self.gridLayout.addWidget(self.pushButton_4, 0, 2, 1, 1, QtCore.Qt.AlignLeft)
        self.dateEdit_3 = QtWidgets.QDateEdit(self.centralwidget)
        self.dateEdit_3.setMaximumSize(QtCore.QSize(120, 16777215))
        self.dateEdit_3.setDateTime(QtCore.QDateTime(QtCore.QDate(2020, 1, 1), QtCore.QTime(0, 0, 0)))
        self.dateEdit_3.setObjectName("dateEdit_3")
        self.gridLayout.addWidget(self.dateEdit_3, 2, 1, 1, 1, QtCore.Qt.AlignLeft)
        self.pushButton_5 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_5.setMaximumSize(QtCore.QSize(20, 20))
        self.pushButton_5.setObjectName("pushButton_5")
        self.gridLayout.addWidget(self.pushButton_5, 1, 2, 1, 1, QtCore.Qt.AlignLeft)
        self.label_8 = QtWidgets.QLabel(self.centralwidget)
        self.label_8.setObjectName("label_8")
        self.gridLayout.addWidget(self.label_8, 3, 0, 1, 1, QtCore.Qt.AlignRight)
        self.lineEdit_3 = QtWidgets.QLineEdit(self.centralwidget)
        self.lineEdit_3.setObjectName("lineEdit_3")
        self.gridLayout.addWidget(self.lineEdit_3, 0, 1, 1, 1, QtCore.Qt.AlignLeft)
        self.label_7 = QtWidgets.QLabel(self.centralwidget)
        self.label_7.setObjectName("label_7")
        self.gridLayout.addWidget(self.label_7, 2, 0, 1, 1, QtCore.Qt.AlignRight)
        self.pushButton_6 = QtWidgets.QPushButton(self.centralwidget)
        self.pushButton_6.setObjectName("pushButton_6")
        self.gridLayout.addWidget(self.pushButton_6, 4, 1, 1, 1, QtCore.Qt.AlignHCenter)
        self.horizontalLayout.addLayout(self.gridLayout)
        WB.setCentralWidget(self.centralwidget)
        self.statusbar = QtWidgets.QStatusBar(WB)
        self.statusbar.setObjectName("statusbar")
        WB.setStatusBar(self.statusbar)

        self.retranslateUi(WB)
        QtCore.QMetaObject.connectSlotsByName(WB)

    def retranslateUi(self, WB):
        _translate = QtCore.QCoreApplication.translate
        WB.setWindowTitle(_translate("WB", "WB"))
        self.label_5.setText(_translate("WB", "文件源："))
        self.label_6.setText(_translate("WB", "输出地址："))
        self.pushButton_4.setText(_translate("WB", "..."))
        self.pushButton_5.setText(_translate("WB", "..."))
        self.label_8.setText(_translate("WB", "结束时间："))
        self.label_7.setText(_translate("WB", "起始时间："))
        self.pushButton_6.setText(_translate("WB", "开始输出"))
        self.click_event()

    def click_event(self):

        self.pushButton_5.clicked.connect(
            lambda: self.search_path(2))
        self.pushButton_4.clicked.connect(
            lambda: self.search_path(1))
        self.pushButton_6.clicked.connect(self.output)

    def threading_event(self):
        t = threading.Thread(target=self.output)  # 创建线程
        t.setDaemon(True)  # 设置为后台线程，这里默认是False，设置为True之后则主线程不用等待子线程
        t.start()  # 开启线程
    def output(self):
        # 使用：
        try:

            input_path = self.lineEdit_3.text()
            output_path = self.lineEdit_4.text()
            if input_path == '' or output_path == '':
                self.signal.emit('提示', "请输入完整路径！")
                return

            filename_list = get_file_name_list(input_path)
            data_df = pd.DataFrame()

            for file_name in filename_list:
                try:
                    print(file_name)
                    datadf = read_excel(file_name)
                    data_df = data_df.append(datadf)
                    # data_df = pd.merge(data_df, datadf)
                except Exception as e:
                    print(e)
            print(data_df)
            # start_date = datetime(2020, 8, 1)
            # end_date = datetime(2020, 8, 26)
            start_date = self.dateEdit_3.date().toPyDate()
            end_date = self.dateEdit_4.date().toPyDate()
            if end_date < start_date:
                self.signal.emit('提示', "请输入正确的时间段！！！")
            else:
                write_data(output_path, data_df, start_date, end_date)
                self.signal.emit('提示', "输出成功！！！")
        except Exception as e:
            self.signal.emit('提示', "输出失败！！！\n" + '原因：' +str(e))
            print(e)

    def search_path(self,tag):
        try:
            directory = QFileDialog.getExistingDirectory(self.mainwindows, "选择路径", "",QFileDialog.DontUseNativeDialog)
            directory = QFileDialog.geto

            # 当窗口非继承QDialog时，self可替换成 None
            if tag == 1:
                if type(directory) == tuple:
                    self.lineEdit_3.setText(directory[0])
                else:
                    self.lineEdit_3.setText(directory)
            else:
                if type(directory) == tuple:
                    self.lineEdit_4.setText(directory[0])
                else:
                    self.lineEdit_4.setText(directory)
        except Exception as e:
            print(e)
    def show_Message(self,title,content):
        QMessageBox.about(self.mainwindows, title, content)

if __name__ =='__main__':

    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_WB()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

读取Excel信息（read_excel.py）：

from write_excel import *

def get_file_name_list(file_dir):
    '''
    :brief:获取文件夹下内，所有文件
    :param file_dir:文件夹目录
    :return: 文件列表
    '''
    root = ''
    dirs = ''
    files = ''
    for root,dirs,files in os.walk(file_dir):
        break
    file_list = []
    for file in files:
        file_path = (os.path.join(root, file))
        file_list.append(file_path)
    return file_list

def read_excel(path):
    try:
        pd.set_option('display.max_columns', None)
        # 显示所有行
        pd.set_option('display.max_rows', None)
        data = pd.read_excel(path, sheet_name = 0)
        # for index, row in data.iterrows():
            # print(row)
        # print(data)
        return data
    except Exception as e:
        print(e)

生成新Excel输出（write_excel.py）

import pandas as pd
from  datetime import datetime,timedelta
from colum_to_name import column_to_name
import os

def write_data(path,input_df,start_date,end_date):
    start_date = datetime.combine(start_date, datetime.min.time())
    end_date = datetime.combine(end_date, datetime.min.time())
    start_date_str = start_date.strftime('%Y%m%d')
    end_date_str = end_date.strftime('%Y%m%d')
    # print(start_date_str,end_date_str)
    days = pd.date_range(start_date_str, end_date_str, freq='1D')

    # print(days.tolist())
    date_list = days.tolist()
    columns = []
    weeks = []
    for dateTime in date_list:
        # print(type(dateTime))
        date1 = dateTime.to_pydatetime()
        # print(type(date1))
        columns.append(date1.strftime("%y/%m/%d"))
        # print(date1.weekday())
        week_day = {
            0: '星期一',
            1: '星期二',
            2: '星期三',
            3: '星期四',
            4: '星期五',
            5: '星期六',
            6: '星期日',
        }
        weeks.append(week_day[date1.weekday()])

    name = ['星期']
    df1 = pd.DataFrame(index=name,columns = columns)  # 随机生成一个DataFrame 数据
    for index,colums in enumerate(df1.columns):
        # print(index,colums)
        df1.loc['星期', colums] = weeks[index]
    # print(df1)


    t = datetime.now().date() - timedelta(days=1)

    path = (os.path.join(path, ('FSE scheduling%d%02d%02d.xlsx' % (t.year, t.month, t.day))))

    writer = pd.ExcelWriter(path)
    workbook = writer.book
    fmt = workbook.add_format({"font_name": u"微软雅黑"})
    percent_fmt = workbook.add_format({'num_format': '0.00%'})
    red_format = workbook.add_format({'bg_color': 'red'})
    red_format2 = workbook.add_format({'bg_color': '#FFC0CB'})
    yellow_format1 = workbook.add_format({'bg_color': '#FF8247'})
    yellow_format2 = workbook.add_format({'bg_color': '#FFFACD'})
    green_format1 = workbook.add_format({'bg_color': '#90EE90'})
    green_format2 = workbook.add_format({'bg_color': '#7FFFD4'})

    # 填充图案样式
    patternType1 = workbook.add_format({'pattern': 2})
    patternType3 = workbook.add_format({'pattern': 3})
    patternType4 = workbook.add_format({'pattern': 4})
    patternType5 = workbook.add_format({'pattern': 5})
    patternType6 = workbook.add_format({'pattern': 6})
    patternType7 = workbook.add_format({'pattern': 7})
    patternType8 = workbook.add_format({'pattern': 8})
    patternType9 = workbook.add_format({'pattern': 9})
    patternType10 = workbook.add_format({'pattern': 10})
    patternType11= workbook.add_format({'pattern': 11})
    patternType12 = workbook.add_format({'pattern': 12})
    patternType13 = workbook.add_format({'pattern': 13})
    patternType14 = workbook.add_format({'pattern': 14})

    border_format = workbook.add_format({'border': 1})
    note_fmt = workbook.add_format(
        {'bold': True, 'font_name': u'微软雅黑', 'font_color': 'red', 'align': 'left', 'valign': 'vcenter'})
    date_fmt = workbook.add_format({'bold': False, 'font_name': u'微软雅黑', 'num_format': 'yyyy-mm-dd'})

    date_fmt1 = workbook.add_format(
        {'bold': True, 'font_size': 10, 'font_name': u'微软雅黑', 'num_format': 'yyyy-mm-dd', 'bg_color': '#9FC3D1',
         'valign': 'vcenter', 'align': 'center'})
    highlight_fmt = workbook.add_format({'bg_color': '#FFD7E2', 'num_format': '0.00%'})


    for index, row in input_df.iterrows():
        # print(row)
        # print(type(row['计划开始时间']))
        try:
            date1 = (row['计划开始时间']).to_pydatetime()
            date2 = (row['计划结束时间']).to_pydatetime()
            if date1 > end_date or date2 < start_date:
                continue
            elif date1 < start_date and date2 >= start_date:
                date1 = start_date
            elif date1 <= end_date and date2 > end_date:
                date2 = end_date
            else:
                pass
            colum = date1.strftime("%y/%m/%d")
            df1.loc[row['姓名'], colum] = row['地点'] + '-' + row['服务类型'] + '-' + row['客户']
        except Exception as e:
            print(e)

    rows_list = (df1._stat_axis.values.tolist())
    df = df1

    l_end = len(df.index) + 1  # 表格的行数,便于下面设置格式

    df.to_excel(writer, sheet_name=u'测试页签', encoding='utf8', header=True, index=True)
    worksheet1 = writer.sheets[u'测试页签']

    for index, row in input_df.iterrows():
        try:
            date1 = (row['计划开始时间']).to_pydatetime()
            date2 = (row['计划结束时间']).to_pydatetime()
            if date1 > end_date or date2 < start_date:
                continue
            elif date1 < start_date and date2 >= start_date:
                date1 = start_date
            elif date1 <= end_date and date2 > end_date:
                date2 = end_date
            else:
                pass
            colum_num = (date1 - start_date).days + 2
            column_name_start = column_to_name(colum_num)

            if date2 > end_date:
                date2 = end_date
            colum_num = (date2 - start_date).days + 2
            column_name_end = column_to_name(colum_num)

            # colum = date1.strftime("%y/%m/%d")
            row1 = rows_list.index(row['姓名']) + 2

            worksheet1.conditional_format('{}{}:{}{}'.format(column_name_start,row1,column_name_end,row1), {'type': 'no_blanks', 'format': green_format2})
            worksheet1.conditional_format('{}{}:{}{}'.format(column_name_start, row1, column_name_end, row1),
                                          {'type': 'blanks', 'format': green_format2})

            date1 = (row['实际开始时间']).to_pydatetime()
            date2 = (row['实际结束时间']).to_pydatetime()

            if date1 > end_date or date2 < start_date:
                continue
            elif date1 < start_date and date2 >= start_date:
                date1 = start_date
            elif date1 <= end_date and date2 > end_date:
                date2 = end_date
            else:
                pass
            colum_num2 = (date1 - start_date).days + 2
            column_name_start2 = column_to_name(colum_num2)


            colum_num2 = (date2 - start_date).days + 2
            column_name_end2 = column_to_name(colum_num2)

            worksheet1.conditional_format('{}{}:{}{}'.format(column_name_start2, row1, column_name_end2, row1),
                                          {'type': 'no_blanks', 'format': patternType14})
            worksheet1.conditional_format('{}{}:{}{}'.format(column_name_start2, row1, column_name_end2, row1),
                                          {'type': 'blanks', 'format': patternType14})
        except Exception as e:
            print(e)

    # for col_num, value in enumerate(df.columns.values):
    #     worksheet1.write(1, col_num, value, date_fmt1)
    # worksheet1.merge_range('A1:B1', u'测试情况统计表', note_fmt)
    # 设置列宽
    # worksheet1.set_column('B:H%d' % l_end, 30, fmt)

    num = (end_date - start_date).days + 2
    # print(end_date,start_date)
    # print(num)
    column_name = column_to_name(num)

    # print(column_name)

    # worksheet1.set_column('B:H%d' % l_end, 25, fmt)
    worksheet1.set_column('B:{}{}'.format(column_name,l_end), 10, fmt)
    # 有条件设定表格格式：周六日列
    worksheet1.conditional_format('B2:{}{}'.format(column_name,l_end), {'type': 'text', 'criteria': 'containing',
                                                                        'value': '星期六', 'format': red_format2})
    worksheet1.conditional_format('B2:{}{}'.format(column_name, l_end), {'type': 'text', 'criteria': 'containing',
                                                                         'value': '星期日', 'format': red_format2})

    # # 有条件设定表格格式：百分比
    # worksheet1.conditional_format('E3:H%d' % l_end,
    #                               {'type': 'cell', 'criteria': '<=', 'value': 0.1, 'format': percent_fmt})
    # # 有条件设定表格格式：高亮百分比
    # worksheet1.conditional_format('E3:H%d' % l_end,
    #                               {'type': 'cell', 'criteria': '>', 'value': 0.1, 'format': highlight_fmt})
    # 加边框
    worksheet1.conditional_format('A1:{}{}'.format(column_name,l_end), {'type': 'no_blanks', 'format': border_format})
    worksheet1.conditional_format('A1:{}{}'.format(column_name, l_end), {'type': 'blanks','format': border_format})

    # 设置日期格式
    worksheet1.conditional_format('A3:A62', {'type': 'no_blanks', 'format': date_fmt})
    writer.save()