from openpyxl import Workbook
# 创建一个 workbook
wb = Workbook()
# 获取被激活的 worksheet
ws = wb.active
# 设置单元格内容
ws['A1'] = 42
# 设置一行内容
ws.append([1, 2, 3])
# python 数据类型可以被自动转换
import datetime
ws['A2'] = datetime.datetime.now()
# 保存 Excel 文件
wb.save("sample.xlsx")

from openpyxl import load_workbook

wb = load_workbook('test.xlsx')

# 显示文档中包含的 表单 名称
print(wb.sheetnames)

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws1 = wb.create_sheet("sheet")  #创建一个 sheet 名为 sheet
ws1.title = "新表单"  # 设置 sheet 标题
ws2 = wb.create_sheet("mysheet", 0) # 创建一个 sheet，插入到最前面 默认插在后面
ws2.title = u"你好"  # 设置 sheet 标题

ws1.sheet_properties.tabColor = "1072BA"  # 设置 sheet 标签背景色

# 获取 sheet
ws3 = wb.get_sheet_by_name(u"你好")
ws4 = wb['New Title']

# 复制 sheet
ws1_copy = wb.copy_worksheet(ws1)

# 删除 sheet
wb.remove(ws1)

ws1 = wb.create_sheet("Mysheet")  #创建一个sheet
# 通过单元格名称设置
ws1["A1"]=123.11
ws1["B2"]="你好"

# 通过行列坐标设置
d = ws1.cell(row=4, column=2, value=10)

# 操作单列
for cell in ws["A"]:
    print(cell.value)
# 操作单行
for cell in ws["1"]:
    print(cell.value)
# 操作多列
for column in ws['A:C']:
    for cell in column:
        print(cell.value)
# 操作多行
for row in ws['1:3']:
    for cell in row:
        print(cell.value)
# 指定范围
for row in ws['A1:C3']:
    for cell in row:
        print(cell.value)

# 所有行
for row in ws.iter_rows():
    for cell in row:
        print(cell.value)
# 所有咧
for column in ws.iter_cols():
    for cell in column:
        print(cell.value)

ws.append((1,2,3))

# 合并
ws.merge_cells('A2:D2')
# 解除合并
ws.unmerge_cells('A2:D2')

ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4)
ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4)

from openpyxl.styles import Font, PatternFill, Border, Side, Alignment, Protection
from openpyxl.styles import numbers

wb = Workbook()
ws = wb.active
ws.cell(row=1, column=1, value='宋体').font = Font(name=u'宋体', size=12, bold=True, color='FF0000')
ws.cell(row=2, column=2, value='右对齐').alignment = Alignment(horizontal='right')
ws.cell(row=3, column=3, value='填充渐变色').fill = PatternFill(fill_type='solid', start_color='FF0000')
ws.cell(row=4, column=4, value='设置边线').border = Border(left=Side(border_style='thin', color='FF0000'), right= Side(border_style='thin', color='FF0000'))
ws.cell(row=5, column=5, value='受保护的').protection = Protection(locked=True, hidden=True)
ws.cell(row=6, column=6, value=0.54).number_format =numbers.FORMAT_PERCENTAGE

font = Font(bold=True)

# 遍历范围内的单元格
for row in ws['A1:C3']:
    for cell in row:
        cell.font = font

# 设置整行
row = ws.row_dimensions[1]
row.font = font

# 设置整列
column = ws.column_dimensions["A"]
column.font = font

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

rows = [
    ('月份', '苹果', '香蕉'),
    (1, 43, 25),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]

for row in rows:
    ws.append(row)

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "销量柱状图"
chart1.y_axis.title = '销量'
chart1.x_axis.title = '月份'

data = Reference(ws, min_col=2, min_row=1, max_row=8, max_col=3)
series = Reference(ws, min_col=1, min_row=2, max_row=8)
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(series)
ws.add_chart(chart1, "A10")

from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference

