python3读写excel文件_Python3读写 EXCEL文档

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()

完成图

image.png

方法解析

行和列的表示方法

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

示例文档的样子

image.png

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)

# 输出结果:

[, , , ]

当然也可以循环这些工作表名称

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)

# 以下是输出结果

(, , )

(, , )

(, , )

(, , )

迭代所有的列

此示例的数据对象，沿用了上面的示例

for col in ws.columns:

print(col)

# 以下是输出结果

(, , , )

(, , , )

(, , , )

保存数据

要把内存的数据保存到硬盘中，使用 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://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 = 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)