XlsxWriter 只写
基本介绍
XlsxWriter 是在 Python 下操作 EXCEL 文档的利器
100% 支持 Excel XLSX 文件， 支持 Excel 2003、Excel 2007 等版本
支持所有 Excel 单元格的数据格式
包括：
- 合并单元格、批注、自动筛选、丰富多格式字符串等
- 支持工作表PNG / JPEG / BMP / WMF / EMF图像。
- 用于写入大文件的内存优化模式。
安装
pip3 install XlsxWriter
基本使用示例
import xlsxwriter
# 创建一个新的 Excel 文件，并添加一个工作表
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
# 设置第一列(A) 单元格宽度为 20
worksheet.set_column('A:A', 20)
# 定义一个加粗的格式对象
bold = workbook.add_format({'bold': True})
# 在 A1 单元格处写入字符串 'Hello'
worksheet.write('A1', 'Hello')
# 在 A2 单元格处写入中文字符串，并加粗字体
worksheet.write('A2', '千锋教育', bold)
# 利用 行和列的索引号方式，写入数字，索引号是从 0 开始的
worksheet.write(2, 0, 100) # 3 行 1列
worksheet.write(3, 0, 35.8)
# 计算 A3 到 A4 的结果
worksheet.write(4, 0, '=SUM(A3:A4)')
# 在 B5 单元格处插入一个图片
worksheet.insert_image('B5', 'logo.png')
# 关闭 Excel 文件
workbook.close()
完成图
方法解析
行和列的表示方法
XlsxWriter支持两种表示法来指定单元格的位置： 行列符和A1符号。
行列符号 表示法是 对行和列都使用基于零的索引号表示。
而A1符号表示法是 使用标准Excel中的字母表示列，基于1的数字表示行。例如：
# 下面两个都表示为 第一行的第一列
(0, 0)
('A1')
# 下面两个都表示为 第七行的第三列
(6, 2)
('C7')
如果您以编程方式引用单元格，则行列符号表示法很有用：
for row in range(0, 5):
worksheet.write(row, 0, 'Hello')
如何表示行列范围
XlsxWriter支持Excel的工作表限制1,048,576行16,384列。
# 用 A1 符号表示法
worksheet.print_area('A1:XFD1') # Same as 1:1
worksheet.print_area('A1:A1048576') # Same as A:A
这些范围也可以使用行列符号指定：
worksheet.print_area(0, 0, 0, 16383) # Same as 1:1
worksheet.print_area(0, 0, 1048575, 0) # Same as A:A
Workbook 类
用于创建一个新的 Excel 文件
workbook = xlsxwriter.Workbook('demo.xlsx')
注意： 和文件的打开模式 w 一样，会把原来的内容清空。所以只能新建文件。
他会返回一个 Workbook 的实例对象，代表了整个 Excel 文件，并且会把这个文件存储在磁盘上。
最后要记得关闭这个文件
workbook.close()
当然建议使用 with 管理上下文，这样就可以不用显示的调用 workboo.close()
with xlsxwriter.Workbook('hello_world.xlsx') as workbook:
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello world')
我们通过这个实例的方法来操作 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 文件来展示
import xlsxwriter
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']
)
示例代码
import xlsxwriter
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 = 1
col = 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 读写
安装
pip3 install openpyxl
基本使用
在内存中创建一个新文档
>>> from openpyxl import Workbook
>>> 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)
# 输出结果:
[<Worksheet "date1">, <Worksheet "New Title">, <Worksheet "Sheet1">, <Worksheet "date1_copy">]
当然也可以循环这些工作表名称
for sheet in wb:
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 in row:
print(cell) # 每一行中的每一列
循环单元格以列为单位返回
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
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 in col:
cell.value = 'sharkyun'
获取单元格的值
# 方式一 指定获取第 4 行的第 3 列
cell_val = ws.cell(row=4,column=3).value
print(cell_val)
# 方式二 循环得到每个单元格的值
for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
for cell in col:
print(cell.value)
设置单元格的字体样式
from openpyxl.styles import Font
from openpyxl.styles import colors
# 设置字体样式
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 import Workbook
wb = Workbook()
ws = wb.active
# 假如循环的是新创建的工作表， 那么初始时工作表中是没有单元格的，
# 所以需要先在工作表中创建出一些单元格
ws['C4'] = "Hello"
# ws.rows 是一个生成器对象，可以迭代它，每次迭代会返回工作表中的一行
for row in ws.rows:
print(row)
# 以下是输出结果
(<Cell 'Sheet3'.A1>, <Cell 'Sheet3'.B1>, <Cell 'Sheet3'.C1>)
(<Cell 'Sheet3'.A2>, <Cell 'Sheet3'.B2>, <Cell 'Sheet3'.C2>)
(<Cell 'Sheet3'.A3>, <Cell 'Sheet3'.B3>, <Cell 'Sheet3'.C3>)
(<Cell 'Sheet3'.A4>, <Cell 'Sheet3'.B4>, <Cell 'Sheet3'.C4>)
迭代所有的列
此示例的数据对象，沿用了上面的示例
for col in ws.columns:
print(col)
# 以下是输出结果
(<Cell 'Sheet3'.A1>, <Cell 'Sheet3'.A2>, <Cell 'Sheet3'.A3>, <Cell 'Sheet3'.A4>)
(<Cell 'Sheet3'.B1>, <Cell 'Sheet3'.B2>, <Cell 'Sheet3'.B3>, <Cell 'Sheet3'.B4>)
(<Cell 'Sheet3'.C1>, <Cell 'Sheet3'.C2>, <Cell 'Sheet3'.C3>, <Cell 'Sheet3'.C4>)
保存数据
要把内存的数据保存到硬盘中，使用 wb.save() 方法即可
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws["A1"] = "hello"
wb.save("one.xlsx")
# 注意文件的后缀名 *.xlsx
示例演练
假设有如下数据主机 IP 信息，需要转化成 Excel 文件来展示
import xlsxwriter
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']
)
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import colors
# 定制一个字体样式对象
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:///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 = 16
ws1.column_dimensions['B'].width = 22
ws1.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 in zip(rows, host_ip):
for cell, item in zip(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 import load_workbook
# 获取文件对象
wb2 = load_workbook('empty_book.xlsx')
# 查看文件中所有的工作表名
wb2.get_sheet_names()
# 通过工作表名获取到工作表对象
ws = wb2.get_sheet_by_name('Sheet')
# 同样可以安装上面提到的方法访问这个工作表中的行和列
# 比如循环每一行
for row in ws.rows:
print(row)
