python对execl 处理

佚名 4年前 (2020-06-22) Python 591人围观抢沙发百度已收录

1. 读取execl

1.前提需要安装xlrd模块，这个在网上可以找安装教程，这里就不写了

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

2. 打开表格

3. 读取表格的sheet

4. 按行读取数据或者列读取数据或者单元格读取数据

实际操作：

import xlrd
import xlwt
from xlutils.copy import copy

def info_row_col_execl(file_name):
    myworkbook = xlrd.open_workbook(file_name)
    mySheetlist = myworkbook.sheets()
    mysheet = mySheetlist[0]
    nrows = mysheet.nrows
    ncols = mysheet.ncols
    return nrows,ncols

def read_execl(filename):
    myworkbook = xlrd.open_workbook(filename)
    mySheetlist = myworkbook.sheets()
    mysheet = mySheetlist[0]
    return mysheet

def com_exel(file_name_dai,file_name_all):
    dai_rows, dai_cols = info_row_col_execl(file_name_dai)
    all_rows,all_cols = info_row_col_execl(file_name_all)
    print(dai_rows, dai_cols)
    print(all_rows,all_cols)

def read_execl_values(file_name):
    list_execl_values = []
    mysheet = read_execl(file_name)
    execl_rows, execl_cols = info_row_col_execl(file_name)
    myRowValues_name= mysheet.row_values(0)
    for nrow in range(1,execl_rows):
        myRowValues= mysheet.row_values(nrow)
        #print(myRowValues)
        myRow_name_value=dict(zip(myRowValues_name,myRowValues))
        list_execl_values.append(myRow_name_value)
    return list_execl_values

def com_execl_values(file_name_dai,file_name_all,key_list):
    dai_list_execl_values = read_execl_values(file_name_dai)
    all_list_execl_values = read_execl_values(file_name_all)
    row_num = 1
    for i in dai_list_execl_values:
        dai_list_execl_value = i
        dai_len_row = len(dai_list_execl_value)
        for k in all_list_execl_values:
            all_list_execl_value = k
            result = compare_two_dict(dai_list_execl_value,all_list_execl_value,key_list)
            if  result == "Pass":
                #print(dai_list_execl_value,all_list_execl_value,result)
                print(dai_list_execl_value['ID'],dai_list_execl_value['名字'],)
                print(all_list_execl_value['班级'],all_list_execl_value['教师'])
                write_execl(file_name_dai,all_list_execl_value['班级'],all_list_execl_value['教师'],row_num,dai_len_row)
                break;
        row_num = row_num + 1       
    
def write_execl(file_name_dai,all_list_execl_value_1,all_list_execl_value_2,row_num,dai_len_row):
    open_execl = xlrd.open_workbook(file_name_dai)
    open_execl_copy = copy(open_execl)
    open_execl_copy_sheet = open_execl_copy.get_sheet(0)
    open_execl_copy_sheet.write(row_num,dai_len_row-3,all_list_execl_value_1)
    open_execl_copy_sheet.write(row_num,dai_len_row-2,all_list_execl_value_2)
    open_execl_copy.save(file_name_dai)
    

def compare_two_dict(dai_dict,all_dict,key_list):
    flag = True
    dai_dict_key = dai_dict.keys()
    all_dict_key = all_dict.keys()
    if len(key_list) != 0:
        for key in key_list:
            if key in dai_dict_key and key in all_dict_key:
                if dai_dict[key] == all_dict[key]:
                    #print(dai_dict[key],all_dict[key])
                    flag = flag & True
                else:
                    flag = flag & False
            else:
                raise Exception('key_list contains error key')
    else:
        raise Exception('key_list is null')

    if flag:
        result = "Pass"
    else:
        result = "Failed"
    #print(result)
    return result
    

if __name__ == '__main__':
    file_name_dai = 'name_1.xlsx'
    file_name_all = 'name.xlsx'
    key_list = ['ID', '名字']
    com_execl_values(file_name_dai,file_name_all,key_list)