Python自動化辦公 - 對Excel表格的操作（openpyxl的基本使用）

素材、筆記文檔下載下傳

1 安裝openpyxl

Windows使用者打開指令行輸入：

pip install openpyxl

Mac使用者打開終端/Terminal輸入：

pip3 install openpyxl

2 建立新的表格

2.1 建立一個工作簿

from openpyxl import Workbook # 引入openpyxl子產品，可以建立xls或者xlsx檔案

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
sheet.title = '表格1'
workbook.save(filename='寫入表格.xlsx')
           

2.2 對表格對象的一些操作

2.2.1 建立工作表

create_sheet()方法傳回一個新的表格對象

index和title參數，指定新工作表的索引及名稱

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
# 使用工作簿對象建立一個新的表格，表格名稱為表格2
workbook.create_sheet(index=None,title='表格2')
print(workbook.sheetnames)
           

index

：整數類型，設定新工作表索引，預設為None即放在最後，如果設定為0，則表格放置在最前。

title

：字元串類型，設定新工作表名稱，如果新工作表名稱已存在，新工作表名稱會自動變為title1。

2.2.2 删除工作表

remove()方法中接收一個表格對象而不是表格名稱的字元串。需要具象化表名。

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook['表格2']
workbook.remove(sheet)
# workbook.remove(workbook['表格2'])
print(workbook.sheetnames)
workbook.save(filename='寫入表格.xlsx')
           

2.2.3 複制工作表

workbook.copy_worksheet(sheet)

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet = workbook['表格1']
workbook.copy_worksheet(sheet)
print(workbook.sheetnames)
workbook.save(filename='寫入表格.xlsx')
           

2.2.4 修改工作表名

sheet.title = ‘工作表名’

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet = workbook['表格1 Copy']
sheet.title = '表格3'
print(workbook.sheetnames)
workbook.save(filename='寫入表格.xlsx')
           

3 讀取表格内容

3.1 擷取工作簿對象

openpyxl.load_workbook()方法可以接收多個參數進行讀取

from openpyxl import load_workbook

wb = openpyxl.load_workbook(filename='寫入表格.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True)
print(wb.sheetnames)
           

filename

：字元串類型，讀取Excel檔案的檔案路徑，可使用相對路徑或是絕對路徑。

read_only

：布爾類型，選擇隻讀模式或是讀寫模式，若想寫入公式及重新擷取結果，需要用将這個模式關閉。預設為False。

keep_vba

：布爾類型，保留vba内容(這并不意味着可以使用它)，預設為False。

data_only

：布爾類型，如果設定為True則包含公式的單元格，顯示最近計算結果或是None，如果設定為False 則單元格顯示公式，預設為False。

keep_links

：布爾類型，是否保留指向外部工作簿的連結。預設為True。

3.2 擷取工作表對象

3.2.1 擷取工作薄所有工作表名稱

load_workbook(filename=“表格檔案路徑”) #若是絕對路徑直接寫檔案名。

sheetnames屬性可以取得工作簿中所有表名，傳回為一個清單。

from openpyxl import load_workbook

fileName = "寫入表格.xlsx"
wb = load_workbook(filename = fileName)
print(wb.sheetnames)
           

3.2.2 選中需要操作的工作表

sheet = wb.active # 擷取工作簿中的目前活動表

sheet = wb[‘工作表名稱’] # 通過工作表名稱選中的工作表

sheet.title # 擷取活動表的表名稱

# 擷取上次關閉表格前激活的表格名稱
from openpyxl import load_workbook
fileName = "寫入表格.xlsx"
wb = load_workbook(filename = fileName)                                           
sheet = wb.active                                    
print(sheet.title)                                              
           

# 擷取特定表的表名稱
from openpyxl import load_workbook
fileName = "寫入表格.xlsx"
wb = load_workbook(filename = fileName)
sheet = wb['Sheet2'] 
print(sheet.title)        
           

3.2.3 擷取表格範圍

擷取表格：workbook[sheet名稱]

擷取表格的尺寸大小：sheet.dimensions(表格存在資料的大小)

單獨擷取最大行：sheet.max_row

單獨擷取最大列：sheet.max_column

from openpyxl import load_workbook   
                                     
workbook = load_workbook(filename="46
sheet = workbook['Booklist 12-26']   
print("表格範圍是：",sheet.dimensions)     
print("最大行數是：", sheet.max_row)       
print("最大列數是：", sheet.max_column)    
           

輸出結果：

表格範圍是： A1:E4693

最大行數是： 4693

最大列數是： 5

3.3 擷取單元格對象

3.3.1 擷取單元格的某些屬性

.row 行數

.column 列數

.coordinate 坐标

from openpyxl import load_workbook                  
                                                    
workbook = load_workbook(filename="4600本書單名.xlsx")  
sheet = workbook.active                             
cell = sheet['A5']                                  
print("單元格的行号：",cell.row)                           
print("單元格的列号：",cell.column)                        
print("單元格的坐标：",cell.coordinate)
           

輸出結果：

單元格的行号： 5

單元格的列号： A

單元格的坐标： A5

3.3.2 擷取表格内的資料

3.3.2.1 擷取表格内某一範圍的資料

# 取某一單元格
cell = sheet['A1']
# 取一列單元格
cells = sheet['A']
# 取一行單元格
cells = sheet[1]
# 取表格當中的所有列，一列為一組
cells = sheet.columns
# 取表格當中的所有行，一行為一組
cells = sheet.rows
# 已知單元格列範圍坐标時
cells = sheet['A:C']
cells = sheet['1:3']
# 已知單元格矩形範圍坐标時
cells = sheet['A1:C5']
# 已知單元格起始與終結的行列數時
cells = sheet.iter_cols(min_row=1, max_row=5, min_col=1, max_col=3)
cells = sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3)
           

workbook.active 打開活躍的/唯一的表格

cell.value 格子的資料

from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook.active
cell = sheet['A1']
print(cell.value)
           

sheet.iter_rows(min_row=最低行數,max_row=最高行數,min_col=最低列數,max_col=最高列數): # 行

sheet.iter_cols(min_row=最低行數,max_row=最高行數,min_col=最低列數,max_col=最高列數): # 列

from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook.active
for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):
    print(row)
