HotXLS のリスト検証：255 文字上限と x14 拡張

Excel は、リストのデータ検証が 255 文字を超えるインラインリストを抱えている XLSX ブックを開こうとしません。Excel 自身が保存したファイルでも同じです。Delphi と C++Builder 向けのネイティブ Excel コンポーネントである HotXLS は、そのようなリストをロードし、シートをまたぐリストルールを x14 拡張としてモデル化します。しかし恒久的な修正はどこでも同じです。長いリストはワークシートの範囲か定義名へ置くことです

バグ報告はたいてい自分のテストではなく顧客から来ます。テンプレートの Region 列や Product 列が育って、ドロップダウンが 8 項目から 60 項目になったとします。共有ドライブに誰かが置いておいた XLSB 複製では全部まだ動きます。それからエクスポートジョブが同じルールを XLSX へ書き、Excel は修復プロンプトか完全な拒否で応えます。変わったのは 1 つの文字列の長さだけで、まさにそれが、この境界が自分を襲う前に理解しておく価値がある理由です

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;

埋め込まれた二重引用符は 2 文字として数えてください。AddListValidation が数式文字列の妥当性を保つために 2 重化するからです

XLSB が XLSX では運べないリストを運べる理由

XLSB はインラインリストを 1 つの PtgStr 文字列トークンとして保存し、その長さは 16 ビットフィールドです。バイナリ形式には 255 文字の壁がありません。60 項目、419 文字のネイティブサンプルは 1 つのトークンに住み、Excel で文句なしに往復します。同じリストの XLSX 版が、前述の 421 文字のケースです

ダイアログの 255 文字上限を XLSB に適用するローダーは、このファイルを拒むことになります。HotXLS の XLSB 検証トークン検査は、代わりに文字列トークンを 32,767 文字で拘束します。Excel のセル文字列と同じ上限です。留保を 2 つ、率直に述べておきます。ローダーは検証数式全体も 16,384 バイトで押さえるため、1 つのインラインリストの実質上限は、トークン検査単独の見かけより低いです。そして PtgStr 内部の NUL 文字区切りリストはサポート対象プロファイルの外側なので、推測ではなく非対応として報告されます

罠は変換に座っています。XLSB では完全に健全な長いリストが、何者かが（HotXLS を含めて）そのブックを XLSX として保存した瞬間に、拒まれるファイルになります。顧客の XLSB ファイルを変換するパイプラインなら、XLSX 保存の前にすべてのリストルールへ長さ検査を掛けてください。顧客の報告の後ではなく

別シートを指すリストはどうなるのか

ソースが別のワークシートに座るリストを、Excel は普通の dataValidations 要素ではなく、x14:dataValidations 拡張へ保存します。ルールはワークシートの extLst 内部、拡張 URI {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}（[MS-XLSX] で定義）の下に住み、ソース参照は Lists!$A$2:$A$61 のような xm:f 要素です。XLSB は同じルールを独自の拡張レコードで保持します

HotXLS はこれらの XLSX 拡張ルールを型付きの DataValidations コレクションへ解析し、各ルールに TXLSXDataValidation.IsExtension を立てます。シートをまたぐリストは、範囲、型、数式が読める状態で普通のルールの隣に現れ、編集と保存と開き直しを生き延びます。保存時、ライタは拡張ルールを普通の要素から除外し、x14 拡張をスキーマ上の位置、ワークシートの末尾へ再構築します。両数式は保持されるため、between や notBetween を使う整数、小数、文字列長、日付、時刻のルールは両境界を保ちます

型付き拡張プロファイルはルール全体をカバーします。type、operator、allowBlank、2 つの 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 の警告診断を 1 件記録します。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 が 1 つのセルを見る前に 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 と読む 1 項目のドロップダウンが手に入ります。小さな点が 2 つ。WriteAutoValue はテキストを Excel のセル入力と同じように変換するため、TRUE のような項目は Boolean になり、空白を含むシート名は参照の中で一重引用符を要します

名前なしで直接別シートを指し続けなければならないルールには、AddListValidation が返したインデックス位置のルールに IsExtension := True を設定します。HotXLS はそれを x14 拡張として書きます。Excel 自身が使うのと同じ形です。定義名の経路のほうが単純で、拡張を一度も解析しないツールでも機能し続けるため、ここでの既定の推奨は定義名です

ここで述べた挙動のすべては、Delphi、C++Builder、Lazarus 向けの HotXLS Delphi Excel component に同梱されています。対応する検証型、ファイル形式、診断の全リファレンスは製品ページにあります