封装一个工具类
#参考博客:https://www.jb51.net/article/237275.htm
#pip install xlwt=1.3.0
import xlwt
import copy
# 辅助函数:获取每列所占用的最大列宽
def get_max_col(max_list):
line_list = []
# i表示行,j代表列
for j in range(len(max_list[0])):
line_num = []
for i in range(len(max_list)):
line_num.append(max_list[i][j]) # 将每列的宽度存入line_num
line_list.append(max(line_num)) # 将每列最大宽度存入line_list
return line_list
#函数原型:void writeToExcel(...)
#函数功能:写excel表格!
#形参excelFilePath:String,*.xls文件的路径!例如:"C:/A/1.xls"
#形参sheetName:String。Sheet表的标名。
#形参data:要写入的数据,格式:[[列名1,列名2],[值1,值2],[值1,值2]]
def writeToExcel(excelFilePath,sheetName,data):
row_num = 0 # 记录写入行数
col_list = [] # 记录每行宽度
# 创建一个Workbook对象
book = xlwt.Workbook(encoding="utf-8", style_compression=0)
# 创建一个sheet对象
sheet = book.add_sheet(sheetName, cell_overwrite_ok=True)
col_num = [0 for x in range(0, len(data))]
# 写入数据
for i in range(0, len(data)):
for j in range(0, len(data[i])):
sheet.write(row_num, j, data[i][j])
col_num[j] = len(data[i][j].encode('gb18030')) # 计算每列值的大小
col_list.append(copy.copy(col_num)) # 记录一行每列写入的长度
row_num += 1
# 获取每列最大宽度
col_max_num = get_max_col(col_list)
# 设置自适应列宽
for i in range(0, len(col_max_num)):
# 256*字符数得到excel列宽,为了不显得特别紧凑添加两个字符宽度
sheet.col(i).width = 256 * (col_max_num[i] + 2)
# 保存excel文件
book.save(excelFilePath)
#测试
#要准备的写入数据
data = \
[
['姓名', '性别', '年龄', '手机号', '固定电话', '邮箱'],
['厘清', '女', '31', '18745214693', '0104784125', '5412546qq.com'],
['张三', '男', '26', '18245554693', '010-4784125', '无'],
['王武', '男', '19', '13245266693', '无', '785992546qq.com'],
['熊大', '男', '16', '无', '010-4784125', '115412546qq.com'],
['熊二', '男', '22', '18745214693', '010-4784125', '3654126qq.com']
]
writeToExcel("C:/A/haha.xls","test",data)
========================================
#案例:web自动化的一个案例!字节跳动!写到*.xls文件中!
from selenium import webdriver # 导入webdriver包
import time
from utils.exportExcelUtil import writeToExcel
driver=webdriver.Chrome()
driver.maximize_window() # 最大化浏览器
driver.implicitly_wait(30)
driver.get("https://baike.baidu.com/item/%E5%AD%97%E8%8A%82%E8%B7%B3%E5%8A%A8%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8/19921953?fr=aladdin")
#
# driver.find_element_by_id("kw").send_keys("百度百科字节跳动")
# driver.find_element_by_id("su").click()
# driver.find_element_by_css_selector("[data-showurl-highlight='false']").click()
# driver.switch_to.window(driver.window_handles[-1])
#解析发展历程
ele_div=driver.find_element_by_css_selector("div.main-content")
eles_div=ele_div.find_elements_by_css_selector("div[class='para'][data-pid]")
data=[["时间","事件"]]
for div in eles_div:
attr_data_pid=div.get_attribute("data-pid")
t1=int(attr_data_pid)
if t1>=5 and t1<=30:
text=div.text
t2=text.split(",",1)
time=t2[0]
sijian=t2[1]
t3=[time,sijian]
data.append(t3)
# print(data)
#通过工具类把data写入到Excel表格中
writeToExcel("C:/A/byte.xls","test",data)
time.sleep(5)
driver.quit() # 关闭并退出浏览器