プロデルで始める日本語プログラミング言語入門の第15話です。今回は、プロデルからExcelを操作して帳票印刷する方法について紹介したいと思います。プロデルは、エクセルと深い連携が可能で、基本的なエクセル操作をプロデルのプログラムで操作できます。この回では、Excel連携で使う種類やその構成について詳しく説明します。
Excelの操り方
プロデルでExcelのデータを加工したり制御したりするには「エクセル」種類を使います。
まずプロデルからExcelを起動してみましょう。プロデルでExcelを起動するには『エクセルを起動する』と書きます。
エクセルを起動する
エクセルへブックを追加する対応するExcelのバージョンは、Excel 2013以降です。Microsoft Storeで入手できるOfficeアプリには未対応ですので、Office永続版やMicrosoft 365でExcelをインストールしてください。
Excelを操作する時には、「ワークブック(ブック)」「ワークシート(シート)」「セル」の3つを主に使います。
普通にExcelを起動した時には、新しいブックに新しいシートが1つ作られています。
ワークブック(WorkBook)は、シートの集合でExcelの1つのファイル(.xlsx)を表します。
ワークシート(WorkSheet)は、画面に表示されているセルの集合で、一つのシートを行と列で表します。
セル(Range)は、画面に表示される値を格納できる一つの枠を表します。プロデルでは、複数のセルを選択した場合にも、その範囲を1つのセルとして一度に扱うことができます。
セルA1に文字を入れる
Excelを起動して列が「A」、行が「1」のセルに文字を入れるには次のようなプログラムを書きます。
エクセルを起動する
エクセルへブックを追加する
操作セルは、エクセルの現在シートのセル（「A1」）
操作セルの内容を「プロデル」に変える3行目の『エクセルの現在シート』の部分ではブックを作ったときに選択されたシートを表します。『セル（「A1」）』は、シートの中で特定のセルを取得する手順で、「A1」は1行目, A列のセルを表しています。セルを取得すると、そのセルに対しての操作ができます。
セルは、一度に複数のセルを選ぶこともでき、例えば「A1:C3」とすると、左上がA1で右下がC3の範囲にあるすべてのセルが選択されます。セルの指定方法には次のような形式があります。
- A1 → A1のセルを選択します
- A1:C3 → 左上がA1で右下がC3となる範囲のセルをすべて選択します
- A:A → A列をすべて選択します
- 1:1 → 1行目をすべて選択します
- A1,C3 → A1とC3を選択します(カンマ区切りで複数指定)
また、『セル({横,縦})』 『セル({横,縦,横2,縦2})』 とすると座標でセルの場所を指定できます。
カレンダーを作る
今回は1つ目に、プロデルでExcel上にカレンダーを作ってみます。今月のカレンダーをシート上に自動で作るプログラムを書いてみます。
セルの文字色やフォントサイズを変えるには
セルの「文字色」設定項目を使います。色はプロデルで使える色の名前を指定します。文字の大きさは、「フォントサイズ」設定項目を使います。
操作セルの文字色を赤に変える
操作セルのフォントサイズを16に変える
セルの大きさを変えるには
セルの「幅」設定項目と「高さ」設定項目を使います。幅の単位は文字数で、高さの単位はポイント(pt)です。
操作セルの幅を10に変える
操作セルの高さを50に変える
カレンダーの作り方
カレンダーはプロデルのサンプルフォルダにあるサンプルプログラムを改造してシート上にカレンダーを作ります。
シート上にカレンダーを描くには、日曜から土曜までの列と、日にちを書き始める行を決めておきます。まず曜日を書いてから、次のようなステップで1日から順番にその月の最後の日まで日にちを書いていきます。
- 1日目のセルを決めて「1」と書きます。
- 対象のセルを左の列へ移動して次の日にちを書きます
- 土曜の列まで書いたら次の行へ移動して再び日曜の列へ移動します
- 月末のまで日にちを書いていきます
日にちを書く時には、まずその月の1日が何曜日から始まるかを調べる必要があります。何曜日かは、ツェラーの公式を使います。この公式は、特定の日が何曜日かを計算するための式で、カレンダーを書く時に使います。
レイアウトを決めて指定する
年と月の見出し：セルD2に設定します。
曜日の見出し：セルB4:H4の範囲で順番に設定します。
日にち：セルB5からH9までの範囲で1日から順番に設定します。
セルの列の指定には、A,B,Cなどと指定します。繰り返し文では数値としてカウントされるのでこのような指定方法では不便なときがあります。 『(列番号+64)を文字化』とすると、列番号1をA、2をBといった具合に列番号に対応する文字を調べることができます。
完成したプログラム
エクセルを起動する
エクセルへブックを追加する
操作シートは、エクセルの現在シート
(今日の年)年(今日の月)月カレンダーを表示する
［表示年］年［表示月］月、カレンダーを表示する手順
日数は｛３１，２８＋表示年のうるう，３１，３０，３１，３０，３１，３１，３０，３１，３０，３１｝
//年月を書く
操作セルは、操作シートのセル(「D2」)
操作セルの内容を「<<［表示年］年［表示月］月>>」に変える
操作セルの太字を○に変える
操作セルのフォントサイズを16に変える
//曜日を書く
Zは、1－（（表示年）年、（表示月）月、１日、曜日番号）
LDは、日数（表示月）
見出しは、{「日」,「月」,「火」,「水」,「木」,「金」,「土」}
見出しの個数回、１から列番号にカウントして繰り返す
操作セルは、操作シートのセル((列番号+1+64)を文字化&「4」)
操作セルの内容を見出し（列番号）に変える
操作セルの文字配置を中央に変える
操作セルの幅を5に変える
列番号が1なら操作セルの文字色を赤に変える
他なら列番号が7なら操作セルの文字色を青に変える
そして
//日にちを書く
行番号は、5
ZからLDまでDを増やしながら繰り返す
列番号は、2
７回DからEを増やしながら繰り返す
操作セルは、操作シートのセル（(列番号+64)を文字化&行番号）
Eが１未満またはEがLDより大きいなら
他なら
操作セルの内容をEに変える
操作セルの幅を5に変える
そして
列番号が2なら操作セルの文字色を赤に変える
他なら列番号が8なら操作セルの文字色を青に変える
列番号を増やす
そして
Dに6を足す
行番号を増やす
そして
終わり
［Y］年［M］月［D］日、ツェラー式を求める手順
あは（Y／１００）を切り捨てたもの
いは、Yを１００で割った剰り
（[M×２．６－０．２を切り捨たもの]＋D＋い＋（い÷４を切り捨てたもの）＋（あ÷４を切り捨てたもの）＋５×あ）を7で割った剰りを返す
終わり
［Y］年［M］月［D］日、曜日番号を求める手順
Mが3未満なら
ツェラー式（（Y－１）年、（M＋１０）月、（D）日）を返す
他なら
（Y）年、（M－２）月、（D）日ツェラー式を返す
そして
終わり
［年］の、うるうを求める手順
年%4が0かつ(年%100が0でないまたは年%400が0)なら1を返す。そうでなければ0を返す
終わり
実行するとExcelのシートに今月のカレンダーが作られます。
罫線を引く
セルに罫線を引くには、まず罫線を引く範囲のセルを取得して、その「罫線の形」と「罫線のスタイル」設定項目を設定します。
例えばB2からD4のすべてのセルの四方に罫線を引くには、次のように命令します。
操作シートは、エクセルの現在シート
操作セルは、エクセルの選択シートのセル（「B2:D4」）
操作セルの罫線の形を「すべて」に変える
操作セルの罫線のスタイルを「実線」に変える「罫線の形」設定項目は、セルの線の引き方を表します。設定できる値は、次の通りです。
そして「罫線のスタイル」設定項目は、線の種類を表します。設定できる値と例は、次の通りです。
上記を組み合わせて指定するには、「罫線の形」設定項目と「罫線のスタイル」設定項目を連続で指定します。
操作セルの罫線の形を「なし」に変える
操作セルの罫線の形を「左」に変える
操作セルの罫線の形を「右」に変える
操作セルの罫線の形を「下」に変えるスケジュール帳を作る
スケジュール帳を作ってみましょう。
スケジュール帳の作り方
スケジュール帳には、3ヶ月分(今月,来月,再来月)の日付を書いていきます。1ヶ月ごとに縦方向へ日付と曜日を順番に書いていき、最後に罫線を引きます。
日付を書く時は「現在」変数に日付時刻型の値を入れておき1日ずつ増やしながらセルに日付を下方向へ埋めていきます。日付時刻型には、まず今日の日付を入れておきます。セルの日付を書き終わったら、「現在の翌日」を現在に入れて次の日へ進めていきます。
セルの行番号は「行番号」変数に格納します。また月ごとに列を分けるために「開始列番号」変数に現在の列の番号を入れておきます。
曜日に応じて色を変える
「曜日番号」設定項目でその日が何曜日かを0～6の数字で調べられます。もし「現在の曜日番号」で0ならば赤色、6ならば青色をセルの文字色に設定しておきます。
完成したプログラム
エクセルを起動する
エクセルへブックを追加する
操作シートは、エクセルの現在シート
開始は、今日の月初
終了は、今日から2ヶ月後の月末
現在は、開始
行番号は、3
開始列番号は、2
操作セルは、操作シートのセル（「B2」）
操作セルの内容を「［開始の年］/［開始の月］～［終了の年］/［終了の月］の予定表」に変える
操作セルの太字を○に変える
操作セルのフォントサイズを16に変える
現在が終了以下の間繰り返す
操作セルは、操作シートのセル（(開始列番号+64)を文字化&行番号）
操作セルの幅を10に変える
操作セルの内容を[現在を「MM/dd」に整えたもの]に変える
操作セルは、操作シートのセル（(開始列番号+1+64)を文字化&行番号）
操作セルの幅を5に変える
操作セルの内容を現在の曜日の1文字目に変える
現在の曜日番号が0なら操作セルの文字色を赤に変える
他ならもし現在の曜日番号が6なら操作セルの文字色を青に変える
現在の日が1なら
操作セルは、操作シートのセル（「[(開始列番号+64)を文字化][行番号]:[(開始列番号+2+64)を文字化][行番号]」）
操作セルの罫線の形を「上」に変える
そして
行番号を増やす
現在は、現在の翌日
現在の日が1なら
//次の月となったら3列分移動する
操作セルは、操作シートのセル（「[(開始列番号+64)を文字化]3:[(開始列番号+2+64)を文字化][行番号-1]」）
操作セルの罫線の形を「四角」に変える
操作セルの罫線のスタイルを「二重線」に変える
操作セルは、操作シートのセル（「[(開始列番号+2+64)を文字化]3:[(開始列番号+2+64)を文字化][行番号-1]」）
操作セルの幅を20に変える
操作セルの罫線の形を「四角」に変える
操作セルの罫線のスタイルを「二重線」に変える
開始列番号を3だけ増やす
行番号は、3
そして
そして帳票を印刷する
最後に、納品書を印刷するプログラムを作ってみます。
ここでは、私たちは「プロゾン」という仮想の小さな雑貨販売サイトを運営しているということにします。お客さんからネット注文があると、商品を段ボールに詰めて、一緒に納品書を付けて梱包します。この納品書を、プロデルで注文データから作り印刷します。
データに応じて、お届け先や商品の明細、合計金額を納品書に自動的に書くプログラムを作る所が今回のポイントです。
帳票を作る
まずは印刷する帳票のレイアウトをExcel上で作ってみます。ここでは「納品書」という次のようなレイアウトの雛形を作り「納品書.xlsx」とします。
(帳票のレイアウトは、プロデルのプログラムで作ることができますが少々手間が掛かるので、予め.xlsxファイルとして作っておく方が簡単ですね)
納品書のレイアウトの例は、次のリンクからダウンロードできるのでプログラムを作る時にはこれを使います。
場所を決める
帳票のレイアウトを作る際には、プロデルからどのセルに値を入力するか、決めておきます。具体的にはプロデルで書きたい部分のセルの場所を控えておきます。
この納品書では、次のセルのプロデルから値を入力することにします。
- お届け先: C6
- 商品コード, 商品名, 単価, 数量, 金額: B10～F15
- 商品金額合計: F17
- 送料: F18
- 消費税: F19
- 合計金額: F21
帳票シートを開く
プロデルで作成したブックを開くには、「開く」手順を使います。この手順の戻り値は、「ワークブック」種類のオブジェクトなので「現在シート」設定項目からさらに「ワークシート」種類のオブジェクトを取得することで、帳票があるシートを操作できます。
エクセルを起動する
帳票ブックは、エクセルで「帳票.xlsx」を開いたもの
帳票シートは、帳票ブックの現在シートデータを設定する
注文した商品は、「データ」配列に入れておきます。配列の要素にはさらに辞書を入れ、商品コード,商品名,価格をそれぞれ入れておきます。
データは｛
｛商品コード=「A001」,商品名=「キーホルダー」,単価=600,数量=2｝,
｛商品コード=「A003」,商品名=「Tシャツ」,単価=3500,数量=1｝,
｛商品コード=「A005」,商品名=「ハンドタオル」,単価=1200,数量=1｝,
｝Excelシートへ転記するときには、繰り返し文を使って、配列と辞書の内容を取り出していきます。この時にB10のセルから順番に値を設定していきます。
行番号は、10
データのすべての要素についてそれぞれ繰り返す
帳票シートのセル（「B」&行番号）の内容を要素の「商品コード」に変える
帳票シートのセル（「C」&行番号）の内容を要素の「商品名」に変える
帳票シートのセル（「D」&行番号）の内容を要素の「単価」に変える
帳票シートのセル（「E」&行番号）の内容を要素の「数量」に変える
行番号を増やす
そして
計算する
注文した商品の合計金額を計算して、送料と消費税も合わせた合計を計算します。商品の合計金額の計算は、繰り返し文の中で「総計」変数に計算して入れておきます。
データのすべての要素についてそれぞれ繰り返す
//・・・
金額は、要素の「単価」*要素の「数量」
帳票シートのセル（「F」&行番号）の内容を金額に変える
//・・・
繰り返し終わり完成したプログラム
データは｛
｛商品コード=「A001」,商品名=「キーホルダー」,単価=600,数量=2｝,
｛商品コード=「A003」,商品名=「Tシャツ」,単価=3500,数量=1｝,
｛商品コード=「A005」,商品名=「ハンドタオル」,単価=1200,数量=1｝,
｝
エクセルを起動する
帳票ブックは、エクセルで「帳票.xlsx」を開いたもの
帳票シートは、帳票ブックの現在シート
帳票シートのセル（「C6」）の内容を「中野 様」に変える
行番号は、10
帳票シートのセル（「B[行番号]:F[行番号+5]」）から値をクリアする
総計は、0
送料は、600
データのすべての要素についてそれぞれ繰り返す
帳票シートのセル（「B」&行番号）の内容を要素の「商品コード」に変える
帳票シートのセル（「C」&行番号）の内容を要素の「商品名」に変える
帳票シートのセル（「D」&行番号）の内容を要素の「単価」に変える
帳票シートのセル（「E」&行番号）の内容を要素の「数量」に変える
金額は、要素の「単価」*要素の「数量」
帳票シートのセル（「F」&行番号）の内容を金額に変える
総計に金額を足す
行番号を増やす
そして
帳票シートのセル（「F17」）の内容を総計に変える //商品金額合計
帳票シートのセル（「F18」）の内容を送料に変える //消費税
帳票シートのセル（「F19」）の内容を総計*0.1に変える //送料
帳票シートのセル（「F21」）の内容を総計*1.1+送料に変える //合計金額印刷する
印刷するには、「印刷する」手順を使います。Excelの印刷機能が呼び出されるので、設定を確認して印刷します。
帳票シートを印刷するヒント：VBAと同じプロパティを使う
プロデル1.7.1070よりExcel関連の種類に「元実体」設定項目が増えました。この設定項目からVBAと同じプロパティにアクセスできます。例えば印刷先のプリンタを指定するには、次のように命令します。
エクセルの元実体のActivePrinterは「Microsoft Print to PDF on Ne01:」おまけ：CSVファイルをもとに印刷する
ここまでのプログラムでは、注文データをプログラム上で書いていますが、前回紹介したようにデータをCSVファイルから読み込むということもできます。
プログラムを改良してCSVファイルにあるデータをもとに印刷するようにしてみました。
//データの読み込み
データ表を作って伝票データとする
伝票データの見出し行は、〇
伝票データへ「注文伝票.csv」から読み込む
データ表を作って明細データとする
明細データの見出し行は、〇
明細データへ「注文明細.csv」から読み込む
//Excelの操作
エクセルを起動する
帳票ブックは、エクセルで「納品書.xlsx」を開いたもの
帳票シートは、帳票ブックの現在シート
伝票データの行一覧のすべての伝票についてそれぞれ繰り返す
帳票シートのセル（「C6」）の内容を「[伝票から「名前」を取得したもの] 様」に変える
行番号は、10
帳票シートのセル（「B[行番号]:F[行番号+5]」）から値をクリアする
総計は、0
送料は、伝票から「送料」を取得したもの
注文番号は、伝票から「注文番号」を取得したもの
明細データの行一覧のすべての明細についてそれぞれ繰り返す
注文番号が(明細から「注文番号」を取得したもの)でないなら繰り返しを続ける
帳票シートのセル（「B」&行番号）の内容を（明細から「商品コード」を取得したもの）に変える
帳票シートのセル（「C」&行番号）の内容を（明細から「商品名」を取得したもの）に変える
帳票シートのセル（「D」&行番号）の内容を（明細から「単価」を取得したもの）に変える
帳票シートのセル（「E」&行番号）の内容を（明細から「数量」を取得したもの）に変える
金額は、（明細から「単価」を取得したもの）*（明細から「数量」を取得したもの）
帳票シートのセル（「F」&行番号）の内容を金額に変える
総計に金額を足す
行番号を増やす
そして
帳票シートのセル（「F17」）の内容を総計に変える //商品金額合計
帳票シートのセル（「F18」）の内容を送料に変える //消費税
帳票シートのセル（「F19」）の内容を総計*0.1に変える //送料
帳票シートのセル（「F21」）の内容を総計*1.1+送料に変える //合計金額
帳票シートを印刷する
そして
CSVファイルを作る
CSVファイルは、次のようなプログラムで作成しました。見出しが合っていればCSVファイルをExcelなどで作成してもOKです。
CSVファイルは、「注文伝票.csv」と「注文明細.csv」の2つのファイルに分けられています。注文伝票.csvには、注文ごとに1行ずつ登録します。注文明細.csvは、注文した商品を注文番号別に登録します。伝票と明細で注文番号が同じデータが1回の帳票で印刷されます。
伝票データというデータ表を作る
伝票データの見出し行は、〇
伝票データに{「注文番号」,「名前」,「住所」,「送料」}という列を加える
伝票データへ｛注文番号=1,名前=「中野」,住所=「神奈川県横浜市」,送料=600｝を加える
伝票データへ｛注文番号=2,名前=「田中」,住所=「千葉県千葉市」,送料=600｝を加える
伝票データを「注文伝票.csv」へ保存する
明細データというデータ表を作る
明細データの見出し行は、〇
明細データに{「注文番号」,「商品コード」,「商品名」,「単価」,「数量」}という列を加える
明細データへ｛注文番号=1,商品コード=「A001」,商品名=「キーホルダー」,単価=600,数量=2｝を加える
明細データへ｛注文番号=1,商品コード=「A003」,商品名=「Tシャツ」,単価=3500,数量=1｝を加える
明細データへ｛注文番号=1,商品コード=「A005」,商品名=「ハンドタオル」,単価=1200,数量=1｝を加える
明細データへ｛注文番号=2,商品コード=「A001」,商品名=「キーホルダー」,単価=600,数量=3｝を加える
明細データを「注文明細.csv」へ保存するまとめ
今回は、プロデルからExcelを操作して、手作業では大変なシートの作成作業を自動化する方法を紹介しました。その過程でカレンダーやスケジュール帳、帳票を印刷するプログラムを作りました。プロデルとExcelを組み合わせれば、これまで面倒だった書類の作成作業が便利になるプログラムが作れます。この後はプログラムを自分にとって便利なプログラムに改良して作ってみましょう。
※当ブログの記事の著作権はゆうとにあります。プロデルに関係が無い目的で、文章や図表,プログラムを複製, 改変, 移植して掲載することを堅く禁止します
