python实现VLOOKUP功能 | 云深不知处

type

status

date

slug

summary

category

icon

password

需求：现有a.xlsx b.xlsx两个表格，都存在appid字段，现在需要把b.xlsx中appid关联的某些列加到a.xlsx数据右边。

上面的需求看着就是VLOOKUP功能，下面使用Python实现

思路：

实现left join，输出DataFrame的内容

使用DataFrame的内容生成新的表格，并美化（可省略）

💡

# 创建DataFrame，下面的df_result只要是符合要求的表格内容就行，内容实现方式有多种 df = pd.DataFrame(df_result)

💡

# 自适应列宽,这里的列宽是根据表头长度来的，故如果表头很短，内容很长，那一列会挤在一起。 for col in ws.columns:

💡

#df_b[column_save]如果简写成df_b，那会取b.xlsx所有的列。column_save是为了取需要的列。

index= 'appid’

column_save = [index,'owner'] df_result = left_join(df_a, df_b[column_save], on=index, how='left')


import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment

# 定义左连接函数
def left_join(df_left, df_right, on, how='left'):
    """
    实现左连接的函数
    :param df_left: 左表
    :param df_right: 右表
    :param on: 连接键
    :param how: 连接方式，默认为左连接
    :return: 左连接结果
    """
    # 使用merge函数实现左连接
    df_result = pd.merge(df_left, df_right, on=on, how=how)
    return df_result

def excelCreate(df_result,tbname):
    # 创建DataFrame
    df = pd.DataFrame(df_result)

    # 创建Excel工作簿
    wb = Workbook()
    ws = wb.active

    # 自定义表格样式
    header_font = Font(name='Calibri', size=12, bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='2F75B5', end_color='2F75B5', fill_type='solid')
    header_border = Border(left=Side(border_style='thin', color='FFFFFF'),
                           right=Side(border_style='thin', color='FFFFFF'),
                           top=Side(border_style='thin', color='FFFFFF'),
                           bottom=Side(border_style='thin', color='FFFFFF'))
    cell_font = Font(name='Calibri', size=12, color='000000')
    cell_border = Border(left=Side(border_style='thin', color='000000'),
                         right=Side(border_style='thin', color='000000'),
                         top=Side(border_style='thin', color='000000'),
                         bottom=Side(border_style='thin', color='000000'))
    cell_alignment = Alignment(horizontal='center', vertical='center')

    # 写入表头
    for col_num, column_title in enumerate(df.columns, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.value = column_title
        cell.font = header_font
        cell.fill = header_fill
        cell.border = header_border

    # 写入数据
    for row_num, row_data in enumerate(df.values, 2):
        for col_num, cell_value in enumerate(row_data, 1):
            cell = ws.cell(row=row_num, column=col_num)
            cell.value = cell_value
            cell.font = cell_font
            cell.border = cell_border
            cell.alignment = cell_alignment

    # 自适应列宽
    for col in ws.columns:
        max_length = 0
        column = col[0].column_letter  # Get the column name
        #print(column)
        for cell in col:
            try:
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 2)
        #print(adjusted_width)
        ws.column_dimensions[column].width = adjusted_width

    # 保存Excel文件
    wb.save(tbname + '.xlsx')
    print(tbname + ".xlsx excel done")

#程序开始
# 读取Excel文件
tbname = 'newxlsx'
df_a = pd.read_excel('a.xlsx')
df_b = pd.read_excel('b.xlsx')
# 调用左连接函数
index= 'appid'
column_save = [index,'owner']
df_result = left_join(df_a, df_b[column_save], on=index, how='left')
excelCreate(df_result,tbname)