操作场景,给一个Excel表格随机生成10万个手机号码
python中常见的对Excel操作模块
xlwt module 将数据写入Excel表
xlrd module 读取Excel表格
xlsxwriter modole xlwt模块的升级版,用于处理大量数据
先用xlwt写入Excel操作
import xlwt # 将数据写入Excel
import random book = xlwt.Workbook(encoding='utf-8',style_compression=) # 创建workbook对象,设置编码格式
worksheet = book.add_sheet('test',cell_overwrite_ok=True) # 创建一个sheet对象,cell_overwrite_ok表示可以覆盖单元格
worksheet.write(,,'phone') # 在0,0位置写入phone,前面0代表列,后面0代表行 # 手机开头号码
prelist = ["","","","","","","","","","","","","","","","","","","","","","",""]
nums =
for i in range():
# 组合成一个11位手机号码
phones = random.choice(prelist)+"".join(random.choice("") for i in range())
nums +=
worksheet.write(nums,,phones) book.save(r'D:\python\test.xls')
执行后发现报错:ValueError: row index was 65536, not allowed by .xls format
原因是xlwt修改xls数据最多只能修改到65536行,所以只能使用新的模块xlsxwriter模块修改的文件是xlsx
用xlsxwriter模块修改Excel文件
import xlwt # 将数据写入Excel
import xlrd # 读取Excel文档
from xlutils.copy import copy
import xlsxwriter
import random workbook = xlsxwriter.Workbook(r'D:\python\test.xlsx') # 创建Excel文件
worksheet = workbook.add_worksheet() # 创建一个sheet,不加名字默认sheet
worksheet.write(,,"phone")
# 手机开头号码
prelist = ["","","","","","","","","","","","","","","","","","","","","","",""]
nums =
for i in range():
# 组合成一个11位手机号码
phones = random.choice(prelist)+"".join(random.choice("") for i in range())
nums +=
worksheet.write(nums,,phones) workbook.close()