data = [
    ['水果', '销量'],
    ['苹果', 50],
    ['樱桃', 30],
    ['橘子', 10],
    ['香蕉', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie = PieChart()
pie.title = "水果销量占比"
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)

ws.add_chart(pie, "D1")

pip install mitmproxy

mitmweb

# articles.py
import html
import requests
import utils

from urllib.parse import urlsplit

class Articles(object):
    """文章信息"""

    def __init__(self， appmsg_token， cookie):
        # 具有时效性
        self.appmsg_token = appmsg_token
        
        self.headers = {
            "User-Agent": "Mozilla/5.0 AppleWebKit/537.36 (KHTML， like Gecko) Version/4.0Chrome/57.0.2987.132 MQQBrowser/6.2 Mobile"，
            "Cookie": cookie
        }
        
        self.data = {
            "is_only_read": "1"，
            "is_temp_url": "0"，
            "appmsg_type": "9"，
        }


    def read_like_nums(self， article_url):
        """获取数据"""
        appmsgstat = self.get_appmsgext(article_url)["appmsgstat"]
        return appmsgstat["read_num"]， appmsgstat["old_like_num"]， appmsgstat["like_num"]

    def get_params(self， article_url):
        """
        获取到文章url上的请求参数
        :param article_url: 文章 url
        :return: 
        """
        # url转义处理
        article_url = html.unescape(article_url)
        """获取文章链接的参数"""
        url_params = utils.str_to_dict(urlsplit(article_url).query， "&"， "=")
        return url_params

    def get_appmsgext(self， article_url):
        """
        请求阅读数
        :param article_url: 文章 url
        :return: 
        """
        url_params = self.get_params(article_url)

        appmsgext_url = "https://mp.weixin.qq.com/mp/getappmsgext?appmsg_token={}&x5=0".format(self.appmsg_token)
        self.data.update(url_params)

        appmsgext_json = requests.post(
            appmsgext_url， headers=self.headers， data=self.data).json()

        if "appmsgstat" not in appmsgext_json.keys():
            raise Exception(appmsgext_json)
        return appmsgext_json


if __name__ == '__main__':
    info = Articles('1068_XQoMoGGBYG8Tf8k23jfdBr2H_LNekAAlDDUe2aG13TN2fer8xOSMyrLV6s-yWESt8qg5I2fJr1r9n5Y5'， 'rewardsn=; wxtokenkey=777; wxuin=1681274216; devicetype=android-29; version=27001037; lang=zh_CN; pass_ticket=H9Osk2CMhrlH34mQ3w2PLv/RAVoiDxweAdyGh/Woa1qwGy2jGATJ6hhg7syTQ9nk; wap_sid2=COjq2KEGEnBPTHRVOHlYV2U4dnRqaWZqRXBqaWl3Xy1saXVWYllIVjAzdlM1VkNDNHgxeWpHOG9pckdkREMwTFEwYmNWMl9FZWtRU3pRRnhDS0pyV1BaZUVMWXN1ZWN0WnZ6aHFXdVBnbVhTY21BYnBSUXNCQUFBMLLAjfgFOA1AAQ==')
    a， b，c = info.read_like_nums('http://mp.weixin.qq.com/s?__biz=MzU1NDk2MzQyNg==&mid=2247486254&idx=1&sn=c3a47f4bf72b1ca85c99190597e0c190&chksm=fbdad3a3ccad5ab55f6ef1f4d5b8f97887b4a344c67f9186d5802a209693de582aac6429a91c&scene=27#wechat_redirect')
    print(a， b， c)

# 阅读数 点赞数 再看数
1561 23 18

# -s 运行的python脚本, -w 将截取的内容保持到文件
mitmdump -s write_cookie.py -w outfile mp.weixin.qq.com/mp/getappmsgext

# coding: utf-8
# write_cookie.py
import urllib
import sys

from mitmproxy import http

# command: mitmdump -s write_cookie.py -w outfile mp.weixin.qq.com/mp/getappmsgext

class WriterCookie:
    """
    mitmproxy的监听脚本，写入cookie和url到文件
    """

    def __init__(self， outfile: str) -> None:
        self.f = open(outfile， "w")

    def response(self， flow: http.HTTPFlow) -> None:
        """
        完整的response响应
        :param flow: flow实例，
        """
        # 获取url
        url = urllib.parse.unquote(flow.request.url)

        # 将url和cookie写入文件
        if "mp.weixin.qq.com/mp/getappmsgext" in url:
            self.f.write(url + '\n')
            self.f.write(str(flow.request.cookies))
            self.f.close()
            # 退出
            exit()

# 第四个命令中的参数
addons = [WriterCookie(sys.argv[4])]

# read_cookie.py
import re
import os

class ReadCookie(object):
    """
    启动write_cookie.py 和 解析cookie文件，
    """

    def __init__(self， outfile):
        self.outfile = outfile

    def parse_cookie(self):
        """
        解析cookie
        :return: appmsg_token， biz， cookie_str·
        """
        f = open(self.outfile)
        lines = f.readlines()
        appmsg_token_string = re.findall("appmsg_token.+?&"， lines[0])
        biz_string = re.findall('__biz.+?&'， lines[0])
        appmsg_token = appmsg_token_string[0].split("=")[1][:-1]
        biz = biz_string[0].split("__biz=")[1][:-1]

        cookie_str = '; '.join(lines[1][15:-2].split(']， [')).replace('

示例结果



cookie.txt 文件内容解析后

1
2
3
appmsg_token：1068_av3JWyDn2XCS2fwFj3ICCnwArRb2kU4Y5Y5m9Z9NkWoCOszl3a-YHFfBkAguUlYQJi2dWo83AQT4FsNK 
biz：MzU1NDk2MzQyNg== 
cookie：rewardsn=; wxtokenkey=777; wxuin=1681274216; devicetype=android-29; version=27001037; lang=zh_CN; pass_ticket=H9Osk2CMhrlH34mQ3w2PLv/RAVoiDxweAdyGh/Woa1qwGy2jGATJ6hhg7syTQ9nk; wap_sid2=COjq2KEGEnBPTHRVOHlYV2U4dnRqaWZqRXBqaWktTHpUSEJnTTdhU0hVZTEtZXpZZEY4a3lNY29zc0VZeEFvLS01YmJRRnQ5eFRmR2dOY29nUWdKSTRVWG13WE1obGs1blhQcVh0V18tRnBSRnVlc1VhOHNCQUFBMPeIjfgFOA1AAQ==


ReadCookie 模块可以自动获取到 appmsg_token 和 cookie，将它们当作参数一样传递到 Articles 模块中，就能解放双手再也不要复制黏贴 appmsg_token 和 cookie 了，只要在 appmsg_token 过期时刷新一下公众号文章就可以了，省心又省时

批量抓取

在公众号历史消息界面将屏幕向上滑动，每次都可以加载 10 条历史消息，盲猜这个翻页就是批量抓取的请求链接，用 MitmProxy 的 WEBUI 界面在 Response 面板查找一下，找到一个是 https://mp.weixin.qq.com/mp/profile_ext?action=getmsg&__biz=MzU1NDk2MzQyNg==&f=json&offset=10&count=10… 的链接，它的响应返回值中有多个文章标题，再看这个链接上有 offset=10&count=10 参数，一看就是翻页偏移量和每页显示的条数，就它了



在链接中比较重要的参数有：__biz、offset、pass_ticket、appmsg_token ，这些数据都可以在 cookie 和 appmsg_token 中得到。

1
2
3
4
5
6
7
8
9
10
11
12
# utils.py
# 工具模块，将字符串变成字典
def str_to_dict(s， join_symbol="\n"， split_symbol=":"):
    s_list = s.split(join_symbol)
    data = dict()
    for item in s_list:
        item = item.strip()
        if item:
            k， v = item.split(split_symbol， 1)
            data[k] = v.strip()
    return data



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
# coding:utf-8
# wxCrawler.py
import os
import requests
import json
import urllib3
import utils


class WxCrawler(object):
    """翻页内容抓取"""
    urllib3.disable_warnings()

    def __init__(self， appmsg_token， biz， cookie， begin_page_index = 0， end_page_index = 100):
        # 起始页数
        self.begin_page_index = begin_page_index
        # 结束页数
        self.end_page_index = end_page_index
        # 抓了多少条了
        self.num = 1

        self.appmsg_token = appmsg_token
        self.biz = biz
        self.headers = {
            "User-Agent": "Mozilla/5.0 AppleWebKit/537.36 (KHTML， like Gecko) Version/4.0Chrome/57.0.2987.132 MQQBrowser/6.2 Mobile"，
            "Cookie": cookie
        }
        self.cookie = cookie

    def article_list(self， context):
        articles = json.loads(context).get('general_msg_list')
        return json.loads(articles)

    def run(self):

        # 翻页地址
        page_url = "https://mp.weixin.qq.com/mp/profile_ext?action=getmsg&__biz={}&f=json&offset={}&count=10&is_ok=1&scene=&uin=777&key=777&pass_ticket={}&wxtoken=&appmsg_token=" + self.appmsg_token + "&x5=0f=json"
        # 将 cookie 字典化
        wx_dict = utils.str_to_dict(self.cookie， join_symbol='; '， split_symbol='=')
        # 请求地址
        response = requests.get(page_url.format(self.biz， self.begin_page_index * 10， wx_dict['pass_ticket'])， headers=self.headers， verify=False)
        # 将文章列表字典化
        articles = self.article_list(response.text)

        for a in articles['list']:
            # 公众号中主条
            if 'app_msg_ext_info' in a.keys() and '' != a.get('app_msg_ext_info').get('content_url'， ''):
                print(str(self.num) + "条"， a.get('app_msg_ext_info').get('title')， a.get('app_msg_ext_info').get('content_url'))
            # 公众号中副条
            if 'app_msg_ext_info' in a.keys():
                for m in a.get('app_msg_ext_info').get('multi_app_msg_item_list'， []):
                    print(str(self.num) + "条"， m.get('title')， a.get('content_url'))

            self.num = self.num + 1

        self.is_exit_or_continue()
        # 递归调用
        self.run()

    def is_exit_or_continue(self):
        self.begin_page_index = self.begin_page_index + 1

        if self.begin_page_index > self.end_page_index:
            os.exit()


最后用自动化cookie启动程序后刷新公众号文章

1
2
3
4
5
6
7
8
9
10
11
12
13
# coding:utf-8
# main.py
from read_cookie import ReadCookie
from wxCrawler import WxCrawler

"""程序启动类"""ss
if __name__ == '__main__':
    cookie = ReadCookie('E:/python/cookie.txt')

    cookie.write_cookie()
    appmsg_token， biz， cookie_str = cookie.parse_cookie()
    wx = WxCrawler(appmsg_token， biz， cookie_str)
    wx.run()


示例结果



总结

虽然本文可能有那么一点点一点点的标题党，并没有完全的自动抓取数据，还需要人为的刷新一下公众号文章。希望小伙伴们不要介意哈。如果需要抓取微信文章，推荐参考本公众号的【第 129 天：爬取微信公众号文章内容】。


  
示例代码 使用 MitmProxy 自动抓取微信公众号阅读数、点赞和再看数据

'，'').replace('， '， '=')
        return appmsg_token， biz， cookie_str

    def write_cookie(self):
        """
        启动 write_cookie。py
        :return:
        """

        #当前文件路径
        path = os.path.split(os.path.realpath(__file__))[0]
        # mitmdump -s 执行脚本 -w 保存到文件 本命令
        command = "mitmdump -s {}/write_cookie.py -w {} mp.weixin.qq.com/mp/getappmsgext".format(
            path， self.outfile)

        os.system(command)


if __name__ == '__main__':
    rc = ReadCookie('cookie.txt')
    rc.write_cookie()
    appmsg_token， biz， cookie_str = rc.parse_cookie()
    print("appmsg_token：" + appmsg_token ， "\nbiz：" + biz， "\ncookie："+cookie_str)

appmsg_token：1068_av3JWyDn2XCS2fwFj3ICCnwArRb2kU4Y5Y5m9Z9NkWoCOszl3a-YHFfBkAguUlYQJi2dWo83AQT4FsNK 
biz：MzU1NDk2MzQyNg== 
cookie：rewardsn=; wxtokenkey=777; wxuin=1681274216; devicetype=android-29; version=27001037; lang=zh_CN; pass_ticket=H9Osk2CMhrlH34mQ3w2PLv/RAVoiDxweAdyGh/Woa1qwGy2jGATJ6hhg7syTQ9nk; wap_sid2=COjq2KEGEnBPTHRVOHlYV2U4dnRqaWZqRXBqaWktTHpUSEJnTTdhU0hVZTEtZXpZZEY4a3lNY29zc0VZeEFvLS01YmJRRnQ5eFRmR2dOY29nUWdKSTRVWG13WE1obGs1blhQcVh0V18tRnBSRnVlc1VhOHNCQUFBMPeIjfgFOA1AAQ==

# utils.py
# 工具模块，将字符串变成字典
def str_to_dict(s， join_symbol="\n"， split_symbol=":"):
    s_list = s.split(join_symbol)
    data = dict()
    for item in s_list:
        item = item.strip()
        if item:
            k， v = item.split(split_symbol， 1)
            data[k] = v.strip()
    return data

# coding:utf-8
# wxCrawler.py
import os
import requests
import json
import urllib3
import utils


class WxCrawler(object):
    """翻页内容抓取"""
    urllib3.disable_warnings()

    def __init__(self， appmsg_token， biz， cookie， begin_page_index = 0， end_page_index = 100):
        # 起始页数
        self.begin_page_index = begin_page_index
        # 结束页数
        self.end_page_index = end_page_index
        # 抓了多少条了
        self.num = 1

        self.appmsg_token = appmsg_token
        self.biz = biz
        self.headers = {
            "User-Agent": "Mozilla/5.0 AppleWebKit/537.36 (KHTML， like Gecko) Version/4.0Chrome/57.0.2987.132 MQQBrowser/6.2 Mobile"，
            "Cookie": cookie
        }
        self.cookie = cookie

    def article_list(self， context):
        articles = json.loads(context).get('general_msg_list')
        return json.loads(articles)

    def run(self):

        # 翻页地址
        page_url = "https://mp.weixin.qq.com/mp/profile_ext?action=getmsg&__biz={}&f=json&offset={}&count=10&is_ok=1&scene=&uin=777&key=777&pass_ticket={}&wxtoken=&appmsg_token=" + self.appmsg_token + "&x5=0f=json"
        # 将 cookie 字典化
        wx_dict = utils.str_to_dict(self.cookie， join_symbol='; '， split_symbol='=')
        # 请求地址
        response = requests.get(page_url.format(self.biz， self.begin_page_index * 10， wx_dict['pass_ticket'])， headers=self.headers， verify=False)
        # 将文章列表字典化
        articles = self.article_list(response.text)

        for a in articles['list']:
            # 公众号中主条
            if 'app_msg_ext_info' in a.keys() and '' != a.get('app_msg_ext_info').get('content_url'， ''):
                print(str(self.num) + "条"， a.get('app_msg_ext_info').get('title')， a.get('app_msg_ext_info').get('content_url'))
            # 公众号中副条
            if 'app_msg_ext_info' in a.keys():
                for m in a.get('app_msg_ext_info').get('multi_app_msg_item_list'， []):
                    print(str(self.num) + "条"， m.get('title')， a.get('content_url'))

            self.num = self.num + 1

        self.is_exit_or_continue()
        # 递归调用
        self.run()

    def is_exit_or_continue(self):
        self.begin_page_index = self.begin_page_index + 1

        if self.begin_page_index > self.end_page_index:
            os.exit()

# coding:utf-8
# main.py
from read_cookie import ReadCookie
from wxCrawler import WxCrawler

"""程序启动类"""ss
if __name__ == '__main__':
    cookie = ReadCookie('E:/python/cookie.txt')

    cookie.write_cookie()
    appmsg_token， biz， cookie_str = cookie.parse_cookie()
    wx = WxCrawler(appmsg_token， biz， cookie_str)
    wx.run()