[Python知识库]完整python爬取网页数据写入excel保存xlsx格式

完整代码：

import json
import requests
from lxml import etree
import xlwings as xw
import re
#爬取网址
url="https://shenzhen.baixing.com/jiudianfuwu/m35936-m5238/"
#模拟浏览器访问
headers={'user-agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/\
537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36','referer':url}
#获取正文
response=requests.get(url,headers=headers)
body=response.text
# print(body)
#定位内容
html=etree.HTML(body,etree.HTMLParser())
gethtml=html.xpath('//div[contains(@class,"media-body-title")]')
print(gethtml)
pass
#定义存放数组
i=0
jsondata = []

for item in gethtml:

    try:
        title = item.xpath('//a[contains(@class,"ad-title")]/text()')[i]
        url=item.xpath('//a[contains(@class,"ad-title")]/attribute::href')[i]
        # telphone=item.xpath('//button[contains(@class,"contact-button")]/attribute::data-contact')[i]
        telphone = item.xpath('//button[contains(@class,"contact-button")]/attribute::data-qq')[i]
        import re

        # str = '<span class="t4">1万 - 1.6万</span>'
        # searchObj = re.search(r'<span class="t4">(.*?)</span>', str, re.M | re.I)
        # salary = searchObj.group(1)

        print(i,title ,url,telphone,sep="隔断")

        #判断是否为空很重要
        jsondata.append(
            {
            'title':title if title else'',
            'url':url if url else '',
            'telphone': telphone if telphone else '',
            })
        i = i + 1
    except IndexError:#异常跳出，实际异常有数据，也没搞明白为啥报错
        print(i,title,url,telphone,sep=",")
        print('第{0}条数据处理失败'.format(i))
    continue


    # jsonone={}
    # jsonone["title"]=item.xpath('//a[contains(@class,"ad-title")]/text()')[i]
    # jsonone["url"]=item.xpath('//a[contains(@class,"ad-title")]/attribute::href')[i]
    # jsonone['telphone']=item.xpath('//button[contains(@class,"contact-button")]/attribute::data-contact')[2]
    # jsondata.append(jsonone)
    # print(jsondata["url"])
# i=i+1
#写入json
with open(".d.json",'w',encoding='utf-8') as f:
    json.dump(jsondata,f,indent=1,ensure_ascii=False)

    #读取json
with open(".d.json",'r',encoding='utf-8') as f:
    data=json.load(f)


#创建workbook
app=xw.App(visible=False,add_book=False)
new_workbook=xw.Book()
new_worksheet=new_workbook.sheets.add('test')
app.display_alerts=False
app.screen_updating=False
#以此写入数据
for i in range(len(data)):
    title=data[i]["title"]
    url=data[i]["url"]
    telphone=data[i]["telphone"]

    new_worksheet.cells[i,0].value=title
    new_worksheet.cells[i,1].value=url
    new_worksheet.cells[i,2].value=telphone
    i=i+1
    # print(i,title ,url,telphone,sep="隔断")

#保存文件
new_worksheet.autofit()
new_workbook.save('test3.xlsx')
new_workbook.close()
app.quit()

注意获取网页内容，要进行是否为空判断，否则写入json会报错，内容如下：

#定义存放数组
i=0
jsondata=[]
for item in gethtml:
    title = item.xpath('//a[contains(@class,"ad-title")]/text()')[i]
    url = item.xpath('//a[contains(@class,"ad-title")]/attribute::href')[i]
    telphone=item.xpath('//button[contains(@class,"contact-button")]/attribute::data-contact')[i]
    # print(telphone)
    #判断是否为空很重要
    jsondata.append(
        {
        'title':title if title else'',
        'url':url if url else '',
        'telphone': telphone if telphone else '',
        })

    # jsonone={}
    # jsonone["title"]=item.xpath('//a[contains(@class,"ad-title")]/text()')[i]
    # jsonone["url"]=item.xpath('//a[contains(@class,"ad-title")]/attribute::href')[i]
    # jsonone['telphone']=item.xpath('//button[contains(@class,"contact-button")]/attribute::data-contact')[2]
    # jsondata.append(jsonone)
    # print(jsondata["url"])
    i=i+1

其实这里有个数据异常，也没搞懂：

实际是有问题的，但里面的确是有数据，也没搞懂

for item in gethtml:

    try:
        title = item.xpath('//a[contains(@class,"ad-title")]/text()')[i]
        url=item.xpath('//a[contains(@class,"ad-title")]/attribute::href')[i]
        # telphone=item.xpath('//button[contains(@class,"contact-button")]/attribute::data-contact')[i]
        telphone = item.xpath('//button[contains(@class,"contact-button")]/attribute::data-qq')[i]
        import re

        # str = '<span class="t4">1万 - 1.6万</span>'
        # searchObj = re.search(r'<span class="t4">(.*?)</span>', str, re.M | re.I)
        # salary = searchObj.group(1)

        print(i,title ,url,telphone,sep="隔断")

        #判断是否为空很重要
        jsondata.append(
            {
            'title':title if title else'',
            'url':url if url else '',
            'telphone': telphone if telphone else '',
            })
        i = i + 1
    except IndexError:#异常跳出，实际异常有数据，也没搞明白为啥报错
        print(i,title,url,telphone,sep=",")
        print('第{0}条数据处理失败'.format(i))
    continue