使用Python在Excel工作表中設置數(shù)據(jù)驗證
引言
在企業(yè)數(shù)據(jù)管理和報表系統(tǒng)中，數(shù)據(jù)的準確性和規(guī)范性至關重要。無論是員工信息錄入、財務數(shù)據(jù)填報還是庫存信息維護，都需要確保用戶輸入的數(shù)據(jù)符合業(yè)務規(guī)則。手動在 Excel 中設置數(shù)據(jù)驗證雖然可行，但當需要批量處理多個文件或統(tǒng)一驗證規(guī)則時，效率低下且容易遺漏。通過 Python 程序自動化設置 Excel 數(shù)據(jù)驗證，不僅可以快速批量處理，還能保證規(guī)則的一致性和可追溯性。
本文將使用 Free Spire.XLS for Python 演示如何在 Excel 工作表中設置多種類型的數(shù)據(jù)驗證，包括下拉列表、整數(shù)范圍、小數(shù)范圍、日期區(qū)間、文本長度和時間范圍等，并結合實際業(yè)務場景幫助你理解數(shù)據(jù)驗證的應用價值。
本文使用的方法需要用到 Free Spire.XLS for Python，可通過 pip 安裝：
pip install spire.xls.free
1. 初始化工作簿和工作表
首先創(chuàng)建一個新的 Excel 工作簿，并獲取第一個工作表用于設置數(shù)據(jù)驗證：
from spire.xls import * from spire.xls.common import * workbook = Workbook() sheet = workbook.Worksheets[0] sheet.Name = "員工信息錄入" sheet.Range["A1"].Text = "所屬部門" sheet.Range["B1"].Text = "員工年齡" sheet.Range["C1"].Text = "績效得分" sheet.Range["D1"].Text = "入職日期" sheet.Range["E1"].Text = "員工工號" sheet.Range["F1"].Text = "上班時間"
操作說明：
這里新建了一個 Excel 工作簿并獲取第一個工作表，命名為"員工信息錄入"。我們在第一行設置了六個字段的表頭，后續(xù)將針對每個字段設置不同的數(shù)據(jù)驗證規(guī)則，確保錄入數(shù)據(jù)的規(guī)范性。
2. 下拉列表驗證（部門選擇）
在實際業(yè)務中，員工所屬部門通常是固定的幾個選項，例如"人事部""財務部""技術部""市場部"。通過下拉列表驗證，可以避免用戶輸入錯誤的部門名稱，確保數(shù)據(jù)的統(tǒng)一性。
sheet.Range["A2"].Text = "可選部門：" sheet.Range["A3"].Text = "人事部" sheet.Range["A4"].Text = "財務部" sheet.Range["A5"].Text = "技術部" sheet.Range["A6"].Text = "市場部" dept_cell = sheet.Range["B2"] dept_cell.DataValidation.DataRange = sheet.Range["A3:A6"] dept_cell.DataValidation.ShowError = True dept_cell.DataValidation.AlertStyle = AlertStyleType.Stop dept_cell.DataValidation.ErrorTitle = "輸入錯誤" dept_cell.DataValidation.ErrorMessage = "請從下拉列表中選擇部門！" dept_cell.DataValidation.ShowInput = True dept_cell.DataValidation.InputTitle = "選擇部門" dept_cell.DataValidation.InputMessage = "請從固定部門列表中選擇。"
使用場景：避免部門名稱不統(tǒng)一（如"技術""技術部"混用），確保人事系統(tǒng)中的部門數(shù)據(jù)標準化。
保存文件后效果：
3. 整數(shù)驗證（員工年齡）
員工年齡一般處于合理范圍內(nèi)，例如 18 到 60 歲。通過整數(shù)驗證可以限制用戶只能輸入該范圍內(nèi)的整數(shù)值，避免出現(xiàn)異常數(shù)據(jù)。
sheet.Range["B1"].Text = "員工年齡 (18-60)" age_cell = sheet.Range["B3"] age_cell.DataValidation.AllowType = CellDataType.Integer age_cell.DataValidation.CompareOperator = ValidationComparisonOperator.Between age_cell.DataValidation.Formula1 = "18" age_cell.DataValidation.Formula2 = "60" age_cell.DataValidation.AlertStyle = AlertStyleType.Warning age_cell.DataValidation.ShowError = True age_cell.DataValidation.ErrorTitle = "年齡錯誤" age_cell.DataValidation.ErrorMessage = "請輸入 18 到 60 之間的整數(shù)！" age_cell.DataValidation.InputMessage = "員工年齡驗證" age_cell.DataValidation.IgnoreBlank = True age_cell.DataValidation.ShowInput = True
使用場景：保證錄入的年齡數(shù)據(jù)合理，不會出現(xiàn)"5 歲員工"或"100 歲員工"的異常數(shù)據(jù)，確保人事數(shù)據(jù)的真實性和合規(guī)性。
保存文件后效果：
4. 小數(shù)驗證（績效得分）
員工的績效考核得分通常是帶小數(shù)的數(shù)值，例如 0 到 100 分之間的小數(shù)。通過小數(shù)驗證可以確保績效數(shù)據(jù)的精確性和合理性。
sheet.Range["C1"].Text = "績效得分 (0-100)" score_cell = sheet.Range["C2"] score_cell.DataValidation.AllowType = CellDataType.Decimal score_cell.DataValidation.CompareOperator = ValidationComparisonOperator.Between score_cell.DataValidation.Formula1 = "0" score_cell.DataValidation.Formula2 = "100" score_cell.DataValidation.ShowError = True score_cell.DataValidation.ErrorMessage = "績效得分必須在 0 到 100 之間！" score_cell.DataValidation.AlertStyle = AlertStyleType.Stop
使用場景：適用于績效考核、評分統(tǒng)計等需要小數(shù)精度的場景，避免輸入超出范圍的分數(shù)或無效數(shù)值。
5. 日期驗證（入職日期）
企業(yè)通常要求員工入職日期在某一合理區(qū)間內(nèi)。例如，數(shù)據(jù)錄入系統(tǒng)只允許選擇 2023 年內(nèi)的入職日期，以防止錄入歷史錯誤數(shù)據(jù)。
sheet.Range["D1"].Text = "入職日期 (2023年)" hire_date_cell = sheet.Range["D2"] hire_date_cell.DataValidation.AllowType = CellDataType.Date hire_date_cell.DataValidation.CompareOperator = ValidationComparisonOperator.Between hire_date_cell.DataValidation.Formula1 = "2023-01-01" hire_date_cell.DataValidation.Formula2 = "2023-12-31" hire_date_cell.DataValidation.ShowError = True hire_date_cell.DataValidation.ErrorMessage = "請輸入 2023 年的有效日期！" hire_date_cell.DataValidation.AlertStyle = AlertStyleType.Warning
使用場景：確保入職時間不會超出考勤和人事系統(tǒng)設定范圍，避免錄入未來日期或過于久遠的歷史日期。
保存文件后效果：
6. 文本長度驗證（員工工號）
工號通常有固定的位數(shù)規(guī)則，例如必須是 6 位字符。通過文本長度驗證可以保證工號錄入規(guī)范，便于后續(xù)系統(tǒng)識別和處理。
sheet.Range["E1"].Text = "員工工號 (6位)" id_cell = sheet.Range["E2"] id_cell.DataValidation.AllowType = CellDataType.TextLength id_cell.DataValidation.CompareOperator = ValidationComparisonOperator.Equal id_cell.DataValidation.Formula1 = "6" id_cell.DataValidation.ShowError = True id_cell.DataValidation.ErrorMessage = "工號必須為 6 位字符！" id_cell.DataValidation.AlertStyle = AlertStyleType.Stop
使用場景：避免工號錄入長度不一導致系統(tǒng)識別異常，確保所有工號格式統(tǒng)一，便于數(shù)據(jù)庫存儲和查詢。
7. 時間驗證（上班時間）
在考勤管理中，員工的上班時間通常需要在合理的時間范圍內(nèi)，例如上午 7:00 到 9:00 之間。通過時間驗證可以規(guī)范考勤數(shù)據(jù)的錄入。
sheet.Range["F1"].Text = "上班時間 (07:00-09:00)" time_cell = sheet.Range["F2"] time_cell.DataValidation.AllowType = CellDataType.Time time_cell.DataValidation.CompareOperator = ValidationComparisonOperator.Between time_cell.DataValidation.Formula1 = "07:00" time_cell.DataValidation.Formula2 = "09:00" time_cell.DataValidation.AlertStyle = AlertStyleType.Info time_cell.DataValidation.ShowError = True time_cell.DataValidation.ErrorTitle = "時間錯誤" time_cell.DataValidation.ErrorMessage = "上班時間應在 07:00 到 09:00 之間！" time_cell.DataValidation.InputMessage = "上班時間驗證" time_cell.DataValidation.IgnoreBlank = True time_cell.DataValidation.ShowInput = True
使用場景：適用于考勤系統(tǒng)、排班管理等場景，確保時間數(shù)據(jù)的合理性，便于統(tǒng)計分析和薪資計算。
8. 保存文件并調(diào)整格式
完成所有驗證規(guī)則設置后，調(diào)整工作表的格式并保存為 Excel 文件：
for col in range(1, 7):
sheet.AutoFitColumn(col)
workbook.SaveToFile("DataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()說明：
使用 AutoFitColumn 方法自動調(diào)整列寬，使數(shù)據(jù)顯示更加美觀。最后將工作簿保存為 Excel 2016 格式的文件，并釋放資源。
關鍵類與屬性總結
數(shù)據(jù)驗證設置流程
- 獲取單元格范圍：通過
sheet.Range["單元格地址"]獲取需要設置驗證的單元格對象。 - 設置驗證類型：通過
DataValidation.AllowType指定驗證類型（整數(shù)、小數(shù)、日期、時間、文本長度等）。 - 設置比較運算符：通過
DataValidation.CompareOperator指定比較方式（Between、Equal、LessOrEqual 等）。 - 設置驗證條件：通過
Formula1和Formula2設置驗證參數(shù)值。 - 配置提示信息：設置
ShowError、ErrorMessage、ShowInput、InputMessage等屬性，提供用戶友好的提示。 - 保存文件：使用
SaveToFile方法保存工作簿。
關鍵類與屬性對照表
| 類 / 屬性 | 說明 |
|---|---|
Workbook | 表示 Excel 工作簿，用于創(chuàng)建和保存文件 |
Worksheet | 表示 Excel 工作表，所有操作都基于該對象 |
CellRange | 表示單元格或單元格區(qū)域 |
DataValidation | 用于設置單元格數(shù)據(jù)驗證規(guī)則 |
AllowType | 指定驗證類型（整數(shù)、小數(shù)、日期、時間、文本長度等） |
CompareOperator | 指定比較運算符（Between、Equal、LessOrEqual 等） |
Formula1 / Formula2 | 用于設置驗證條件的參數(shù)值 |
DataRange | 用于設置下拉列表的數(shù)據(jù)源范圍 |
AlertStyle | 錯誤提示樣式（Stop、Warning、Info） |
ShowError | 是否顯示錯誤提示 |
ErrorTitle | 錯誤提示標題 |
ErrorMessage | 錯誤提示信息 |
ShowInput | 是否顯示輸入提示 |
InputTitle | 輸入提示標題 |
InputMessage | 輸入提示信息 |
IgnoreBlank | 是否允許空值 |
總結
通過本文示例，你已經(jīng)了解如何使用 Free Spire.XLS for Python 在 Excel 工作表中設置多種類型的數(shù)據(jù)驗證，包括下拉列表、整數(shù)范圍、小數(shù)范圍、日期區(qū)間、文本長度和時間范圍。從初始化工作簿到設置各類驗證規(guī)則，整個過程高度自動化，特別適用于批量生成帶有數(shù)據(jù)驗證規(guī)則的 Excel 模板文件。
相比手動設置驗證規(guī)則，代碼方式具有以下優(yōu)勢：可以批量處理多個文件，保證規(guī)則一致性；可以輕松修改和擴展驗證規(guī)則；可以與數(shù)據(jù)處理流程無縫集成。你可以在此基礎上擴展更多能力，例如自定義公式驗證、條件格式設置、批量數(shù)據(jù)導入等。
如果你正在處理員工信息錄入、財務數(shù)據(jù)填報、庫存管理等需要數(shù)據(jù)規(guī)范化驗證的需求，這種基于 Python 的 Excel 數(shù)據(jù)驗證方案將為你的工作帶來顯著提升。
以上就是使用Python在Excel工作表中設置數(shù)據(jù)驗證的詳細內(nèi)容，更多關于Python Excel設置數(shù)據(jù)驗證的資料請關注腳本之家其它相關文章！
相關文章
這篇文章主要介紹了Python中計時程序運行時間的幾種常用方法,分別是一般方法、基于上下文管理器和基于裝飾器,每種方法都有其適用場景和優(yōu)缺點,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2025-04-04
Python3 執(zhí)行Linux Bash命令的方法
今天小編就為大家分享一篇Python3 執(zhí)行Linux Bash命令的方法，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2019-07-07
Python實現(xiàn)為Excel中每個單元格計算其在文件中的平均值
這篇文章主要為大家詳細介紹了如何基于Python語言實現(xiàn)對大量不同的Excel文件加以跨文件、逐單元格平均值計算,感興趣的小伙伴可以跟隨小編一起學習一下2023-10-10
Pytorch搭建YoloV5目標檢測平臺實現(xiàn)過程
這篇文章主要為大家介紹了Pytorch搭建YoloV5目標檢測平臺實現(xiàn)過程，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-04-04
django實現(xiàn)更改數(shù)據(jù)庫某個字段以及字段段內(nèi)數(shù)據(jù)
這篇文章主要介紹了django實現(xiàn)更改數(shù)據(jù)庫某個字段以及字段段內(nèi)數(shù)據(jù)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-03-03
這篇文章主要介紹了python使用import引入其他目錄文件方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-02-02
這篇文章主要介紹了python查找目錄下指定擴展名的文件,實例分析了Python文件查詢的技巧,非常具有實用價值,需要的朋友可以參考下2015-04-04
Windows+Mac通用Python詳細安裝教程及避坑指南(新手必看!)
Python安裝包雖然版本不同,但安裝過程都是差不多的,這篇文章主要介紹了Windows+Mac通用Python詳細安裝教程及避坑指南的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2026-05-05
這篇文章主要為大家詳細介紹了Python實現(xiàn)井字棋小游戲，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2020-03-03
這篇文章主要介紹了使用Atom支持基于Jupyter的Python開發(fā),Vscode雖然說也有連接Jupyter的工具，但是交互式的開發(fā)Hydrogen體驗更好,需要的朋友可以參考下2021-08-08