ColdFusionカフェテリア
SAMURAIZ

 

| ↑トップへ戻る |

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

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

■はじめに

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

■エクセル取込サンプル

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

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

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

 

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

 

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

 

アップロード失敗時

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

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

 

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

 @ cffileタグでサーバー上のディレクトリにファイルをコピーする。
     ↓
 A cfspreadsheetタグで取込用エクセルファイルの読み取りを行う。
     ↓
 B アップロードしたファイルの削除を行う。
     ↓
 C エクセルデータの入力チェックを行う。
     ↓
 D Cの入力チェックでエラーがない場合、エクセルファイルのデータが社員マスタに存在するかCFQUERYタグで
    検索処理を行う。
     ↓
 E Dの検索処理でデータが存在する場合、CFQUERYタグで更新処理を行う。
     ↓
 F Dの検索処理でデータが存在しない場合、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" />

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

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">   <!---ファイル名--->

<!--- Excelデータ読み取り --->
<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

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

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

 

【参考】 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#">

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

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

 

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

 

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

 

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

<!--- 入力チェック(3行目〜) --->
<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>

■Dデータ存在チェック

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

 

存在チェックの検索処理のサンプルプログラム:[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>

■E更新処理

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>

■F登録処理

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のテーブル、マスタデータ作成用のスクリプト
※第1回と同じデータベースです。既にダウンロードされている方は、
 db1.zipは不要です。

Download

source2.zip 16kb プログラムソース(第1回+今回(第2回))、エクセルテンプレート

■さいごに

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

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


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


最新情報
■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.