Python实现Excel办公自动化

Python实现Excel办公自动化

  在工作中难免遇到统计表格，汇总表格，数据查重等。可根据自己的需求进行更改，代码书写的初衷主要是想解法双手，摆脱重复机械的ctrl+C，ctrl+V，完成数据的批量替换，查找不同表格的相同位置，用代码的1秒钟代替你一小时的复制粘贴。

1 开发环境：

Python3.6及以上
pandas，matplotlib：数据分析及可视化
xlwings：配合系统的office，完成功能扩展

2 具体功能及实现代码

1. 批量新建Excel文件

import xlwings as xw

app=xw.App(visible=True,add_book=False)

for grade in ["一年级","二年级","三年级","四年级"]:
    workbook=app.books.add()
    workbook.save(f"./信息统计-{grade}.xlsx")

2. 批量打开Excel文件

import os
import xlwings as xw

app=xw.App(visible=True,add_book=False)

for file in os.listdir("."):
    if file.endswith(".xlsx"):
        app.books.open(file)

将会打开".xlsx"结尾的文件：

3. 批量重命名Excel工作表

import xlwings as xw

app=xw.App(visible=False,add_book=False)

workbook=app.books.open("信息统计-一年级.xlsx")

for sheet in workbook.sheets:
    sheet.name=sheet.name.replace("Sheet","成绩")
workbook.save()
app.quit()

初始：

代码运行后：

4. 批量合并Excel文件

import pandas as pd
import os

data_list=[]
for fname in os.listdir("."):
    if fname.startswith("信息统计-") and fname.endswith(".xlsx"):
         data_list.append(pd.read_excel(fname))
data_all=pd.concat(data_list)
data_all.to_excel("信息统计总表.xlsx",index=False)

表一：

表二：

运行效果：

功能完善中…

3 具体需求中的应用：

txt文档替换内容，数学建模初步筛选数据使用

def updateFile(file,old_str,new_str):
    file_data = ""
    with open(file, "r", encoding="utf-8") as f:
        for line in f:
            if old_str in line:
                line = line.replace(old_str,new_str)
            file_data += line
    with open(file,"w",encoding="utf-8") as f:
        f.write(file_data)
def updateOneLine(file):
    file_data = ""
    with open(file, 'r', encoding="utf-8") as r:
        lines = r.readlines()
    with open(file, "w", encoding="utf-8") as f:
        for l in lines:
            if 'DecaRangeRTLS' not in l:
                f.write(l)
            else:
                f.write("time id distance real_distance serial_number number \n")
for i in range(1,325):
    flie_name=str(i)+".异常.txt"
    print(flie_name)
    updateOneLine(flie_name)
    updateFile(flie_name, "T:", "")
    updateFile(flie_name, ":RR:0:", " ")
    updateFile(flie_name, ":", " ")

Excel表格合成（汇总数据使用）汇总多人的表格数据，快速合并到对应的表格中

import os # 导入模块
import pandas as pd


def combine_Excel(dir):
    file_name_li = os.listdir(dir)

    # 遍历出每个文件名
    for file_name in file_name_li:
        # 将文件夹绝对路径 与 文件名进行拼接
        file_path_li = os.path.join(dir, file_name)
        print(file_path_li)

    # 定义文件名集合
    # all_file_name = set()
    all_file_name = []
    # 定义数据列表
    all_data_li = []

    # 遍历出每个文件名
    for file_name in file_name_li:
        # 将文件夹绝对路径 与 文件名进行拼接
        file_path_li = os.path.join(dir, file_name)
        # 读取 excel 表格数据
        all_data = pd.read_excel(file_path_li, sheet_name=None)
        # 将数据添加到数据列表中
        all_data_li.append(all_data)
        # 将工作表名添加到文件夹集合中
        for name in all_data:
            all_file_name.append(name)

    # print(all_data_li)
    # all_file_name.sort()
    new_all_file_name = []
    for x in all_file_name:
        if x not in new_all_file_name:
            new_all_file_name.append(x)
    print(new_all_file_name)
    # 创建工作簿
    writer = pd.ExcelWriter("all_data.xlsx")

    # 遍历每个工作表名
    for sheet_name in all_file_name:
        data_li = []
        # 遍历数据
        for data in all_data_li:
            # 获取同名数据并添加到data_li中
            try:
                n_rows = data_li.append(data[sheet_name])
            except:
                print("此表格无此表！")
                continue
        # 将同名数据进行拼接
        group_data = pd.concat(data_li)
        # 保存到writer工作簿中，并指定工作表名为sheet_name
        group_data.to_excel(writer, sheet_name=sheet_name)
    # 千万莫忘记，保存工作簿
    writer.save()

