【初級】ColdFusionでエクセル出力
作成者: 株式会社ウイング 樋熊隆康
公開日: 2014年2月13日
■はじめに
はじめまして！株式会社ウイングの樋熊です。
簡単な自己紹介になりますが、弊社はColdFusionの4.5から10までのバージョンの実績があります。
ColdFusionはトータルで2001年から10年以上の開発実績になります。
弊社はColdFusionのバージョンアップ（進化）と共に開発実績を重ねており、特にバージョン7以降のColdFusionの新機能は開発の幅を広げてくれました。
ColdFusionMX7のレポート機能、ColdFusion8のAJAX機能、ColdFusion9のエクセル連携はその代表と言えます。
その中でもcfspreadsheetによるエクセル連携はアイデア次第で様々なシステム実装が可能で面白い機能の１つです。
エクセルの利便性とColdFusionの手軽さを組み合わせることで、ColdFusionの開発効率と運用の手軽さや便利さを実感できるでしょう。
エクセル連携も色々な実装方法がありますが、今回は初級編としてマスタの一覧データをエクセル出力する機能を紹介します。
題材は社員マスタのマスタデータのエクセル出力のサンプルプログラムになります。
■社員マスタのテーブル構成
エクセル出力する社員マスタは以下の構成とします。| 論理名 | Key |
|---|---|
| 社員番号 | P |
| 氏名 | |
| ふりがな | |
| 性別 | |
| 年齢 | |
| 生年月日 |
■マスタデータ検索サンプル
今回紹介するエクセル出力機能はマスタデータ検索画面に「エクセル出力」ボタンを押すと検索結果の一覧データをエクセル出力する機能として実装します。
サンプルを構成するファイルは以下の通りです。
| ファイル名 | 説明 |
|---|---|
| UserList.cfm | 社員一覧画面 |
| UserOutput.cfm | 社員一覧エクセル出力処理 |
| UserTemplate.xls | 社員一覧出力用エクセルファイル |
社員一覧画面イメージ：[UserSearch.cfm]
■エクセル出力の処理の流れ
エクセル出力ボタンを押した時の処理の流れは以下の通りです。
準備 出力用エクセルファイルの用意
① CFQUERYタグで社員マスタからデータ取得を行う。
↓
② 関数SpreadSheetReadで出力用エクセルファイルの読み取りを行う。
↓
③ 関数SpreadsheetSetCellValueで②で取得したエクセルファイルに①のCFQUERYタグで取得したデータの書き込みを行う。
↓
④ 関数SpreadsheetWriteで③で作成したエクセルファイルをサーバに保存する。
↓
⑤ CFHEADERタグ、CFCONTENTタグを使い、サーバに保存したエクセルファイルをダウンロードさせる（ダウンロード後、サーバからファイルを削除）。
■【準備】出力用エクセルファイルの用意
まず事前にエクセル出力する元のエクセルテンプレートを作成し、サーバ上に保存します。
【ポイント】
ColdFusionからは社員マスタから取得したデータの出力のみを行います。
そのため一覧のタイトルや装飾、ページ設定などは事前にエクセルで作成しておきます。
当サンプルでは下記の設定をエクセルファイルに行いました。
・ ページレイアウトで印刷用のタイトル行、フッターにページ番号を設定
・ タイトル行（1行目～2行目）の作成
・ タイトル行に並び変え用のフィルタを追加
・ 2行目以降のデータ出力行に罫線を追加
・ 社員番号列（A列）の書式設定を「文字列」に設定
・ 性別列（D列）の書式設定に「センタリング」を設定
出力用エクセルファイル：[UserTemplate.xls]
■①データ取得
CFQUERYタグを使って社員マスタから出力するデータを取得します。
【ポイント】
後にエクセルファイルの出力をループ処理し易いように、SELECTする項目名をCOLUMN_1、COLUMN_2、・・・・COLUMN_6に変えています。
社員マスタからデータ取得のサンプルプログラム：[UserOutput.cfm]
<!--- 社員マスタデータを取得 --->
<cfquery name = "qGetList" datasource="#Application.dsname#">
SELECT USER_ID AS COLUMN_1
,USER_NM AS COLUMN_2
,USERKANA_NM AS COLUMN_3
,SEX AS COLUMN_4
,AGE AS COLUMN_5
,BIRTHDAY AS COLUMN_6
FROM M_USER
ORDER BY USER_ID
</cfquery>
■②出力用エクセルファイルの読み取り
関数spreadsheetreadを使い、サーバ上に保存されている出力用エクセルファイルの読み込みを行います。
関数spreadsheetreadのパラメータは、読み込みを行うファイルの場所（パス）とファイル名、シート名を定義します。
※spreadsheetread（"ファイルパス\ファイル名","シート名"）
【参考】 ColdFusion 10 CFML リファレンス SpreadsheetRead
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSe9cbe5cf462523a0-7b585809122c5a12c54-8000.html
【ポイント】
サンプルではサーバに保存した出力用エクセルファイルの保存パス、ファイル名、シート名を変数で定義しています。
※「temp」フォルダ内に出力用エクセルファイルを保存
出力用エクセルファイル読み取りのサンプルプログラム：[UserOutput.cfm]
<!--- 読取情報 --->
<cfset temp_dir = ExpandPath("temp\")> <!---ファイルパス--->
<cfset temp_file_name = "UserTemplate.xls"> <!---ファイル名--->
<cfset sheet_name = "list"> <!---シート名--->
<!---エクセル生成--->
<cfscript>
//スプレッドシートオブジェクトを作成
spObj = spreadsheetread("#temp_dir##temp_file_name#","#sheet_name#");
</cfscript>
■③クエリ取得データのエクセルファイル書き込み
関数SpreadsheetSetCellValueを使ってクエリ結果をCFに読み込んだエクセルに出力します。
関数SpreadsheetSetCellValueのパラメータは、出力先オブジェクト、出力する値、出力先のセルの行番号、出力先のセルの列番号を定義します。
※SpreadsheetSetCellValue（出力先オブジェクト,出力する値,出力先のセルの行番号,出力先のセルの列番号）
【参考】 ColdFusion 10 CFML リファレンス SpreadsheetSetCellValue
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6731.html
【ポイント】
サンプルでは②で取得した社員マスタの項目数、データ件数を変数に定義しています。
関数SpreadsheetSetCellValueは1セルずつ行番号、列番号を指定して出力するクエリの項目名を設定するため、
出力する行数、列数（項目数）はLOOP処理を行い、出力するクエリの項目名（COLUMN_1、COLUMN_2、・・・・COLUMN_6）も変数で出力し易いように変えています。
クエリ取得データのエクセルファイル書き込みのサンプルプログラム：[UserOutput.cfm]
<cfset iRow = qGetList.recordcount> <!---出力件数--->
<cfset iColumn = listlen(qGetList.columnList)> <!---出力項目数--->
<!---エクセル生成--->
<cfscript>
//クエリを出力
for(j = 1;j lte #iRow# ;j=j+1){
for(i = 1;i lte #iColumn# ;i=i+1){
SpreadsheetSetCellValue(spObj,#qGetlist["COLUMN_#i#"][j]#,j+2,i); //値出力
}
}
</cfscript>
■④エクセルファイルをサーバに保存
関数SpreadsheetWriteを使ってクエリ結果を出力したエクセルファイルをサーバ上に保存します。
関数SpreadsheetWriteのパラメータは、出力元オブジェクト、書き込みを行うファイルの場所（パス）とファイル名を定義します。
※SpreadsheetWrite(出力元オブジェクト,"ファイルパス\ファイル名","上書き有無")
【参考】 ColdFusion 10 CFML リファレンス SpreadsheetWrite
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSe9cbe5cf462523a0-7b585809122c5a12c54-7fff.html
【ポイント】
サンプルではサーバに保存するエクセルファイルの保存パス、ファイル名を変数で定義しています。
※「temp」フォルダ内にエクセルファイルを保存
エクセルファイルのサーバ保存のサンプルプログラム：[UserOutput.cfm]
<!--- 出力情報 --->
<cfset output_dir = ExpandPath("temp\")> <!---ファイルパス--->
<cfset output_file_name = "社員一覧" & #DateFormat(Now(),"yyyymmdd")# & #TimeFormat(Now(),"HHmmss")# &".xls"> <!---ファイル名--->
<cfscript>
//workフォルダにエクセル生成
SpreadsheetWrite(spObj,"#output_dir##output_file_name#","yes");
</cfscript>
■⑤エクセルのダウンロード
cfheaderタグ、cfcontentタグを使ってサーバ上のエクセルファイルをダウンロードさせます。
【ポイント】
cfheaderタグのvalue="attachment; filename=ファイルパス\ファイル名"指定で、ブラウザ上からダウンロード処理が可能です。
cfcontentタグのdeleteFile="yes"指定で、ダウンロード後にサーバ上に作成したエクセルファイルは削除されます。
エクセルのダウンロードのサンプルプログラム：[UserOutput.cfm]
<!--- ダウンロード指示 --->
<cfheader name="Content-Disposition" value="attachment; filename=#output_file_name#">
<cfcontent type="text/comma-separated-values" file="#output_dir##output_file_name#" deleteFile="YES">
■完成したエクセルダウンロードのサンプルプログラム
完成したエクセル出力のサンプルプログラムは以下の通りです。
エクセルのダウンロードのサンプルプログラム：[UserOutput.cfm]
<cfprocessingdirective suppressWhitespace="yes">
<cfsilent>
<cfprocessingdirective pageEncoding="utf-8" />
<!--- 読取情報 --->
<cfset temp_dir = ExpandPath("temp\")> <!---ファイルパス--->
<cfset temp_file_name = "UserTemplate.xls"> <!---ファイル名--->
<cfset sheet_name = "list"> <!---シート名--->
<!--- 出力情報 --->
<cfset output_dir = ExpandPath("temp\")> <!---ファイルパス--->
<cfset output_file_name = "社員一覧" & #DateFormat(Now(),"yyyymmdd")# & #TimeFormat(Now(),"HHmmss")# &".xls"> <!---ファイル名--->
<!--- 社員マスタデータを取得 --->
<cfquery name = "qGetList" datasource="#Application.dsname#">
SELECT USER_ID AS COLUMN_1
,USER_NM AS COLUMN_2
,USERKANA_NM AS COLUMN_3
,SEX AS COLUMN_4
,AGE AS COLUMN_5
,BIRTHDAY AS COLUMN_6
FROM M_USER
ORDER BY USER_ID
</cfquery>
<cfset iRow = qGetList.recordcount> <!---出力件数--->
<cfset iColumn = listlen(qGetList.columnList)> <!---出力項目数--->
</cfsilent>
<!---エクセル生成--->
<cfscript>
//スプレッドシートオブジェクトを作成
spObj = spreadsheetread("#temp_dir##temp_file_name#","#sheet_name#");
//クエリを出力
for(j = 1;j lte #iRow# ;j=j+1){
for(i = 1;i lte #iColumn# ;i=i+1){
SpreadsheetSetCellValue(spObj,#qGetlist["COLUMN_#i#"][j]#,j+2,i); //値出力
}
}
//サーバにエクセル生成
SpreadsheetWrite(spObj,"#output_dir##output_file_name#","yes");
</cfscript>
<!--- ダウンロード指示 --->
<cfheader name="Content-Disposition" value="attachment; filename=#output_file_name#">
<cfcontent type="text/comma-separated-values" file="#output_dir##output_file_name#" deleteFile="YES">
</cfprocessingdirective>
■ダウンロード
テーブルを作成するスクリプトとマスタの初期データを登録するスクリプト、プログラムソース、エクセルテンプレートは以下からダウンロードして下さい。| ファイル名 | ファイルサイズ | ||
|---|---|---|---|
| Download | db1.zip | 140kb | ・サンプル用 Apache Derbyデータベース ・DBのテーブル、マスタデータ作成用のスクリプト |
| Download | source1.zip | 11kb | プログラムソース、エクセルテンプレート |
■さいごに
今回は３つのSpreadsheet関数（SpreadsheetRead、SpreadsheetSetCellValue、SpreadsheetWrite）を使ってエクセル出力のプログラムを作りました。
出力用に装飾したエクセルファイルを用意することで、単純なプログラムでエクセル出力が実装できます。
サンプルでは紹介しませんでしたがSpreadsheet関数の中にセルの書式設定（SpreadsheetFormatCell）や、ヘッダー（SpreadsheetSetHeader）など変更する関数もあります。
ColdFusionのプログラムの中で１つ１つエクセルファイルの設定や装飾を行うことも可能ですが、
設定を行ったエクセルファイルを用意した方が遥かに楽で、変更時のメンテナンスもしやすいメリットがあります。
エクセルでできることはエクセルで行うことがエクセルの利便性を生かして、ColdFusionの手軽さと組み合わせることがポイントです。
ColdFusion 基礎プログラミング
記事一覧（随時更新）
■2022/6/28
Adobe ColdFusion 2021
インストール セットアップ情報
CFサーバーのインストール
■2019/11/27
『ColdFusion 実験室』
実験７
■2019/11/27
Adobe ColdFusion 2018
インストール セットアップ情報
CFサーバーのインストール
■2017/6/26
『ColdFusion 実験室』
実験６
■2017/3/27
ColdFusion 2016 対応
インストール セットアップ情報
CFサーバーのインストール
CFビルダーのインストール
■2016/9/20
『ColdFusion 2016 リリース
Enterprise Edition 活用資料』
記事一覧
■2016/3/30
『ColdFusion 実験室』
実験４、実験５
■2015/8/10 ～
『ColdFusion クリニック』
記事一覧（随時更新中）
ColdFusionユーザーグループ
