LlosLabソフトウェア開発ブログPDF、Delphi、およびドキュメントソフトウェア開発者向けのエンジニアリングノート。

技術記事

HotXLSによるDelphiでのExcel条件付き書式評価

HotXLS Component

HotXLSはDelphi・C++Builder向けのネイティブスプレッドシートコンポーネントであり、バージョン2.209.0以降、Excelが通常自分だけの秘密にしている問いに答えられる:この正確なセルについて、どの条件付き書式ルールが発火し、どんな塗り、フォント、データバー、アイコンに解決されるか。その答えこそ、出力がHTMLレポート、PDF、あるいは自分で描くグリッドである瞬間に必要になるものだ

これはルールを作成することとは別の問題だ。2つの以前の記事が作成側を扱っている:条件付き書式とリッチテキストスタイルはルールと差分フォーマットを範囲にアタッチすることを扱い、アンカー付き条件付き書式の分割は行や列が挿入・削除されたときにルール範囲に何が起きるかを扱う。どちらも構造的だ。この記事はセマンティクスについてだ:すでにルールを持つワークブックが与えられたとき、ハイライトを計算する

なぜファイル形式はどのセルが光るかを教えてくれないのか

短い答えは、ECMA-376とISO 29500-1は評価ではなく格納を定義しているからだ。conditionalFormatting要素(§18.3.1.18)はsqrefとcfRuleの子要素のリスト(§18.3.1.10)を持ち、各ルールはtype、任意のoperator、priority、stopIfTrueフラグ、1つか2つのformula子要素、そしてビジュアル系については一連のcfvo閾値を持つ。これらはそれぞれユーザーが設定したものを忠実に記述しているが、そのどれもアルゴリズムではない。ルールタイプの半分についてはこのギャップは問題にならない:operator="greaterThan"を持つcellIsはより大きいことを意味し、containsTextは部分文字列が存在することを意味する。ギャップが開くのは集計系ファミリーだ。27個の数値が入力されたセルに対するrank="10"かつpercent="1"のtop10ルールは、何個のセルをハイライトするのか?2.7は数字ではない。丸めるか、切り捨てるか、切り上げるか——仕様は沈黙しており、間違った選択をすれば、あなたのPDFは顧客が隣で開いているワークブックと食い違うことになる

単一セルルールと、TCondFormatRule.Evaluateが止まるところ

HotXLSはまず安価な半分を取った。2.199.0で追加されたlxCondFormat.pas内のTCondFormatRule.Evaluateは、範囲の残りについて何も知らずに、1つのルールが1つのセルに対して発火するかどうかに答える。cellIsの背後にある8つのBIFF比較演算子(between、notBetween、equal、notEqual、greater、less、greaterEqual、lessEqual)、相対参照が正しく再基準化されるようセルで評価される自由形式のexpressionルール、4つのテキスト述語、空白とエラーの述語を処理する。閾値はTXLSCalculator.GetRangeValueを通じてセル位置で解決されるFFormula1とFFormula2から来て、逆になっている境界は拒否されるのではなく入れ替えられる

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

このメソッドの誠実な部分は、それが推測することを拒否している点だ。top10、aboveAverage、belowAverage、duplicateValues、uniqueValuesはFalseを返す。それは難しいからではなく、1つのセルからは決定不可能だからだ——それぞれが定義域全体にわたる統計量を必要とする。4つのビジュアル系ファミリー、dataBar、colorScale2、colorScale3、iconSetは、別の理由でFalseを返す:それらはそもそもブール値を生成せず、レンダリング用のペイロードを生成するので、ブールの戻り値型はそれらには不適切な形なのだ

ワークシートレベルの評価器はどうやってシートの再スキャンを避けるのか

共有される量をすべて構築時に一度だけ計算し、二度と計算しないことによって。lxHandleX.pas内のTXLSXConditionalFormatEvaluatorは、TXLSXWorksheet.CreateConditionalFormatEvaluatorを通じて構築される1つのワークシートのための不変スナップショットであり、その設計全体が、描画される各セルがフルレンジのスキャンをトリガーしてしまう素朴な実装に対する防御になっている

コンストラクタでは4つのことが起きる。異なる複数エリアのsqrefのそれぞれは、正確に1回だけTXlsxCfRangeSnapshotにパースされるので、1つの範囲を共有する10個のルールは1回のパースと1回の統計パスを共有する。そのパスは、入力された各セルにわたって平均、母標準偏差、最小値、最大値を1回の走査でストリーム処理し、Top/Bottomまたはパーセンタイルルールが実際に順序統計を必要とする場合にのみ、順序付き数値配列を保持する。重複キーと一意キーはUnicode安全に構築され、ルックアップごとではなく一度だけバッチソートされる。それから行軸は各エリア境界でバンドに分割されるので、EvaluateCellはバンドをバイナリサーチし、その行に到達し得る範囲を持つルールだけを訪れる

4つ目が最もスケールにおいて重要だ。=A1>AVERAGE($A$1:$A$100)のような相対ルール式は、定義域内の各セルで異なる意味を持ち、素直な実装はセルごとに新しい構文木をコンパイルする。TXlsxCfRulePlanはそれを一度だけコンパイルし、可逆な座標オフセットを通じて同じ木を再評価する。これはセルごとの構文木割り当てなしにExcelのアンカー動作を保つ。ルールはその後priorityによって層状に並べられ、StopIfTrueが設定されているルールでの一致はループを打ち切る——Excelが短絡評価するのとまったく同じだ

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Excelは実際にどうTop 10パーセントルールを丸めているのか