if __name__ == '__main__':
    dir = r'C:\Users\dell\Desktop\Test_EXCel\Excel'
    combine_Excel(dir)
    pass

Excel 统一时间格式，主要是完成表格中的数据替换

# coding=utf-8

import xlrd
import codecs
import datetime


def clear_date_xlsx(file,sheet,num):
    # 加载Excel数据，处理数据
    data = xlrd.open_workbook(file)  # 读取工作表
    table = data.sheet_by_name(sheet)  # 读取当前sheet表对象
    rows = table.nrows  # 获取行数
    print('一共有{}行数据，开始清洗数据'.format(rows))

    for i in range(1, rows):
        MyTime = str(table.row_values(i)[num])
        today=" "
        if "44" in MyTime:
            delta=datetime.timedelta(days=float(MyTime))
            today = datetime.datetime.strptime('1899-12-30', '%Y-%m-%d') + delta
            today=datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "/" in MyTime:
            today = datetime.datetime.strptime(MyTime, '%m/%d/%Y')
            today = datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "日"in MyTime:
            today = datetime.datetime.strptime(MyTime, '%Y年%m月%d日')
            today = datetime.datetime.strftime(today, '%Y.%m.%d')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "年" in MyTime:
            today = datetime.datetime.strptime(MyTime, '%Y年%m月')
            today = datetime.datetime.strftime(today, '%Y.%m')
            if ".0" in today:
                today = today.replace(".0", ".")
        elif "，" in MyTime:
            today = MyTime.replace("，", ".")
        else:
            today=MyTime

        # MyTime = MyTime.replace("年", ".").replace("月","").replace("，", ".")
        output = ('{}\n').format(today)
        f = codecs.open('清洗后的数据.xls', 'a+')
        f.write(output)
        f.close()

if __name__ == '__main__':
    clear_date_xlsx('all_data.xlsx','sheet',6)

Excel 两个表格找相同数据，避免重复统计

import openpyxl
from openpyxl.styles import PatternFill
import re
import traceback

changeCells = 0

def Find_Same(file1,num1,col1,file2,num2,col2):
    # load the file(*.xlsx)
    wb1 = openpyxl.load_workbook(file1)
    wb2 = openpyxl.load_workbook(file2)
    # ! deal with one sheet
    ws1 = wb1.worksheets[num1]
    ws2 = wb2.worksheets[num2]

    global changeCells
    # get rows and columns of file
    rows1 = ws1.max_row
    rows2 = ws2.max_row
    all_file1_data = []
    file1_cells = 0
    same_Cells=0
    for row in range(2, rows1+1):
        content = ws1.cell(row=row, column=col1).value
        if (content != None):
            if (type(content) == str):
                if "-" in content:
                    content= content.replace("-", " ")
                all_file1_data.append(content.lower())
                file1_cells += 1
    for row in range(2, rows2+1):
        content = ws2.cell(row=row, column=col2).value
        Color = ['ffeb9c', '9c6500']  # 黄
        fille = PatternFill('solid', fgColor=Color[0])
        if (content != None):
            if (type(content) == str):
                if "-" in content:
                    content = content.replace("-", " ")
                for file1_data in all_file1_data:
                    if content.lower() in file1_data:
                        same_Cells += 1
                        ws2.cell(row=row, column=col2, value=content).fill = fille
                        ws2.cell(row=row, column=col2).value = content.replace(content, content+" "+str(all_file1_data.index(file1_data)))
    print('file1_cells', file1_cells)
    print('same_Cells', same_Cells)
    wb2.save(file2)

