python 将内容写入表格 xlsx openpyxl使用笔记

点击查看官方文档

初步使用

创建工作表、工作簿， 写入、读取内容， 保存、读取工作表

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)]
效果如下：

值得注意的是，合并后的单元格显示的是左上角单元格的内容，合并后的单元格取消合并后边框样式也没有了

颜色

常用颜色输入有三种

1. indexed color 系统中将下面的颜色按索引，可以直接用索引得到对应颜色 索引值范围 [0, 63]
   
2. theme color 工作簿中对应的主题颜色如下，从左到右10种颜色，主题 [0, 9]
   每种主题颜色还有 浅色比例、深色比例 再划分几种颜色，openpyxl 中如果设置主题颜色，可以设置 tint 参数，值范围为 [-1, 1]
   tint > 0 则为浅色比例，tint < 0 则为深色比例，见代码示例
   
3. 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 或更简单，这里只总结了一些常用的写入功能，可以满足一般需求，有更多需求请参考 官方文档