使用openpyxl處理表格資料

導讀	你們都一定在生活中的某個時候使用過 Excel，並且一定覺得需要自動化在本教程中，我們將學習如何使用 Python 處理 Excel 一些重複或乏味的任務。

Openpyxl 是一個 Python 庫，它提供了各種使用 Python 與 Excel 檔案互動的方法。它允許讀、寫、算術運算、繪製圖形等操作。

在 8中安裝方式如下：

[root@localhost ~]# yum -y install python3-openpyxl

如果要讀取 Excel 檔案，必須使用  load_workbook() 方法開啟電子表格。之後可以使用  active 來選擇第一個可用的工作表，並透過傳遞 row 和 column 引數使用  cell() 屬性來選擇單元格。value 屬性返回特定單元格的值。請參閱下面的示例以獲得更好的理解。

注意：第一行或第一列整數是 1，而不是 0。

下面的資料可以儲存在excel表格檔案中，用來做下面的實驗，檔名稱為 wb.xlsx：

Name	Course	Branch	Semester
Ankit	B.Tech	CSE	4
Rahul	M.Tech	CSE	2
Priya	MBA	HR	3
Nikhil	B.Tech	CSE	4
Nisha	B.Tech	Biotech	5

 

[root@localhost data]# python3
# 匯入openpyxl庫
>>> import openpyxl
# path變數指定表格檔案的位置
>>> path = "/data/wb.xlsx"
# 載入表格檔案
>>> wb_obj = openpyxl.load_workbook(path)
# 啟用預設的工作表
>>> sheet_obj = wb_obj.active
# 選擇第一行第一列的單元格，也就是A1單元格
>>> cell_obj = sheet_obj.cell(row = 1, column = 1)
# 列印單元格所屬位置
>>> print(cell_obj)
<cell 'Sheet1'.A1>
# 列印A1單元格的值
>>> print(cell_obj.value)
Name

從多個單元格中讀取資料的方式有兩種。

方法一：我們可以分別使用  max_row 和  max_column 獲得總行數和總列數。我們可以在 for 迴圈中使用這些值來根據情況獲取所需行或列或任何單元格的值。讓我們看看如何獲取第一列和第一行的值。

# 使用max_row獲取工作表有多少行的資料
>>> row = sheet_obj.max_row
# 使用max_column獲取工作表有多少列的資料
>>> column = sheet_obj.max_column
# 列印行數
>>> print("Total Rows: ", row)
Total Rows:  6
# 列印列數
>>> print("Total Cols: ", column)
Total Cols:  4
# 使用for迴圈列印第一列資料
>>> for i in range(1, row + 1):
...   cell_obj = sheet_obj.cell(row = i, column = 1)
...   print(cell_obj.value)
... 
Name
Ankit
Rahul
Priya
Nikhil
Nisha
# 使用for迴圈列印第二條資料：
>>> for i in range(1, column + 1):
...   cell_obj = sheet_obj.cell(row = 3, column = i)
...   print(cell_obj.value, end = " ")
... 
Rahul M.Tech CSE 2

方法二：我們還可以使用單元格名稱從多個單元格中讀取。這可以看作是Python的列表切片。

# 選中A1到B6的單元格
>>> cell_obj = sheet_obj['A1':'B6']
>>> 
# for玄幻列印兩列的資料
>>> for cell1, cell2 in cell_obj:
...   print(cell1.value, cell2.value)
... 
Name Course
Ankit B.Tech
Rahul M.Tech
Priya MBA
Nikhil B.Tech
Nisha B.Tech

首先，讓我們建立一個新的表格，然後我們將一些資料寫入新建立的檔案。可以使用  Workbook() 方法建立一個空的表格。讓我們看看下面的例子。

[root@localhost data]# python3
# 匯入openpyxl庫裡面的Workbook
>>> from openpyxl import Workbook
>>> 
# 建立空的工作簿
>>> workbook = Workbook()
>>> 
# 儲存檔案
>>> workbook.save(filename="/data/sample.xlsx")
>>>

建立一個空檔案後，讓我們看看如何使用 Python 向其中新增一些資料。要首先新增資料，我們需要選擇活動工作表，然後使用 cell() 方法，我們可以透過傳遞行號和列號作為其引數來選擇任何特定的單元格。我們也可以使用單元格名稱進行書寫。

