python 读写excel大文件 python3读写excel

我们通过这个实例的方法来操作 Excel 文件，具体有以下方法：

add_worksheet([sheetname]) 方法
用于创建一个新的工作表
sheet_shee1 = workbook.add_worksheet() # 工作表名: Shee1
sheet_qf01 = workbook.add_worksheet('qf01') # 工作表名: qf01
sheet_shee3 = workbook.add_worksheet() # 工作表名: Shee3

它会返回一个表的对象，可以通过这个对象对表里的内容和样式来进行具体的操作。

add_format([properties]) 方法

用于创建一个格式化对象，使用这个对象可以对任意一个单元格进行格式化

properties 是一个字典类型的参数，里边定义具体的格式

字体加粗格式
bold = workbook.add_format({'bold': True})
日期格式
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd'})
示例演练
假设有如下数据主机 IP 信息，需要转化成 Excel 文件来展示
importxlsxwriter
host_ip=(
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)
示例代码
importxlsxwriter
host_ip=(
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)#创建一个新的文件
with xlsxwriter.Workbook('host_ip.xlsx') as workbook:#添加一个工作表
worksheet = workbook.add_worksheet('ip信息')#设置一个加粗的格式
bold = workbook.add_format({"bold": True})#设置一个日期的格式
date_format =workbook.add_format(
{'num_format': 'yyyy-mm-dd'})#分别设置一下 A 和 B 列的宽度
worksheet.set_column('A:A', 10)
worksheet.set_column('B:B', 15)
worksheet.set_column('C:C', 18)#先把表格的抬头写上，并设置字体加粗
worksheet.write('A1', '主机名', bold)
worksheet.write('B1', 'IP 地址', bold)
worksheet.write(0,2, '统计日期', bold)#设置数据写入文件的初始行和列的索引位置
row = 1col=0#迭代数据并逐行写入文件
for name, ip,date in(host_ip):
worksheet.write(row, col, name)
worksheet.write(row, col+ 1, ip)
worksheet.write(row, col+ 2, date, date_format)
row+= 1

示例文档的样子

openpyxl 读写

安装

pip install openpyxl
基本使用
在内存中创建一个新文档
>>> from openpyxl importWorkbook>>> wb = Workbook()

一个新文档中必须至少有一个工作表

创建一个新工作表

>>> ws =wb.active#或者
>>> ws1 = wb.create_sheet() #默认在结尾创建一个工作表
>>> ws2 = wb.create_sheet("date1", 0) #指定在开头创建一个工作表

在创建表格时，假如不指定名称，则表格会自动命名。(Sheet1,Sheet2)

工作表的名称可以更改
ws.title = 'New Title'
可以在一个文件中对某一个工作表进行复制操作
qf_copy =wb.copy_worksheet(ws2)
qf_copy.title= 'date1_copy'

注意:

只有单元格（包括值，样式，超链接和注释）和某些工作表属性（包括尺寸，格式和属性）被复制。所有其他工作簿/工作表属性不会被复制 - 例如图像，图表。

查看当前文件中所有的工作表对象

