Python openpyxl庫處理Excel文件高級(jí)應(yīng)用技巧實(shí)例
openpyxl的重要性和優(yōu)勢
跨平臺(tái)性： openpyxl可以在多個(gè)平臺(tái)上運(yùn)行，包括Windows、Linux和MacOS，使得數(shù)據(jù)處理任務(wù)更加靈活和便捷。
無需Excel軟件： 與使用Excel軟件相比，openpyxl允許在沒有安裝Microsoft Excel的環(huán)境中進(jìn)行Excel文件的讀寫和操作。
開放源代碼： openpyxl是一個(gè)開源項(xiàng)目，可以自由使用和修改，使得開發(fā)者能夠根據(jù)自己的需求對(duì)其進(jìn)行定制和擴(kuò)展。
活躍的社區(qū)支持： 由于其在開源社區(qū)中的活躍參與，openpyxl不斷更新和改進(jìn)，確保與最新的Excel格式兼容，并提供最佳性能。
功能豐富： openpyxl提供了豐富的功能，包括但不限于讀取和寫入數(shù)據(jù)、創(chuàng)建圖表、設(shè)置樣式、執(zhí)行公式計(jì)算等，使其適用于各種復(fù)雜的Excel處理任務(wù)。
安裝openpyxl
使用pip（Python包管理工具）來安裝openpyxl。
pip install openpyxl
基本概念
1 Workbook（工作簿）
在openpyxl中，Workbook是一個(gè)Excel工作簿的抽象表示。它是整個(gè)Excel文件的最頂層對(duì)象，包含了所有的Sheet和相關(guān)數(shù)據(jù)。
示例代碼：創(chuàng)建Workbook
from openpyxl import Workbook # 創(chuàng)建一個(gè)Workbook對(duì)象 wb = Workbook()
在這個(gè)例子中，使用Workbook類創(chuàng)建了一個(gè)新的Excel工作簿。
2 Sheet（工作表）
Sheet是工作簿中的一個(gè)單獨(dú)的工作表。工作簿可以包含一個(gè)或多個(gè)工作表。默認(rèn)情況下，創(chuàng)建工作簿時(shí)會(huì)包含一個(gè)名為”Sheet”的工作表。
示例代碼：添加Sheet
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 創(chuàng)建一個(gè)名為"NewSheet"的Sheet
new_sheet = wb.create_sheet("NewSheet")在這個(gè)例子中，通過active屬性獲取了活動(dòng)的工作表，然后使用create_sheet方法創(chuàng)建了一個(gè)名為”NewSheet”的新工作表。
3 Cell（單元格）
Cell是工作表中的一個(gè)單元格，用于存儲(chǔ)數(shù)據(jù)。單元格由列字母和行號(hào)唯一標(biāo)識(shí)，例如”A1″表示第一列第一行的單元格。
示例代碼：寫入數(shù)據(jù)到單元格
# 向單元格寫入數(shù)據(jù) sheet['A1'] = 'Hello' sheet['B1'] = 'World'
在這個(gè)例子中，使用單元格坐標(biāo)（’A1’和’B1’）向工作表中的特定單元格寫入了數(shù)據(jù)。
讀取數(shù)據(jù)
在openpyxl中，可以使用不同的方法來從已有的Excel文件中讀取數(shù)據(jù)，包括文本、數(shù)字和日期等不同數(shù)據(jù)類型。
1 打開Excel文件
首先，需要使用load_workbook函數(shù)打開已有的Excel文件。
from openpyxl import load_workbook
# 打開Excel文件
wb = load_workbook('example.xlsx')
# 獲取活動(dòng)的Sheet
sheet = wb.active2 讀取文本數(shù)據(jù)
對(duì)于包含文本數(shù)據(jù)的單元格，可以直接通過value屬性讀取。
# 讀取文本數(shù)據(jù)
text_data = sheet['A1'].value
print("文本數(shù)據(jù):", text_data)
3 讀取數(shù)字?jǐn)?shù)據(jù)
對(duì)于包含數(shù)字?jǐn)?shù)據(jù)的單元格，同樣可以通過value屬性讀取。
# 讀取數(shù)字?jǐn)?shù)據(jù)
number_data = sheet['B1'].value
print("數(shù)字?jǐn)?shù)據(jù):", number_data)
4 讀取日期數(shù)據(jù)
如果單元格包含日期數(shù)據(jù)，可以通過value屬性讀取日期對(duì)象。
# 讀取日期數(shù)據(jù)
date_data = sheet['C1'].value
print("日期數(shù)據(jù):", date_data)
在讀取日期數(shù)據(jù)時(shí)，openpyxl會(huì)將其轉(zhuǎn)換為Python的datetime對(duì)象。
寫入數(shù)據(jù)
在openpyxl中，可以使用不同的方法向Excel文件中寫入不同類型的數(shù)據(jù)，同時(shí)還可以進(jìn)行一些樣式設(shè)置和合并單元格等操作。
1 寫入文本和數(shù)字?jǐn)?shù)據(jù)
對(duì)于文本和數(shù)字?jǐn)?shù)據(jù)，可以直接在單元格中使用等號(hào)賦值。
# 寫入文本和數(shù)字?jǐn)?shù)據(jù) sheet['A1'] = 'Hello' sheet['B1'] = 123
2 寫入日期數(shù)據(jù)
對(duì)于日期數(shù)據(jù)，可以使用Python的datetime對(duì)象。
from datetime import datetime # 寫入日期數(shù)據(jù) sheet['C1'] = datetime(2023, 1, 1)
3 樣式設(shè)置
openpyxl可以對(duì)單元格進(jìn)行樣式設(shè)置，包括字體、顏色和邊框等。
from openpyxl.styles import Font, PatternFill, Border
# 設(shè)置字體樣式
sheet['A1'].font = Font(bold=True, color='FF0000')
# 設(shè)置背景顏色
sheet['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 設(shè)置邊框
sheet['A1'].border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))4 合并單元格
可以使用merge_cells方法合并單元格。
# 合并單元格
sheet.merge_cells('A1:B2')
5 保存文件
最后，需要保存Workbook，以便在Excel中查看結(jié)果。
# 保存文件
wb.save('write_example.xlsx')
圖表和圖片
在openpyxl中，可以使用圖表和圖片來更生動(dòng)地展示數(shù)據(jù)和信息。以下是如何處理圖表和圖片的示例。
1 添加圖表
首先，創(chuàng)建一個(gè)簡單的柱狀圖，并將其添加到Excel文件中。
from openpyxl.chart import BarChart, Reference # 創(chuàng)建柱狀圖對(duì)象 chart = BarChart() # 設(shè)置圖表數(shù)據(jù)范圍 data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=2) chart.add_data(data) # 將圖表添加到Sheet sheet.add_chart(chart, "D4")
2 插入圖片
接下來，插入一張圖片到Excel文件中。
from openpyxl.drawing.image import Image
# 插入圖片
img = Image('path/to/image.jpg')
sheet.add_image(img, 'E4')
確保替換 'path/to/image.jpg' 為實(shí)際圖片的路徑，并將圖片添加到Excel文件的指定位置。
3 設(shè)置圖表和圖片屬性
還可以設(shè)置圖表和圖片的各種屬性，如位置、大小、標(biāo)題等。
# 設(shè)置圖表位置和大小 chart.x = 50 chart.y = 100 chart.width = 300 chart.height = 200 # 設(shè)置圖片位置和大小 img.width = 100 img.height = 100 img.anchor = 'F4'
4 保存文件
最后，需要保存Workbook，以便在Excel中查看結(jié)果。
# 保存文件
wb.save('chart_and_image_example.xlsx')
數(shù)據(jù)篩選和排序
在openpyxl中，可以使用篩選和排序功能對(duì)Excel表格中的數(shù)據(jù)進(jìn)行進(jìn)一步的處理。以下是如何在openpyxl中實(shí)現(xiàn)數(shù)據(jù)篩選和排序的示例。
1 數(shù)據(jù)篩選
使用AutoFilter來實(shí)現(xiàn)數(shù)據(jù)篩選。下面是一個(gè)示例，演示如何按條件篩選數(shù)據(jù)。
from openpyxl import Workbook
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入示例數(shù)據(jù)
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'
data = [
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 95),
('Eva', 88)
]
for row in data:
sheet.append(row)
# 啟用AutoFilter
sheet.auto_filter.ref = sheet.dimensions
# 按條件篩選數(shù)據(jù)
sheet.auto_filter.add_filter_column(1, ["Alice", "David"])
# 保存文件
wb.save('filter_example.xlsx')在上述代碼中，使用auto_filter屬性啟用了AutoFilter，并通過add_filter_column方法按條件篩選了數(shù)據(jù)。
2 數(shù)據(jù)排序
openpyxl也提供了對(duì)數(shù)據(jù)進(jìn)行排序的功能。以下是一個(gè)示例，演示如何按指定列對(duì)數(shù)據(jù)進(jìn)行升序排序。
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入示例數(shù)據(jù)
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'
data = [
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 95),
('Eva', 88)
]
for row in data:
sheet.append(row)
# 將數(shù)據(jù)轉(zhuǎn)換為DataFrame
df = pd.DataFrame(data, columns=['Name', 'Score'])
# 按Score列升序排序
df.sort_values(by='Score', inplace=True)
# 將排序后的數(shù)據(jù)寫回Excel
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 2):
for c_idx, value in enumerate(row, 1):
sheet.cell(row=r_idx, column=c_idx, value=value)
# 保存文件
wb.save('sort_example.xlsx')在上述代碼中，使用pandas庫將數(shù)據(jù)轉(zhuǎn)換為DataFrame，然后使用sort_values方法按指定列（這里是’Score’列）進(jìn)行升序排序，最后將排序后的數(shù)據(jù)寫回Excel。
公式計(jì)算
在openpyxl中，可以使用公式對(duì)Excel文件中的數(shù)據(jù)進(jìn)行計(jì)算。以下是如何使用公式的示例，以及一些注意事項(xiàng)。
1 基本公式
首先，可以在單元格中使用基本的Excel函數(shù)進(jìn)行計(jì)算。
from openpyxl import Workbook
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入示例數(shù)據(jù)
sheet['A1'] = 10
sheet['A2'] = 20
# 使用SUM函數(shù)計(jì)算A1和A2的總和
sheet['A3'] = '=SUM(A1:A2)'
# 保存文件
wb.save('formula_example.xlsx')在上述代碼中，使用了Excel的SUM函數(shù)計(jì)算了A1和A2的總和。
2 復(fù)雜公式
還可以編寫更復(fù)雜的公式，涉及多個(gè)單元格和函數(shù)。
from openpyxl import Workbook
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入示例數(shù)據(jù)
sheet['B1'] = 5
sheet['B2'] = 10
sheet['B3'] = 15
# 使用AVERAGE函數(shù)計(jì)算B1到B3的平均值
sheet['B4'] = '=AVERAGE(B1:B3)'
# 保存文件
wb.save('complex_formula_example.xlsx')在上述代碼中，使用了Excel的AVERAGE函數(shù)計(jì)算了B1到B3的平均值。
3 計(jì)算日期差
如果數(shù)據(jù)涉及日期，可以使用日期函數(shù)計(jì)算日期之間的差值。
from openpyxl import Workbook
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入日期數(shù)據(jù)
sheet['C1'] = '2023-01-01'
sheet['C2'] = '2023-01-10'
# 使用DATEDIF函數(shù)計(jì)算C1和C2之間的天數(shù)差
sheet['C3'] = '=DATEDIF(C1, C2, "D")'
# 保存文件
wb.save('date_formula_example.xlsx')在上述代碼中，使用了Excel的DATEDIF函數(shù)計(jì)算了C1和C2之間的天數(shù)差。
4 注意事項(xiàng)
在寫入公式時(shí)，確保在公式字符串前面加上等號(hào)（=）。
請注意使用Excel支持的函數(shù)和語法。
樣式設(shè)置
在openpyxl中，可以通過設(shè)置樣式來美化Excel文件，包括設(shè)置字體、顏色、邊框等方面。以下是如何使用openpyxl進(jìn)行樣式設(shè)置的示例。
1 設(shè)置字體樣式
設(shè)置單元格的字體樣式，如粗體、斜體、字體顏色等。
from openpyxl import Workbook
from openpyxl.styles import Font
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入數(shù)據(jù)
sheet['A1'] = 'Hello, World!'
# 設(shè)置字體樣式
font_style = Font(size=14, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font_style
# 保存文件
wb.save('font_style_example.xlsx')
在上述代碼中，創(chuàng)建了一個(gè)字體樣式對(duì)象font_style，并將其應(yīng)用到單元格’A1’。
2 設(shè)置背景顏色
還可以設(shè)置單元格的背景顏色。
from openpyxl import Workbook
from openpyxl.styles import PatternFill
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入數(shù)據(jù)
sheet['A1'] = 'Colored Cell'
# 設(shè)置背景顏色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['A1'].fill = fill
# 保存文件
wb.save('background_color_example.xlsx')
在上述代碼中，創(chuàng)建了一個(gè)填充樣式對(duì)象fill，并將其應(yīng)用到單元格’A1’。
3 設(shè)置邊框
設(shè)置單元格的邊框樣式，包括左、右、上、下邊框。
from openpyxl import Workbook
from openpyxl.styles import Border, Side
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入數(shù)據(jù)
sheet['A1'] = 'Bordered Cell'
# 設(shè)置邊框
border_style = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
sheet['A1'].border = border_style
# 保存文件
wb.save('border_style_example.xlsx')
在上述代碼中，創(chuàng)建了一個(gè)邊框樣式對(duì)象border_style，并將其應(yīng)用到單元格’A1’。
實(shí)際應(yīng)用場景
openpyxl在實(shí)際項(xiàng)目中有著廣泛的應(yīng)用，特別是在需要處理Excel文件的數(shù)據(jù)報(bào)告、數(shù)據(jù)導(dǎo)出等場景。以下是一些實(shí)際應(yīng)用示例，展示openpyxl在真實(shí)項(xiàng)目中的用途。
1 數(shù)據(jù)報(bào)告生成
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入數(shù)據(jù)報(bào)告標(biāo)題
sheet['A1'] = '月度銷售報(bào)告'
sheet['A1'].font = Font(size=18, bold=True)
sheet['A1'].alignment = Alignment(horizontal='center')
# 寫入銷售數(shù)據(jù)
sales_data = [
('產(chǎn)品', '銷售額', '利潤'),
('A產(chǎn)品', 5000, 2000),
('B產(chǎn)品', 8000, 3500),
('C產(chǎn)品', 6000, 2800),
]
for row_idx, row_data in enumerate(sales_data, start=3):
for col_idx, value in enumerate(row_data, start=1):
sheet.cell(row=row_idx, column=col_idx, value=value)
# 保存文件
wb.save('sales_report.xlsx')
在這個(gè)示例中，使用openpyxl創(chuàng)建了一個(gè)銷售報(bào)告，包括標(biāo)題、銷售數(shù)據(jù)表格等。通過設(shè)置字體、居中對(duì)齊等樣式，使得報(bào)告看起來更加專業(yè)。
2 數(shù)據(jù)導(dǎo)出
from openpyxl import Workbook
# 創(chuàng)建一個(gè)Workbook對(duì)象
wb = Workbook()
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 寫入數(shù)據(jù)
data_to_export = [
['Name', 'Age', 'City'],
['Alice', 25, 'New York'],
['Bob', 30, 'London'],
['Charlie', 22, 'Tokyo'],
]
for row_idx, row_data in enumerate(data_to_export, start=1):
for col_idx, value in enumerate(row_data, start=1):
sheet.cell(row=row_idx, column=col_idx, value=value)
# 保存文件
wb.save('exported_data.xlsx')
在這個(gè)示例中，使用openpyxl創(chuàng)建了一個(gè)簡單的數(shù)據(jù)表格，并將其導(dǎo)出為Excel文件。這在數(shù)據(jù)導(dǎo)出和交換方面非常常見。
3 批量處理數(shù)據(jù)
from openpyxl import load_workbook
from openpyxl.styles import Font
# 加載已有的Excel文件
wb = load_workbook('existing_data.xlsx')
# 獲取活動(dòng)的Sheet
sheet = wb.active
# 在現(xiàn)有數(shù)據(jù)上追加新數(shù)據(jù)
new_data = [
['Eva', 28, 'Berlin'],
['David', 35, 'Paris'],
]
for row_data in new_data:
sheet.append(row_data)
# 設(shè)置標(biāo)題行的字體樣式
for cell in sheet['1']:
cell.font = Font(bold=True)
# 保存文件
wb.save('updated_data.xlsx')
在這個(gè)示例中，加載了一個(gè)已有的Excel文件，追加了新的數(shù)據(jù)，并設(shè)置了標(biāo)題行的字體樣式。這在批量處理數(shù)據(jù)的場景中非常實(shí)用。
總結(jié)
在總結(jié)中，深入探索了openpyxl這一強(qiáng)大的Python庫，用于處理Excel文件。首先了解了其基本概念，包括Workbook、Sheet和Cell，然后學(xué)習(xí)了如何讀取和寫入不同類型的數(shù)據(jù)。通過示例代碼，演示了如何處理圖表、圖片，以及使用公式進(jìn)行計(jì)算。在樣式設(shè)置方面，詳細(xì)介紹了如何設(shè)置字體、顏色、邊框等，以美化Excel文件中的數(shù)據(jù)。通過實(shí)際應(yīng)用示例，展示了openpyxl在數(shù)據(jù)報(bào)告生成、數(shù)據(jù)導(dǎo)出、批量處理數(shù)據(jù)等方面的廣泛應(yīng)用。
尤其值得強(qiáng)調(diào)的是，openpyxl的靈活性和功能豐富性使其成為處理Excel數(shù)據(jù)的理想選擇，適用于沒有安裝Microsoft Excel的環(huán)境中。
以上就是Python openpyxl庫處理Excel文件高級(jí)應(yīng)用技巧實(shí)例的詳細(xì)內(nèi)容，更多關(guān)于Python openpyxl處理Excel的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了基于Python爬取51cto博客頁面信息過程解析,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-08-08
Python中的二維數(shù)組實(shí)例(list與numpy.array)
下面小編就為大家分享一篇Python中的二維數(shù)組實(shí)例(list與numpy.array)，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2018-04-04
如何用Anaconda搭建虛擬環(huán)境并創(chuàng)建Django項(xiàng)目
在本篇文章里小編給大家整理了關(guān)于如何用Anaconda搭建虛擬環(huán)境并創(chuàng)建Django項(xiàng)目的相關(guān)文章，需要的朋友們可以跟著學(xué)習(xí)下。2020-08-08
這篇文章主要為大家詳細(xì)介紹了python編寫分類決策樹的代碼，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-12-12
這篇文章主要為大家介紹了python學(xué)習(xí)探尋WSGI?Application原理詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步，早日升職加薪2023-04-04
這篇文章主要介紹了Django 刪除upload_to文件的步驟，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-03-03
PyQt5實(shí)現(xiàn)暗黑風(fēng)格的計(jì)時(shí)器
這篇文章主要為大家詳細(xì)介紹了PyQt5實(shí)現(xiàn)暗黑風(fēng)格的計(jì)時(shí)器，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-07-07
Python內(nèi)建模塊collections實(shí)現(xiàn)特殊容器數(shù)據(jù)類型
collections模塊是Python的內(nèi)建模塊之一，它實(shí)現(xiàn)了特殊的容器數(shù)據(jù)類型，提供了Python內(nèi)建的數(shù)據(jù)類型dict、list、set、和tuple的高效替代選擇2023-06-06
Python3.6計(jì)劃在2016-12-16正式發(fā)布。截止目前，PSF 已經(jīng)發(fā)布了 9 個(gè)測試版本。在新版本正式發(fā)布之前，我們一起來體驗(yàn)一下都會(huì)有哪些重要的新特性。2016-12-12
Django之編輯時(shí)根據(jù)條件跳轉(zhuǎn)回原頁面的方法
今天小編就為大家分享一篇Django之編輯時(shí)根據(jù)條件跳轉(zhuǎn)回原頁面的方法，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2019-08-08