切り捨てを行い、最小値は1で、境界の同値も含める。それはISO 29500-1のどこにも書かれていない——手作りのワークブックでExcel 16を突いて、アプリケーションがどのセルをハイライトするか読み取ることで固定された。HotXLSは正確にそれを実装している:ランク数はFloor(Count * Min(Rank, 100) / 100)であり、ゼロに落ちたときは1に引き上げられ、入力された件数にクランプされ、境界値はその後>=で比較されるので、境界に等しいすべてのセルは、それが要求された件数を超過することになってもハイライトされる。27個の値と10パーセントルールは2セルをハイライトし、それに加えて2番目の値に同点であるさらなるセルもハイライトされる

平均以上のルールは2つ目の曖昧さを隠していた:stdDev="1"を持つaboveAverageは平均より1標準偏差上のセルを選ぶが、標本標準偏差と母標準偏差はベッセルの補正によって異なり、それはまさに条件付き書式が使われる小さな範囲において目に見える形で食い違う。Excel 16は母標準偏差を使い、HotXLSはそれに合わせている。equalAverageフラグは、偏差帯が絡んでいない場合にのみ厳密な比較を包含的にする。重複と一意のルールはキーの同一性を基準にしている。あるセルが数値100を保持し、別のセルがテキスト"100"を保持している場合、Excelはそれらを同じ重複キーとして扱うので、HotXLSは生の文字列を比較する代わりに、数値のテキストを数値キー空間に正規化する。空白セルは鏡像のケースだ:本当に空のセルは範囲の集計には参加するが、それ自体がスタイル付けされることはないので、列内の空白セルが互いの重複として一斉に光ることはない

カラースケールとアイコンセット:補間と境界規則

ビジュアル系ファミリーはブール値ではなくレンダリング準備済みの数値に解決され、その端の挙動も同じ方法で固定された。明示的な数値閾値を持つカラースケールについて、HotXLSは位置の割合を閉区間0から1にクランプし、その後チャンネルごとに丸めではなく切り捨てで補間する——最小値未満の値は外挿された色ではなく最小値の色を得て、3段階のスケールは中間の段階と比較してペアを選び、両端が同じ閾値を持つ退化したスケールはゼロ除算する代わりに最上位の色に折りたたまれる。アイコンセットには逆の種類の注意が必要だった。最初のものを除く各cfvoは自分自身の比較の厳密さを持つからだ:HotXLSは閾値ごとにThresholdEqualsIncludeを読み、それに応じて>=または>を適用し、上向きに歩いて満たされた最高の閾値がアイコンインデックスを勝ち取る。逆転されたセットは閾値ではなく解決されたインデックスを反転し、アイコンごとのオーバーライドは別のファミリーからグリフを引っ張ることができ、無効な閾値はもっともらしく見える間違ったアイコンを生成する代わりにルールを中断する

1つの結果からグリッド、HTML書き出し、PDFに供給する

EvaluateCellは、テーマの色調がすでに適用された差分の塗りとフォント色、太字、斜体、下線、数値書式ID、方向性のある正と負のバーの範囲、軸位置、アイコンファミリーとインデックスを持つ、完全に解決されたTXLSXCfCellResultを返すので、すべてのコンシューマーは同じレコードを読み、そのどれもルールの内部を理解する必要がない。HotXLSはHTML書き出し、PDF書き出し、インタラクティブビューアにその1つのパスを使っており、これが3つのレンダラーが乖離しないようにする唯一の実用的な方法だ。バージョン2.210.0はこれをTXLSWorkbookViewerに組み込んだ。これはアクティブな各ワークシートについて1つの準備済み評価器をキャッシュし、スクロール、選択、再描画にわたって再利用し、ワークブックまたはワークシートが変わったときに解放する——Paintのたびにスナップショットを再構築することは、構築時設計全体を無に帰すことになる。そのキャッシュはまたTXLSWorkbookViewer.RefreshConditionalFormatsが存在する理由でもある:スナップショットは不変なので、アタッチされたワークブックをその場で変更すると、それを呼び出すまで集計統計と解決済み閾値が古いままになる

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

評価器がやってくれないこと

率直に述べておく価値のある境界が3つある。古典的な単一セルのTCondFormatRule.EvaluateとワークシートレベルのTXLSXConditionalFormatEvaluatorは異なる能力を持つ異なる面であり、単一セル側は集計系とビジュアル系のファミリーを近似するのではなく意図的に断る——Top/Bottomやカラースケールが必要なら評価器を構築しよう。相対的な期間ルールは評価時のマシンの時計に依存するので、timePeriodルールは今日生成されたPDFと来週生成されたPDFで異なって描画される。それは正しい挙動でありながら、アーカイブがバイト単位で安定していることを期待しているならサポートチケットが待っている。3つ目は技術的というより文法的だ:条件付き書式の式文法は構造化テーブル参照を禁止しているので、ルールはワークシートの式ができるようにテーブルの列を名前で扱うことができない。それは実装の制約ではなく形式の制約だ

レポート出力、書き出しパイプライン、あるいはExcelとセル単位で一致しなければならないカスタムグリッドを構築しているなら、同じ解決済み結果が、このブログの別の場所で説明されているカスタムVCLスプレッドシートグリッドも駆動している。HotXLS Delphiスプレッドシートコンポーネントの完全なAPIドキュメント、ルールモデル、トライアルダウンロードは製品ページで入手できる