【中級】ColdFusionで実装するエクセル集計機能

作成日: 2014年10月20日
作成者: 株式会社ウイング樋熊隆康
公開日: 2014年10月27日

■はじめに

こんにちは！株式会社ウイングの樋熊です。
過去２回の連載ではColdFusionの関数SpreadSheetReadを使ったエクセルのデータ出力、データ取込みを紹介しました。
SpreadSheetReadはエクセルの操作する機能ですが、エクセルの便利な機能をそのまま生かすこともできます。
今回は皆さんが大好き（笑）なエクセルのピボットテーブルとColdFusionのSpreadSheetReadを組み合わせた例を紹介します。
エクセルの集計機能やグラフ表示などを組み合わせることで分析レポートを簡単に作れてしまいます。

■テーブル構成

エクセル出力する売上データの構成は以下の通りです。

売上トラン

論理名	Key
受注No	P
商品No
売上店舗No
削除フラグ

商品マスタ

論理名	Key
商品No	P
価格
商品名
削除フラグ

店舗マスタ

論理名	Key
店舗No	P
店名
削除フラグ

■マスタデータ検索サンプル

今回紹介するエクセル出力機能は店舗別売上ダウンロード画面の「ダウンロード」ボタンを押すと
店舗別の売上データ一覧と、店舗別・商品別売上のグラフを表示します。

サンプルを構成するファイルは以下の通りです。

ファイル名	説明
SalesTop.cfm	店舗別売上ダウンロード画面
SalesOutput.cfm	エクセル出力処理
SalesTemplate.xls	エクセル出力テンプレートファイル

店舗別売上ダウンロード画面イメージ：[SalesTop.cfm]

■エクセル出力の処理の流れ

CFの処理自体は「【初級】ColdFusionでエクセル出力」の掲載内容と全く同じです。
違う点は準備段階の出力用のエクセルファイルの用意と、データ取得を行うクエリ部分だけです。

準備　　ピボットテーブル出力用エクセルファイルの用意

　①　CFQUERYタグで店舗別売上のデータ取得を行う。
　　　　　↓
　②　関数SpreadSheetReadで出力用エクセルファイルの読み取りを行う。
　　　　　↓
　③　関数SpreadsheetSetCellValueで②で取得したエクセルファイルに①のCFQUERYタグで取得したデータの書き込みをで行う。
　　　　　↓
　④　関数SpreadsheetWriteで③で作成したエクセルファイルをサーバに保存する。
　　　　　↓
　⑤　CFHEADERタグ、CFCONTENTタグを使い、サーバに保存したエクセルファイルをダウンロードさせる（ダウンロード後、サーバからファイルを削除）。

それでは、前回と異なっている部分、「ピボットテーブル出力用エクセルファイル」の設定を説明します。

■ピボットテーブル出力用エクセルファイルの用意

店舗別売上明細出力：[list]シート
・店舗別の売上明細を出力するためのシートです。
・一番上の行にはダミー値を設定しておきます。
　　（[店舗別売上]シートでカラム未設定を出力しないようにするためです）
　　→エクセル生成処理で上書きされますのでダミー値は何でも構いません

店舗別売上グラフ：[店舗別売上]シート
・グラフを表示するシートです。
・まず、店舗別、商品別の売上ピボットテーブルを作成します。
・作成したピボットテーブルを選択し、積み上げグラフを挿入します。

■①ディレクトリにファイルをコピー

CFQUERYタグを使って店舗別売上のデータを取得します。

【ポイント】
今回のサンプルでは画面上の一覧表示とダウンロード時のデータ取得で同じSQL文を発行することから
ColdFusionコンポーネント（CFC）を使ってデータ取得部分を共通関数化します。

ColdFusionコンポーネントを利用した店舗別売上のデータの取得サンプルファイル：[Common.cfc]
※関数fGetSalesは引数のデータソース名を渡すと、：店舗別売上明細データ取得して取得したクエリを返します。

