如何使用 Python openpyxl 套件驗證 Excel 資料？openpyxl DataValidation 物件介紹

Python openpyxl 套件中的 Excel 資料驗證物件 DataValidation

Pythonopenpyxl套件中的DataValidation類別，屬於模組openpyxl.worksheet.datavalidation，用於表示與 Excel 儲存格相關的資料驗證。一個資料驗證可以針對一個 Excel 儲存格，也可以針對一個 Excel 範圍，Excel 工作表所包含的資料驗證，可通過openpyxl套件的Worksheet物件的data_validations變數進行檢視，具體可以檢視取得和設定工作表中的資料驗證一段。

使用 Python openpyxl 套件的 DataValidation 物件建立 Excel 資料驗證

你可以使用 Pythonopenpyxl套件的DataValidation物件的建構子，建立新的 Excel 資料驗證，然後通過Worksheet物件將其新增到 Excel 工作表中，具體可以檢視為工作表新增資料驗證一段。

DataValidation(type=None, formula1=None, formula2=None, showErrorMessage=False, showInputMessage=False, showDropDown=False, allowBlank=False, sqref=(), promptTitle=None, errorStyle=None, error=None, prompt=None, errorTitle=None, imeMode=None, operator=None, allow_blank=None)

type 參數

type參數是一個字串，用於指示將 Excel 儲存格中的內容作為哪種資料型別進行驗證，可以是以下取值之一，'whole'（整數），'decimal'（小數），'list'（內容需要存在於串列中），'date'（日期），'time'（時間），'textLength'（內容的長度），'custom'（根據formula1參數給出的公式進行驗證）。如果type參數為None，則允許輸入任何型別的資料。

formula1，formula2 參數

formula1參數和formula2參數是兩個表示 Excel 公式的字串，這些公式被用於資料驗證，比如，要判斷數值是否在3和10之間，那麽formula1應該為'3'，formula2應該為'10'。這裏需要指出，一些資料驗證僅會用到參數formula1所表示的公式。

如果type參數為'list'，那麽formula1表示的是串列中的項，兩個項之間需要使用逗號進行分隔，所有項需要使用雙引號括住，比如'"是,否,不確定"'。

showErrorMessage 參數

showErrorMessage參數用於設定在 Excel 儲存格中的內容未通過資料驗證時，是否顯示相關的錯誤資訊。預設值為False，不顯示錯誤資訊。

showInputMessage 參數

showInputMessage參數用於設定在選取 Excel 儲存格時，是否顯示相關提示資訊以提示使用者如何進行輸入。預設值為False，不顯示提示資訊。

showDropDown 參數

showDropDown參數用於設定在選取 Excel 儲存格時，是否顯示清單方塊，該參數在type參數為'list'時有效。在一些 Office 軟體中，showDropDown參數為False表示顯示清單方塊。

allowBlank，allow_blank 參數

allowBlank參數和allow_blank參數用於設定是否允許 Excel 儲存格為空。預設值為False，不允許為空。

sqref 參數

sqref參數用於設定對哪些 Excel 儲存格進行資料驗證，該參數可以是某個儲存格或範圍的位址，也可以是一個MultiCellRange物件或包含CellRange物件的 Python 元組。

promptTitle 參數

promptTitle參數是在選取 Excel 儲存格時，顯示的提示資訊的標題。

errorStyle 參數

errorStyle參數表示在 Excel 儲存格中的內容未通過資料驗證時執行的操作，可以是以下取值之一，'stop'（輸入的內容會被復原），'warning'（顯示警告），'information'（顯示資訊）。

error 參數

error參數是在 Excel 儲存格中的內容未通過資料驗證時，顯示的錯誤資訊的內容。

prompt 參數

prompt參數是在選取 Excel 儲存格時，顯示的提示資訊的內容。

errorTitle 參數

errorTitle參數是在 Excel 儲存格中的內容未通過資料驗證時，顯示的錯誤資訊的標題。

imeMode 參數

