創(chuàng)建工作簿
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
創(chuàng)建表格
# 在最后插入表格
ws1 = wb.create_sheet('MySheet')
# 在指定位置插入表格
ws2 = wb.create_sheet("MySheet", 1) # 1是重左往右第二張表,根據(jù)列表的索引位置
訪問
# 單一單元格訪問
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: 刪除是注意要拆解合并單元格
移動范圍數(shù)據(jù)
可以理解為以單元格A1為原點,箭頭向右為y軸,箭頭向下為x軸,
參數(shù)rows代表x軸的方向距離,參數(shù)cols代表y軸上的方向距離
ws.move_range('C1:D2', rows=3, cols=-2)
# 將c1:d2的數(shù)內容向下移動3個單元格,向左移動2個單元格
字母與數(shù)字的相互轉換
from openpyxl.utils improt get_column_letter, column_index_from_string
# 數(shù)字轉字母
print(get_column_letter(2)) #B
# 字母轉數(shù)字
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 ], # 這里少一個數(shù)據(jù)
[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等等參數(shù)
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
合并,拆分單元格
# 合并單元格， 往左上角寫入數(shù)據(jù)即可
sheet.merge_cells('B1:G1') # 合并一行中的幾個單元格
sheet.merge_cells('A1:C3') # 合并一個矩形區(qū)域中的單元格
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
