openpyxl操作excel教程

李凌峰

openpyxl使用—Excel篇

1.安装openpyxl

安装openxlsx

1. pip install openpyxl

复制代码

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

2.1创建新的工作簿

1. from openpyxl import Workbook
2. #新建一个工作簿
3. #实例化一个对象
4. wb =Workbook()
5. ws = wb.active #获取默认的工作表
6. print(ws.title)  # 返回工作表名，Sheet
8. #保存的位置
9. #加r是为了解决转义的问题
10. wb.save(r"F:\desktop\aaa\test.xlsx")
11. wb.close()

复制代码

2.2 打开已有的工作簿

1. from openpyxl import load_workbook
2. # 打开已存在的工作簿
3. wb =load_workbook(r"F:\desktop\aaa\test.xlsx")
5. ws = wb.active
6. print(ws.title)
7. ws.title = "default_2" #改变标题名为default_2
8. wb.save(r"F:\desktop\aaa\test.xlsx")  #保存

复制代码

2.3保存工作簿

2.3.1 保存为文件

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
5. print(ws.title)
6. wb.save("./test.xlsx")  # 保存到硬盘
7. wb.close()  # 该方法在只读或只写模式下有用

复制代码

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

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

1. from tempfile import NamedTemporaryFile
2. from openpyxl import Workbook
4. wb = Workbook()
5. with NamedTemporaryFile() as tmp:
6.     wb.save(tmp.name)
7.     tmp.seek(0)
8.     stream = tmp.read()

复制代码

2.4 工作表信息

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

复制代码

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

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

1. from openpyxl import Workbook
2. wb =Workbook()
3. ws1 = wb.active
5. print(ws1.title)  # 返回工作表名，Sheet
6. print(wb.sheetnames)
8. # create_sheet(title,index)，接收两个参数，表名和位置
9. # title：表名
10. # index：下标即位置，从0开始
11. #创建sheet2工作簿，在2的位置，一开始索引为0
12. ws2 = wb.create_sheet("Sheet2",1)
13. ws3 = wb.create_sheet("Sheet3",2)
16. # 通过表名获取表
17. ws4=wb["Sheet3"]
18. print(ws4.title)
20. #将sheet3工作簿做移动一位， -1为向左移动，+1为向右移动
21. wb.move_sheet(ws3,-1)
22. #删除sheet3工作簿
23. # del wb["Sheet3"]
25. # 获取表的下标位置（下标从0开始）
26. index = wb.index(ws2)
27. print("get_index:", index)
29. print(wb.sheetnames)

复制代码

3.2获取和修改单个单元格

1. from openpyxl import Workbook
2. wb = Workbook()
3. ws = wb.active
5. cell = ws["a6"]  # 通过坐标获取
6. cell2 = ws.cell(1, 2)  # 通过行列下标获取
8. # 直接修改某个单元格的值
9. ws["a5"] = 666  
10. ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")  # 修改为时间类型
11. ws.append([1, 2, 3]) # 在最下面新增一行追加一个或多个值
13. # 先获取单元格对象然后再进行修改
14. cell = ws["a6"]
15. cell.value = 777
16. print(cell, cell.value)  # 输出：<Cell 'Sheet'.A6> 777
18. cell2 = ws.cell(6, 1)  # 第6行第1列，即A6
19. print(cell2, cell2.value)  # 输出：<Cell 'Sheet'.A6> 777
21. # 单元格坐标信息
22. print(c.coordinate)  # 单元格坐标，例如A6
23. print(c.column_letter)  # 单元格列名，例如A
24. print(c.col_idx)  # 单元列下标，例如1
25. print(c.row)  # 单元格所在行，例如6

复制代码

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

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

1. from openpyxl import Workbook
2. wb =Workbook()
3. ws = wb.active
5. x=1
6. for i in range(1,11):
7.     for j in range(1,6):
8.         ws.cell(i,j,x)
9.         x += 1
10. wb.save("./fangwen.xlsx")

复制代码

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

3.3 获取多个单元格

3.3.1 通过范围取值

