openpyxl使用—Excel篇

1.安装openpyxl

安装openxlsx

 pip install openpyxl

2.创建/打开、保存工作簿

2.1创建新的工作簿

from openpyxl import Workbook
#新建一个工作簿
#实例化一个对象
wb =Workbook()
ws = wb.active #获取默认的工作表
print(ws.title)  # 返回工作表名，Sheet

#保存的位置
#加r是为了解决转义的问题
wb.save(r"F:\desktop\aaa\test.xlsx")
wb.close()

2.2 打开已有的工作簿

from openpyxl import load_workbook
# 打开已存在的工作簿
wb =load_workbook(r"F:\desktop\aaa\test.xlsx")

ws = wb.active
print(ws.title)
ws.title = "default_2" #改变标题名为default_2
wb.save(r"F:\desktop\aaa\test.xlsx")  #保存

2.3保存工作簿

2.3.1 保存为文件

from openpyxl import Workbook

wb = Workbook()
ws = wb.active 
print(ws.title) 
wb.save("./test.xlsx")  # 保存到硬盘
wb.close()  # 该方法在只读或只写模式下有用

注意，通过保存路径和文件名相同会覆盖原先的文件，不会有提示

2.3.2 保存为流文件

有时候你需要保存为流文件，通过web应用服务进行传输，可以使用下面的方法

from tempfile import NamedTemporaryFile
from openpyxl import Workbook

wb = Workbook()
with NamedTemporaryFile() as tmp:
    wb.save(tmp.name)
    tmp.seek(0)
    stream = tmp.read()

2.4 工作表信息

print(ws.max_row)  # 最大行数，例如14
print(ws.max_column)  # 最大列数，例如20
print(ws.dimensions)  # 已启用的单元格范围，例如A1:T14
print(ws.encoding)  # 编码类型，例如utf-8
print(ws.sheet_view)  # 对象信息

3.操作单元格（获取、修改、合并，删除等）

3.1 操作工作表（创建、改名、移动、复制、删除）

from openpyxl import Workbook
wb =Workbook()
ws1 = wb.active

print(ws1.title)  # 返回工作表名，Sheet
print(wb.sheetnames)

# create_sheet(title,index)，接收两个参数，表名和位置
# title：表名
# index：下标即位置，从0开始
#创建sheet2工作簿，在2的位置，一开始索引为0
ws2 = wb.create_sheet("Sheet2",1)
ws3 = wb.create_sheet("Sheet3",2)


# 通过表名获取表
ws4=wb["Sheet3"]
print(ws4.title)

#将sheet3工作簿做移动一位， -1为向左移动，+1为向右移动
wb.move_sheet(ws3,-1)
#删除sheet3工作簿
# del wb["Sheet3"]

# 获取表的下标位置（下标从0开始）
index = wb.index(ws2)
print("get_index:", index)

print(wb.sheetnames)

3.2获取和修改单个单元格

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

cell = ws["a6"]  # 通过坐标获取
cell2 = ws.cell(1, 2)  # 通过行列下标获取

# 直接修改某个单元格的值
ws["a5"] = 666  
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")  # 修改为时间类型
ws.append([1, 2, 3]) # 在最下面新增一行追加一个或多个值

# 先获取单元格对象然后再进行修改
cell = ws["a6"]
cell.value = 777
print(cell, cell.value)  # 输出：<Cell 'Sheet'.A6> 777

cell2 = ws.cell(6, 1)  # 第6行第1列，即A6
print(cell2, cell2.value)  # 输出：<Cell 'Sheet'.A6> 777

# 单元格坐标信息
print(c.coordinate)  # 单元格坐标，例如A6
print(c.column_letter)  # 单元格列名，例如A
print(c.col_idx)  # 单元列下标，例如1
print(c.row)  # 单元格所在行，例如6

1.如果使用cell(row, column, value)获取，第一个参数是行，第二个参数是列，下标都是从1开始，例如，ws[“a6”]等同于ws.cell(6, 1)，但如果指定了第三个参数value，则修改了该单元格的值
2.只要访问了一个cell就会被创建，不管是否赋值

