03-数据持久化--openpyxl(附国家税务局税收政策库爬虫案例)

日常工作中，一般我们自己使用python写入数据时，会选择txt、csv等文件格式，或者直接写入数据库，但是在帮别人写外包爬虫，或者职场中需要使用office时，我们就需要将数据写入Excel了。而在python中操作excel，有一个非常好用的库叫openpyxl，今天我们就来介绍一下它。

下文从4个方面描述openpyxl的用法：
1.创建操作
2.写入操作
3.保存操作
4.读取操作

01.创建操作

将数据写入Excel的第一步是创建工作簿，基本步骤如下：

1. 导入openpyxl相关库
2. 创建工作簿对象
3. 获取工作簿的活动表

'''
1. 导入相关库
'''
from openpyxl import Workbook

'''
2. 创建工作簿 -- 创建新的、空的excel文件
默认创建的工作表命名为sheet
'''
wb = Workbook()
'''
3. 获取工作簿的活动表
默认指向索引为0的工作表【即第一张表】
'''
sheet = wb.active

以上基本操作基本可以满足大部分需求，但是有时候我们想要创建更多的工作表，或者更改工作表的表名，那就要通过以下代码实现。

'''
sheet = wb.create_sheet(命名，插入位置)
'''
sheet = wb.create_sheet("Mysheet") # 在末尾插入(default)
sheet = wb.create_sheet("Mysheet", 0) # 在最前端插入
sheet = wb.create_sheet("Mysheet", -1) # 插入在倒数第二个位置

'''
sheet.title = "新表名"
'''
sheet.title = '重命名的工作表'

02.写入操作

创建完工作簿后，我们就可以写入数据了，写入数据一般有以下两种方法
1. 逐个单元格写入
2. 逐行写入
实际工作中我们可以根据不同的实际情况使用不同的方法，下面就来一一介绍下他们

'''
1. 操作单个单元格
      sheet1["A1"] = "内容"        
      这种方法操作不是很友好，不推荐使用

      Worksheet.cell(row = ,column = ,value = ) 方法          
      操作单个单元格，推荐使用这种方法
'''

sheet['A1'] = 666

sheet.cell(row=1,column=2,value="123")

'''
2. 逐行写入
      工作表对象.append(iterable) 
      写入列表
           sheet.append(list)
      写入字典
            sheet.append(dict)
'''
sheet.append(['Value1','Value2','Value3'])  #写入列表

sheet.append({'Key1': 'Value1','Key2': 'Value2'}) # 写入字典

与在单个单元格内写入不同，写入iterable对象需要注意以下几点：

1. iterable必须是list,tuple,dict,range等类型
2.写入完成后，如果重新打开进行append写入，则本次写入会覆盖上次写入的内容
3. 如果是list，将list从头到尾写入
4. 如果是dict，则会按照相应的键添加对应的值

03.保存操作

写入完成后，我们需要保存数据，往往需要存入指定路径的指定文件夹内，下面就来介绍下如何保存我们的操作

'''使用os模块生成指定的文件夹，然后将数据保存到指定文件夹内'''
import os

# 判断路径是否存在，如果不存在就创建
if not os.path.exists(path):
    os.mkdir(path)
    wb.save("path + "/" + 文件名.xlsx")   #保存数据

这种方法可以帮助我们将excel文件存入指定文件名的文件夹内，如果不需要将excel存入指定文件夹，直接使用wb.save()即可。

04.读取操作

读取数据到python中进行操作，分为以下几步:
1. 导入库
2. 打开文件
3. 获取需要操作的表table
4. 读取相关操作

'''导入库'''
from openpyxl import load_workbook

'''
打开指定文件
wb = load_workbook("文件路径")
'''
wb = load_workbook('sample.xlsx')

'''
获取需要操作的表table
table = wb['表名']
'''
table = wb['sheet1']

'''读取相关操作'''
row = table.max_row             #获取最大行数
column = table.max_column       #获取最大列数

'''读取单个单元格的值'''
Data = table.cell(row=1, column=1).value   # 【注意】行和列是指excel里的行和列，均从1开始

