一、Excel文檔

工作薄: .xlsx文件、包含多個表（工作表）。

活動表：用戶當(dāng)前查看的表、關(guān)閉Excel前最后查看的表。

二、安裝openpyxl模塊

import openpyxl

三、讀取Excel表格

先制作一張例表1.xlsx

?1、用openpyxl模塊打開Excel文檔

openpyxl模塊提供了openpyxl.load_workbook()函數(shù)，用來打開Excel文檔。

?openpyxl.load_workbook()函數(shù)打開Excel文檔會返回一個workbook數(shù)據(jù)類型的值。

workbook對象代表這個Excel文件，相當(dāng)于File對象代表一個打開的文本文件。

2、用工作薄中取得工作表

>>> import openpyxl
>>> wb = openpyxl.load_workbook('example.xlsx') 打開Excel文檔，得到workbook對象
>>> wb.get_sheet_names() workbook對象調(diào)用get_sheet_names()方法，得到工作薄中的所有工作表名
['Sheet1', 'Sheet2', 'Sheet3']   所有工作表名
>>> sheet = wb.get_sheet_by_name('Sheet3') get_sheet_by_name()方法指定工作表名，得到工作表對象，即worksheet對象
>>> sheet
<Worksheet "Sheet3">  Worksheet對象
>>> type(sheet)
<class 'openpyxl.worksheet.worksheet.Worksheet'>
>>> sheet.title      worksheet對象調(diào)用title方法，得到工作表名
'Sheet3'
>>> anotherSheet = wb.get_active_sheet() worksheet對象調(diào)用get_active_sheet()方法得到活動表
>>> anotherSheet
<Worksheet "Sheet1">

openpyxl.load_workbook('example.xlsx')：打開Excel文件，得到工作薄對象，workbook對象。

wb.get_sheet_by_name('Sheet3')：工作薄對象調(diào)用get_sheet_by_name()方法，得到工作表對象，worksheet對象。

3、從表中取得單元格

工作薄對象——工作表對象——單元格對象

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄對象
sheet=wb['Sheet5']                     #得到工作表對象
print(type(sheet['A1']))               #得到單元格對象
print(sheet['A1'].value)
c=sheet['B1']
print(c)
print(c.value)

?Cell對象，單元格對象，有value、row、column和coordinate屬性。

工作表對象調(diào)用cell()方法，傳入整數(shù)作為row和column關(guān)鍵字參數(shù)，也可以得到一個單元格對象。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄對象
sheet=wb['Sheet5']                     #得到工作表對象
a=sheet.cell(row=1,column=2)
print(type(a))

?利用 cell()方法和它的關(guān)鍵字參數(shù)， 就可以編寫 for 循環(huán)，打印出一系列單元格的值。

練習(xí)：打印出B列所有所有單元格的值。

工作表worksheet對象的get_highest_row()方法和get_highest_column()方法可以得到表的大小，但是在最新的openpyxl模塊中，已經(jīng)廢除，轉(zhuǎn)而用max_row、max_column。

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄對象
sheet=wb['Sheet5']   #得到工作表對象
r=sheet.max_row
c=sheet.max_column
print(r,c)
for i in range(1,7):
    a=sheet.cell(row=i,column=2)
    print(a.value)

4、列字母和數(shù)字之間的轉(zhuǎn)換

>>> import openpyxl
>>> from openpyxl.cell import get_column_letter, column_index_from_string
>>> get_column_letter(1)
'A'
>>> get_column_letter(2) 
'B'
>>> get_column_letter(27)
'AA'
>>> get_column_letter(900)
'AHP'
>>> wb = openpyxl.load_workbook('example.xlsx')
>>> sheet = wb.get_sheet_by_name('Sheet1')
>>> get_column_letter(sheet.get_highest_column())
'C'
>>> column_index_from_string('A')
1 
>>> column_index_from_string('AA')
27

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄對象
sheet=wb['Sheet5']   #得到工作表對象
print(tuple(sheet['A1':'C3']))

import openpyxl

wb=openpyxl.load_workbook('1.xlsx')    #得到工作薄對象
sheet=wb['Sheet5']   #得到工作表對象
a=sheet['A1':'C3']
b=[col for col in sheet.columns][1]
print(b)

官方文檔：https://openpyxl.readthedocs.io/en/stable/

