PHPExcelでDB2データからExcelスプレッドシートを作成する
注: この記事に記載のコードはここからダウンロードできます。
より詳細な分析のために、多くのユーザーがレポートから手作業でスプレッドシートに入力しているのは驚きです。PHPExcel というツールを使用して、ユーザーの代わりに直接スプレッドシートに書き込むことができます。ここでは、PHPExcel を使用して、DB2 から簡単なスプレッドシートをインストールして、作成するための手順をご紹介します。
はじめに
以下は Power Systems on IBM i で PHPExcel を使用するための要件です。
- Zend Server がインストールされ、稼働している
- PHPExcel をダウンロードし、インストールしている
この記事では Zend Server がすでにインストールされていることを前提としています。まだインストールしていない場合は、Zend Server Installation for IBM i のリンクをご覧になるか、検索エンジンを使用してください。ソフトウェアのバージョンを最新の状態にするのはもっともなのですが、最新版の Zend Server をインストールしておく必要はありません。(私の場合は、PHPExcel をバージョン 5.0.x の Zend Server で使用しました。)
PHPExcel のインストールは極めて簡単です。https://phpexcel.codeplex.com/ に移動して、ZIP ファイルを自分が選んだフォルダーにダウンロードし、好みのアーカイブ・ツールで解凍します。ZIP ファイルには、以下のフォルダーとファイルが入っています。
- Classes--PHPExcel コードが入っています
- Documentation--最新の資料すべてが入っています
- Examples--PHPExcel の使用方法の例が入っています
- changelog.txt--製品のすべてのアップデートのリストが入っています
- install.txt--PHPExcel のインストール方法に関する指示が入っています (必ず一読のこと)
- license.txt--ライセンス交付情報が入っています
「Classes」フォルダーを IFS にコピーします。このフォルダーは好きな場所に置いてかまいません。Web アプリケーションで PHPExcel を使用する場合、Web ルート・フォルダー下に入れておいたほうがよいでしょう。紹介する例はすべて、CL プログラムから PHP スクリプトを呼び出していますが、Web からも使用することができるように、Web ルート内にインストールしている状態をお見せします。
これだけです。これで PHPExcel を使用できるようになります。
最初のスクリプト
PHP スクリプトを始める前に、基本的なハウスキーピング・タスクをいくつか実行します。まず、PHPExcel で提供された関数を参照する必要があります。
// Require PHPExcel Classes
require_once '/www/zendsvr/htdocs/Classes/PHPExcel/PHPExcel.php';
次に、自分のスクリプトに渡したいパラメーターを取り出すことができます。この例では、「年」と「月」のパラメーターを取り出そうとしています。スクリプトがブラウザーの URL から呼び出されている場合、パラメーターは $_GET 配列から利用できます。スクリプトが CL プログラムから呼び出されている場合は、パラメーターは引数配列から利用できます (第 1 引数 $argv[0] は常に実行中のスクリプトの名前です):
// パラメーターを取得する
$year = ( $_GET['year'] ? $_GET['year'] : $argv[1] );
$month = ( $_GET['month'] ? $_GET['month'] : $argv[2] );
ハウスキーピングの最終ビットは IBM i データベースに接続するためのものです。
/データベースを設定する
$database = "SYSTEMNAME";
//*Blanks のユーザー名とパスワードを使用すると QTMHHTTP がデフォルトで使用される
$username = "";
$password = "";
//以下の行は db2 オプションを設定する。ライブラリー・リストは、複数のライブラリーをスペースで区切って表示できる
$options = array("i5_libl" => "LIBRARYA LIBRARYB", "i5_naming" =>
DB2_I5_NAMING_ON);
//db2 データベースに接続する
$conn = db2_connect($database, $username, $password, $options);
if(!$conn)
{
die("Connection Error");
}
これで PHPExcel を使用できるようになります。スプレッドシート・オブジェクトを作成し、どの属性でもかまいませんので、適切だと思う属性を割り当てます。ここでは詳しく説明しませんが、いくつか例を挙げます。
//PHPExcel オブジェクトおよび文書プロパティーを設定する
$objPHPExcel = new PHPExcel();
$title = 'My Report';
$objPHPExcel->getProperties()->setCreator("Bruce Guetzkow")
->setLastModifiedBy("Bruce Guetzkow")
->setTitle($title)
->setSubject($title)
->setDescription($title)
->setKeywords($title)
->setCategory("report");
書き込むアクティブなワークシートを設定する必要があります。いつもどおり、PHP は 0 を基準としたエレメントを使用しているため、最初のワークシートはエレメント 0 です。
//アクティブなシートの指標を設定する
$objPHPExcel->setActiveSheetIndex(0);
DB2 データを読み込む
データをいくつかスプレッドシートに追加してみましょう。「select」SQL ステートメントを変数にすることから始めます。
//SQL ステートメントを定義する
$sqlstmt ="select fielda, fieldb, fieldc
from myfile
where datayear = $year and datamonth = $month";
SQL ステートメントを準備する必要があります。
//SQL ステートメントを準備する
$stmt = db2_prepare($conn, $sqlstmt);
if(!$stmt)
{
$error = db2_stmt_errormsg();
die( "Error occured on prepare. " . $error);
}
これでステートメントを実行できるようになりました。
//SQL ステートメントを実行する
$result = db2_execute($stmt);
if(!$result)
{
$error = db2_stmt_errormsg();
die("Error occured on execute. " . $error);
}
PHPExcel は、SQL ステートメントで取り出した行全体を、ワンステップでスプレッドシートの行に読み込む簡単な方法を提供しています。個々にセルを読み込むこともできますが、これから始めるのが良いでしょう。
//データをスプレッドシートに読み込む
$currRow = 0;
while ($row = db2_fetch_array($stmt)) {
$currRow++;
$objPHPExcel->getActiveSheet()->fromArray($row, ' ', "A$currRow");
}
この時点で、スプレッドシートはまだ PHPExcel オブジェクト内にあります。今度はこれを IFS に書き込みます。
//スプレッドシートを書き込む
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("/myfolder/mySpreadsheet.xlsx");
自分でやってみる
これで、スプレッドシートを開き、DB2 ファイルから抽出したデータの行を表示できるようになりました。もちろん、必要なものは、見出し、合計、公式、またはフォーマットではなく、データです。次回は、スプレッドシートをちょっと派手にするような属性をいくつか取り上げましょう。
すでにご想像どおり、前に抽出した Documentation フォルダーには、文書に一味添えるための指示が入っています。基本がわかっていれば、他の設定にもトライしてみて、さらに驚きを発見してみてください。
