エクセルベースの帳票出力機能を自動生成するツール

すっかり月一ペースのエントリになってしまってますが、そろそろ９月も終わりなので実務も兼ねたネタを１つ… 今やってる業務は、２ヶ月程前に引き継いで担当になってしまった新システム（Webアプリ）の設計で、外注分の帳票出力機能の設計をやっているところです。今のところ以下のような前提条件があります。

エクセルで出力
IE 内の VBScript（ActiveX）で帳票生成処理を完結させる（社内標準が Offece2000 から Offece2010 になろうとしてる関係もあり･･･）
１つのブックにいろいろなパターンのシートを含める（動的に生成するグラフやオートシェイプを含んだシートとか）
年に１、２回の確率で帳票レイアウトが変更されていく可能性が高い

VBScript からグラフやオートシェイプを直接いじるのは結構面倒なのですが、エクセル内で定義した特定のマクロを引数付きで起動することもできるので、いざとなったらこれを併用すれば何とかなりそうです。

レイアウト変更については、保守を続けていかなければならない立場としては非常に嫌です。エクセルベースの帳票出力の案件は C/S 時代にもよくありましたが、当時はレイアウト変更の問題を解決するために、座標データ（どのデータをどのセルに貼るかという情報）を XML 形式で外だしにして、汎用処理でデータを貼り付けるという実装を、自動生成するようにしていました。
今回も、この自動生成ツールが使えればレイアウト変更も怖くないという事で、Web アプリでも使用できるようにカスタマイズしたのでご紹介します。（IE + ActiveX の使用が前提になるのであまり需要は無いとは思いますが…）

機能概要

帳票レイアウトとデータ貼り付け箇所を定義したエクセルを参照し、座標 XML と帳票生成 VBScript を自動生成します。帳票生成 VBScript はレイアウトエクセル、座標 XML、データ XML を読み込みエクセルベースの帳票を生成します。

生成したプログラムは IE 限定ですが Web アプリ上でも使用することができます。

使い方

まず、エクセルで出力帳票のサンプルを作ります。必要に応じ計算式や書式設定をセルに割り当てておきます。ファイル名は、下記例だと見積書の帳票なので mitsumori_sample.xls といった感じで保存しておきます。シート名も同様に帳票の内容を示す”見積書”とします。
１つのブックに複数種類の帳票を収めたい場合は、同じブックにその数分レイアウトシートを定義しておきます。

次に、これを２つ分コピーして、実際にシステムで使用されるレイアウト用エクセルと座標データ生成用エクセルを作ります。レイアウト用エクセルは、単純にデータ部のサンプル値を消して mitsumori.xls という名前で保存します。

座標データ生成用のエクセルは、データ部に { } で囲った識別子を記述し mitsumori_map.xls という名前で保存します。例えば納品期日なら {delivery_date} のようにして記述します。

ツールをダウンロードし解凍すると reportGenerator.wsf というファイルがあるので、これに対し mitsumori_map.xls をドロップします。生成処理開始のメッセージの後、生成処理が始まります。

\report-generator\reportGenerator.wsf

生成処理が終わると result_mitsumori というフォルダができ、その配下に下記ファイルとフォルダが生成されてます。

mitsumori.vbs … データ貼り付け処理
mitsumori.xls … レイアウトエクセル（上記で作成したファイルのコピー）
mitsumori_map.xml … 座標データ
html_sample … HTML 経由で帳票生成するデモのフォルダ
bat_sample … BAT 経由で帳票生成するデモのフォルダ

座標の抽出範囲を指定する

lib フォルダにある init.vbs を開くと下記記述があります。SEARCH_MAP_X、SEARCH_MAP_Y の指定で座標抽出の対象となるセルの範囲を指定してるので、使用してるセル数が多い場合はこの値を書き換えます。

Const SEARCH_MAP_X = 100
Const SEARCH_MAP_Y = 100
Const MAP_EXT = "_map"
Const DAT_EXT = "_dat"
Const GEN_FILE_NAME = "generateReport.vbs"
Const JQUERY_NAME = "jquery-1.4.2.min.js"

\report-generator\lib\init.vbs

使用例

html_sample フォルダ内の mitsumori.html を実行すると以下画面が表示されます。

Generate Report ボタンをクリックすると以下エクセルが生成されます。

\report-generator\sample_app\ex01\mitsumori.html

データが値0、値1、値2…となってるのは、同フォルダにあるデモ用のデータ mitsumori_dat.xml が以下のように定義されているためです。

