创建工作簿

from openpyxl as xl
# 实例化
wb = xl.WorkBook()
# 激活worksheet
ws = wb.active

读取

from openpyxl as xl
wb = xl.load_workbook('file_path')
# sheet的列表,工作簿中所有的工作表,返回**列表**
list_ws = wb.sheetnames
# 读取表格
ws = wb['Sheet']
# 获取表格最大行
row = ws.max_row
# 获取表格最大列
column = ws.max_column

创建表格

# 在最后插入表格
ws1 = wb.create_sheet('MySheet')
# 在指定位置插入表格
ws2 = wb.create_sheet("MySheet", 1) # 1是重左往右第二张表,根据列表的索引位置

访问

# 单一单元格访问
c = ws['A1'].value
d = ws.cell(row=4, column=2).value

# 多单元访问
cs = ws['A1': 'C2']
for i in cs:
    print(i.value)

cs1 = ws['A']  #访问列
for i in cs1:
    print(i.value)

cs2 = ws[10]  #访问行,范围是1到max_column
for i in cs2:
    print(i.value)

# 指定行列
for i in ws.iter_cols(min_row=1, max_col=3, max_row=3, min_col=2):
    for j in i:
        print(j)
--------------------------------------------
<Cell 'Sheet'.B1>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.B3>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.C2>
<Cell 'Sheet'.C3>
---------------------------------------------

for i in ws.iter_rows(min_row=1, max_col=3, max_row=3, min_col=2):
    for j in i:
        print(j)
----------------------------------------------
<Cell 'Sheet'.B1>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.C2>
<Cell 'Sheet'.B3>
<Cell 'Sheet'.C3>
-----------------------------------------------

# 遍历所有
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>))
-------------------------------------------------

for i in ws.rows:
    for j in i:
        print(j)
-----------------------------------------------------
<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>
---------------------------------------------------

tuple(ws.columns)
------------------------------------------------
((<Cell 'Sheet'.A1>, <Cell 'Sheet'.A2>, <Cell 'Sheet'.A3>),
 (<Cell 'Sheet'.B1>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.B3>),
 (<Cell 'Sheet'.C1>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.C3>))
-------------------------------------------------

for i in ws.columns:
    for j in i:
        print(j)
--------------------------------------------------
<Cell 'Sheet'.A1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.A3>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.B3>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.C2>
<Cell 'Sheet'.C3>
--------------------------------------------------

for i in range(1, ws.max_row):
    for j in range(1, ws.max_column):
        print(ws.cell(i, j))
-------------------------------------------------
<Cell 'Sheet'.A1>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.B2>
--------------------------------------------------

行列的插入与删除

列插入

# 插入单列
ws.insert_cols(idx=2)  # 在第二列(B)的位置插入一空白列

# 插入多列
ws.insert_cols(idx=2, amount=5) # 在第二列(B)的位置插入五空白列

行插入

# 插入单行
ws.insert_rows(idx=2)  # 在第二行的位置插入一空白行 

# 插入多行
ws.insert_rows(idx=2, amount=4) # 在第二行的位置插入4空白行

删除

ws.delete_rows(idx=2, amount=4)  # 删除多行
ws.delete_cols(idx=2, amount=5) # 删除多列

ps: 删除是注意要拆解合并单元格

移动范围数据

可以理解为以单元格A1为原点,箭头向右为y轴,箭头向下为x轴,
参数rows代表x轴的方向距离,参数cols代表y轴上的方向距离

ws.move_range('C1:D2', rows=3, cols=-2)  
# 将c1:d2的数内容向下移动3个单元格,向左移动2个单元格

字母与数字的相互转换

from openpyxl.utils improt get_column_letter, column_index_from_string
# 数字转字母
print(get_column_letter(2))  #B
# 字母转数字
print(column_index_from_string('D')) #4

删除工作表

del wb['Sheet']

矩阵置换

rows = [
    ['Number', 'data1', 'data2'],
    [2, 40, 30],
    [3, 40, 25],
    [4, 50, 30],
    [5, 30, 10],
    [6, 25, 5],
    [7, 50, 10]]
list(zip(*rows))
------------------------------------------------------------------------
[('Number', 2, 3, 4, 5, 6, 7),
 ('data1', 40, 40, 50, 30, 25, 50),
 ('data2', 30, 25, 30, 10, 5, 10)]
------------------------------------------------------------------------

rows = [
  ['Number', 'data1', 'data2'],
  [2, 40  ], # 这里少一个数据
  [3, 40, 25],
  [4, 50, 30],
  [5, 30, 10],
  [6, 25, 5],
  [7, 50, 10],
]
list(zip(*(rows)))
-----------------------------------------------------------------------------------------------
[('Number', 2, 3, 4, 5, 6, 7), ('data1', 40, 40, 50, 30, 25, 50)]
-----------------------------------------------------------------------------------------------

设置单元格样式

字体

# 指定了等线(name)24号(size)，加粗斜体(blod)，字体颜色红色(color)。直接使用cell的font属性，将Font对象赋值给它
from openpyxl.styles import Font
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
sheet['A1'].font = bold_itatic_24_font

对齐方式

• 水平对齐: distributed, justify, center, left, fill, centerContinuous, right, general
• 垂直对齐: bottom, distributed, justify, center, top

# 直接使用cell的属性aligment，这里指定垂直(vertical)居中和水平(horizontal)居中。除了center，还可以使用right、left等等参数
sheet['B1'].alignment = Alignment(horizontal='center', vertical='center', text_rotation=45, wrap_text=True)
# text_rotation表示字体倾斜度,wrap_text表格是否自动换行

行高,列宽

# 第2行行高
sheet.row_dimensions[2].height = 40
# C列列宽
sheet.column_dimensions['C'].width = 30

设置边框样式

from openpyxl.styles import Side, Border
side = Side(style='thin', color='FF0000')
border = Border(left=side, right=side, top=side, bottom=side)
ws.cell(row=1, column=2).border = border

合并,拆分单元格

# 合并单元格， 往左上角写入数据即可
sheet.merge_cells('B1:G1')  # 合并一行中的几个单元格
sheet.merge_cells('A1:C3')  # 合并一个矩形区域中的单元格

sheet.unmerge_cells('A1:C3')  #拆分单元格

单元格背景色

from openpyxl.styles import PatternFill
fille = PatternFill('solid', fgColor='ffffff')  # 设置填充颜色为 橙色
sheet1.cell(row=2, column=8).fill = fille  # 序列

渐变单元格样式填充

from openpyxl.styles import GradientFill
gradient_fill = GradientFill(stop=('FFFFFF', '99ccff', '000000'))
ws.cell(row=1, column=2).fill = grandient_fill