Delphi에서 HotXLS 드롭다운 목록과 입력 검증

HotXLS는 Excel 데이터 유효성 검사 드롭다운 목록을 Delphi 코드에서 곧장 읽는다. TryGetCellDropdownItems는 셀의 후보 문자열을 돌려주고, SelectCellDropdownItem은 그중 하나를 인덱스나 텍스트로 기록하며, ValidateCellInput은 무엇이든 저장되기 전에 입력된 값이 셀 규칙을 통과할지 보고한다. 같은 메서드들이 Classic XLS의 TXLSWorksheet와 XLSX의 TXLSXWorksheet에 있고, 둘 다 1 기반 행·열 번호를 받는다

이것이 필요해지는 전형적 계기는 여러분이 설계하지 않은 템플릿이다. 실무 쪽 누군가가 Status 열을 Draft, Review, Final로 잠근 워크북을 배포하고, Channel 열은 숨겨진 시트의 범위에서 먹이고, 수량 열은 1부터 100만 받는다. 이제 여러분의 임포트 잡이나 웹 폼, 일괄 채우기가 그 규칙들을 존중해야 하는데, 규칙은 여러분 코드가 아니라 워크북 안에 산다. 허용값을 템플릿 옆에 하드코딩하는 순간 누군가 Excel에서 목록을 고치는 첫 시점에 둘은 어긋난다

코드 쓰기는 왜 Excel 데이터 유효성 검사를 무시하는가?

데이터 유효성 검사는 범위에 붙은 메타데이터이고, 강제는 사용자가 입력하는 편집기의 몫이다. XLSX에서 규칙은 유형, 연산자, formula1, formula2, 프롬프트, 오류 경고를 실은 dataValidation 요소(ECMA-376 Part 1, §18.3.1.32)다. Classic XLS 파일에서는 같은 정보가 [MS-XLS]가 기술하는 DV 레코드에 앉는다. 어느 포맷도 셀 자체를 표시하지 않으므로, 규칙을 깨는 값을 저장하는 프로그램을 막는 장치는 없다. Excel도 똑같이 행동한다. 입력된 항목은 검사하지만, 다른 경로로 들어온 범위 밖 값은 누군가 Circle Invalid Data를 돌릴 때에야 드러난다

HotXLS는 이 모델을 일부러 따른다. Sheet.Cells[3, 2].Value := 250 같은 대입은 1부터 100 규칙 아래에서도 아무 불평 없이 250을 저장한다. 번들 DataValidation 데모가 그 사실을 증명하려고 하는 행동이 정확히 이것이다. 검사는 옵트인이고, 이 글의 나머지는 어디서 옵트인할지에 관한 이야기다

셀 뒤에 있는 드롭다운 항목 읽기

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown)는 셀이 목록형 규칙에 덮일 때만 True를 돌려주고 AItems를 후보 TArray<WideString>로 채운다. HotXLS는 목록 원본이 실전에서 쓰이는 네 가지 형태를 모두 해석하므로 호출자는 formula1을 손으로 파싱하지 않는다

• 쉼표로 갈라지는 인용 인라인 목록, 이를테면 "Draft,Review,Final"
• 항목이 #0 문자로 갈라진 인라인 목록, Classic XLS 규칙에서 발견되는 형태
• 같은 시트나 다른 시트의 범위, 이를테면 =$G$2:$G$10이나 =Lists!$A$2:$A$7, 현재 셀 값으로 평가된다
• 그런 범위를 가리키는 =ProductList 같은 정의된 이름

병합 셀도 처리된다. 병합 안의 어떤 셀을 물어도 HotXLS는 병합 앵커에서 규칙을 찾는데, Excel이 규칙과 값을 함께 보관하는 곳이 바로 거기다

AShowDropDown 플래그는 한마디할 가치가 있다. 파일 포맷이 이것을 거꾸로 저장하기 때문이다. XLSX에서 showDropDown="1"은 실제로 셀 안 화살표를 숨기고, BIFF8에서 동등한 비트는 '콤보 억제' 플래그다. HotXLS는 읽을 때 양쪽을 뒤집으므로 AShowDropDown = True는 말하는 그대로다. Excel이 화살표를 보여 준다는 뜻이다. 화살표가 숨겨진 목록 규칙도 여전히 입력을 제한하므로, TryGetCellDropdownItems는 어느 쪽이든 항목을 돌려준다

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2: 행 2, 열 6, 둘 다 1 기반
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

문자열만 필요하다면 GetCellDropdownItems(ARow, ACol)가 배열을 곧장 돌려주고 목록이 없으면 빈 배열을 내놓는다. TryGetCellValidationPrompt는 입력 메시지 제목과 텍스트를 돌려주는데, 웹 폼 필드 옆에 템플릿 작성자의 자체 힌트를 보여 주고 싶을 때 요긴하다

