python自动设置excel作图脚本

有需要的小伙伴自取，我用的xlxswriter库写的报表脚本

import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import xlsxwriter
import os
from openpyxl import load_workbook
#创建excel和sheet
book = xlsxwriter.Workbook('pict.xlsx')

#创建数据
title_x = ['A','B','C','D']
title_y = ['1','2','3','4','5','6','7','8','9','10','11','12']
data_unsplited = np.random.randn(48).tolist()
#定义纠错
#寻找低于-1的值
def find_lower_error(list):
    i_lower = [i for (i,x) in enumerate(list) if x < -1]
    x_lower = [x for (i,x) in enumerate(list) if x < -1]
    return i_lower,x_lower
#寻找高于1的值
def find_upper_error(list):
    i_upper = [i for (i,x) in enumerate(list) if x > 1]
    x_upper = [x for (i, x) in enumerate(list) if x > 1]
    return i_upper,x_upper
lower_ilist,lower_xlist = find_lower_error(data_unsplited)
upper_ilist,upper_xlist =  find_upper_error(data_unsplited)
print(lower_ilist,lower_xlist)
print(upper_ilist,upper_xlist)
#j进行数据单元格标记
def signing_cells(ilist,xlist,style):
    for i in range(len(ilist)):
        if ilist[i] in range(0,12):
            worksheet1.write('B' + str(ilist[i]+2), xlist[i], style)
        elif ilist[i] in range(12,24):
            worksheet1.write('C' + str(ilist[i]-12+2), xlist[i], style)
        elif ilist[i] in range(24,36):
            worksheet1.write('D' + str(ilist[i]-24+2), xlist[i], style)
        elif ilist[i] in range(36,48):
            worksheet1.write('E' + str(ilist[i]-36+2), xlist[i], style)


data1 = data_unsplited[0:12]
data2 = data_unsplited[12:24]
data3 = data_unsplited[24:36]
data4 = data_unsplited[36:48]
data5 = [1]*12
data6 = [-1]*12

#工作表样式
style1 = book.add_format({
    'border': 1, # 边框
    'align': 'center', # 水平居中
    'valign': 'vcenter', # 垂直居中
    'bold': True, # 加粗（默认False）
    'font': u'宋体', # 字体
    'fg_color': 'white', # 背景色
    'color': 'black' #字体颜色
})

style2 = book.add_format({
    'border': 1, # 边框
    'align': 'center', # 水平居中
    'valign': 'vcenter', # 垂直居中
    'bold': True, # 加粗（默认False）
    'font': u'宋体', # 字体
    'fg_color': 'red', # 背景色
    'color': 'black' #字体颜色
})

style3 = book.add_format({
    'border': 1, # 边框
    'align': 'center', # 水平居中
    'valign': 'vcenter', # 垂直居中
    'bold': True, # 加粗（默认False）
    'font': u'宋体', # 字体
    'fg_color': 'yellow', # 背景色
    'color': 'black' #字体颜色
})
worksheet1 = book.add_worksheet('data')
#写入文件
worksheet1.write_row('B1',title_x,style1)
worksheet1.write_column('A2',title_y,style1)
worksheet1.write_column('B2',data1,style1)
worksheet1.write_column('C2',data2,style1)
worksheet1.write_column('D2',data3,style1)
worksheet1.write_column('E2',data4,style1)

signing_cells(lower_ilist,lower_xlist,style2)
signing_cells(upper_ilist,upper_xlist,style3)
#组成dataframe
df_1 = pd.DataFrame(data1,columns = [title_x[0]])
df_2 = pd.DataFrame(data2,columns = [title_x[1]])
df_3 = pd.DataFrame(data3,columns = [title_x[2]])
df_4 = pd.DataFrame(data4,columns = [title_x[3]])
df_5 = pd.DataFrame(data5,columns = ["upper_lim"])
df_6 = pd.DataFrame(data6,columns = ["lower_lim"])
#拼接
df = pd.concat([df_1,df_2,df_3,df_4,df_5,df_6],axis=1)
df.index = range(1,13)
print(df)


#作图

df.plot()
plt.grid(True)
plt.savefig('pci.png',dpi=600,bbox_inches='tight')

# 显示图片并写入表demo
plt.show()
worksheet2 = book.add_worksheet('chart')
worksheet2.insert_image('A1','pci.png')
# os.remove('pci.png')
book.close()
# 不覆盖写方法的调用
# append_df_to_excel('pict.xlsx', df, sheet_name='demo', startcol=0,startrow=0,index=False)