当前位置：首页 > news >正文

Python数据规则录入Excel

news 来源：原创 2024/4/28 2:30:46

将获取到的邮件信息，按照Excel表格的规范放入到Excel表格中

具体需求如下：将上面的这个txt数据，转到下面的邮箱表格中，有的为1，没有的为0

没有找个合适的类似于Java中的map那种比较方便的数据结构，就是用比较麻烦的方法实现

具体就是设法得到每一个主题的五个不同规则的邮件，使用一个列表【0,0,0,0,0】来代替

然后获取具体的邮件，如果存在的时候就将对应的index位置的值置为1

# *===================================*
# -*- coding: utf-8 -*-
# * Time : 2019/11/8 15:25
# * Author : zhangsf
# *===================================*
import datetime
import requests
import re
import json
import ssl
import xlwt
#关闭了verify之后，引入urllib3的disable_warnings
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
ssl._create_default_https_context = ssl._create_unverified_context
# post请求
login_url = "https://xxx"
header = {
    'Cookie':'X-BackEndCookie=S-1-5-21-1713849901-2797640346-4150151575-1009910=u56Lnp2ejJqBch|cpc|5512151540_0_452b2b93194849dd98a47f4e192906a4|1572932258714; shshshfpb=hK9LDqjkpSdZgURqoxXh1BA%3D%3D; 3AB9D23F7A4B3C9B=JZTUZROwlBFeOYPn+ptAEdf4jq4hIigKwzIA5I9kybYkKmC/GyKxPus9Uow==; cadataSig=EZYiLFk+gDjH0HkVyk0mXeP8ldFIcXeVq67Ml+5d9EW5Jin4Jj9OgGUTnwS3oAHSx3VVi0Qa6x7UVsLrFwMoWDd6if1+I/x20hWvxNJqTL8GeIuqeVEPeSg9H7VjNoRTvgeAINdGy8Id5swOKCnsZnM4dg==',
'X-OWA-UrlPostData':'%7B%22__type%22%3A%22FindItemJsonRequest%3A%23Exchange%22%2C%22Header%22%3A%7B%22__type%22%3A%22JsonRequestHeaders%3A%23Exchange%22%2C%22RequestServerVersion%22%3A%22Exchange2016%22%2C%22TimeZoneContext%22%3A%7B%22__type%22%3A%22TimeZoneContext%3A%23Exchange%22%2C%22TimeZoneDefinition%22%3A%7B%22__type%22%3A%22TimeZoneDefinitionType%3A%23Exchange%22%2C%22Id%22%3A%22China%20Standard%20Time%22%7D%7D%7D%2C%22Body%22%3A%7B%22__type%22%3A%22FindItemRequest%3A%23Exchange%22%2C%22ItemShape%22%3A%7B%22__type%22%3A%22ItemResponseShape%3A%23Exchange%22%2C%22BaseShape%22%3A%22IdOnly%22%7D%2C%22ParentFolderIds%22%3A%5B%7B%22__type%22%3A%22DistinguishedFolderId%3A%23Exchange%22%2C%22Id%22%3A%22inbox%22%7D%5D%2C%22Traversal%22%3A%22Shallow%22%2C%22Paging%22%3A%7B%22__type%22%3A%22SeekToConditionPageView%3A%23Exchange%22%2C%22BasePoint%22%3A%22Beginning%22%2C%22Condition%22%3A%7B%22__type%22%3A%22RestrictionType%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22IsEqualTo%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22InstanceKey%22%7D%2C%22FieldURIOrConstant%22%3A%7B%22__type%22%3A%22FieldURIOrConstantType%3A%23Exchange%22%2C%22Item%22%3A%7B%22__type%22%3A%22Constant%3A%23Exchange%22%2C%22Value%22%3A%22AQAAAAAAAQwBAAAAB6mSYAAAAAA%3D%22%7D%7D%7D%7D%2C%22MaxEntriesReturned%22%3A311%7D%2C%22ViewFilter%22%3A%22All%22%2C%22IsWarmUpSearch%22%3Afalse%2C%22FocusedViewFilter%22%3A-1%2C%22Grouping%22%3Anull%2C%22ShapeName%22%3A%22MailListItem%22%2C%22SortOrder%22%3A%5B%7B%22__type%22%3A%22SortResults%3A%23Exchange%22%2C%22Order%22%3A%22Descending%22%2C%22Path%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22ReceivedOrRenewTime%22%7D%7D%2C%7B%22__type%22%3A%22SortResults%3A%23Exchange%22%2C%22Order%22%3A%22Descending%22%2C%22Path%22%3A%7B%22__type%22%3A%22PropertyUri%3A%23Exchange%22%2C%22FieldURI%22%3A%22DateTimeReceived%22%7D%7D%5D%7D%7D'
}
# requests中的post中传入的data可以不进行重编码
# login_postdata = urllib.parse.urlencode(postdata).encode('utf8')
response = requests.post(url=login_url, headers=header,verify=False)  # <class 'requests.models.Response'>
# 以下三种都可以解析结果
json1 = response.json()  # <class 'dict'>
json2 = json.loads(response.text)  # <class 'dict'>
json_str = response.content.decode('utf-8')  # <class 'str'>
pattern="(?<='Subject': ').*?(?=',)"
#正则匹配到所有的主题
#findall需要传入的格式为str格式，需要str（）
all_subject=re.findall(pattern, str(json2))
print("获取到的主题数："+str(len(all_subject)))
RULE_1="【用例评审】"
RULE_2="【用例评审结果】"
RULE_3="【提测】"
RULE_4="【测试报告】"
RULE_5="【上线自查结果】"
RULE=[RULE_1,RULE_2,RULE_3,RULE_4,RULE_5]
out_subject=[]
value=[0,0,0,0,0]
key=''
index=0
for subject in all_subject:
    #针对一个邮件主题进行五个rule的检测，最终生成key为邮件，
    # value为5个数字（0,0,0,0,0）代表五个规则一个也没有匹配上
    for rule in RULE:
        if rule in subject:
            #获取到当前邮件的项目名
            key=subject.split(rule)[0]
            #第几个规则匹配成功就将第几个的value置为1
            value[index]=1
        #每过一个规则就下标+1
        index = index + 1
    #一封邮件匹配完就将下标重新置为0
    index = 0
    #能获取到项目名的就开始转化
    if key!="":
        #必须使用不可变的，需要转为tuple类型
        value2=tuple(value)
        map1=[key,value2]
        #将每一个项目添加到最终需要添加到Excel表格中
        out_subject.append(map1)
    #重新归为初始值
    value=[0,0,0,0,0]
    key=''


