ColdFusion 9のcfspreadsheetタグを使ってEXCELファイルを書き出してみる

津熊 弘和

2011.10.21

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

今回は新しくColdFusion 9から追加されたタグ（ cfspreadsheet タグ ）の検証をしてみます。以下はサンプルソースです。

<!--- EXCELファイルの書き出しパスを設定する --->
<cfset APL_Excel_Dir = "C:\inetpub\wwwroot\temp">

<!--- テンポラリディレクトリにあるログインユーザ名＋時間.xlsのファイルを削除する --->
<cfdirectory 
	directory="#APL_Excel_Dir#"
	action="list"
	name="qDirList"
	filter="testuser*.xls"
	type="file">

<!--- 該当のファイルが存在する場合は、そのテンポラリファイルを削除する --->
<cfif #qDirList.RecordCount# neq 0>
	<cfloop query="qDirList">
		<cffile action="delete" file="#APL_Excel_Dir#\#qDirList.Name#">
	</cfloop>
</cfif>

<!--- EXCELに書き出したいクエリを発行 --->
<!--- ここのクエリは適宜環境に応じて置き換えてください --->
<cfquery name="qRead" datasource="blogDB">
	SELECT Report_Date,Order_No,Project,Digestion_Time,Person_Name,Remark_Text
	FROM	sample_table
	where	Person_Name = 'testuser'
	ORDER BY Report_Date asc
</cfquery>

<!--- クエリで返ってきたレコード数をセットする。 --->
<cfset qRead_cnt = #qRead.RecordCount#>

<!--- 合計の式を設定するためにレコード数を+1しておく --->
<cfset qRead_sum = #qRead.RecordCount#+1>

<cfscript>
    //ユニークなファイル名にするための変数生成
    t_time = Dateformat(now(),'YYYYMMDDHHMMSS');
    
    ///絶対パスでEXCELファイル名の位置を指定する 
    theFile= #APL_Excel_Dir# & "\testuser" & #t_time# & ".xls"; 
    
    //スプレッドシートのオブジェクトを生成（通常の.xlsとして作成します。）
    //theSheet = SpreadsheetNew("tcData","true");
    theSheet = SpreadsheetNew("tcData"); 
    
    //ヘッダをEXCELの1行目、1カラム目から追加する
    SpreadsheetAddRow(theSheet,"日付,作業コード,作業内容,時間,名前,備考",1,1); 
    
    //クエリの結果をEXCELに書き出す
    SpreadsheetAddRows(theSheet,qRead); 
    
    //トータルレコード数＋3行目、3カラム目に合計を追加する。
    SpreadsheetAddRow(theSheet,"合計",#qRead_cnt#+3,3); 
    
    //トータルレコード数＋3行目、4カラム目に合計値の計算式を追加する。
    SpreadsheetSetCellFormula(theSheet,"SUM(D2:D#qRead_sum#)",#qRead_cnt#+3,4);
    
</cfscript> 

<!--- EXCELのセル幅を指定する。 --->
<cfset SpreadSheetSetColumnWidth(theSheet,1,11)> 
<cfset SpreadSheetSetColumnWidth(theSheet,2,15)>
<cfset SpreadSheetSetColumnWidth(theSheet,3,62)>
<cfset SpreadSheetSetColumnWidth(theSheet,4,5)>
<cfset SpreadSheetSetColumnWidth(theSheet,5,13)>
<cfset SpreadSheetSetColumnWidth(theSheet,6,60)>

<!--- cfspreadsheetタグで実際にサーバ上のディレクトリにEXCELファイルを書き出す --->
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" sheet="1" sheetname="qRead" overwrite="true">

<!--- cfspreadsheetで書き出されたEXCELファイルにロケーションを飛ばす ---> 
<cflocation url="./temp/testuser#t_time#.xls">

【まとめ】
60行目～65行目で列の幅指定を個別に設定しているので、お分かりかと思いますが、それと同様で罫線の描画は非常にめんどくさいことになりそうです。。。SpreadsheetFormatCellとSpreadsheetFormatRowを駆使して描画することになります。これは時間をかければできますがあまりやりたくない作業内容です。固定の帳票ならありかもしれませんが。。。あと55行目にありますが計算式の設定ができることはなかなか良いなと思いました。（EXCELなんで当たり前でしょうけど）今回のサンプルでは.xls形式のファイルを作成していますが、SpreadsheetNewのオプションであるxmlformatをtrue指定するとMicrosoft Office Excel 2007以降でサポートされている形式である.xlsxファイルも作成できるようです。（42行目のコメント部分を外して43行目をコメントにする。）

AWS