- はじめに
- 覚えてほしいこと
- やりたいこと - Excelの装飾
- 事前準備 - Install Extensions
- XLS Formatter Nodesの使い方
- ちょっと一言
- おわりに
- 参考リンク
はじめに
こんにちは、自動化大好きまっきーです。
中級編くらいまで終えられた方は、すでにかなりExcelの自動化ができるようになっているのではないでしょうか。
そうすると、少しだけ欲が出てくると思います。
「出力したExcelをもっと綺麗に見せたい。。。」
KNIMEでレポート作りを自動化した後、それを誰かに共有するかもしれません。そして共有する前に、背景色を変えたり、ヘッダの部分にフィルタを適用したり、太文字にしてみたり。。
いろんな作業をまだマニュアルでやっているかもしれません。そこも自動化してしまいたいですよね。
今回はその、痒いところまで手が届くNodeをご紹介します。
今回のテーマ ~XLS Formatter Nodes~
覚えてほしいこと
装飾情報はタグ情報をもとに、既存のExcel Sheetに上塗りするイメージ
やりたいこと - Excelの装飾
下記のことをやっていきたいと思います。
やりたいこと１ - 枠線をつける ~XLS Border Formatter~
全てのセルに枠線をつける
やりたいこと２ - 中央揃え ~XLS Cell Formatter~
列ヘッダの部分を中央揃え
やりたいこと３ - 太文字 ~XLS Font Formatter~
列ヘッダを太文字にする
部署が「営業」の時、太文字にする
やりたいこと４ - 塗りつぶし ~XLS Background Colorizer~
部署が「営業」の時、赤色で塗りつぶす
やりたいこと５ - フィルタ ~XLS Sheet Properties~
列にフィルタを適用する
やりたいこと６ - 条件付き書式 ~XLS Conditional Formatter~
生年月日を見て、
1975年01月01日（19750101）生まれをベテラン、
1995年01月01日（19950101）生まれを新人
と定義します。
このとき、ベテランに近いほど「青色」、新人に近いほど「黄緑色」という背景色のルールに基づいて色付け
事前準備 - Install Extensions
XLS Formatterはデフォルトではインストールされていません。追加でインストールする必要があります。まだこんなNode、自分のKNIMEにはないぞ！？と言う方はまず先に下記の記事をご覧ください。
ソフトウェアサイトはKNIME Hubから検索できます。
Continental Nodes for KNIME – KNIME Hub
PreferenceのAvailable Software Sitesに追加されていることを確認しましょう。
Available Software Sitesに追加できたら、Install KNIME Extensions からExtensionのタイトルを打ち込みます。KNIME HubのExtensionのタイトルと一致しているかと思います。
再起動を求められるので、再起動したら準備完了です。Node Repositoryでは、Community Nodeの下に表示されます。
XLS Formatter Nodesの使い方
Excelを装飾することができます。下記が注意点となります。
- 拡張機能が必要：Excel Writerで出力した後、追加情報としてExcelの装飾データを作成することができます。拡張機能でインストールする必要があります。
- 出力データの特性：通常のデータファイル(Excel Writer)の上に装飾データを上に塗っていくイメージとなります。
- 装飾データの上書き：一度出力した装飾データを、再度装飾データのみを上書きすることはできないようなので、再度装飾データを編集するときは、Excel Writerから一度出力し直す必要があります。
- 装飾Nodeの特性：装飾データをつけるNodeは、タグをもとに動きます。Workflowでは、まずタグをつけ、そのタグをもとに装飾データをつけていきます。
- 複数の装飾：複数装飾をつけたい場合は、複数タグをつけることで容易になります。複数タグをつけるときは、カンマでくぎります。
Workflow - XLS Formatter
大きく、２つの作業があります。
- タグ付け
- タグをもとに装飾
装飾データは元データとは別で動きます。まず最初の部分でタグ付けを行なっていきます。装飾を行いたいセルをタグで置き換えていきます。
Confirmを見ていただくとイメージがつくかもしれません。
緑色の四角いブロックが装飾データ、黒い矢印からタグテーブルの情報を受け渡しています。装飾データの出力は、変数で出力パスを、緑ブロックから装飾データを受け渡して出力します。
Workflowは下記からダウンロードできます。
Confirm - タグと装飾の組み合わせ
タグ付けが終わった段階の、「XLS Control Table from Cell Range」のOutputと、最終的なExcelを見てみましょう。
複数タグをつける場合は、カンマで区切ります。
各タグと、実行した装飾は下記の通りです。
- header：中央揃え（やりたいこと２）、フィルタの適用（やりたいこと５）
- Bold：太字（やりたいこと３）
- Red：赤色で塗りつぶし（やりたいこと４）
- Numeric：条件付き書式（やりたいこと６）
- 全て：枠線（やりたいこと１）
Configure - タグ付け部
まずはタグ付けの部分のNodeについて解説します。Rule Engineや、Constant Value Column についてはすでに扱ったことがあるのでちょっと一言に掲載します。
Configure - タグ付け用テーブルの生成 XLS Control Table Generator
タグ付け用のTableに変換するNodeです。Extract Column Headerのように、列ヘッダを抜き出すことができます。Outputを見ると、Excelの表示と同じようになるため、少しイメージつきやすいかもしれません。
Write Column header to first rowにチェックをつけておきましょう。
Configure - セル範囲からタグ付け XLS Control Table from Cell Range
セルの範囲をもとに、タグを追加したり、タグにセルの値を置き換えたりできます。
今回は、ヘッダの部分（A1:H1）にタグを２つ（"header, Bold"）をつけます。
最初は、"header"というタグをつけて、もう一つNodeを挿入して、”Bold”というタグをカンマを挟んで加えています。
Configure - 装飾部
タグが付いたら、次はタグをもとに装飾データをつけていきます。
Configure - 枠線の追加 XLS Border Formatter
やりたいこと１です。枠線を全てのセルにつけていきます。見る限り、前後で値が同じ場合、枠線はセルの結合のような形で、値が変わった部分のみ付くようです。
バグなのか元々のデザインなのかはわからないです。。
全てのセルにつけたいので、”applies to all tags”にチェックをつけます。
Outer Border Settingsで枠線の位置を指定します。また、その他のOptionでは枠線の種類や色、太さなどを指定できます。
Configure - セルの書式設定 XLS Cell Formatter
headerのタグのついたセルを中央揃えにしたいと思います。
タグを入力した後、Text Position and Layoutで「中央揃え」を選択できます。
その他、上下の位置や、回転の設定も可能です。
word wrapは、折り返し設定です。
Data Type and Formatではセルの書式設定で、小数点や日付の表示フォーマットなどを指定できます。
Configure - フォントの設定 ~XLS Font Formatter~
Boldというタグがついている場合に、太文字にする設定です。
そのほかにも、Italicで斜字にしたり、underlineで下線を引いたり、Change fort sizeで文字サイズ、Change Colorでフォントの色を調整できます。
Configure - 塗りつぶしの設定 ~XLS Background Colorizer~
Redというタグがついているセルを赤色で塗りつぶす設定です。
Pattern fillはExcelで塗りつぶしの詳細設定をみた時に出てくる項目です。
Configure - フィルタ ~XLS Sheet Properties~
headerタグにフィルタを適用します。
このほかにも、セルの固定、行や列の非表示も設定可能です。
Configure - 条件付き書式 ~XLS Conditional Formatter~
数値をもとに塗りつぶしを行う設定です。
生年月日（タグ名：Numeric）を見て、
1975年01月01日（19750101）生まれをベテラン、
1995年01月01日（19950101）生まれを新人
と定義します。
このとき、ベテランに近いほど「青色」、新人に近いほど「黄緑色」という背景色のルールに基づいて色付けを行う設定です。
中間値を設定することも可能です。
Configure - 書き出し部
最後に、装飾データをExcelに上書きしていきます。変数で出力パスを指定しているので、Excel WriterのConfigureからお見せします。相対パスって？という方は下記をご覧ください。
Configure - Excelに出力 - Excel Writer
まずExcel Writerで装飾予定のテーブルをExcelに書き出します。このとき、書き出したパスを変数にしておくと、後から再度指定しなくて済みます。
今回は、「OutputPath」という変数名で保存しました。また、パスは相対パスを使用しました。
Configure - Path型からString型に変換 - Path to String (Variable)
そのまま出力した変数を使えればいいんですが、変数のデータ型がPath型という形になっており、なぜかそのまま使えないのでString型に変換します。
このように、String型に変換したものはパスの形になっています。相対パスを使う設定にしたので、記述は相対パスの形になっています。
Configure - 装飾データを出力 - XLS Formatter(apply)
最後に、装飾データをExcelに書き出していきます。上書きしたいので、Input、Outputともに同じパスを指定します。先ほど作った変数を指定すると再度入力する必要がありません。
ちょっと一言
その他のNodeのConfigure
メインでないタグ付け部分のConfigureを掲載しておきます。以前解説したNodeになりますので、わからない場合は過去記事をご覧ください。
Rule Engine - 「営業」のタグ付け
Constant Value Column - 条件付き書式のタグ付け
おわりに
お疲れ様でした！今回が記念すべき100記事目でした！
約１年半、ひたすらに土日祝を潰して書き続けた甲斐もあり、KNIMEのIntroductionとしては十分な内容がカバーできたのではと思っています。View数の大半が、Python、Mac M1系の記事でしたし、特に見返りもないまま(笑) KNIMEについて書いてきましたが、みなさんの学習の支えに少しでもなったでしょうか？
初心者が十分KNIMEを使えるレベルまで成長するところまで、完成したと思っているので、今後は要望があれば書いていくペースでもいいかなーと思っています。私がお手伝いできることであれば、ぜひおっしゃってください。KNIMEでお仕事ができてくればいいですね。
ご連絡いただく際は、Twitterでいただけると反応しやすいです。
ではまた！
参考リンク
- すさんのBlog：
【KNIME】気休め程度に体裁を整えたエクセルファイルを吐き出す - 非プログラマーのためのインフォマティクス入門。（仮）
- KNIME公式Node Pit（英語）：
Continental Nodes for KNIME [Plugin] — NodePit
Continental Nodes for KNIME [Feature] — NodePit
- KNIME Example Workflow：
Excel Formatter - Excelの装飾 – KNIME Hub
-
Continental Nodes for KNIME — XLS Formatter Nodes （英語） :
Continental Nodes for KNIME | KNIME
Continental Nodes for KNIME — XLS Formatter Nodes | KNIME
- 今回新しく使ったNodeの詳細（英語）：
XLS Control Table from Cell Range – KNIME Hub
XLS Border Formatter – KNIME Hub
XLS Control Table Generator – KNIME Hub
XLS Sheet Properties – KNIME Hub
XLS Cell Formatter – KNIME Hub
XLS Conditional Formatter – KNIME Hub
Path to String (Variable) – KNIME Hub
XLS Background Colorizer – KNIME Hub
