Python办公自动化，工程数据的处理分析

前言

最近把自己之前写的自动化作业的代码重新梳理了一遍，增加了好多新的功能，虽说下载的时候还是需要手动点击，但也基本上实现了自动登录、自动处理、自动发送的需求，所以又处于没有实战项目的时期。刚好群里有位同学找人要了一份数据练手，看着挺复杂的，也勾起了我的欲望，就跟他联系了下，他也慷慨的共享了一份给我，在这里也谢谢他提供的数据。

数据分析

这次的数据是一份工程数据，首先对数据进行分析。

用xlwings和pandas读了一下，发现这个Excel里有22个工程、23个sheet，然后随机抽取几个sheet具体分析一下。

发现每个sheet里面都是这样的排版，但还好数据结构比较简单。

最后再回头分析一下“汇总”sheet里的表格数据

发现每个工程对应的“名称(材料、机械规格型号)”有“单位、数量、单价(元)、合价(元)”四列。

需求分析

最初的需求是，用Excel的VLOOKUP实现每个物品的数量统计，因为这张表格别人已经手动处理好了，那么可以将整个手工处理的过程整理一下，把这个过程当做需求，最终产出和当前格式差不多的表格，经过简单整理，需求如下：

1 查找“安全网”等物品分别在每个工程里的使用数量
2 计算每种物品的合价（元）
3 计算总的使用数量
4 每个物品要包含“单位、数量、单价(元)、合价(元)”四列

代码实现

通过对需求的分析，可以从以下步骤实现：

1 摸底sheet的数量

    def read_excel(self):
        '''
        打开Excel,获取sheet列表
        :return:sheetList的切片
        '''
        sheet_list = []
        app = xw.App(visible=False, add_book=False)  
        workbook = app.books.open(self.fpath)  
        sheets = workbook.sheets  # 获取文件所有sheet
        for sheet in sheets:
            sheet_list.append(sheet.name)  # 将每一个sheet的name添加到列表
        workbook.close()  
        app.quit()  
        return sheet_list[1:]

2 摸底工程的数量

   def get_pro_name(self):
        '''
        获取工程名称列表
        :return:
        '''
        df = pd.read_excel(self.fpath, skiprows=2)
        pro_name_list = list(df.loc[:, "-"])  # 获取“-”列所有值的列表
        return pro_name_list

3 打开所有sheet，对数据进行清洗、合并，方便后续操作

    def get_df_all(self):
        '''
        数据清洗、合并
        :return: 合并后的Dataframe
        '''
        df_list = []
        for i in range(22):
            df = pd.read_excel(self.fpath, sheet_name=self.read_excel()[i], skiprows=2)
            df["备注"] = "无"  # 备注行填充为“无”
            df.dropna(axis=0, how="any", inplace=True)  # 删除空行
            df.set_index("序号", inplace=True)  # 设置序号列为index
            try:
                df.drop("序号"


    
, axis=0, inplace=True)  # 删除含有序号的行
            except Exception:
                pass
            df["工程名称"] = self.get_pro_name()[i]  # 新增工程名称列
            df_list.append(df)
        df_all = pd.concat(df_list)
        df_all.drop("Unnamed: 6", axis=1, inplace=True)
        return df_all

清洗后的数据如下：

4 新增“合价”列，并筛选出“安全网”等5个DataFrame，实现多个df的merge

    def get_part_merge_df(self):
        cho_list = ["工程名称", "名称(材料、机械规格型号)", "单位", "数量", "单价\n(元)"]
        cho_df = self.get_df_all()[cho_list]  # 用列表切片原df，产生新的dataframe
        cho_df.loc[:, "合价（元）"] = cho_df["数量"] * cho_df["单价\n(元)"]  # 新增合价列
        cho_df.dropna(axis=0, how="any", inplace=True)  # 删除空行
        res_list = ["安全网", "竹脚手板 侧编", "支撑钢管及扣件", "脚手架钢管", "脚手架底座"]
        df_list = []
        for i in range(len(res_list)):
            df1 = cho_df.loc[lambda df: (df["名称(材料、机械规格型号)"].str.startswith(res_list[i])), :]
            df1.rename(columns={"数量": res_list[i] + "数量"}, inplace=True)
            df1.drop("名称(材料、机械规格型号)", axis=1, inplace=True)  # 删除名称列
            df_list.append(df1)
        df_merge = reduce(lambda left, right: pd.merge(left, right, how="outer", on="工程名称"), df_list)
        return df_merge

5 构造包含工程名称的df，与第4步的数据进行merge，并计算数量合计

    def get_merge_df(self):
        df = pd.DataFrame(self.get_pro_name())  # 创建df
        df.columns = ["工程名称"]  # 修改列名
        merge_df_all = pd.merge(left=df, right=self.get_part_merge_df(), left_on="工程名称", right_on="工程名称", how="outer")
        merge_df_all.fillna(0,inplace=True)
        # 新增数量合计列
        merge_df_all.loc[:, "数量合计"] = merge_df_all["脚手架底座数量"] + merge_df_all["脚手架钢管数量"] + merge_df_all["支撑钢管及扣件数量"] + \
                                      merge_df_all["竹脚手板 侧编数量"] + merge_df_all["安全网数量"]
        return merge_df_all