注意：openpyxl模塊只支持xlsx/xlsm/xltx/xltm格式，不支持xls格式。

二、新建

1、新建工作薄

?from?openpyxl?import?Workbook：從openpyxl模塊導(dǎo)入Workbook類

wb?=?Workbook() 從Workbook類中實例化對象wb

ws?=?wb.active wb對象調(diào)用active方法 得到活動表

2、新建工作表

可以使用create_sheet()函數(shù)新建一張工作表。

#新建工作表，名稱以Sheet1，Sheet2，....自動填充
ws_1 = wb.create_sheet()    # 默認(rèn)在結(jié)尾處新建一個新的工作表
ws_2 = wb.create_sheet(0)   # 在當(dāng)前工作表的指定索引處新建一個工作表

# 用title指定工作表名稱
ws_1.title = "新建工作表" 

# 新建工作表，并指定名稱
ws_3 = wb.create_sheet(title="新建工作表-2",index=0)
ws_4 = wb.create_sheet("新建工作表-1", 0) 

# 改變工作表標(biāo)簽顏色，默認(rèn)為無顏色
ws.sheet_properties.tabColor = "F22F27"   

?

?三、操作

1、設(shè)置工作薄只讀

wb = load_workbook(filename='數(shù)據(jù).xlsx', read_only=True) #設(shè)置只讀

?

?2、工作表操作

# 導(dǎo)入openpyxl模塊的Workbook類
import openpyxl
from openpyxl import load_workbook

wb=load_workbook(filename='1.xlsx',read_only=True)
print(wb.sheetnames) # 獲取工作薄中所有工作表名稱
for sheet in wb:
	print(sheet.title) # 遍歷工作薄中所有工作表名
#獲取指定工作表名稱
ws=wb['Sheet1'] #創(chuàng)建工作表對象
print('***********'+ws.title) #輸入工作表名稱
ws_copy=wb.copy_worksheet(ws) #復(fù)制工作表

#刪除工作表
# 方式一
ws = wb["Sheet1"]
wb.remove(ws)
# 方式二
del wb["Sheet1"]

3、行列操作

獲知單元格范圍

?插入空白行、空白列

?刪除行、列

?4、訪問單元格

?注意：當(dāng)一個工作表被創(chuàng)建時，其中不包含單元格，只有當(dāng)單元格被獲取時才被創(chuàng)建。

這種方式我們不會創(chuàng)建我們從不會使用的單元格，從而減少了內(nèi)存消耗。

訪問單個單元格

cell_A2=ws['A2']

cell_C3=ws[row=3,column=3]

訪問多個單元格

通過切片訪問：

cell_area?=?ws['A1':'B4']
cell_exact?=?ws.iter_rows(min_row=1,?max_row=3,?min_col=1,?max_col=2)?????#即A1:B3

通過行、列訪問：

col_A?=?ws['A']???????????#A列
col_area?=?ws['A:B']??????#A、B列
row_2?=?ws[2]?????????????#第2行
row_area?=?ws[2:5]????????#2-5行

#?迭代所有行
all_by_row?=?ws.rows?

#?迭代所有列
all_by_col?=ws.columns??

結(jié)果可以用tuple()、list()、循環(huán)進(jìn)行處理

e.g. 1.xlsx

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']
#獲取單個單元格的值
cell_A2=ws['A2']
cell_C3=ws.cell(row=3,column=3)
print(cell_A2.value,cell_C3.value)
print('---------------------------')
#獲取多個單元格的值
cell_area = ws['A1':'D4']
for row in cell_area:
    for cell in row:
        print(cell.value)

?5、設(shè)置行高和列寬

設(shè)置整行行高

# 設(shè)置第2行行高
ws.row_dimensions[2].height = 40

設(shè)置整列列寬

# 設(shè)置C列列寬
ws.column_dimensions['C'].width = 30

最后一定要記得保存，wb.save('文件名')，不然不會應(yīng)用的。

e.g. 1.xlsx

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

# 設(shè)置第2行行高
ws.row_dimensions[2].height = 40

# 設(shè)置C列列寬
ws.column_dimensions['C'].width = 30

wb.save('1.xlsx')

原來的：?

修改后：?

?6、合并單元格

?合并

