python3 reqeusts后写入excel

用python通过手机号批量辨别运营商并写入excel

初始文件：

具体代码：

#coding=utf-8
import requests
import re
import xlrd
import xlwt

read_path = 'xxx.xlsx' #你的初始文件
write_path='xxx.xls'  #你的生成文件

# 设置手机号和运营商列表
phonelist = ['手机号']
yyslist = ['运营商']
# 读取excel,
def read_excel(read_path):
    # 打开文件
    filename = xlrd.open_workbook(read_path)
    # 获取当前文档的表(得到的是sheet的个数，一个整数）
    sheets=filename.nsheets

    # 通过sheet索引获得sheet对象
    sheet = filename.sheet_by_index(0)
    # 获取行数
    nrows = sheet.nrows
    print(nrows)
    # 获取列数
    ncols = sheet.ncols
    print(ncols)
    # 获取第2行，第2列数据(调取接口，获取手机号的运营商信息，并存在yyslist)
    cell_value = sheet.cell_value(1, 1)
    print(type(cell_value))
    # print ("https://tcc.taobao.com/cc/json/mobile_tel_segment.htm?tel="+sheet.cell_value(1,0))
    # 获取第2行以后的数据（第一列）

    #获取每个手机号获取信息，并用正则从返回中获取catName
    for i in range(1, nrows):
        ur = "https://tcc.taobao.com/cc/json/mobile_tel_segment.htm?tel=" + str(int(sheet.cell_value(i, 0)))
        print(ur)
        response = requests.get(ur)
        response.enconding = 'utf-8'
        xinxi = response.text
        res = re.findall("catName:'(.*?)',", xinxi, re.M | re.S | re.U)
        print(res)
        yyslist.append(res)
        phonelist.append(int(sheet.cell_value(i, 0)))
    print(yyslist)#检查运营商信息
    print(phonelist)#检查手机号
#写入新的excel
def write_excel(write_path):
    # 创建工作簿
    workbook = xlwt.Workbook(encoding='utf-8')
    # 创建sheet
    data_sheet = workbook.add_sheet('demo')
    # 生成第一列和第二列
    for i in range(len(yyslist)):
        data_sheet.write(i, 0, phonelist[i])
        data_sheet.write(i, 1, yyslist[i])

    # 保存文件
    # workbook.save('demo.xls')
    workbook.save(write_path)
    # book2 = copy(filename)
    # sheet = book2.get_sheet(0)
    # for i in range(1,9):
    #     sheet.write(i, 1, yyslist[i-1])
    #
    # book2.save('D:saaa.xlsx')
read_excel(read_path)
write_excel(write_path)

转载请备注“文章转载来自博客园-输是谁”