HotXLS 목록 유효성 검사: 255자 한계와 x14 확장

Excel은 목록 데이터 유효성 검사가 255자를 넘는 인라인 목록을 담고 있는 XLSX 워크북 열기를 거부하며, Excel이 스스로 저장한 파일이라도 예외가 아니다. Delphi와 C++Builder용 네이티브 Excel 컴포넌트인 HotXLS는 그런 목록을 로드하고 시트를 넘나드는 목록 규칙을 x14 확장으로 모델링하지만, 어디서나 통하는 근본 해법은 같다. 긴 목록은 워크시트 범위나 정의된 이름에 보관하라

버그 리포트는 대개 여러분 테스트가 아니라 고객에게서 온다. Region이나 Product 열의 드롭다운이 여덟 항목으로 시작해 이제 예순 항목이 된 템플릿이다. 공유 드라이브에 누군가 갖고 있는 XLSB 사본에서는 여전히 다 잘 동작한다. 그러다 익스포트 잡이 같은 규칙을 XLSX에 기록하면 Excel이 복구 프롬프트나 완전 거부로 답한다. 코드에서 바뀐 것은 문자열 하나의 길이뿐인데, 이 경계가 여러분을 찾아내기 전에 이해할 가치가 있는 이유가 정확히 그것이다

255자 목록 한계는 실제로 어디에 적용되는가?

한계는 XLSX 목록 유효성 검사의 인라인 형태, 즉 formula1에 저장되는 인용·쉼표 구분 문자열에 적용되며, Excel은 데이터 유효성 검사 대화상자에서 목록을 입력할 때만이 아니라 파일을 열 때 이를 강제한다. Excel을 직접 상대로 한 테스트는 깔끔한 결과를 줬다. 253자 목록은 열렸고 260자와 421자 목록은 거부됐다. 421자 사례가 교훈적인데, Excel이 스스로 만든 워크북에서 나왔기 때문이다. Excel은 Excel 자신이 읽지 않을 파일을 쓴다

스키마는 여기서 도와주지 않는다. ECMA-376 Part 1 §18.3.1.32는 dataValidation 요소와 수식을 정의하면서 255자 한도를 언급하지 않으므로, 파일은 스키마에 적합하면서도 거부될 수 있다. 이것은 응용 프로그램 제약이고, 신뢰할 만한 유일한 참조는 Excel 자신의 행동이다

HotXLS는 그 한도를 대신 강제하지 않는다. TXLSXWorksheet.AddListValidation은 항목을 인용부로 감싸 준 그대로 저장하며, 긴 목록을 자르지도 거부하지도 않는다. 그래서 400자 목록은 요청한 그대로 저장된 XLSX에 들어가고 Excel은 결과를 거부한다. 이것은 의도된 선택이다. 조용히 목록을 잘라 버리면 아무도 눈치채지 못하는 방식으로 데이터를 훼손하니까. 하지만 길이 검사는 여러분 쪽에 놓인다. 작은 헬퍼가 이를 지키게 해 준다

const
  // Excel은 253자 목록을 열고 260자와 421자는 거부했다.
  // 정확한 가장자리를 겨냥하는 대신 여유를 두어라.
  MaxInlineListChars = 250;

function InlineListLength(const AItems: array of WideString): Integer;
var
  I: Integer;
begin
  Result := 0;
  for I := 0 to High(AItems) do
    Inc(Result, Length(AItems[I]));
  if Length(AItems) > 1 then
    Inc(Result, High(AItems));  // 구분 쉼표
end;

내부 겹따옴표는 두 글자로 계산하라. AddListValidation이 수식 문자열을 유효하게 유지하려고 두 배로 늘리기 때문이다

XLSB는 왜 XLSX가 못 실는 목록을 실을 수 있는가

XLSB는 인라인 목록을 길이가 16비트 필드인 단일 PtgStr 문자열 토큰으로 저장하므로 바이너리 포맷에는 255자 장벽이 없다. 항목 60개에 419자인 네이티브 샘플이 토큰 하나로 살아 Excel에서 아무 불평 없이 왕복한다. 같은 목록의 XLSX 버전이 위의 421자 사례다

대화상자의 255자 한도를 XLSB에 적용하는 로더는 그 파일을 거부할 것이다. HotXLS의 XLSB 검증 토큰 검사는 문자열 토큰을 32767자로 묶는다. Excel 셀 문자열과 같은 한도다. 두 가지 단서를 분명히 말해 둔다. 로더는 완전한 검증 수식도 16384바이트로 제한하므로, 인라인 목록 하나의 실질 한도는 토큰 검사만이 시사하는 것보다 낮다. 그리고 PtgStr 안에서 NUL 문자로 갈라진 목록은 지원 프로파일 밖이므로, 추측하는 대신 지원하지 않는다고 보고된다

