python对Excel表格操作

操作场景，给一个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()

秒客网

python对Excel表格操作

相关文章