# for row in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):
#     print(row)
           

3.3.2.2 疊代所有的行/列

.rows

.columns

# 周遊所有的行
from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook.active
擷取所有的行
for row in sheet.rows:
     print(row)
           

# 周遊所有的列
from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook.active
for col in sheet.columns:
    print(col)
           

輸出結果：

(<Cell ‘Booklist 12-26’.A1>, <Cell ‘Booklist 12-26’.A2>, <Cell ‘Booklist 12-26’.A3>, <Cell ‘Booklist 12-26’.A4>…

結果一樣，輸出順序不一樣。

4 對單元格對象的一些操作

4.1 插入行/列

.insert_cols(idx=數字編号，amount=要插入的列數)

.insert_rows(idx=數字編号，amount=要插入的行數)

• 在idx列左邊插入一列

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet.insert_cols(idx=2)
# sheet.insert_rows(idx=2) # 插入一行
workbook.save(filename='寫入表格.xlsx')
           

• 在idx列左邊插入多列

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet.insert_cols(idx=2,amount=3)
# sheet.insert_rows(idx=2,amount=3) # 插入多行
workbook.save(filename='寫入表格.xlsx')
           

4.2 删除行/列

.delete_cols(idx=數字編号，amount=要删除的列數)

.delete_rows(idx=數字編号，amount=要删除的行數)

在idx列這一行/列開始，包括idx這一行/列

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet.delete_cols(idx=2)
# sheet.delete_rows(idx=2) # 删除一行
workbook.save(filename='寫入表格.xlsx')
           

4.3 插入一行資料

append()方法接收一個可變參數，包括但不限于清單、範圍或生成器或字典

如果傳入一個清單：從第一列開始順序添加所有值，清單元素對應每一行

如果傳入一個字典：值被配置設定給鍵(數字或字母)訓示的列

注意：append一次隻能添加一行的資料，如果想要多行添加，需要與循環或其他方法相結合實作。

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
data = [
    [‘張三',1],[‘李四',2],[‘王五',3],[‘趙六',4],
]
for row in data:
    sheet.append(row)
workbook.save(filename='寫入表格.xlsx')
           

4.4 修改單元格資料及定義公式

4.4.1 修改單元格資料

• sheet[‘A1’] = '寫入内容’

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet['A1'] = 'test'
workbook.save(filename='寫入表格.xlsx')
           

輸出結果：

表格[A1]：test

• cell.value ='寫入内容’

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
cell = sheet['A1']
cell.value = 'test'
workbook.save(filename='寫入表格.xlsx')
           

輸出結果：

表格[A1]：test

• sheet與cell結合

sheet.cell(row=行數,column=列數)

cell.value = ‘内容’ # 為單元格指派

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
cell = sheet.cell(row=1,column=1)
cell.value = 'test'
           

輸出結果：

表格[A1]：test

4.4.2 插入公式

直接指派公式字元串

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet['B6'] = '=SUM(B2:B5)'
workbook.save(filename='寫入表格.xlsx')
           

檢視openpyxl支援的公式

from openpyxl.utils import formulas

print (formulas)
           

<module ‘openpyxl.utils.formulas’ from ‘D:\Software\Python\lib\site-packages\openpyxl\utils\formulas.py’>

根據這個路徑打開相應的formulas.py，顯示如下：

FORMULAE = (“CUBEKPIMEMBER”, “CUBEMEMBER”, “CUBEMEMBERPROPERTY”, “CUBERANKEDMEMBER”, “CUBESET”,

…

4.5 移動單元格

.move_range(‘C1:D4’,rows=2,cols=-2)

正整數為向下或者向右，負整數為向左或者向上，類似于剪貼CTRL+X功能。

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
sheet = workbook.active
sheet.move_range("C1:D4", rows=2, cols=-2)
workbook.save(filename='寫入表格.xlsx')
           

4.6 檢視Excel表格内容

4.6.1 設定顯示或隐藏狀态

設定表格為隐藏狀态：

sheet.sheet_state = 'hidden'

設定表格為顯示狀态：

sheet.sheet_state = 'visible'

from openpyxl import load_workbook

workbook = load_workbook(filename="寫入表格.xlsx")
print(workbook.sheetnames)
m = input("是否要隐藏工作表Y/N：")
if m == "Y" or "y":
    sheet = workbook.active
    sheet.sheet_state = 'hidden'
else:
    sheet = workbook.active
    sheet.sheet_state = 'visible'
workbook.save(filename='寫入表格.xlsx')
           

4.6.2 當機窗格

sheet.freeze_panes = “單元格”

from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook.active
sheet.freeze_panes = "B2"
workbook.save(filename='4600本書單名.xlsx')
           

4.6.3 添加篩選

auto_filter.ref

from openpyxl import load_workbook

workbook = load_workbook(filename="4600本書單名.xlsx")
sheet = workbook['Booklist 12-26']
sheet.auto_filter.ref = sheet.dimensions # 對整張表進行篩選
workbook.save(filename='4600本書單名.xlsx')
           

5 批量調整字型、樣式

5.1 擷取字型樣式

cell.font.屬性

from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = cell.font
print('font.name是:',font.name,'font.size是:',font.size,'是否粗體:',font.bold,'是否斜體:',font.italic)
           

5.2 修改字型樣式

font = Font(name=‘字型名稱’,size =字型大小,bold = 是否粗體,italic = 是否斜體,color =‘字型顔色’)

from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='宋體',size =12,bold = True,italic = True,color ='FF0000')
cell.font =font
workbook.save(filename='寫入表格.xlsx')
           

5.3 設定單元格格式分類

可以通過設定單元格的風格來設定單元格格式分類

[openpyxl所支援的excel的單元格格式][https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html?highlight=openpyxl.styles.numbers]

from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']

# 設定單元格分類為百分比
cell.style = '百分比'
cell.style = '正常'

# 正常單元格
cell.number_format = 'General'
# 百分比單元格
cell.number_format = '0.00%'
# 科學計數法
cell.number_format = '0.00E+00'
workbook.save(filename='寫入表格.xlsx')
           

5.4 設定對齊樣式

Alignment(horizontal=水準對齊模式,vertical=垂直對齊模式,text_rotation=旋轉角度,wrap_text=是否自動換行)

from openpyxl.styles import Alignment
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)
cell.alignment =alignment
workbook.save(filename='寫入表格.xlsx')
           