# 獲取啟用的工作表
>>> sheet = workbook.active
>>> 
# 為A1新增Hello值
>>> c1 = sheet.cell(row = 1, column = 1, value = 'Hello')
# 為B1新增World值
>>> C2 = sheet.cell(row = 1, column = 2, value = 'World')
>>> 
# 為A2新增Welcome值
>>> C3  = sheet['A2'].value = 'Welcome'
# 為B2新增Everyone值
>>> C4 = sheet['B2'].value = 'Everyone'
>>> 
# 儲存到檔案
>>> workbook.save("/data/sample.xlsx")

在上面的示例中，您將看到每次嘗試寫入表格時，現有資料都會被覆蓋，並且該檔案將另存為新檔案。發生這種情況是因為 Workbook() 方法總是建立一個新的工作簿檔案物件。要寫入現有工作簿，必須使用  load_workbook() 方法開啟檔案。

[root@localhost data]# python3
>>> import openpyxl
>>> 
# 使用load_workbook載入檔案
>>> wb = openpyxl.load_workbook("/data/sample.xlsx")
>>> 
>>> sheet = wb.active
>>> 
>>> c = sheet['A3']
>>> 
>>> c.value = 'New Data'
>>> 
>>> wb.save("/data/sample.xlsx")

我們還可以使用  append() 方法在工作表的末尾附加多個資料。

# 定義資料
>>> data = (
... (1,2,3),
... (4,5,6)
... )
>>> 
# 使用for迴圈將內容附加到工作表中
>>> for row in data:
...   sheet.append(row)
... 
>>> wb.save('/data/sample.xlsx')

可以透過在電子表格的特定單元格中鍵入公式來執行算術運算。例如，如果我們想求和，則使用 Excel 檔案的  =Sum() 公式。

[root@localhost data]# python3
>>> import openpyxl
>>> 
# 建立新工作簿
>>> wb = openpyxl.Workbook()
>>> 
>>> sheet = wb.active
>>> 
# 在單元格中寫入資料
>>> sheet['A1'] = 200
>>> sheet['A2'] = 300
>>> sheet['A3'] = 400
>>> sheet['A4'] = 500
>>> sheet['A5'] = 619
# 在A7單元格求和
>>> sheet['A7'] = '=SUM(A1:A5)'
>>> 
# 儲存檔名稱為sum.xlsx
>>> wb.save('/data/sum.xlsx')

工作表物件具有控制行高和列寬的  row_dimensions 和  column_dimensions 屬性。工作表的  row_dimensions 和  column_dimensions 是類似字典的值； row_dimensions 包含  RowDimension 物件，  column_dimensions 包含  ColumnDimension 物件。在  row_dimensions 中，可以使用行號（在本例中為 1 或 2）訪問其中一個物件。在  column_dimensions 中，可以使用列的字母（在本例中為 A 或 B）訪問其中一個物件。

[root@localhost data]# python3
# 匯入模組
>>> import openpyxl
>>> 
# 建立新工作簿
>>> wb = openpyxl.Workbook()
>>> 
>>> sheet = wb.active
>>> 
# 向A1單元格寫入資料
>>> sheet.cell(row = 1, column = 1, value = ' Hello ')
<cell 'Sheet'.A1>
# 向B2單元格寫入資料
>>> sheet['B2'].value = ' Everyone '
>>> 
# 設定第一行單元格高度為45
>>> sheet.row_dimensions[1].height = 45
>>> 
# 設定B列單元格寬度為70
>>> sheet.column_dimensions['B'].width = 70
>>> 
>>> wb.save('/data/h_and_w.xlsx')

可以使用  merge_cells() 方法將多個單元格合併為單個單元格。

[root@localhost data]# python3
>>> import openpyxl
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
# 合併A2到D4單元格，並新增資料
>>> sheet.merge_cells('A2:D4')
>>> sheet['A2'].value = 'Twelve cells join together.'
>>> 
# 合併C6到D6單元格，並新增資料
>>> sheet.merge_cells('C6:D6')
>>> sheet['C6'].value = 'Two merge cells.'
>>> 
>>> wb.save('/data/merge_cells.xlsx')