1. from openpyxl import Workbook
3. wb = Workbook()
5. ws = wb.active
7. i = 1
8. for x in range(1, 11):
9.     for y in range(1, 21):
10.         ws.cell(row=x, column=y, value=i)
11.         i += 1
13. # wb.save("test.xlsx")
15. row_cells = ws[2]  # 选取第2行（下标从1开始）
16. print(row_cells)  # 输出：(A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2)
18. col_cells = ws["b"]  # 选取B列
19. print(col_cells)  # 输出：(B1, B2, B3, B4, B5, B6, B7, B8, B9, B10)
21. row_range_cells = ws[2:5]  # 选取2、3、4、5共4行
22. print(row_range_cells)  # 输出：
23. # ((A2, B2, C2, D2, E2, F2, G2, H2, I2, J2, K2, L2, M2, N2, O2, P2, Q2, R2, S2, T2),
24. # (A3, B3, C3, D3, E3, F3, G3, H3, I3, J3, K3, L3, M3, N3, O3, P3, Q3, R3, S3, T3),
25. # (A4, B4, C4, D4, E4, F4, G4, H4, I4, J4, K4, L4, M4, N4, O4, P4, Q4, R4, S4, T4),
26. # (A5, B5, C5, D5, E5, F5, G5, H5, I5, J5, K5, L5, M5, N5, O5, P5, Q5, R5, S5, T5))
28. col_range_cells = ws["B:D"]  # 选取B、C、D共3列
29. print(col_range_cells)  # 输出：
30. # ((B1, B2, B3, B4, B5, B6, B7, B8, B9, B10),
31. # (C1, C2, C3, C4, C5, C6, C7, C8, C9, C10),
32. # (D1, D2, D3, D4, D5, D6, D7, D8, D9, D10))
34. range_cells = ws["c3:f6"]  # 选取 C3到F6区域共16个元素
35. print(range_cells)  # 输出：
36. # ((C3, D3, E3, F3),
37. # (C4, D4, E4, F4),
38. # (C5, D5, E5, F5),
39. # (C6, D6, E6, F6))

复制代码

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

3.3.2通过iter_rows或iter_cols迭代取值

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

1. ...
2. # wb.save("test.xlsx")
4. cells = ws.iter_rows(min_row=1, max_row=3, min_col=2, max_col=5)
5. for cell in cells:
6.     print(cell)
7. # 输出：
8. # (B1, C1, D1, E1)
9. # (B2, C2, D2, E2)
10. # (B3, C3, D3, E3)
12. cells = ws.iter_cols(min_row=1, max_row=3, min_col=2, max_col=5)
13. for cell in cells:
14.     print(cell)
15. # 输出：
16. # (B1, B2, B3)
17. # (C1, C2, C3)
18. # (D1, D2, D3)
19. # (E1, E2, E3)

复制代码

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

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

1. for cell in ws.rows:
2.     print(cell)
4. for cell in ws.columns:
5.     print(cell)
7. for row in ws.values:
8.     for value in row:
9.         print(value)

复制代码

3.4操作单元格

3.4.1 合并单元格

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

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
5. i = 1
6. for x in range(1, 11):
7.     for y in range(1, 21):
8.         ws.cell(row=x, column=y, value=i)
9.         i += 1
11. print(ws["C2"].value)  # 输出：23
12. ws.merge_cells("A1:F3")
13. ws.unmerge_cells("A1:F3")
14. print(ws["C2"].value)  # 输出：None
15. # 等同于下面的代码
16. # ws.merge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
17. # ws.unmerge_cells(start_row=1, start_column=1, end_row=3, end_column=6)
18. wb.save("./test.xlsx")

复制代码

3.4.2 删除或插入行列

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
5. i = 1
6. for x in range(1, 11):
7.     for y in range(1, 21):
8.         ws.cell(row=x, column=y, value=i)
9.         i += 1
11. ws.insert_cols(5)  # 在第5列即E列插入1列，原来的E列及后面的列都往后移动
12. ws.insert_rows(2, 3)  # 在第2行后面插入3行
13. ws.delete_cols(2, 3)  # 从2列开始往后删除3列
14. ws.delete_rows(5, 3)  # 从5行开始往后删除3行
16. wb.save("./test.xlsx")

复制代码

3.4.3 移动单元格

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

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
5. i = 1
6. for x in range(1, 11):
7.     for y in range(1, 21):
8.         ws.cell(row=x, column=y, value=i)
9.         i += 1
11. ws.move_range("B1:D3", rows=6, cols=-1, translate=False)  # 移动单元格，向下移动6行，向左移动1列
13. wb.save("./test.xlsx")

复制代码

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

4.1字体样式

1. from openpyxl import Workbook
2. from openpyxl.styles import Font
4. wb = Workbook()
5. ws = wb.active
7. # 默认字体样式
8. ws["A1"] = "A1"
10. # 自定义字体样式
11. ws["B2"] = "B2"
12. font = Font(
13.     name="微软雅黑",   # 字体
14.     size=15,         # 字体大小
15.     color="0000FF",  # 字体颜色，用16进制rgb表示
16.     bold=True,       # 是否加粗，True/False
17.     italic=True,     # 是否斜体，True/False
18.     strike=None,     # 是否使用删除线，True/False
19.     underline=None,  # 下划线, 可选'singleAccounting', 'double', 'single', 'doubleAccounting'
20. )
21. ws["B2"].font = font
23. wb.save("./test.xlsx")

