python得到与处理结果怎么保存到excelpython爬取数据保存到Excel中

1 #-*- conding:utf-8 -*-

2

3 #1.两页的内容

4 #2.抓取每页title和URL

5 #3.根据title创建文件&＃xff0c;发送URL请求&＃xff0c;提取数据

6 importrequests7 from lxml importetree8 importtime, random, xlwt9

10

11 #专家委员会成员的xpath&＃xff08;"//tbody//tr[&＃64;height&＃61;"29"]’&＃xff09;

12

13 classDoc_spider(object):14

15 def __init__(self):16 self.base_url &＃61; "http://www.bjmda.com"

17 self.url &＃61; "http://www.bjmda.com/Aboutus/ShowClass.asp?ClassID&＃61;12&page&＃61;{}"

18 self.headers &＃61;{19 "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36"}20

21 defget_request(self, url):22 """发送请求&＃xff0c;返回html"""

23 response &＃61; requests.get(url, headers&＃61;self.headers).content.decode("gbk")24 #time.sleep(random.random())

25 html &＃61;etree.HTML(response)26 returnhtml27

28 defparse_page_html(self, html, url):29 """提取列表页的专家委员会title和URL"""

30

31 url_lists &＃61; html.xpath("//tr/td[2]/a[2]/&＃64;href")[1:]32 temp_lists &＃61; html.xpath("//tr/td[2]/a[2]/text()")[1:]33 title_lists &＃61; [title.rstrip() for title intemp_lists]34

35 urls &＃61;[]36 titles &＃61;[]37

38 for i inrange(len(title_lists)):39 url &＃61; self.base_url &＃43;url_lists[i]40 title &＃61;title_lists[i]41 urls.append(url)42 titles.append(title)43

44 returnurls, titles45

46 defparse_detail(self, html):47 """详细页的提取数据,返回每组列表信息"""

48

49 lists &＃61; html.xpath("//td[&＃64;id&＃61;"fontzoom"]//tr")50 content_list &＃61;[]51 for list inlists:52 contents &＃61; list.xpath(".//td//text()")53 new &＃61;[]54 for i incontents:55 new.append("".join(i.split()))56 content_list.append(new)57

58 returncontent_list59

60 defsave_excel(self, sheet_name, contents, worksheet, workbook):61 """保存数据到Excel"""

62

63 #创建一个workbook 设置编码

64 #workbook &＃61; xlwt.Workbook()

65 #创建一个worksheet

66 #worksheet &＃61; workbook.add_sheet(sheet_name)

67

68 try:69

70 for i inrange(len(contents)):71 if len(contents[i&＃43;1])>1:72 content_list &＃61; contents[i &＃43; 1]73

74 #写入excel

75 #参数对应 行, 列, 值

76 worksheet.write(i, 0, label&＃61;content_list[0])77 worksheet.write(i, 1, label&＃61;content_list[1])78 worksheet.write(i, 2, label&＃61;content_list[2])79 if len(contents[i&＃43;1])>3:80 worksheet.write(i, 3, label&＃61;content_list[3])81

82 #保存

83 #workbook.save(sheet_name &＃43; ".xls")

84 #time.sleep(0.1)

85 except:86 print(sheet_name,"保存OK")87

88 pass

89

90 defrun(self):91 #1.发送专家委员会列表页请求

92 urls &＃61; [self.url.format(i &＃43; 1) for i in range(2)]93

94 #创建一个workbook 设置编码

95 workbook &＃61;xlwt.Workbook()96

97 for url inurls:98 html &＃61;self.get_request(url)99 #2.提取委员会的title和URL

100 list_urls, titles &＃61;self.parse_page_html(html, url)101

102 for i inrange(len(list_urls)):103 url_detail &＃61;list_urls[i]104 #每个委员会的名称

105 title_detail &＃61;titles[i]106 #3.创建每个委员会文件&＃xff0c;发送每个委员会的请求

107 html_detail &＃61;self.get_request(url_detail)108 #4.提取专家委员会详细页的内容

109 contents &＃61;self.parse_detail(html_detail)110 #保存每个委员会的所有人

111

112 #创建一个worksheet

113 worksheet &＃61;workbook.add_sheet(title_detail)114 self.save_excel(title_detail, contents,worksheet,workbook)115 workbook.save("专家委员会.xls")116 print("保存结束&＃xff0c;请查看")117

118

119

120 if __name__ &＃61;&＃61; "__main__":121 doc &＃61;Doc_spider()122 doc.run()