简介
openpyxl 是一个用于读写 Excel 2010 及以上版本（.xlsx/.xlsm/.xltx/.xltm）文件的 Python 库。
安装
pip install openpyxl
教程
book
创建excel
Workbook
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
wb.save('example.xlsx')
读取现有excel
load_workbook
- 常用参数
- data_only：以数据形式读取，忽略公式等
- read_only: 只读, 若不需要写入，可以使用read_only来减少读取时间。
from openpyxl import load_workbook
wb = load_workbook('example.xlsx')
ws = wb['sheet1']
保存 save
wb.save('path')
sheet
获取sheet对象
ws = wb.active
ws = wb['Sheet1']
遍历sheet
from sheet in wb.sheetnames:
print(sheet)
创建sheet
create_sheet(title, index)
ws1 = wb.create_sheet('sh1')
ws2 = wb.create_sheet('sh2', 0)
修改sheet 名
ws.title = 'a'
复制sheet
只能在一个workbook内
ws2 = wb.copy_worksheet(wb['ws1'])
# ws2 的sheet名为 ws1 Copy
页面设置
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
ws.page_setup.paperSize = ws.PAPERSIZE_TABLOID
ws.page_setup.fitToHeight = 0
ws.page_setup.fitToWidth = 1
页眉和页脚
ws.oddHeader.left.text = "Page &[Page] of &N"
ws.oddHeader.left.size = 14
ws.oddHeader.left.font = "Tahoma,Bold"
ws.oddHeader.left.color = "CC3366"
# ws.evenHeader
# ws.evenFooter
# ws.firstHeader
# ws.firstFooter
打印设置
ws.print_options.horizontalCentered = True
ws.print_options.verticalCentered = True
打印标题
ws.print_title_cols = 'A:B' # the first two cols
ws.print_title_rows = '1:1' # the first row
打印区域
ws.print_area = 'A1:F10'
range
cell_range = ws['A1':'C2']
ws['C']
ws[0]
插入 列 & 行
# 第七行插入一行 (存在第七行)
ws.insert_rows(7)
删除
# 删除 F:H 列
ws.delete_cols(6, 3)
合并&拆分单元格
ws.merge_cells('A2:D2')
ws.unmerge_cells('A2:D2')
移动
# 将 E4:F10 单元格向上移动3行向右移动2列，已存在的单元格将会被覆盖
ws.move_range("E4:F10", rows=-3, cols=2)
ws.move_range("E4:F10", rows=-3, cols=2, translate=True)
如果移动的单元格中有公式，可以使用 translate=True 参数，只有单元格本身的公式被调整，引用改单元格的不会更新。
隐藏
ws.column_dimensions.group('A','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)
cell
遍历
iter_rows()iter_cols()
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=4):
if row[1].value == 2:
print(1)
for col in row:
print(col.value)
填充值
ws['A1'] = 10
ws.cell(1, 1) = 29
公式
ws['A1'].value = 'row()-1'
append
ws.append([1, 2, 3, 4])
插入图像
from openpyxl.drawing.image import Image
img = Image('logo.png')
ws.add_image(img, 'A1')
获取值
ws['A1'].value
ws.cell(1, 1).value
单元格格式
ws['A1'].number_format
number_format = 'General'
注释
加载时工作簿中存在的注释会自动存储在其相应单元格的 comment 属性中
from openpyxl.comments import Comment
comment = ws["A1"].comment
comment = Comment('This is the comment text', 'Comment Author')
print(comment.text)
print(comment.author)
comment.width = 300 # 单位，像素
comment.height = 50
ws["A1"].comment = comment
使用openpyxl.utils.units 将其他度量单位（mm，points）转换为像素
comment = Comment("Text", "Author")
comment.width = units.points_to_pixels(300)
comment.height = units.points_to_pixels(50)
样式
color
有三种方式： indexed, aRGB， theme
- 索引颜色（indexed colours）是旧版实现，颜色本身取决于工作簿或应用程序默认提供的索引。
- 主题颜色可用于互补色，但也取决于工作簿中存在的主题。
- 建议使用aRGB颜色。
from openpyxl.styles.colors import Color
c = Color(indexed=22)
c = Color(theme=3, tint=0.2)
protection
protection = Protection(locked=True, hidden=False)
font
from openpyxl.styles import Font
font = Font(
name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'
)
fill
from openpyxl.styles import PatternFill
- PatternFill 参数
- fill_type： 指定填充的类型，“solid”、 “darkGray”、“mediumGray”、“lightGray”、“grid”（网格）、“dots”（点）、“horiz”（水平条纹）、“vert”（垂直条纹）等
- start_color: 定义填充的主颜色, 使用 十六进制颜色代码（不带 # 符号）
- end_color: 定义填充的辅助颜色
ws['A1'].fill = PatternFill(
start_color="FF0000", # 红色
end_color="FF0000",
fill_type="solid"
)
// 只能逐个填充，如果需要多个，则需要遍历
for row in ws.iter_rows():
for col in row:
col.fill = fill
常用的颜色代码参考：
- 红色：FF0000
- 绿色：00FF00
- 蓝色：0000FF
- 黄色：FFFF00
- 白色：FFFFFF
- 黑色：000000
border
from openpyxl.styles import Border, Side
thin_border = Side(style="thin", color="000000") # 细实线，黑色
medium_border = Side(style="medium", color="FF0000") # 中等粗细，红色
border = Border(
left=thin_border, # 左边框
right=thin_border, # 右边框
top=medium_border, # 上边框
bottom=medium_border,# 下边框
diagonal=thin_border,# 对角线（可选）
diagonalUp=True, # 对角线方向（上）
diagonalDown=True # 对角线方向（下）
)
ws['A1'].border = border
alignment
from openpyxl.styles import Alignment
alignment = Alignment(
horizontal="center", # 水平对齐
vertical="center", # 垂直对齐
wrap_text=True, # 自动换行
indent=1, # 缩进（字符数）
text_rotation=0 # 文本旋转角度（0-180）
)
ws['A1'].alignment = alignment
参数
-
horizontal
- general：默认对齐（文本左对齐，数字右对齐）
- left、center、right
- fill： 填充对齐
- justify： 两端对齐
- centerContinuous： 跨列居中
- distributed：分散对齐
-
vertical
- top, center, bottom
- justify: 底端对齐
- distributed: 分散对齐
-
wrap_text: 设置为 True 时，单元格内容会根据列宽自动换行
-
indent: 整数，设置文本左侧缩进的字符数
-
text_rotation: 整数（0-180），设置文本旋转角度
命名样式
from openpyxl.styles import NamedStyle, Font, Border, Side
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=20)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
wb.add_named_style(highlight)
ws['A1'].style = highlight
ws['D5'].style = 'highlight'
和pandas 相互转换
from openpyxl.utils.dataframe import dataframe_to_rows
for row in dataframe_to_rows(df, index=True, header=True):
ws.append(row)
转 pandas
from itertools import islice
data = ws.values
cols = next(data)[1:]
data = list(data)
idx = [r[0] for r in data]
data = (islice(r, 1, None) for r in data)
df = DataFrame(data, index=idx, columns=cols)
图表
用openpyxl打开文件并进行保存会导致图片和图表的丢失，因为 openpyxl 无法读取 Excel 文件所有可能的项。
面积图
二维
from openpyxl.chart import AreaChart, Reference, Series
chart = AreaChart()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
三维
from openpyxl.chart import AreaChart3D, Reference, Series
chart = AreaChart3D()
chart.title = "Area Chart"
chart.style = 13
chart.x_axis.title = 'Test'
chart.y_axis.title = 'Percentage'
chart.legend = None
cats = Reference(ws, min_col=1, min_row=1, max_row=7)
data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "A10")
其他类似。
筛选和排序
ws.auto_filter.ref = "A1:B15"
ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
ws.auto_filter.add_sort_condition("B2:B15")
这会将相关指令添加到文件中，但实际上不会 过滤或排序 。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
