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

テーブルを追加する

はじめにシート上にテーブルを追加します。テーブルの基本設定を行う以下の 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);
// （途中のコード記載を省略しています）
};

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

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

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

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

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 }]};

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;
}

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

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

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;
}

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

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

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

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

合計金額の表示

これまでは結合データにある”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の印刷機能を使用し、この請求書を印刷したいと思います。どうぞお楽しみに！