Python收集excel中的数据

需求描述：数据收集部门下发统一excel模板到各部门，各部门根据实际情况填写数据后交由数据收集部门统一整理后录入系统。

问题点：虽然有统一的模板，但收集上来的数据还是存在差异。比如有的部门很大，统计的时候收集了多个excel，每个excel中又包含了众多的sheet。还存在对模板进行二次加工，比如在最前面插入几列，用于自己的备注，但提交前只是对列做了隐藏，或者添加了自己的sheet，最后提交前也做了隐藏。

所以本次程序需要解决几个点：

1、能够一次性读取文件夹下所有的excel文件，以及excel里非隐藏的sheet；

2、自动判断sheet是否为按照模板填写的有效数据；

3、对因为合并单元格而缺失的数据进行补全；

4、只读取指定的列数据。

前置条件：使用pandas和openpyxl来处理数据。

读取文件夹下所有的excel文件

def listdir(path, list_name):  #传入存储的list
    # 循环目标路径下所有文件目录及名称
    for dirpath,dirnames,filenames in os.walk(path):
        for filename in filenames:
            # 剔除缓存的表
            extName = os.path.splitext(filename)
            if(extName[0].find("~") >= 0 or extName[0].find("$") >= 0):
                continue
            print(os.path.join(dirpath,filename))
            list_name.append(os.path.join(dirpath,filename))

读取文件的内容

#读取excel
wb = load_workbook(filename)
# 获取workbook中所有非隐藏的表格
all_sheet_name = wb.sheetnames
useful_sheet = [i for i in all_sheet_name if wb[i].sheet_state != 'hidden']

补全缺失的单元格

#补全被合并单元格的数据
fdata['单位'].fillna(method='ffill',inplace=True)

完整的代码如下：

import os
import pandas as pd
import openpyxl
from openpyxl import load_workbook

def listdir(path, list_name):  #传入存储的list
    # 循环目标路径下所有文件目录及名称
    for dirpath,dirnames,filenames in os.walk(path):
        for filename in filenames:
            # 剔除缓存的表
            extName = os.path.splitext(filename)
            if(extName[0].find("~") >= 0 or extName[0].find("$") >= 0):
                continue
            print(os.path.join(dirpath,filename))
            list_name.append(os.path.join(dirpath,filename))

def editdata(fdata):
    #补全被合并单元格的数据
    fdata['单位'].fillna(method='ffill',inplace=True)
    fdata['部门'].fillna(method='ffill',inplace=True)
    fdata['岗位'].fillna(method='ffill',inplace=True)
    fdata['指标类型'].fillna(method='ffill',inplace=True)

    fdata.to_excel("D:/test_files/数据完整版.xlsx",index = False)

    return fdata

def readfile(list_name):    #读取文件的内容
    data2 = pd.DataFrame()
    for filename in list_name:
        print("开始处理文件"+filename)
        wb = load_workbook(filename)
        # 获取workbook中所有的表格
        all_sheet_name = wb.sheetnames
        useful_sheet = [i for i in all_sheet_name if wb[i].sheet_state != 'hidden']
        print('所有不是隐藏的sheet: \n', useful_sheet)
        
        for i in range(len(useful_sheet)):
            print("开始处理表格---"+useful_sheet[i])
            df2 = pd.read_excel(filename, sheet_name=useful_sheet[i], header=1,encoding='utf8')

            model_columns = ['单位', '部门', '岗位','指标类型', '指标名称'] # 创建自定义列名称
            dfdata = pd.DataFrame(columns=list(model_columns)) # 创建自定义列名称的DataFrame

            #记录表格数据
            try:
                dfdata['单位'] = df2['单位']
                dfdata['部门'] = df2['部门']
                dfdata['岗位'] = df2['岗位']
                dfdata['指标类型'] = df2['指标类型']
                dfdata['指标名称'] = df2['指标名称']
                data2 = data2.append(dfdata)
            except Exception as identifier:
                print("*******非有效表格*******"+useful_sheet[i])
                pass
    print(len(data2))        
    return data2
            
def main():
    path = r"D:\test_files\kpi"
    try:
        #读取文件夹下的所有文件
        List_files=[]
        listdir(path,List_files)
        all_data = readfile(List_files)

        adata=editdata(all_data)
        print("*******打印有效数据*******")
        print(adata)

    except Exception as  e:
        # 打印异常信息
        print(e)

if __name__ == '__main__':
    main()