Python 配合Excel进行附件的查缺补漏，附件重命名

打杂也要高效率打杂，配合Excel进行附件筛选，附件编号工作，人工太累了。python上线3秒解决，简直不要太爽。

Excel_check_item(Excel_file_path,Exce_check_Rows,Exce_check_Cols,File_path)仅支持附件筛选工作

Excel_add_item_number(Excel_file_path, Exce_check_Rows, Exce_check_Cols, Number_enable, Number_add_item_Rows, File_path)支持附件筛选和附件编号工作


代码见下方：

import xlrd2
import os
import sys



company_last = "Test "
Init_value = 0

def Excel_check_item(Excel_file_path,Exce_check_Rows,Exce_check_Cols,File_path):
    count = 0
    count_total = 0
    count_fail = 0
    global company_last
    global Init_value
    # 打开文件
    workbook = xlrd2.open_workbook(Excel_file_path)
    file_names = os.listdir(File_path)
    #获取所有sheet
    print ("Sheet Includes")
    print(workbook.sheet_names()) # [u'sheet1', u'sheet2']
    #获取sheet2
    sheet1_name= workbook.sheet_names()[0]   #deal with sheet1
    print (sheet1_name)
    #根据sheet索引或者名称获取sheet内容
    sheet1 = workbook.sheet_by_name(sheet1_name)
    # sheet的名称，行数，列数
    print("%s  Rows:%d  Cols:%d" % (sheet1.name, sheet1.nrows, sheet1.ncols))

    #获取单元格内容
    for num in range(Exce_check_Rows, sheet1.nrows):
        state = 0
        count_total = count_total + 1
        company_pre = sheet1.cell(num, Exce_check_Cols).value
        if company_pre == company_last or company_pre in company_last:  #单元格重复项处理
            continue
        company_last = company_pre
        file_names = os.listdir(File_path)
        for company_check in file_names:
            portion = os.path.splitext(company_check)
            if company_pre == portion[0] or company_pre in portion[0]:
                state = 1
                count = count + 1
                break

        if state == 0:
            count_fail = count_fail + 1
            print("Rows:%d  " %(count_total) + company_pre + "  not found !")
            # print(portion[0])
    print("Tolta check items: %d   Have already found: %d   Not found items :%d" % (count_total, count, count_fail))

def Excel_add_item_number(Excel_file_path, Exce_check_Rows, Exce_check_Cols, Number_enable, Number_add_item_Rows, File_path):
    count = 0
    count_total = 0
    count_fail = 0
    global company_last
    global Init_value
    # 打开文件
    workbook = xlrd2.open_workbook(Excel_file_path)
    file_names = os.listdir(File_path)
    #获取所有sheet
    print ("Sheet Includes")
    print(workbook.sheet_names()) # [u'sheet1', u'sheet2']
    #获取sheet2
    sheet1_name= workbook.sheet_names()[0]   #deal with sheet1
    print (sheet1_name)
    #根据sheet索引或者名称获取sheet内容
    sheet1 = workbook.sheet_by_name(sheet1_name)
    # sheet的名称，行数，列数
    print("%s  Rows:%d  Cols:%d" % (sheet1.name, sheet1.nrows, sheet1.ncols))

    #获取单元格内容
    for num in range(Exce_check_Rows, sheet1.nrows):
        state = 0
        count_total = count_total + 1
        company_pre = sheet1.cell(num, Exce_check_Cols).value
        if company_pre == company_last or company_pre in company_last:  #单元格重复项处理
            continue
        company_last = company_pre
        Item_number = sheet1.cell(num, Number_add_item_Rows).value
        file_names = os.listdir(File_path)
        for company_check in file_names:

            portion = os.path.splitext(company_check)
            if company_pre == portion[0] or company_pre in portion[0]:
                if Number_enable == 1:
                    old_name = File_path + '\\' + company_check  # 获取旧文件的名字，注意名字要带路径名
                    if Item_number not in portion[0]:
                        new_name = File_path + '\\' + Item_number +"  "+ portion[0] + portion[1]
                        os.rename(old_name, new_name)  # 用rename()函数重命名
                state = 1
                count = count + 1
                break

        if state == 0:
            count_fail = count_fail + 1
            print("Rows:%d  " %(count_total) + company_pre + "  not found !")
            # print(portion[0])
    print("Tolta check items: %d   Have already found: %d   Not found items :%d" % (count_total, count, count_fail))



if __name__ == '__main__':

    file_path = 'C:\\Users\\ZKK\\Desktop\\测试文件'  # 文件目录
    excel_file_path = 'C:\\Users\\ZKK\\Desktop\\test.xlsx '
    # Excel_add_item_number(Excel_file_path, Exce_check_Rows, Exce_check_Cols, Number_enable, Number_add_item_Rows,File_path):
    Excel_add_item_number(excel_file_path, 3, 1, 1, 8, file_path)