注意：要想在合并單元格寫入數(shù)據(jù)，只需要在合并區(qū)域左上角的單元格寫入數(shù)據(jù)即可。

如果合并區(qū)域內(nèi)的單元格都有數(shù)據(jù)，也只會保留左上角的數(shù)據(jù)。

ws.merge_cells('A2:D4')

ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws.merge_cells('A5:B6')
ws['A5']='這是一個合并后的單元格'
wb.save('1.xlsx')

?

?取消合并

ws.unmerge_cells('A2:D4')

ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws.merge_cells('A5:B6')
ws['A5']='這是一個合并后的單元格'
ws.unmerge_cells('A5:B6')


wb.save('1.xlsx')

四、寫入

1、寫入數(shù)據(jù)

# 在單元格寫入數(shù)據(jù)
ws['A1'] = 42    #A1單元格寫入
ws.cell(row=1, column=2, value=42)   #B1單元格寫入
ws.cell(1,3).value= 42   #C1單元格寫入

# 新增一行數(shù)據(jù)
ws.append([1, 2, 3, 4])

舉個例子：

import openpyxl
from openpyxl import load_workbook

wb=load_workbook('1.xlsx')
ws=wb['Sheet5']

ws['A1']='姓名'
ws.cell(row=5, column=1, value='韓梅梅')   #A5單元格寫入
ws.cell(5,2).value= 42   #B5單元格寫入

ws.append(['離美', 2, 3, 4]) # 新增一行數(shù)據(jù)

wb.save('1.xlsx')

2、寫入公式?

#寫入公式
ws['B2'] = "=SUM(A2:A4)"
ws.cell(row=2, column=2, value = "=SUM(A2:A4)")
ws.cell(2,2).value = "=SUM(A2:A4)"

3、插入圖片

img = Image('image') # image：要插入的圖片
ws.add_image(img, 'B1') #在B1單元格插入圖片

五、設(shè)置單元格樣式

單元格樣式有：number_format(數(shù)據(jù)格式)、Font(字體)、Fill(填充)、Border（邊框）、Alignment(對齊方式)、Protection(保護(hù))。

1、數(shù)字格式

import openpyxl
from openpyxl import Workbook
import datetime

wb = Workbook()
ws = wb.active

ws['A1'] = '文字'
print(ws['A1'].number_format)    #-->>> General

ws['A2'] = 5
print(ws['A2'].number_format)   #-->>> General

ws['A3'] = 0.05
ws['A3'].number_format='0.00%' # 自定義格式
print(ws['A3'].number_format)  # -->>> 0.00%

ws['B1'] = datetime.datetime.now()
print(ws['B1'].number_format)   # -->>> yyyy-mm-dd h:mm:ss

ws['B2'] = datetime.datetime.now()
ws['B2'].number_format='yyyy-mm-dd' # 自定義格式
print(ws['B2'].number_format)  #-->>>  yyyy-mm-dd 

wb.save("2.xlsx") 

?2、字體

導(dǎo)入

# 導(dǎo)入Font
from openpyxl.styles import Font

參數(shù)說明

name           #字體
size           #字號，默認(rèn)11。
bold           #是否加粗，默認(rèn)False。加粗：True
italic         #是否斜體，默認(rèn)False。斜體：True
vertAlign      #上下標(biāo)，默認(rèn)None。正常：baseline，上標(biāo)：superscript，下標(biāo)：subscript
color          #字體顏色，默認(rèn)黑色（FF000000）。
strikethrough  #刪除線，默認(rèn)不設(shè)置。設(shè)置：True
underline      #下劃線，默認(rèn)不帶下劃線。單下劃線：single，雙下劃線：double，會計用單下劃線：singleAccounting，會計用雙下劃線：doubleAccounting

實例

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font
import datetime

wb = Workbook()
ws = wb.active

ws['A1'] = '默認(rèn)'      #A1單元格寫入
ws['B2'] = '設(shè)置格式'  #B2單元格寫入
ws['C3'] = '設(shè)置上標(biāo)'  #C3單元格寫入

#設(shè)置B2單元格字體格式
ws['B2'].font = Font(name='Calibri', size=12, color="00FF9900", italic=True, underline='double', strikethrough=True)

#設(shè)置C3單元格字體格式
ws['C3'].font = Font(vertAlign='superscript',  bold=True)

#保存

