条件付き書式の困りどころ
- Excelで行・列をコピーペーストすると、新たな条件付き書式が定義される。(増殖する)
- もともとあった条件付き書式の適用先が、貼り付けられた範囲のところで分断されて汚くなる
- 汚いだけならいいが、動作が重くなる
- ググると書式をまとめてくれるVBAが公開されているが、サンプルだったり、そもそもVBAなので読みにくかったりして心もとない
対処方法
Excelアドインを作った。VBAでなくVSTOで作ったのでコーディングしやすく、配布先でのインストールもexeで一発で嬉しい。
GitHub standstonecraft/SscExcelAddIn: 自分の業務効率化のためのExcelアドインです。
RelaxToolsに実装済みかも知れないけど、もしそうだったら悲しいので確認してません。
注意：大量の書式がある場合、数秒～数十秒フリーズすると思われます
それと他にも正規表現置換、連番、図形文字列埋め込み・書き出し・検索、交互行選択、空行削除など、方眼紙いじりに便利な機能を自分用に追加していってます。使いたかったら自己責任でどうぞ。アンインストールは「設定＞アプリ＞アプリと機能」で「SscExcelAddIn」をアンインストール。
以下はVSTO開発時の備忘録。
実装のポイント
統合対象(同一書式)の判定
書式の文字列表現やIDがあるわけでもないので、ルールとか書式設定を全部比較して、多分同じだろという判定を行う。
今回はどんなルール・書式設定でも対応できるように、 FormatCondition object (Excel) | Microsoft Docs に合わせてモデルとできる限り全部のプロパティを作成し、IEquatable<T>で等価性比較できるようにしてGroupByでグルーピングした。
しかし、プロパティの型がドキュメントから読み取れなかったりデバッグで型を見分けられなかったりした部分があるので、そのあたりで誤判定やクラッシュがあるかもしれない。(ルールの数式はちゃんと出来てるからよほど凝ったExcelじゃなきゃ平気でしょ)
数式ルールの相対化
数式は適用先の1セル目を起点とする相対参照で表現されるので、増殖して適用先が変わると同じ数式かどうかわからなくなる。
そこで、Application.ConvertFormula()関数を使ってR1C1形式に変換することで、適用先がどこになろうと同じ数式として等価性比較できるようにする。
formula1 = (string)Globals.ThisAddIn.Application.ConvertFormula(fc.Formula1,
Excel.XlReferenceStyle.xlA1, Excel.XlReferenceStyle.xlR1C1,
RelativeTo: fc.AppliesTo[1, 1]);
条件付き書式削除時のクラッシュ
統合して不要になった書式をFormatCondition.Delete()で消すのだが、FormatConditionsから取り出したリストのループ中で削除すると無言でクラッシュする。
Excel VBA hard crash on ModifyAppliesToRange with no error message - Stack Overflow を見つけて解決。FormatConditionsを直接ループしている中で消すのであれば怒られても仕方ないが、これはバグだと言いたくなるのもわかる。
プロパティ取得時のクラッシュ
COMオブジェクトの状態？実際の型？によって取得できないプロパティを触ろうとするとクラッシュする。
e.g. 書式の条件が「次の値の間」でないときFormula2に触ると例外発生
…かといっていちいちtryしたくないのでこのような関数を作った。(他の値型、参照型も同様に作成)
SscExcelAddIn/Funcs_OrDefault.cs at master · standstonecraft/SscExcelAddIn
// in class Funcs
public static int? OrDefault<TO>(TO obj, Func<TO, int> f)
{
try
{
return f.Invoke(obj);
}
catch
{
return null;
}
}
使い方は
int? result = Funcs.OrDefault(theObj, e => e.IntPropOfTheObj);
取れなきゃnullって乱暴だしバグが埋もれそうだけど、楽になりすぎて濫用した。
既知の問題
条件付き書式には優先度(上からの並び順)があるが、マージすると思ったような優先度にならない場合がある。