資料持久化 - Openpyxl 模組

Microsoft 的 Excel 是最流行的電子表格應用程式。它已使用超過 25 年。Excel 的較新版本使用Office Open XML (OOXML) 檔案格式。因此，可以透過其他程式設計環境訪問電子表格檔案。

OOXML 是一個 ECMA 標準檔案格式。Python 的openpyxl 包提供了讀取/寫入副檔名為 .xlsx 的 Excel 檔案的功能。

openpyxl 包使用的類命名法類似於 Microsoft Excel 術語。Excel 文件稱為工作簿，並以 .xlsx 副檔名儲存在檔案系統中。一個工作簿可以包含多個工作表。工作表呈現一個大型單元格網格，每個單元格都可以儲存值或公式。構成網格的行和列都有編號。列由字母 A、B、C、…、Z、AA、AB 等標識。行從 1 開始編號。

典型的 Excel 工作表如下所示：

pip 實用程式足以安裝 openpyxl 包。

pip install openpyxl

Workbook 類表示一個帶有一個空白工作表的新工作簿。我們需要啟用它以便可以向工作表中新增一些資料。

from openpyxl import Workbook
wb=Workbook()
sheet1=wb.active
sheet1.title='StudentList'

眾所周知，工作表中的單元格名為 ColumnNameRownumber 格式。因此，左上角單元格為 A1。我們將字串分配給此單元格，如下所示：

sheet1['A1']= 'Student List'

或者，使用工作表的cell() 方法，該方法使用行號和列號來標識單元格。呼叫單元格物件的 value 屬性來賦值。

cell1=sheet1.cell(row=1, column=1)
cell1.value='Student List'

在使用資料填充工作表後，透過呼叫工作簿物件的 save() 方法儲存工作簿。

wb.save('Student.xlsx')

此工作簿檔案在當前工作目錄中建立。

以下 Python 指令碼將一系列元組寫入工作簿文件。每個元組儲存學生的學號、年齡和分數。

from openpyxl import Workbook
wb = Workbook()
sheet1 = wb.active
sheet1.title='Student List'
sheet1.cell(column=1, row=1).value='Student List'
studentlist=[('RollNo','Name', 'age', 'marks'),(1,'Juhi',20,100), 
   (2,'dilip',20, 110) , (3,'jeevan',24,145)]
for col in range(1,5):
   for row in range(1,5):
      sheet1.cell(column=col, row=1+row).value=studentlist[row-1][col-1]
wb.save('students.xlsx')

工作簿 students.xlsx 儲存在當前工作目錄中。如果使用 Excel 應用程式開啟它，它將顯示如下：

openpyxl 模組提供load_workbook() 函式，該函式有助於讀取工作簿文件中的資料。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')

您現在可以訪問由行號和列號指定的任何單元格的值。

cell1=sheet1.cell(row=1, column=1)
print (cell1.value)
Student List

示例

以下程式碼使用工作表資料填充列表。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')
sheet1 = wb['Student List']
studentlist=[]
for row in range(1,5):
   stud=[]
for col in range(1,5):
   val=sheet1.cell(column=col, row=1+row).value
stud.append(val)
studentlist.append(tuple(stud))
print (studentlist)

輸出

[('RollNo', 'Name', 'age', 'marks'), (1, 'Juhi', 20, 100), (2, 'dilip', 20, 110), (3, 'jeevan', 24, 145)]

Excel 應用程式的一個非常重要的功能是公式。要將公式分配給單元格，請將其分配給包含 Excel 公式語法的字串。將 AVERAGE 函式分配給包含年齡的 C6 單元格。

sheet1['C6']= 'AVERAGE(C3:C5)'

openpyxl 模組具有Translate_formula() 函式，用於將公式複製到一個範圍內。以下程式在 C6 中定義 AVERAGE 函式，並將其複製到計算平均分數的 C7。

from openpyxl import load_workbook
wb=load_workbook('students.xlsx')

sheet1 = wb['Student List']
from openpyxl.formula.translate import Translator#copy formula
sheet1['B6']='Average'
sheet1['C6']='=AVERAGE(C3:C5)'
sheet1['D6'] = Translator('=AVERAGE(C3:C5)', origin="C6").translate_formula("D6")
wb.save('students.xlsx')

更改後的工作表現在如下所示：

列印頁面