我想获取知乎热榜的排行榜并保存到excel 可是excel那里出来问题请大神们帮帮忙,Python交流,编程语言专区,鱼C论坛

13714309891 发表于 2020-3-27 22:51:27

我想获取知乎热榜的排行榜并保存到excel 可是excel那里出来问题请大神们帮帮忙

我想获取知乎热榜的排行榜并保存到excel 可是excel那里出来问题请大神们帮帮忙

from lxml import etree
import requests
import openpyxl

def get_url(url):

headers = {"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",
                  'Cookie':'_zap=99431b2c-8767-4685-b98b-cd9d0d3d88cd; _xsrf=5p5XGCN215CjXjnbPuj1OmbQmvR2pQdQ; d_c0="AKDg7kP8dxCPTsNRshHIPT2mH69mOZb8xuE=|1575709991"; z_c0="2|1:0|10:1575709998|4:z_c0|92:Mi4xa2poTkNBQUFBQUFBb09EdVFfeDNFQ1lBQUFCZ0FsVk5MYnZZWGdEZ0xuLWJoVWFzV2ZtYjk4T2M3dHNYa1lRd1dn|ab32d6e8ac6c6c86e72b218ad426d24b26d9adea21fdbd28afe64ada5ac53e77"; _ga=GA1.2.2089582079.1583647145; q_c1=5919a9b1025f437f8105f1a16a9f0d36|1583647148000|1575710038000; _gid=GA1.2.633099727.1584344528; tst=h; tshl=; Hm_lvt_98beee57fd2ef70ccdd5ca52b9740c49=1584357660,1584362899,1584406978,1584411187; Hm_lpvt_98beee57fd2ef70ccdd5ca52b9740c49=1584420694; KLBRSID=53650870f91603bc3193342a80cf198c|1584420984|1584420275'}

ret = requests.get(url, headers=headers)
ret.encoding = 'GBK'
html_str = ret.content.decode()
html = etree.HTML(html_str)
res= html.xpath("//section[@class='HotItem']")

return res

#获取知乎热榜排名、标题、热度
def get_data(res):

for table in res:
   data = {}
   data['排名'] = table.xpath(".//div[@class='HotItem-index']/div/text()")
   data['标题'] = table.xpath(".//div[@class='HotItem-content']/a/h2/text()")
   data['热度'] = table.xpath(".//div[@class='HotItem-metrics HotItem-metrics--bottom']/text()")
   data['地址'] = table.xpath(".//div[@class='HotItem-content']/a/@href")

   return data

def to_excel(data):
wb = openpyxl.Workbook()
wb.guess_types = True
ws = wb.active
ws.append(['排名', '标题', '热度', '地址'])
for each in data:
   ws.append(each)

   wb.save("知乎热榜排行榜.xlsx")

def main():
url = "https://www.zhihu.com/hot"
res = get_url(url)
data = get_data(res)
to_excel(data)

if __name__ == "__main__":
main()

Levin-e 发表于 2020-3-28 15:15:24

出了什么问题

Chysial 发表于 2020-3-28 15:38:24

本帖最后由 Chysial 于 2020-3-28 16:10 编辑

我运行的结果是typeerror,说是ValueError: Cannot convert ['1'] to Excel，应该是爬虫哪个地方要进行字符串转化，由于你的openpyxl和我习惯不一样，我吧后边的代码改了一下
这是我给你修改后的代码
from lxml import etree
import requests
import openpyxl

def get_url(url):

headers = {"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",
                  'Cookie':'_zap=99431b2c-8767-4685-b98b-cd9d0d3d88cd; _xsrf=5p5XGCN215CjXjnbPuj1OmbQmvR2pQdQ; d_c0="AKDg7kP8dxCPTsNRshHIPT2mH69mOZb8xuE=|1575709991"; z_c0="2|1:0|10:1575709998|4:z_c0|92:Mi4xa2poTkNBQUFBQUFBb09EdVFfeDNFQ1lBQUFCZ0FsVk5MYnZZWGdEZ0xuLWJoVWFzV2ZtYjk4T2M3dHNYa1lRd1dn|ab32d6e8ac6c6c86e72b218ad426d24b26d9adea21fdbd28afe64ada5ac53e77"; _ga=GA1.2.2089582079.1583647145; q_c1=5919a9b1025f437f8105f1a16a9f0d36|1583647148000|1575710038000; _gid=GA1.2.633099727.1584344528; tst=h; tshl=; Hm_lvt_98beee57fd2ef70ccdd5ca52b9740c49=1584357660,1584362899,1584406978,1584411187; Hm_lpvt_98beee57fd2ef70ccdd5ca52b9740c49=1584420694; KLBRSID=53650870f91603bc3193342a80cf198c|1584420984|1584420275'}

ret = requests.get(url, headers=headers)
ret.encoding = 'GBK'
html_str = ret.content.decode()
html = etree.HTML(html_str)
res= html.xpath("//section[@class='HotItem']")

return res

#获取知乎热榜排名、标题、热度
def get_data(res):

for table in res:
   data = {}
   data['排名'] = str(table.xpath(".//div[@class='HotItem-index']/div/text()"))
   data['标题'] = str(table.xpath(".//div[@class='HotItem-content']/a/h2/text()"))
   data['热度'] = str(table.xpath(".//div[@class='HotItem-metrics HotItem-metrics--bottom']/text()"))
   data['地址'] = str(table.xpath(".//div[@class='HotItem-content']/a/@href"))

   return data