根据上面的方式，我们可以通过循环来准备一下数据

from openpyxl import Workbook
wb =Workbook()
ws = wb.active

x=1
for i in range(1,11):
    for j in range(1,6):
        ws.cell(i,j,x)
        x += 1
wb.save("./fangwen.xlsx")

这样我们就得到了从1~50共50个（10行5列）单元格的数据

3.3 获取多个单元格

3.3.1 通过范围取值

from openpyxl import Workbook

wb = Workbook()

ws = wb.active

i = 1
for x in range(1, 11):
    for y in range(1, 21):
        ws.cell(row=x, column=y, value=i)
        i += 1

# wb.save("test.xlsx")

row_cells = ws[2]  # 选取第2行（下标从1开始）
print(row_cells)  # 输出：(A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2)

col_cells = ws["b"]  # 选取B列
print(col_cells)  # 输出：(B1, B2, B3, B4, B5, B6, B7, B8, B9, B10)

row_range_cells = ws[2:5]  # 选取2、3、4、5共4行
print(row_range_cells)  # 输出：
# ((A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2),
# (A3, B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3, N3, O3, P3, Q3, R3, S3, T3),
# (A4, B4, C4, D4, E4, F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4, Q4, R4, S4, T4),
# (A5, B5, C5, D5, E5, F5, G5, H5, I5, J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5, T5))

col_range_cells = ws["B:D"]  # 选取B、C、D共3列
print(col_range_cells)  # 输出：
# ((B1, B2, B3, B4, B5, B6, B7, B8, B9, B10),
# (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10),
# (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10))

range_cells = ws["c3:f6"]  # 选取 C3到F6区域共16个元素
print(range_cells)  # 输出：
# ((C3, D3, E3, F3),
# (C4, D4, E4, F4),
# (C5, D5, E5, F5),
# (C6, D6, E6, F6))

1.以上输出应该类似<Cell ‘Sheet’.A2>、 <Cell ‘Sheet’.B2>，为了好看，简化为A2、B2的形式
2.以上获取到的多个单元格，返回的是元组或元组套元组，可以通过遍历的方式访问或修改

3.3.2通过iter_rows或iter_cols迭代取值

iter_rows()与iter_cols()都可以指定最大最小的行列，下标从1开始
返回结果是生成器

...
# wb.save("test.xlsx")

