ColdFusionカフェテリア
SAMURAIZ

 

| ↑トップへ戻る |

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

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

■はじめに

こんにちは!株式会社ウイングの樋熊です。
過去2回の連載では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タグで店舗別売上のデータ取得を行う。
     ↓
 A 関数SpreadSheetReadで出力用エクセルファイルの読み取りを行う。
     ↓
 B 関数SpreadsheetSetCellValueでAで取得したエクセルファイルに@のCFQUERYタグで取得したデータの書き込みをで行う。
     ↓
 C 関数SpreadsheetWriteでBで作成したエクセルファイルをサーバに保存する。
     ↓
 D CFHEADERタグ、CFCONTENTタグを使い、サーバに保存したエクセルファイルをダウンロードさせる(ダウンロード後、サーバからファイルを削除)。

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

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

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

 

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

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

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

 

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

 

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

<cfcomponent>
    <cfprocessingdirective pageencoding="UTF-8" />
    <!---
    ================================================================
    機能  : 店舗別売上明細データ取得
    関数名 : fGetSales
    引数  : DataSource:データソース名
    戻り値 : qGetSales :取得したクエリ
    ================================================================
    --->

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

■完成したエクセル

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

■ダウンロード

テーブルを作成するスクリプトとマスタの初期データを登録するスクリプト、プログラムソース、エクセルテンプレートは以下から
ダウンロードして下さい。
  ファイル名 ファイルサイズ  
Download db3.zip 277kb ・サンプル用 Apache Derbyデータベース
・DBのテーブル、マスタデータ作成用のスクリプト
※第1回のデータベースに第3回で使用しているテーブルを追加
 したものとなります。

Download

source3.zip 35kb ・プログラムソース(第1回+第2回+今回(第3回)
・エクセルテンプレート

■参考

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


例1)円グラフ表示


例2)エクセルの集計機能

■さいごに

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

ColdFusionの各種情報の配信


最新情報
■2017/6/26
『ColdFusion 実験室』
実験6

■2017/3/27
ColdFusion 2016 対応
インストール セットアップ情報
CFサーバーのインストール
CFビルダーのインストール

■2016/9/20
『ColdFusion 2016 リリース
Enterprise Edition 活用資料』
記事一覧

■2016/3/30
『ColdFusion 実験室』
実験4、実験5

■2015/8/10 〜
『ColdFusion クリニック』
記事一覧(随時更新中)

■2015/4/8
『JasperReportsによる帳票出力』
2. JasperReportsによる帳票出力【後編】

■2015/3/12
アップクロス株式会社
西元 貞昭様
『JasperReportsによる帳票出力』
1. JasperReportsによる帳票出力【前編】


■2015/2/25
『ColdFusionでエクセルを使いこなそう!』
4. 【上級】ColdFusionのエクセル機能を利用したPDF帳票の紹介

ColdFusionトレーニング


ColdFusionユーザーグループ
ColdFusionユーザーグループ
Copyright 2012 Samuraiz Corporation. All Rights Reserved.