openpyxl操作excel教程

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异常

-->
收藏
关注
评论