horizontal：

'distributed', 'justify','center','left';'fill', 'centerContinuous','right",'general'

vertical：

'bottom', 'distributed','justify','center';'top'

wrap_text：布爾類型，設定是否自動換行

textRotation：整數類型，設定文本旋轉角度，最大值180

5.5 設定邊框樣式

side = Side(style=邊線樣式,color=邊線顔色)

border = Border(left=左邊線樣式,right=右邊線樣式,top=上邊線樣式,bottom=下邊線樣式)

from openpyxl.styles import Side, Border
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell = sheet['A1']
side = Side(style='thin',color='FF000000')
border = Border(left=side,right=side,top=side,bottom=side)
cell.border=border
workbook.save(filename='寫入表格.xlsx')
           

side.style：

'double','mediumDashDotDot','slantDashDot','dashDotDot','dotted', 'hair', 'mediumDashed', 'dashed' 'dashDot' ,'thin', 'Dash Dot', 'medium', 'thick'

5.6 設定填充顔色

• 純色填充

PatternFill(fill_type=填充樣式,fgColor=填充顔色)

• 漸變顔色填充

GradientFill(stop=(漸變顔色1,漸變顔色2,漸變顔色3…))

from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
cell_a1 = sheet['A1']
pattern_fill = PatternFill(fill_type='solid',fgColor='00B0F0')
cell_a1.fill = pattern_fill
cell_b4 = sheet['B4']
gradient_fill = GradientFill(stop=('FFFFFF', '99CCFF', '000000'))
cell_b4.fill = gradient_fill
workbook.save(filename='寫入表格.xlsx')
           

