python对execl 处理

1. 读取execl 

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

   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)