导出Excel文件 #

import xlwt
import xlrd
from xlutils.copy import copy

file = 'tenant_oss.xlsx'
nfv_info = [
    {"a": 1, "b": 2},
    {"a": 3, "b": 4}
]

if os.path.isfile(file):
    # 往Excel追加
    rb = xlrd.open_workbook(file, formatting_info=True)
    r_sheet = rb.sheet_by_index(0)
    row_num = r_sheet.nrows
    workbook = copy(rb)
    worksheet = workbook.get_sheet(0)
else:
    # 新增
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('tenant')

    row_num = 1
    col_names = nfv_info[0].keys()
    for idx, col in enumerate(col_names):
        worksheet.write(0, idx, col)

print(len(nfv_info))
for ulog in nfv_info:
    for col_num, col_info in enumerate(ulog.values()):
        worksheet.write(row_num, col_num, col_info)
    row_num = row_num + 1

workbook.save(file)

if os.path.isfile(file):
    wb = openpyxl.load_workbook(filename=file)
    ws = wb.active
else:
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "tenant1"
    col_names = list(nfv_info[0].keys())
    ws.append(col_names)

for ulog in nfv_info:
    ws.append(list(ulog.values()))
a = ws['B6']
a.font = openpyxl.styles.Font(color="000000", bold=True, size=12, name='微软雅黑')
bd = openpyxl.styles.Side(style='thin', color="000000")
a.border = openpyxl.styles.Border(left=bd, top=bd, right=bd, bottom=bd)
wb.save(file)

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

import openpyxl

# file2的数据在file1里面是否存在，存在返回True
def in_cmdb(ws, project_name, line_name):
    for key, item in enumerate(ws.values):
        if key < 2:
            continue
        if project_name.strip() == item[1].strip() and line_name.strip() == item[3].strip():
            return True
    return False

if __name__ == '__main__':
    file1 = '专线-配置实例导入模板 -20230104.xlsx'
    file2 = 'bk_cmdb_export_inst_sf_privateline .xlsx'

    wb1 = openpyxl.load_workbook(filename=file1)
    ws1 = wb1.active
    wb2 = openpyxl.load_workbook(filename=file2)
    ws2 = wb2.active

    new = openpyxl.Workbook()
    dest_filename = 'new.xlsx'
    new_ws = new.active    
    #ws1 客户名称:item[1] 专线名称：item[3]
    #ws2 客户名称:item[6] 专线名称：item[11]
    for key, item in enumerate(ws2.values):
        if item[6] is None and item[11] is None:
            break
        if key == 0:
            new_ws.append(item)
        print("key:", key)
        if key < 3 or in_cmdb(ws1, item[6], item[11]):
            continue
        new_ws.append(item)
    new.save(filename = dest_filename)