HotXLSの条件付き書式とリッチテキストをDelphiで

OOXMLの条件付き書式のルールは、1つの名前をまとった2つの別物です。条件（比較、数式、文字列の一致）はどのセルが該当するかを決めます。見た目（ECMA-376の用語でdxfと呼ばれる差分書式のレコード）は、該当したセルがどう見えるかを決めます。Excelのダイアログは両方を一度に埋めさせることで、この継ぎ目を隠しています。HotXLSは隠しません。DelphiからcellIsのルールを作ってスタイルを省くと、ルールは妥当で、範囲も正しく、数式もまさに狙ったセルで真になり、それでいて色は何も変わりません。そのルールの指示が「真、ただし何も塗らない」だったからです。条件と結果のあいだのこの隙間こそ最初に押さえるべきところで、ルールの管理では正しく見えるのに何も強調されないルールの大半は、これが原因です

HotXLSは条件付き書式をBIFF8の.xlsとOOXMLの.xlsxの両方へネイティブに書き込み、リッチテキストのランとプール化されたセルスタイルのモデルについても同じことをします。この3つの機能は、平坦なAPIの見た目が示すより多くの配線を共有しており、出力が意図からずれる場所はたいていその継ぎ目です

条件には結果が要る：dxfスタイル

XLSXのワークシートでは、比較のルールはAddConditionalFormatから生まれます。この関数は範囲、TXLSXCfOperatorの演算子、そして数式かリテラルを受け取り、シートのConditionalFormatsコレクションにおける新しいルールの添字を返します。その添字のルールオブジェクトはStyleプロパティを公開しており、強調表示が住んでいるのはそこです。塗りを設定すれば該当セルはその塗りを受け取ります。手を付けなければ、先ほど述べた見えないルールを組み上げたことになります

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // 負の差異：薄い赤の塗り
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // 重複した注文IDも同じやり方で目印を付ける
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // 独自の数式ルール：実績が目標の90%に届かない行を強調する
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

ここでの色は32ビットのARGB値なので、$FFFFC7CEはダイアログでおなじみのExcelの「薄い赤」で、RGBの手前に完全不透明のアルファバイトが座っています。セルごとの条件で発火するルールの種類はどれも、作ってからスタイルを付けるという同じ形をたどります。文字列の照合（AddCondFormatContainsText、AddCondFormatBeginsWith、AddCondFormatEndsWith）は後からスタイルを付ける添字を返しますし、AddCondFormatTop10、AddCondFormatAboveAverage、空白とエラーの検出も同様です。この型を一度覚えれば、文字列と比較の系統はすべて同じように振る舞います

データバー、カラースケール、アイコンセットは自分で塗る

見た目そのものを扱うルールの種類は逆向きに働きます。見た目をルールの定義の内側に抱えており、Styleプロパティを完全に無視します。データバーのルールに塗りを割り当てても何も起きません。分類が腑に落ちるまではバグに見えますが、AddCondFormatDataBarはバーの色を直接の引数として受け取り、2点と3点のカラースケールも端点の色を同じように受け取り、AddCondFormatIconSetはicsTrafficLights3のような26種類のアイコンセットから1つを選びます。ここには忘れようのある別のスタイルレコードがありません。そもそも別のスタイルレコードが存在しないからです

これらの呼び出しで考える価値のあるパラメーターは、TXLSCfValueKindとして型付けされた値の基準点です。バーやスケールの端点は、範囲の最小値や最大値、リテラルの数値、パーセントやパーセンタイル、あるいは数式の結果に置けます。既定値である範囲の最小と最大は、整ったデモデータでは行儀よく振る舞い、外れ値のある実データで裏切ります。突出した1つの値がスケールを引き伸ばし、他のすべてのバーを切り株に潰してしまうのです。ダッシュボードを期間をまたいで読ませるつもりなら、端点を固定の数値かパーセンタイルに留めてください。そうすれば3月の半分のバーが4月の半分のバーと同じ量を意味します。自動でスケールされたバーは、自分自身としか比べられません

XLSの書き出しが覆うのは4種類のルールだけ

従来のBIFF8側はXLSX側を小さく映した鏡ではなく、意図的な部分集合です。XLSのファサードが作れる条件付きルールの形はきっかり4つ、データバー、2色スケール、3色スケール、アイコンセットで、CF12レコードとしてストリームへ出力されます。cellIs、数式、文字列のルールを作るAPIはありません。開いたファイルに既にそれらの種類のルールがある場合は読み取られ、保たれ、そのまま書き戻されるので、顧客の.xlsを開いて保存し直しても、元々入っていた書式が壊れることはありません。できないのは、.xlsへしきい値による強調表示をゼロから生成することです。そこでの選択肢は、コードで計算した通常のセルの塗りで見せかけるか、成果物を.xlsxにしてルールの一族すべてを使えるようにするかです

これはデータ層ができた後ではなく、できる前に決着させるべき制約です。ダッシュボードの形をしたものについては、ファイル形式の判断そのものを変えてしまうからです。互換性のために.xlsを選んだチームが、その後cellIsのしきい値を使うKPIレポートを仕様に書いたなら、噛み合わない2つを選んだことになります。それに気付くのが安上がりなのは、着手から3週間後ではなく形式を決める時点です

ルールの重ね方、優先度、重なり合う範囲

