python中openpyxl庫用法詳解

更新時間：2022年09月15日 12:05:45 作者：IT之一小佬

openpyxl模塊是一個讀寫Excel 文檔的Python庫，openpyxl是一個比較綜合的工具，能夠同時讀取和修改Excel文檔，這篇文章主要介紹了python中openpyxl庫用法詳解,需要的朋友可以參考下

一、讀取數(shù)據(jù)

數(shù)據(jù)如下圖所示：

示例代碼：

import openpyxl
 
# 打開給定的文件名并返回 工作簿
data = openpyxl.load_workbook('data/體檢表.xlsx')
print(type(data))
print(data)  # 返回一個 workbook 數(shù)據(jù)類型的值

運行結(jié)果：

1.1 從工作簿中取得工作表

工作簿對象.sheetnames - 獲取當(dāng)前工作簿中所有表的名字
工作簿對象.active - 獲取當(dāng)前活動表1 對應(yīng)的Worksheet對象
工作簿對象[表名] - 根據(jù)表名獲取指定表對象
表對象.title - 獲取表對象的表名
表對象.max_row - 獲取表的最大有效行數(shù)
表對象.max_column - 獲取表的最大有效列數(shù)

示例代碼：

import openpyxl
 
# 打開給定的文件名并返回 工作簿
data = openpyxl.load_workbook('data/體檢表.xlsx')
print(type(data))
print(data)  # 返回一個 workbook 數(shù)據(jù)類型的值
 
# 獲取所有表的表名
sheets_names = data.sheetnames
print(sheets_names)
 
# 獲取活動表對應(yīng)的表對象(表對象就是Worksheet類的對象)
active_sheet = data.active
print(active_sheet)
 
# 根據(jù)表名獲取工作簿中指定的表
sheet2 = data['Sheet2']
print(sheet2)
 
# 根據(jù)表對象獲取表的名字
sheet_name1 = active_sheet.title
sheet_name2 = sheet2.title
print(sheet_name1, sheet_name2)
 
# 獲取第二列的所有內(nèi)容
sheet = data.active
row_num = sheet.max_row  # 獲取當(dāng)前表中最大的行數(shù)
for row in range(1, row_num+1):
    cell = sheet.cell(row, 2)
    print(cell.value)

運行結(jié)果：

1.2 從表中取得單元格

表對象['列號行號'] - 獲取指定列的指定行對應(yīng)的單元格對象（單元格對象是 Cell 類的對象，列號是從A開始，行號是從1開始）
表對象.cell(行號, 列號) - 獲取指定行指定列對應(yīng)的單元格（這兒的行號和列好號都可以用數(shù)字）
表對象.iter_rows() - 一行一行的取
表對象.iter_cols() - 列表一列的取
單元格對象.value - 獲取單元格中的內(nèi)容
單元格對象.row - 獲取行號（數(shù)字1開始）
單元格對象.column - 獲取列號（數(shù)字1開始）
單元格對象.coordinate - 獲取位置（包括行號和列號）

示例代碼：

import openpyxl
 
# 打開給定的文件名并返回 工作簿
data = openpyxl.load_workbook('data/體檢表.xlsx')
print(type(data))
print(data)  # 返回一個 workbook 數(shù)據(jù)類型的值
 
# 獲取活躍表對象
sheet = data.active
 
# 獲取單元格對應(yīng)的 Cell 對象
a1 = sheet['A1']  # A1 表示A列中的第一行，這兒的列號采用的是從A開始的
print(a1)
 
# 獲取單元格中的內(nèi)容
content = a1.value
print(content)
 
# 調(diào)用表的 cell()方法時，可以傳入整數(shù) 作為 row 和 column 關(guān)鍵字參數(shù)，也可以得到一個單元格
content2 = sheet.cell(2, 2).value
print(content2)
 
# 獲取單元格的行和列信息
row = a1.row
print('行：', row)
 
column = a1.column
print('列:', column)
 
coordinate = a1.coordinate
print(coordinate)
print("*" * 100)
 
