1.单/多线程普通模板 + 写入excel

import requests requests.packages.urllib3.disable_warnings() from concurrent.futures import ThreadPoolExecutor, as_completed from threading import Lock

# save_to_excel 是个独立文件 from save_to_excel import OperateExcel

class Test(object): def __init__(self): self.operexcel = OperateExcel() self.lock1 = Lock() self.lock2 = Lock() self.lock3 = Lock()

def get_ip(self): pass

def parse(self,url,ip): n = 0 while True: proxy = { 'http': 'http://' + ip, 'https': 'https://' + ip } head = { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.81 Safari/537.36', } try: resp = requests.get(url, headers=head,proxies=proxy,verify=False,timeout=5) if n > 20: return '',ip elif '请求失败' in resp.text: n += 1 ip = self.get_ip() else: ip = self.get_ip() except requests.exceptions.RequestException as e: n += 1 ip = self.get_ip() if n > 50: return '',ip

def get_list(self,city,city_st,file_path,threadpool): loc_list = [] ip = self.get_ip()

# 初始化excel wbook, wsheet = self.operexcel.init_excel(file_path) row_num = 2 # 行数

# 例如将某个网站的页数作为多线程来抓取 future_list = [] for p in range(1, 100): page_url = 'http://www.666.cpm/page='.format(p) future = threadpool.submit(self.handle_true,page_url) future_list.append(future) for future in as_completed(future_list): data_list_fin = future.result() # 线程运行结果 if data_list_fin: self.lock2.acquire() row_max_num = self.operexcel.write_excel(wbook, wsheet, file_path, row_num,data_list_fin) self.lock2.release() row_num = row_max_num + 1

def handle_true(self,page_url): data_list = [] ip = self.get_ip()

# 中间步骤掠过

# 这里返回的列表一定是[[],[],[],[],…]这样的格式 return data_list

if __name__ == '__main__': test = Test() threadpool = ThreadPoolExecutor(max_workers=10)

outpath = '…' outfile = outpath + '\' + '{}.xlsx'.format('给文件取的名字') fp = open(outfile,'w') fp.close() file_path = outfile test.get_list(file_path,threadpool) print('sucessful')

2.save_to_excel代码如下：

import openpyxl

class OperateExcel(object): # 初始化excel def init_excel(self, filename): workbook = openpyxl.Workbook()

# 创建新表 # worksheet = workbook.create_sheet(sheetname)

# 载入默认表 worksheet = workbook['Sheet']

# 修改表名 worksheet.title = 'Data'

# 指定打开excel后显示哪个表 # workbook.active = 0 # 设置active参数，即工作表索引值

# 表头 datas = ['Name', 'Price','','','', '', '', '','oneyearinfo','','latitude','longtitude','houserentinfo']

for i in range(1, len(datas)+1): worksheet.cell(row=1, column=i).value = datas[i – 1] workbook.save(filename) # workbook.close() return workbook, worksheet

# 向excel中写入数据 def write_excel(self, workbook, worksheet, filename, row_number, data_list): # print(data_list) for data in data_list: for i in range(1, len(data)+1): worksheet.cell(row=row_number, column=i).value = data[i–1] row_number += 1 workbook.save(filename) return worksheet.max_row # workbook.close()

if __name__ == '__main__': ox = OperateExcel() filename = 'text.xlsx' wb, ws = ox.init_excel(filename)

3.另一种写入excel方式：

def writeToExcel(self,file_path, new_list): # total_list = [['A', 'B', 'C', 'D', 'E'], [1, 2, 4, 6, 8], [4, 6, 7, 9, 0], [2, 6, 4, 5, 8]] wb = openpyxl.Workbook() ws = wb.active ws.title = 'Data' tit_list = ['','','','','','', '','',''] for tit in range(len(tit_list)): ws.cell(1, tit + 1).value = tit_list[tit] for r in range(len(new_list)): for c in range(len(new_list[0])): try: ws.cell(r + 2, c + 1).value = new_list[r][c] except: pass # excel中的行和列是从1开始计数的，所以需要+1 wb.save(file_path) # 注意，写入后一定要保存 print("成功写入文件: " + file_path + " !")