python爬取数据保存到Excel中
1 # -*- conding:utf-8 -*- 2 3 # 1.两页的内容 4 # 2.抓取每页title和URL 5 # 3.根据title创建文件，发送URL请求，提取数据 6 import requests 7 from lxml import etree 8 import time, random, xlwt 9 10 11 # 专家委员会成员的xpath（‘//tbody//tr[@height='29']’） 12 13 class Doc_spider(object): 14 15 def __init__(self): 16 self.base_url = 'http://www.bjmda.com' 17 self.url = 'http://www.bjmda.com/Aboutus/ShowClass.asp?ClassID=12&page={}' 18 self.headers = { 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 def get_request(self, url): 22 '''发送请求，返回html''' 23 response = requests.get(url, headers=self.headers).content.decode('gbk') 24 # time.sleep(random.random()) 25 html = etree.HTML(response) 26 return html 27 28 def parse_page_html(self, html, url): 29 '''提取列表页的专家委员会title和URL''' 30 31 url_lists = html.xpath('//tr/td[2]/a[2]/@href')[1:] 32 temp_lists = html.xpath('//tr/td[2]/a[2]/text()')[1:] 33 title_lists = [title.rstrip() for title in temp_lists] 34 35 urls = [] 36 titles = [] 37 38 for i in range(len(title_lists)): 39 url = self.base_url + url_lists[i] 40 title = title_lists[i] 41 urls.append(url) 42 titles.append(title) 43 44 return urls, titles 45 46 def parse_detail(self, html): 47 '''详细页的提取数据,返回每组列表信息''' 48 49 lists = html.xpath("//td[@id='fontzoom']//tr") 50 content_list = [] 51 for list in lists: 52 contents = list.xpath('.//td//text()') 53 new = [] 54 for i in contents: 55 new.append(''.join(i.split())) 56 content_list.append(new) 57 58 return content_list 59 60 def save_excel(self, sheet_name, contents, worksheet, workbook): 61 '''保存数据到Excel''' 62 63 # 创建一个workbook 设置编码 64 #workbook = xlwt.Workbook() 65 # 创建一个worksheet 66 #worksheet = workbook.add_sheet(sheet_name) 67 68 try: 69 70 for i in range(len(contents)): 71 if len(contents[i+1])>1: 72 content_list = contents[i + 1] 73 74 # 写入excel 75 # 参数对应 行, 列, 值 76 worksheet.write(i, 0, label=content_list[0]) 77 worksheet.write(i, 1, label=content_list[1]) 78 worksheet.write(i, 2, label=content_list[2]) 79 if len(contents[i+1])>3: 80 worksheet.write(i, 3, label=content_list[3]) 81 82 # 保存 83 #workbook.save(sheet_name + '.xls') 84 # time.sleep(0.1) 85 except: 86 print(sheet_name,'保存OK') 87 88 pass 89 90 def run(self): 91 # 1.发送专家委员会列表页请求 92 urls = [self.url.format(i + 1) for i in range(2)] 93 94 # 创建一个workbook 设置编码 95 workbook = xlwt.Workbook() 96 97 for url in urls: 98 html = self.get_request(url) 99 # 2.提取委员会的title和URL 100 list_urls, titles = self.parse_page_html(html, url) 101 102 for i in range(len(list_urls)): 103 url_detail = list_urls[i] 104 # 每个委员会的名称 105 title_detail = titles[i] 106 # 3.创建每个委员会文件，发送每个委员会的请求 107 html_detail = self.get_request(url_detail) 108 # 4.提取专家委员会详细页的内容 109 contents = self.parse_detail(html_detail) 110 # 保存每个委员会的所有人 111 112 # 创建一个worksheet 113 worksheet = workbook.add_sheet(title_detail) 114 self.save_excel(title_detail, contents,worksheet,workbook) 115 workbook.save('专家委员会.xls') 116 print('保存结束，请查看') 117 118 119 120 if __name__ == '__main__': 121 doc = Doc_spider() 122 doc.run()
这个小程序可以爬取该网站的医生专家的信息，分不同的专科保存到同一个Excel中。
# -*- conding:utf-8 -*- import xlwt # 创建工作workbook workbook = xlwt.Workbook() # 创建工作表worksheet,填入表名 worksheet = workbook.add_sheet('表名') # 在表中写入相应的数据 worksheet.write(0, 0, 'hello world') worksheet.write(1, 1, '你好') # 保存表 workbook.save('hello.xls')