if __name__ == "__main__":
     Find_Same('test.xlsx',1,4,'test1.xlsx',1,4)

课代表查出勤,10班是我班学生的名单，遍历搜索出勤名单中未出现的人，出现的表蓝，未出现直接print（他的名字），老师再也不用担心我的学习了，哈哈哈哈哈

import openpyxl
from openpyxl.styles import PatternFill


def Find_Same(file1,num1,col1,file2,num2,col2):
    # Find_Same('10班.xlsx',1,4,'test1.xlsx',1,4)
    wb1 = openpyxl.load_workbook(file1)
    wb2 = openpyxl.load_workbook(file2)
    # ! deal with one sheet
    ws1 = wb1.worksheets[num1]
    ws2 = wb2.worksheets[num2]
    # get rows and columns of file
    rows1 = ws1.max_row
    rows2 = ws2.max_row

    find_name_flag = 0
    for row in range(2, rows1 + 1):
        content = ws1.cell(row=row, column=col1).value
        Color = ['ffeb9c', 'A4BBEE']  # 黄
        fille = PatternFill('solid', fgColor=Color[1])
        if (content != None):
            if (type(content) == str):
                for row in range(2, rows2 + 1):
                    attendance_data = ws2.cell(row=row, column=col2).value
                    if (attendance_data != None):
                        if (type(attendance_data) == str):
                            if content in attendance_data:
                                ws2.cell(row=row, column=1, value=attendance_data).fill = fille
                                find_name_flag = 1
                    if row == rows2 and find_name_flag==0:
                        print("未出勤人员——"+content)
                    if find_name_flag == 1:
                        find_name_flag=0
                        break

    wb2.save(file2)

if __name__ == "__main__":
     Find_Same('10班.xlsx',0,3,'出勤.xlsx',0,1)

名单核对系统源码

采用pyqt开发显示页面，可快速统计网课人员的出勤情况
主文件：

from PyQt5 import QtWidgets
from PyQt5.QtWidgets import QMessageBox
from PyQt5.QtCore import QTimer
from Ui_show import Ui_Form
from PyQt5.QtWidgets import *
from PyQt5 import QtWidgets
import sys
import os
import openpyxl
from openpyxl.styles import PatternFill



class Pyqt5_Serial(QtWidgets.QWidget, Ui_Form):
    def __init__(self):
        super(Pyqt5_Serial, self).__init__()
        self.setupUi(self)
        self.init()
        self.setWindowTitle("名单核对系统")
        self.file1= ""
        self.num1= 0
        self.col1 = 0
        self.file2 = ""
        self.num2 = 0
        self.col2 = 0



    def init(self):

        self.class_Button.clicked.connect(self.open_class_file)
        self.work_Button.clicked.connect(self.open_work_file)
        self.run_Button.clicked.connect(self.check_excel)
        self.class_row.textChanged.connect(self.class_textchanged)
        self.woek_row.textChanged.connect(self.work_textchanged)

    def open_class_file(self):
        fileName, fileType = QtWidgets.QFileDialog.getOpenFileName(self, "选取班级名单", os.getcwd(),
                                                                   "All Files(*);;Text Files(*.txt)")
        if fileName != "":
            self.class_lineEdit.setText(fileName)
            self.file1 = fileName

    def open_work_file(self):
        fileName, fileType = QtWidgets.QFileDialog.getOpenFileName(self, "选取出勤名单", os.getcwd(),
                                                                   "All Files(*);;Text Files(*.txt)")
        if fileName != "":
            self.work_lineEdit.setText(fileName)
            self.file2 = fileName
    def class_textchanged(self,text):
        self.col1 = int(text)
        # print(self.col1)

    def work_textchanged(self,text):
        self.col2 = int(text)
        # print(self.col2)

    def check_excel(self):
        self.num1 = 0
        self.num2 = 0
        res_1=QMessageBox.question(self, 'Is it right?',"名单第"+str(self.col1)+"列"+"与"+"出勤第"+str(self.col2)+"列", QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes)
        if res_1==QMessageBox.Yes:
            try:
                self.Find_Same(self.file1, self.num1, self.col1, self.file2, self.num2, self.col2)
                QMessageBox.information(self, "Finish!", "核对完成,请查看核对名单.xlsx")
            except:
                QMessageBox.critical(self, "Error！", "您输入的信息有误！")
        else:
            pass


    def Find_Same(self,file1, num1, col1, file2, num2, col2):
        current_path = os.path.abspath(self.file2)
        father_path =os.path.dirname(current_path) + os.path.sep
        # Find_Same('10班.xlsx',1,4,'test1.xlsx',1,4)
        wb1 = openpyxl.load_workbook(file1)
        wb2 = openpyxl.load_workbook(file2)
        # ! deal with one sheet
        ws1 = wb1.worksheets[num1]
        ws2 = wb2.worksheets[num2]
        # get rows and columns of file
        rows1 = ws1.max_row
        rows2 = ws2.max_row

        find_name_flag = 0
        for row in range(2, rows1 + 1):
            content = ws1.cell(row=row, column=col1).value
            Color = ['F1F84F', 'A4BBEE']  # 颜色
            fille = PatternFill('solid', fgColor=Color[1])
            if (content != None):
                if (type(content) == str):
                    for row in range(2, rows2 + 1):
                        attendance_data = ws2.cell(row=row, column=col2).value
                        if (attendance_data != None):
                            if (type(attendance_data) == str):
                                if content in attendance_data:
                                    ws2.cell(row=row, column=1, value=attendance_data).fill = fille
                                    find_name_flag = 1
                        if row == rows2 and find_name_flag == 0:
                            self.Show.insertPlainText("未出勤人员——" + content+"\n")
                            print("未出勤人员——" + content)
                        if find_name_flag == 1:
                            find_name_flag = 0
                            break
        wb2.save(father_path+"核对名单.xlsx")
    



