【初級】ColdFusionでエクセル取込

作成日: 2014年4月23日
作成者: 株式会社ウイング樋熊隆康
公開日: 2014年5月14日

■はじめに

こんにちは！株式会社ウイングの樋熊です。
前回の連載第１回では、ColｄFusionでマスタの一覧データをエクセル出力する機能を紹介しました。
ColdFusionの関数SpreadSheetReadでエクセル出力が簡単に実装できることを体感できたかと思います。
今回の連載第２回では連載第１回で作成した社員一覧から出力したエクセルデータを変更して
エクセル取込からマスタデータの登録、更新する機能を紹介します。
是非実際に動かしてみてください。

■エクセル取込サンプル

今回紹介するエクセル取込機能は出力したエクセルデータを変更し、エクセル取込画面から「アップロード」ボタンを押すと
マスタデータを登録、更新する機能として実装します。

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

ファイル名	説明
UserUpload.cfm	社員一括取込画面
UserUpload_Result.cfm	社員一括取込結果画面

エクセル取込画面イメージ：[UserUpload.cfm]

エクセル取込結果画面イメージ：[UserUpload_Result.cfm]
アップロード成功時

アップロード失敗時

■エクセル取込処理の流れ

アップロードボタンを押した時の処理の流れは以下の通りです。

準備　　取込用エクセルファイルの用意

　①　cffileタグでサーバー上のディレクトリにファイルをコピーする。
　　　　　↓
　②　cfspreadsheetタグで取込用エクセルファイルの読み取りを行う。
　　　　　↓
　③　アップロードしたファイルの削除を行う。
　　　　　↓
　④　エクセルデータの入力チェックを行う。
　　　　　↓
　⑤　④の入力チェックでエラーがない場合、エクセルファイルのデータが社員マスタに存在するかCFQUERYタグで
　　　検索処理を行う。
　　　　　↓
　⑥　⑤の検索処理でデータが存在する場合、CFQUERYタグで更新処理を行う。
　　　　　↓
　⑦　⑤の検索処理でデータが存在しない場合、CFQUERYタグで登録処理を行う。

■【準備】取込用エクセルファイルの用意

まず事前にエクセル取込する元のエクセルデータを準備します。
前回紹介しました社員一覧画面の「エクセル出力」ボタンを押下し、社員一覧データを出力します。
ダウンロードしたエクセルを開いて内容を変更して保存します。

取込用エクセルファイル：[社員一覧XXXX.xls]

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

CFFILEタグを使ってサーバー上のディレクトリに取込用のファイルをコピーします。

【参考】 ColdFusion 10 CFML リファレンス CFFILE
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fa1.html

サーバー上のディレクトリに取込用のファイルをコピーサンプルプログラム：[UserUpload_Result.cfm]

