HotXLS でドロップダウンリストと入力検証を扱う（Delphi）

HotXLS は Excel のデータ検証ドロップダウンリストを Delphi コードから直接読めます。TryGetCellDropdownItems がセルの候補文字列を返し、SelectCellDropdownItem がその 1 つをインデックスかテキストで書き込み、ValidateCellInput は何かが保存される前に、入力された値がセルのルールを通るかを報告します。同じメソッドが Classic XLS の TXLSWorksheet と XLSX の TXLSXWorksheet の両方に存在し、どちらも行と列の番号は 1 基点です

これが必要になるきっかけの定番は、自分で設計していないテンプレートです。事業側の誰かが、Draft、Review、Final に固定された Status 列、隠しシートの範囲から給仕される Channel 列、1 から 100 しか受け付けない数量列を持つブックを送ってきます。インポートジョブも Web フォームも一括投入ツールも、そのルールを守らなければならず、ルールはブックの中に住んでいてコードにはいません。許可値をテンプレートの隣へハードコードするのが、誰かが Excel でリストを編集した最初の瞬間に両者が乖離する仕組みです

コードからの書き込みはなぜ Excel のデータ検証を無視するのか

データ検証は範囲に付くメタデータであり、強制はユーザーが入力するエディタの仕事です。XLSX ではルールは dataValidation 要素（ECMA-376 Part 1、§18.3.1.32）で、型、演算子、formula1 と formula2、プロンプト、エラーアラートを運びます。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 は実務でリストソースが書かれる 4 つの形すべてを解決するため、呼び出し側が 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 は入力メッセージのタイトルとテキストを返します。Web フォームのフィールドの隣へ、テンプレート作者自身のヒントを見せたいときに便利です

インデックスかテキストで項目を選ぶ

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 を写す独自のコンバータがあります

仲間が 2 つあってセットを完成させます。ValidateCellValue(ARow, ACol) はすでにセルへ保存された値を検査し、空セルを有効として扱います。TryGetCellValidationError はルールのエラーアラートのタイトル、テキスト、スタイルを返し、ルールがエラーアラートをまったく表示しないときは False を返します。スタイル型はエンジンごとに異なるため、ワークシートクラスに合わせます。TXLSWorksheet には lxDataCheck の TXLSDvErrorStyle、TXLSXWorksheet には TXLSXDvErrorStyle です

自分で再現しなければならない挙動が 1 つあります。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 のエスケープ罠

二重引用符を含むインラインリスト項目は、ルールを黙って失う手軽な方法です。インラインリストは数式文字列として保存され、数式文字列は埋め込まれた " を 2 重化してエスケープします。内側を 2 重化せずに項目を引用符でくるむと、He said "hi",No のようなリストはコンパイルされない数式を生みます。例外は上がりません。ルールが単に作られず、ドロップダウンも一緒に消えます。TXLSWorksheet.AddListValidation とその XLSX 対応物は、どちらも埋め込まれた引用符を 2 重化するため、そのような項目は無傷で生き残ります

TXLSXWorksheet の対応物に合わせて、TXLSWorksheet.AddListValidation には array of WideString のオーバーロードもあります。便利ですが、何をするかを読んでください。項目をカンマで結合して文字列オーバーロードへ渡します。自分自身がカンマを含む項目は 2 つの項目に化け、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 ハンドラなしで編集を駆動するテストは、モーダルダイアログで止まります。ビューアのスタイル列挙は 3 つ目の型、TXLSViewerValidationErrorStyle で、xveStop、xveWarning、xveInformation を持ち、2 つのワークシート列挙とは別物です

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 バイナリブックを読み書きする記事で述べています

ドロップダウンの解決、項目選択、入力検証は、Delphi、C++Builder、Lazarus 向けの HotXLS Delphi Excel component に同梱されています。この記事のすべての呼び出しを運動させる DataValidation と WorkbookViewer のデモも一緒です