<cfcomponent>
    <cfprocessingdirective pageencoding="UTF-8" />
    
    <cffunction name="fGetSales">
        <cfargument name="DataSource" required="yes" type="string">

        
        <cfquery name="qGetSales" datasource="#DataSource#">
        SELECT     M_STORE.STORE_NO AS COLUMN_1,
                       M_STORE.STORE_NM AS COLUMN_2,
                       M_PRODUCT.PRODUCT_NM AS COLUMN_3,
                       M_PRODUCT.PRICE_NM AS COLUMN_4
        FROM       T_SALES
        LEFT JOIN
            M_STORE
            ON       T_SALES.SALES_STORE_NO = M_STORE.STORE_NO
        LEFT JOIN
            M_PRODUCT
            ON       T_SALES.PRODUCT_NO = M_PRODUCT.PRODUCT_NO
        WHERE
            T_SALES.DELETE_FG = '0'
            AND     M_STORE.DELETE_FG = '0'
            AND     M_PRODUCT.DELETE_FG = '0'
        ORDER BY
            M_STORE.STORE_NO,
            M_PRODUCT.PRODUCT_NO
        </cfquery>

        <cfreturn qGetSales>
    </cffunction>

</cfcomponent>

上記CFC関数fGetSalesを利用してデータ取得するサンプルプログラム：[SalesTop.cfm]、[SalesOutput.cfm]
※関数fGetSalesを実行して返り値（店舗別売上明細データ取得クエリ）を変数qGetSalesにセットします。

<CFINVOKE component="cafe.common.common" method="fGetSales" returnvariable="qGetSales">
<CFINVOKEARGUMENT name="DataSource" value="#Application.dsname#">
</CFINVOKE>

【初級】ColdFusionでエクセル出力との違いの説明は以上になります。
以降の②エクセル読み取り～⑤ダウンロードまでの処理は【初級】ColdFusionでエクセル出力と同様の処理のためサンプルは割愛します。
■　②出力用エクセルファイルの読み取り　※[list]シートを読み取り
■　③クエリ取得データのエクセルファイル書き込み<font color="red">※[list]シートに取得した店舗別売上明細データを出力
■　④エクセルファイルをサーバに保存
■　⑤エクセルのダウンロード

■完成したエクセル

以上の処理を組み込み、ダウンロードするとこちらのエクセルが出力されます。
今までエクセルで一生懸命作っていた集計一覧やグラフがシステムから出力できるととても便利ですね！
しかもエクセルのまま出力されるのでソート機能で絞り込んだり、加工したり様々な用途でそのまま利用できます。

■ダウンロード

テーブルを作成するスクリプトとマスタの初期データを登録するスクリプト、プログラムソース、エクセルテンプレートは以下から
ダウンロードして下さい。

	ファイル名	ファイルサイズ
Download	db3.zip	277kb	・サンプル用 Apache Derbyデータベース・DBのテーブル、マスタデータ作成用のスクリプト ※第１回のデータベースに第３回で使用しているテーブルを追加　したものとなります。
Download	source3.zip	35kb	・プログラムソース（第１回＋第２回＋今回（第３回））・エクセルテンプレート

■参考

今回はピボットテーブルと積み上げ棒グラフを表示しましたが、エクセル側の設定を変えれば他にも下記のような見やすい表を間単に作ることができます！

例１）円グラフ表示

例２）エクセルの集計機能

■さいごに

今回はColdFusionのエクセル出力機能とエクセルのピボットテーブルの機能を組み合わせ、積み上げ棒グラフを表示しました。
コツはエクセルの得意分野はエクセルに任せてしまうことです。
これは２つのメリットがあります。
１つめは運用で使っているエクセルがそのまま有効活用できることです。
２つめは表示系の変更を行いたい場合、プログラム修正は不要で出力用のエクセルファイルを変更することで対応できる点です。
アイデア次第で様々な表示や機能が実装できますので、是非ColdFusionとエクセル機能の組み合わせを試してみて下さい。