인덱스나 텍스트로 항목 고르기

SelectCellDropdownItem은 목록 항목을 셀에 기록하고 셀에 목록 규칙이 없거나 요청한 항목이 없으면 False를 돌려준다. 정수 오버로드는 TryGetCellDropdownItems가 돌려준 배열의 0 기반 인덱스를 받는다. 텍스트 오버로드는 정확 일치를 먼저 찾고 그다음 대소문자 무시 일치로 폴백하므로 'review'도 Review를 고른다

양쪽 오버로드는 날 문자열 대입이 아니라 WriteAutoValue를 통해 기록한다. 숫자로 된 범위 기반 목록에서 이것이 중요하다. 항목은 '3' 같은 문자열로 돌아오지만 저장되는 값은 숫자 3인데, 사용자가 화살표에서 골랐을 때 Excel이 저장하는 것이 바로 그것이고 그 셀을 참조하는 모든 수식이 기대하는 것도 그것이다. DataValidation 데모는 저장 후 XLS, XLSX, XLSB에서 다시 열어 정확히 이것을 검사한다

// TXLSWorksheet(XLS)와 TXLSXWorksheet(XLSX)에서 같은 호출
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 4번째 항목, 0 기반
  Writeln('F2: index 3 is out of range');

입력값을 쓰기 전에 어떻게 검증하는가?

ValidateCellInput(ARow, ACol, AText)는 후보 문자열을 셀을 덮는 규칙에 대조하며 셀을 절대 고치지 않는다. 규칙이 없거나 후보가 통과하면 True를 돌려준다. 문자열은 WriteAutoValue가 쓰는 것과 같은 텍스트-값 변환을 거치므로 검사와 실제 기록이 같은 값을 본다. XLSX 쪽 변환은 XlsxAutoTextValue다. TRUE/FALSE, 오류 리터럴, ISO 날짜, m/d/yyyy와 d.m.yyyy 날짜, 불변 형식 숫자를 알아보고 0123은 Excel처럼 텍스트로 유지한다. Classic XLS 엔진은 자기 WriteAutoValue를 그대로 반영하는 자체 변환기를 갖고 있다

동료 둘이 세트를 완성한다. ValidateCellValue(ARow, ACol)는 이미 셀에 저장된 값을 검사하고 빈 셀은 유효로 친다. TryGetCellValidationError는 규칙의 오류 경고 제목, 텍스트, 스타일을 돌려주고 규칙이 오류 경고를 아예 보여 주지 않으면 False를 돌려준다. 스타일 유형은 엔진마다 다르므로 워크시트 클래스에 맞춰라. TXLSWorksheet에는 lxDataCheck의 TXLSDvErrorStyle, TXLSXWorksheet에는 TXLSXDvErrorStyle

여러분이 직접 재현해야 하는 동작이 하나 있다. Excel은 빈 항목이나 =로 시작하는 무엇이든 검증하지 않는다. HotXLS는 이 건너뛰기를 뷰어 편집 경로에 적용하지 ValidateCellInput 안에 적용하지 않으므로, 서버 측 게이트는 호출 전에 같은 일을 해야 한다

uses
  lxDataCheck, lxHandle;

// AText를 쓸 수 있으면 True를 돌려준다, Excel 경고 스타일을 그대로 반영
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel은 비워진 셀과 수식은 검증을 건너뛴다
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // 오류 경고가 설정돼 있지 않으면 Excel은 무엇이든 통과시킨다
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop은 거부하고, Warning과 Information은 결정을 호출자에게 맡긴다
  Result := Style <> xlsDvErrStop;
end;

// 호출자(Msg: WideString): 검증한 뒤 같은 변환으로 저장한다
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

ValidateCellInput과 WriteAutoValue를 짝지우는 것이 예제의 요점이다. 한 변환으로 검사하고 다른 변환으로 저장하면, 언젠가 숫자로 검사를 통과한 값이 텍스트로 셀에 앉는다

인용 목록 항목과 Classic XLS 이스케이프 함정

겹따옴표를 포함한 인라인 목록 항목은 규칙을 조용히 잃게 만드는 쉬운 길이다. 인라인 목록은 수식 문자열로 저장되고, 수식 문자열은 내부의 "를 두 배로 늘려 이스케이프한다. 안쪽 따옴표를 두 배로 늘리지 않고 항목을 인용부로 감싸면 He said "hi",No 같은 목록이 컴파일되지 않는 수식을 낳는다. 예외는 없다. 규칙이 그저 만들어지지 않고, 드롭다운이 따라가 사라진다. TXLSWorksheet.AddListValidation과 XLSX 대응물은 둘 다 내부 따옴표를 두 배로 늘리므로 그런 항목은 온전히 살아남는다