'''获取表名'''
sheet_names = wb.sheetnames
sheet1 = sheet_names[0]

通过上述方法将数据读取到python后，便可以对数据进行操作了，是不是很简单？注意操作完成后，别忘了wb.save()操作。

上述介绍的方法基本可以满足大部分的excel操作需求，如果需要更多的功能，可以查阅官方文档 http://yumos.gitee.io/openpyxl3.0/index.html#document-tutorial

最后，附上一个爬取国家税务总局税收政策库的小案例，使用openpyxl写入excel，实战操作一下openpyxl。【由于写的比较急，没有封装成类拉，大家将就看~】

"""
目标地址：http://www.chinatax.gov.cn/chinatax/n810346/n810825/index.html
1. 采集
    采集以下栏目：
    国家税务总局税收政策库的文号、标题、发文时间

2. 保存
    将所有信息保存到`税务局.xlsx`文件，依据栏目名创建数据表，每个栏目的数据保存到对应的数据表
"""
import requests
import openpyxl

headers = {
    "Cookie":"yfx_c_g_u_id_10003701=_ck20031023441411141558717747518; yfx_mr_10003701=%3A%3Amarket_type_free_search%3A%3A%3A%3Abaidu%3A%3A%3A%3A%3A%3A%3A%3Awww.baidu.com%3A%3A%3A%3Apmf_from_free_search; yfx_key_10003701=; yfx_f_l_v_t_10003701=f_t_1583855054108__r_t_1589904305879__v_t_1589904305879__r_c_1; _Jo0OQK=261D27D4607C671C636D8A35FCD2ACFE9E27EB5478B194EBDD749A125A8EF78DFD2D5393D3C153447D3605F1E5747E3E1661A3EF7EF39AEA38073124D83FC3789EC34275DAD340EB4DDFFF13AA80B4DD4EFFFF13AA80B4DD4EF022996074D0538508C9AF81BA121EF63GJ1Z1QA==; CPS_SESSION=017508EBB5815EE597456BCBBEF0B89B",
    "Host":"www.chinatax.gov.cn",
    "Referer":"http://www.chinatax.gov.cn/chinatax/n810346/n810825/index.html",
    "User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36",
}
url ="http://www.chinatax.gov.cn/api/query"
params = {
    'siteCode': 'bm29000fgk',
    'tab': 'all',
    'key': '9A9C42392D397C5CA6C1BF07E2E0AA6F',
}
wb = openpyxl.Workbook() # 创建新表时，不能重复使用openpyxl.Workbook()，否则会覆盖原数据

def main():
    number = 1  #main函数循环的次数
    while True:
        C6 = input("输入要查询的税种名")
        list_all = []
        for page in range(1,10):
            data = {
                "timeOption": "0",
                "C6": C6,
                "page": page,
                "pageSize": "10",
                "keyPlace": "1",
                "sort": "dateDesc",
                "qt": "*",
            }
            data_list = get_data(url,data)
            list_all.append(data_list)

        list_all = [j for i in list_all for j in i ]
        print(list_all)
        save_date(list_all,number,C6,wb)
        number += 1


def get_data(url,data):
    data_list =[]
    response = requests.post(url,data=data,params=params,headers=headers)
    response.encoding  = response.apparent_encoding
    json = response.json()
    print(json)

    dict_list = json["resultList"]
    for dict in dict_list:
        publishTime = dict["publishTime"]
        title = dict["title"]
        file_number= dict["customHs"]["DOCNOVAL"]
        link = dict["url"]
        list = [publishTime,title,file_number,link]
        data_list.append(list)

    return data_list

def save_date(list_all,number,C6,wb):
    """
    保存数据到excel
    :param list_all: 需要写入的数据
    :param number:创建工作表的参数
    :param C6:输入的税种关键字
    :param wb:创建的工作簿对象
    :return:
    """
    
    sheet = wb.create_sheet(str(number))  # 使用create_sheet创建新表
    sheet.title = f"{C6}"

    for data in list_all:
        sheet.append(data)

    wb.save("税务文件.xlsx")


if __name__ == '__main__':
    main()

作者：爱学习，也爱女票的于饼喵