imeMode參數是一個表示輸入法模式的字串，他將在使用者編輯 Excel 儲存格時自動切換輸入法，imeMode可以是以下取值之一，'noControl'（不對 IME 進行任何控製），'off'（關閉），'on'（開啟），'disabled'（停用），'hiragana'（平假名），'fullKatakana'（全形片假名），'halfKatakana'（半形片假名），'fullAlpha'（全形字母數值），'halfAlpha'（半形字母數值），'fullHangul'（全形韓語），'halfHangul'（半形韓語）。

需要指出的是，並非所有的 Office 都會支援輸入法的自動切換功能，該功能是否有效可能會與其他設定相關。

operator 參數

operator參數是一個表示運算方式的字串，他與formula1，formula2兩個參數一起完成資料的驗證，可以是以下取值之一，'between'（在某個範圍之間），'notBetween'（不在某個範圍之間），'equal'（等於），'notEqual'（不等於），'lessThan'（小於），'lessThanOrEqual'（小於等於），'greaterThan'（大於），'greaterThanOrEqual'（大於等於）。

使用 Python openpyxl 套件的 Worksheet 物件取得和設定 Excel 工作表中的資料驗證

Pythonopenpyxl套件的Worksheet物件的data_validations變數是一個DataValidationList物件，該物件的dataValidation變數是一個包含了 Excel 工作表中所有資料驗證的 Python 串列，通過此 Python 串列可以取得或設定某個資料驗證。

# 開啟 Data.xlsx 中的工作表 Students
from openpyxl import load_workbook
workbook = load_workbook('Data.xlsx')
worksheet = workbook['Students']


dvs = worksheet.data_validations.dataValidation
# 顯示 Excel 工作表中所有的資料驗證
for dv in dvs:
	print(dv)


# 修改第二個資料驗證
dv = dvs[1]
dv.errorTitle = '請選擇一項'
dv.error = '只能選擇男或女'
workbook.save('Save.xlsx')

<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [A1:A1002]>, showDropDown=False, showInputMessage=False, showErrorMessage=True, allowBlank=True, errorTitle=None, error=None, promptTitle=None, prompt=None, type='textLength', errorStyle='stop', imeMode=None, operator='between', formula1='2', formula2='4'
<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [B1:B1002]>, showDropDown=False, showInputMessage=False, showErrorMessage=True, allowBlank=True, errorTitle=None, error=None, promptTitle=None, prompt=None, type='list', errorStyle='stop', imeMode=None, operator='equal', formula1='"男,女"', formula2='0'

使用 Python openpyxl 套件的 Worksheet 物件為 Excel 工作表新增資料驗證

通過 Pythonopenpyxl套件的Worksheet物件的add_data_validation方法，或者Worksheet物件的data_validations變數的append方法，可以為 Excel 工作表新增一個資料驗證。

worksheet.add_data_validation(data_validation)
worksheet.data_validations.append(data_validation)

data_validation 參數

data_validation參數是一個openpyxlDataValidation物件，表示被新增的 Excel 資料驗證。

# …
# 開啟 Data.xlsx 中的工作表 Students
from openpyxl import load_workbook
wb = load_workbook('Data.xlsx')
ws = wb['Students']


from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.worksheet.cell_range import MultiCellRange, CellRange


# 為 C1:C1001 新增資料驗證，只能輸入在 0 之間 100 的整數
newDV = DataValidation(
	'whole', 0, 100,
	sqref='C1:C1001', imeMode='off', operator='between'
	)
ws.add_data_validation(newDV)


# 為 D1:D2，D4:D5 兩個範圍新增資料驗證，內容長度需要小於等於 5
ws.data_validations.append(DataValidation(
	'textLength', 5,
	sqref=(CellRange('D1:D2'), CellRange('D4:D5')), operator='lessThanOrEqual'
	))


# 為 E1:E2，E4:E5 兩個範圍新增資料驗證，小數需要大於 1.5
ws.add_data_validation(DataValidation(
	'decimal', 1.5,
	sqref=MultiCellRange(['E1:E2', 'E4:E5']), operator='greaterThan'
	))


wb.save('Add.xlsx')

程式碼

src/zh-hant/openpyxl/data/validations·codebeatme/office-programming·GitHub