MZ Platformエクセル連携導入チュートリアル

１．エクセル派遣管理アプリケーションの概要

MZ Platformでは［データベースアクセス］コンポーネントを使用することによって、既存のエクセルファイルに対して、 データの 読み 込みと書き込みを行うアプリケーションを作成できます。
この機能を実現するために、データベースシステムで用いられる SQL Structured Query Language のコマンドを送信しています。
すなわち、 MZ Platform ではエクセルも SQL 系のデータベースの 1 つとして扱われています。
なお、 SQL のコマンド等詳細に関しましては市販の参考書をご 覧下さい。
参考＜デー タベース説明＞SQL について＞

1.実行できる操作

  1. 以下のSQLによるエクセルに対する操作が可能です。
    2. エクセルファイルに対する操作 詳細 送信する SQL のコマンド
    読み込み シート一覧の取得
    各シートのデータ一覧取得
    データの検索    		 SELECT
    書込み データの追加
    データの更新    		 INSERT
    UPDATE
    削除 データの削除 DELETE
  2. エクセルでの制限事項
      1. Select文
        結合はinner joinのみ有効です。
        例）select sheet1.列1,sheet2.列2 from sheet1 inner join sheet2 on sheet1.列1＝sheet2.列2
      2. Insert文　
        列名は必須です。
        例）Insert into sheet1 (列1,列2……）values (値1,値2......)はOK。
        Insert into sheet1 values (値1,値2......)はエラーが発生する。
      3. Insert時の日付書式　
        'yyyy/MM/dd HH:mm:ss'とします。
      4. Update文
        where句が必須です。
        例）Update sheet1 set 列1＝値1 where 列2=値2はOK。
        Update sheet1 set 列1＝値1だけではエラーが発生する。
      5. 副問い合わせは使用できません。

2.接続時の引数設定

  1. メソッドの引数
    データベースに接続するためには、［データベースアクセス］コンポーネントのメソッド［データベースに接続する (String,String,String, ］を用います。
    各引数の意味は次のとおりです。
    第 1 引数：ドライバ名
    第 2 引数：データベースの URL
    第 3 引数：ユーザ名
    第 4 引数：パスワー ド
    エクセルのファイルに接続する場合、第 2 引数のデータベースのURLにエクセルファイルまでのパスを指定し、ドライバ名、ユーザー名、パスワードは必要ありません。
この派遣管理アプリケーションは、エクセルのデータを MZ Platform に取り込み、ガントチャートで表示することを目的としています。
エクセルファイル
エクセルデータdispatch.xlsx は、派遣管理表であり、派遣者が、どの派遣先に、いつからいつまでの期間派遣されるのかを表しています。
また、派遣先に対し請求書を発行する際の伝票番号も管理していま す。

派遣管理アプリケーション

派遣管理アプリケーションでは、まず「テーブル表示」ボタンをクリックすると、エクセルデータ dispatch.xls と連携し、 データを テーブル型データとして取得し、［テーブル］に設定します。
［テーブル］のデータを派遣者を項目、派遣先を系列に据え、いつからい つまでの期間派遣されるのかを表したガントチャート（帯状グラフ）として表現します。
またガントチャー トの各帯のタスク名として伝票番号も表示します。

２．テーブル表示画面の作成

1.ファイル選択画面作成

［テキストフィールド］に設定したファイルのパスを使って、［データベースアクセス］コンポーネ ントでエクセルファイルに接続します。
直接入力もできますが、［ファイル選択ダイアログ］を使用し、ファイルの絶対パスを取得できるようにします。
コンポーネント追加
  1. 以下のコンポーネントを追加します。
    2. コンポーネント名 必要数 カテゴリー ｺﾝﾎﾟｰﾈﾝﾄｷｰ
    フレーム 1 画面構成部品＞ウィンドウ 派遣管理一覧
    ラベル ２ 画面構成部品＞テキスト
    テキストフィールド ２ 画面構成部品＞テキスト ファイル名
    シート名
    ボタン １ 画面構成部品＞ボタン
    パネル １ 画面構成部品＞パネル
    ファイル選択 １ 画面構成部品＞ダイアログ
画面編集
  1. 画面編集画面に入り、パネルを配置します。
  2. 画面編集画面の左側ツリー領域の［パネル］をクリックします。
    編集領域がパネルの領域に変化します。
  3. ［ファイル選択］以外のコンポーネントをパネル上に横方向整列で配置します。
  4. 画面編集画面を終了し、［実行（設定可）］ボタンで実行します。
    ラベルのテキストを「ファイル名」と「シート名」に、ボタンのテキストを［選択」に変更します。

接続処理
  1. ファイル選択ダイアログで選択したファイル名を［テキストフィールド］に設定する
    1. 【接続1-1】［ボタン（接続）］を押したら［ファイル選択］を表示します。
    2. 【接続2-1】ファイル名が選択されたら、［テキストフィールド］に設定します。
      ［ファイル選択］のファイルを「開く」ボタンが押されたときのみ処理を行い、「取消」ボタンが押されたときは処理を行わないよう に、イベント番号１を設定します。
　完成画面例

［編集画面図1］ ※右側の接続先コンポーネントをクリックすると、起動メソッド情報の画像が表示されます。

１.エクセルファイルへの接続

［テキストフィールド］に設定したファイル名のファイルへ［データベースアクセス］コンポーネントを使って接続します。
コンポーネント追加
  1. 以下のコンポーネントを追加します。
    2. コンポーネント名 必要数 カテゴリー ﾃｷｽﾄ/ｺﾝﾎﾟｰﾈﾝﾄｷｰ
    データベースアクセス 1 入出力＞データベース
    ボタン １ 画面構成部品＞ボタン テーブル表示
    テーブル １ 画面構成部品＞テーブル 派遣一覧
    文字列格納変数 １ 処理部品＞変数 検索SQL
画面編集
  1. 画面編集画面に入り、ボタンとテーブルを配置します。
  2. 画面編集を終了し、[実行（設定可）」ボタンで実行してボタン名を「テーブル表示」に変更します。
接続処理の作成
  1. エクセルファイルへの接続
    1. 【接続3-1】ボタンを押してエクセルファイルに接続します。
      引数１にエクセルファイルまでのパスを［テキストフィールド（ファイル名）］から取得し、設定します。
      その他の引数欄は空白のままにします。
    2. 【接続3-2】エクセルとの接続を切断します。
      エクセルファイルがMZ Platformによって使用されている間は、EXCELを直接開くことができないため、必要な処理が終 わったらすぐに切断するようにします。

    ※ここまででボタンを押して、エラーが出なければ接続ができています。

２.検索結果の取得

  1. エクセルファイルの検索
    1. 【接続3-3】文字列格納変数にSelect文の前半部分を設定します。
      引数に「Select ID, 氏名, 派遣先, 開始日, 終了日, 伝票番号 from 」と設定します。
      「from」の後ろにも半角スペースを入れてください。
    2. 【接続3-4】変数内でシート名をSelect文字列に連結します。
      シート名は予め［テキストフィールド（シート名）］に入力しておきます。
      実行すると「Select ID, 氏名, 派遣先, 開始日, 終了日, 伝票番号, from plan2」という文字列ができます。
    3. 【接続3-5】Select文を実行します。
      ［文字列格納変数］内の文字列を引数とします。
      イベント番号は１を設定しておきます。

    ※【接続3-2】のデータベースとの切断処理は一番最後になるようにドラッグして処理の順番を入れ替えます。
  2. 検索データの設定
    1. 【接続4-1】検索結果を［テーブル］に設定します。
      結果の返るSQLの実行が行われるとデータ生成イベントが発生します。
      データ生成ベントの接続先に［テーブル］を設定します。
　完成画面例

［編集画面図2］

３．ガントチャート表示

１.グラフ作成複合コンポーネントとは

グラフを表示するときには、各 グラフの書式 に則ったテーブ ルデータを用意しなければなりませんが、［グラフデータ作成］複合コン ポーネントを使うと簡単に テーブルデータを整理して、グラフ表示ができます。
参考：グラフ化複合コンポー ネントチュートリアル＞グ ラフ データ作成複合コンポーネントとは
　　　グラフ化複合コンポー ネントチュートリアル＞ガント チャートグラフ表示 　 　　

コンポーネント追加
  1. 以下のコンポーネントを追加します。
    コンポーネント名 必要数 カテゴリー テキスト
    ボタン １ 画面構成部品＞ボタン ガントチャート表示
    ガントチャート １ 画面構成部品＞グラフ
    グラフデータ作成 １ 複合コンポーネント追加＞グラフ
  2. グラフデータ作成複合コンポーネントの追加の仕方
    アプリケーションビルダー編集画面で右クリック＞[複合コンポーネント追加]＞[グラフ]＞[グラフ化モジュール.mzcx]を選び ます。

画面編集
  1. 画面編集画面で［ボタン］と［ガントチャート］を配置します。
  2. 「実行（設定可）」ボタンで実行し、ボタンのテキストを『ガントチャート化』に変更します。
接続処理の作成
  1. ガントチャート用データの作成
    2. ［グラフデータ作成］に引数としてテーブル データを設定し、項 目、系列、開始日時、終了日時に列 名または列番号を指定することでガントチャート用に整理したテーブルデータを作成できます。
    1. 【接続5-1】ガントチャート用テーブルデータを複合コンポーネント内で作成します。
      （引数設定例）

      		説明 取得方法 コンポーネント メソッド／値
      引数０ テーブルデータ メソッド戻り値 テーブル テーブルデータを取得する
      引数１ 系列列 固定値 ー
      		 氏名
      引数２ 項目列 固定値 ー
      		 派遣先
      引数３ 開始日時列 固定値 ー
      		 開始日
      引数４ 終了日時列 固定値 ー
      		 終了日
    2. 【接続5-2】タスク名用のテーブル デー タを複合コンポーネント内で作成します。
      タスク名はテーブル型のデータによって一括で設定することができます。
      先にガントチャート用データ作成を行っていることが必要です。
  2. ガントチャートへデータ設定
    1. 【接続5-3】作成されたテーブルデータをガントチャートに設定します。
    2. 【接続5-4】作成されたタスク名データをガントチャートに設定します。
  3. 全系列の色を設定する
    4. 同系列は同じ色で 表示されるように色のリストを設定します。
    1. 【接続5-5】系列色のリストを設定します。
  4. 属性設定の変更
    1. 表示開始日、表示期間、時間軸の大目盛間隔、小目盛間隔などを変更します。
      （変更例）表示期間：1か月　大目盛間隔：1週間　小目盛間隔：1日
      1. ガントチャート上で右クリック＞［ガントチャート］＞［時間軸］＞［表示期間...］を選択します。
        設定窓で１月を入力し、「適用」ボタンを押します。
      2. ガントチャート上で右クリック＞［ガントチャート］＞［時間軸］＞［大目盛間隔..］を選択します。
        設定窓で１週を入力し、「適用」ボタンを押します。
      3. ガントチャート上で右クリック＞［ガントチャート］＞［時間軸］＞［小目盛間隔...］を選択します。
        設定窓で１日を入力し、「適用」ボタンを押します。
    2. タスク名を表示できるようにします。
      ガントチャート上で右クリック＞［ガントチャート］＞［タスク］＞［タスク名］＞［表示する］を選択します。
完成画面例

［編集画面図3］

１.初期化・終了処理の作成

接続処理の作成
  1. 終了処理
    2. アプリケーションビルダーのアプリケーション終了イベントに接続します。
    1. 【接続6-1】データベースとの接続を切断します。
      アプリケーション終了時には必ず切断するようにします。
    2. 【接続6-2】テーブルの全行列を削除します。
    3. 【接続6-2】ガントチャートのデータをクリアします。
    4. 【接続6-2】グラフ作成複合コンポーネントの終了処理を行います。
  2. 初期化処理
    1. 【接続0-1-2】グラフ作成複合コンポ―ネントを初期化します。

［編集画面図4］

４．分類テーブル作成コンポーネント（参考）

１.分類テーブルの作成

ガントチャートへ設定する基本のデータは1列目が項目列、2列目以降に2列一組で日付データのテーブルデータです。
エクセルやデータベースで蓄積 されるデータの形式とは違うことが多いですが、［グラフデータ作成］複合コンポーネントを使って整理することができます。
複合コンポーネントの中では［分類テーブル作成］コンポーネントを使用してテーブルデータを分類しています。
ここでは簡単に分類の考え方について説明します。　　

分類の大まかな流れ
  1. 大項目列と大系列列を指定します。
    ここでは「大項目：氏名」、「大系列：派遣先」とします。
    ※大、中、小項目または系列を指定することができ、さらに細かい分類も可能です。
  2. 項目と系列で分類します。
    行データはリスト型データとして格納されます。
    このテーブルデータは［グラフデータ作成］複合コンポーネントで取得できる「ガントチャート用に分類されたテーブル」です。
  3. セルを2つに分け、値に開始日と終了日を指定します。
    ガントチャート用のテーブルデータとなります。
  4. ［グラフデータ作成］複合コンポーネントでは、２と３のテーブルデータの他にもタスク名のテーブルや系列色のリストを取得することができ ます。
    参考：グラフ作成複合コン ポーネントチュートリアル＞グ ラフ化複合コンポーネ ントの引数設定方法

５．ガントチャート更新の反映

ガントチャートの帯を移動して表示を変更することができますが、実際のエクセルファイルが変更されているわけではありません。
ガントチャートの表示を変更したら、［データベースアクセス］コンポーネントでUpdate文を実行して、エクセルファイルの行を更新するよ うに します。

１.更新されたデータの取得

ガントチャートの帯を選択したとき、「作業項目インデックス」と「系列インデックス」をメソッドで取得できます。
このインデックスを使って、『４．分類作成コンポーネント』で説明した「分類テーブル」から選択された 帯のデータの情報を知ることができま す。

コンポーネント追加
  1. 以下のコンポーネントを追加します。
    コンポーネント名 必要数 カテゴリー コンポーネントキー
    テーブル格納変数 １ 処理部品＞変数 ガントチャート用分類テーブル
    リスト格納変数 １ 処理部品＞変数 選択された受注データ

接続処理の作成
  1. 分類テーブルの設定
    2. 接続元は［ボタン（ガントチャート化）］です。
    1. 【接続5-6】ガントチャート用に分類されたテーブルを変数に格納します。
    2. 【接続5-7】第0列目を削除します。
      分類テーブルは第０列目が項目名列でデータ列ではないため、系列インデクスで指定したときに位置が合うように削除します。
  2. ガントチャート更新時のデータ取得
    1. 【接続7-1】ガントチャート用分類テーブルからセルの値を取得します。
      引数に現在選択されている項目のインデックスを使用して、分類テーブルからデータを取得します。
      取得したデータはリスト型のデータです。
      （例［1,本郷猛,ヨタヨタ自動車,2023/3/2,2023/3/6,a12］）
    2. 【接続7-2】取得したデータをリスト格納変数に格納します。
ガントチャート変更時
（例：作業項目インデックス：１
系列インデックス：２）
ガントチャート用分類テーブルのイメージ

［編集画面図5］

ガントチャートが更新されたとき、タスクの開始日時、終了日時を取得しますが、取得した日付データはDate型のため、文字列として見ると「 Wed Sept 15 09: 00 00 JST 2007 」といった表現になっています。
［カレンダー］コンポーネントを使うと書式を指定して日付型のデータを文字列型のデータとして取得することができます。

ここでは［ファンクション］を先頭に文字列変換の機能を作成しておきます。

１.［カレンダー］コンポーネント　

日付や時刻の演算処理を行うコンポーネントです。
日時の加算、減算や経過時間の取得などができます。
設定するデータの型はCalenderまたはDate型ですが、日 付書式を指定して文字列で設定も可能です。
また、日付データを書式を指定して文字列として取得することもできます。

２.［ファンクション］コンポーネント

［ファンクション］には任意の数の引数を呼び出し時、または事前に設定することができます。
この引数は他のコンポーネントのメソッド実行の引数として利用することができ、処理の呼び出しとデータ（引数）の受け渡しを一連の処理にまと められます。

メソッド「ファンクションの呼び出し」を実行すると、［ファンクション］からは｢処理要求イベント｣が発生します。
処理要求イベントは最後に接続されているメソッドの戻り値を取得するようになっていて、この値が「ファンクションの呼び出し」の戻り値となり ます。
ファンクションの処理要求イベントの接続先処理が済んだ後で、ファンクションの処理の結果を戻り値として利用できます。

コンポーネント追加
  1. 以下のコンポーネントを追加します。
    コンポーネント名 必要数 カテゴリー ｺﾝﾎﾟｰﾈﾝﾄｷｰ
    ファンクション １ 処理部品＞変数 日付文字列取得
    カレンダー １ 処理部品＞システム　
    またはユーティリティー

接続処理の作成
  1. 日付データの設定
    1. 【接続8-1】日付データをカレンダーに設定します。
      引数にはファンクションの第1引数を指定します。
      （後でファンクション呼び出し時にガントチャートの選択されている開始日または終了日のデータを設定します。）
    2. 【接続8-2】書式を指定して、日付文字列を取得します。
      引数０（日付書式）は「yyyy/MM/dd HH:mm:ss」とします。
      ここで取得した日付文字列は、後でファンクションを呼び出したときの戻り値となります。
ファンクションの使用


［編集画面図6］

［文字列格納変数］に更新SQL文の雛形を設定し、［ガントチャート］の更新（選択されている） データの開始日、終了日、ID番号で置換えます。

コンポーネント追加
  1. 以下のコンポーネントを追加します。
    コンポーネント名 必要数 カテゴリー ｺﾝﾎﾟｰﾈﾝﾄｷｰ
    サブルーチン １ 処理部品＞サブルーチン エクセル更新と読込
    文字列格納変数 １ 処理部品＞変数 データ変更用SQL

接続処理の作成
  1. 更新SQLの雛形設定
    1. 【接続9-1】雛形文字列を変数に設定します。
      「Update plan2 set 開始日='START', 終了日='END' where ID=NO」を設定します。
  2. 開始日を取得して雛形文更新
    1. 【接続9-2】ガントチャートの選択データの開始日を取得します。
      引数は選択されている項目インデックスと選択されている系列インデックスです。
    2. 【接続9-3】日付を書式指定で文字列に変換します。
      先に接続処理を設定しておいた［ファンクション（日付文字列取得）］を呼び出します。
    3. 【接続9-4】雛形の文字列の「START」と日付データを置換えます。
  3. 終了日を取得して雛形文更新
    1. 【接続9-5】ガントチャートの選択データの終了日を取得します。
    2. 【接続9-6】日付を書式指定で文字列に変換します。
    3. 【接続9-7】雛形の文字列の「START」と日付データを置換えます。
      引数の取得方法は「メソッド処理結果」です。
      メソッド処理結果選択窓には同名の選択候補が現れますが、2番目の候補を選択します。
  4. ID列のデータを取得して雛形文更新
    1. 【接続9-8】ガントチャートの選択データのIDを取得します。
      事前にリストに格納してあるリストデータ（例［1,本郷猛,ヨタヨタ自動車,2023/3/2,2023/3/6,a12］）か ら0番目のID番号データを取得します
    2. 【接続9-9】雛形の文字列の「NO」とID番号を置換えます。
  5. ガントチャートを更新したら、雛形文を更新する

    6. 接続元コンポーネントは［ガントチャート］です。
    1. 【接続7-3】ガントチャートが更新されたら、［サブルーチン］を呼び出します。
※ここで一度実行して、Update文が置換わっているか確認します。
実行した後［文字列格納変数（データ変更用SQL）］をダブルクリックして、コンポーネント属性情報の「String」の項を確認してくださ い。
（例: Update task set 開始日='2017/11/24 06:45:00',終了日='2017/11/25 04:00:00' where id=7）
文字列格納変数の属性情報

［編集画面図7］

作成した更新SQL文を［データベースアクセス］コンポーネントで実行します。
更新SQLを実行すると、更新された行数は取得可能ですが、更新後のテーブルデータは取得されません。
検索SQLも後から実行して、テーブルの表示も更新するようにします。

接 続処理の作成
  1. データベースに接続する
    2. 接続9-10、9-12、9-13は接続3-1、3-5、3-2をコピーすると簡単です。
    1. 【接続9-10】データベースに接続します。
    2. 【接続9-11】更新SQLを実行します。
      イベント番号は２です。
      （※ここまでで一旦実行して、ガントチャートを変更してエラーが起こらないことを確認するとよいでしょう）
    3. 【接続9-12】検索SQLを実行します。
      変更された結果を再検索します。
      テーブルにデータが再設定されます。
    4. 【接続9-13】データベースとの接続を切断します。

［編集画面図8］

ここまで作成して実行し、ガントチャートを変更すると、エクセルデータにも反映されるようになります。