wb.save("2.xlsx") 

?3、填充

純色填充

導(dǎo)入

#導(dǎo)入
from openpyxl.styles import PatternFill

參數(shù)說明

fill_type    #設(shè)置圖案樣式，如果不設(shè)置則不會顯示顏色。
'''
fill_type可設(shè)置的值：solid（實心），lightHorizontal, 
darkTrellis, darkUp, darkGray, darkVertical, lightDown,
lightTrellis, lightUp, darkDown, darkHorizontal, mediumGray, 
lightVertical, gray0625, gray125, lightGrid, darkGrid, lightGray
'''
fgColor/start_color     #前景色 ，即填充色
bgColor/end_color      #背景色，即圖案顏色

?實例

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active

ws['A1'] = '默認(rèn)'    #A1單元格寫入
ws['B2'] = '前景色'  #B2單元格寫入
ws['C3'] = '背景色'  #C3單元格寫入

#前景色，即填充色。也是我們一般設(shè)置的填充色
ws['B2'].fill = PatternFill(fill_type='solid', fgColor='00FF9900') 

#背景色，即圖案顏色。
ws['C3'].fill = PatternFill(fill_type='solid', bgColor='00FF9900')  

wb.save("實例.xlsx") 

2、漸變填充（GradientFill）

導(dǎo)入

#導(dǎo)入
from?openpyxl.styles?import?GradientFill

參數(shù)說明

type/fill_type  # 漸變填充類型：linear，path

'''
linear ：
漸變在一組指定的 Stops 之間插入顏色，跨越一個區(qū)域的長度。默認(rèn)情況下漸變是從左到右的，但可以使用 degree 屬性修改此方向?？梢愿臑樘峁╊伾斜恚鼈冎g的距離將相等。

path： 
漸變從區(qū)域的每個邊緣應(yīng)用線性漸變。屬性 top、right、bottom、left 指定從各個邊界填充的范圍。比如top=”0.2” 將填充單元格的前 20%。
'''

實例

from openpyxl.styles import GradientFill
from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# 合并單元格
ws.merge_cells('B2:F4')

#對合并單元格左上角單元格設(shè)置漸變填充
top_left_cell = ws['B2']
top_left_cell.fill = GradientFill(type='linear', degree=0, stop=('FFFFFF', '99ccff', '000000'))   #漸變填充

wb.save("實例.xlsx") 

4、邊框

設(shè)置邊框樣式時還需要通過Side。文章來源地址http://www.zghlxwxcb.cn/news/detail-449462.html

導(dǎo)入

#導(dǎo)入
from openpyxl.styles import Border, Side

參數(shù)說明

# Border參數(shù)說明
left = Side(style , color)  #左邊框設(shè)置
right = Side(style , color)  #右邊框設(shè)置
top = Side(style , color)  #上邊框設(shè)置
bottom = Side(style , color) #下邊框設(shè)置

diagonalDown  #是否顯示左上-右下對角線，顯示：True
diagonalUp   #是否顯示左下-右上對角線，顯示：True
diagonal = Side(style , color)    #對角線邊框設(shè)置，注意首先要設(shè)置顯示對角線

#Side參數(shù)說明
style/border_style   #邊框樣式
'''
邊框樣式可設(shè)置的有：
thick, mediumDashDot, dashed, mediumDashDotDot, 
dashDot, slantDashDot, dotted, double, thin, 
hair, dashDotDot, mediumDashed, medium
'''
color  #邊框顏色

實例

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

wb = Workbook()
ws = wb.active


ws['A1'] = '默認(rèn)'   #A1單元格寫入
ws['B2'] = '邊框'   #B2單元格寫入
ws['C3'] = '對角線'      #C3單元格寫入

#邊框線格式設(shè)置
line_format = Side(style='medium',color='00FF9900')

#B2單元格設(shè)置上下左右邊框
ws['B2'].border = Border(left=line_format, right=line_format, top=line_format, bottom=line_format)

#C3單元格設(shè)置對角線
ws['C3'].border = Border(diagonalDown=True, diagonalUp=True, diagonal=line_format)

#保存
wb.save("實例.xlsx") 

參考鏈接：Python自動化辦公：openpyxl教程(基礎(chǔ))

到了這里，關(guān)于Python之第十二章 處理Excel電子表格的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！