# 獲取第二列的所有內(nèi)容
row_num = sheet.max_row  # 獲取當(dāng)前表中最大的行數(shù)
for row in range(1, row_num+1):
    cell = sheet.cell(row, 2)
    print(cell.value)

運行結(jié)果：

注意：在 Z 列之后，列開始使用兩個字母:AA、AB、AC 等。作為替代，在調(diào)用表的 cell()方法時，可以傳入整數(shù) 作為 row 和 column 關(guān)鍵字參數(shù)，也可以得到一個單元格。

注意：第一行或第一列的對應(yīng)的整數(shù) 是 1，不是 0。

1.3 從表中取得行和列

表對象也可以像列表或者字符串那樣進(jìn)行切片操作，來獲取電子表格中一行、一列或一個矩形區(qū)域中的所有 Cell 對象，然后就可以對數(shù)據(jù)進(jìn)行相應(yīng)的操作。

表對象[位置1:位置2] - 獲取指定范圍中的所有的單元格

示例代碼：

import openpyxl
from openpyxl.utils import get_column_letter
 
# 打開給定的文件名并返回 工作簿
data = openpyxl.load_workbook('data/體檢表.xlsx')
# print(type(data))
# print(data)  # 返回一個 workbook 數(shù)據(jù)類型的值
 
# 獲取表對象
sheet = data.active
 
# 1.獲取整個一行的單元格
max_column = sheet.max_column  # 獲取最大列數(shù)
column = get_column_letter(max_column)  # 獲取最大列數(shù)對應(yīng)的字母列號
# 獲取第一行所有單元格對象
row2 = sheet['A1':'%s1' % column]  # ((<Cell '表1'.A1>, <Cell '表1'.B1>, <Cell '表1'.C1>),)
print(row2)
 
for row_cells in row2:
    for cell in row_cells:
        print(cell.coordinate, cell.value)
 
print("*" * 100)
 
# 2.獲取整個列的單元格
max_row = sheet.max_row
columnB = sheet['B1':'B%d' % max_row]
 
# 獲取B列對應(yīng)的所有單元格對象
for column_cells in columnB:
    for cell in column_cells:
        print(cell.coordinate, cell.value)
 
print("*" * 100)
 
# 3. 獲取矩形區(qū)域中的單元格對象
cell_tuples = sheet['A1': 'C3']
print(cell_tuples)
for cells in cell_tuples:
    for cell in cells:
        print(cell.coordinate, cell.value)

運行結(jié)果：

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

2.1 創(chuàng)建Workbook對象來創(chuàng)建Excel文件并保存

openpyxl.Workbook() - 創(chuàng)建空的 Excel 文件對應(yīng)的工作簿對象
工作簿對象.save(文件路徑) - 保存文件
工作簿對象.create_sheet(title, index) - 在指定工作簿中的指定位置（默認(rèn)是最后）創(chuàng)建指定名字的表，并且返回表對象
工作簿對象.remove(表對象) - 刪除工作簿中的指定表
表對象[位置] = 值 - 在表中指定位置對應(yīng)的單元格中寫入指定的值，位置是字符串：‘A1’（第1列的第一行）、‘B1’（第二列的第一行）

示例代碼：

import openpyxl
 
# 創(chuàng)建空的Workbook對象
w_data = openpyxl.Workbook()
 
# 獲取所有表名
print(w_data.sheetnames)  # ['Sheet']
# 可知默認(rèn)情況下，新建的Workbook對象對應(yīng)的Excel 文件中只有一張名字是 'Sheet' 的表
 
# 獲取活動表
sheet_active = w_data.active
 
# 修改表的名字
sheet_active.title = 'first_table'
 
# 保存至文件
w_data.save(filename='data/info.xlsx')
 
# 新建表
w_data.create_sheet()
w_data.create_sheet()
# for i in range(20):
#     wb.create_sheet()
print(w_data.sheetnames)  # ['first_table', 'Sheet', 'Sheet1']
# 新建表時，從Sheet開始一直到Sheet n
 
w_data.create_sheet('second_table')
 
