SpreadJSで実現する帳票画面(2)テーブルバインディング編

みなさん、こんにちは。今回は「SpreadJSで実現する帳票画面」シリーズの第二回目となる「テーブルバインディング編」をお送りいたします。

以前ご紹介した「セルバインディング編」では、Spread.Sheetsを使用して作成した請求書雛形に、セルバインディング機能を使って顧客データを結合し、簡易的な「Excel帳票」を実現しました。

今回はこの帳票に明細テーブルを加えて、より請求書らしい内容にしていきたいと思います。完成予想図は以下の通りです。

f:id:GrapeCity_dev:20180610232629p:plain

上の画像にあるように、シート上に明細テーブルを追加します。また、明細にある「金額」は数式で自動計算するようにして、その合計は「合計金額」部分に表示するようにしたいと思います。

f:id:GrapeCity_dev:20180610234715p:plain

テーブルを追加する

はじめにシート上にテーブルを追加します。テーブルの基本設定を行う以下の setTable 関数を作成して、前回作成したコードにある sheet.setDataSource のコール前に呼び出すように設定します。

function setTable(sheet){
//販売リストのテーブルを作成します
var table = sheet.tables.add("salesTable", 13, 1, 1, 4);
//列の自動出力設定をオフにします
table.autoGenerateColumns(false);
return table;
}
window.onload = function(){
// (途中のコード記載を省略しています)
//雛形上にテーブルを設定します
var table = setTable(sheet);
sheet.setDataSource(source);
// (途中のコード記載を省略しています)
};

また、テーブルの追加にあわせて、setTemplate() で設定しているシート上の行数を少し増やしましょう。

function setTemplate(spread){
// (途中のコード記載を省略しています)
// 行列数を設定します
var ROW_NUM = 16; // 13から16に変更
var COL_NUM = 6;
sheet.setRowCount(ROW_NUM);
sheet.setColumnCount(COL_NUM);
// (途中のコード記載を省略しています)
};

以下はこのコードの実行結果です。雛形に明細となるテーブルが追加されました。テーブル列の設定やデータ連結を行っていないので、テーブルのデータはまだありません。

f:id:GrapeCity_dev:20180610234759p:plain

テーブルにデータ連結する

次に、Spread.Sheetsの「テーブルバインディング」機能を使用して、テーブルに明細データを連結します。

前回使用した「セルバインディング」は、JavaScriptの連想配列をデータソースとして、クライアントに表示したスプレッドシートの特定箇所に、データソースの特定データを紐づけて表示させることができる機能でしたが、「テーブルバインディング」は同様のコンセプトで指定したテーブルにデータを表示させることができる機能です。

データの作成

テーブルバインディングの利用に先駆けて、テーブルに設定する明細データを作成します。今回はテーブルに表示するデータを”sales”として、以下のように定義したいと思います。前回セルバインディングに使用したデータソース”data”を編集して、以下のようにします。

var data = { postNumber: "〒981-3205",
address: "宮城県仙台市泉区紫山3-1-4",
name: "グレープ太郎",
total: 128000,
sales: [ { item: 'OAモニタ', units:2, cost: 29800 },
{ item: 'SDDユニット', units:2, cost: 25000 },
{ item: '出張点検費', units:1, cost: 18400 }]};

テーブル列の設定とデータ結合

先ほど表示したテーブルに、列の情報(列の名称や列に設定する結合データのフィールド名)を設定して、データを結合できるようにします。前項で作成した setTable 関数に処理を追加して、以下のようにします。

function setTable(sheet){
//販売リストのテーブルを作成します
var table = sheet.tables.add("salesTable", 13, 1, 1, 4);
//列の自動出力設定をオフにします
table.autoGenerateColumns(false);
//テーブルの列情報設定を行います
var tableColumns = [];
var names = ['品名', '数量', '単価', '金額'];
var fields = ['item', 'units', 'cost', ''];
for(var i=0; i<names.length; i++){
var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn.name(names[i]); // 列の名称を設定します
tableColumn.dataField(fields[i]); // 結合するデータのフィールド名を設定します
tableColumns.push(tableColumn);
}
table.bindColumns(tableColumns);
return table;
}

セルバインディングの際と同様にテーブルにもバインディングパスを設定し、データとテーブルを関連付けます。以下のように setTable のコール後にテーブルの bindingPath を呼び出し、データ’sales’とのテーブルを関連付けを行います。

window.onload = function(){
// (途中のコード記載を省略しています)
//雛形上にテーブルを設定します
var table = setTable(sheet);
//テーブルにデータをバインドします
table.bindingPath('sales');
sheet.setDataSource(source);
// (途中のコード記載を省略しています)
};

以下はこのコードの実行結果です。テーブルにデータ’sales’の内容が表示されるようになりました。

f:id:GrapeCity_dev:20180610234851p:plain