함정은 변환에 앉아 있다. XLSB에서 완전히 건강한 긴 목록이, HotXLS든 무엇이든 그 워크북을 XLSX로 저장하는 순간 거부되는 파일이 된다. 고객 XLSB 파일을 변환하는 파이프라인이라면, 고객이 보고한 뒤가 아니라 XLSX 저장 전에 모든 목록 규칙에 길이 검사를 돌려라

다른 시트를 가리키는 목록은 어떻게 되는가?

Excel은 원본이 다른 워크시트에 있는 목록을 평범한 dataValidations 요소가 아니라 x14:dataValidations 확장에 저장한다. 규칙은 확장 URI {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF} 아래 워크시트의 extLst 안에 살며, [MS-XLSX]에 정의돼 있고, 원본 참조는 Lists!$A$2:$A$61 같은 xm:f 요소다. XLSB는 같은 규칙을 자기 확장 레코드로 간직한다

HotXLS는 그 XLSX 확장 규칙을 타입화된 DataValidations 컬렉션으로 파싱하고 각 규칙에 TXLSXDataValidation.IsExtension 플래그를 꽂는다. 시트를 넘나드는 목록은 따라서 범위, 유형, 수식이 읽히는 채로 평범한 규칙 곁에 나타나고 편집, 저장, 재열기에서 살아남는다. 저장할 때 라이터는 확장 규칙을 평범한 요소 밖으로 유지하고 x14 확장을 워크시트 꼬리의 스키마 위치에 다시 지어 놓는다. 수식 둘 다 보존되므로 between이나 notBetween을 쓰는 정수, 소수, 텍스트 길이, 날짜, 시간 규칙은 양 경계를 모두 간직한다

타입화된 확장 프로파일은 규칙 전체를 커버한다. type, operator, allowBlank, 두 show 플래그, 범위, 수식 둘 외에도 확장 규칙은 입력 프롬프트 제목과 텍스트, 오류 경고 제목과 텍스트, 경고 스타일, IME 모드, 드롭다운 숨김 설정을 로드와 재저장을 거쳐 간직하며 이스케이프 규칙은 평범한 검증과 같다

uses
  SysUtils, lxHandleX, lxDiagnostics;

