python调用xlwings实现excel在原文件上的内容修改和饼图绘制

一、原文件

二、python程序

import xlwings as xw
import numpy as np
import matplotlib.pyplot as plt

plt.rcParams['font.family']='SimHei'

app=xw.App(visible=True,add_book=False)
workbook=app.books.open('data.xlsx')
sheet=workbook.sheets['Sheet1']

# 将李四缺失的数学成绩改成82并保存到原文件
sheet.range('D3').value=82

# 计算总成绩
for i in range(2,5):
    sheet.range(i,6).formula=f'=SUM(C{i}:E{i})'

# 计算平均成绩
for j in range(3,6):
    sheet.range(5,j).formula=f'=AVERAGE('+chr(64+j)+'2'+':'+chr(64+j)+'4'+')'
    # 保留3位小数
    sheet.range(5,j).number_format='0.000'

# 根据总成绩进行排名
for i in range(2,5):
    sheet.range(i,7).formula=f'=RANK.AVG(F{i},F2:F4)'

# 绘制饼图
cmap=plt.colormaps['tab20c']
outer_colors=cmap(np.arange(3)*4)
inner_colors=cmap([0,1,2,4,5,6,8,9,10])
fig,ax=plt.subplots()
ax.pie(np.array(sheet.range('C2:E4').value).sum(axis=1),autopct=lambda x:'{:.0f}'.format(x*np.array(sheet.range('C2:E4').value).sum()/100),
       radius=1,labels=sheet.range('B2:B4').value,pctdistance=0.7,colors=outer_colors,wedgeprops=dict(width=0.3,edgecolor='w'),textprops={'fontsize':10,'color':'k'})
ax.pie(np.array(sheet.range('C2:E4').value).flatten(),autopct=lambda x:'{:.0f}'.format(x*np.array(sheet.range('C2:E4').value).sum()/100),
       radius=0.7,labels=sheet.range('C1:E1').value*3,pctdistance=0.8,colors=inner_colors,wedgeprops=dict(width=0.3,edgecolor='w'),textprops={'fontsize':8,'color':'k'})
ax.set(aspect='equal')
sheet.pictures.add(fig,name='成绩',update=True,left=sheet.range('I1').left,top=sheet.range('I1').top)

print(sheet.range('A1').expand().value)
workbook.save('data.xlsx')
workbook.close()
app.quit()

三、效果展示