print("开始构建Excel表格")
myxls = xlwt.Workbook()
sheet1 = myxls.add_sheet(u'sheet1', cell_overwrite_ok=True)
#先初始化Excel表格的表头的内容
sheet1.write(0, 0, "编号")
sheet1.write(0, 1, "项目")
sheet1.write(0, 2, "需求")
sheet1.write(0, 3, "用例评审邮件")
sheet1.write(0, 4, "用例评审结果邮件")
sheet1.write(0, 5, "提测邮件")
sheet1.write(0, 6, "测试报告邮件")
sheet1.write(0, 7, "上线自查邮件")
sheet1.write(0, 8, "负责人")
sheet1.write(0, 9, "流程规范打分")
sheet1.write(0, 10, "总分")

#将所有的搜集好的邮件信息填入到Excel表格中
#遍历out_subject
for i in range(0, len(out_subject)):
    email=out_subject[i]
    print((email))
    project=email[0]
    email_list=list(email[1])
    # print(type(email_list))
    sheet1.write(i + 1, 0, i + 1)
    sheet1.write(i + 1, 1, project)
    sheet1.write(i + 1, 3,email_list[0])
    sheet1.write(i + 1, 4,email_list[1])
    sheet1.write(i + 1, 5,email_list[2])
    sheet1.write(i + 1, 6,email_list[3])
    sheet1.write(i + 1, 7,email_list[4])
file_name=datetime.date.today()
print(file_name)
myxls.save('E:\\python_file\\web_spider\\'+str(file_name)+'.xls')
print("结果统计完成！！！")

最终的效果：