それらしい値で上書きし再度出力してみます。

\report-generator\sample_app\ex02\mitsumori.html

データ XML の設定

データ XML より帳票出力処理の簡単な制御ができます。複雑な処理（例えばデータ貼り付け後、動的に変わるポジションにオートシェイプを貼るなど）が必要な場合は、帳票生成 VBScript に処理を追加するか、レイアウトエクセルにマクロを記述し、VBScript から起動するようにして対応します。

１つのレイアウトシートから複数の帳票シートを生成する

例えば、見積No.M00001 と M00002 の帳票を生成し、１つのブックに収めたい場合は以下のように記述します。sheet 要素の name 属性は参照するレイアウトシートの名前を指し、newName 属性にはリネームしたいシート名を指定する事ができます。

<?xml version="1.0" encoding="Shift_JIS"?>
<dat>
<page>
<sheet name="見積書" newName="M00001">
	<No>M00001</No>
	…省略
</sheet>
<sheet name="見積書" newName="M00002">
	<No>M00002</No>
	…省略
</sheet>
</page>
</dat>

\report-generator\sample_app\ex03\mitsumori.html

１つのブックに複数のレイアウトシートを生成する

例えば、見積書シートと資料シートを１つのブックに収めたい場合は、レイアウト用エクセルに両シートのレイアウトを事前に定義しておき、データ XML を以下の様に記述します。

<?xml version="1.0" encoding="Shift_JIS"?>
<dat>
<page>
<sheet name="見積書">
	<No>M00001</No>
	…省略
</sheet>
<sheet name="資料"/>
</page>
</dat>

\report-generator\sample_app\ex04\mitsumori.html

上記の資料シートの様に、貼り付けるデータを保持しない場合は、sheet 要素のみを記述します。

データの貼り付け箇所を相対位置で指定する

見積書の品名や単価のような繰り返し複数行を出力するようなデータは、基準位置（例えば1行目の各項目の位置）のみを定義し、その相対位置でデータ貼り付け箇所を指定することができます。

<products  >パソコン</products>
<unit_price>150000</unit_price>
<quantity  >10</quantity>

<products   addY="1">プリンタ</products>
<unit_price addY="1">30000</unit_price>
<quantity   addY="1">30</quantity>

<products   addY="2">ディスプレイ</products>
<unit_price addY="2">40000</unit_price>
<quantity   addY="2">10</quantity>

\report-generator\sample_app\ex05\mitsumori.html

上記の例では、垂直方向の出力箇所を addY 属性で基準要素の相対位置で指定してます。addX 属性で水平方向の指定もできます。

Web アプリへの適用

イントラ環境限定になってしまうかと思いますが、Web アプリに適用する場合は以下の様にします。

mitsumori_dat.xml 相当のデータを出力するサーバサイドのロジックを実装
mitsumori.vbs、mitsumori.xls、mitsumori_map.xml を Web サーバ上に配置
帳票出力処理を起動する HTML を Web サーバ上に配置

HTML で mitsumori.vbs を読み込むと帳票生成関数である generateReport 関数が、下記書式で使用できるようになります。

gernerateReport( layout.xls のフルパス , map.xml のパス , dat.xml のパス )

layout.xls のみ http:// から始まるフルパスで指定する必要があります。リテラルで記述するとテスト環境から本番環境へ移行する際に書き換えが必要になるので、下記のようにパスを求めると良いかと思います。

//http://hostname/app/view/mitsumori.html から
//http://hostname/app/xls/mitsumori.xls を求める場合

//location オブジェクトから求める方法
location.protocol + "//" + location.host + "/app/xls/layout.xls"

//HTML の相対パスで求める方法
var basePath = function (url){
	return url.substring(0,url.lastIndexOf('/'));
}
basePath(basePath(location.href)) + "/xls/layout.xls"

使用例

<html>
<head>
	<script src='mitsumori.vbs' language='VBScript'></script>
	<script language='JavaScript' src="jquery.js"></script>
	<script language='JavaScript'>
	jQuery(function($){
		$('#print').click(function(){
			generateReport(
				location.protocol + "//" + location.host + "/app/xls/layout.xls",
				'../dat/mitsumori_map.xml',
				'../dat/mitsumori.jsp?no='+$('#mitumoriNo').val()
			);
		});
	});
	</script>
</head>
<body>
	見積No<input id="mitumoriNo"/><button id="#print">見積書出力</button>
</body>

ダウンロード

こちらからどうぞ