みなさん、こんにちは。今回は「SpreadJSで実現する帳票画面」シリーズの第二回目となる「テーブルバインディング編」をお送りいたします。
以前ご紹介した「セルバインディング編 」では、Spread.Sheetsを使用して作成した請求書雛形に、セルバインディング機能を使って顧客データを結合し、簡易的な「Excel帳票」を実現しました。
今回はこの帳票に明細テーブルを加えて、より請求書らしい内容にしていきたいと思います。完成予想図は以下の通りです。
上の画像にあるように、シート上に明細テーブルを追加します。また、明細にある「金額」は数式で自動計算するようにして、その合計は「合計金額」部分に表示するようにしたいと思います。
テーブルを追加する
はじめにシート上にテーブルを追加します。テーブルの基本設定を行う以下の 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;
var COL_NUM = 6;
sheet.setRowCount(ROW_NUM);
sheet.setColumnCount(COL_NUM);
} ;
以下はこのコードの実行結果です。雛形に明細となるテーブルが追加されました。テーブル列の設定やデータ連結を行っていないので、テーブルのデータはまだありません。
テーブルにデータ連結する
次に、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'の内容が表示されるようになりました。
次はこのテーブルに計算式とフッターを設定して、明細に「金額」と「合計」が表示されるようにしましょう。
テーブルフッターと数式の設定
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の印刷機能を使用し、この請求書を印刷したいと思います。どうぞお楽しみに！
開発者におすすめのフォーラム
7月10日(火)に弊社の開発支援ツール販売30周年記念フォーラム「Toolsの杜（ツールのもり）」を開催します。
今回の記事でご紹介したような弊社のJavaScript製品「SpreadJS」 「InputManJS」 「Wijmo(ウィジモ)」 のご紹介や、製品の活用事例や最新の技術動向についてセッションも行います。ぜひご参加ください。
