I have prepared a scrollable HTML table implementation for a prospect, and it seems it is handy. So I would like to share this idea with you all. If you are interested, try it out!
Attached is documentation. (Sorry but it is Japanese edition.)
Features are:
- Fuzzy search is applicable
- Sortable
- Copy the whole table (or individual cell) to clipboard
- Scroll all the records, or display records in separate pages
Add the following to USERENV:
Code: Select all
simpleScrollTable: function( p1 ) {
var r = USERENV.simpleScrollTableV8.create( p1 );
return r;
},
simpleScrollTableV8:
{
startRecordAtPage: 1,
totalPages: 1,
recordsPerPage: 10,
originalTable : [],
tableDataToCopy: "",
copyRecordToClipboard: function( v ){
var textarea = document.createElement("textarea");
textarea.setAttribute("id", "textareaForClipboard");
textarea.setAttribute("style", "position:absolute;top:-10px;left:0px;width:1px;height:1px;z-index:-9999;");
document.getElementById('terminalContent').appendChild(textarea);
var button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;margin:0px;");
button.setAttribute(
"onClick",
"var c = document.getElementById('textareaForClipboard');\
c.innerHTML='"+v+"';c.select();document.execCommand('copy');"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;cursor:pointer;transform:scale(0.6);vertical-align:middle;");
itag.innerHTML = "content_copy";
button.appendChild(itag);
return button;
},
copyTableToClipboard: function( p ){
var textarea = document.createElement("textarea");
textarea.setAttribute("id", "textareaForClipboardAll");
textarea.setAttribute("style", "position:absolute;top:-10px;left:100px;width:100px;height:1px;z-index:-9999;");
document.getElementById('terminalContent').appendChild(textarea);
var button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;margin:0px;");
button.setAttribute(
"onClick",
"var r;\
var ret = '';\
var q = AXES.TableManager.getTable('"+p.tableName+"');\
var col="+JSON.stringify(p.columns)+";\
for ( var j = 0; j < col.length; j ++ ){\
ret += col[j].title;\
ret += '\\t';\
}\
ret+='\\n';\
for ( var i = 0; i < q.childCount(); i ++ ){\
for ( var j = 0; j < col.length; j ++ ){\
ret += q.child(i)[col[j].resultColumnName];\
if ( j===(col.length-1) ) ret += '\\n';\
else ret += '\\t';\
}\
}\
var c=document.getElementById('textareaForClipboardAll');\
c.innerHTML=ret;c.select();document.execCommand('copy');"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;cursor:pointer;transform:scale(1.0);vertical-align:middle;");
itag.innerHTML = "copy_all";
button.appendChild(itag);
return button;
},
appendElement: function ( elm, v ){
// tagName
var append = document.createElement(elm.tagName);
if ( elm.tagName ){
append = document.createElement(elm.tagName);
}else{
append = document.createElement("span");
}
// style
if ( elm.cssText ){
append.setAttribute( "style", elm.cssText );
}
// innerHTML
if ( elm.innerHTML ){
if ( elm.innerHTML === "_v_" ){
append.innerHTML = v;
}else{
append.innerHTML = elm.innerHTML;
}
}
// tagName, style, innerHTML 以外
for ( var el in elm ) {
if ( el !== "tagName" &&
el !== "style" &&
el !== "innerHTML" ){
if ( elm[el] === "_v_" ){
append.setAttribute( el, v );
}else{
append.setAttribute( el, elm[el] );
}
}
}
return append;
},
create: function( p ){
// テーブルロード
var sqlVariables = (p.sqlVariables)?p.sqlVariables:{};
sqlVariables.SQLVariable_SEARCH_ = "%";
AXES.TableManager.loadDynamicTable( p.tableName, "tables_dynamic.txt", sqlVariables, false );
var q = AXES.TableManager.getTable(p.tableName);
// パラメータ
var sortable = ( p.sortable ) ? true : false;
var searchable = ( p.searchable ) ? true : false;
if ( p.idSuffix === undefined ) p.idSuffix = "";
if ( p.tableBodyHeight === undefined ) p.tableBodyHeight = "20em";
if ( p.maxRecordsPerPage === undefined ) p.maxRecordsPerPage = 10;
if ( p.placeholderText === undefined ) p.placeholderText = "あいまい検索";
if ( p.totalRecordNumberText === undefined ) p.totalRecordNumberText = "件";
if ( p.pageText === undefined ) p.pageText = "ページ";
if ( p.recordsPerPageText === undefined ) p.recordsPerPageText = "行/ページ";
//最大ページ数 (切り上げ)
var maxPage = Math.ceil( q.childCount() / p.maxRecordsPerPage );
// DIV
var divContainer = document.createElement('div');
divContainer.setAttribute("id","simpleScrollTableContainer"+p.idSuffix);
divContainer.setAttribute("style","width:100%;height:100%;");
var input;
var stringToCopy = "";
// INPUT (検索用)
if ( searchable ){
input = document.createElement('input');
input.setAttribute("placeholder", p.placeholderText );
input.setAttribute("oninput",
"USERENV.simpleScrollTableV8.searchOnChange(this, "+JSON.stringify(p)+")"
);
input.setAttribute("onfocus", "document.activeElement.select()");
divContainer.appendChild(input);
}
var span;
if ( p.showTotalRecordNumber ){
span = document.createElement("span");
span.setAttribute("id","totalRecordNumber"+p.idSuffix);
span.setAttribute("style","margin-left:4px;margin-right:4px;")
span.innerHTML = q.childCount().toLocaleString() + p.totalRecordNumberText;
divContainer.appendChild(span);
}
var itag;
var button;
// INPUT (ページ番号)
if ( p.showRecordsPerPage ){
//ページ「戻る」ボタン
button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;padding:0px;");
button.setAttribute(
"onClick",
"USERENV.simpleScrollTableV8.refresh(1, "+JSON.stringify(p)+")"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;");
itag.innerHTML = "skip_previous";
button.appendChild(itag);
divContainer.appendChild(button);
//ページ「戻る」ボタン
button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;padding:0px;");
button.setAttribute(
"onClick",
"e=document.getElementById('startRecordAtPage"+p.idSuffix+"');if(e.value>1){e.value=e.value-1;\
USERENV.simpleScrollTableV8.refresh(e.value, "+JSON.stringify(p)+")}"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;");
itag.innerHTML = "navigate_before";
button.appendChild(itag);
divContainer.appendChild(button);
//開始ページ番号
input = document.createElement("input");
input.setAttribute("id", "startRecordAtPage"+p.idSuffix);
input.setAttribute("placeholder", "1");
input.setAttribute("value", USERENV.simpleScrollTableV8.startRecordAtPage);
input.setAttribute("size", "4");
input.setAttribute("style", "text-align:right;");
input.setAttribute("oninput",
"USERENV.simpleScrollTableV8.refresh(this.value, "+JSON.stringify(p)+")"
);
input.setAttribute("onfocus", "document.activeElement.select()");
divContainer.appendChild(input);
//ページ「進む」ボタン
button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;margin:0px;");
button.setAttribute(
"onClick",
"e=document.getElementById('startRecordAtPage"+p.idSuffix+"');\
if(e.value<Number(document.getElementById('maxPage"+p.idSuffix+"').innerHTML)){e.value++;\
USERENV.simpleScrollTableV8.refresh(e.value, "+JSON.stringify(p)+")}"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;");
itag.innerHTML = "navigate_next";
button.appendChild(itag);
divContainer.appendChild(button);
//ページ「進む」ボタン
button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:24px;margin:0px;");
button.setAttribute(
"onClick",
"USERENV.simpleScrollTableV8.refresh(document.getElementById('maxPage"+p.idSuffix+"').innerHTML, "+JSON.stringify(p)+")"
);
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;");
itag.innerHTML = "skip_next";
button.appendChild(itag);
divContainer.appendChild(button);
span = document.createElement("span");
span.innerHTML = "/";
divContainer.appendChild(span);
span = document.createElement("span");
span.setAttribute("id","maxPage"+p.idSuffix);
span.innerHTML = maxPage;
divContainer.appendChild(span);
span = document.createElement("span");
span.innerHTML = p.pageText + " (";
divContainer.appendChild(span);
if ( p.maxRecordsPerPageArray !== undefined ){
var select = document.createElement("select");
select.setAttribute("id","maxRecordsPerPage"+p.idSuffix);
select.setAttribute(
"onchange",
"document.getElementById('maxPage"+p.idSuffix+"').innerHTML=Math.ceil(" + q.childCount()+ "/this.value);\
USERENV.simpleScrollTableV8.refresh(1, "+ JSON.stringify(p) +");"
);
var option;
for ( var i = 0; i < p.maxRecordsPerPageArray.length; i ++ ){
//console.log(i);
option = document.createElement("option");
option.setAttribute("value",p.maxRecordsPerPageArray[i]);
if ( p.maxRecordsPerPageArray[i] === p.maxRecordsPerPage ){
option.setAttribute("selected", true );
}
option.innerHTML = p.maxRecordsPerPageArray[i];
select.appendChild(option);
}
divContainer.appendChild(select);
span = document.createElement("span");
span.setAttribute("id","maxRecordsPerPage"+p.idSuffix);
span.setAttribute("style","margin-left:4px;margin-right:4px;");
divContainer.appendChild(span);
span = document.createElement("span");
span.innerHTML = p.recordsPerPageText +")";
divContainer.appendChild(span);
}else{
span = document.createElement("span");
span.setAttribute("id","maxRecordsPerPage"+p.idSuffix);
span.setAttribute("style","margin-left:4px;margin-right:4px;");
span.innerHTML = p.maxRecordsPerPage;
divContainer.appendChild(span);
span = document.createElement("span");
span.innerHTML = p.recordsPerPageText +")";
divContainer.appendChild(span);
}
}
// TABLE
var table = document.createElement('table');
table.setAttribute("id","simpleScrollTable"+p.idSuffix);
table.setAttribute("class","scroll");
table.setAttribute("style","border-collapse:collapse;border-spacing:0;border-radius:1px;font-family:Meiryo UI;");
var thead = document.createElement('thead');
thead.setAttribute("class","ui-widget-header");
thead.setAttribute("style","display:block;background:#fff !important;border:none !important;");
var tr = document.createElement('tr');
tr.setAttribute("class","md-sftable-header--full");
var th;
var button;
var itag;
for ( var i = 0; i < p.columns.length; i ++ ){
th = document.createElement('td');
th.style.cssText += p.columns[i].cssText;
th.innerHTML = ( p.columns[i].title ) ? p.columns[i].title : "" ;
if ( sortable ){
button = document.createElement("button");
button.setAttribute("class", "ax-terminal-button mdc-button mdc-button--dense mdc-ripple-upgraded");
button.setAttribute("style","height:14px;width:18px;padding:0px;");
button.setAttribute( "onclick", "USERENV.simpleScrollTableV8.sortOnClick(this.parentNode,"+JSON.stringify(p)+")" );
itag = document.createElement("i");
itag.setAttribute("class", "material-icons mdc-button__icon");
itag.setAttribute("aria-hidden","true");
itag.setAttribute("style","margin:0px;");
itag.innerHTML = "sort";
button.appendChild(itag);
th.appendChild(button);
}
tr.appendChild(th);
}
thead.appendChild(tr);
table.appendChild(thead);
var tbody = document.createElement('tbody');
tbody.setAttribute("id","searchTBody"+p.idSuffix);
tbody.setAttribute("style","display:block;height:"+p.tableBodyHeight+";overflow-y:scroll;overflow-x:hidden;-webkit-overflow-scrolling:touch;");
var td;
stringToCopy = "";
for (var j = 0; j < q.childCount(); j ++) {
var r = q.child(j);
tr = document.createElement("tr");
if ( p.highlightRows ){
tr.setAttribute("class", "md-sftable-row md-sftable-highlight");
if ( p.alternateColors ){
if ( j % 2 === 0 ){
tr.setAttribute("class", "md-sftable-row md-sftable-highlight md-sftable-highlight-color");
}
}
}
for ( var k = 0; k < p.columns.length; k ++ ){
td = document.createElement('td');
td.style.cssText += p.columns[k].cssText;
//
// appendオプション (エレメントの追加)
//
if ( p.columns[k].append ){
td.appendChild(
USERENV.simpleScrollTableV8.appendElement(
p.columns[k].append,
r[p.columns[k].resultColumnName] )
);
}else{
if ( p.columns[k].replaceFrom !== null && p.columns[k].replaceFrom !== undefined &&
p.columns[k].replaceTo !== null && p.columns[k].replaceTo !== undefined ){
var v = r[p.columns[k].resultColumnName];
v = v.replace(new RegExp(p.columns[k].replaceFrom,'g'), p.columns[k].replaceTo);
td.innerHTML = v;
}else{
td.innerHTML = r[p.columns[k].resultColumnName];
}
}
//個別セルをコピー
if ( p.columns[k].copyToClipboard ){
//
// Clipboard APIはHTTPSのみサポートのため、HTTPのときはexecCommand()を代替的に使用。
// ただしこのメソッドは非推奨となっている。(下記リンク参照)
// https://developer.mozilla.org/en-US/docs/Web/API/Document/execCommand
// 将来的に使用できなくなるリスクがあります。
//
td.appendChild( USERENV.simpleScrollTableV8.copyRecordToClipboard( r[p.columns[k].resultColumnName] ) );
}
tr.appendChild(td);
}
tbody.appendChild(tr);
if( p.showRecordsPerPage ){
if ( j >= (p.maxRecordsPerPage - 1) ){
break;
}
}
}
if ( p.copyToClipboard ){
divContainer.appendChild( USERENV.simpleScrollTableV8.copyTableToClipboard( p ) );
}
table.appendChild(tbody);
divContainer.appendChild(table);
//オリジナルデータ一式をUSERENVに保持
let orgTable = tbody.cloneNode(true);
USERENV.simpleScrollTableV8.originalTable = orgTable;
return divContainer.outerHTML;
},
refresh : function(s, p ) {
//開始ページの最小値は1
if ( s < 1 ){
s = 1;
}
if ( p.showRecordsPerPage ){
document.getElementById("startRecordAtPage"+p.idSuffix).value = s;
}
var oldTbody = document.getElementById("searchTBody"+p.idSuffix);
if ( oldTbody !== null ){
oldTbody.parentNode.removeChild(oldTbody);
}
var tbody = document.createElement('tbody');
tbody.setAttribute("id","searchTBody"+p.idSuffix);
tbody.setAttribute("style","display:block;height:"+p.tableBodyHeight+";overflow-y:scroll;overflow-x:hidden;-webkit-overflow-scrolling:touch;");
var tr;
var td;
// maxRecordsPerPageCulc
// 1ページあたりのレコード数を算出して、以降のロジックで使用
// p.paxRecordsPerPageArray (配列) が指定されているときは<input>のためvalue属性で参照
// 上記以外の場合は<span>のためinnerHTML属性で参照する
var maxRecordsPerPageCulc;
if ( p.showRecordsPerPage ){
if ( p.maxRecordsPerPageArray !== undefined ){
maxRecordsPerPageCulc = document.getElementById("maxRecordsPerPage"+p.idSuffix).value;
}else{
maxRecordsPerPageCulc = document.getElementById("maxRecordsPerPage"+p.idSuffix).innerHTML;
}
}else{
maxRecordsPerPageCulc = p.maxRecordsPerPage;
}
var startRecordNumber = Number( ( s - 1 ) * maxRecordsPerPageCulc );
var q = AXES.TableManager.getTable(p.tableName);
var endRecordNumber = ( p.showRecordsPerPage ) ?
Number( startRecordNumber ) + Number( maxRecordsPerPageCulc ) :
q.childCount();
for ( var i = startRecordNumber; i < q.childCount(); i++ ) {
if ( i < endRecordNumber ){
tr = document.createElement("tr");
for ( var j = 0; j < p.columns.length; j ++ ){
td = document.createElement('td');
td.style.cssText += p.columns[j].cssText;
//
// appendオプション (エレメントの追加)
//
if ( p.columns[j].append ){
td.appendChild(
USERENV.simpleScrollTableV8.appendElement(
p.columns[j].append,
q.child(i)[p.columns[j].resultColumnName]
)
);
}else{
tr.appendChild(td);
tr.appendChild(td);
td.innerHTML = q.child(i)[p.columns[j].resultColumnName];
}
if ( p.columns[j].copyToClipboard ){
//
// Clipboard APIはHTTPSのみサポートのため、HTTPのときはexecCommand()を代替的に使用。
// ただしこのメソッドは非推奨となっている。(下記リンク参照)
// https://developer.mozilla.org/en-US/docs/Web/API/Document/execCommand
// 将来的に使用できなくなるリスクがあります。
//
td.appendChild( USERENV.simpleScrollTableV8.copyRecordToClipboard( q.child(i)[p.columns[j].resultColumnName] ) );
}
tr.appendChild(td);
}
if ( p.highlightRows ){
tr.setAttribute("class", "md-sftable-row md-sftable-highlight");
if ( p.alternateColors ){
if ( i % 2 === 0 ){
tr.setAttribute("class", "md-sftable-row md-sftable-highlight md-sftable-highlight-color");
}
}
}
tbody.appendChild(tr);
}else{
break;
}
}
document.getElementById("simpleScrollTable"+p.idSuffix).appendChild(tbody);
},
searchOnChange : function(obj, p ) {
var oldTbody = document.getElementById("searchTBody"+p.idSuffix);
if ( oldTbody !== null ){
oldTbody.parentNode.removeChild(oldTbody);
}
var tbody = document.createElement('tbody');
tbody.setAttribute("id","searchTBody"+p.idSuffix);
tbody.setAttribute("style","display:block;height:"+p.tableBodyHeight+";overflow-y:scroll;overflow-x:hidden;-webkit-overflow-scrolling:touch;");
var cnt = 0;
var tr;
var td;
var s;
// テーブルロード
var sqlVariables = (p.sqlVariables)?p.sqlVariables:{};
sqlVariables.SQLVariable_SEARCH_ = "%" + obj.value.trim() + "%";
AXES.TableManager.loadDynamicTable( p.tableName, "tables_dynamic.txt", sqlVariables, false );
var q = AXES.TableManager.getTable(p.tableName);
if ( p.showTotalRecordNumber ){
document.getElementById("totalRecordNumber"+p.idSuffix).innerHTML = q.childCount().toLocaleString() + p.totalRecordNumberText;
}
if ( p.showRecordsPerPage ){
document.getElementById("startRecordAtPage"+p.idSuffix).value = 1;
document.getElementById("maxPage"+p.idSuffix).innerHTML = Math.ceil( q.childCount() / p.maxRecordsPerPage );
}
USERENV.simpleScrollTableV8.refresh( 1, p );
},
sort : {
column_no : 0, //今回クリックされた列番号
column_no_prev : 0, //前回クリックされた列番号
},
sortOnClick : function(obj, p) {
if ( p.showRecordsPerPage ){
document.getElementById("startRecordAtPage"+p.idSuffix).value = 1;
}
var column_no = obj.cellIndex; //クリックされた列番号
// column_noを保持
USERENV.simpleScrollTableV8.sort.column_no = column_no;
// 保持していたcolumn_no_prev を取得
var column_no_prev = USERENV.simpleScrollTableV8.sort.column_no_prev;
var q = AXES.TableManager.getTable(p.tableName);
if (column_no_prev == column_no) { //同じ列が2回クリックされた場合は降順ソート
q._arrayChildren.sort( compareStringDesc );
} else {
q._arrayChildren.sort( compareString );
}
USERENV.simpleScrollTableV8.refresh(1, p );
//昇順/降順ソート切り替えのために列番号を保存
if ( column_no_prev === column_no ) {
USERENV.simpleScrollTableV8.sort.column_no_prev = -1; //降順ソート
} else {
USERENV.simpleScrollTableV8.sort.column_no_prev = column_no;
}
//数値ソート(昇順)
function compareNumber ( a, b ){
return a._oData.xhrdob - b._oData.xhrdob;
}
//数値ソート(降順)
function compareNumberDesc ( a, b ){
return b._oData.xhrdob - a._oData.xhrdob;
}
//文字列ソート(昇順)
function compareString ( a, b ){
if ( a._oData[p.columns[column_no].resultColumnName] < b._oData[p.columns[column_no].resultColumnName] ) {
return -1;
} else {
return 1;
}
return 0;
}
//文字列ソート(降順)
function compareStringDesc ( a, b ){
if ( a._oData[p.columns[column_no].resultColumnName] > b._oData[p.columns[column_no].resultColumnName] ) {
return -1;
} else {
return 1;
}
return 0;
}
}
},
Sample code in html property of Raw HTML extension:
Code: Select all
var prms = {
showRecordsPerPage: true,
totalRecordNumberText: "record(s)",
pageText: "page(s)",
recordsPerPageText: "record(s)/Page",
placeholderText: "Search String",
maxRecordsPerPage: 14,
showTotalRecordNumber: true,
searchable: true,
sortable : true,
tableName : "XHREMPTN",
tableBodyHeight: "19em",
highlightRows: true,
alternateColors: true,
copyToClipboard: true,
columns:[
{
resultColumnName: "xhrdepcde",
title: "DEPT",
cssText: "width:80px;"
},
{
resultColumnName: "xhrbuabrv",
title: "BUSUNIT",
cssText: "width:80px;"
},
{
resultColumnName: "xhrempid",
title: "EMPLOYEE",
cssText: "width:100px;",
copyToClipboard: true
},
{
resultColumnName: "xhrsurnme",
title: "SURNAME",
cssText: "width:120px;"
},
{
resultColumnName: "xhrgivnme",
title: "GIVEN NAME",
cssText: "width:120px;"
},
{
resultColumnName: "xhrdob",
title: "DATE OF BIRTH",
cssText: "width:140px;",
replaceFrom: "-",
replaceTo: "/"
}
]
}
ENV.returnValue = USERENV.simpleScrollTable( prms );
Dynamic table:
Code: Select all
-- ====================================================================================
-- XHREMPTN
-- ====================================================================================
DefineObjectInstance {
className = "DynamicTable",
name = "XHREMPTN",
source = "sql",
selectSQLcommand = [[
XHRDEPCDE,
XHRBUABRV,
XHREMPID,
XHRSURNME,
XHRGIVNME,
XHRDOB
from AXESDEMO.XHREMPTN
where XHRDEPCDE like :SQLVariable_SEARCH_ or
XHRBUABRV like :SQLVariable_SEARCH_ or
XHREMPID like :SQLVariable_SEARCH_ or
XHRSURNME like :SQLVariable_SEARCH_ or
XHRGIVNME like :SQLVariable_SEARCH_
]],
resultColumnNames = { "xhrdepcde", "xhrbuabrv", "xhrempid", "xhrsurnme", "xhrgivnme", "xhrdob" },
};
Hide from Fairdinkum