如要取消合併單元格，請用  unmerge_cells() 方法。

# 取消合併A2到D4單元格
>>> sheet.unmerge_cells('A2:D4')
>>> 
# 取消合併C6到D6單元格
>>> sheet.unmerge_cells('C6:D6')
>>> 
>>> wb.save('/data/merge_cells.xlsx')

要自定義單元格中的字型樣式，重點是從  openpyxl.styles 模組匯入  Font() 函式。

[root@localhost data]# python3
>>> import openpyxl
# 從openpyxl.styles匯入Font函式
>>> from openpyxl.styles import Font
>>> 
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 為A1單元格新增內容，並設定字型大小為24
>>> sheet['A1'].value = 'Linuxprobe.com'
>>> sheet['A1'].font = Font(size = 24)
# B2單元格新增內容，設定字型大小24，斜體
>>> sheet.cell(row = 2, column = 2, value = "Linuxprobe.com").font = Font(size = 24, italic = True)
# C3單元格新增內容，設定字型大小24，粗體
>>> sheet.cell(row = 3, column = 3, value ="Linuxprobe.com").font = Font(size = 24, bold = True)
# D4單元格新增內容，設定字型大小24，設定字型樣式
>>> sheet.cell(row = 4, column = 4, value = "Linuxprobe.com").font = Font(size = 24, name = 'Times New Roman')
>>> wb.save('/data/font_styles.xlsx')

要在 Excel 表格上繪製圖表，首先要建立特定圖表類（即 BarChart、LineChart 等）的圖表物件。建立圖表物件後，在其中插入資料，最後將該圖表物件新增到工作表中。

[root@localhost data]# python3
>>> import openpyxl
# 匯入圖表庫
>>> from openpyxl.chart import BarChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 使用迴圈為工作表附加內容
>>> for i in range(10):
...   sheet.append([i])
... 
# 為圖表建立資料
>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)
#建立圖表例項
>>> chart = BarChart()
# 向條形圖新增資料
>>> chart.add_data(values)
# 新增圖表標題
>>> chart.title = " BAR-CHART "
# 新增X座標標題
>>> chart.x_axis.title = " X_AXIS "
# 新增Y座標標題
>>> chart.y_axis.title = " Y_AXIS "
>>> 
# 工作表的E2單元格新增圖表
>>> sheet.add_chart(chart, 'E2')
>>> 
>>> wb.save("/data/charts.xlsx")

下面例項，是新增折線圖：

[root@localhost data]# python3
>>> import openpyxl
# 匯入圖表庫
>>> from openpyxl.chart import LineChart, Reference
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>> 
# 使用迴圈為工作表附加內容
>>> for i in range(10):
...   sheet.append([i])
... 
# 為圖表建立資料
>>> values = Reference(sheet, min_col=1,min_row=1,max_col=1,max_row=10)
#建立圖表例項
>>> chart = LineChart()
# 向折現圖新增資料
>>> chart.add_data(values)
# 新增圖表標題
>>> chart.title = " LINE-CHART "
# 新增X座標標題
>>> chart.x_axis.title = " X_AXIS "
# 新增Y座標標題
>>> chart.y_axis.title = " Y_AXIS "
>>> 
# 工作表的E2單元格新增圖表
>>> sheet.add_chart(chart, 'E2')
>>> 
>>> wb.save("/data/charts.xlsx")

為了在我們的工作表中匯入影像，我們將使用  openpyxl.drawing.image.Image。該方法是在 pillow庫中找到的 PIL.Image 方法。因此，必須安裝  python3-pillow庫才能使用此方法。

[root@localhost data]# yum -y install python3-pillow
[root@localhost data]# python3
>>> import openpyxl
# 匯入Image函式
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = openpyxl.Workbook()
>>> sheet = wb.active
>>>
# 向工作表第一行新增內容
>>> sheet.append(["abc",10,"def",20])
# 新增圖片
>>> img = Image("/data/banner1.jpg")
>>> 
>>> sheet.add_image(img, 'A2')
>>> 
>>> wb.save("/data/images.xlsx")

本文講述瞭如何使用Python和openpyxl庫讀取和寫入Excel表格資料、計算、單元格設定、繪製圖表和新增突變等操作。

本文原創地址：