$(function (){ editable_table.Initalize(); }); var editable_table_val = {}; var editable_table = { Initalize: function () { editable_table.AddressSetting(); $('.editable-table').find('td').each(function(){ var $td = $(this); $td.click(function () { var $this = $(this); if ($this.hasClass("selected")) { $this.trigger("dblclick"); } else { editable_table.ClearSelectionCells(); $this.toggleClass("selected", true); } }); $td.dblclick(function () { var $this = $(this); if ($this.hasClass("editing")) { return } if ($this.attr("lock") != undefined) { editable_table.CellLock(this); return } var adrs = $this.attr("data-address"); var cell_val = editable_table_val[adrs].value; editable_table.ClearSelectionCells(); var input = $("").attr("id", "edit_target").attr("data-address", adrs).addClass("cell-edit").text(cell_val); $this.toggleClass("selected",true).addClass("editing"); $this.find('div').html("").append(input); $('.cell-address').fadeIn(1000); $("#edit_target").focus().blur(function (){ var adrs = $(this).attr("data-address"); var edit_val = $(this).val().toString(); editable_table_val[adrs]["value"] = edit_val; $(this).remove(); $('.cell-address').fadeOut(1000); $("td[data-address='" + adrs + "']").toggleClass("editing", false); editable_table.Recalculation(); }); }); }); $(document).keydown(function(e){ var cursor = function() { return ($("#edit_target").length) ? false : ($(".selected").length) ? true : false; } switch (e.key) { case "Esc": case "Escape": var adrs = $("#edit_target").attr("data-address"); if (adrs == undefined) { return } $("#edit_target").remove(); $("td[data-address='" + adrs + "']").toggleClass("editing", false); $('.cell-address').hide(); editable_table.Recalculation(); break; case "Left": case "ArrowLeft": if (cursor()) { editable_table.Offset(0,-1) } break; case "Up": case "ArrowUp": if (cursor()) { editable_table.Offset(-1,0) } break; case "Right": case "ArrowRight": if (cursor()) { editable_table.Offset(0,1) } break; case "Down": case "ArrowDown": if (cursor()) { editable_table.Offset(1,0) } break; } }); editable_table.OptionSheetJS(); editable_table.Recalculation(); }, BuildAddress: function (row, col) { var adrs = ""; var col_idx = ['A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z']; limit = col_idx.length; digit = col; do{ adrs = (adrs.length)?col_idx[digit%limit-1] + adrs: col_idx[digit % limit]; digit = Math.floor(digit / limit); }while (digit > 0); return adrs + row; }, AddressSetting: function () { $('.editable-table').find('td').each(function(){ var $td = $(this); var tr = $(this).parent(); var row = $('.editable-table').find('tr').index(tr); var col = $(tr).find('td').index(this); var adrs = editable_table.BuildAddress(row, col); $td.attr("data-address", adrs).css("position", "relative"); editable_table_val[adrs] = { value: $td.html(), row: row, col: col }; var $span = $("").addClass("cell-address").text(adrs).hide(); $td.html("").append($span).append($("
")); }); }, ClearSelectionCells: function () { $('.editable-table').find('td').each(function(){ $(this).toggleClass("selected", false); }); }, Offset: function (row, col) { var adrs = $('.selected').attr("data-address"); var cell = editable_table_val[adrs]; var nextAdrs = editable_table.BuildAddress(cell.row+row, cell.col+col); if (editable_table_val[nextAdrs]){ editable_table.ClearSelectionCells(); $("td[data-address='" + nextAdrs + "']").toggleClass("selected", true); } }, CellLock: function(cell) { var msg = "このセルは変更できません。"; var $tooltip = $("").addClass("tooltip").append($("").addClass("tooltip__body").text(msg)); $('body').append($tooltip); var $cell = $(cell); var cell_offset = $cell.offset(); var cell_size = { width: $cell.outerWidth(), height: $cell.outerHeight() }; var tip_size = { width: $tooltip.outerWidth(), height: $tooltip.outerHeight() }; $tooltip.css({ top: cell_offset.top - tip_size.height, left: cell_offset.left + cell_size.width / 2 - tip_size.width / 2}); $cell.mouseout(function (){ $('.tooltip').fadeOut(2000, function() {$(this).remove(); })}); }, LoopCheck: function(adrs) { var roots = []; return editable_table.RootExists(adrs, roots); }, RootExists: function(adrs, roots) { if (roots.indexOf(adrs) == -1) { roots.push(adrs); var cell_val = editable_table_val[adrs].value; if (/^=.*([A-Z]+\d+).*/.test(cell_val)) { var mtch_adrs = cell_val.match(/([A-Z]+\d+)/g); if (mtch_adrs.length == 0) { return false } for (adrs in mtch_adrs){ console.log(adrs); if (editable_table.RootExists(mtch_adrs[adrs], roots)) { return true } } } else { return false; } } else { return true } }, Recalculation: function() { for (adrs in editable_table_val) { editable_table.CellCalculation(adrs); } }, CellCalculation: function(adrs) { var $div = $("td[data-address='" + adrs + "'] > div:first"); var val = editable_table_val[adrs].value if (/^=.+/.test(val)) { if (editable_table.LoopCheck(adrs)) {editable_table.CalculateExeption(adrs, "循環参照")} // 本当はここにセル計算する処理を書きたい。 } else { $div.html(val); } }, CalculateExeption: function(adrs, err_val) { var $span = $("").append(err_val).addClass("calculate-exeption").hide(); $("td[data-address='" + adrs + "'] > div:first").html(editable_table_val[adrs].value).append($span); $span.fadeIn(1000); }, OptionSheetJS: function() { // FileSaver.min.js , xlsx.full.min.js 依存の機能 if (typeof(saveAs) == "function" && typeof(XLSX) == "object") { var $expt_btn = $("").attr("id", "editable_table_export").attr("type", "button") .addClass("export-xlsx").text("to XLSX"); var $inex_div = $("").addClass("in-out-xlsx").append($expt_btn); $('.editable-table').each(function () { $(this).css("margin-top", "34px"); }); $('.editable-table tr th:first').each(function (){ $(this).css("position", "relative").append($inex_div); }); $('button.export-xlsx').click(function () { editable_table.Export_XLSX() }); } }, Export_XLSX: function(){ // 式に戻してExport ⇒ セルの再計算をする。 $('.in-out-xlsx').remove(); for (adrs in editable_table_val) { $("td[data-address='" + adrs + "']").empty().text(editable_table_val[adrs].value); } var wopts = { bookType: 'xlsx', bookSST: false, type: 'binary'}; var workbook = {SheetNames: [], Sheets: {}}; var filename = ($("table.editable-table[export_filename]").length)?$("table.editable-table").attr("export_filename"):"html_table.xlsx" $('table.editable-table').each(function () { var n = $(this).attr('data-sheet-name'); n = (n)?n:'Sheet'+(workbook.SheetNames.length+1); workbook.SheetNames.push(n); workbook.Sheets[n] = XLSX.utils.table_to_sheet($(this).get(0), wopts); }); var wbout = XLSX.write(workbook, wopts); var s2ab = function (s) { var buf = new ArrayBuffer(s.length); var view = new Uint8Array(buf); for (var i = 0; i != s.length; ++i) { view[i] = s.charCodeAt(i) & 0xFF; } return buf; } saveAs(new Blob([s2ab(wbout)], {type: 'application/octet-stream'}), filename); editable_table.AddressSetting(); editable_table.Recalculation(); editable_table.OptionSheetJS(); } }