python xlwings 实践应用

实现的功能是合并两个excel表格的内容，以某一列为唯一索引

 

 

# -*- coding: UTF-8 -*-

import time
import os
import xlwings as xw

################################################################################
def excelMapGet(excel, sheet, key):   
    start = time.time()

    table = xw.Book(excel)
    table_sht = table.sheets[sheet]

    row_max = table_sht.used_range.last_cell.row
    column_max = table_sht.used_range.last_cell.column

    # print(row_max)
    # print(column_max)
    # print(table_sht[0:row_max,0:column_max].value)

    title_list = table_sht[0, 0:column_max].value
    title_map = {}

    for i in range(len(title_list)):
        title_map[title_list[i]] = i

    # print(title_list)
    # print(title_map)

    excel_table = table_sht[0:row_max,0:column_max].value
    # print(excel_table)

    excel_key = 0
    excel_map = {}

    for i in range(1, row_max):
        excel_key = excel_table[i][title_map[key]]
        excel_map[excel_key] = {}

        for j in range(column_max):
            excel_map[excel_key][title_list[j]] = excel_table[i][j]

    table.close()
    end = time.time()
    print(f"Get excel map ok! Used {end-start} seconds.")
    return excel_map

def excelMapPrint(excel_map):
    for key, value in excel_map.items():
        print(f"Key: {key}")

        for key1, value1 in value.items():
            print(f"{key1}:{value1}")

def workTableUpdate(excel, sheet, key, excel_map):
    start = time.time()

    table = xw.Book(excel)
    table_sht = table.sheets[sheet]

    row_max = table_sht.used_range.last_cell.row
    column_max = table_sht.used_range.last_cell.column

    # print(row_max)
    # print(column_max)

    excel_table = table_sht[0:row_max,0:column_max].value
    # print(excel_table)

    title_list = excel_table[0]
    title_map = {}

    for i in range(len(title_list)):
        title_map[title_list[i]] = i
    
    # print(title_list)
    # print(title_map)
    
    id_map = {}

    for i in range(1, row_max):
        id_map[excel_table[i][title_map[key]]] = i

    # print(id_list)
    # print(id_map)

    index_num = 0
    index_max = len(excel_map)
    filter_map = {}.fromkeys(["已关闭", "已取消", "已确认重复", "已上报"])

    for key, value in excel_map.items():
        index_num += 1

        if key in id_map:
            if excel_table[id_map[key]][title_map["状态名称"]] in filter_map:
                print(f"{key} is closed.")
                continue
                
            for key1, value1 in value.items():
                if key1 in title_map:
                    if excel_table[id_map[key]][title_map[key1]] != value1:
                        table_sht[id_map[key], title_map[key1]].value = value1        
        else:
            for key1,value1 in value.items():
                if key1 in title_map:
                    table_sht[row_max, title_map[key1]].value = value1
            
            row_max += 1

        print(f"Totle {index_max}, now is processed {index_num}.")

    table.save()
    end = time.time()    
    print(f"Update work table ok! Used {end-start} seconds.")




################################################################################
# 主任务处理
################################################################################

ecmap = excelMapGet("ec.xlsx", "sheet1", "编号")
workTableUpdate("table.xlsx", "ec", "编号", ecmap)

#ecmap2 = excelMapGet("ec2.xlsx", "sheet1", "编号")
#workTableUpdate("table.xlsx", "sheet1", "编号", ecmap2)

os.system("pause")