Python openpyxl庫處理Excel文件高級應用技巧實例

更新時間：2024年01月03日 09:52:41 作者：濤哥聊Python

openpyxl是一個用于處理Excel文件的Python庫,它提供了豐富的功能,使得用戶能夠輕松地讀取、寫入和操作Excel文件,而不需要依賴于Microsoft Excel軟件,作為一個開源項目,openpyxl在Python生態(tài)系統(tǒng)中得到了廣泛的應用,成為處理Excel數據的首選工具之一

openpyxl的重要性和優(yōu)勢

跨平臺性： openpyxl可以在多個平臺上運行，包括Windows、Linux和MacOS，使得數據處理任務更加靈活和便捷。

無需Excel軟件： 與使用Excel軟件相比，openpyxl允許在沒有安裝Microsoft Excel的環(huán)境中進行Excel文件的讀寫和操作。

開放源代碼： openpyxl是一個開源項目，可以自由使用和修改，使得開發(fā)者能夠根據自己的需求對其進行定制和擴展。

活躍的社區(qū)支持： 由于其在開源社區(qū)中的活躍參與，openpyxl不斷更新和改進，確保與最新的Excel格式兼容，并提供最佳性能。

功能豐富： openpyxl提供了豐富的功能，包括但不限于讀取和寫入數據、創(chuàng)建圖表、設置樣式、執(zhí)行公式計算等，使其適用于各種復雜的Excel處理任務。

安裝openpyxl

使用pip（Python包管理工具）來安裝openpyxl。

pip install openpyxl

基本概念

1 Workbook（工作簿）

在openpyxl中，Workbook是一個Excel工作簿的抽象表示。它是整個Excel文件的最頂層對象，包含了所有的Sheet和相關數據。

示例代碼：創(chuàng)建Workbook

from openpyxl import Workbook

# 創(chuàng)建一個Workbook對象
wb = Workbook()

在這個例子中，使用Workbook類創(chuàng)建了一個新的Excel工作簿。

2 Sheet（工作表）

Sheet是工作簿中的一個單獨的工作表。工作簿可以包含一個或多個工作表。默認情況下，創(chuàng)建工作簿時會包含一個名為”Sheet”的工作表。

示例代碼：添加Sheet

# 獲取活動的Sheet
sheet = wb.active
# 創(chuàng)建一個名為"NewSheet"的Sheet
new_sheet = wb.create_sheet("NewSheet")

在這個例子中，通過active屬性獲取了活動的工作表，然后使用create_sheet方法創(chuàng)建了一個名為”NewSheet”的新工作表。

3 Cell（單元格）

Cell是工作表中的一個單元格，用于存儲數據。單元格由列字母和行號唯一標識，例如”A1″表示第一列第一行的單元格。

示例代碼：寫入數據到單元格

# 向單元格寫入數據
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'

在這個例子中，使用單元格坐標（’A1’和’B1’）向工作表中的特定單元格寫入了數據。

讀取數據

在openpyxl中，可以使用不同的方法來從已有的Excel文件中讀取數據，包括文本、數字和日期等不同數據類型。

1 打開Excel文件

首先，需要使用load_workbook函數打開已有的Excel文件。

from openpyxl import load_workbook
# 打開Excel文件
wb = load_workbook('example.xlsx')
# 獲取活動的Sheet
sheet = wb.active

2 讀取文本數據

對于包含文本數據的單元格，可以直接通過value屬性讀取。

# 讀取文本數據
text_data = sheet['A1'].value
print("文本數據:", text_data)

3 讀取數字數據

對于包含數字數據的單元格，同樣可以通過value屬性讀取。

# 讀取數字數據
number_data = sheet['B1'].value
print("數字數據:", number_data)

4 讀取日期數據

如果單元格包含日期數據，可以通過value屬性讀取日期對象。

# 讀取日期數據
date_data = sheet['C1'].value
print("日期數據:", date_data)

在讀取日期數據時，openpyxl會將其轉換為Python的datetime對象。

寫入數據

在openpyxl中，可以使用不同的方法向Excel文件中寫入不同類型的數據，同時還可以進行一些樣式設置和合并單元格等操作。