現実のダッシュボードで1つの範囲にルールが1つだけということはめったにありません。差異の列は、大きさを表すデータバー、厳密なしきい値のためのcellIsルール、そしてその両方の上に載るエスカレーション用の行単位の数式ルールを抱えているかもしれません。各TXLSXConditionalFormatはPriorityの値を公開しており、Excelは競合するルールを優先度の順で解決します。2つのルールが同じセルを塗ろうとしたとき、勝者を決めるのはあなたが設定した数値であって、レビュー担当者がルールの管理ダイアログをたまたまどの順にスクロールしたかではありません

優先度は作図ソフトのz順序のように扱ってください。2つのルールが同じセルに届き得るところでは意図して割り当て、後からルールを差し込むときに全部を振り直さずに済むよう値のあいだに隙間を空けておきます。ルールが衝突しようのないところ、たとえばE列に閉じたデータバーとG列に閉じた文字列ルールなら、作成順のままで構いませんし、優先度に注意を払う価値はありません。その注意は範囲の境界に使ってください。ここで高くつく不具合は、ほぼ決して優先度の逆転ではないからです。それは350行に育ったレポートに対するB2:B200のような範囲であり、覆われなかった末尾は健全なデータとまったく同じに見える素のセルとして描かれます。すべてのルール範囲を、ブック内の他の場所でグラフの系列や入力規則の範囲を決めているのと同じ最終行数の値から導いてください。そうすれば末尾が落ちることはなくなります

元が取れる検証の習慣が1つあります。生成後にファイルをExcelで開き、書式を設定した範囲を選び、テンプレートを変更するたびに一度ルールの管理を見て回ることです。条件付き書式は、権威ある描画装置がファイルを消費するアプリケーションしかない数少ない領域の1つなので、XMLに対する単体テストが証明するのはルールが書かれたことであって、Excelが意図どおりに塗ることではありません。1分の目視でその隙間が埋まります

リッチテキスト：1つのセルの中に複数の書式

XLSXのモデルにおけるリッチテキストのセルは、ランのリストを保持します。各ランはテキストの一続きと、それ自身のフォント属性です。リストはTXLSXRichTextオブジェクトとして脇で組み立て、そこへランを足し、それから全体をセルへ取り付けます。噛みついてくるのは所有権の規則です。Cell.RichTextへ代入するとそのオブジェクトの所有権はセルへ移り、セルは自分の破棄の際にそれを解放します。自分でも解放すれば二重解放になります。原因となった処理のあいだは沈黙を保ち、ずっと後の無関係な場所でクラッシュとして現れる類のものです

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // 所有権はセルへ移る：Freeしないこと
end;

明示的なColorIsAuto := Falseは省ける飾りではありません。ランは自動色のフラグを持っており、色の代入はそのフラグが解除されて初めて尊重されます。Colorを設定してColorIsAutoを忘れると、ランは太字にはなるものの頑固に黒いままで、原因を指し示すエラーも出ません。ランは取り消し線、各種の下線、上付きと下付きのための縦方向の配置にも対応しており、テキストの内容を書き出したり差分を取ったりしたいときはPlainTextがリスト全体を1つの文字列へ平坦化します

セル単位のリッチテキストはXLSX専用です。XLSのファサードにはそれを書くための公開APIがありませんが、コメントとテキストボックスについてはTextRunsを通じてランを使えますし、既存の.xlsから読み取ったリッチな文字列は往復しても無傷で残ります。引力は条件付き書式のときと同じです。1つのセルの中で書式を混ぜるものは、XLSXの書き出し側の領分です

スタイルのプールと、そのまま出荷される1ずれ

XLSXのモデルにおける素のセルの装飾は、ブック上のプール化されたコレクションを通ります。Fonts.Add、Fills.AddSolid、Borders.Addはそれぞれ定義を登録し、プール内の添字を返します。この添字は0起点です。それを受け取るセル側のプロパティ、たとえばFontIndexは0を「既定」のために予約しているので、セルへ代入する値はプールの添字に1を足したものになります：

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // プールの添字、0起点
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // セルの添字、1起点

+ 1を落とすと、すべての見出しが既定のフォントへ戻ります。例外も警告もなく、あるのは誰も装飾しなかったように見えるブックだけです。二次的な誤りはループの中に隠れています。行ごとにFonts.Addを呼ぶことです。同一のフォント定義は重複が除かれるのでファイルが壊れることはありませんが、仕事は無駄になりますし、とりわけ配置のプールは重複をまとめずに呼び出しごとに新しいオブジェクトを返します。ひと握りのスタイルはループの前に一度だけ作り、その添字を使い回してください。10万行のレポートでは、この1つの変更がHotXLSの大きなブックの性能調整で扱っているてこの1つになります。既製の意味付きの見た目だけで済む場合は、どちらのファサードも範囲に対してApplyBuiltinStyleを公開しており、プールにまったく触れずにExcelの組み込みのGood、Bad、Neutralやアクセントのスタイルへ対応づけられます

条件付き書式、リッチテキスト、プール化されたスタイルはレポートのラストマイルで、データモデルとレイアウトが固まった後に適用されます。それより前の段階はHotXLSによるテンプレートベースのレポート生成の主題です。ルール、ラン、スタイルの完全なリファレンスはHotXLS Delphiコンポーネントの製品ページにあります