cells = ws.iter_rows(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
    print(cell)
# 输出：
# (B1, C1, D1, E1)
# (B2, C2, D2, E2)
# (B3, C3, D3, E3)

cells = ws.iter_cols(min_row=1, max_row=3, min_col=2, max_col=5)
for cell in cells:
    print(cell)
# 输出：
# (B1, B2, B3)
# (C1, C2, C3)
# (D1, D2, D3)
# (E1, E2, E3)

iter_cols和iter_rows都可以指定参数values_only=True，这样只返回值而不是cell对象

也可以使用rows或columns属性遍历全部行或列，values属性取出所有值，它们都得到迭代器，但是注意只读模式下columns属性无效

for cell in ws.rows:
    print(cell)

for cell in ws.columns:
    print(cell)

for row in ws.values:
    for value in row:
        print(value)

3.4操作单元格

3.4.1 合并单元格

合并单元格，会保留最左上角的单元格的数据和样式，其他单元格会被清空，即使取消合并。即，合并之后只保留左上角第一个单元格的数据和样式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
    for y in range(1, 21):
        ws.cell(row=x, column=y, value=i)
        i += 1

print(ws["C2"].value)  # 输出：23
ws.merge_cells("A1:F3")
ws.unmerge_cells("A1:F3")
print(ws["C2"].value)  # 输出：None
# 等同于下面的代码
# ws.merge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
# ws.unmerge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
wb.save("./test.xlsx")

3.4.2 删除或插入行列

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
    for y in range(1, 21):
        ws.cell(row=x, column=y, value=i)
        i += 1

ws.insert_cols(5)  # 在第5列即E列插入1列，原来的E列及后面的列都往后移动
ws.insert_rows(2, 3)  # 在第2行后面插入3行
ws.delete_cols(2, 3)  # 从2列开始往后删除3列
ws.delete_rows(5, 3)  # 从5行开始往后删除3行

wb.save("./test.xlsx")

3.4.3 移动单元格

可以使用move_range()合并指定范围的单元格，但是注意，如果移动到的位置原来有数据会被覆盖掉，移动之后公式会丢失，可以通过设置translate=True来更新，默认是False

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
i = 1
for x in range(1, 11):
    for y in range(1, 21):
        ws.cell(row=x, column=y, value=i)
        i += 1

ws.move_range("B1:D3", rows=6, cols=-1, translate=False)  # 移动单元格，向下移动6行，向左移动1列

wb.save("./test.xlsx")

4.设置样式（字体样式、行列宽高、对齐方式等）

4.1字体样式

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

# 默认字体样式
ws["A1"] = "A1"

# 自定义字体样式
ws["B2"] = "B2"
font = Font(
    name="微软雅黑",   # 字体
    size=15,         # 字体大小
    color="0000FF",  # 字体颜色，用16进制rgb表示
    bold=True,       # 是否加粗，True/False
    italic=True,     # 是否斜体，True/False
    strike=None,     # 是否使用删除线，True/False
    underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
)
ws["B2"].font = font

wb.save("./test.xlsx")

4.2 行列宽高

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws.row_dimensions[2].height = 30  # 设置第2行高度为30
ws.column_dimensions["B"].width = 30  # 设置B列宽度为30

wb.save("./test.xlsx")

4.3 对齐方式

from openpyxl import Workbook
from openpyxl.styles import Alignment

wb = Workbook()
ws = wb.active

ws.row_dimensions[2].height = 30  # 设置第2行高度为30
ws.column_dimensions["B"].width = 30  # 设置B列宽度为30

# 默认字体样式
ws["A1"] = "A1"

ws["B2"] = "B1"
ws['B2'].alignment = Alignment(
    horizontal='left',  # 水平对齐，可选general、left、center、right、fill、justify、centerContinuous、distributed
    vertical='top',  # 垂直对齐， 可选top、center、bottom、justify、distributed
    text_rotation=0,  # 字体旋转，0~180整数
    wrap_text=False,  # 是否自动换行
    shrink_to_fit=False,  # 是否缩小字体填充
    indent=0,  # 缩进值
)

wb.save("./test.xlsx")

4.4 边框

from openpyxl import Workbook
from openpyxl.styles import Border, Side

wb = Workbook()
ws = wb.active

ws["B2"] = "B2"

side = Side(
    style="medium",  # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
    color="ff66dd",  # 边框颜色，16进制rgb表示
)

ws["B2"].border = Border(
    top=side,  # 上
    bottom=side,  # 下
    left=side,  # 左
    right=side,  # 右
    diagonal=side  # 对角线
)

wb.save("./test.xlsx")

4.5 填充和渐变

from openpyxl import Workbook
from openpyxl.styles import PatternFill, GradientFill

wb = Workbook()
ws = wb.active

ws["B2"] = "B2"

fill = PatternFill(
    patternType="solid",  # 填充类型，可选none、solid、darkGray、mediumGray、lightGray、lightDown、lightGray、lightGrid
    fgColor="F562a4",  # 前景色，16进制rgb
    bgColor="0000ff",  # 背景色，16进制rgb
    # fill_type=None,  # 填充类型
    # start_color=None, # 前景色，16进制rgb
    # end_color=None    # 背景色，16进制rgb
)
ws["B2"].fill = fill
ws["B3"].fill = GradientFill(
    degree=60,  # 角度
    stop=("000000", "FFFFFF")  # 渐变颜色，16进制rgb
)

wb.save("./test.xlsx")

5.使用公式、复制(翻译)公式

5.1可用公式

当然我演示的openpyxl版本是3.0.9，一共支持352个公式，公式保存在一个frozenset类型的集合了，我们可以通过python的in语法判断是否支持某个公式，记住每个公式都是大写的

from openpyxl.utils import FORMULAE

print(FORMULAE)  # frozenset({'ODD', 'VDB', 'RANK', 'LOGEST', 'ISNONTEXT', 'COUNTA'...
print(len(FORMULAE))  # 352

# 判断是否支持某个公式，公式名区分大小写
print("SUM" in FORMULAE)  # True
print("PI" in FORMULAE)  # True
print("sum" in FORMULAE)  # False

使用公式很简单，你只要记得公式名和用法，直接像在Excel那样输入即可，例如，下面的求和、求平均值

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.append(["价格1", "价格2", "总和", "平均值"])
ws.append([22, 63])
ws.append([11, 88])
ws.append([15, 68])

ws["c2"] = "=SUM(A2,B2)"  # 求和
ws["d2"] = "=AVERAGE(A2:B2)"  # 求平均值
wb.save("test.xlsx")

5.2 翻译公式

过Excel的同学都知道，当某个单元格使用了公式，可以通过拖动填充柄的方式快速复制上一个公式进行填充，在openpyxl做法如下

from openpyxl import Workbook
from openpyxl.formula.translate import Translator

wb = Workbook()
ws = wb.active

ws.append(["价格1", "价格2", "总和", "平均值"])
ws.append([22, 63])
ws.append([11, 88])
ws.append([15, 68])

ws["c2"] = "=SUM(A2,B2)"
ws["d2"] = "=AVERAGE(A2:B2)"
# C3、C4使用上面的C2的求和公式
ws["C3"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C3")
ws["C4"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C4")

wb.save("test.xlsx")

结果如下图

当然，既然是重复操作，我们要使用优雅的循环写法

from openpyxl import Workbook
from openpyxl.formula.translate import Translator
....
ws["c2"] = "=SUM(A2,B2)"
ws["d2"] = "=AVERAGE(A2:B2)"
# C3、C4使用上面的C2的求和公式
for cell in ws["C3:C4"]:
    # ws["C3"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C3")
    cell[0].value = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula(cell[0].coordinate)
wb.save("test.xlsx")

6. 插入图标（例入折线图）

6.1 图表

Excel支持的图表类型还挺多的，包括柱状图、折线图、饼图、雷达图等等，2D和3D都有，而且支持很多自定义配置，例如颜色、大小、位置等。因为内容较多，所以我这里只举例折线图，其他图表类型大家可以参考官方文档
https://openpyxl.readthedocs.io/en/stable/charts/introduction.html

6.2折线图代码

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active

# 准备数据
rows = [
    ['月份', '桃子', '西瓜', '龙眼'],
    [1, 38, 28, 29],
    [2, 52, 21, 35],
    [3, 39, 20, 69],
    [4, 51, 29, 41],
    [5, 29, 39, 31],
    [6, 30, 41, 39],
]
for row in rows:
    ws.append(row)

# 创建图表
c1 = LineChart()
c1.title = "折线图"  # 标题
c1.style = 13  # 样式
c1.y_axis.title = '销量'  # Y轴
c1.x_axis.title = '月份'  # X轴

# 选择数据范围
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)

# 线条样式
s0 = c1.series[0]
s0.marker.symbol = "triangle"  # triangle为三角形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
s0.marker.graphicalProperties.solidFill = "FF0000"  # 填充颜色
s0.marker.graphicalProperties.line.solidFill = "0000FF"  # 边框颜色
# s0.graphicalProperties.line.noFill = True  # 改为True则隐藏线条，但显示标记形状

s1 = c1.series[1]
s1.graphicalProperties.line.solidFill = "00AAAA"
s1.graphicalProperties.line.dashStyle = "sysDot"  # 线条点状样式
s1.graphicalProperties.line.width = 80000  # 线条大小，最大20116800EMUs

s2 = c1.series[2]  # 采用默认设置
s2.smooth = True  # 线条平滑

ws.add_chart(c1, "A8")  # 图表位置

wb.save("line.xlsx")

大概过程是，创建一个图表（Chart）–指定数据范围（Reference）–设置系列（series）样式–添加到工作表中

7.过滤和排序

7.1过滤和排序

如果你想对表格进行过滤或排序，openpyxl有提供对应的设置，但是，只是添加过滤排序选项，并不会真的操作数据，如果想要操作，还是得在Excel中手动点击

2.代码

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 准备数据
rows = [
    ['月份', '桃子', '西瓜', '龙眼'],
    [1, 38, 28, 29],
    [2, 52, 21, 35],
    [3, 39, 20, 69],
    [4, 51, 29, 41],
    [5, 39, 39, 31],
    [6, 30, 41, 39],
]
for row in rows:
    ws.append(row)

ws.auto_filter.ref = "A1:D7"  # 选择数据范围
ws.auto_filter.add_filter_column(1, ["39", "29", "30"])  # 选择第2列为过滤数据（下标从0开始），并勾选需要过滤的数据项
ws.auto_filter.add_sort_condition("C2:C7", True)  # 设置排序范围，第二个参数是是否倒序，默认为否

wb.save("./openpyxl/test.xlsx")

效果如下

7.2 pandas排序

虽然openpyxl不能真的实现排序，但是我们可以借助超级强大的pandas轻松实现排序

import pandas as pd
# 读取上一步保存的Excel文件
df = pd.read_excel("./openpyxl/test.xlsx", sheet_name="Sheet")
df_value = df.sort_values(by=["桃子", "西瓜"], ascending=False)  # 如果"桃子"数据相同再按照"西瓜"进行排列
# 保存文件
writer = pd.ExcelWriter('./openpyxl/sort_file.xlsx')
df_value.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()

8. 只读模式、只写模式

说明：

前面我们使用的normal模式进行读写Excel文件，这是一种兼顾读写相对比较平衡的模式，但是，数据加载到内存占用的资源是比较大的，大概是文件的50倍，如果你的Excel文件本身就10M，加载之后程序
需要占用0.5G内存，这很不划算（大内存电脑请自动忽略），所以我们需要考虑是不是可以选择只读或只写模式以便提高性能

8.1只读模式

只读模式，如果你需要读取很大的Excel文件，但是又不改变和保存，例如只读取数值用于其他数据分析，这时候我们完全可以使用只读模式提供性能

from openpyxl import load_workbook

# 加载Excel文件时使用read_only指定只读模式
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

# 可以正常读取值
for row in ws.rows:
    for cell in row:
        print(cell.value)

# 注意：读取完之后需要手动关闭避免内存泄露
wb.close()

load_workbook参数说明：

定义：
def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA, data_only=False, keep_links=True)

参数：
read_only:是否只读，默认False
keep_vba：是否使用VBA编程，默认False
data_only：是否只加载数据值，即丢弃公式、排序等操作，默认False
keep_links：是否保留超链接，默认True

8.2 只写模式

如果文件是以写为主，可以在创建工作簿的时候指定为只写模式以便提高性能，不管文件有多大，都可以把内存保持在10M以下

from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font

wb = Workbook(write_only=True)  # 创建工作簿时指定只写模式
ws = wb.create_sheet()  # 需要通过create_sheet创建一个sheet

# 可以正常保存数据
for _ in range(100):
    ws.append([i for i in range(200)])  # 只能通过append写

# 如果需要保留公式、注释等操作，可以使用WriteOnlyCell
cell = WriteOnlyCell(ws, value="冰冷的希望")
cell.font = Font(name='黑体', size=15)
cell.comment = Comment(text="这是注释", author="pan")

ws.append([cell])

wb.save('openpyxl/test.xlsx')

只写模式注意点：
1.需要通过create_sheet()创建表
2.只能通过append()增加数据，不能通过cell或iter_rows()
3.wb.save()之后不能再修改，否则抛出WorkbookAlreadySaved异常