![](http://image109.360doc.com/DownloadImg/2023/07/2200/269497284_1_20230722122451291.png)
![](http://image109.360doc.com/DownloadImg/2023/07/2200/269497284_2_20230722122451604.png)
![](http://image109.360doc.com/DownloadImg/2023/07/2200/269497284_3_20230722122451885.png)
import pandas as pd import openpyxl from openpyxl.styles import Border, Side,colors
wb = openpyxl.load_workbook('../xlwings/vlookup.xlsx') sheet1 = wb.sheetnames[0] df_total = pd.read_excel('../xlwings/vlookup.xlsx',sheet_name=sheet1) students_dic = { '姓名':[], '电话':[], '班级':[]
} for sheet_name in wb.sheetnames[1:]: sheet = wb[sheet_name] for row in sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=1,max_col=1): for cell in row: students_dic['姓名'].append(cell.value) cell.value = sheet_name[:2] students_dic['班级'].append(cell.value) for row in sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=2,max_col=2): for cell in row: students_dic['电话'].append(cell.value)
df_students = pd.DataFrame(data=students_dic) df_merge = pd.merge(left=df_total,right=df_students,left_on=["班级","姓名"],right_on=["班级","姓名"]) df_merge["电话号码"] = df_merge["电话"] df_merge.drop(columns="电话",inplace=True) df_merge = df_merge.sort_values("班级") df_merge.to_excel('../xlwings/result2_vlookup.xlsx',index=False)
border = Border( left=Side(border_style="medium", color=colors.BLACK), right=Side(border_style="medium", color=colors.BLACK), top=Side(border_style="medium", color=colors.BLACK), bottom=Side(border_style="medium", color=colors.BLACK) )
wb2 = openpyxl.load_workbook('../xlwings/result2_vlookup.xlsx') sheet2 = wb2.active sheet2.title = "多个sheet_vlookup_多列" for row in sheet2.iter_rows(min_row=1,max_row=sheet2.max_row, min_col=1, max_col=sheet2.max_column): for cell in row: cell.border=border wb2.save('../xlwings/result2_vlookup.xlsx')
|