复制代码

4.2 行列宽高

1. from openpyxl import Workbook
2. wb = Workbook()
3. ws = wb.active
5. ws.row_dimensions[2].height = 30  # 设置第2行高度为30
6. ws.column_dimensions["B"].width = 30  # 设置B列宽度为30
8. wb.save("./test.xlsx")

复制代码

4.3 对齐方式

1. from openpyxl import Workbook
2. from openpyxl.styles import Alignment
4. wb = Workbook()
5. ws = wb.active
7. ws.row_dimensions[2].height = 30  # 设置第2行高度为30
8. ws.column_dimensions["B"].width = 30  # 设置B列宽度为30
10. # 默认字体样式
11. ws["A1"] = "A1"
13. ws["B2"] = "B1"
14. ws['B2'].alignment = Alignment(
15.     horizontal='left',  # 水平对齐，可选general、left、center、right、fill、justify、centerContinuous、distributed
16.     vertical='top',  # 垂直对齐， 可选top、center、bottom、justify、distributed
17.     text_rotation=0,  # 字体旋转，0~180整数
18.     wrap_text=False,  # 是否自动换行
19.     shrink_to_fit=False,  # 是否缩小字体填充
20.     indent=0,  # 缩进值
21. )
23. wb.save("./test.xlsx")

复制代码

4.4 边框

1. from openpyxl import Workbook
2. from openpyxl.styles import Border, Side
4. wb = Workbook()
5. ws = wb.active
7. ws["B2"] = "B2"
9. side = Side(
10.     ,  # 边框样式，可选dashDot、dashDotDot、dashed、dotted、double、hair、medium、mediumDashDot、mediumDashDotDot、mediumDashed、slantDashDot、thick、thin
11.     color="ff66dd",  # 边框颜色，16进制rgb表示
12. )
14. ws["B2"].border = Border(
15.     top=side,  # 上
16.     bottom=side,  # 下
17.     left=side,  # 左
18.     right=side,  # 右
19.     diagonal=side  # 对角线
20. )
22. wb.save("./test.xlsx")

复制代码

4.5 填充和渐变

1. from openpyxl import Workbook
2. from openpyxl.styles import PatternFill, GradientFill
4. wb = Workbook()
5. ws = wb.active
7. ws["B2"] = "B2"
9. fill = PatternFill(
10.     patternType="solid",  # 填充类型，可选none、solid、darkGray、mediumGray、lightGray、lightDown、lightGray、lightGrid
11.     fgColor="F562a4",  # 前景色，16进制rgb
12.     bgColor="0000ff",  # 背景色，16进制rgb
13.     # fill_type=None,  # 填充类型
14.     # start_color=None, # 前景色，16进制rgb
15.     # end_color=None    # 背景色，16进制rgb
16. )
17. ws["B2"].fill = fill
18. ws["B3"].fill = GradientFill(
19.     degree=60,  # 角度
20.     stop=("000000", "FFFFFF")  # 渐变颜色，16进制rgb
21. )
23. wb.save("./test.xlsx")

复制代码

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

5.1可用公式

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

1. from openpyxl.utils import FORMULAE
3. print(FORMULAE)  # frozenset({'ODD', 'VDB', 'RANK', 'LOGEST', 'ISNONTEXT', 'COUNTA'...
4. print(len(FORMULAE))  # 352
6. # 判断是否支持某个公式，公式名区分大小写
7. print("SUM" in FORMULAE)  # True
8. print("PI" in FORMULAE)  # True
9. print("sum" in FORMULAE)  # False

复制代码

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

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
6. ws.append(["价格1", "价格2", "总和", "平均值"])
7. ws.append([22, 63])
8. ws.append([11, 88])
9. ws.append([15, 68])
11. ws["c2"] = "=SUM(A2,B2)"  # 求和
12. ws["d2"] = "=AVERAGE(A2:B2)"  # 求平均值
13. wb.save("test.xlsx")

复制代码

5.2 翻译公式

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

