DelphiおよびC++Builder用Excelコンポーネントである HotXLS は、行や列の挿入・削除がルールの対象範囲を、異なる相対数式アンカーを必要とする複数の断片に切り分けてしまうたびに、条件付き書式やデータ検証ルールを自動的に2つ以上の別個のルールオブジェクトに分割し、その後すべての条件付き書式ルールに新しい一意の優先度番号を再割り当てする。この挙動はXLSXエンジンのバージョン2.196で出荷され、自動的に実行される。無効化する設定はない。この引き金は狭いがよくある状況だ：自身の範囲に対して相対的にセルを読む数式を持つcellIsルールまたは式ルールが、後にちょうどその範囲の途中で行が挿入または削除されるワークシート上に存在する場合である
Excel自動化に関するほとんどの解説は数式テキストの問題で止まる：あらゆるSUM()やVLOOKUP()の中の行番号・列番号をシフトし、参照が正しいセルを指し続けるようにする。その半分は本物であり、HotXLSが行と列の移動時に数式参照をどう書き換えるかの関連記事で扱われているが、条件付き書式やデータ検証ルールはセルに座っているだけの数式ではない。それは数式と範囲——ECMA-376の用語で言えばsqref——を組にしており、この2つは一緒に動かなければならない。構造的な編集がその範囲を、正しくあるために2つの異なる相対オフセットを必要とする2つの断片に切り分けてしまうと、1つの数式文字列を持つ1つのルールオブジェクトを保つことはもはや選択肢ではなくなる。そうではないふりをすることこそが、ハイライトルールが静かに間違った行を比較し始める原因である
なぜ行の挿入は条件付き書式ルールを単に移動させるのではなく分割するのか
条件付き書式またはデータ検証ルールは、その範囲全体に対してちょうど1つの数式を保持し、単一のアンカーセルに対して相対的に評価される。そのため、編集がその範囲の2つの部分に2つの異なる相対オフセットを要求させると、1つの数式ではもはや両方の部分を正しく記述できなくなる。ECMA-376はルールの適用範囲をconditionalFormattingまたはdataValidation要素上のsqref属性として表現し、ExcelはFormula1とFormula2を、あたかもそのテキストがそのsqrefの左上セルに入力され、残りの範囲全体に埋め込まれたかのように評価する。これは通常の相対数式が列に沿って下方に埋め込まれるのと同じ原理だ。予算を超える実績値をフラグ立てするB2:B50上の差異ハイライトを想像してみよう。これはFormula1がリテラルテキストC2であるcellIsルールとして構築されており、現在の行のBセルをその同じ行のCセルと比較することを意味する
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25元の25行目にこの1つの区切り行を挿入すると、挿入位置より上の行は動かないため、それらの分のルールはFormula1を依然として正しくC2として読む。かつて25行目から50行目だった行は26行目から51行目にずれ、それらにとってC2は今や完全に間違ったセルになる。なぜなら26行目は2ダース近く上にある予算値ではなく、C26と比較する必要があるからだ
HotXLSはルールを分割すべきかどうかをどう決めるのか
HotXLSは、幾何学的に本当にそれが必要な場合にのみ追加のルールオブジェクトを作成する：内部ルーチンであるXlsxBuildShiftedRulePartsは、ルールのsqref内のすべての非連結領域を歩き、編集前にその領域のアンカーセルが何だったか、編集後に何になるかを計算し、結果として生じるすべての断片が同じ相対オフセット補正を必要とするかどうかをチェックする。すべての断片が一致するなら、1つのルールが生き残り、そのsqrefはシフトされた断片の和集合として再構築され、その数式は一度だけ再アンカーされる。本当の分割が起こるのは断片が一致しない場合だけであり、これはまさに上記のB2:B50のケースであり、上のブロックは元のアンカーを保ち、下のブロックは新しいアンカーを必要とする
断片の数式を再アンカーすることは、HotXLSがOOXMLの共有数式グループ用にすでに持っている仕組みを再利用する2段階の動作である：まず数式は、あたかも元々その断片自身の左上セルにアンカーされていたかのように、共有数式をその範囲全体に展開するのと同じ相対オフセットの計算を使って変換される。次にその結果は、通常のワークシート数式を書き換えるのと同じ行・列シフトスキャナを通る。これがFormula1が1つの手書きの特殊ケースではなく2段階の動作でC2からC26になる仕組みである：C2を23行分前方に変換してC25を得る、あたかもそのルールが常にそこから始まっていたかのように、その後25行目での通常のシフトがそれをC26へと押し進める。他のすべてのプロパティ——塗りつぶし色、stop-if-true、演算子自体——は変更されずに新しいルールオブジェクトに引き継がれる。そのため両方の半分は常にそうであったのと同じ色でセルを塗り続ける
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)データバーとアイコンセットはcellIsルールと同じように分割されるのか
いいえ：HotXLSは、その正しさが実際に領域ごとの相対数式に依存するルール種別——cellIs比較と式ルール——だけを分割し、他のすべての条件付き書式の種別は、そのsqrefがシフトされた断片をカバーするようにマルチエリアの和集合として単純に拡大する単一のルールオブジェクトのままにする。内部では、この分岐は単純なKindチェック、すなわちcf.Kind in [cfkCellIs, cfkExpression]であり、それ以上に凝ったものではない。データバー、2色・3色スケール、アイコンセット、上位・下位ランキング、そして重複・空白・エラー検出器は、バーの色、スケールの止め点の集合、アイコンファミリーといった、領域ごとの相対比較ではなくカバーされる範囲全体を一度に記述するペイロードを運ぶ。そのため、それらを複数の優先度付きルールオブジェクトに分割しても正しさは何も得られず、管理すべきルールが増えるだけである。編集がそれらの範囲を分断すると、HotXLSは断片ごとに新しいルールオブジェクトを複製するのではなく、断片を1つのマルチエリアsqrefを持つ1つのルールに再結合し、ペイロードを単一の単位として再アンカーする。この区別は条件付き書式とリッチテキストの基礎に関する記事にあるルール種別の分類と一致している：データバー、カラースケール、アイコンセットは、Styleプロパティを完全に無視することですでにcellIsルールと一線を画しており、今度は同じ根本的な理由で領域ごとの再アンカーとも一線を画すことが分かる
構造的な編集の後になぜルールの優先度が変わるのか
優先度が変わるのは、すべての複製が分割元のルールと全く同じ優先度値を持って始まり、HotXLSがその後、2つのルールが同じ順位で並ぶままにするのではなく、結果として生じる重複をきれいで隙間のない順序に解決する正規化パスを実行するからである。2つ目の内部ルーチンであるXlsxNormalizeConditionalFormatPrioritiesは、各条件付き書式の現在の優先度を取得し、明示的に設定されたことのないルールについてはそのコレクション内の位置にフォールバックし、リスト全体を安定的にソートして同順位のものは元の相対順序を保ち、ソートされた結果に隙間も重複もない1、2、3という密な連番を振り直す。HotXLSはシフトが始まる前に一度これを実行するため、複製はきれいな基準線から始まり、分割のたびと空になったルールが削除されるたびにもう一度実行される。そのため保存されるファイルには同じ優先度を主張する2つのルールエントリが決して存在しない。これは、後で追加されるルールが残りを振り直すことなく挿入できるように優先度値の間に隙間を残すという、条件付き書式の基礎に関する記事のアドバイスに従っていた場合に重要になる：その隙間は、そのワークシートに次に行や列の編集が触れるまでは生き残るが、その後崩壊する。なぜなら正規化が保証するのは一意性と安定した順序だけであり、元の番号付け方式が変わらず戻ってくることではないからだ
データ検証ルールも分割される、ただし振り直すべき優先度はない
データ検証ルールはcellIsルールや式条件付き書式と同じ範囲分割ロジックを通るが、条件付き書式とは異なり、すべての検証タイプがそのパスを一様に通る：HotXLSには、データバーやアイコンセットが条件付き書式に対してそうであるような、データ検証用の別個の非数式ファミリーは存在しない。そのため単純なリストや整数のルールも、相対的なカスタム数式を扱うのと同一のルーチンによって分割される。異なるのは優先度である：ECMA-376はdataValidation要素にpriority属性を一切与えていないため、条件付き書式のような検証用の振り直しステップは存在しない。隣の列にある予算を超えないように各行の実際の金額を保つカスタム数式検証を想像してみよう
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)これが重要なのは、データ検証の基礎に関する記事が行数が確定する前にルールを付けることに対して警告しているのと同じ理由による：検証はあなたが与えた文字通りのセルだけをカバーしており、後の構造的な編集は、以前は1つで済んでいた仕事を2つ以上のルールが行うようにしてしまうことがある。機能的には何も壊れない：元の範囲のすべてのセルは依然として何かによって検証されているが、1列につき1つのDataValidationsエントリを前提とするコードは、最初の編集がそこに触れた後にインデックスを誤り始める。これがどこまで行けるかには厳格な上限がある：分割がワークシートを65,534件のデータ検証ルールを超えて押し上げてしまう場合、HotXLSはExcelが静かに拒否するようなファイルを書き込む代わりに例外を発生させる。これはライブラリが壊れたワークブックを製造することを拒否しているのであって、通常の使用でおそらく到達する制限ではない
一括挿入・削除の後に何を確認すべきか
スクリプトが条件付き書式と検証で満ちたシートに対して行や列の編集をバッチで実行した後に検証する価値のある2つのものは、ルールの総数と優先度の順序である。どちらもコードレビューでは見逃しやすく、誰かがExcelで「ルールの管理」を開いた瞬間に明白になる方法で漂うことがあるからだ。1回の編集がもたらすダメージはめったに大きくない：1つのcellIsルールの途中への単一の挿入は、1つだったものからせいぜい2つのルールオブジェクトを生む。リスクが複合するのは、レポート生成ルーチンが、すでにいくつかの数式アンカー付きルールを持つシート上でループの中で一度に1行ずつ行を挿入する場合である：各パスは前のパスがすでに分割したルールを再分割することがあり、5つの元のcellIsルールは、元の範囲の細切れをカバーする低価値の断片の何倍にもなり得る。構造的な編集をバッチ化し、新しいブロック全体を1行ずつではなく1回の呼び出しで挿入することは、ルール数を実行された編集の回数ではなく本当に異なるアンカーの数に結びつけたままにする
ルールの分割と優先度の正規化は、DelphiおよびC++Builder向けHotXLS Delphi ExcelコンポーネントのXLSXエンジンの標準動作として出荷される。製品ページには、ここで説明した条件付き書式とデータ検証のメソッドを含む、ワークシート編集APIの全リファレンスが掲載されている