파이썬을 사용하여 Excel에서 데이터 유효성 검사 사용하는 방법

들어가기 전에

Excel의 데이터 유효성 검사는 특정 셀에 입력되는 데이터에 대해 어떤 기준이나 규칙을 설정할 수 있는 기능입니다. 사전 정의된 값이나 조건으로 입력을 제한하여 데이터의 정확성과 무결성을 높이는 데 도움이 됩니다. 파이썬을 사용하여 Excel에서 데이터 유효성 검사를 추가하고 제거하는 방법을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

---

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: Add or Remove Data Validation in Excel with Python
• URL: https://medium.com/@alice.yang_10652/add-or-remove-data-validation-in-excel-with-python-f998dca8f995

---

필요한 라이브러리 설치

Python에서 Excel의 데이터 유효성 검사를 추가하고 제거하려면 Python용 Spire.XLS 라이브러리를 사용할 수 있습니다. 이 라이브러리는 Python 애플리케이션 내에서 Excel 파일을 생성, 읽기, 편집 및 변환하기 위한 사용하기 쉽고 기능이 풍부한 라이브러리입니다.

 

이 라이브러리를 사용하면 XLS, XLSX, XLSB, XLSM 및 ODS와 같은 다양한 스프레드시트 형식으로 작업할 수 있습니다. 또한 Excel 파일을 PDF, HTML, CSV, 텍스트, 이미지, XML, SVG, ODS, PostScript 및 XPS와 같은 다른 유형의 파일 형식으로 렌더링할 수도 있습니다.

터미널에서 다음 명령을 실행하여 PyPI에서 Python용 Spire.XLS를 설치할 수 있습니다.

pip install Spire.Xls

 

설치에 대한 자세한 내용은 [여기]에서 확인할 수 있습니다.

파이썬으로 Excel에 데이터 유효성 검사 추가하기

Excel에 목록 데이터 유효성 검사 추가(드롭다운)를 사용하면 미리 정의된 옵션의 드롭다운 목록을 생성하여 특정 셀에 입력된 데이터가 사용 가능한 선택 항목 중 하나와 일치하는지 확인할 수 있습니다. 목록 데이터 유효성 검사는 목록의 유효한 값으로 입력을 제한함으로써 데이터 무결성과 정확성을 효과적으로 유지합니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 목록 데이터 유효성 검사를 추가(드롭다운)하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "List Data Validation:"

# Add list data validation to cell C2
range = sheet.Range["C2"]
listValidation = range.DataValidation
listValidation.Values = ["Red", "Blue", "Green", "Yellow"]
listValidation.IsSuppressDropDownArrow = False
listValidation.InputMessage = "Select a color from the list"
listValidation.AlertStyle = AlertStyleType.Stop
listValidation.ShowError = True
listValidation.ErrorTitle = "Error"
listValidation.ErrorMessage = "Please select a color from the list"

# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("ListDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel에 "숫자" 데이터 유효성 검사 추가하기

Excel에 정수 데이터 유효성 검사를 추가하면 셀에 입력할 수 있는 데이터 유형에 제한을 설정할 수 있습니다. 이 유효성 검사를 사용하면 최소값 및 최대값과 같은 정수에 대한 특정 기준을 정의하여 유효한 정수만 허용되도록 할 수 있습니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 정수 데이터 유효성 검사를 추가하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "Whole Number Data Validation:"

# Add whole number data validation to cell C2
range = sheet.Range["C2"]
wholeNumberValidation = range.DataValidation
wholeNumberValidation.AllowType = CellDataType.Integer
wholeNumberValidation.CompareOperator = ValidationComparisonOperator.Between
wholeNumberValidation.Formula1 = "1"
wholeNumberValidation.Formula2 = "100"
wholeNumberValidation.InputMessage = "Enter a number between 1 and 100"
wholeNumberValidation.AlertStyle = AlertStyleType.Stop
wholeNumberValidation.ShowError = True
wholeNumberValidation.ErrorTitle = "Error"
wholeNumberValidation.ErrorMessage = "Please enter a number between 1 and 100"