<cfset sUpdDir = ExpandPath("temp\upload\") >　　　


<cffile action="upload" filefield="form.temp_filename" destination="#sUpdDir#" nameconflict="overwrite" />

■②取込用エクセルファイルの読み取り

cfspreadsheetタグを使い、サーバ上に保存されている取込用エクセルファイルの読み込みを行います。
actionのread指定でXLS形式のファイルの内容を読み取ります。
※<cfspreadsheet action="read" src="ファイルパス\ファイル名" query="変換されたスプレッドシートファイルを保管するクエリー名" sheet="シートの番号">

【参考】 ColdFusion 10 CFML リファレンス cfspreadsheet
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html

【ポイント】
サンプルではサーバに保存した取込用エクセルファイルの保存パス、ファイル名を変数で定義しています。
※「temp\upload」フォルダ内に取込用エクセルファイルを保存

取込用エクセルファイル読み取りのサンプルプログラム：[UserUpload_Result.cfm]

<cfset sUpdDir = ExpandPath("temp\upload\")>　　　
<cfset sUpdFileNM = "">　　　　　　　　　　　　　　　　　 


<cffile action="upload" filefield="form.temp_filename" destination="#sUpdDir#" nameconflict="overwrite" />
<cfset sUpdFileNM = "cffile.clientFile">　　　


<cfspreadsheet action="read" src="#sUpdDir##sUpdFileNM#" query="qData" sheet="1">

Excelシートからどのようにデータが読み込まれたかを取り急ぎ確認したい場合は、
<cfdump var="#qData#">を使って確認してみると良いでしょう。

【参考】 ColdFusion 10 CFML リファレンス cfdump
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ef7.html

■③アップロードしたファイルの削除

CFFILEタグを使ってサーバー上にアップロードしたファイルの削除処理を行います。
②でエクセルファイルを読み取った内容はサーバに保持されるため、このタイミングでエクセルファイルを削除して問題無いです。

【参考】 ColdFusion 10 CFML リファレンス CFFILE
http://help.adobe.com/ja_JP/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fa1.html

サーバー上のディレクトリに取込用のファイルをコピーサンプルプログラム：[UserUpload_Result.cfm]

<cfset sUpdDir = ExpandPath("temp\upload\")>　　
<cfset iColumn = cffile.clientFile>　　　　　　　　　　　


<cffile action="delete" file="#sUpdDir##sUpdFileNM#">

■④エクセルデータの入力チェックを行う。

社員番号、名前、ふりがな、性別、年齢、生年月日項目の入力チェックを行います。

項目名	チェック内容
項目名	データ存在	必須	桁数	半角英数字	半角数字	全角ひらがな、カタカナ	日付
社員番号	○	○	○（8桁以下）	○
名前			○（50桁以下）
ふりがな			○（50桁以下）			○
性別			○（5桁以下）
年齢			○（3桁以下）		○
生年月日							○(yyyy/mm/dd形式)

【ポイント】
②で読み取ったエクセルデータの内容をクエリー形式の変数で保持しています。
cfspreadsheetは1セルずつ行番号、列番号を指定して取込するクエリの項目名を設定するため、
取込する列数（項目数）、行数はLOOP処理を行い、qData[列数][行数]で入力チェックを行います。
エクセルファイルの1行目、2行目はタイトル行のため、入力チェックは3行目から行います。
社員番号の必須チェック、桁数チェック、半角英数字チェックを例として紹介します。
入力チェックエラー時は社員データの登録・更新処理は行わず画面上にエラーメッセージを表示します。

エクセルデータの入力チェックのサンプルプログラム：[UserUpload_Result.cfm]

<cfloop query = "qData" startRow = "3">
　　<cfset iRowIdx = qData.CurrentRow>
　　<cfset iNullCnt = iNullCnt + 1>
　　
　　<cfif qData['col_1'][iRowIdx] eq '' or (len(qData['col_1'][iRowIdx]) gt 8 or Refind("^[A-Za-z0-9]+$",qData['col_1'][iRowIdx]) neq 1)>
　　　　<cfset aErrSyo[1] = "社員番号を半角英数8桁以内で入力してください。">
　　　　<cfset iRowErr = 1>
　　</cfif>
</cfloop>

■⑤データ存在チェック

CFQUERYタグを使ってエクセルファイルのデータが社員マスタに存在するか検索処理を行います。
データ存在の有無によって、⑥更新処理、⑦登録処理と処理を分けます。

存在チェックの検索処理のサンプルプログラム：[UserUpload_Result.cfm]

<cfquery name="qSelUpdTarget" datasource="#Application.dsname#">
    SELECT *
    FROM M_USER
    WHERE USER_ID = <cfqueryparam value="#qData['col_1'][iRowIdx]#" cfsqltype="CF_SQL_VARCHAR" maxlength="8">
</cfquery>

■⑥更新処理

CFQUERYタグを使ってエクセルファイルの社員番号が社員マスタに存在する場合、マスタデータの更新処理を行います。

更新処理のサンプルプログラム：[UserUpload_Result.cfm]

<cfloop query = "qSelUpdTarget">

  <cfquery name = "qInsData" datasource = "#Application.dsname#">
    UPDATE M_USER
    SET USER_NM = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qData['col_2'][iRowIdx]#">
          ,USERKANA_NM = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qData['col_3'][iRowIdx]#">
          ,SEX = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qData['col_4'][iRowIdx]#">
          ,AGE = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qData['col_5'][iRowIdx]#">
          ,BIRTHDAY = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qData['col_6'][iRowIdx]#">
    WHERE USER_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#qSelUpdTarget.USER_ID#">
  </cfquery>
</cfloop>

■⑦登録処理

CFQUERYタグを使ってエクセルファイルの社員番号が社員マスタに存在しない場合、マスタデータに登録処理を行います。

登録処理のサンプルプログラム：[UserUpload_Result.cfm]

<cfquery name="qInsData" datasource="#Application.dsname#">
    INSERT INTO M_USER (
                       USER_ID
                       ,USER_NM
                       ,USERKANA_NM
                       ,SEX
                       ,AGE
                       ,BIRTHDAY
     ) VALUES (
                       <cfqueryparam value="#qData['col_1'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="8">
                       ,<cfqueryparam value="#qData['col_2'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="50">
                       ,<cfqueryparam value="#qData['col_3'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="50">
                       ,<cfqueryparam value="#qData['col_4'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="5">
                       ,<cfqueryparam value="#qData['col_5'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="3">
                       ,<cfqueryparam value="#qData['col_6'][iRowIdx]#" cfsqltype="cf_sql_varchar" maxlength="10">
     )
</cfquery>

■ダウンロード

プログラムソースは以下からダウンロードして下さい。

	ファイル名	ファイルサイズ
Download	db1.zip	140kb	・サンプル用 Apache Derbyデータベース・DBのテーブル、マスタデータ作成用のスクリプト ※第１回と同じデータベースです。既にダウンロードされている方は、　db1.zipは不要です。
Download	source2.zip	16kb	プログラムソース（第１回＋今回（第２回））、エクセルテンプレート

■さいごに

今回はcfspreadsheetタグを使ってエクセル取込のプログラムを作りました。
cfspreadsheetタグを使用し、取込用のエクセルデータを読み込み、簡単にマスタデータを登録、更新することができます。

前回ご紹介致しましたエクセル出力機能と今回ご紹介致しましたエクセル取込機能を用いて簡単にマスタデータのメンテナンスが
可能です。
今回のサンプルではデータの削除処理や複雑な入力チェック、入力チェック時のエラーメッセージの詳細表示は割愛していますが、
これらの処理を加えることでより便利な機能を実装することができます。

また、入力チェックのプログラムの実装の他にダウンロード時のエクセルファイルに入力規制やプルダウンリストを加えることで
エクセルファイルの中で入力チェック、制限を掛けることも可能です。
実際にシステム実装する際は利用者のITスキルや利便性を考えてエクセルファイル内の制御も視野に入れた方がよいでしょう。