点击查看官方文档
初步使用
创建工作表、工作簿, 写入、读取内容, 保存、读取工作表
from openpyxl import Workbook, load_workbook
wb = Workbook() # 创建工作表,该工作表中会附带一个工作簿
ws = wb.active # 获取当前的工作簿 默认索引 0
print("创建工作表获取默认的工作簿名称为:", ws.title) # 新建的工作表默认第一个 工作簿 名称为 Sheet
ws_1 = wb.create_sheet("MySheet") # 在最后增加一个工作簿 MySheet
ws_2 = wb.create_sheet(title='MySheet_1', index=0) # 在第一个位置插入工作簿 MySheet_1
ws_3 = wb.create_sheet('Animal', -1) # 在倒数第二位置插入工作簿 Animal
ws_2.title = 'fruit' # 将 MySheet_1 工作簿的名称改为 fruit
ws_2.sheet_properties.tabColor = "1072BA" # 设置工作簿标签的颜色
ws_4 = wb['fruit'] # 创建工作簿后可以直接用工作簿的名字作为 key 获取工作簿
print('直接用工作簿的名称获取工作簿', ws_4 == ws_2)
print("该工作表所有工作簿的名称", wb.sheetnames)
for sheet in wb: # 遍历工作表中的工作簿
print("工作簿名称", sheet.title)
# 为单元格赋值
ws_2['A1'] = "苹果" # 直接为 A1 单元格赋值
ws_2.cell(row=2, column=1, value='橘子') # 为第 2 行, 第 1 列单元格赋值为 橘子
## 当工作簿创建后不包含单元格,只有在第一次访问单元格时单元格创建
ws_2.append(["香蕉", "芒果", "菠萝", "哈密瓜"]) # 逐行添加
# 批量访问单元格
cell_range = ws_2['A1':'C2'] # 用切片访问一系列单元格
colC = ws_2['C'] # 获取 C列
col_range = ws_2['C:D'] # 获取 C,D 列
row10 = ws_2[10] # 获取第10 行单元格
row_range = ws_2[5:10] # 获取第5到10行单元格
print("批量访问:", row_range)
for row in ws_2.iter_rows(min_row=1, max_col=3, max_row=2): # 该方法在只读模式禁用
for cell in row:
print("行迭代获取", cell)
for col in ws_2.iter_cols(min_row=1, max_col=3, max_row=2): # 该方法在只读模式禁用
for cell in col:
print("列迭代获取", cell)
print(f"获取工作簿 {ws_2.title} 所有行:", tuple(ws_2.rows)) # 获取所有行
print(f"获取工作簿 {ws_2.title} 所有列:", tuple(ws_2.columns)) # 获取所有列
# 将工作表保存成流文件
from tempfile import NamedTemporaryFile
# 官方给的例子中没有delete 参数, 实测window系统下需要将 delete设置为 False
with NamedTemporaryFile(delete=False) as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
# 也可以下面方法,更简洁
import io
file = io.BytesIO()
wb.save(file)
file.seek(0)
stream = file.read()
# 保存文件到本地
wb.save('fruit.xlsx')
# 打开本地文件
from openpyxl.cell.cell import Cell
wb = load_workbook('fruit.xlsx')
ws = wb['fruit']
for row in ws.rows:
for cell in row: # type: Cell
print("单元格" + cell.coordinate + ":", cell.value)
上述代码运行打印如下:
创建工作表获取默认的工作簿名称为: Sheet
直接用工作簿的名称获取工作簿 True
该工作表所有工作簿的名称 [‘fruit’, ‘Sheet’, ‘Animal’, ‘MySheet’]
工作簿名称 fruit
工作簿名称 Sheet
工作簿名称 Animal
工作簿名称 MySheet
批量访问: ((<Cell ‘fruit’.A5>, <Cell ‘fruit’.B5>, <Cell ‘fruit’.C5>, <Cell ‘fruit’.D5>), (<Cell ‘fruit’.A6>, <Cell ‘fruit’.B6>, <Cell ‘fruit’.C6>, <Cell ‘fruit’.D6>), (<Cell ‘fruit’.A7>, <Cell ‘fruit’.B7>, <Cell ‘fruit’.C7>, <Cell ‘fruit’.D7>), (<Cell ‘fruit’.A8>, <Cell ‘fruit’.B8>, <Cell ‘fruit’.C8>, <Cell ‘fruit’.D8>), (<Cell ‘fruit’.A9>, <Cell ‘fruit’.B9>, <Cell ‘fruit’.C9>, <Cell ‘fruit’.D9>), (<Cell ‘fruit’.A10>, <Cell ‘fruit’.B10>, <Cell ‘fruit’.C10>, <Cell ‘fruit’.D10>))
行迭代获取 <Cell ‘fruit’.A1>
行迭代获取 <Cell ‘fruit’.B1>
行迭代获取 <Cell ‘fruit’.C1>
行迭代获取 <Cell ‘fruit’.A2>
行迭代获取 <Cell ‘fruit’.B2>
行迭代获取 <Cell ‘fruit’.C2>
列迭代获取 <Cell ‘fruit’.A1>
列迭代获取 <Cell ‘fruit’.A2>
列迭代获取 <Cell ‘fruit’.B1>
列迭代获取 <Cell ‘fruit’.B2>
列迭代获取 <Cell ‘fruit’.C1>
列迭代获取 <Cell ‘fruit’.C2>
获取工作簿 fruit 所有行: ((<Cell ‘fruit’.A1>, <Cell ‘fruit’.B1>, <Cell ‘fruit’.C1>, <Cell ‘fruit’.D1>), (<Cell ‘fruit’.A2>, <Cell ‘fruit’.B2>, <Cell ‘fruit’.C2>, <Cell ‘fruit’.D2>), (<Cell ‘fruit’.A3>, <Cell ‘fruit’.B3>, <Cell ‘fruit’.C3>, <Cell ‘fruit’.D3>), (<Cell ‘fruit’.A4>, <Cell ‘fruit’.B4>, <Cell ‘fruit’.C4>, <Cell ‘fruit’.D4>), (<Cell ‘fruit’.A5>, <Cell ‘fruit’.B5>, <Cell ‘fruit’.C5>, <Cell ‘fruit’.D5>), (<Cell ‘fruit’.A6>, <Cell ‘fruit’.B6>, <Cell ‘fruit’.C6>, <Cell ‘fruit’.D6>), (<Cell ‘fruit’.A7>, <Cell ‘fruit’.B7>, <Cell ‘fruit’.C7>, <Cell ‘fruit’.D7>), (<Cell ‘fruit’.A8>, <Cell ‘fruit’.B8>, <Cell ‘fruit’.C8>, <Cell ‘fruit’.D8>), (<Cell ‘fruit’.A9>, <Cell ‘fruit’.B9>, <Cell ‘fruit’.C9>, <Cell ‘fruit’.D9>), (<Cell ‘fruit’.A10>, <Cell ‘fruit’.B10>, <Cell ‘fruit’.C10>, <Cell ‘fruit’.D10>))
获取工作簿 fruit 所有列: ((<Cell ‘fruit’.A1>, <Cell ‘fruit’.A2>, <Cell ‘fruit’.A3>, <Cell ‘fruit’.A4>, <Cell ‘fruit’.A5>, <Cell ‘fruit’.A6>, <Cell ‘fruit’.A7>, <Cell ‘fruit’.A8>, <Cell ‘fruit’.A9>, <Cell ‘fruit’.A10>), (<Cell ‘fruit’.B1>, <Cell ‘fruit’.B2>, <Cell ‘fruit’.B3>, <Cell ‘fruit’.B4>, <Cell ‘fruit’.B5>, <Cell ‘fruit’.B6>, <Cell ‘fruit’.B7>, <Cell ‘fruit’.B8>, <Cell ‘fruit’.B9>, <Cell ‘fruit’.B10>), (<Cell ‘fruit’.C1>, <Cell ‘fruit’.C2>, <Cell ‘fruit’.C3>, <Cell ‘fruit’.C4>, <Cell ‘fruit’.C5>, <Cell ‘fruit’.C6>, <Cell ‘fruit’.C7>, <Cell ‘fruit’.C8>, <Cell ‘fruit’.C9>, <Cell ‘fruit’.C10>), (<Cell ‘fruit’.D1>, <Cell ‘fruit’.D2>, <Cell ‘fruit’.D3>, <Cell ‘fruit’.D4>, <Cell ‘fruit’.D5>, <Cell ‘fruit’.D6>, <Cell ‘fruit’.D7>, <Cell ‘fruit’.D8>, <Cell ‘fruit’.D9>, <Cell ‘fruit’.D10>))
单元格A1: 苹果
单元格B1: None
单元格C1: None
单元格D1: None
单元格A2: 橘子
单元格B2: None
单元格C2: None
单元格D2: None
单元格A3: 香蕉
单元格B3: 芒果
单元格C3: 菠萝
单元格D3: 哈密瓜
用 Excel 打开保存的工作表,如下:
样式
单元格边框
设置边框首先要定义边Side
,再定义框Border
,之后才能给单元格添加边框样式
边的线可以指定线型和颜色,框的各个边可以定义成不同的样式,常用的上下左右四边
from openpyxl import Workbook
from openpyxl.styles import Border, Side, colors
from openpyxl.utils import get_column_letter
styles = ('dashDot', 'dashDotDot', 'dashed', 'dotted', 'double', 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin')
wb = Workbook()
ws = wb.active
ws.title = 'border'
for i, style in enumerate(styles):
thin = Side(style=style, color=colors.BLACK) # 定义边
border = Border(
left=thin, # 左边框
right=thin, # 右边框
top=thin, # 上边框
bottom=thin # 下边框
)
col = get_column_letter((i + 1) * 2) # 获取列字母
cell = ws[f'{col}2']
cell.value = style
cell.border = border # 为单元格设置边框
wb.save("border.xlsx")
边框各种线条样式如下
字体样式
字体样式主要包括 字体、颜色、大小、加粗、倾斜、下划线、删除线、垂直对齐等
字体类如下
from openpyxl.styles import Font
font = Font(
name='黑体', # 字体
size=11, # 大小
bold=False, # 加粗
italic=False, # 倾斜
vertAlign=None, # 垂直对齐 (‘subscript’, ‘baseline’, ‘superscript’)
underline='none', # 下滑线 ('singleAccounting', 'single', 'double', 'doubleAccounting')
strike=False, # 删除线
color='FF000000' # 颜色
)
# 为单元格添加字体样式
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.title = 'font'
cell = ws.cell(1, 1)
cell.value = '字体测试'
cell.font = font # 为单元格添加字体样式
wb.save('font.xlsx')
各种参数样式如下
对齐方式
水平对齐方式有以下几种:
-
general
常规 -
left
靠左(缩进) -
center
居中 -
right
靠右(缩进) -
fill
填充 -
justify
两端对齐 -
centerContinuous
跨列居中 -
distributed
分散对齐(缩进)
垂直对齐方式有以下几种:
-
top
靠上 -
center
居中 -
bottom
靠下 -
justify
两端对齐 -
distributed
分散对齐
有的对齐方式不常用,具体可以在表格中设置单元格样式自己来体会区别
对齐 Alignment
类的属性如下,可以设置水平对齐、垂直对齐、自动换行、缩进等常用设置
alignment = Alignment(
horizontal='general', # 水平对齐
vertical='bottom', # 垂直对齐
text_rotation=0, # 文本旋转
wrap_text=False, # 自动换行
shrink_to_fit=False, # 缩小字体填充
indent=0 # 缩进
)
示例代码
from openpyxl.workbook import Workbook
from openpyxl.styles import Alignment
# 对齐
horizontal_alignments = (
"general", # 常规
"left", # 靠左(缩进)
"center", # 居中
"right", # 靠右(缩进)
"fill", # 填充
"justify", # 两端对齐
"centerContinuous", # 跨列居中
"distributed", # 分散对齐(缩进)
)
# 垂直方向
vertical_aligments = (
"top", # 靠上
"center", # 居中
"bottom", # 靠下
"justify", # 两端对齐
"distributed", # 分散对齐
)
wb = Workbook()
ws = wb.active
ws.title = 'alignment'
r = 1
for i, horizontal_alignment in enumerate(horizontal_alignments):
ws.cell(row=i + 1, column=1).value = 'horizontal_' + horizontal_alignment
alignment = Alignment(horizontal=horizontal_alignment)
ws.cell(row=i + 1, column=2).value = "这句话是用来测试水平对齐的。"
ws.cell(row=i + 1, column=3).value = "This sentence is used to test horizontal alignment."
ws.cell(row=i + 1, column=2).alignment = alignment # 为单元格设置对齐
ws.cell(row=i + 1, column=3).alignment = alignment
r += 1
for i, vertical_aligment in enumerate(vertical_aligments):
ws.cell(row=r + 1, column=i + 1).value = 'vertical_' + vertical_aligment
alignment = Alignment(vertical=vertical_aligment)
ws.cell(row=r + 2, column=i + 1).value = "这句话是用来测试垂直对齐的。"
ws.cell(row=r + 3, column=i + 1).value = "This sentence is used to test vertical alignment."
ws.cell(row=r + 2, column=i + 1).alignment = alignment # 为单元格设置对齐
ws.cell(row=r + 3, column=i + 1).alignment = alignment
wb.save('alignment.xlsx')
保存文件 alignment.xlsx
用 wps
打开调整间距后如下:
还是大致可以看出各种对齐方式的区别的
单元格填充
最长用到的是图案填充 PatternFill
, 图案样式常用的是 solid
可以设置图案样式(fill_type),前景色(start_color),背景色(end_color)
平常使用中设置图案为 solid ,再设置前景色即可,这里注意前景色是指填充的颜色,并不是字的颜色,前景色背景色是相对图案类型来说的,有的图案需要背景色
上代码
from openpyxl import Workbook
from openpyxl.styles import PatternFill, fills
from openpyxl.utils import get_column_letter
wb = Workbook()
ws = wb.active
ws.title = 'fill'
for i, fill_type in enumerate(fills.fills):
fill = PatternFill(
fill_type=fill_type,
start_color='FFFF0000', # 设置填充色(前景色)
end_color='FFFFFF00' # 设置背景色, 这里设置为黄色,注意看区别
)
col = get_column_letter(i + 1) # 获取列字母
ws[f'{col}2'].value = fill_type
cell = ws[f'{col}3']
cell.fill = fill # 填充单元格
for i, fill_type in enumerate(fills.fills):
fill = PatternFill(
fill_type=fill_type,
start_color='FFFF0000', # 设置填充色(前景色)
end_color='FF00FF00' # 设置背景色, 这里设置为绿色,注意看区别
)
col = get_column_letter(i + 1) # 获取列字母
ws[f'{col}5'].value = fill_type
cell = ws[f'{col}6']
cell.fill = fill # 填充单元格
wb.save("fill.xlsx")
图片结果中前景色都是红色,上面背景色为黄色,下面背景色为绿色,可以看出各种图案的区别
设置列宽和行高
我们可以为列设置列宽,为行设置行高,这里要注意的是行高的单位是磅,列宽的单位是字符
openpyxl 列宽上也有 bestFit(auto_size),单测试下来达不到想要效果
from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws['B2'].value = '设置列宽行高'
ws.row_dimensions[2].height = 40 # 40磅
ws.column_dimensions['B'].width = 20 # 20个字符
ws['C2'].value = '测试设置自动列宽'
ws.column_dimensions['C'].bestFit = True # 自动列宽 测试有点效果,但是效果不佳
wb.save("row_col.xlsx")
效果如下
合并(取消合并)
直接上代码
from openpyxl import Workbook
from openpyxl.styles import Border, Side
from openpyxl.utils import column_index_from_string
wb = Workbook()
ws = wb.active
for i in range(10):
ws.append([j for j in range(10, 20)])
thin = Side(style='thin')
border = Border(thin, thin, thin, thin)
# 为单元格添加内容
for i in range(ws.max_row):
for j in range(ws.max_column):
ws.cell(i + 1, j + 1).border = border
ws.merge_cells("A2:A5") # 合并单元格
ws.merge_cells("B4:F4") # 合并单元格
ws.merge_cells("G6:I8") # 合并单元格
start_col_index = column_index_from_string('G')
end_col_index = column_index_from_string('I') # 由列字母得到列号
ws.merge_cells(start_row=3, start_column=start_col_index, end_row=4, end_column=end_col_index) # 合并单元格
print(ws.merged_cells)
print(ws.merged_cells.ranges)
merged_cells_1 = ws.merged_cells.ranges[2]
print(merged_cells_1.min_col, merged_cells_1.min_row, merged_cells_1.max_col, merged_cells_1.max_row)
print("bounds: ", merged_cells_1.bounds) # 合并单元格边界
print("coord: ", merged_cells_1.coord) # 合并单元格的坐标
print("merged_rows", list(merged_cells_1.rows))
print("merged_cells", list(merged_cells_1.cells))
ws.unmerge_cells("G6:I8") # 取消合并
wb.save("merge.xlsx")
代码执行如下:
A2:A5 B4:F4 G6:I8 G3:I4
[<MergedCellRange A2:A5>,<MergedCellRange B4:F4>, <MergedCellRange G6:I8>, <MergedCellRange G3:I4>]
7 6 9 8
bounds: (7, 6, 9, 8)
coord: G6:I8
merged_rows [[(6, 7), (6, 8), (6, 9)], [(7, 7), (7, 8), (7, 9)], [(8, 7), (8, 8), (8, 9)]]
merged_cells [(6, 7), (6, 8), (6, 9), (7, 7), (7, 8), (7, 9), (8, 7), (8, 8), (8, 9)]
效果如下:
值得注意的是,合并后的单元格显示的是左上角单元格的内容,合并后的单元格取消合并后边框样式也没有了
颜色
常用颜色输入有三种
- indexed color 系统中将下面的颜色按索引,可以直接用索引得到对应颜色 索引值范围 [0, 63]
- theme color 工作簿中对应的主题颜色如下,从左到右10种颜色,主题 [0, 9]
每种主题颜色还有 浅色比例、深色比例 再划分几种颜色,openpyxl 中如果设置主题颜色,可以设置 tint 参数,值范围为 [-1, 1]
tint > 0 则为浅色比例,tint < 0 则为深色比例,见代码示例 - aRGB color 直接输入颜色的 aRGB 即可
上代码
from openpyxl import Workbook
from openpyxl.styles import Color, Font
wb = Workbook()
ws = wb.active
ws.title = 'color'
c_12 = Color(indexed=12) # 索引颜色 0 - 63
font = Font(sz=12, bold=True, color=c_12)
ws.cell(2, 2, 'indexed_12')
ws.cell(2, 2).font = font
c_t9 = Color(theme=9, tint=-0.25) # 主题颜色 0 - 9, tint范围 -1.0 - 1.0
font = Font(sz=12, bold=True, color=c_t9)
ws.cell(2, 4, 'theme_9_tint=-0.25')
ws.cell(2, 4).font = font
c_t9_ = Color(theme=9, tint=0.6) # 主题颜色 0 - 9, tint范围 -1.0 - 1.0
font = Font(sz=12, bold=True, color=c_t9_)
ws.cell(3, 4, 'theme_9_tint=0.6')
ws.cell(3, 4).font = font
c_rgb = Color(rgb="00FF0000") # argb颜色
font = Font(sz=12, bold=True, color=c_rgb)
ws.cell(2, 6, 'rgb_00FF0000')
ws.cell(2, 6).font = font
wb.save('color.xlsx')
颜色样式效果如下
命名样式
当我们想对大量单元格应用样式时,我们可以定义一个命名样式,将希望用到的样式如字体、大小、对齐、颜色、边框等都添加到命名样式中,这样我们就可以直接对单元格应用样式
上代码
from openpyxl import Workbook
from openpyxl.styles import NamedStyle, Font, Side, Border, Color, PatternFill, Alignment
highlight = NamedStyle(name="highlight") # 创建命名样式
highlight.font = Font(bold=True, size=20, color="FF0000") # 为命名样式设置字体样式
bd = Side(style='thick', color="0000FF")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd) # 为命名样式设置边框样式
alignment = Alignment(horizontal="center", vertical="center")
highlight.alignment = alignment
fill = PatternFill('solid', Color(indexed=50))
highlight.fill = fill
thin = Side(style='thin', color="000000")
normal = NamedStyle(name='normal')
normal.border = Border(thin, thin, thin, thin)
wb = Workbook()
wb.add_named_style(normal) # 为工作表注册命名样式,注册后可以直接用样式的名字,也可直接为一个单元格设置,之后变自动注册
ws = wb.active
ws.title = 'namedStyle'
for i in range(10):
for j in range(10):
cell = ws.cell(i + 1, j + 1, i * j)
if (i * j + 1) % 3 == 0:
cell.style = highlight
else:
cell.style = "normal" # 注册后的命名样式可以直接用名字
ws["B12"].value = "测试"
ws["B12"].style = "highlight"
wb.save('namedStyle.xlsx')
效果如下:
内置样式
WPS中单元格样式如下,openpyxl 中的内置样式基本包含,可以直接使用
上代码:
from openpyxl import Workbook
from openpyxl.styles import builtins
print("内置样式名称列表:", list(builtins.styles.keys()))
wb = Workbook()
ws = wb.active
ws.cell(1, 1, '好,差和适中')
for i, style_name in enumerate(['Normal', "Good", "Bad", "Neutral"]):
ws.cell(2, i + 1, style_name).style = style_name # 设置内置样式
ws.cell(3, 1, '数据和模型')
for i, style_name in enumerate(['Hyperlink', 'Followed Hyperlink', 'Note', 'Warning Text']):
ws.cell(4, i + 1, style_name).style = style_name # 设置内置样式
for i, style_name in enumerate(['Input', 'Output', 'Calculation', 'Check Cell']):
ws.cell(5, i + 1, style_name).style = style_name # 设置内置样式
for i, style_name in enumerate(['Linked Cell', 'Explanatory Text']):
ws.cell(6, i + 1, style_name).style = style_name # 设置内置样式
ws.cell(7, 1, '标题')
for i, style_name in enumerate(['Title', 'Headline 1', 'Headline 2', 'Headline 3']):
ws.cell(8, i + 1, style_name).style = style_name # 设置内置样式
for i, style_name in enumerate(['Headline 4', 'Total']):
ws.cell(9, i + 1, style_name).style = style_name # 设置内置样式
ws.cell(10, 1, '主题单元格样式')
accents = [
["Accent1", "20 % - Accent1", "40 % - Accent1", "60 % - Accent1"],
["Accent2", "20 % - Accent2", "40 % - Accent2", "60 % - Accent2"],
["Accent3", "20 % - Accent3", "40 % - Accent3", "60 % - Accent3"],
["Accent4", "20 % - Accent4", "40 % - Accent4", "60 % - Accent4"],
["Accent5", "20 % - Accent5", "40 % - Accent5", "60 % - Accent5"],
["Accent6", "20 % - Accent6", "40 % - Accent6", "60 % - Accent6"]
]
for i, accent in enumerate(accents):
for j, style_name in enumerate(accent):
ws.cell(i + 11, j + 1, style_name).style = style_name # 设置内置样式
ws.cell(17, 1, '数字格式')
for i, style_name in enumerate(['Comma', 'Currency', 'Percent', 'Comma [0]']):
if style_name == 'Percent':
ws.cell(18, i + 1, 0.69).style = style_name # 设置内置样式
else:
ws.cell(18, i + 1, 5020.59).style = style_name # 设置内置样式
ws.cell(19, 1, 5020.59).style = 'Currency [0]' # 设置内置样式
ws.cell(21, 1, 'Pandas').style = 'Pandas' # 设置内置样式
wb.save('buildinstyle.xlsx')
内置样式名称列表: [‘Normal’, ‘Comma’, ‘Currency’, ‘Percent’, ‘Comma [0]’, ‘Currency [0]’, ‘Hyperlink’, ‘Followed Hyperlink’, ‘Note’, ‘Warning Text’, ‘Title’, ‘Headline 1’, ‘Headline 2’, ‘Headline 3’, ‘Headline 4’, ‘Input’, ‘Output’, ‘Calculation’, ‘Check Cell’, ‘Linked Cell’, ‘Total’, ‘Good’, ‘Bad’, ‘Neutral’, ‘Accent1’, ‘20 % - Accent1’, ‘40 % - Accent1’, ‘60 % - Accent1’, ‘Accent2’, ‘20 % - Accent2’, ‘40 % - Accent2’, ‘60 % - Accent2’, ‘Accent3’, ‘20 % - Accent3’, ‘40 % - Accent3’, ‘60 % - Accent3’, ‘Accent4’, ‘20 % - Accent4’, ‘40 % - Accent4’, ‘60 % - Accent4’, ‘Accent5’, ‘20 % - Accent5’, ‘40 % - Accent5’, ‘60 % - Accent5’, ‘Accent6’, ‘20 % - Accent6’, ‘40 % - Accent6’, ‘60 % - Accent6’, ‘Explanatory Text’, ‘Pandas’]
显示如下:
超级表
如果将表格变为超级表,可以直接为超级表设置预设样式
源码中的超级表样式支持如下
TABLESTYLES = tuple(
["TableStyleMedium{0}".format(i) for i in range(1, 29)]
+ ["TableStyleLight{0}".format(i) for i in range(1, 22)]
+ ["TableStyleDark{0}".format(i) for i in range(1, 12)]
)
PIVOTSTYLES = tuple(
["PivotStyleMedium{0}".format(i) for i in range(1, 29)]
+ ["PivotStyleLight{0}".format(i) for i in range(1, 29)]
+ ["PivotStyleDark{0}".format(i) for i in range(1, 29)]
)
超级表样式中可以设置
上代码:
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5") # 超级表
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style # 为超级表添加预设样式
ws.add_table(tab)
wb.save("table.xlsx")
效果如下:
这里要注意的是,并不是所有的表都适合超级表,所以想用超级表样式的要了解下超级表
Pandas 装换
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
title = ["Fruit", "2011", "2012", "2013", "2014"]
df = pd.DataFrame(data, columns=title)
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=False): # 将DataFrame 转为工作簿
ws.append(r)
for row in ws.rows:
for cell in row:
cell.style = 'Pandas'
wb.save("pandas_openpyxl.xlsx")
wb = load_workbook("pandas_openpyxl.xlsx")
ws = wb.active
df = pd.DataFrame(ws.values) # 将工作簿转为DataFrame
print(df)
# 一下为带索引和列明的DataFrame
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 = pd.DataFrame(data, index=idx, columns=cols)
print(df)
打印结果如下:
可以明显看出上面的 DataFrame 和下面的 DataFrame 有明显区别,下面的把索引和列名提取出来了保存工作簿如下
最后,用 openpyxl
通常是为了批量写一些内容到表格,稍微复杂的操作直接用 WPS
或 office
或更简单,这里只总结了一些常用的写入功能,可以满足一般需求,有更多需求请参考 官方文档