创建工作簿
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