1. from openpyxl import Workbook
2. from openpyxl.formula.translate import Translator
4. wb = Workbook()
5. ws = wb.active
7. ws.append(["价格1", "价格2", "总和", "平均值"])
8. ws.append([22, 63])
9. ws.append([11, 88])
10. ws.append([15, 68])
12. ws["c2"] = "=SUM(A2,B2)"
13. ws["d2"] = "=AVERAGE(A2:B2)"
14. # C3、C4使用上面的C2的求和公式
15. ws["C3"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C3")
16. ws["C4"] = Translator(formula="=SUM(A2,B2)", origin="C2").translate_formula("C4")
18. wb.save("test.xlsx")

复制代码

结果如下图

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

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

复制代码

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

6.1 图表

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

1. from openpyxl import Workbook
2. from openpyxl.chart import LineChart, Reference
4. wb = Workbook()
5. ws = wb.active
7. # 准备数据
8. rows = [
9.     ['月份', '桃子', '西瓜', '龙眼'],
10.     [1, 38, 28, 29],
11.     [2, 52, 21, 35],
12.     [3, 39, 20, 69],
13.     [4, 51, 29, 41],
14.     [5, 29, 39, 31],
15.     [6, 30, 41, 39],
16. ]
17. for row in rows:
18.     ws.append(row)
20. # 创建图表
21. c1 = LineChart()
22. c1.title = "折线图"  # 标题
23. c1.style = 13  # 样式
24. c1.y_axis.title = '销量'  # Y轴
25. c1.x_axis.title = '月份'  # X轴
27. # 选择数据范围
28. data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
29. c1.add_data(data, titles_from_data=True)
31. # 线条样式
32. s0 = c1.series[0]
33. s0.marker.symbol = "triangle"  # triangle为三角形标记， 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
34. s0.marker.graphicalProperties.solidFill = "FF0000"  # 填充颜色
35. s0.marker.graphicalProperties.line.solidFill = "0000FF"  # 边框颜色
36. # s0.graphicalProperties.line.noFill = True  # 改为True则隐藏线条，但显示标记形状
38. s1 = c1.series[1]
39. s1.graphicalProperties.line.solidFill = "00AAAA"
40. s1.graphicalProperties.line.dashStyle = "sysDot"  # 线条点状样式
41. s1.graphicalProperties.line.width = 80000  # 线条大小，最大20116800EMUs
43. s2 = c1.series[2]  # 采用默认设置
44. s2.smooth = True  # 线条平滑
46. ws.add_chart(c1, "A8")  # 图表位置
48. wb.save("line.xlsx")

复制代码

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

7.1过滤和排序

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

2.代码

1. from openpyxl import Workbook
3. wb = Workbook()
4. ws = wb.active
6. # 准备数据
7. rows = [
8.     ['月份', '桃子', '西瓜', '龙眼'],
9.     [1, 38, 28, 29],
10.     [2, 52, 21, 35],
11.     [3, 39, 20, 69],
12.     [4, 51, 29, 41],
13.     [5, 39, 39, 31],
14.     [6, 30, 41, 39],
15. ]
16. for row in rows:
17.     ws.append(row)
19. ws.auto_filter.ref = "A1:D7"  # 选择数据范围
20. ws.auto_filter.add_filter_column(1, ["39", "29", "30"])  # 选择第2列为过滤数据（下标从0开始），并勾选需要过滤的数据项
21. ws.auto_filter.add_sort_condition("C2:C7", True)  # 设置排序范围，第二个参数是是否倒序，默认为否
23. wb.save("./openpyxl/test.xlsx")

复制代码

效果如下

7.2 pandas排序

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

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

复制代码

8. 只读模式、只写模式

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

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

1. from openpyxl import load_workbook
3. # 加载Excel文件时使用read_only指定只读模式
4. wb = load_workbook(filename='large_file.xlsx', read_only=True)
5. ws = wb['big_data']
7. # 可以正常读取值
8. for row in ws.rows:
9.     for cell in row:
10.         print(cell.value)
12. # 注意：读取完之后需要手动关闭避免内存泄露
13. 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以下

1. from openpyxl import Workbook
2. from openpyxl.cell import WriteOnlyCell
3. from openpyxl.comments import Comment
4. from openpyxl.styles import Font
6. wb = Workbook(write_only=True)  # 创建工作簿时指定只写模式
7. ws = wb.create_sheet()  # 需要通过create_sheet创建一个sheet
9. # 可以正常保存数据
10. for _ in range(100):
11.     ws.append([i for i in range(200)])  # 只能通过append写
13. # 如果需要保留公式、注释等操作，可以使用WriteOnlyCell
14. cell = WriteOnlyCell(ws, value="冰冷的希望")
15. cell.font = Font(name='黑体', size=15)
16. cell.comment = Comment(text="这是注释", author="pan")
18. ws.append([cell])
20. wb.save('openpyxl/test.xlsx')

复制代码

只写模式注意点：
1.需要通过create_sheet()创建表
2.只能通过append()增加数据，不能通过cell或iter_rows()
3.wb.save()之后不能再修改，否则抛出WorkbookAlreadySaved异常
-->                        收藏                                    关注                                    评论
来源:https://www.cnblogs.com/yin-jihu/p/17988408
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！