Python对excel进行报表自动化

报表内容

Python程序

from openpyxl import load_workbook
data = load_workbook(r'C:\Users\Administrator\Desktop\Excel自动化\小样单产品毛利核算表.xlsx') #读取excel表
data1 = data['单产品毛利核算表'] #读取sheet表单产品毛利核算表
price_usd = data1['C3'].value #读取售价(USD)
cost_usd = data1['D3'].value #d读取成本(USD)
normal_usd = [price_usd * 0.08, 3.31, 1.71] #计算E3:G3单元格
discount_usd = [price_usd * 0.8, cost_usd, price_usd * 0.064, 3.31, 1.71]
for i in range(len(data1['E3':'G3'][0])): #单元格赋值
    data1['E3':'G3'][0][i].value = normal_usd[i]
for i in range(len(data1['C3':'G3'][0])):
    data1['C4':'G4'][0][i].value = discount_usd[i]
data.save('小样单产品毛利核算表.xlsx') #存储数据

自动化后的结果