一蛇耀、接口說明

圖片類型.png

二嫁蛇、企業(yè)機器人發(fā)送圖片消息

1、設計excel模板厦坛，截圖生成圖片

代碼：

# -*- coding: utf-8 -*-
import requests
import hashlib
import base64
import xlwings as xw
import time,os
import pandas as pd
from PIL import ImageGrab
from openpyxl import load_workbook

#發(fā)送圖片_傳入圖片本地路徑/文本
def post_image(url,image):
    with open(image, 'rb') as file:
    #轉換圖片成base64格式
        data = file.read()
        encodestr = base64.b64encode(data)
        image_data = str(encodestr, 'utf-8')
    #圖片的MD5值
    with open(image, 'rb') as file:
        md = hashlib.md5()
        md.update(file.read())
        image_md5 = md.hexdigest()
    data = {"msgtype": "image",
            "image": {"base64": image_data,
                      "md5": image_md5
                     }
            }
    result = requests.post(url, json=data)
    return(result)

# excel截圖并發(fā)送
def photo_post(excel_file,n,url):
    app = xw.App(visible=True, add_book=False)  # 使用xlwings的app啟動
    # 截圖
    wb = app.books.open(excel_file)  # 打開文件
    num = 0
    while num < n:  # 參數(shù) excel sheet 是第幾個
        sheet = wb.sheets[num]  # 選定sheet
        photo_range = sheet.used_range  # 獲取有內(nèi)容的range
        # print(photo_range.value)
        photo_range.api.CopyPicture()  # 復制圖片區(qū)域
        sheet.api.Paste()  # 粘貼
        img_name = 'photo' + str(num)
        pic = sheet.pictures[0]  # 當前圖片
        pic.api.Copy()  # 復制圖片
        time.sleep(10)

        img = ImageGrab.grabclipboard()  # 獲取剪貼板的圖片數(shù)據(jù)
        img_name = img_name + ".png"
        img.save(img_name)  # 保存圖片

        post_image(url, img_name) # 發(fā)送圖片
        num = num + 1
        pic.delete()  # 刪除sheet上的圖片
    wb.close()  # 之前已經(jīng)保存五垮，直接關閉
    app.quit()

# 指定sheet寫入df
def add_sheet(excel_0, df_name, excel_1):
    # 寫入源數(shù)據(jù)，Sheet:data
    wb = load_workbook(excel_0)
    ws = wb.get_sheet_by_name('data')
    value = df_name.to_numpy()
    for i in range(len(value)):
        ws.append(list(value[i]))
    # 寫入時間,Sheet:pic
    now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    txt = '截止時間：' + str(now_time)
    ws2 = wb.get_sheet_by_name('pic')
    ws2.cell(row=2, column=4).value = txt

    wb.save(excel_1)
    wb.close()

if __name__ == "__main__":
    path_0 = os.path.dirname(__file__)
    excel_0 = path_0 + '/mb_0.xlsx' # excel原始模板
    excel_1 = path_0 + '/mb_1.xlsx' # 數(shù)據(jù)處理后的目標文件

    # 獲取源數(shù)據(jù)--示例數(shù)據(jù)
    data_fruit = pd.DataFrame({'type':['圣女果','車厘子','草莓','香蕉','蘋果','芒果','葡萄','沙糖桔','丑橘','西瓜','哈密瓜','牛奶棗'],
                           'sales_num':[1200,500,800,790,470,390,250,2560,1040,880,2800,300],
                           'sales_amt':[2.2,2.3,4.2,1.1,0.8,1.4,2.6,6.3,1.8,0.9,7.2,2.5]
                           })
    data_fruit['rank'] = data_fruit['sales_num'].rank(ascending=False, method='first') # 銷量排名
    data_fruit = data_fruit[['rank','type','sales_num','sales_amt']]

    all_num = data_fruit['sales_num'].sum() # 銷量總計
    all_amt = data_fruit['sales_amt'].sum() # 銷售額總計
    df1 = pd.DataFrame({'rank':'總計','type':['總計'],'sales_num':[all_num],'sales_amt':[all_amt]})
    data_fruit = data_fruit.append(df1, ignore_index=True) # 新增總計記錄

    # 數(shù)據(jù)寫入模板excel的data Sheet中
    add_sheet(excel_0, data_fruit, excel_1)
    time.sleep(10)

    # 截圖發(fā)送
    urlme = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=xxx'
    photo_post(excel_1, 1, urlme)