# `工作簿對象.create_sheet(title, index)`  - 在指定工作簿中的指定位置（默認(rèn)是最后）創(chuàng)建指定名字的表，并且返回表對象
w_data.create_sheet('third_table', 1)
 
print(w_data.sheetnames)  # ['first_table', 'third_table', 'Sheet', 'Sheet1', 'second_table']
 
# 刪除表
w_data.remove(w_data['Sheet1'])
w_data.save(filename='data/info.xlsx')
 
# 寫入數(shù)據(jù)
w_data = openpyxl.load_workbook('data/info.xlsx')
sheet = w_data['first_table']  # 獲取表
 
sheet['A1'] = '姓名'
sheet['B1'] = '年齡'
sheet['C1'] = '性別'
 
sheet['A2'] = '張三'
sheet['B2'] = 25
sheet['C2'] = '男'
 
w_data.save('data/info.xlsx')

運行結(jié)果：

2.2 案例分析一：爬取數(shù)據(jù)并保存excel中

示例代碼：

# 利用requests獲取天行數(shù)據(jù)中疫情數(shù)據(jù)，并且將獲取到的數(shù)據(jù)使用excel文件保存到表中。
import requests
import openpyxl
from openpyxl.utils import get_column_letter
 
# 1.獲取數(shù)據(jù)
url = 'http://api.tianapi.com/txapi/ncovabroad/index?key=c9d408fefd8ed4081a9079d0d6165d43'
rep = requests.get(url)
news_list = rep.json()['newslist']
 
# 2.設(shè)置表頭信息
headers = {'continents': ('洲', 'A'),
           'provinceName': ('國家', 'B'),
           'currentConfirmedCount': ('現(xiàn)有確診', 'C'),
           'confirmedCount': ('累計確診', 'D'),
           'curedCount': ('治愈', 'E'),
           'deadCount': ('死亡', 'F')}
 
# 3.創(chuàng)建工作表
wb = openpyxl.Workbook()
sheet = wb.active
 
# 4.寫入數(shù)據(jù)
# 先寫入第一行的表頭
column_num = 1
for key in headers:
    column = get_column_letter(column_num)
    location = f'{column}1'
    sheet[location] = headers[key][0]
    column_num += 1
 
# 再從第二行開始寫入爬取到的數(shù)據(jù)
row = 2
for news in news_list:  # 遍歷每條數(shù)據(jù)項，一個數(shù)據(jù)項對應(yīng)一個字典
    for key in news:  # 遍歷數(shù)據(jù)鍵值
        if key in headers:  # 保證鍵是表頭中的某一項我們需要的數(shù)據(jù)
            location = f'{headers[key][1]}{row}'  # 存在表中的位置
            value = news[key]  # 需要的數(shù)據(jù)
            sheet[location] = value  # 寫入
    row += 1
 
wb.save(filename='data/epidemic.xlsx')

運行結(jié)果：

2.3 案例分析二：操作單元格中內(nèi)容樣式并保存數(shù)據(jù)

示例代碼：

import openpyxl
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment  # 字體、填充圖案、邊框、側(cè)邊、對齊方式
 
# 1.打開工作簿
data = openpyxl.load_workbook('data/體檢表.xlsx')
sheet = data.active
 
# 2.設(shè)置單元格字體樣式
"""
Font(
    name=None,      # 字體名，可以用字體名字的字符串
    strike=None,    # 刪除線，True/False
    color=None,     # 文字顏色
    size=None,      # 字號
    bold=None,      # 加粗, True/False
    italic=None,    # 傾斜，Tue/False
    underline=None  # 下劃線, 'single', 'singleAccounting', 'double','doubleAccounting'
)
"""
# 創(chuàng)建字體對象，并調(diào)整合適的參數(shù)
font1 = Font(
    name='微軟雅黑',
    size=15,
    italic=False,
    color='ff0000',
    bold=False,
    strike=False,
    underline='single',
)
 
# 設(shè)置指定單元格的字體
# 單元格對象.font = 字體對象
area = sheet['A1':'E1']
for row in area:
    for _ in row:
        _.font = font1  # 調(diào)整字體
 