if __name__ == '__main__':
    app = QtWidgets.QApplication(sys.argv)
    myshow = Pyqt5_Serial()
    myshow.show()
    sys.exit(app.exec_())

显示页面Ui_show.py

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

# Form implementation generated from reading ui file 'test.ui'
#
# Created by: PyQt5 UI code generator 5.15.4
#
# WARNING: Any manual changes made to this file will be lost when pyuic5 is
# run again.  Do not edit this file unless you know what you are doing.


from PyQt5 import QtCore, QtGui, QtWidgets


class Ui_Form(object):
    def setupUi(self, Form):
        Form.setObjectName("Form")
        Form.resize(631, 246)
        Form.setStyleSheet("background-color: rgb(252, 208, 255);")
        self.verticalLayoutWidget = QtWidgets.QWidget(Form)
        self.verticalLayoutWidget.setGeometry(QtCore.QRect(13, 15, 611, 221))
        self.verticalLayoutWidget.setObjectName("verticalLayoutWidget")
        self.verticalLayout = QtWidgets.QVBoxLayout(self.verticalLayoutWidget)
        self.verticalLayout.setSizeConstraint(QtWidgets.QLayout.SetDefaultConstraint)
        self.verticalLayout.setContentsMargins(0, 0, 0, 0)
        self.verticalLayout.setObjectName("verticalLayout")
        self.label = QtWidgets.QLabel(self.verticalLayoutWidget)
        font = QtGui.QFont()
        font.setFamily("隶书")
        font.setPointSize(24)
        self.label.setFont(font)
        self.label.setLayoutDirection(QtCore.Qt.LeftToRight)
        self.label.setAlignment(QtCore.Qt.AlignCenter)
        self.label.setObjectName("label")
        self.verticalLayout.addWidget(self.label)
        self.horizontalLayout = QtWidgets.QHBoxLayout()
        self.horizontalLayout.setObjectName("horizontalLayout")
        self.Show = QtWidgets.QPlainTextEdit(self.verticalLayoutWidget)
        self.Show.setObjectName("Show")
        self.horizontalLayout.addWidget(self.Show)
        self.formLayout = QtWidgets.QFormLayout()
        self.formLayout.setObjectName("formLayout")
        self.class_Button = QtWidgets.QPushButton(self.verticalLayoutWidget)
        self.class_Button.setMinimumSize(QtCore.QSize(50, 0))
        self.class_Button.setObjectName("class_Button")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.LabelRole, self.class_Button)
        self.class_lineEdit = QtWidgets.QLineEdit(self.verticalLayoutWidget)
        self.class_lineEdit.setObjectName("class_lineEdit")
        self.formLayout.setWidget(1, QtWidgets.QFormLayout.FieldRole, self.class_lineEdit)
        self.work_Button = QtWidgets.QPushButton(self.verticalLayoutWidget)
        self.work_Button.setObjectName("work_Button")
        self.formLayout.setWidget(2, QtWidgets.QFormLayout.LabelRole, self.work_Button)
        self.work_lineEdit = QtWidgets.QLineEdit(self.verticalLayoutWidget)
        self.work_lineEdit.setObjectName("work_lineEdit")
        self.formLayout.setWidget(2, QtWidgets.QFormLayout.FieldRole, self.work_lineEdit)
        self.run_Button = QtWidgets.QPushButton(self.verticalLayoutWidget)
        self.run_Button.setObjectName("run_Button")
        self.formLayout.setWidget(5, QtWidgets.QFormLayout.LabelRole, self.run_Button)
        self.horizontalLayout_2 = QtWidgets.QHBoxLayout()
        self.horizontalLayout_2.setObjectName("horizontalLayout_2")
        self.label_2 = QtWidgets.QLabel(self.verticalLayoutWidget)
        self.label_2.setObjectName("label_2")
        self.horizontalLayout_2.addWidget(self.label_2)
        self.class_row = QtWidgets.QLineEdit(self.verticalLayoutWidget)
        self.class_row.setText("")
        self.class_row.setObjectName("class_row")
        self.horizontalLayout_2.addWidget(self.class_row)
        self.label_3 = QtWidgets.QLabel(self.verticalLayoutWidget)
        self.label_3.setObjectName("label_3")
        self.horizontalLayout_2.addWidget(self.label_3)
        self.woek_row = QtWidgets.QLineEdit(self.verticalLayoutWidget)
        self.woek_row.setText("")
        self.woek_row.setObjectName("woek_row")
        self.horizontalLayout_2.addWidget(self.woek_row)
        self.formLayout.setLayout(5, QtWidgets.QFormLayout.FieldRole, self.horizontalLayout_2)
        self.textBrowser = QtWidgets.QTextBrowser(self.verticalLayoutWidget)
        self.textBrowser.setObjectName("textBrowser")
        self.formLayout.setWidget(6, QtWidgets.QFormLayout.FieldRole, self.textBrowser)
        self.horizontalLayout.addLayout(self.formLayout)
        self.verticalLayout.addLayout(self.horizontalLayout)

        self.retranslateUi(Form)
        QtCore.QMetaObject.connectSlotsByName(Form)
        Form.setTabOrder(self.class_Button, self.work_Button)

    def retranslateUi(self, Form):
        _translate = QtCore.QCoreApplication.translate
        Form.setWindowTitle(_translate("Form", "Form"))
        self.label.setText(_translate("Form", "出勤名单核对系统"))
        self.class_Button.setText(_translate("Form", "选择名单"))
        self.work_Button.setText(_translate("Form", "选择出勤表"))
        self.run_Button.setText(_translate("Form", "开始筛查"))
        self.label_2.setText(_translate("Form", "名单列"))
        self.class_row.setPlaceholderText(_translate("Form", "1"))
        self.label_3.setText(_translate("Form", "出勤列"))
        self.woek_row.setPlaceholderText(_translate("Form", "1"))
        self.textBrowser.setHtml(_translate("Form", "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0//EN\" \"http://www.w3.org/TR/REC-html40/strict.dtd\">\n"
"<html><head><meta name=\"qrichtext\" content=\"1\" /><style type=\"text/css\">\n"
"p, li { white-space: pre-wrap; }\n"
"</style></head><body style=\" font-family:\'SimSun\'; font-size:9pt; font-weight:400; font-style:normal;\">\n"
"<p style=\" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;\">使用说明：确定名册姓名所在列数，出勤姓名所在列数，填写以上位置，选择对应文件即可开始进行出勤核对</p></body></html>"))

能力有限仅供参考,欢迎提出宝贵意见！