效果如下：

6 将结果写入原Excel

    def write_excel(self):
        app = xw.App(visible=False, add_book=False)
        workbook = app.books.open("汇总表.xlsx")
        workbook.sheets.add("汇总表（new）", before=workbook.sheets[0])  # 添加sheet
        workbook.sheets["汇总表（new）"].range("A1").options(index=True).value = self.get_merge_df()  # 写入数据
        workbook.save()
        workbook.close()
        app.quit()
        print("数据已写入")

最终效果

全部代码




    
import xlwings as xw
import pandas as pd
import os
from functools import reduce


class Engin_data():
    def __init__(self, path, fname):
        self.path = path
        self.fname = fname
        self.fpath = os.path.join(self.path, self.fname)

    def read_excel(self):
        '''
        打开Excel,获取sheet列表
        :return:sheetList的切片
        '''
        sheet_list = []
        app = xw.App(visible=False, add_book=False)  # 创建app
        workbook = app.books.open(self.fpath)  # 打开文件
        sheets = workbook.sheets  # 获取文件所有sheet
        for sheet in sheets:
            sheet_list.append(sheet.name)  # 将每一个sheet的name添加到列表
        workbook.close()  # 关闭文件
        app.quit()  # 退出app
        return sheet_list[1:]

    def get_pro_name(self):
        '''
        获取工程名称列表
        :return:
        '''
        df = pd.read_excel(self.fpath, skiprows=2)
        pro_name_list = list(df.loc[:, "-"])  # 获取“-”列所有值的列表
        return pro_name_list

    def get_df_all(self):
        '''
        数据清洗、合并
        :return: 合并后的Dataframe
        '''
        df_list = []
        for i in range(22):
            df = pd.read_excel(self.fpath, sheet_name=self.read_excel()[i], skiprows=2)
            df["备注"] = "无"  # 备注行填充为“无”
            df.dropna(axis=0, how="any", inplace=True)  # 删除空行
            df.set_index("序号", inplace=True)  # 设置序号列为index
            try:
                df.drop("序号", axis=0, inplace=True)  # 删除含有序号的行
            except Exception:
                pass
            df["工程名称"] = self.get_pro_name()[i]  # 新增工程名称列
            df_list.append(df)
        df_all = pd.concat(df_list)
        df_all.drop("Unnamed: 6", axis=1, inplace=True)
        print("正在合并文件")
        return df_all

    def get_part_merge_df(self):
        cho_list = ["工程名称", "名称(材料、机械规格型号)", "单位", "数量", "单价\n(元)"]
        cho_df = self.get_df_all()[cho_list]  # 用列表切片原df，产生新的dataframe
        cho_df.loc[:, "合价（元）"] = cho_df["数量"] * cho_df["单价\n(元)"]  # 新增合价列
        cho_df.dropna(axis=0, how="any", inplace=True)  # 删除空行
        res_list = ["安全网", "竹脚手板 侧编", "支撑钢管及扣件", "脚手架钢管", "脚手架底座"]
        df_list = []
        for i in range(len(res_list)):
            df1 = cho_df.loc[lambda df: (df["名称(材料、机械规格型号)"].str.startswith(res_list[i])), :]
            df1.rename(columns={"数量": res_list[i] + "数量"}, inplace=True)
            df1.drop("名称(材料、机械规格型号)", axis=1, inplace=True)  # 删除名称列
            df_list.append(df1)
        df_merge = reduce(lambda left, right: pd.merge(left, right, how="outer", on="工程名称"), df_list)
        return df_merge

    def get_merge_df(self):
        df = pd.DataFrame(self.get_pro_name())  # 创建df
        df.columns = ["工程名称"]  # 修改列名
        merge_df_all = pd.merge(left=df, right=self.get_part_merge_df(), left_on="工程名称", right_on="工程名称", how="outer")
        merge_df_all.fillna(0,inplace=True)
        # 新增数量合计列
        merge_df_all.loc[:, "数量合计"] = merge_df_all["脚手架底座数量"] + merge_df_all["脚手架钢管数量"] + merge_df_all["支撑钢管及扣件数量"] + \
                                      merge_df_all["竹脚手板 侧编数量"] + merge_df_all["安全网数量"]
        merge_df_all.replace("0","-")
        return merge_df_all

    def write_excel(self):
        app = xw.App(visible=False, add_book=False)
        workbook = app.books.open("汇总表.xlsx")
        workbook.sheets.add("汇总表（new）", before=workbook.sheets[0])  # 添加sheet
        workbook.sheets["汇总表（new）"].range("A1").options(index=True).value = self.get_merge_df()  # 写入数据
        workbook.save()
        workbook.close()
        app.quit()
        print("数据写入完成")


if __name__ == '__main__':
    path = r"D:\Python资料\工程数据处理"
    fname = "汇总表.xlsx"
    engin_data = Engin_data(path, fname)
    engin_data.write_excel()

最后，推荐蚂蚁老师的《Python终生全栈大套餐》，有交流群和答疑服务：

注意这个图片，需要在抖音扫码打开