5.7 設定行高和列高

.row_dimensions[行編号].height = 行高

.column_dimensions[列編号].width = 列寬

from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['B'].width = 20
workbook.save(filename='寫入表格.xlsx')
           

5.8 合并單元格

sheet.merge_cells(‘合并的範圍’)

sheet.merge_cells(start_row=起始行号,start_column=起始列号,end_row=結束行号,end_column=結束列号)

from openpyxl.styles import PatternFill, GradientFill
from openpyxl import load_workbook

workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active
sheet.merge_cells('C1:C2')
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)
workbook.save(filename='寫入表格.xlsx')
           

6 生成圖表

6.1 插入圖檔

openpyxl.drawing.image

sheet.add_image

from openpyxl import load_workbook
from openpyxl.drawing.image import Image


workbook = load_workbook(filename='寫入表格.xlsx')
sheet = workbook.active

logo = Image("butterfly.png")
logo.height = 100
logo.width = 100

sheet.add_image(logo, "D1")
workbook.save(filename='寫入表格.xlsx')
           

6.2 生成圖表

6.2.1 插入柱狀圖

表資料【A1：C7】

項目	産值	數量
A	1802	18
B	2098	20
C	2668	26
D	3537	35
E	3650	36
F	1068	15

BarChart() Reference()

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

workbook = load_workbook(filename='圖表.xlsx')
sheet = workbook.active

chart = BarChart()
data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)
categories = Reference(sheet,min_col=1,min_row=2,max_row=7)

chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart, "E2")

workbook.save(filename='圖表.xlsx')
           

6.2.2 插入條形圖

表資料【A19：M21】

月份	廣東	上海	北京	遼甯	天津	重慶	四川	浙江	江蘇	安徽	江西	福建
一月	221	279	160	340	276	224	326	174	406	454	46	146
二月	364	136	423	77	467	33	454	46	462	129	371	108

LineChart()

from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

workbook = load_workbook(filename='圖表.xlsx')
sheet = workbook.active

chart = LineChart()
# data = Reference(worksheet=sheet, min_row=1, max_row=7, min_col=2, max_col=3)
# categories = Reference(sheet,min_col=1,min_row=2,max_row=7)

data = Reference(worksheet=sheet, min_row=20, max_row=21, min_col=1, max_col=13)
categories = Reference(sheet, min_col=2, min_row=19, max_col=13)

chart.add_data(data, from_rows=True, titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart, "E2")

workbook.save(filename='圖表.xlsx')