print(wb.worksheets)#输出结果:
[, , , ]
当然也可以循环这些工作表名称
for sheet inwb:print(sheet.title)#输出结果
date1
New Title
Sheet1
访问单元格
当工作表在内存中创建时，它不包含单元格。它们在第一次访问时创建。
单元格可以作为工作表的键直接访问
#存在则获取值，不存在则创建
>>> c= ws['A4']#赋值操作，不存在则直接创建
>>> ws['A4'] = 4
这使用行和列表示法提供对单元格的访问权限：
>>> d = ws.cell(row=4, column=2, value=10)
警告
由于这个特性，即使没有为它们赋值，通过滚动单元格而不是直接访问它们也会在内存中创建它们。就像是
>>> for i in range(1,101):for j in range(1,101):
ws.cell(row=i, column=j)
将在内存中创建100x100的单元格，无需任何操作。
单元格的范围操作
同样支持 A1 表示法的切片操作
>>> cell_range = ws['A1':'C2']
还可以用以下方法获取
ws[1:4] #获取到第一列的第一行到第四行
循环单元格以行为单位返回
for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):print(row) #每一行
for cell inrow:print(cell) #每一行中的每一列
循环单元格以列为单位返回
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):for cell incol:print(cell)
给单元格赋值
从以上知识点中我们得到了具体的单元格对象，此时我们就可对他们进行赋值的操作了
#可以这样赋值
ws.cell(row=1, column=2, value='sharkyun') #对第一行的第二列进行赋值
#还可以这样
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):for cell incol:
cell.value= 'sharkyun'
获取单元格的值
#方式一 指定获取第 4 行的第 3 列
cell_val = ws.cell(row=4,column=3).valueprint(cell_val)#方式二 循环得到每个单元格的值
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):for cell incol:print(cell.value)
设置单元格的字体样式
from openpyxl.styles importFontfrom openpyxl.styles importcolors#设置字体样式
ft = Font(bold=True, #加粗
size=20, #大小
color=colors.RED, #颜色
#color='FFBB00' # 颜色
)#使用字体样式到单元格
ws["A1"].font =ft
ws["B1"].font =ft
ws["C1"].font = ft
设置列宽
ws.column_dimensions['A'].width = 18
迭代所有的行和列
迭代所有的行
from openpyxl importWorkbook
wb=Workbook()
ws=wb.active#假如循环的是新创建的工作表， 那么初始时工作表中是没有单元格的，#所以需要先在工作表中创建出一些单元格
ws['C4'] = "Hello"
#ws.rows 是一个生成器对象，可以迭代它，每次迭代会返回工作表中的一行
for row inws.rows:print(row)#以下是输出结果
(, , )
(, , )
(, , )
(, , )
迭代所有的列
此示例的数据对象，沿用了上面的示例
for col inws.columns:print(col)#以下是输出结果
(, , , )
(, , , )
(, , , )
保存数据
要把内存的数据保存到硬盘中，使用 wb.save() 方法即可
from openpyxl importWorkbook
wb=Workbook()
ws=wb.active
ws["A1"] = "hello"wb.save("one.xlsx")#注意文件的后缀名 *.xlsx
示例演练
假设有如下数据主机 IP 信息，需要转化成 Excel 文件来展示
importxlsxwriter
host_ip=(
["server1",'192.168.1.101','2018-06-11'],
["server2",'192.168.1.102','2018-06-11'],
["server3",'192.168.1.103','2018-06-11'],
["server4",'192.168.1.104','2018-06-11']
)
aa
from openpyxl importWorkbookfrom openpyxl.styles importFontfrom openpyxl.styles importcolors#定制一个字体样式对象
ft = Font(bold=True, size=20,
color=colors.RED,#color='FFBB00'
)
host_ip=(
["server1", '192.168.1.101', '2018-06-11'],
["server2", '192.168.1.102', '2018-06-11'],
["server3", '192.168.1.103', '2018-06-11'],
["server4", '192.168.1.104', '2018-06-11']
)#创建 Excel 文件对象
wb =Workbook()
ws1=wb.active#更改工作表标签的背景色,值是RRGGBB颜色代码#http://www.sioe.cn/yingyong/yanse-rgb-16/
ws1.sheet_properties.tabColor = "DC143C"
#设置标题的内容和字体样式
ws1.cell(row=1,column=1,value="主机名").font =ft
ws1.cell(row=1,column=2,value="IP 地址").font =ft
ws1.cell(row=1,column=3,value="统计时间").font =ft#设置列宽
ws1.column_dimensions['A'].width = 16ws1.column_dimensions['B'].width = 22ws1.column_dimensions['C'].width = 22
#获取到所有的行以及每行的所有列
rows = ws1.iter_rows(min_row=2, max_col=len(host_ip[0]),max_row=len(host_ip))for row,items inzip(rows, host_ip):for cell, item inzip(row, items):
cell.value=item
cell.font= Font(size=18)#print(cell.value, item)
#定义文件名
dest_filename = 'empty_book.xlsx'
#保存文件到硬盘
wb.save(filename = dest_filename)
读取一个已存在的 Excel 文件
from openpyxl importload_workbook#获取文件对象
wb2 = load_workbook('empty_book.xlsx')#查看文件中所有的工作表名
wb2.get_sheet_names()#通过工作表名获取到工作表对象
ws = wb2.get_sheet_by_name('Sheet')#同样可以安装上面提到的方法访问这个工作表中的行和列#比如循环每一行
for row inws.rows:print(row)