def to_excel(data):
wb = openpyxl.Workbook()
wb.guess_types = True
ws = wb.active
i = 0
for each in data:
   i += 1
   _=ws.cell(column=1,row=i,value=each)
   _=ws.cell(column=2,row=i,value=data)

wb.save("C:\\Users\\Chysial\\Desktop\\知乎热榜排行榜.xlsx")

url = "https://www.zhihu.com/hot"
res = get_url(url)
data = get_data(res)
to_excel(data)

这是最终excel的结果
排名 ['1']
标题 ['如何看待 3 月 28 日美国新冠肺炎确诊人数突破 10 万？']
热度 ['6332 万热度']
地址 ['https://www.zhihu.com/question/382855006']

13714309891 发表于 2020-3-28 17:50:47

Levin-e 发表于 2020-3-28 15:15
出了什么问题

是data那个类型的问题我想知道要怎么改（刚刚才看到你发的贴子{:5_110:}）

13714309891 发表于 2020-3-28 17:53:15

Chysial 发表于 2020-3-28 15:38
我运行的结果是typeerror,说是ValueError: Cannot convert ['1'] to Excel，应该是爬虫哪个地方要进行字符 ...

谢谢你的帮忙，但是为什么只有一条结果

Chysial 发表于 2020-3-28 20:24:04

13714309891 发表于 2020-3-28 17:53
谢谢你的帮忙，但是为什么只有一条结果

我对requests不是很熟，应该是爬虫的问题，就像find与find_all的，你再仔细看看request，我今天晚上有事，就不翻文档了，你自己找找看看，是不是这个爬虫只爬一条，先打印出来看看看，如果是，改一下，然后就在最后to_excel加一层循环就ok了。

Chysial 发表于 2020-3-28 21:14:04

我给你改完了代码，完全爬出来了50个，应该只是第一页，，本来想先让你写一下的，算了from lxml import etree
import requests
import openpyxl

def get_url(url):

headers = {"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",
                  'Cookie':'_zap=99431b2c-8767-4685-b98b-cd9d0d3d88cd; _xsrf=5p5XGCN215CjXjnbPuj1OmbQmvR2pQdQ; d_c0="AKDg7kP8dxCPTsNRshHIPT2mH69mOZb8xuE=|1575709991"; z_c0="2|1:0|10:1575709998|4:z_c0|92:Mi4xa2poTkNBQUFBQUFBb09EdVFfeDNFQ1lBQUFCZ0FsVk5MYnZZWGdEZ0xuLWJoVWFzV2ZtYjk4T2M3dHNYa1lRd1dn|ab32d6e8ac6c6c86e72b218ad426d24b26d9adea21fdbd28afe64ada5ac53e77"; _ga=GA1.2.2089582079.1583647145; q_c1=5919a9b1025f437f8105f1a16a9f0d36|1583647148000|1575710038000; _gid=GA1.2.633099727.1584344528; tst=h; tshl=; Hm_lvt_98beee57fd2ef70ccdd5ca52b9740c49=1584357660,1584362899,1584406978,1584411187; Hm_lpvt_98beee57fd2ef70ccdd5ca52b9740c49=1584420694; KLBRSID=53650870f91603bc3193342a80cf198c|1584420984|1584420275'}

ret = requests.get(url, headers=headers)
ret.encoding = 'GBK'
html_str = ret.content.decode()
html = etree.HTML(html_str)
res= html.xpath("//section[@class='HotItem']")

return res

#获取知乎热榜排名、标题、热度
def get_data(table):
data = {}
data['排名'] = str(table.xpath(".//div[@class='HotItem-index']/div/text()"))
data['标题'] = str(table.xpath(".//div[@class='HotItem-content']/a/h2/text()"))
data['热度'] = str(table.xpath(".//div[@class='HotItem-metrics HotItem-metrics--bottom']/text()"))
data['地址'] = str(table.xpath(".//div[@class='HotItem-content']/a/@href"))
return data

def to_excel(res):
wb = openpyxl.Workbook()
wb.guess_types = True
ws = wb.active
ws.append(['排名', '标题', '热度', '地址'])
i = 0
for table in res:
   data = get_data(table)
   i +=1
   j = 0
   for each in data:
         j += 1
         _=ws.cell(column=j,row=i+1,value=data)

wb.save("C:\\Users\\Chysial\\Desktop\\知乎热榜排行榜.xlsx")

url = "https://www.zhihu.com/hot"
res = get_url(url)
to_excel(res)

Chysial 发表于 2020-3-28 21:15:54

Chysial 发表于 2020-3-28 21:14
我给你改完了代码，完全爬出来了50个，应该只是第一页，，本来想先让你写一下的，算了

你的错误是哪个字典，因为很多个，形成的不是单一地映射了，出现了这种问题，只有一个，我是这么改的你可以尝试一下用字典改，那么循环不是在to_excel中了，而是在get_table，你试一下吧

Chysial 发表于 2020-3-28 21:21:37

13714309891 发表于 2020-3-28 23:10:13

Chysial 发表于 2020-3-28 21:21

非常感谢！！！

页: [1]

鱼C论坛's Archiver

我想获取知乎热榜的排行榜并保存到excel 可是excel那里出来问题 请大神们帮帮忙

我想获取知乎热榜的排行榜并保存到excel 可是excel那里出来问题请大神们帮帮忙