TXLSWorksheet.AddListValidation에는 TXLSXWorksheet와 맞먹는 array of WideString 오버로드도 있다. 편리하지만 무엇을 하는지 읽어라. 항목을 쉼표로 이어붙여 문자열 오버로드로 넘긴다. 스스로 쉼표를 담은 항목은 따라서 두 항목으로 변하고, Classic 엔진에서는 !를 담은 원본 문자열이 인라인 목록이 아니라 시트 한정 참조로 취급된다. 그런 표제라면 값을 셀 범위에 넣고 규칙이 그것을 가리키게 하라

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // 믿기 전에 규칙이 존재하는지 확인한다
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

저장 후 다시 열면 H2의 TryGetCellDropdownItems가 따옴표를 온전히 지닌 He said "hi"를 돌려준다. XLS, XLSX, XLSB, ODS 출력 모두에서 마찬가지다

TXLSWorkbookViewer에게 규칙 강제 맡기기

사용자가 화면에서 셀을 편집할 때는 TXLSWorkbookViewer가 같은 검사를 대신 적용한다. Editable이 True이고 EnforceDataValidation이 기본값 True로 남으면, 오류 경고를 지닌 규칙을 깨는 입력 편집은 기록되기 전에 검사된다. Stop은 거부하고 Warning은 묻고 Information은 알린다. 뷰어는 활성 셀에 드롭다운 화살표를 그리고, 클릭이나 Alt+Down으로 목록을 열고, 셀 아래 노란 입력 프롬프트를 보여 준다. 그리드 자체의 디테일은 TXLSWorkbookViewer로 커스텀 스프레드시트 그리드 만들기 글이 다룬다

CircleInvalidData를 True로 두면 Excel의 같은 이름 명령처럼 규칙을 위반하는 저장 값을 빨간 원으로 둘러싼다. 보이는 영역만 스캔하고, FindDataValidationAt로 대부분의 셀을 빠르게 탈락시키며, 빈 셀은 절대 둘러싸지 않는다

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // 거부된 텍스트로 편집기를 다시 연다
  else
    Action := xvaAccept;
  Handled := True;        // 내장 대화상자를 억제한다
end;

Handled를 False로 두면 뷰어가 자기 경고 대화상자를 보여 주며, VCL과 LCL 양쪽에서 동작한다. 이 기본값은 자동화 테스트에서 물어뜯는다. Handled를 설정하는 OnValidationError 처리기 없이 편집을 몰아 넣는 테스트는 모달 대화상자에서 멈춘다. 뷰어의 스타일 enum은 세 번째 타입으로, 두 워크시트 enum과 별개인 xveStop, xveWarning, xveInformation을 지닌 TXLSViewerValidationErrorStyle이다

HotXLS 입력 검증을 믿기 전에 알아야 할 것은?

검사는 목록, 숫자, 날짜, 시간, 텍스트 길이 규칙에 대해 충실하며, 엔진별 디테일 몇 가지는 알아 둘 만하다

• Classic XLS 워크시트에서 ValidateCellInput은 커스텀 수식 규칙을 입력 후보에 대해 유효하다고 보고한다. 의존성 체인을 흔들지 않고 후보를 BIFF 셀에 임시로 앉힐 방법이 없기 때문이다. 저장된 값은 커스텀 수식을 평가하는 ValidateCellValue로 검사하거나, 후보에 대해서도 커스텀 규칙을 평가하는 XLSX 엔진을 써라
• Classic XLS에서 목록 매칭은 수치적으로 같은 값을 같은 항목으로 치므로 5와 5.0 둘 다 5 항목에 대응된다
• 뷰어에서 붙여넣은 콘텐츠는 Excel처럼 검증을 건너뛴다. EditSelectedCell은 다시 열 편집기가 없어서 거기서 Retry 결과는 Cancel처럼 행동한다
• 평범한 Cells[...].Value 대입은 설계상 결코 검사되지 않는다. 검증은 여러분 코드가 요청하는 자리에서만 일어난다

규칙 만들기, 그러니까 정수, 날짜, 텍스트 길이, 커스텀 유형을 포함한 규칙 생성은 HotXLS 데이터 유효성 검사, AutoFilter, 테이블 가이드가 다룬다. XLSB 워크북은 TXLSXWorkbook으로 로드되므로 여기서 보인 모든 TXLSXWorksheet 호출이 그대로 동작한다. Delphi에서 XLSB 바이너리 워크북 읽고 쓰기 글이 그 점을 기술한다

드롭다운 해석, 항목 선택, 입력 검증은 이 글의 모든 호출을 운동시키는 DataValidation과 WorkbookViewer 데모와 함께 Delphi, C++Builder, Lazarus용 HotXLS Delphi Excel 컴포넌트에 들어 있다