# Set column width
sheet.SetColumnWidth(2, 27)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("WholeNumberDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel에 "소수점" 데이터 유효성 검사 추가하기

Excel에 소수점 데이터 유효성 검사를 추가하면 셀에 입력된 소수점 값에 제약 조건을 설정할 수 있습니다. 소수점 이하 자릿수, 최소값 및 최대값을 지정하거나 특정 형식을 적용할 수도 있습니다. 이러한 유효성 검사를 통해 정확하고 허용 가능한 소수점 데이터만 입력되도록 할 수 있습니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 소수점 데이터 유효성 검사를 추가하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "Decimal Data Validation:"

# Add decimal data validation to cell C2
range = sheet.Range["C2"]
wholeNumberValidation = range.DataValidation
wholeNumberValidation.AllowType = CellDataType.Decimal
wholeNumberValidation.CompareOperator = ValidationComparisonOperator.Between
wholeNumberValidation.Formula1 = "-10.50"
wholeNumberValidation.Formula2 = "10.50"
wholeNumberValidation.InputMessage = "Enter a decimal between -10.50 and 10.50"
wholeNumberValidation.AlertStyle = AlertStyleType.Stop
wholeNumberValidation.ShowError = True
wholeNumberValidation.ErrorTitle = "Error"
wholeNumberValidation.ErrorMessage = "Please enter a decimal between -10.50 and 10.50"

# Set column width
sheet.SetColumnWidth(2, 23)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("DecimalDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

파이썬으로 Excel에 "날짜" 데이터 유효성 검사 추가하기

Excel에 날짜 데이터 유효성 검사를 추가하면 셀에 입력된 날짜를 제어하고 유효성을 검사할 수 있습니다. 특정 날짜 형식을 설정하고, 날짜 범위를 정의하고, 입력을 평일 또는 특정 요일로 제한할 수도 있습니다. 이러한 유효성 검사는 날짜 기반 데이터의 일관성과 정확성을 유지하는 데 도움이 됩니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 날짜 데이터 유효성 검사를 추가하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "Date Data Validation:"

# Add date data validation to cell C2
range = sheet.Range["C2"]
dateValidation = range.DataValidation
dateValidation.AllowType = CellDataType.Date
dateValidation.CompareOperator = ValidationComparisonOperator.Between
dateValidation.Formula1 = "01/01/2023"
dateValidation.Formula2 = "12/31/2023"
dateValidation.InputMessage = "Enter a date between 01/01/2023 and 12/31/2023"
dateValidation.AlertStyle = AlertStyleType.Stop
dateValidation.ShowError = True
dateValidation.ErrorTitle = "Error"
dateValidation.ErrorMessage = "Please enter a date between 01/01/2023 and 12/31/2023"

# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("DateDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel에 "시간" 데이터 유효성 검사 추가하기

Excel에 시간 데이터 유효성 검사를 추가하면 셀에 입력된 시간 값의 유효성을 검사하고 제어할 수 있습니다. 특정 시간 형식, 최소값 및 최대값을 정의하거나 특정 시간 간격으로 입력을 제한할 수도 있습니다. 이러한 유효성 검사를 통해 입력된 시간 데이터가 정확하고 원하는 매개변수 내에 있는지 확인할 수 있습니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 시간 데이터 유효성 검사를 추가하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "Time Data Validation:"

# Add time data validation to cell C2
range = sheet.Range["C2"]
timeValidation = range.DataValidation
timeValidation.AllowType = CellDataType.Time
timeValidation.CompareOperator = ValidationComparisonOperator.Between
timeValidation.Formula1 = "8:30"
timeValidation.Formula2 = "11:30"
timeValidation.InputMessage = "Enter a time between 8:30 and 11:30"
timeValidation.AlertStyle = AlertStyleType.Stop
timeValidation.ShowError = True
timeValidation.ErrorTitle = "Error"
timeValidation.ErrorMessage = "Please enter a time between 8:30 and 11:30"

# Set column width
sheet.SetColumnWidth(2, 20)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("TimeDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel에 "텍스트 길이" 데이터 유효성 검사 추가하기

Excel에 텍스트 길이 데이터 유효성 검사를 추가하면 셀에 입력되는 텍스트의 길이에 제한을 설정할 수 있습니다. 최대 및 최소 글자 수 제한을 정의하여 입력된 텍스트가 원하는 기준을 충족하는지 확인할 수 있습니다. 이 유효성 검사는 데이터 입력 오류를 방지하고 일관된 텍스트 길이를 보장하는 데 도움이 됩니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에 텍스트 길이 데이터 유효성 검사를 추가하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Add text to cell B2
sheet.Range["B2"].Text = "Text Length Data Validation:"

# Add text length data validation to cell C2
range = sheet.Range["C2"]
textLengthValidation = range.DataValidation
textLengthValidation.AllowType = CellDataType.TextLength
textLengthValidation.CompareOperator = ValidationComparisonOperator.Greater
textLengthValidation.Formula1 = "8"
textLengthValidation.InputMessage = "Enter a text greater than 8 characters"
textLengthValidation.AlertStyle = AlertStyleType.Stop
textLengthValidation.ShowError = True
textLengthValidation.ErrorTitle = "Error"
textLengthValidation.ErrorMessage = "Please enter a text greater than 8 characters"

# Set column width
sheet.SetColumnWidth(2, 25)
sheet.SetColumnWidth(3, 20)

# Save the result file
workbook.SaveToFile("TextLengthDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

이미지: medium

 

파이썬으로 Excel에서 데이터 유효성 검사 제거하기

Excel에서 데이터 유효성 검사를 제거하면 셀 또는 셀 범위에서 이전에 설정한 유효성 검사 규칙을 제거할 수 있습니다. 이 기능은 유효성 검사로 인한 제한이 더 이상 필요하지 않거나 유효성 검사 기준을 변경하려는 경우에 유용할 수 있습니다.

다음은 Python 및 Python용 Spire.XLS를 사용하여 Excel 파일에서 데이터 유효성 검사를 제거하는 방법을 보여주는 예제입니다.

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file containing data validations
workbook.LoadFromFile("ListDataValidation.xlsx")

# Get the first worksheet
worksheet = workbook.Worksheets[0]

# Create a list of rectangles to specify the cells or cell ranges where the data validation will be removed
rects= []
rects.append(Rectangle.FromLTRB(0, 0, 3, 2))
         
# Remove the data validation from the selected cells
worksheet.DVTable.Remove(rects)

# Save the result file
workbook.SaveToFile("RemoveDataValidation.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

 

마치며

파이썬을 사용하면 Excel 파일에 다양한 유형의 데이터 유효성 검사를 추가하거나 제거할 수 있습니다. 개인적으로는 VBA를 이용하는 것이 편리해보입니다만, 각자 처한 상황이나 형편에 적합한 방법을 사용하시기 바랍니다.

---

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.