[Python知识库]【python自动化】基于python的excel自动化办公技巧总结

python批量创建excel文件

import xlwings as xw

app = xw.App(visible=True,add_book=False)

for dept in ['A_section','B_section','C_section','D_section']:
    workbook = app.books.add()
    workbook.save(f'./期末成绩-{dept}.xlsx')

python批量打开excel文件

import xlwings as xw
import os

app = xw.App(visible=True,add_book=False)

for file in os.listdir('.'):
    if file.endswith('.xlsx'):
        app.books.open(file)

按照固定样式新建excle文件

import xlwings as xw
import os
import shutil

datas = [('数学','小明'),('物理','小刚'),('语文','小红'),('英语','小一')]

app = xw.App(visible=False,add_book=False)

for subject,instrutor in datas:
    target_excel = f'{instrutor}-{subject}-成绩.xlsx'
    shutil.copy('数学成绩.xlsx',target_excel)
    workbook = app.books.open(target_excel)
    worksheet = workbook.sheets[0]
    worksheet["A1"].value = worksheet["A1"].value.replace('{subject}',subject)
    worksheet["A2"].value = worksheet["A2"].value.replace('{instrutor}', instrutor)
    workbook.save()
    workbook.close()
app.quit()

python批量重命名excel工作表

import xlwings as xw
import os
import shutil


app = xw.App(visible=False,add_book=False)
workbook = app.books.open('数学成绩.xlsx')

for sheet in workbook.sheets:
    sheet.name = sheet.name.replace('成绩','')

workbook.save()
app.quit()

python实现多个表的vlookup函数

import xlwings as xw
import pandas as pd

# 启动excel APP并打开excel
app = xw.App(visible=False,add_book=False)
workbook = app.books.open('副本副本成绩汇总.xlsx')

# 读取成绩表
df_total = (
    workbook
    .sheets[0]
    .range("A1")
    .options(pd.DataFrame,expand = 'table',index = False,number = int)
    .value
)
print(df_total)

df_student_list = []

for sheet in list(workbook.sheets)[1:]:
    class_ = {
        '电话1':'1班',
        '电话2':'2班',
        '电话3':'3班',
        '电话4':'4班'
        }[sheet.name]
    df_student = (
        sheet.range("A1")
            .options(pd.DataFrame, expand='table', index=False, number=int)
            .value)
    df_student['班级']=class_
    df_student_list.append(df_student)

df_student_all = pd.concat(df_student_list)
print(df_student_all)

# 数据合并
df_merge = pd.merge(
    left=df_total,
    right=df_student_all,
    left_on=['班级','姓名'],
    right_on=['班级','姓名']
)
print(df_merge)

df_merge['电话号码'] = df_merge['电话']
df_merge.drop(columns='电话',inplace=True)

# 更新表格
workbook.sheets[0].range('A1').options(index=False).value = df_merge

# 保存和关闭
workbook.save()
workbook.close()
app.quit()