procedure AuditListRules(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Dv: TXLSXDataValidation;
  S, I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    for S := 1 to Book.Sheets.Count do        // Sheets[]는 1 기반이다
    begin
      Sheet := Book.Sheets[S];
      for I := 0 to Sheet.DataValidations.Count - 1 do
      begin
        Dv := Sheet.DataValidations[I];
        if Dv.ValidationType <> xlsxDvList then
          Continue;
        if Dv.IsExtension then
          Writeln(Sheet.Name, ' ', Dv.Range, ': x14 list from ', Dv.Formula1)
        else if (Dv.Formula1 <> '') and (Dv.Formula1[1] = '"') and
                (Length(Dv.Formula1) - 2 > MaxInlineListChars) then
          Writeln(Sheet.Name, ' ', Dv.Range, ': inline list of ',
            Length(Dv.Formula1) - 2, ' chars, refused by Excel as XLSX');
      end;
    end;
  finally
    Book.Free;
  end;
end;

드롭다운 뒤의 항목 읽기, 항목 선택, 입력값 대조는 HotXLS로 드롭다운 항목 읽고 입력 검증하기를 보라. 이 글은 저장 형식에만 머문다

Excel과 HotXLS는 미지의 검증 유형을 어떻게 다루는가

Excel은 type 속성이 알 수 없는 토큰을 담은 dataValidation을 none 유형 규칙으로 열고 수식을 간직하며, 저장할 때는 미지 속성을 버리고 type 속성 없는 벌거벗은 dataValidation 요소로 다시 기록한다. 그런 토큰은 손으로 편집한 XML, 서드파티 생성기, 미래의 Office 기능에서 온다. Excel의 답은 관용이지 거부가 아니다

HotXLS는 같은 관용을 따른다. 재저장에서 흔적 없이 잃어버리게 되는 규칙을 버리는 대신, 리더는 수식과 범위를 지닌 채 xlsxDvNone으로 간직하고 정규화된 규칙마다 코드 1307 경고 진단을 하나 기록하며, 이는 lxDiagnostics의 xlsDiagnosticValidationTypeNormalized 상수로 드러난다. 저장할 때 none 규칙은 Excel이 만드는 것과 같은 벌거벗은 요소로 기록된다. none 규칙은 아무것도 검증하지 않으므로, 그 진단은 작성자가 활성이라 믿었던 규칙이 더 이상 입력을 구속하지 않는다는 신호탄이다

for I := 0 to Book.Diagnostics.Count - 1 do
  if Book.Diagnostics[I].Code = xlsDiagnosticValidationTypeNormalized then
    Writeln('Untyped rule kept on sheet ', Book.Diagnostics[I].SheetName,
      ': ', Book.Diagnostics[I].Message);

왕복 뒤에 Excel이 워크북을 거부할 수 있는 이유는?

재생된 확장 조각이 새 파일이 전혀 선언하지 않는 네임스페이스 접두사를 쓰면 Excel은 재저장된 워크북을 거부한다. 결과가 well-formed XML이 아니기 때문이다. 확장 조각은 흔히 xr:uid 같은 접두사 붙은 속성을 실어 다니는데, 그 xmlns:xr 선언은 조각 안이 아니라 원본 워크시트 루트에 앉아 있다. 그 선언이 없는 새 워크시트 루트에 조각을 그대로 복사하면 Excel이 셀 하나 보기도 전에 XML 네임스페이스 규칙이 깨진다

HotXLS는 캡처 단계에서 이를 처리한다. 읽는 동안 재생된 각 조각을 조각이 스스로 선언하지 않는 요소·속성 접두사를 찾아 훑고, 각 접두사를 리더를 통해 URI로 풀고, 저장 시점에 그 선언들을 워크시트 루트에 기록한다. 스트리밍 저장 경로도 같은 루틴으로 루트를 만들므로 두 저장 모드 모두 well-formed 출력을 내고, 재저장된 파일은 재생된 시트 간 x14 목록을 포함해 모든 검증 규칙이 살아 있는 채로 Excel에서 다시 열린다. HotXLS가 모델링하지 않는 extLst 콘텐츠를 운반하는 더 넓은 그림은 테마, extLst, calcChain의 무손실 XLSX 왕복에, Excel 복구 프롬프트의 일반 규칙은 Excel이 복구 프롬프트를 보여 주는 이유에 있다

긴 목록은 범위나 정의된 이름에 살아야 한다

자랄지도 모르는 목록의 튼튼한 설계는 항목을 워크시트 셀에 저장하고 정의된 이름으로 검증이 그것을 가리키게 하는 것이다. 범위 원본은 어느 포맷에서도 255자 한도가 없고, 규칙을 건드리지 않고 항목을 편집할 수 있으며, 정의된 이름은 셀이 별도 시트에 앉아 있어도 규칙을 평범한 dataValidation 요소에 유지한다. 그 파일의 모든 소비자가 읽을 수 있는 형태다

// Lists는 전용 시트다, 이를테면 Book.Sheets.Add('Lists')
function AddListRule(Book: TXLSXWorkbook; Target, Lists: TXLSXWorksheet;
  ACol: Integer; const ARange, AName: WideString;
  const AItems: array of WideString): Integer;
var
  I: Integer;
  ColRef: WideString;
begin
  if InlineListLength(AItems) <= MaxInlineListChars then
  begin
    Result := Target.AddListValidation(ARange, AItems);
    Exit;
  end;
  for I := 0 to High(AItems) do
    Lists.WriteAutoValue(I + 1, ACol, AItems[I]);   // 1 기반 행, 열
  ColRef := Chr(Ord('A') + ACol - 1);               // A부터 Z까지 열
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // 선행 '='이 참조를 표시한다, 없으면 이름이 텍스트로 인용된다
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

그 선행 등호가 중요하다. AddListValidation은 =로 시작하는 인자를 참조로 취급해 부호를 벗겨 내고, !를 담은 인자는 인용하지 않은 채 범위로 남기며, 나머지는 전부 인라인 목록으로 인용한다. 부호 없이 'StatusList'를 넘기면 문자 그대로 StatusList라고 읽히는 항목 하나짜리 드롭다운을 얻는다. 작은 점 두 개. WriteAutoValue는 Excel 셀 입력과 같은 방식으로 텍스트를 변환하므로 TRUE 같은 항목은 Boolean이 되고, 공백을 담은 시트 이름은 참조에서 작은따옴표가 필요하다

이름 없이 다른 시트를 곧장 가리켜야 하는 규칙에는 AddListValidation이 돌려준 인덱스의 규칙에 IsExtension := True를 설정하라. HotXLS가 Excel 자신이 쓰는 것과 같은 x14 확장 형태로 기록한다. 정의된 이름 경로가 더 단순하고 확장을 파싱하지 않는 도구에서도 계속 동작하므로, 여기서의 기본 추천은 그쪽이다

여기서 기술한 모든 동작은 지원 검증 유형, 파일 형식, 전체 진단 참조를 제품 페이지에 정리해 둔 Delphi, C++Builder, Lazarus용 HotXLS Delphi Excel 컴포넌트에 실려 제공된다