# 3.設(shè)置單元格填充樣式
"""
PatternFill(
	fill_type=None,	# 設(shè)置填充樣式: 'darkGrid', 'darkTrellis', 'darkHorizontal', 'darkGray', 'lightDown', 'lightGray', 'solid', 'lightGrid', 'gray125', 'lightHorizontal', 'lightTrellis', 'darkDown', 'mediumGray', 'gray0625', 'darkUp', 'darkVertical', 'lightVertical', 'lightUp'
                    #              '深色網(wǎng)格“，”深色網(wǎng)格“，”深色水平“，”深色灰色“，”淺色向下“，”淺灰色“，”純色“，”淺色網(wǎng)格“，”灰色125“，”淺色水平“，”淺色網(wǎng)格“，”深色向下“，”中灰色“，”灰色0625“，”深色向上“，”深色垂直“，”淺色垂直“，”淺色向上“
	start_color=None# 設(shè)置填充顏色
)
"""
 
# 設(shè)置填充對象
fill = PatternFill(fill_type='solid', start_color='FFC0CB')
 
# 設(shè)置單元格的填充樣式
# 單元格對象.fill = 填充對象
area = sheet['A1':'E1']
for row in area:
    for _ in row:
        _.fill = fill  # 調(diào)整填充格式
 
# 4. 設(shè)置單元格對齊樣式
# 創(chuàng)建對象
al = Alignment(
    horizontal='right',  # 水平方向:center, left, right
    vertical='top',  # 垂直方向: center, top, bottom
)
# 設(shè)置單元格的對齊方式
sheet['B2'].alignment = al
 
# 5. 設(shè)置邊框樣式
# 設(shè)置邊對象（四個邊的邊可以是一樣的也可以不同，如果不同就創(chuàng)建對個Side對象）
# border_style取值('dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin')
#                  單點劃線，雙點劃線，點劃線，雙劃線，頭發(fā)，中劃線，單點中劃線，雙點中劃線，中劃線，斜劃線，粗劃線，細(xì)劃線
side = Side(border_style='thin', color='000000')
 
# 設(shè)置邊框?qū)ο?
# left、right、top、bottom表示的是邊框的四個邊，四個邊可以使用一個邊對象
bd = Border(left=side, right=side, top=side, bottom=side)
# 設(shè)置單元格的邊框
area = sheet['A1':'E1']
for row in area:
    for _ in row:
        _.border = bd
 
# 6.設(shè)置單元格的寬度和高度
# 設(shè)置列寬
sheet.column_dimensions['A'].width = 10
# 設(shè)置行高
sheet.row_dimensions[1].height = 30
 
# 7. 保存
data.save(filename='data/體檢表2.xlsx')

運行結(jié)果：

2.4 案例分析三：將列表數(shù)據(jù)寫入excel中

示例代碼：

import openpyxl
import datetime
 
datas = [
    ('學(xué)號', '姓名', '年齡', '專業(yè)', '考試時間'),
    ('B00001', '張1', 18, '語文', datetime.datetime(2019, 6, 18, 0, 0)),
    ('B00002', '張2', 19, '數(shù)學(xué)', datetime.datetime(2019, 6, 19, 0, 0)),
    ('B00003', '張3', 20, '英語', datetime.datetime(2019, 6, 20, 0, 0)),
    ('B00004', '張4', 21, '物理', datetime.datetime(2019, 6, 21, 0, 0)),
    ('B00005', '張5', 22, '化學(xué)', datetime.datetime(2019, 6, 22, 0, 0)),
    ('B00006', '張6', 23, '生物', datetime.datetime(2019, 6, 23, 0, 0)),
    ('B00007', '張7', 24, '歷史', datetime.datetime(2019, 6, 24, 0, 0))
]
 
# 創(chuàng)建空的Workbook對象
w_data = openpyxl.Workbook()
 
# 獲取活動表
sheet = w_data.active
 
for i in range(1, len(datas) + 1):
    for j in range(1, 6):
        sheet.cell(row=i, column=j, value=datas[i - 1][j - 1])
 
w_data.save('data/info.xlsx')

運行結(jié)果：