執(zhí)行結果：

excel模板_生成截圖.png

說明：

思路大致可分為3步：
1杜秸、數(shù)據(jù)源獲取與處理
2放仗、數(shù)據(jù)源寫入模板excel中，根據(jù)事先設置好的公式樣式撬碟，自動生成所需表格樣式
3诞挨、打開excel指定sheet，按需截圖發(fā)送

excel模板如下：

excel模板_數(shù)據(jù)源sheet.png

excel模板_目標圖片sheet.png

2呢蛤、設計底圖惶傻，往底圖中寫入數(shù)據(jù)

代碼：

# -*- coding:utf-8 -*-
# 水果銷售額大PK
import pandas as pd
import os,time,xlrd
import matplotlib.pyplot as plt
from PIL import Image, ImageFont, ImageDraw
import base64,hashlib,requests

#發(fā)送圖片_傳入圖片本地路徑/文本
def post_image(url,image):
    with open(image, 'rb') as file:
    #轉換圖片成base64格式
        data = file.read()
        encodestr = base64.b64encode(data)
        image_data = str(encodestr, 'utf-8')
    #圖片的MD5值
    with open(image, 'rb') as file:
        md = hashlib.md5()
        md.update(file.read())
        image_md5 = md.hexdigest()
    data = {"msgtype": "image",
            "image": {"base64": image_data,
                      "md5": image_md5
                     }
            }
    result = requests.post(url, json=data)
    return(result)

# 獲取pk水果信息
def read_infos(path):
    f = open(path,"r",encoding='utf-8-sig')
    lines = f.readlines()
    fruit_infos = {}
    for line in lines:
        line = line.strip('\n')
        a = line.split(',')
        a0 = a[0]
        a1 = a[1:]
        fruit_infos[a0] = {'fruit_infos':a1}
    f.close()
    print(fruit_infos)
    return(fruit_infos)

# 批量繪制 折線圖
def draw_jpg(path, list_x):
    f, ax = plt.subplots(figsize=(14, 14))
    # 設置橫軸,31天/30天均可用
    # 攻守分別為 0 1
    y1 = list_x[0]
    y2 = list_x[1]
    x = [i + 1 for i in range(len(y1))]

    # 設置 X軸長短 15日 元素*2 15日以后為31
    if len(y1) <= 10:
        lenth = 2 * len(y1)
        y3 = [yi0 * 3 for yi0 in list_x[0]]
        y4 = [yi1 * 3 for yi1 in list_x[1]]
    elif len(y1) <= 20:
        lenth = 1.5 * len(y1)
        y3 = [yi0 * 2 for yi0 in list_x[0]]
        y4 = [yi1 * 2 for yi1 in list_x[1]]
    else:
        lenth = 31
        y3 = y1
        y4 = y2
    axes = plt.axes()
    axes.set_xlim([0, lenth])
    plt.plot(x, y1, label='xxx', c='Coral', lw=15)
    plt.plot(x, y2, label='yyy', c='slategray', lw=15)
    plt.plot(x, y3, label='xxx', c='white', lw=1)
    plt.plot(x, y4, label='yyy', c='white', lw=1)
    # plt.show()r blue
    # 去掉刻度 + 坐標軸的方法：
    # plt.xticks([]
    plt.axis('off')
    f.savefig(path)

# 本月每日累計（T+1），示例源數(shù)據(jù)
def req_data_daily(file,sheet):
    month_data = pd.read_excel(file,sheet_name=sheet)
    return month_data

# 今日實時其障，示例源數(shù)據(jù)
def req_data_today(file,sheet):
    day_data = pd.read_excel(file,sheet_name=sheet)
    return day_data

