OpenPyXL教程
一、Excel概念理解
Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。
- 工作簿：每一个excel文件就是一个工作簿，文件扩展名为
.xlsx - 工作表：每次新建工作簿后默认在这个文件中创建一个
Sheet1页，这就是一个工作表，每一个工作簿中可有255个工作表 - 单元格：工作表中的每一个方格就是一个单元格（由行号和列号定位），是excel中存储数据的最基本元素
- 行号：工作表中的每一行用一个数字进行标识，最多
65536行 - 列号：工作表中的每一列用字母进行标识，最多
256列，命名通过A、B、C...AA...、BA...
二、openpyxl 简介
openpyxl 是一个用于读取/写入 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。
在使用该模块时首先进行安装：
>>> pip install openpyxl
然后可以进行使用，简单的使用可以按照如下步骤：
- 创建工作簿
- 创建工作表
- 操作单元格
- 保存文件
from openpyxl import Workbook
wb = Workbook() # 创建工作簿
ws1 = wb.create_sheet("Mysheet") # 创建工作表
ws1['A1'] = 2 # 单元格赋值
wb.save('files/1.xlsx') # 保存文件
上面就是简单的操作，接下来具体学习一下每部分的操作，包括：
- 工作簿操作
- 工作表操作
- 单元格操作
- 颜色样式配置
- 集成Pandas
三、工作簿操作
from openpyxl import Workbook # 导入模块
- Workbook() 创建新工作簿
- load_workbook(path) 加载现有工作簿
如果需要删除工作簿，相当于删除文件，可使用os模块：
- os.remove（path 删除文件
四、工作表操作
（一）获取默认工作表
当工作簿创建后会默认创建一个工作表，此时可以通过Workbook.active属性获取
>>> ws = wb.active # <Worksheet "Sheet">
（二）创建新的工作表
通过Workbook.create_sheet可创建新的工作表，参数为工作表的名称
>>> ws1 = wb.create_sheet("Mysheet") # insert at the end (default)
# or
>>> ws2 = wb.create_sheet("Mysheet", 0) # insert at first position
# or
>>> ws3 = wb.create_sheet("Mysheet", -1) # insert at the penultimate position
（三）复制工作表
可以在单个工作簿中创建工作表的副本，通过Workbook.copy_worksheet()方法：
>>> source = wb.active
>>> target = wb.copy_worksheet(source)
注意：仅复制单元格（包括值、样式、超链接和注释）和某些工作表属性（包括维度、格式和属性）。不会复制所有其他工作簿/工作表属性 - 例如图像、图表。
也不能在工作簿之间复制工作表。如果工作簿以只读或只写模式打开，则无法复制工作表 。
（四）获取所有工作表
循环浏览工作表：
>>> for sheet in wb: # 每一个sheet就是一个工作表对象
... print(sheet.title)
另外也可以通过Workbook.sheetname获取工作簿的所有工作表的名称，然后再获取具体的工作表：
print(wb.sheetnames) # ['Sheet', 'Mysheet', 'Mysheet Copy']
for sheet_name in wb.sheetnames:
print(wb[sheet_name])
"""
<Worksheet "Sheet">
<Worksheet "Mysheet">
<Worksheet "Mysheet Copy">
"""
（五）移除工作表
从工作簿中移除工作表，通过WorkBook.remove(worksheet)来实现：
from openpyxl import load_workbook
wb = load_workbook('files/1.xlsx')
wb.remove(wb["Mysheet Copy"])
wb.save('files/2.xlsx')
（六）移动工作表
将一个工作簿中的工作表从一个位置移动到另一个位置，WorkBook.move_sheet(worksheet, index)：
from openpyxl import load_workbook
wb = load_workbook('files/1.xlsx')
wb.move_sheet(wb["Mysheet"], -1) # 将后面的sheet移动到第一个位置需要使用负值
wb.save('files/3.xlsx')
（七）修改工作表属性
1、修改标题
工作表在创建时会自动命名。它们按顺序编号（Sheet、Sheet1、Sheet2、...）。使用Worksheet.title属性更改此名称：
ws.title = "New Title"
2、修改标题颜色
默认情况下，此标题的选项卡的背景颜色为白色。可以更改此设置，通过设置RRGGBB颜色 ：Worksheet.sheet_properties.tabColor
ws.sheet_properties.tabColor = "1072BA"
（六）移除工作表
五、单元格操作
（一）访问单个单元格
1、列行索引
单元格可以作为工作表的键直接访问：
>>> c = ws['A4'] # 列数、行数作为键
这将返回 A4 处的单元格，如果尚不存在则创建一个。可以直接赋值：
>>> ws['A4'] = 4
2、cell方式
Worksheet.cell()方法提供了对使用行和列表示法的单元格的访问：
>>> d = ws.cell(row=4, column=2, value=10)
ws.cell方法将第四行、第二列单元格赋值为10，并且返回对象<Cell 'Sheet'.B4>
（二）访问多个单元格
1、切片访问
可以使用切片访问单元格范围：
>>> cell_range = ws['A1':'C2']
可以类似地获得行或列的范围：
>>> colC = ws['C']
>>> col_range = ws['C:D']
>>> row10 = ws[10]
>>> row_range = ws[5:10]
2、Worksheet.iter_rows()
您还可以使用以下Worksheet.iter_rows()方法：
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2):
... for cell in row:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.B1>
<Cell Sheet1.C1>
<Cell Sheet1.A2>
<Cell Sheet1.B2>
<Cell Sheet1.C2>
3、Worksheet.iter_cols()
同样，该Worksheet.iter_cols()方法将返回列：
>>> for col in ws.iter_cols(min_row=1, max_col=3, max_row=2):
... for cell in col:
... print(cell)
<Cell Sheet1.A1>
<Cell Sheet1.A2>
<Cell Sheet1.B1>
<Cell Sheet1.B2>
<Cell Sheet1.C1>
<Cell Sheet1.C2>
4、Worksheet.rows
如果您需要遍历文件的所有行或列，您可以改用该 Worksheet.rows属性：
>>> ws = wb.active
>>> ws['C9'] = 'hello world'
>>> tuple(ws.rows)
((<Cell Sheet.A1>, <Cell Sheet.B1>, <Cell Sheet.C1>),
(<Cell Sheet.A2>, <Cell Sheet.B2>, <Cell Sheet.C2>),
(<Cell Sheet.A3>, <Cell Sheet.B3>, <Cell Sheet.C3>),
(<Cell Sheet.A4>, <Cell Sheet.B4>, <Cell Sheet.C4>),
(<Cell Sheet.A5>, <Cell Sheet.B5>, <Cell Sheet.C5>),
(<Cell Sheet.A6>, <Cell Sheet.B6>, <Cell Sheet.C6>),
(<Cell Sheet.A7>, <Cell Sheet.B7>, <Cell Sheet.C7>),
(<Cell Sheet.A8>, <Cell Sheet.B8>, <Cell Sheet.C8>),
(<Cell Sheet.A9>, <Cell Sheet.B9>, <Cell Sheet.C9>))
5、Worksheet.columns
类似的Worksheet.columns：
>>> tuple(ws.columns)
((<Cell Sheet.A1>,
<Cell Sheet.A2>,
<Cell Sheet.A3>,
<Cell Sheet.A4>,
<Cell Sheet.A5>,
<Cell Sheet.A6>,
...
<Cell Sheet.B7>,
<Cell Sheet.B8>,
<Cell Sheet.B9>),
(<Cell Sheet.C1>,
<Cell Sheet.C2>,
<Cell Sheet.C3>,
<Cell Sheet.C4>,
<Cell Sheet.C5>,
<Cell Sheet.C6>,
<Cell Sheet.C7>,
<Cell Sheet.C8>,
<Cell Sheet.C9>))
（三）单元格值访问
如果您只想要工作表中的值，则可以使用该Worksheet.values属性。这将遍历工作表中的所有行，但仅返回单元格值：
for row in ws.values:
for value in row:
print(value)
Worksheet.iter_rows()和都Worksheet.iter_cols()可以采用values_only参数来仅返回单元格的值：
>>> for row in ws.iter_rows(min_row=1, max_col=3, max_row=2, values_only=True):
... print(row)
(None, None, None)
(None, None, None)
（四）合并、取消单元格
当您合并单元格时，除了左上角的单元格之外的所有单元格都将从工作表中删除。为了携带合并单元格的边界信息，合并单元格的边界单元格被创建为 MergeCells，其值始终为 None。
openpyxl.worksheet.worksheet.Worksheet
def merge_cells(self, range_string=None, start_row=None, start_column=None, end_row=None, end_column=None):
""" Set merge on a cell range. Range is a cell range (e.g. A1:E1) """
该方法有两种传参方式可合并单元格：
>>> from openpyxl import load_workbook
>>> wb = load_workbook('files/1.xlsx')
>>> ws = wb["Sheet"]
>>> ws.merge_cells(start_row=1, start_column=1, end_row=3, end_column=5) # 合并
>>> wb.save('files/5.xlsx')
对于合并还可以如下：
>>> ws.merge_cells("A1:E3")
（五）插入和删除行和列，移动单元格范围
1、 插入行和列
您可以使用相关的工作表方法插入行或列：
openpyxl.worksheet.worksheet.Worksheet.insert_rows()openpyxl.worksheet.worksheet.Worksheet.insert_cols()openpyxl.worksheet.worksheet.Worksheet.delete_rows()openpyxl.worksheet.worksheet.Worksheet.delete_cols()
默认为一行或一列。例如在第 7 行插入一行（在现有第 7 行之前）：
>>> ws.insert_rows(7)
2、删除行和列
要删除列F:H：
>>> ws.delete_cols(6, 3)
3、单元格的移动范围
您还可以在工作表中移动单元格范围：
def move_range(self, cell_range, rows=0, cols=0, translate=False):
"""
Move a cell range by the number of rows and/or columns:
down if rows > 0 and up if rows < 0
right if cols > 0 and left if cols < 0
Existing cells will be overwritten.
Formulae and references will not be updated.
"""
>>> ws.move_range("D4:F10", rows=-1, cols=2)
这会将范围内的单元格D4:F10向上移动一行，向右移动两列。这些单元格将覆盖任何现有单元格。
如果单元格包含公式，您可以让 openpyxl 为您翻译这些公式，但由于这并不总是您想要的，因此默认情况下它是禁用的。也只有单元格本身的公式会被翻译。
六、颜色样式配置
（一）样式方法
样式用于更改数据在屏幕上显示时的外观。它们还用于确定数字的格式。
样式可以应用于以下几个方面：
- font 设置字体大小、颜色、下划线等。
- fill 填充以设置图案或颜色渐变
- border 在单元格上设置边框
- cell 单元格对齐
- protection 保护
以下为默认值：
>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> font = Font(name='Calibri',
... size=11,
... bold=False,
... italic=False,
... vertAlign=None,
... underline='none',
... strike=False,
... color='FF000000')
>>> fill = PatternFill(fill_type=None,
... start_color='FFFFFFFF',
... end_color='FF000000')
>>> border = Border(left=Side(border_style=None,
... color='FF000000'),
... right=Side(border_style=None,
... color='FF000000'),
... top=Side(border_style=None,
... color='FF000000'),
... bottom=Side(border_style=None,
... color='FF000000'),
... diagonal=Side(border_style=None,
... color='FF000000'),
... diagonal_direction=0,
... outline=Side(border_style=None,
... color='FF000000'),
... vertical=Side(border_style=None,
... color='FF000000'),
... horizontal=Side(border_style=None,
... color='FF000000')
... )
>>> alignment=Alignment(horizontal='general',
... vertical='bottom',
... text_rotation=0,
... wrap_text=False,
... shrink_to_fit=False,
... indent=0)
>>> number_format = 'General'
>>> protection = Protection(locked=True,
... hidden=False)
>>>
（二）颜色配置
字体、背景、边框等的颜色可以通过三种方式设置：索引、aRGB 或主题。索引颜色是遗留实现，颜色本身取决于工作簿或应用程序默认提供的索引。主题颜色对于颜色的互补色调很有用，但也取决于工作簿中存在的主题。因此，建议使用 aRGB 颜色。
RGB 颜色使用红色、绿色和蓝色的十六进制值设置。
RGB 颜色使用红色、绿色和蓝色的十六进制值设置。
>>> from openpyxl.styles import Font
>>> font = Font(color="FF0000")
alpha 值在理论上指的是颜色的透明度，但这与单元格样式无关。默认值 00 将添加到任何简单的 RGB 值之前：
>>> from openpyxl.styles import Font
>>> font = Font(color="00FF00")
>>> font.color.rgb
'0000FF00'
（三）单元格样式和命名样式
有两种类型的样式：单元格样式和命名样式，也称为样式模板。
1、单元格样式
单元格样式在对象之间共享，一旦分配它们就不能更改。例如在只有一个更改时可以更改大量单元格的样式。
>>> from openpyxl.styles import colors
>>> from openpyxl.styles import Font, Color
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> a1 = ws['A1']
>>> d4 = ws['D4']
>>> ft = Font(color="FF0000")
>>> a1.font = ft
>>> d4.font = ft
>>>
>>> a1.font.italic = True # is not allowed # doctest: +SKIP
>>>
>>> # If you want to change the color of a Font, you need to reassign it::
>>>
>>> a1.font = Font(color="FF0000", italic=True) # the change only affects A1
2、命名样式
与单元格样式相反，命名样式是可变的。当您想一次将格式应用于许多不同的单元格时，它们很有意义。注意。一旦您为单元格指定了命名样式，对该样式的其它更改将 不会影响该单元格。
一旦命名样式已在工作簿中注册，就可以简单地通过名称来引用它。
创建命名样式：
>>> 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'
（四）样式应用（合并单元格）
合并后的单元格与其他单元格对象的行为类似。它的值和格式在其左上角的单元格中定义。要更改整个合并单元格的边框，请更改其左上角单元格的边框。
>>> from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
>>> from openpyxl import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws.merge_cells('B2:F4')
>>>
>>> top_left_cell = ws['B2']
>>> top_left_cell.value = "My Cell"
>>>
>>> thin = Side(border_style="thin", color="000000")
>>> double = Side(border_style="double", color="ff0000")
>>>
>>> top_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)
>>> top_left_cell.fill = PatternFill("solid", fgColor="DDDDDD")
>>> top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF"))
>>> top_left_cell.font = Font(b=True, color="FF0000")
>>> top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
>>>
>>> wb.save("styled.xlsx")
七、集成Pandas
（一）Dataframes转成excel
该openpyxl.utils.dataframe.dataframe_to_rows()函数提供了一种使用 Pandas Dataframes 的简单方法：
from openpyxl import Workbook
import seaborn as sns
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.DataFrame([{"username": 'lily', 'age': 12}, {"username": 'lily', 'age': 12}])
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=True, header=True):
print(r)
ws.append(r)
wb.save("files/pandas_openpyxl.xlsx")
虽然 Pandas 本身支持转换为 Excel，但这为客户端代码提供了额外的灵活性，包括将数据帧直接流式传输到文件的能力。
要将数据框转换为突出显示标题和索引的工作表：
from openpyxl import Workbook
import seaborn as sns
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
df = pd.DataFrame([{"username": 'lily', 'age': 12}, {"username": 'lily', 'age': 12}])
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=True, header=True):
print(r)
ws.append(r)
for cell in ws['A'] + ws[1]:
cell.style = 'Pandas'
wb.save("files/pandas_openpyxl.xlsx")
或者，如果您只想转换数据，您可以使用只写模式：
from openpyxl.cell.cell import WriteOnlyCell
wb = Workbook(write_only=True)
ws = wb.create_sheet()
cell = WriteOnlyCell(ws)
cell.style = 'Pandas'
def format_first_row(row, cell):
for c in row:
cell.value = c
yield cell
rows = dataframe_to_rows(df)
first_row = format_first_row(next(rows), cell)
ws.append(first_row)
for row in rows: # 通过生成器取走了第一行，这里从第二行开始
row = list(row)
cell.value = row[0]
row[0] = cell
ws.append(row)
wb.save("openpyxl_stream.xlsx")
（二）将excel装成Dataframes
要将工作表转换为 Dataframe，您可以使用values属性。如果工作表没有标题或索引，这很容易：
df = DataFrame(ws.values)
如果工作表确实有标题或索引，例如由 Pandas 创建的，则需要做更多的工作：
from itertools import islice
from openpyxl import load_workbook
import pandas as pd
wb = load_workbook('files/openpyxl_stream.xlsx')
ws = wb.active
data = ws.values # [(None, 'username', 'age'), (None, None, None), (0, 'lily', 12), (1, 'lily', 12)]
cols = next(data)[1:] # ('username', 'age')
data = list(data) # [(0, 'lily', 12), (1, 'lily', 12)]
idx = [r[0] for r in data] # [0, 1]
data = (islice(r, 1, None) for r in data) # [['lily', 12], ['lily', 12]]
df = pd.DataFrame(data, index=idx, columns=cols)
islice用法：
# islice('ABCDEFG', 2) --> A B # islice('ABCDEFG', 2, 4) --> C D # islice('ABCDEFG', 2, None) --> C D E F G # islice('ABCDEFG', 0, None, 2) --> A C E G
八、csv文件转成excel
数据来源：College Majors and their Graduates
将Major_category列中相同的数据进行合并：
from openpyxl import Workbook
import pandas as pd
from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active
df = pd.read_csv('files/all-ages.csv')
df = df[["Major_category", "Major", "Major_code", "Total"]]
df = df.sort_values(by="Major_category")
data = df.to_dict(orient="list")
head_list = []
value_dict = {}
value_list = []
start_col = end_col = 1
"""
[
{'Agriculture & Natural Resources':[1,3,1,1], },
{}
] # [start_row,end_row,start_col, end_col]
"""
for k, v in data.items():
head_list.append(k)
col_dict = {}
start_row = end_row = 2
for item in v:
if item not in col_dict:
col_dict[item] = []
if len(col_dict) >= 2:
end_row += 1
start_row = end_row
else:
end_row += 1
col_dict[item] = [start_row, end_row, start_col, end_col]
start_col += 1
end_col += 1
value_list.append(col_dict)
"""
value_list = [{'Agriculture & Natural Resources': [2, 11, 1, 1],
'Arts': [12, 19, 1, 1],...}
...,
{'GENERAL AGRICULTURE': [2, 2, 2, 2],...}
]
"""
for idx, head in enumerate(head_list):
ws.cell(1, idx + 1, head)
col_name_list = ['A', 'B', 'C', 'D', 'E', 'F']
for idx, item in enumerate(value_list):
for k, v in item.items():
ws.merge_cells(f"{col_name_list[v[3] - 1]}{v[0]}:{col_name_list[v[3] - 1]}{v[1]}")
merge_top_left_cell = ws[f"{col_name_list[v[3] - 1]}{v[0]}"]
merge_top_left_cell.value = k
merge_top_left_cell.alignment = Alignment(horizontal="center", vertical="center")
wb.save('data.xlsx')
- 读取数据
- 将数据整理成需要的数据结构
- 通过相应的数据结构进行合并
- 保存数据
对于列数的生成可通过如下方式自动生成：
def product(*args, repeat=1):
# product('ABCD', 'xy') --> Ax Ay Bx By Cx Cy Dx Dy
# product(range(2), repeat=3) --> 000 001 010 011 100 101 110 111
all_result = []
if repeat == 1:
result = [[item] for item in args]
all_result.extend(result)
if repeat > 1:
multi_pools = [tuple(pool) for pool in args] * repeat
result = [[]]
for pool in multi_pools:
result = [x + [y] for x in result for y in pool]
all_result.extend(result)
for prod in all_result:
yield "".join(prod)
def generate_col_name():
com_chr_str = ""
for i in range(65, 91):
c = chr(i)
com_chr_str += c
res = product(com_chr_str, repeat=2)
return res
if __name__ == '__main__':
res = generate_col_name()
for i in res:
print(i)
作者：iveBoy
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须在文章页面给出原文连接，否则保留追究法律责任的权利。
浙公网安备 33010602011771号