Python3读写 EXCEL文档

XlsxWriter 只写

基本介绍

XlsxWriter 是在 Python 下操作 EXCEL 文档的利器

100% 支持 Excel XLSX 文件， 支持 Excel 2003、Excel 2007 等版本

支持所有 Excel 单元格的数据格式

包括：

• 合并单元格、批注、自动筛选、丰富多格式字符串等
• 支持工作表PNG / JPEG / BMP / WMF / EMF图像。
• 用于写入大文件的内存优化模式。

官方文档 https://xlsxwriter.readthedocs.io/

安装

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)

# 输出结果:
[<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://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)