资产梳理的神chagpt+excel

a表格第一列是ip 第二列是系统名称

b表格第一列是ip，根据a表格对应的系统生成第二列

import pandas as pd
import os

# 检查文件是否存在
if not os.path.exists('a.xlsx'):
    raise FileNotFoundError("a.xlsx not found in the current directory.")
if not os.path.exists('b.xlsx'):
    raise FileNotFoundError("b.xlsx not found in the current directory.")

# 读取a.xlsx文件
try:
    a_df = pd.read_excel('a.xlsx')
except Exception as e:
    raise Exception(f"Error reading a.xlsx: {e}")

# 读取b.xlsx文件
try:
    b_df = pd.read_excel('b.xlsx')
except Exception as e:
    raise Exception(f"Error reading b.xlsx: {e}")

# 检查a_df和b_df的结构是否正确
if a_df.shape[1] < 2:
    raise ValueError("a.xlsx should have at least two columns.")
if b_df.shape[1] < 1:
    raise ValueError("b.xlsx should have at least one column.")

# 创建一个IP到系统地址的映射字典
ip_to_system = dict(zip(a_df.iloc[:, 0], a_df.iloc[:, 1]))

# 定义一个函数，根据IP查找系统地址
def get_system_address(ip):
    return ip_to_system.get(ip, None)

# 使用apply函数填充b_df中的第二列
b_df[1] = b_df.iloc[:, 0].apply(get_system_address)

# 保存结果到新的Excel文件
try:
    b_df.to_excel('b_updated.xlsx', index=False)
    print("b.xlsx has been updated and saved as b_updated.xlsx")
except Exception as e:
    raise Exception(f"Error saving b_updated.xlsx: {e}")