点击查看官方文档
初步使用
创建工作表、工作簿， 写入、读取内容， 保存、读取工作表
from openpyxl import Workbook, load_workbookwb = Workbook() # 创建工作表，该工作表中会附带一个工作簿ws = wb.active # 获取当前的工作簿 默认索引 0print("创建工作表获取默认的工作簿名称为：", ws.title) # 新建的工作表默认第一个 工作簿 名称为 Sheetws_1 = wb.create_sheet("MySheet") # 在最后增加一个工作簿 MySheetws_2 = wb.create_sheet(title='MySheet_1', index=0) # 在第一个位置插入工作簿 MySheet_1ws_3 = wb.create_sheet('Animal', -1) # 在倒数第二位置插入工作簿 Animalws_2.title = 'fruit' # 将 MySheet_1 工作簿的名称改为 fruitws_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设置为 Falsewith NamedTemporaryFile(delete=False) as tmp:wb.save(tmp.name)tmp.seek(0)stream = tmp.read()# 也可以下面方法，更简洁import iofile = io.BytesIO()wb.save(file)file.seek(0)stream = file.read()# 保存文件到本地wb.save('fruit.xlsx')# 打开本地文件from openpyxl.cell.cell import Cellwb = load_workbook('fruit.xlsx')ws = wb['fruit']for row in ws.rows:for cell in row: # type: Cellprint("单元格" + 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 Workbookfrom openpyxl.styles import Border, Side, colorsfrom openpyxl.utils import get_column_letterstyles = ('dashDot', 'dashDotDot', 'dashed', 'dotted', 'double', 'hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin')wb = Workbook()ws = wb.activews.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 = stylecell.border = border # 为单元格设置边框wb.save("border.xlsx")
边框各种线条样式如下
字体样式
字体样式主要包括字体、颜色、大小、加粗、倾斜、下划线、删除线、垂直对齐等
字体类如下
from openpyxl.styles import Fontfont = 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 Workbookwb = Workbook()ws = wb.activews.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 Workbookfrom 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.activews.title = 'alignment'r = 1for i, horizontal_alignment in enumerate(horizontal_alignments):ws.cell(row=i + 1, column=1).value = 'horizontal_' + horizontal_alignmentalignment = 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 = alignmentr += 1for i, vertical_aligment in enumerate(vertical_aligments):ws.cell(row=r + 1, column=i + 1).value = 'vertical_' + vertical_aligmentalignment = 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 = alignmentwb.save('alignment.xlsx')
保存文件alignment.xlsx用wps打开调整间距后如下：
还是大致可以看出各种对齐方式的区别的
单元格填充
最长用到的是图案填充PatternFill, 图案样式常用的是solid
可以设置图案样式(fill_type)，前景色(start_color)，背景色(end_color)
平常使用中设置图案为 solid ，再设置前景色即可，这里注意前景色是指填充的颜色，并不是字的颜色，前景色背景色是相对图案类型来说的，有的图案需要背景色
上代码
from openpyxl import Workbookfrom openpyxl.styles import PatternFill, fillsfrom openpyxl.utils import get_column_letterwb = Workbook()ws = wb.activews.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_typecell = 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_typecell = ws[f'{col}6']cell.fill = fill # 填充单元格wb.save("fill.xlsx")
图片结果中前景色都是红色，上面背景色为黄色，下面背景色为绿色，可以看出各种图案的区别
设置列宽和行高
我们可以为列设置列宽，为行设置行高，这里要注意的是行高的单位是磅，列宽的单位是字符
openpyxl 列宽上也有 bestFit(auto_size)，单测试下来达不到想要效果
from openpyxl import Workbookwb = Workbook()ws = wb.activews['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 Workbookfrom openpyxl.styles import Border, Sidefrom openpyxl.utils import column_index_from_stringwb = Workbook()ws = wb.activefor 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 = borderws.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 Workbookfrom openpyxl.styles import Color, Fontwb = Workbook()ws = wb.activews.title = 'color'c_12 = Color(indexed=12) # 索引颜色 0 - 63font = Font(sz=12, bold=True, color=c_12)ws.cell(2, 2, 'indexed_12')ws.cell(2, 2).font = fontc_t9 = Color(theme=9, tint=-0.25) # 主题颜色 0 - 9, tint范围 -1.0 - 1.0font = Font(sz=12, bold=True, color=c_t9)ws.cell(2, 4, 'theme_9_tint=-0.25')ws.cell(2, 4).font = fontc_t9_ = Color(theme=9, tint=0.6) # 主题颜色 0 - 9, tint范围 -1.0 - 1.0font = Font(sz=12, bold=True, color=c_t9_)ws.cell(3, 4, 'theme_9_tint=0.6')ws.cell(3, 4).font = fontc_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 = fontwb.save('color.xlsx')
颜色样式效果如下
命名样式
当我们想对大量单元格应用样式时，我们可以定义一个命名样式，将希望用到的样式如字体、大小、对齐、颜色、边框等都添加到命名样式中，这样我们就可以直接对单元格应用样式
上代码
from openpyxl import Workbookfrom openpyxl.styles import NamedStyle, Font, Side, Border, Color, PatternFill, Alignmenthighlight = 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 = alignmentfill = PatternFill('solid', Color(indexed=50))highlight.fill = fillthin = 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.activews.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 = highlightelse:cell.style = "normal" # 注册后的命名样式可以直接用名字ws["B12"].value = "测试"ws["B12"].style = "highlight"wb.save('namedStyle.xlsx')
效果如下：
内置样式
WPS中单元格样式如下，openpyxl 中的内置样式基本包含，可以直接使用
上代码：
from openpyxl import Workbookfrom openpyxl.styles import builtinsprint("内置样式名称列表：", list(builtins.styles.keys()))wb = Workbook()ws = wb.activews.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 Workbookfrom openpyxl.worksheet.table import Table, TableStyleInfowb = Workbook()ws = wb.activedata = [['Apples', 10000, 5000, 8000, 6000],['Pears', 2000, 3000, 4000, 5000],['Bananas', 6000, 6000, 6500, 6000],['Oranges', 500, 300, 200, 700],]ws.append(["Fruit", "", "", "", ""])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_workbookfrom openpyxl.utils.dataframe import dataframe_to_rowsimport pandas as pddata = [['Apples', 10000, 5000, 8000, 6000],['Pears', 2000, 3000, 4000, 5000],['Bananas', 6000, 6000, 6500, 6000],['Oranges', 500, 300, 200, 700],]title = ["Fruit", "", "", "", ""]df = pd.DataFrame(data, columns=title)wb = Workbook()ws = wb.activefor 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.activedf = pd.DataFrame(ws.values) # 将工作簿转为DataFrameprint(df)# 一下为带索引和列明的DataFramefrom itertools import islicedata = ws.valuescols = 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或更简单，这里只总结了一些常用的写入功能，可以满足一般需求，有更多需求请参考 官方文档