次はこのテーブルに計算式とフッターを設定して、明細に「金額」と「合計」が表示されるようにしましょう。

テーブルフッターと数式の設定

Spread.Sheetsのテーブルには、Excelのテーブルと同様に列名を使用した数式(構造化参照数式)とテーブル内容の集計などを表示するフッターの設定が可能です。setTable 関数に処理を追加して、以下のようにします。

function setTable(sheet){
//販売リストのテーブルを作成します
var table = sheet.tables.add("salesTable", 13, 1, 1, 4);
//列の自動出力設定をオフにします
table.autoGenerateColumns(false);
//テーブルの列情報設定を行います
var tableColumns = [];
var names = ['品名', '数量', '単価', '金額'];
var fields = ['item', 'units', 'cost', ''];
for(var i=0; i<names.length; i++){
var tableColumn = new GC.Spread.Sheets.Tables.TableColumn();
tableColumn.name(names[i]); // 列の名称を設定します
tableColumn.dataField(fields[i]); // 結合するデータのフィールド名を設定します
tableColumns.push(tableColumn);
}
table.bindColumns(tableColumns);
//テーブルに数式を設定します
table.setColumnDataFormula(3, "=[@単価]*[@数量]");
//テーブルに合計金額表示用のフッターを設定します
table.showFooter(true);
table.setColumnValue(0, "合計");
table.setColumnFormula(3, "=SUBTOTAL(109,[金額])");
return table;
}

このコードを実行すると、以下のようになります。明細に「金額」と「合計」が表示されるようになりました。

f:id:GrapeCity_dev:20180610234938p:plain

さらに、「単価」と「金額」がある列に書式を設定して、明細らしくなるようにします。setTemplate 関数に以下のような処理を追加します。

function setTemplate(spread){
// (途中のコード記載を省略しています)
// テーブルの金額表示部分の書式を設定します
sheet.setFormatter(-1, 3, "¥#,##0");
sheet.setFormatter(-1, 4, "¥#,##0");
// (途中のコード記載を省略しています)
};

このようにすることで、テーブルの単価、金額部分に書式設定が適用され、以下のように¥マーク、3桁ごとのカンマ区切りで表示されるようになります。

f:id:GrapeCity_dev:20180610235011p:plain

合計金額の表示

これまでは結合データにある”total: 128000″を「合計金額」の部分に表示していましたが、テーブルで合計金額が計算できるようになったことにより、このデータを利用する必要はなくなりました。

今回は「合計金額」部分に”=E18″と設定することで、テーブルの合計部分に表示された金額を請求書の合計金額として表示することが可能です。ただ、このように直接セル参照を設定すると、テーブルに表示するデータ数が変わってしまった場合にフッター位置がずれてしまい、都度の再設定が必要になります。

再設定の必要なく、いつでもテーブルの合計が「合計金額」部分に表示されるように、テーブルのフッター位置を取得できる Spread.Sheets の関数 footerIndex と、別記事「名前ボックスの作成」でも使用した rangeToFormula 関数を使用して、以下のような処理を作成します。

function getTotalPosition(sheet, table){
//テーブルからフッターの行位置を取得し、シートから該当セルを取得します
var totalCellInTable = sheet.getCell(table.footerIndex(), 4);
//取得したセルの位置を数式で使用できる文字列に変換します
var refType = GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative;
var totalRefStr = GC.Spread.Sheets.CalcEngine.rangeToFormula(totalCellInTable, 0, 0, refType);
return totalRefStr;
}

テーブルへのデータ設定後に上記の関数を使い、合計金額の表示設定をします。

window.onload = function(){
// (途中のコード記載を省略しています)
//テーブルにデータをバインドします
table.bindingPath('sales');
sheet.setDataSource(source);
//テーブルに表示された合計値を"合計"部分に表示します
var totalCellPositionStr = getTotalPosition(sheet, table);
sheet.setFormula(11,4,"=" + totalCellPositionStr);
// (途中のコード記載を省略しています)
};

これで、テーブルに表示された合計が自動的に「合計金額」部分に表示されるようになりました。実際の動作とソースコードはこちらで確認可能です。

前回の状態からシートに明細表が加わり、グンと請求書らしい外観になってきました。さて、帳票というくらいですので、みなさん、これ、印刷したいですよね?

その動作、Spread.Sheetsなら可能です!

次回はSpread.Sheetsの印刷機能を使用し、この請求書を印刷したいと思います。どうぞお楽しみに!


開発者におすすめのフォーラム

7月10日(火)に弊社の開発支援ツール販売30周年記念フォーラム「Toolsの杜(ツールのもり)」を開催します。

今回の記事でご紹介したような弊社のJavaScript製品「SpreadJS」「InputManJS」「Wijmo(ウィジモ)」のご紹介や、製品の活用事例や最新の技術動向についてセッションも行います。ぜひご参加ください。