1 # # /usr/bin/python
2 # #coding:utf-8
3 #http://cuiqingcai.com/968.html
4
5 import urllib
6 import urllib2
7 import cookielib
8 import xlrd,xlwt,os
9 import openpyxl
10 from xlutils.copy import copy
11
12 #声明一个MozillaCookieJar对象实例来保存cookie，之后写入文件
13 cookie = cookielib.MozillaCookieJar()
14 #从文件中读取cookie内容到变量
15 cookie.load("cookies.txt", ignore_discard=True, ignore_expires=True)
16 #利用urllib2库的HTTPCookieProcessor对象来创建cookie处理器
17 handle=urllib2.HTTPCookieProcessor(cookie)
18 #通过handler来构建opener
19 opener = urllib2.build_opener(handle)
20 urllib2.install_opener(opener)
21 #opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cookie))
22
23 # 打开文件
24 file_path = "F:\\studyauto\\api_get\\test.xls"
25 #workbook = xlrd.open_workbook(file_path,formatting_info=True) #formatting_info=True保持原有格式，但xlrd的版本 8.1.1不支持
26 workbook = xlrd.open_workbook(file_path)
27 sheet = workbook.sheet_by_index(0) #第一个sheet
28 #复制上面打开的excel文件
29 new_workbook = copy(workbook)
30 #从复制的excel文件中，得到写入用列的sheet表，这里写入第一个表即为0
31 new_sheet = new_workbook.get_sheet(0)
32 nrows = sheet.nrows
33 #获取各行数据
34 for i in range(1,nrows):
35 base_url = sheet.cell(i, 0).value
36 # print base_url
37 req = urllib2.Request(base_url)
38 res_data = urllib2.urlopen(req)
39 res = res_data.read()
40 #写入内容
41 new_sheet.write(i,1,res)
42 # print res
43 new_workbook.save(file_path)