1 寫入文本和數字數據

對于文本和數字數據，可以直接在單元格中使用等號賦值。

# 寫入文本和數字數據
sheet['A1'] = 'Hello'
sheet['B1'] = 123

2 寫入日期數據

對于日期數據，可以使用Python的datetime對象。

from datetime import datetime

# 寫入日期數據
sheet['C1'] = datetime(2023, 1, 1)

3 樣式設置

openpyxl可以對單元格進行樣式設置，包括字體、顏色和邊框等。

from openpyxl.styles import Font, PatternFill, Border
# 設置字體樣式
sheet['A1'].font = Font(bold=True, color='FF0000')
# 設置背景顏色
sheet['A1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
# 設置邊框
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中查看結果。

# 保存文件
wb.save('write_example.xlsx')

圖表和圖片

在openpyxl中，可以使用圖表和圖片來更生動地展示數據和信息。以下是如何處理圖表和圖片的示例。

1 添加圖表

首先，創(chuàng)建一個簡單的柱狀圖，并將其添加到Excel文件中。

from openpyxl.chart import BarChart, Reference

# 創(chuàng)建柱狀圖對象
chart = BarChart()

# 設置圖表數據范圍
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' 為實際圖片的路徑，并將圖片添加到Excel文件的指定位置。

3 設置圖表和圖片屬性

還可以設置圖表和圖片的各種屬性，如位置、大小、標題等。

# 設置圖表位置和大小
chart.x = 50
chart.y = 100
chart.width = 300
chart.height = 200

# 設置圖片位置和大小
img.width = 100
img.height = 100
img.anchor = 'F4'

4 保存文件

最后，需要保存Workbook，以便在Excel中查看結果。

# 保存文件
wb.save('chart_and_image_example.xlsx')

數據篩選和排序

在openpyxl中，可以使用篩選和排序功能對Excel表格中的數據進行進一步的處理。以下是如何在openpyxl中實現數據篩選和排序的示例。

1 數據篩選

使用AutoFilter來實現數據篩選。下面是一個示例，演示如何按條件篩選數據。

from openpyxl import Workbook
# 創(chuàng)建一個Workbook對象
wb = Workbook()
# 獲取活動的Sheet
sheet = wb.active
# 寫入示例數據
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
# 按條件篩選數據
sheet.auto_filter.add_filter_column(1, ["Alice", "David"])
# 保存文件
wb.save('filter_example.xlsx')

在上述代碼中，使用auto_filter屬性啟用了AutoFilter，并通過add_filter_column方法按條件篩選了數據。

2 數據排序

openpyxl也提供了對數據進行排序的功能。以下是一個示例，演示如何按指定列對數據進行升序排序。

from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd
# 創(chuàng)建一個Workbook對象
wb = Workbook()
# 獲取活動的Sheet
sheet = wb.active
# 寫入示例數據
sheet['A1'] = 'Name'
sheet['B1'] = 'Score'
data = [
    ('Alice', 85),
    ('Bob', 92),
    ('Charlie', 78),
    ('David', 95),
    ('Eva', 88)
]
for row in data:
    sheet.append(row)
# 將數據轉換為DataFrame
df = pd.DataFrame(data, columns=['Name', 'Score'])
# 按Score列升序排序
df.sort_values(by='Score', inplace=True)
# 將排序后的數據寫回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庫將數據轉換為DataFrame，然后使用sort_values方法按指定列（這里是’Score’列）進行升序排序，最后將排序后的數據寫回Excel。

公式計算

在openpyxl中，可以使用公式對Excel文件中的數據進行計算。以下是如何使用公式的示例，以及一些注意事項。

1 基本公式

首先，可以在單元格中使用基本的Excel函數進行計算。

from openpyxl import Workbook
# 創(chuàng)建一個Workbook對象
wb = Workbook()
# 獲取活動的Sheet
sheet = wb.active
# 寫入示例數據
sheet['A1'] = 10
sheet['A2'] = 20
# 使用SUM函數計算A1和A2的總和
sheet['A3'] = '=SUM(A1:A2)'
# 保存文件
wb.save('formula_example.xlsx')

在上述代碼中，使用了Excel的SUM函數計算了A1和A2的總和。

2 復雜公式

還可以編寫更復雜的公式，涉及多個單元格和函數。

from openpyxl import Workbook
# 創(chuàng)建一個Workbook對象
wb = Workbook()
# 獲取活動的Sheet
sheet = wb.active
# 寫入示例數據
sheet['B1'] = 5
sheet['B2'] = 10
sheet['B3'] = 15
# 使用AVERAGE函數計算B1到B3的平均值
sheet['B4'] = '=AVERAGE(B1:B3)'
# 保存文件
wb.save('complex_formula_example.xlsx')

在上述代碼中，使用了Excel的AVERAGE函數計算了B1到B3的平均值。

3 計算日期差

如果數據涉及日期，可以使用日期函數計算日期之間的差值。

from openpyxl import Workbook
# 創(chuàng)建一個Workbook對象
wb = Workbook()
# 獲取活動的Sheet
sheet = wb.active
# 寫入日期數據
sheet['C1'] = '2023-01-01'
sheet['C2'] = '2023-01-10'
# 使用DATEDIF函數計算C1和C2之間的天數差
sheet['C3'] = '=DATEDIF(C1, C2, "D")'
# 保存文件
wb.save('date_formula_example.xlsx')

在上述代碼中，使用了Excel的DATEDIF函數計算了C1和C2之間的天數差。

4 注意事項

在寫入公式時，確保在公式字符串前面加上等號（=）。
請注意使用Excel支持的函數和語法。

樣式設置

在openpyxl中，可以通過設置樣式來美化Excel文件，包括設置字體、顏色、邊框等方面。以下是如何使用openpyxl進行樣式設置的示例。

1 設置字體樣式

設置單元格的字體樣式，如粗體、斜體、字體顏色等。

from openpyxl import Workbook
from openpyxl.styles import Font

# 創(chuàng)建一個Workbook對象
wb = Workbook()

# 獲取活動的Sheet
sheet = wb.active

# 寫入數據
sheet['A1'] = 'Hello, World!'

# 設置字體樣式
font_style = Font(size=14, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font_style

# 保存文件
wb.save('font_style_example.xlsx')

在上述代碼中，創(chuàng)建了一個字體樣式對象font_style，并將其應用到單元格’A1’。

2 設置背景顏色

還可以設置單元格的背景顏色。

from openpyxl import Workbook
from openpyxl.styles import PatternFill

# 創(chuàng)建一個Workbook對象
wb = Workbook()

# 獲取活動的Sheet
sheet = wb.active

# 寫入數據
sheet['A1'] = 'Colored Cell'

# 設置背景顏色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['A1'].fill = fill

# 保存文件
wb.save('background_color_example.xlsx')

在上述代碼中，創(chuàng)建了一個填充樣式對象fill，并將其應用到單元格’A1’。

3 設置邊框

設置單元格的邊框樣式，包括左、右、上、下邊框。

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

# 創(chuàng)建一個Workbook對象
wb = Workbook()

# 獲取活動的Sheet
sheet = wb.active

# 寫入數據
sheet['A1'] = 'Bordered Cell'

# 設置邊框
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)建了一個邊框樣式對象border_style，并將其應用到單元格’A1’。

實際應用場景

openpyxl在實際項目中有著廣泛的應用，特別是在需要處理Excel文件的數據報告、數據導出等場景。以下是一些實際應用示例，展示openpyxl在真實項目中的用途。

1 數據報告生成

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment

# 創(chuàng)建一個Workbook對象
wb = Workbook()

# 獲取活動的Sheet
sheet = wb.active

# 寫入數據報告標題
sheet['A1'] = '月度銷售報告'
sheet['A1'].font = Font(size=18, bold=True)
sheet['A1'].alignment = Alignment(horizontal='center')

# 寫入銷售數據
sales_data = [
    ('產品', '銷售額', '利潤'),
    ('A產品', 5000, 2000),
    ('B產品', 8000, 3500),
    ('C產品', 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')

在這個示例中，使用openpyxl創(chuàng)建了一個銷售報告，包括標題、銷售數據表格等。通過設置字體、居中對齊等樣式，使得報告看起來更加專業(yè)。

2 數據導出

from openpyxl import Workbook

# 創(chuàng)建一個Workbook對象
wb = Workbook()

# 獲取活動的Sheet
sheet = wb.active

# 寫入數據
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')

在這個示例中，使用openpyxl創(chuàng)建了一個簡單的數據表格，并將其導出為Excel文件。這在數據導出和交換方面非常常見。

3 批量處理數據

from openpyxl import load_workbook
from openpyxl.styles import Font

# 加載已有的Excel文件
wb = load_workbook('existing_data.xlsx')

# 獲取活動的Sheet
sheet = wb.active

# 在現有數據上追加新數據
new_data = [
    ['Eva', 28, 'Berlin'],
    ['David', 35, 'Paris'],
]

for row_data in new_data:
    sheet.append(row_data)

# 設置標題行的字體樣式
for cell in sheet['1']:
    cell.font = Font(bold=True)

# 保存文件
wb.save('updated_data.xlsx')

在這個示例中，加載了一個已有的Excel文件，追加了新的數據，并設置了標題行的字體樣式。這在批量處理數據的場景中非常實用。

總結

在總結中，深入探索了openpyxl這一強大的Python庫，用于處理Excel文件。首先了解了其基本概念，包括Workbook、Sheet和Cell，然后學習了如何讀取和寫入不同類型的數據。通過示例代碼，演示了如何處理圖表、圖片，以及使用公式進行計算。在樣式設置方面，詳細介紹了如何設置字體、顏色、邊框等，以美化Excel文件中的數據。通過實際應用示例，展示了openpyxl在數據報告生成、數據導出、批量處理數據等方面的廣泛應用。

尤其值得強調的是，openpyxl的靈活性和功能豐富性使其成為處理Excel數據的理想選擇，適用于沒有安裝Microsoft Excel的環(huán)境中。

以上就是Python openpyxl庫處理Excel文件高級應用技巧實例的詳細內容，更多關于Python openpyxl處理Excel的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

基于Python爬取51cto博客頁面信息過程解析
這篇文章主要介紹了基于Python爬取51cto博客頁面信息過程解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2020-08-08
Python中的二維數組實例(list與numpy.array)
下面小編就為大家分享一篇Python中的二維數組實例(list與numpy.array)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2018-04-04
如何用Anaconda搭建虛擬環(huán)境并創(chuàng)建Django項目
在本篇文章里小編給大家整理了關于如何用Anaconda搭建虛擬環(huán)境并創(chuàng)建Django項目的相關文章，需要的朋友們可以跟著學習下。
2020-08-08
python編寫分類決策樹的代碼
這篇文章主要為大家詳細介紹了python編寫分類決策樹的代碼，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2017-12-12
python帶你探尋WSGI?Application原理
這篇文章主要為大家介紹了python學習探尋WSGI?Application原理詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2023-04-04
Django 刪除upload_to文件的步驟
這篇文章主要介紹了Django 刪除upload_to文件的步驟，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2020-03-03
PyQt5實現暗黑風格的計時器
這篇文章主要為大家詳細介紹了PyQt5實現暗黑風格的計時器，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2019-07-07
Python內建模塊collections實現特殊容器數據類型
collections模塊是Python的內建模塊之一，它實現了特殊的容器數據類型，提供了Python內建的數據類型dict、list、set、和tuple的高效替代選擇
2023-06-06
Python3.6正式版新特性預覽
Python3.6計劃在2016-12-16正式發(fā)布。截止目前，PSF 已經發(fā)布了 9 個測試版本。在新版本正式發(fā)布之前，我們一起來體驗一下都會有哪些重要的新特性。
2016-12-12
Django之編輯時根據條件跳轉回原頁面的方法
今天小編就為大家分享一篇Django之編輯時根據條件跳轉回原頁面的方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
2019-08-08

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片