if __name__ == "__main__":
    path_0 = os.path.dirname(__file__)
    path_font = path_0 + '/ops/font/'
    path_txt = path_0 + '/ops/fruit_pk_infos.txt'
    path_xlsx = path_0 + '/ops/data.xlsx'  # 示例數(shù)據(jù)
    path_i = path_0 + '/ops/'

    fruit_items = read_infos(path_txt)
    # 水果明細（導入）
    fruit_list = [k for k, v in fruit_items.items()]
    print("合計水果：" + str(len(fruit_list)) + "種")

    # 獲取每日累計(T+1)數(shù)據(jù)银室、今日實時數(shù)據(jù)
    daily_data = req_data_daily(path_xlsx, 'month')
    daily_data_dict = daily_data.T.to_dict()

    today_data = req_data_today(path_xlsx, 'today')
    today_data_dict1 = today_data.set_index('fruit_name').T.to_dict(orient="records")
    today_data_dict = today_data_dict1[0]

    # 讀取每日累計數(shù)據(jù)
    for fruit_name in fruit_list:
        fruit_new_m_list = []
        for i in range(len(daily_data)):
            if daily_data_dict[i]['fruit_name'] == fruit_name:
                fruit_new_m_list.append(daily_data_dict[i]['sales'])
        # 默認  按日期排序
        fruit_items[fruit_name]['data_list'] = fruit_new_m_list

    # 轉化為 配對組數(shù)據(jù)
    draw_dic = {}
    for ii in range(6):
        i = ii + 1
        dic_i = {}
        for fruit_name in fruit_list:
            fruit_sales = fruit_items[fruit_name]
            pk_no = int(fruit_sales['fruit_infos'][1])
            if pk_no == i:
                # 今日值 無則為0
                day_stu_cnt = today_data_dict.get(fruit_name, 0)
                fruit_sales = fruit_items[fruit_name]
                pk_no = int(fruit_sales['fruit_infos'][1]) # pk組號
                gs_no = int(fruit_sales['fruit_infos'][2]) # 攻方1 守方0
                rate_type = round(float(fruit_sales['fruit_infos'][0]), 1) # pk系數(shù)
                # 新生成一列：昨日原始數(shù)據(jù)+今日值，附加在月累計新簽列表
                fruit_data = fruit_sales['data_list']
                xxx = fruit_data[-1]
                xxx += day_stu_cnt
                fruit_data.append(xxx)

                # 乘以系數(shù)后數(shù)據(jù)
                fruit_data_new = [round(x * rate_type, 1) for x in fruit_data]
                # 水果待寫入圖片數(shù)據(jù)
                fruit_w = fruit_name + '-' + str(rate_type) + ':  ' + str(fruit_data_new[-1])
                fruit_x = [fruit_name, fruit_data_new, fruit_data, rate_type, fruit_w, fruit_data[-1]]
                if pk_no == i and gs_no == 0:
                    dic_i[0] = fruit_x
                elif pk_no == i and gs_no == 1:
                    dic_i[1] = fruit_x

        draw_path = path_i + str(i) +'.jpg'
        draw_dic[i] = dic_i

        #傳參畫折線圖
        draw_jpg(draw_path,[dic_i[0][1],dic_i[1][1]])
        #判定勝負當前勝負方
        win_type_now = ''
        fs = dic_i[0][1][-1]
        jg = dic_i[1][1][-1]
        if fs > jg:
            dic_i[2] = dic_i[0][0]
            dic_i[3] = (13,182,241)
            win_type_now = 'fs'
        elif fs < jg:
            dic_i[2] = dic_i[1][0]
            dic_i[3] = (81,86,102)
            win_type_now = 'jg'
        else:
            dic_i[2] = dic_i[0][0] + ' 平 ' + dic_i[1][0]
            dic_i[3] = (13,182,241)
            win_type_now = 'db'
        #判定早晨勝負方
        win_type_past = ''
        fs0 = dic_i[0][1][-2]
        jg0 = dic_i[1][1][-2]
        if fs0 > jg0:
            win_type_past = 'fs'
        elif fs0 < jg0:
            win_type_past = 'jg'
        else:
            win_type_past = 'db'
        #判斷是否變化
        if win_type_past == win_type_now:
            dic_i[4] = 'change0'
        elif win_type_now != 'db':
            dic_i[4] = 'change1'
        else:
            dic_i[4] = 'change2'

    print('圖完畢励翼，傳參完畢')

    #圖中畫入 折線圖
    img_back = Image.open(path_i + 'month.jpg')
    for y in range(3):
        for x in range(2):
            n = x + 2*y + 1
            if n <= 12:
                jpg_path =path_i + str(n) +'.jpg'
                imgs = Image.open(jpg_path)
                imgs1 = imgs.resize((1770, 1460))
                img_back.paste(imgs1, (110 + x * 1800, 860 + 1500 * y))

    # 圖中寫入文字
    draw = ImageDraw.Draw(img_back)

    # 寫入當前日期
    path_font0 = path_font + 'times new roman bold.ttf'
    Font = ImageFont.truetype(path_font0, 80)
    draw.text([2850, 500], time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()), (0, 0, 0), font=Font)

    #寫入贏家 以及 當前值
    #今日PK 戰(zhàn)報
    comment_text = []
    path_font1 = path_font + 'msyhbd.ttf'
    Font1 = ImageFont.truetype(path_font1, 120)
    path_font2 = path_font + 'msyhbd.ttf'
    Font2 = ImageFont.truetype(path_font2, 200)
    for y in range(3):
        for x in range(2):
            n = x + 2*y + 1
            if n <= 12 :
                #print(draw_text_dic[n])
                txt_list = draw_dic[n]
                # 寫入贏家
                draw.text([160 + x * 1800, 900 + 1600* y], txt_list[2], (81, 86, 102), font=Font2)
                # 寫入防數(shù)據(jù)
                draw.text([160 + x * 1800, 1200 + 1600 * y], txt_list[0][4], (255, 127, 80), font=Font1)
                # 寫入攻數(shù)據(jù)
                draw.text([160 + x * 1800, 1400 + 1600 * y], txt_list[1][4], (81, 86, 102), font=Font1)

                if txt_list[4] == 'change1':
                    t1 = txt_list[0][0]+'VS'+txt_list[1][0]
                    t2 = txt_list[2]+'反超成功'+'蜈敢，太棒啦!'+'\n\n'
                    comment_text.append([t1,t2])
                elif txt_list[4] == 'change2':
                    t1 = txt_list[0][0]+'VS'+txt_list[1][0]
                    t2 = txt_list[2]+',都加油噢！\n\n'
                    comment_text.append([t1,t2])

    path_local =path_i + 'month_all.jpg'
    img_back.save(path_local)

    #每日 日報 總結播報
    path_font7 = path_font + 'msyhbd.ttf'
    Font7 = ImageFont.truetype(path_font7, 160)
    Font8 = ImageFont.truetype(path_font7, 200)

    urlme = 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=xxx'

    if len(comment_text)>0:
        for txt_all in comment_text:
            img_back_daily = Image.open(path_i + 'daily.jpg')
            draw1 = ImageDraw.Draw(img_back_daily)
            draw1.text([2800,780],time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),(0,0,0), font=Font)

            t1,t2 = txt_all[0],txt_all[1]
            draw1.text([800,1400],'《'+t1+' pk簡報》',(81,86,102), font=Font7)
            draw1.text([800,1800],'  '+t2,(255, 127, 80), font=Font8)
            path_local1 =path_i + 'last_daily.jpg'
            img_back_daily.save(path_local1)
            post_image(urlme, path_local1)

    post_image(urlme, path_local)

執(zhí)行結果：

robot發(fā)送圖片.png

month_all.jpg

last_daily.jpg

說明：

思路大致可分為3步：
1抚笔、數(shù)據(jù)源獲取與處理
2扶认、PS設計好底圖
3、數(shù)據(jù)寫入底圖殊橙，并發(fā)送（涉及到坐標運算）

底圖模板如下：

month.jpg

daily.jpg