/** * Jspreadsheet v4.15.0 * * Website: https://bossanova.uk/jspreadsheet/ * Description: Create amazing web based spreadsheets. * * This software is distributed under MIT License */ // TablePress: var formula = ... removed. ;(function (global, factory) { // TablePress: Comment out next to lines to force creation of a global jspreadsheet object. // typeof exports === 'object' && typeof module !== 'undefined' ? module.exports = factory() : // typeof define === 'function' && define.amd ? define(factory) : global.jspreadsheet = global.jexcel = factory(); }(window, (function () { // TablePress: Use `window` instead of `this` to get a global jspreadsheet object. 'use strict'; // Basic version information var Version = function() { // Information var info = { title: 'Jspreadsheet', version: '4.15.0', type: 'CE', host: 'https://bossanova.uk/jspreadsheet', license: 'MIT', print: function() { return [ this.title + ' ' + this.type + ' ' + this.version, this.host, this.license ].join('\r\n'); } } return function() { return info; }; }(); /** * The value is a formula */ var isFormula = function(value) { var v = (''+value)[0]; return v == '=' || v == '#' ? true : false; } /** * Get the mask in the jSuites.mask format */ var getMask = function(o) { if (o.format || o.mask || o.locale) { var opt = {}; if (o.mask) { opt.mask = o.mask; } else if (o.format) { opt.mask = o.format; } else { opt.locale = o.locale; opt.options = o.options; } if (o.decimal) { if (! opt.options) { opt.options = {}; } opt.options = { decimal: o.decimal }; } return opt; } return null; } // Jspreadsheet core object var jexcel = (function(el, options) { // Create jspreadsheet object var obj = {}; obj.options = {}; if (! (el instanceof Element || el instanceof HTMLDocument)) { console.error('Jspreadsheet: el is not a valid DOM element'); return false; } else if (el.tagName == 'TABLE') { if (options = jexcel.createFromTable(el, options)) { var div = document.createElement('div'); el.parentNode.insertBefore(div, el); el.remove(); el = div; } else { console.error('Jspreadsheet: el is not a valid DOM element'); return false; } } // Loading default configuration var defaults = { // External data url:null, // Ajax options method: 'GET', requestVariables: null, // Data data:null, // Custom sorting handler sorting:null, // Copy behavior copyCompatibility:false, root:null, // Rows and columns definitions rows:[], columns:[], // Deprected legacy options colHeaders:[], colWidths:[], colAlignments:[], nestedHeaders:null, // Column width that is used by default defaultColWidth:50, defaultColAlign:'center', // Rows height default defaultRowHeight: null, // Spare rows and columns minSpareRows:0, minSpareCols:0, // Minimal table dimensions minDimensions:[0,0], // Allow Export allowExport:true, // @type {boolean} - Include the header titles on download includeHeadersOnDownload:false, // @type {boolean} - Include the header titles on copy includeHeadersOnCopy:false, // Allow column sorting columnSorting:true, // Allow column dragging columnDrag:false, // Allow column resizing columnResize:true, // Allow row resizing rowResize:false, // Allow row dragging rowDrag:true, // Allow table edition editable:true, // Allow new rows allowInsertRow:true, // Allow new rows allowManualInsertRow:true, // Allow new columns allowInsertColumn:true, // Allow new rows allowManualInsertColumn:true, // Allow row delete allowDeleteRow:true, // Allow deleting of all rows allowDeletingAllRows:false, // Allow column delete allowDeleteColumn:true, // Allow rename column allowRenameColumn:true, // Allow comments allowComments:false, // Global wrap wordWrap:false, // Image options imageOptions: null, // CSV source csv:null, // Filename csvFileName:'jspreadsheet', // Consider first line as header csvHeaders:true, // Delimiters csvDelimiter:',', // First row as header parseTableFirstRowAsHeader:false, parseTableAutoCellType:false, // Disable corner selection selectionCopy:true, // Merged cells mergeCells:{}, // Create toolbar toolbar:null, // Allow search search:false, // Create pagination pagination:false, paginationOptions:null, // Full screen fullscreen:false, // Lazy loading lazyLoading:false, loadingSpin:false, // Table overflow tableOverflow:false, tableHeight:'300px', tableWidth:null, textOverflow:false, // Meta meta: null, // Style style:null, classes:null, // Execute formulas parseFormulas:true, autoIncrement:true, autoCasting:true, // Security secureFormulas:true, stripHTML:true, stripHTMLOnCopy:false, // Filters filters:false, footers:null, // Event handles onundo:null, onredo:null, onload:null, onchange:null, oncomments:null, onbeforechange:null, onafterchanges:null, onbeforeinsertrow: null, oninsertrow:null, onbeforeinsertcolumn: null, oninsertcolumn:null, onbeforedeleterow:null, ondeleterow:null, onbeforedeletecolumn:null, ondeletecolumn:null, onmoverow:null, onmovecolumn:null, onresizerow:null, onresizecolumn:null, onsort:null, onselection:null, oncopy:null, onpaste:null, onbeforepaste:null, onmerge:null, onfocus:null, onblur:null, onchangeheader:null, oncreateeditor:null, oneditionstart:null, oneditionend:null, onchangestyle:null, onchangemeta:null, onchangepage:null, onbeforesave:null, onsave:null, // Global event dispatcher onevent:null, // Persistance persistance:false, // Customize any cell behavior updateTable:null, // Detach the HTML table when calling updateTable detachForUpdates: false, freezeColumns:null, // Texts text:{ noRecordsFound: 'No records found', showingPage: 'Showing page {0} of {1} entries', show: 'Show ', search: 'Search', entries: ' entries', columnName: 'Column name', insertANewColumnBefore: 'Insert a new column before', insertANewColumnAfter: 'Insert a new column after', deleteSelectedColumns: 'Delete selected columns', renameThisColumn: 'Rename this column', orderAscending: 'Order ascending', orderDescending: 'Order descending', insertANewRowBefore: 'Insert a new row before', insertANewRowAfter: 'Insert a new row after', deleteSelectedRows: 'Delete selected rows', editComments: 'Edit comments', addComments: 'Add comments', comments: 'Comments', clearComments: 'Clear comments', copy: 'Copy...', paste: 'Paste...', saveAs: 'Save as...', about: 'About', areYouSureToDeleteTheSelectedRows: 'Are you sure to delete the selected rows?', areYouSureToDeleteTheSelectedColumns: 'Are you sure to delete the selected columns?', thisActionWillDestroyAnyExistingMergedCellsAreYouSure: 'This action will destroy any existing merged cells. Are you sure?', thisActionWillClearYourSearchResultsAreYouSure: 'This action will clear your search results. Are you sure?', thereIsAConflictWithAnotherMergedCell: 'There is a conflict with another merged cell', invalidMergeProperties: 'Invalid merged properties', cellAlreadyMerged: 'Cell already merged', noCellsSelected: 'No cells selected', }, // About message about: true, }; // Loading initial configuration from user for (var property in defaults) { if (options && options.hasOwnProperty(property)) { if (property === 'text') { obj.options[property] = defaults[property]; for (var textKey in options[property]) { if (options[property].hasOwnProperty(textKey)){ obj.options[property][textKey] = options[property][textKey]; } } } else { obj.options[property] = options[property]; } } else { obj.options[property] = defaults[property]; } } // Global elements obj.el = el; obj.corner = null; obj.contextMenu = null; obj.textarea = null; obj.ads = null; obj.content = null; obj.table = null; obj.thead = null; obj.tbody = null; obj.rows = []; obj.results = null; obj.searchInput = null; obj.toolbar = null; obj.pagination = null; obj.pageNumber = null; obj.headerContainer = null; obj.colgroupContainer = null; // Containers obj.headers = []; obj.records = []; obj.history = []; obj.formula = []; obj.colgroup = []; obj.selection = []; obj.highlighted = []; obj.selectedCell = null; obj.selectedContainer = null; obj.style = []; obj.data = null; obj.filter = null; obj.filters = []; // Internal controllers obj.cursor = null; obj.historyIndex = -1; obj.ignoreEvents = false; obj.ignoreHistory = false; obj.edition = null; obj.hashString = null; obj.resizing = null; obj.dragging = null; // Lazy loading if (obj.options.lazyLoading == true && (obj.options.tableOverflow == false && obj.options.fullscreen == false)) { console.error('Jspreadsheet: The lazyloading only works when tableOverflow = yes or fullscreen = yes'); obj.options.lazyLoading = false; } /** * Activate/Disable fullscreen * use programmatically : table.fullscreen(); or table.fullscreen(true); or table.fullscreen(false); * @Param {boolean} activate */ obj.fullscreen = function(activate) { // If activate not defined, get reverse options.fullscreen if (activate == null) { activate = ! obj.options.fullscreen; } // If change if (obj.options.fullscreen != activate) { obj.options.fullscreen = activate; // Test LazyLoading conflict if (activate == true) { el.classList.add('fullscreen'); } else { el.classList.remove('fullscreen'); } } } /** * Trigger events */ obj.dispatch = function(event) { // Dispatch events if (! obj.ignoreEvents) { // Call global event if (typeof(obj.options.onevent) == 'function') { var ret = obj.options.onevent.apply(this, arguments); } // Call specific events if (typeof(obj.options[event]) == 'function') { var ret = obj.options[event].apply(this, Array.prototype.slice.call(arguments, 1)); } } // Persistance if (event == 'onafterchanges' && obj.options.persistance) { var url = obj.options.persistance == true ? obj.options.url : obj.options.persistance; var data = obj.prepareJson(arguments[2]); obj.save(url, data); } return ret; } /** * Prepare the jspreadsheet table * * @Param config */ obj.prepareTable = function() { // Loading initial data from remote sources var results = []; // Number of columns var size = obj.options.columns.length; if (obj.options.data && typeof(obj.options.data[0]) !== 'undefined') { // Data keys var keys = Object.keys(obj.options.data[0]); if (keys.length > size) { size = keys.length; } } // Minimal dimensions if (obj.options.minDimensions[0] > size) { size = obj.options.minDimensions[0]; } // Requests var multiple = []; // Preparations for (var i = 0; i < size; i++) { // Deprected options. You should use only columns if (! obj.options.colHeaders[i]) { obj.options.colHeaders[i] = ''; } if (! obj.options.colWidths[i]) { obj.options.colWidths[i] = obj.options.defaultColWidth; } if (! obj.options.colAlignments[i]) { obj.options.colAlignments[i] = obj.options.defaultColAlign; } // Default column description if (! obj.options.columns[i]) { obj.options.columns[i] = { type:'text' }; } else if (! obj.options.columns[i].type) { obj.options.columns[i].type = 'text'; } if (! obj.options.columns[i].name) { obj.options.columns[i].name = keys && keys[i] ? keys[i] : i; } if (! obj.options.columns[i].source) { obj.options.columns[i].source = []; } if (! obj.options.columns[i].options) { obj.options.columns[i].options = []; } if (! obj.options.columns[i].editor) { obj.options.columns[i].editor = null; } if (! obj.options.columns[i].allowEmpty) { obj.options.columns[i].allowEmpty = false; } if (! obj.options.columns[i].title) { obj.options.columns[i].title = obj.options.colHeaders[i] ? obj.options.colHeaders[i] : ''; } if (! obj.options.columns[i].width) { obj.options.columns[i].width = obj.options.colWidths[i] ? obj.options.colWidths[i] : obj.options.defaultColWidth; } if (! obj.options.columns[i].align) { obj.options.columns[i].align = obj.options.colAlignments[i] ? obj.options.colAlignments[i] : 'center'; } // Pre-load initial source for json autocomplete if (obj.options.columns[i].type == 'autocomplete' || obj.options.columns[i].type == 'dropdown') { // if remote content if (obj.options.columns[i].url) { multiple.push({ url: obj.options.columns[i].url, index: i, method: 'GET', dataType: 'json', success: function(data) { var source = []; for (var i = 0; i < data.length; i++) { obj.options.columns[this.index].source.push(data[i]); } } }); } } else if (obj.options.columns[i].type == 'calendar') { // Default format for date columns if (! obj.options.columns[i].options.format) { obj.options.columns[i].options.format = 'DD/MM/YYYY'; } } } // Create the table when is ready if (! multiple.length) { obj.createTable(); } else { jSuites.ajax(multiple, function() { obj.createTable(); }); } } obj.createTable = function() { // Elements obj.table = document.createElement('table'); obj.thead = document.createElement('thead'); obj.tbody = document.createElement('tbody'); // Create headers controllers obj.headers = []; obj.colgroup = []; // Create table container obj.content = document.createElement('div'); obj.content.classList.add('jexcel_content'); obj.content.onscroll = function(e) { obj.scrollControls(e); } obj.content.onwheel = function(e) { obj.wheelControls(e); } // Create toolbar object obj.toolbar = document.createElement('div'); obj.toolbar.classList.add('jexcel_toolbar'); // Search var searchContainer = document.createElement('div'); var searchText = document.createTextNode((obj.options.text.search) + ': '); obj.searchInput = document.createElement('input'); obj.searchInput.classList.add('jexcel_search'); searchContainer.appendChild(searchText); searchContainer.appendChild(obj.searchInput); obj.searchInput.onfocus = function() { obj.resetSelection(); } // Pagination select option var paginationUpdateContainer = document.createElement('div'); if (obj.options.pagination > 0 && obj.options.paginationOptions && obj.options.paginationOptions.length > 0) { obj.paginationDropdown = document.createElement('select'); obj.paginationDropdown.classList.add('jexcel_pagination_dropdown'); obj.paginationDropdown.onchange = function() { obj.options.pagination = parseInt(this.value); obj.page(0); } for (var i = 0; i < obj.options.paginationOptions.length; i++) { var temp = document.createElement('option'); temp.value = obj.options.paginationOptions[i]; temp.innerHTML = obj.options.paginationOptions[i]; obj.paginationDropdown.appendChild(temp); } // Set initial pagination value obj.paginationDropdown.value = obj.options.pagination; paginationUpdateContainer.appendChild(document.createTextNode(obj.options.text.show)); paginationUpdateContainer.appendChild(obj.paginationDropdown); paginationUpdateContainer.appendChild(document.createTextNode(obj.options.text.entries)); } // Filter and pagination container var filter = document.createElement('div'); filter.classList.add('jexcel_filter'); filter.appendChild(paginationUpdateContainer); filter.appendChild(searchContainer); // Colsgroup obj.colgroupContainer = document.createElement('colgroup'); var tempCol = document.createElement('col'); tempCol.setAttribute('width', '50'); obj.colgroupContainer.appendChild(tempCol); // Nested if (obj.options.nestedHeaders && obj.options.nestedHeaders.length > 0) { // Flexible way to handle nestedheaders if (obj.options.nestedHeaders[0] && obj.options.nestedHeaders[0][0]) { for (var j = 0; j < obj.options.nestedHeaders.length; j++) { obj.thead.appendChild(obj.createNestedHeader(obj.options.nestedHeaders[j])); } } else { obj.thead.appendChild(obj.createNestedHeader(obj.options.nestedHeaders)); } } // Row obj.headerContainer = document.createElement('tr'); var tempCol = document.createElement('td'); tempCol.classList.add('jexcel_selectall'); obj.headerContainer.appendChild(tempCol); for (var i = 0; i < obj.options.columns.length; i++) { // Create header obj.createCellHeader(i); // Append cell to the container obj.headerContainer.appendChild(obj.headers[i]); obj.colgroupContainer.appendChild(obj.colgroup[i]); } obj.thead.appendChild(obj.headerContainer); // Filters if (obj.options.filters == true) { obj.filter = document.createElement('tr'); var td = document.createElement('td'); obj.filter.appendChild(td); for (var i = 0; i < obj.options.columns.length; i++) { var td = document.createElement('td'); td.innerHTML = ' '; td.setAttribute('data-x', i); td.className = 'jexcel_column_filter'; if (obj.options.columns[i].type == 'hidden') { td.style.display = 'none'; } obj.filter.appendChild(td); } obj.thead.appendChild(obj.filter); } // Content table obj.table = document.createElement('table'); obj.table.classList.add('jexcel'); obj.table.setAttribute('cellpadding', '0'); obj.table.setAttribute('cellspacing', '0'); obj.table.setAttribute('unselectable', 'yes'); //obj.table.setAttribute('onselectstart', 'return false'); obj.table.appendChild(obj.colgroupContainer); obj.table.appendChild(obj.thead); obj.table.appendChild(obj.tbody); if (! obj.options.textOverflow) { obj.table.classList.add('jexcel_overflow'); } // Spreadsheet corner obj.corner = document.createElement('div'); obj.corner.className = 'jexcel_corner'; obj.corner.setAttribute('unselectable', 'on'); obj.corner.setAttribute('onselectstart', 'return false'); if (obj.options.selectionCopy == false) { obj.corner.style.display = 'none'; } // Textarea helper obj.textarea = document.createElement('textarea'); obj.textarea.className = 'jexcel_textarea'; obj.textarea.id = 'jexcel_textarea'; obj.textarea.tabIndex = '-1'; // Contextmenu container obj.contextMenu = document.createElement('div'); obj.contextMenu.className = 'jexcel_contextmenu'; // Create element jSuites.contextmenu(obj.contextMenu, { onclick:function() { obj.contextMenu.contextmenu.close(false); } }); // Powered by Jspreadsheet var ads = document.createElement('a'); ads.setAttribute('href', 'https://bossanova.uk/jspreadsheet/'); obj.ads = document.createElement('div'); obj.ads.className = 'jexcel_about'; var span = document.createElement('span'); span.innerHTML = 'Jspreadsheet CE'; ads.appendChild(span); obj.ads.appendChild(ads); // Create table container TODO: frozen columns var container = document.createElement('div'); container.classList.add('jexcel_table'); // Pagination obj.pagination = document.createElement('div'); obj.pagination.classList.add('jexcel_pagination'); var paginationInfo = document.createElement('div'); var paginationPages = document.createElement('div'); obj.pagination.appendChild(paginationInfo); obj.pagination.appendChild(paginationPages); // Hide pagination if not in use if (! obj.options.pagination) { obj.pagination.style.display = 'none'; } // Append containers to the table if (obj.options.search == true) { el.appendChild(filter); } // Elements obj.content.appendChild(obj.table); obj.content.appendChild(obj.corner); obj.content.appendChild(obj.textarea); el.appendChild(obj.toolbar); el.appendChild(obj.content); el.appendChild(obj.pagination); el.appendChild(obj.contextMenu); el.appendChild(obj.ads); el.classList.add('jexcel_container'); // Create toolbar if (obj.options.toolbar && obj.options.toolbar.length) { obj.createToolbar(); } // Fullscreen if (obj.options.fullscreen == true) { el.classList.add('fullscreen'); } else { // Overflow if (obj.options.tableOverflow == true) { if (obj.options.tableHeight) { obj.content.style['overflow-y'] = 'auto'; obj.content.style['box-shadow'] = 'rgb(221 221 221) 2px 2px 5px 0.1px'; obj.content.style.maxHeight = obj.options.tableHeight; } if (obj.options.tableWidth) { obj.content.style['overflow-x'] = 'auto'; obj.content.style.width = obj.options.tableWidth; } } } // With toolbars if (obj.options.tableOverflow != true && obj.options.toolbar) { el.classList.add('with-toolbar'); } // Actions if (obj.options.columnDrag == true) { obj.thead.classList.add('draggable'); } if (obj.options.columnResize == true) { obj.thead.classList.add('resizable'); } if (obj.options.rowDrag == true) { obj.tbody.classList.add('draggable'); } if (obj.options.rowResize == true) { obj.tbody.classList.add('resizable'); } // Load data obj.setData(); // Style if (obj.options.style) { obj.setStyle(obj.options.style, null, null, 1, 1); } // Classes if (obj.options.classes) { var k = Object.keys(obj.options.classes); for (var i = 0; i < k.length; i++) { var cell = jexcel.getIdFromColumnName(k[i], true); obj.records[cell[1]][cell[0]].classList.add(obj.options.classes[k[i]]); } } } /** * Refresh the data * * @return void */ obj.refresh = function() { if (obj.options.url) { // Loading if (obj.options.loadingSpin == true) { jSuites.loading.show(); } jSuites.ajax({ url: obj.options.url, method: obj.options.method, data: obj.options.requestVariables, dataType: 'json', success: function(result) { // Data obj.options.data = (result.data) ? result.data : result; // Prepare table obj.setData(); // Hide spin if (obj.options.loadingSpin == true) { jSuites.loading.hide(); } } }); } else { obj.setData(); } } /** * Set data * * @param array data In case no data is sent, default is reloaded * @return void */ obj.setData = function(data) { // Update data if (data) { if (typeof(data) == 'string') { data = JSON.parse(data); } obj.options.data = data; } // Data if (! obj.options.data) { obj.options.data = []; } // Prepare data if (obj.options.data && obj.options.data[0]) { if (! Array.isArray(obj.options.data[0])) { var data = []; for (var j = 0; j < obj.options.data.length; j++) { var row = []; for (var i = 0; i < obj.options.columns.length; i++) { row[i] = obj.options.data[j][obj.options.columns[i].name]; } data.push(row); } obj.options.data = data; } } // Adjust minimal dimensions var j = 0; var i = 0; var size_i = obj.options.columns.length; var size_j = obj.options.data.length; var min_i = obj.options.minDimensions[0]; var min_j = obj.options.minDimensions[1]; var max_i = min_i > size_i ? min_i : size_i; var max_j = min_j > size_j ? min_j : size_j; for (j = 0; j < max_j; j++) { for (i = 0; i < max_i; i++) { if (obj.options.data[j] == undefined) { obj.options.data[j] = []; } if (obj.options.data[j][i] == undefined) { obj.options.data[j][i] = ''; } } } // Reset containers obj.rows = []; obj.results = null; obj.records = []; obj.history = []; // Reset internal controllers obj.historyIndex = -1; // Reset data obj.tbody.innerHTML = ''; // Lazy loading if (obj.options.lazyLoading == true) { // Load only 100 records var startNumber = 0 var finalNumber = obj.options.data.length < 100 ? obj.options.data.length : 100; if (obj.options.pagination) { obj.options.pagination = false; console.error('Jspreadsheet: Pagination will be disable due the lazyLoading'); } } else if (obj.options.pagination) { // Pagination if (! obj.pageNumber) { obj.pageNumber = 0; } var quantityPerPage = obj.options.pagination; startNumber = (obj.options.pagination * obj.pageNumber); finalNumber = (obj.options.pagination * obj.pageNumber) + obj.options.pagination; if (obj.options.data.length < finalNumber) { finalNumber = obj.options.data.length; } } else { var startNumber = 0; var finalNumber = obj.options.data.length; } // Append nodes to the HTML for (j = 0; j < obj.options.data.length; j++) { // Create row var tr = obj.createRow(j, obj.options.data[j]); // Append line to the table if (j >= startNumber && j < finalNumber) { obj.tbody.appendChild(tr); } } if (obj.options.lazyLoading == true) { // Do not create pagination with lazyloading activated } else if (obj.options.pagination) { obj.updatePagination(); } // Merge cells if (obj.options.mergeCells) { var keys = Object.keys(obj.options.mergeCells); for (var i = 0; i < keys.length; i++) { var num = obj.options.mergeCells[keys[i]]; obj.setMerge(keys[i], num[0], num[1], 1); } } // Update table with custom configurations if applicable obj.updateTable(); // Onload obj.dispatch('onload', el, obj); } /** * Get the whole table data * * @param bool get highlighted cells only * @return array data */ obj.getData = function(highlighted, dataOnly) { // Control vars var dataset = []; var px = 0; var py = 0; // Data type var dataType = dataOnly == true || obj.options.copyCompatibility == false ? true : false; // Column and row length var x = obj.options.columns.length var y = obj.options.data.length // Go through the columns to get the data for (var j = 0; j < y; j++) { px = 0; for (var i = 0; i < x; i++) { // Cell selected or fullset if (! highlighted || obj.records[j][i].classList.contains('highlight')) { // Get value if (! dataset[py]) { dataset[py] = []; } if (! dataType) { dataset[py][px] = obj.records[j][i].innerHTML; } else { dataset[py][px] = obj.options.data[j][i]; } px++; } } if (px > 0) { py++; } } return dataset; } /** * Get json data by row number * * @param integer row number * @return object */ obj.getJsonRow = function(rowNumber) { var rowData = obj.options.data[rowNumber]; var x = obj.options.columns.length var row = {}; for (var i = 0; i < x; i++) { if (! obj.options.columns[i].name) { obj.options.columns[i].name = i; } row[obj.options.columns[i].name] = rowData[i]; } return row; } /** * Get the whole table data * * @param bool highlighted cells only * @return string value */ obj.getJson = function(highlighted) { // Control vars var data = []; // Column and row length var x = obj.options.columns.length var y = obj.options.data.length // Go through the columns to get the data for (var j = 0; j < y; j++) { var row = null; for (var i = 0; i < x; i++) { if (! highlighted || obj.records[j][i].classList.contains('highlight')) { if (row == null) { row = {}; } if (! obj.options.columns[i].name) { obj.options.columns[i].name = i; } row[obj.options.columns[i].name] = obj.options.data[j][i]; } } if (row != null) { data.push(row); } } return data; } /** * Prepare JSON in the correct format */ obj.prepareJson = function(data) { var rows = []; for (var i = 0; i < data.length; i++) { var x = data[i].x; var y = data[i].y; var k = obj.options.columns[x].name ? obj.options.columns[x].name : x; // Create row if (! rows[y]) { rows[y] = { row: y, data: {}, }; } rows[y].data[k] = data[i].newValue; } // Filter rows return rows.filter(function (el) { return el != null; }); } /** * Post json to a remote server */ obj.save = function(url, data) { // Parse anything in the data before sending to the server var ret = obj.dispatch('onbeforesave', el, obj, data); if (ret) { var data = ret; } else { if (ret === false) { return false; } } // Remove update jSuites.ajax({ url: url, method: 'POST', dataType: 'json', data: { data: JSON.stringify(data) }, success: function(result) { // Event obj.dispatch('onsave', el, obj, data); } }); } /** * Get a row data by rowNumber */ obj.getRowData = function(rowNumber) { return obj.options.data[rowNumber]; } /** * Set a row data by rowNumber */ obj.setRowData = function(rowNumber, data) { for (var i = 0; i < obj.headers.length; i++) { // Update cell var columnName = jexcel.getColumnNameFromId([ i, rowNumber ]); // Set value if (data[i] != null) { obj.setValue(columnName, data[i]); } } } /** * Get a column data by columnNumber */ obj.getColumnData = function(columnNumber) { var dataset = []; // Go through the rows to get the data for (var j = 0; j < obj.options.data.length; j++) { dataset.push(obj.options.data[j][columnNumber]); } return dataset; } /** * Set a column data by colNumber */ obj.setColumnData = function(colNumber, data) { for (var j = 0; j < obj.rows.length; j++) { // Update cell var columnName = jexcel.getColumnNameFromId([ colNumber, j ]); // Set value if (data[j] != null) { obj.setValue(columnName, data[j]); } } } /** * Create row */ obj.createRow = function(j, data) { // Create container if (! obj.records[j]) { obj.records[j] = []; } // Default data if (! data) { var data = obj.options.data[j]; } // New line of data to be append in the table obj.rows[j] = document.createElement('tr'); obj.rows[j].setAttribute('data-y', j); // Index var index = null; // Set default row height if (obj.options.defaultRowHeight) { obj.rows[j].style.height = obj.options.defaultRowHeight + 'px' } // Definitions if (obj.options.rows[j]) { if (obj.options.rows[j].height) { obj.rows[j].style.height = obj.options.rows[j].height; } if (obj.options.rows[j].title) { index = obj.options.rows[j].title; } } if (! index) { index = parseInt(j + 1); } // Row number label var td = document.createElement('td'); td.innerHTML = index; td.setAttribute('data-y', j); td.className = 'jexcel_row'; obj.rows[j].appendChild(td); // Data columns for (var i = 0; i < obj.options.columns.length; i++) { // New column of data to be append in the line obj.records[j][i] = obj.createCell(i, j, data[i]); // Add column to the row obj.rows[j].appendChild(obj.records[j][i]); } // Add row to the table body return obj.rows[j]; } obj.parseValue = function(i, j, value, cell) { if ((''+value).substr(0,1) == '=' && obj.options.parseFormulas == true) { value = obj.executeFormula(value, i, j) } // Column options var options = obj.options.columns[i]; if (options && ! isFormula(value)) { // Mask options var opt = null; if (opt = getMask(options)) { if (value && value == Number(value)) { value = Number(value); } // Process the decimals to match the mask var masked = jSuites.mask.render(value, opt, true); // Negative indication if (cell) { if (opt.mask) { var t = opt.mask.split(';'); if (t[1]) { var t1 = t[1].match(new RegExp('\\[Red\\]', 'gi')); if (t1) { if (value < 0) { cell.classList.add('red'); } else { cell.classList.remove('red'); } } var t2 = t[1].match(new RegExp('\\(', 'gi')); if (t2) { if (value < 0) { masked = '(' + masked + ')'; } } } } } if (masked) { value = masked; } } } return value; } var validDate = function(date) { date = ''+date; if (date.substr(4,1) == '-' && date.substr(7,1) == '-') { return true; } else { date = date.split('-'); if ((date[0].length == 4 && date[0] == Number(date[0]) && date[1].length == 2 && date[1] == Number(date[1]))) { return true; } } return false; } /** * Create cell */ obj.createCell = function(i, j, value) { // Create cell and properties var td = document.createElement('td'); td.setAttribute('data-x', i); td.setAttribute('data-y', j); // Security if ((''+value).substr(0,1) == '=' && obj.options.secureFormulas == true) { var val = secureFormula(value); if (val != value) { // Update the data container value = val; } } // Custom column if (obj.options.columns[i].editor) { if (obj.options.stripHTML === false || obj.options.columns[i].stripHTML === false) { td.innerHTML = value; } else { td.textContent = value; } if (typeof(obj.options.columns[i].editor.createCell) == 'function') { td = obj.options.columns[i].editor.createCell(td); } } else { // Hidden column if (obj.options.columns[i].type == 'hidden') { td.style.display = 'none'; td.textContent = value; } else if (obj.options.columns[i].type == 'checkbox' || obj.options.columns[i].type == 'radio') { // Create input var element = document.createElement('input'); element.type = obj.options.columns[i].type; element.name = 'c' + i; element.checked = (value == 1 || value == true || value == 'true') ? true : false; element.onclick = function() { obj.setValue(td, this.checked); } if (obj.options.columns[i].readOnly == true || obj.options.editable == false) { element.setAttribute('disabled', 'disabled'); } // Append to the table td.appendChild(element); // Make sure the values are correct obj.options.data[j][i] = element.checked; } else if (obj.options.columns[i].type == 'calendar') { // Try formatted date var formatted = null; if (! validDate(value)) { var tmp = jSuites.calendar.extractDateFromString(value, obj.options.columns[i].options.format); if (tmp) { formatted = tmp; } } // Create calendar cell td.textContent = jSuites.calendar.getDateString(formatted ? formatted : value, obj.options.columns[i].options.format); } else if (obj.options.columns[i].type == 'dropdown' || obj.options.columns[i].type == 'autocomplete') { // Create dropdown cell td.classList.add('jexcel_dropdown'); td.textContent = obj.getDropDownValue(i, value); } else if (obj.options.columns[i].type == 'color') { if (obj.options.columns[i].render == 'square') { var color = document.createElement('div'); color.className = 'color'; color.style.backgroundColor = value; td.appendChild(color); } else { td.style.color = value; td.textContent = value; } } else if (obj.options.columns[i].type == 'image') { if (value && value.substr(0, 10) == 'data:image') { var img = document.createElement('img'); img.src = value; td.appendChild(img); } } else { if (obj.options.columns[i].type == 'html') { td.innerHTML = stripScript(obj.parseValue(i, j, value, td)); } else { if (obj.options.stripHTML === false || obj.options.columns[i].stripHTML === false) { td.innerHTML = stripScript(obj.parseValue(i, j, value, td)); } else { // TablePress: Add a div container inside each cell. // td.textContent = obj.parseValue(i, j, value, td); var div = document.createElement('div'); div.textContent = obj.parseValue(i, j, value, td); td.appendChild(div); } } } } // Readonly if (obj.options.columns[i].readOnly == true) { td.className = 'readonly'; } // Text align var colAlign = obj.options.columns[i].align ? obj.options.columns[i].align : 'center'; td.style.textAlign = colAlign; // Wrap option if (obj.options.columns[i].wordWrap != false && (obj.options.wordWrap == true || obj.options.columns[i].wordWrap == true || td.innerHTML.length > 200)) { td.style.whiteSpace = 'pre-wrap'; } // Overflow if (i > 0) { if (this.options.textOverflow == true) { if (value || td.innerHTML) { obj.records[j][i-1].style.overflow = 'hidden'; } else { if (i == obj.options.columns.length - 1) { td.style.overflow = 'hidden'; } } } } return td; } obj.createCellHeader = function(colNumber) { // Create col global control var colWidth = obj.options.columns[colNumber].width ? obj.options.columns[colNumber].width : obj.options.defaultColWidth; var colAlign = obj.options.columns[colNumber].align ? obj.options.columns[colNumber].align : obj.options.defaultColAlign; // Create header cell obj.headers[colNumber] = document.createElement('td'); if (obj.options.stripHTML) { obj.headers[colNumber].textContent = obj.options.columns[colNumber].title ? obj.options.columns[colNumber].title : jexcel.getColumnName(colNumber); } else { obj.headers[colNumber].innerHTML = obj.options.columns[colNumber].title ? obj.options.columns[colNumber].title : jexcel.getColumnName(colNumber); } obj.headers[colNumber].setAttribute('data-x', colNumber); obj.headers[colNumber].style.textAlign = colAlign; if (obj.options.columns[colNumber].title) { obj.headers[colNumber].setAttribute('title', obj.headers[colNumber].innerText); } if (obj.options.columns[colNumber].id) { obj.headers[colNumber].setAttribute('id', obj.options.columns[colNumber].id); } // Width control obj.colgroup[colNumber] = document.createElement('col'); obj.colgroup[colNumber].setAttribute('width', colWidth); // Hidden column if (obj.options.columns[colNumber].type == 'hidden') { obj.headers[colNumber].style.display = 'none'; obj.colgroup[colNumber].style.display = 'none'; } } /** * Update a nested header title */ obj.updateNestedHeader = function(x, y, title) { if (obj.options.nestedHeaders[y][x].title) { obj.options.nestedHeaders[y][x].title = title; obj.options.nestedHeaders[y].element.children[x+1].textContent = title; } } /** * Create a nested header object */ obj.createNestedHeader = function(nestedInformation) { var tr = document.createElement('tr'); tr.classList.add('jexcel_nested'); var td = document.createElement('td'); tr.appendChild(td); // Element nestedInformation.element = tr; var headerIndex = 0; for (var i = 0; i < nestedInformation.length; i++) { // Default values if (! nestedInformation[i].colspan) { nestedInformation[i].colspan = 1; } if (! nestedInformation[i].align) { nestedInformation[i].align = 'center'; } if (! nestedInformation[i].title) { nestedInformation[i].title = ''; } if (! nestedInformation[i].id) { nestedInformation[i].id = ''; } // Number of columns var numberOfColumns = nestedInformation[i].colspan; // Classes container var column = []; // Header classes for this cell for (var x = 0; x < numberOfColumns; x++) { if (obj.options.columns[headerIndex] && obj.options.columns[headerIndex].type == 'hidden') { numberOfColumns++; } column.push(headerIndex); headerIndex++; } // Created the nested cell var td = document.createElement('td'); td.setAttribute('data-column', column.join(',')); td.setAttribute('colspan', nestedInformation[i].colspan); td.setAttribute('align', nestedInformation[i].align); td.setAttribute('id', nestedInformation[i].id); td.textContent = nestedInformation[i].title; tr.appendChild(td); } return tr; } /** * Create toolbar */ obj.createToolbar = function(toolbar) { if (toolbar) { obj.options.toolbar = toolbar; } else { var toolbar = obj.options.toolbar; } for (var i = 0; i < toolbar.length; i++) { if (toolbar[i].type == 'i') { var toolbarItem = document.createElement('i'); toolbarItem.classList.add('jexcel_toolbar_item'); toolbarItem.classList.add('material-icons'); toolbarItem.setAttribute('data-k', toolbar[i].k); toolbarItem.setAttribute('data-v', toolbar[i].v); toolbarItem.setAttribute('id', toolbar[i].id); // Tooltip if (toolbar[i].tooltip) { toolbarItem.setAttribute('title', toolbar[i].tooltip); } // Handle click if (toolbar[i].onclick && typeof(toolbar[i].onclick)) { toolbarItem.onclick = (function (a) { var b = a; return function () { toolbar[b].onclick(el, obj, this); }; })(i); } else { toolbarItem.onclick = function() { var k = this.getAttribute('data-k'); var v = this.getAttribute('data-v'); obj.setStyle(obj.highlighted, k, v); } } // Append element toolbarItem.textContent = toolbar[i].content; obj.toolbar.appendChild(toolbarItem); } else if (toolbar[i].type == 'select') { var toolbarItem = document.createElement('select'); var raiseInitialOnChange = false; toolbarItem.classList.add('jexcel_toolbar_item'); toolbarItem.setAttribute('data-k', toolbar[i].k); // Tooltip if (toolbar[i].tooltip) { toolbarItem.setAttribute('title', toolbar[i].tooltip); } // Handle onchange if (toolbar[i].onchange && typeof(toolbar[i].onchange)) { toolbarItem.onchange = toolbar[i].onchange; raiseInitialOnChange = true; } else { toolbarItem.onchange = function() { var k = this.getAttribute('data-k'); obj.setStyle(obj.highlighted, k, this.value); } } // Add options to the dropdown for(var j = 0; j < toolbar[i].v.length; j++) { var toolbarDropdownOption = document.createElement('option'); toolbarDropdownOption.value = toolbar[i].v[j]; toolbarDropdownOption.textContent = toolbar[i].v[j]; if (toolbar[i].selectedValue && toolbarDropdownOption.value === toolbar[i].selectedValue) { toolbarDropdownOption.selected = true; } toolbarItem.appendChild(toolbarDropdownOption); } if (raiseInitialOnChange) { toolbarItem.dispatchEvent(new Event('change')); } obj.toolbar.appendChild(toolbarItem); } else if (toolbar[i].type == 'color') { var toolbarItem = document.createElement('i'); toolbarItem.classList.add('jexcel_toolbar_item'); toolbarItem.classList.add('material-icons'); toolbarItem.setAttribute('data-k', toolbar[i].k); toolbarItem.setAttribute('data-v', ''); // Tooltip if (toolbar[i].tooltip) { toolbarItem.setAttribute('title', toolbar[i].tooltip); } obj.toolbar.appendChild(toolbarItem); toolbarItem.textContent = toolbar[i].content; jSuites.color(toolbarItem, { onchange:function(o, v) { var k = o.getAttribute('data-k'); obj.setStyle(obj.highlighted, k, v); } }); } } } /** * Merge cells * @param cellName * @param colspan * @param rowspan * @param ignoreHistoryAndEvents */ obj.setMerge = function(cellName, colspan, rowspan, ignoreHistoryAndEvents) { var test = false; if (! cellName) { if (! obj.highlighted.length) { alert(obj.options.text.noCellsSelected); return null; } else { var x1 = parseInt(obj.highlighted[0].getAttribute('data-x')); var y1 = parseInt(obj.highlighted[0].getAttribute('data-y')); var x2 = parseInt(obj.highlighted[obj.highlighted.length-1].getAttribute('data-x')); var y2 = parseInt(obj.highlighted[obj.highlighted.length-1].getAttribute('data-y')); var cellName = jexcel.getColumnNameFromId([ x1, y1 ]); var colspan = (x2 - x1) + 1; var rowspan = (y2 - y1) + 1; } } var cell = jexcel.getIdFromColumnName(cellName, true); if (obj.options.mergeCells[cellName]) { if (obj.records[cell[1]][cell[0]].getAttribute('data-merged')) { test = obj.options.text.cellAlreadyMerged; } } else if ((! colspan || colspan < 2) && (! rowspan || rowspan < 2)) { test = obj.options.text.invalidMergeProperties; } else { var cells = []; for (var j = cell[1]; j < cell[1] + rowspan; j++) { for (var i = cell[0]; i < cell[0] + colspan; i++) { var columnName = jexcel.getColumnNameFromId([i, j]); if (obj.records[j][i].getAttribute('data-merged')) { test = obj.options.text.thereIsAConflictWithAnotherMergedCell; } } } } if (test) { alert(test); } else { // Add property if (colspan > 1) { obj.records[cell[1]][cell[0]].setAttribute('colspan', colspan); } else { colspan = 1; } if (rowspan > 1) { obj.records[cell[1]][cell[0]].setAttribute('rowspan', rowspan); } else { rowspan = 1; } // Keep links to the existing nodes obj.options.mergeCells[cellName] = [ colspan, rowspan, [] ]; // Mark cell as merged obj.records[cell[1]][cell[0]].setAttribute('data-merged', 'true'); // Overflow obj.records[cell[1]][cell[0]].style.overflow = 'hidden'; // History data var data = []; // Adjust the nodes for (var y = cell[1]; y < cell[1] + rowspan; y++) { for (var x = cell[0]; x < cell[0] + colspan; x++) { if (! (cell[0] == x && cell[1] == y)) { data.push(obj.options.data[y][x]); obj.updateCell(x, y, '', true); obj.options.mergeCells[cellName][2].push(obj.records[y][x]); obj.records[y][x].style.display = 'none'; obj.records[y][x] = obj.records[cell[1]][cell[0]]; } } } // In the initialization is not necessary keep the history obj.updateSelection(obj.records[cell[1]][cell[0]]); if (! ignoreHistoryAndEvents) { obj.setHistory({ action:'setMerge', column:cellName, colspan:colspan, rowspan:rowspan, data:data, }); obj.dispatch('onmerge', el, cellName, colspan, rowspan); } } } /** * Merge cells * @param cellName * @param colspan * @param rowspan * @param ignoreHistoryAndEvents */ obj.getMerge = function(cellName) { var data = {}; if (cellName) { if (obj.options.mergeCells[cellName]) { data = [ obj.options.mergeCells[cellName][0], obj.options.mergeCells[cellName][1] ]; } else { data = null; } } else { if (obj.options.mergeCells) { var mergedCells = obj.options.mergeCells; var keys = Object.keys(obj.options.mergeCells); for (var i = 0; i < keys.length; i++) { data[keys[i]] = [ obj.options.mergeCells[keys[i]][0], obj.options.mergeCells[keys[i]][1] ]; } } } return data; } /** * Remove merge by cellname * @param cellName */ obj.removeMerge = function(cellName, data, keepOptions) { if (obj.options.mergeCells[cellName]) { var cell = jexcel.getIdFromColumnName(cellName, true); obj.records[cell[1]][cell[0]].removeAttribute('colspan'); obj.records[cell[1]][cell[0]].removeAttribute('rowspan'); obj.records[cell[1]][cell[0]].removeAttribute('data-merged'); var info = obj.options.mergeCells[cellName]; var index = 0; for (var j = 0; j < info[1]; j++) { for (var i = 0; i < info[0]; i++) { if (j > 0 || i > 0) { obj.records[cell[1]+j][cell[0]+i] = info[2][index]; obj.records[cell[1]+j][cell[0]+i].style.display = ''; // Recover data if (data && data[index]) { obj.updateCell(cell[0]+i, cell[1]+j, data[index]); } index++; } } } // Update selection obj.updateSelection(obj.records[cell[1]][cell[0]], obj.records[cell[1]+j-1][cell[0]+i-1]); if (! keepOptions) { delete(obj.options.mergeCells[cellName]); } } } /** * Remove all merged cells */ obj.destroyMerged = function(keepOptions) { // Remove any merged cells if (obj.options.mergeCells) { var mergedCells = obj.options.mergeCells; var keys = Object.keys(obj.options.mergeCells); for (var i = 0; i < keys.length; i++) { obj.removeMerge(keys[i], null, keepOptions); } } } /** * Is column merged */ obj.isColMerged = function(x, insertBefore) { var cols = []; // Remove any merged cells if (obj.options.mergeCells) { var keys = Object.keys(obj.options.mergeCells); for (var i = 0; i < keys.length; i++) { var info = jexcel.getIdFromColumnName(keys[i], true); var colspan = obj.options.mergeCells[keys[i]][0]; var x1 = info[0]; var x2 = info[0] + (colspan > 1 ? colspan - 1 : 0); if (insertBefore == null) { if ((x1 <= x && x2 >= x)) { cols.push(keys[i]); } } else { if (insertBefore) { if ((x1 < x && x2 >= x)) { cols.push(keys[i]); } } else { if ((x1 <= x && x2 > x)) { cols.push(keys[i]); } } } } } return cols; } /** * Is rows merged */ obj.isRowMerged = function(y, insertBefore) { var rows = []; // Remove any merged cells if (obj.options.mergeCells) { var keys = Object.keys(obj.options.mergeCells); for (var i = 0; i < keys.length; i++) { var info = jexcel.getIdFromColumnName(keys[i], true); var rowspan = obj.options.mergeCells[keys[i]][1]; var y1 = info[1]; var y2 = info[1] + (rowspan > 1 ? rowspan - 1 : 0); if (insertBefore == null) { if ((y1 <= y && y2 >= y)) { rows.push(keys[i]); } } else { if (insertBefore) { if ((y1 < y && y2 >= y)) { rows.push(keys[i]); } } else { if ((y1 <= y && y2 > y)) { rows.push(keys[i]); } } } } } return rows; } /** * Open the column filter */ obj.openFilter = function(columnId) { if (! obj.options.filters) { console.log('Jspreadsheet: filters not enabled.'); } else { // Make sure is integer columnId = parseInt(columnId); // Reset selection obj.resetSelection(); // Load options var optionsFiltered = []; if (obj.options.columns[columnId].type == 'checkbox') { optionsFiltered.push({ id: 'true', name: 'True' }); optionsFiltered.push({ id: 'false', name: 'False' }); } else { var options = []; var hasBlanks = false; for (var j = 0; j < obj.options.data.length; j++) { var k = obj.options.data[j][columnId]; var v = obj.records[j][columnId].innerHTML; if (k && v) { options[k] = v; } else { var hasBlanks = true; } } var keys = Object.keys(options); var optionsFiltered = []; for (var j = 0; j < keys.length; j++) { optionsFiltered.push({ id: keys[j], name: options[keys[j]] }); } // Has blank options if (hasBlanks) { optionsFiltered.push({ value: '', id: '', name: '(Blanks)' }); } } // Create dropdown var div = document.createElement('div'); obj.filter.children[columnId + 1].innerHTML = ''; obj.filter.children[columnId + 1].appendChild(div); obj.filter.children[columnId + 1].style.paddingLeft = '0px'; obj.filter.children[columnId + 1].style.paddingRight = '0px'; obj.filter.children[columnId + 1].style.overflow = 'initial'; var opt = { data: optionsFiltered, multiple: true, autocomplete: true, opened: true, value: obj.filters[columnId] !== undefined ? obj.filters[columnId] : null, width:'100%', position: (obj.options.tableOverflow == true || obj.options.fullscreen == true) ? true : false, onclose: function(o) { obj.resetFilters(); obj.filters[columnId] = o.dropdown.getValue(true); obj.filter.children[columnId + 1].innerHTML = o.dropdown.getText(); obj.filter.children[columnId + 1].style.paddingLeft = ''; obj.filter.children[columnId + 1].style.paddingRight = ''; obj.filter.children[columnId + 1].style.overflow = ''; obj.closeFilter(columnId); obj.refreshSelection(); } }; // Dynamic dropdown jSuites.dropdown(div, opt); } } obj.resetFilters = function() { if (obj.options.filters) { for (var i = 0; i < obj.filter.children.length; i++) { obj.filter.children[i].innerHTML = ' '; obj.filters[i] = null; } } obj.results = null; obj.updateResult(); } obj.closeFilter = function(columnId) { if (! columnId) { for (var i = 0; i < obj.filter.children.length; i++) { if (obj.filters[i]) { columnId = i; } } } // Search filter var search = function(query, x, y) { for (var i = 0; i < query.length; i++) { var value = ''+obj.options.data[y][x]; var label = ''+obj.records[y][x].innerHTML; if (query[i] == value || query[i] == label) { return true; } } return false; } var query = obj.filters[columnId]; obj.results = []; for (var j = 0; j < obj.options.data.length; j++) { if (search(query, columnId, j)) { obj.results.push(j); } } if (! obj.results.length) { obj.results = null; } obj.updateResult(); } /** * Open the editor * * @param object cell * @return void */ obj.openEditor = function(cell, empty, e) { // Get cell position var y = cell.getAttribute('data-y'); var x = cell.getAttribute('data-x'); // On edition start obj.dispatch('oneditionstart', el, cell, x, y); // Overflow if (x > 0) { obj.records[y][x-1].style.overflow = 'hidden'; } // Create editor var createEditor = function(type) { // Cell information var info = cell.getBoundingClientRect(); // Create dropdown var editor = document.createElement(type); /* TablePress: The values in the differences were adjusted to make the textarea fit. */ editor.style.width = (info.width - 6) + 'px'; editor.style.height = (info.height - 6) + 'px'; editor.style.minHeight = (info.height - 6) + 'px'; // Edit cell cell.classList.add('editor'); cell.innerHTML = ''; cell.appendChild(editor); // On edition start obj.dispatch('oncreateeditor', el, cell, x, y, editor); return editor; } // Readonly if (cell.classList.contains('readonly') == true) { // Do nothing } else { // Holder obj.edition = [ obj.records[y][x], obj.records[y][x].innerHTML, x, y ]; // If there is a custom editor for it if (obj.options.columns[x].editor) { // Custom editors obj.options.columns[x].editor.openEditor(cell, el, empty, e); } else { // Native functions if (obj.options.columns[x].type == 'hidden') { // Do nothing } else if (obj.options.columns[x].type == 'checkbox' || obj.options.columns[x].type == 'radio') { // Get value var value = cell.children[0].checked ? false : true; // Toogle value obj.setValue(cell, value); // Do not keep edition open obj.edition = null; } else if (obj.options.columns[x].type == 'dropdown' || obj.options.columns[x].type == 'autocomplete') { // Get current value var value = obj.options.data[y][x]; if (obj.options.columns[x].multiple && !Array.isArray(value)) { value = value.split(';'); } // Create dropdown if (typeof(obj.options.columns[x].filter) == 'function') { var source = obj.options.columns[x].filter(el, cell, x, y, obj.options.columns[x].source); } else { var source = obj.options.columns[x].source; } // Do not change the original source var data = []; for (var j = 0; j < source.length; j++) { data.push(source[j]); } // Create editor var editor = createEditor('div'); var options = { data: data, multiple: obj.options.columns[x].multiple ? true : false, autocomplete: obj.options.columns[x].autocomplete || obj.options.columns[x].type == 'autocomplete' ? true : false, opened:true, value: value, width:'100%', height:editor.style.minHeight, position: (obj.options.tableOverflow == true || obj.options.fullscreen == true) ? true : false, onclose:function() { obj.closeEditor(cell, true); } }; if (obj.options.columns[x].options && obj.options.columns[x].options.type) { options.type = obj.options.columns[x].options.type; } jSuites.dropdown(editor, options); } else if (obj.options.columns[x].type == 'calendar' || obj.options.columns[x].type == 'color') { // Value var value = obj.options.data[y][x]; // Create editor var editor = createEditor('input'); editor.value = value; if (obj.options.tableOverflow == true || obj.options.fullscreen == true) { obj.options.columns[x].options.position = true; } obj.options.columns[x].options.value = obj.options.data[y][x]; obj.options.columns[x].options.opened = true; obj.options.columns[x].options.onclose = function(el, value) { obj.closeEditor(cell, true); } // Current value if (obj.options.columns[x].type == 'color') { jSuites.color(editor, obj.options.columns[x].options); } else { jSuites.calendar(editor, obj.options.columns[x].options); } // Focus on editor editor.focus(); } else if (obj.options.columns[x].type == 'html') { var value = obj.options.data[y][x]; // Create editor var editor = createEditor('div'); editor.style.position = 'relative'; var div = document.createElement('div'); div.classList.add('jexcel_richtext'); editor.appendChild(div); jSuites.editor(div, { focus: true, value: value, }); var rect = cell.getBoundingClientRect(); var rectContent = div.getBoundingClientRect(); if (window.innerHeight < rect.bottom + rectContent.height) { div.style.top = (rect.top - (rectContent.height + 2)) + 'px'; } else { div.style.top = (rect.top) + 'px'; } } else if (obj.options.columns[x].type == 'image') { // Value var img = cell.children[0]; // Create editor var editor = createEditor('div'); editor.style.position = 'relative'; var div = document.createElement('div'); div.classList.add('jclose'); if (img && img.src) { div.appendChild(img); } editor.appendChild(div); jSuites.image(div, obj.options.imageOptions); var rect = cell.getBoundingClientRect(); var rectContent = div.getBoundingClientRect(); if (window.innerHeight < rect.bottom + rectContent.height) { div.style.top = (rect.top - (rectContent.height + 2)) + 'px'; } else { div.style.top = (rect.top) + 'px'; } } else { // TablePress: Show all lines of text in an edited cell, and show at least that number of lines in other cells of the row. cell.classList.add('editing'); var cell_height = cell.getBoundingClientRect().height; obj.rows[y].style.setProperty( '--table-editor-line-clamp-editing', Math.floor( (cell_height-10) / 14 ) ); obj.updateCornerPosition(); // Value var value = empty == true ? '' : obj.options.data[y][x]; // Basic editor if (obj.options.columns[x].wordWrap != false && (obj.options.wordWrap == true || obj.options.columns[x].wordWrap == true)) { var editor = createEditor('textarea'); } else { var editor = createEditor('input'); } editor.focus(); editor.value = value; // Column options var options = obj.options.columns[x]; // Format var opt = null; // Apply format when is not a formula if (! isFormula(value)) { // Format if (opt = getMask(options)) { // Masking if (! options.disabledMaskOnEdition) { if (options.mask) { var m = options.mask.split(';') editor.setAttribute('data-mask', m[0]); } else if (options.locale) { editor.setAttribute('data-locale', options.locale); } } // Input opt.input = editor; // Configuration editor.mask = opt; // Do not treat the decimals jSuites.mask.render(value, opt, false); } } editor.onblur = function() { obj.closeEditor(cell, true); }; editor.scrollLeft = editor.scrollWidth; } } } } /** * Close the editor and save the information * * @param object cell * @param boolean save * @return void */ obj.closeEditor = function(cell, save) { var x = parseInt(cell.getAttribute('data-x')); var y = parseInt(cell.getAttribute('data-y')); // TablePress: Reset cell/row heights to previous values. cell.classList.remove('editing'); obj.rows[y].style.removeProperty( '--table-editor-line-clamp-editing' ); obj.updateCornerPosition(); // Get cell properties if (save == true) { // If custom editor if (obj.options.columns[x].editor) { // Custom editor var value = obj.options.columns[x].editor.closeEditor(cell, save); } else { // Native functions if (obj.options.columns[x].type == 'checkbox' || obj.options.columns[x].type == 'radio' || obj.options.columns[x].type == 'hidden') { // Do nothing } else if (obj.options.columns[x].type == 'dropdown' || obj.options.columns[x].type == 'autocomplete') { var value = cell.children[0].dropdown.close(true); } else if (obj.options.columns[x].type == 'calendar') { var value = cell.children[0].calendar.close(true); } else if (obj.options.columns[x].type == 'color') { var value = cell.children[0].color.close(true); } else if (obj.options.columns[x].type == 'html') { var value = cell.children[0].children[0].editor.getData(); } else if (obj.options.columns[x].type == 'image') { var img = cell.children[0].children[0].children[0]; var value = img && img.tagName == 'IMG' ? img.src : ''; } else if (obj.options.columns[x].type == 'numeric') { var value = cell.children[0].value; if ((''+value).substr(0,1) != '=') { if (value == '') { value = obj.options.columns[x].allowEmpty ? '' : 0; } } cell.children[0].onblur = null; } else { var value = cell.children[0].value; cell.children[0].onblur = null; // Column options var options = obj.options.columns[x]; // Format var opt = null; if (opt = getMask(options)) { // Keep numeric in the raw data if (value !== '' && ! isFormula(value) && typeof(value) !== 'number') { var t = jSuites.mask.extract(value, opt, true); if (t && t.value !== '') { value = t.value; } } } } } // Ignore changes if the value is the same if (obj.options.data[y][x] == value) { cell.innerHTML = obj.edition[1]; } else { obj.setValue(cell, value); } } else { if (obj.options.columns[x].editor) { // Custom editor obj.options.columns[x].editor.closeEditor(cell, save); } else { if (obj.options.columns[x].type == 'dropdown' || obj.options.columns[x].type == 'autocomplete') { cell.children[0].dropdown.close(true); } else if (obj.options.columns[x].type == 'calendar') { cell.children[0].calendar.close(true); } else if (obj.options.columns[x].type == 'color') { cell.children[0].color.close(true); } else { cell.children[0].onblur = null; } } // Restore value cell.innerHTML = obj.edition && obj.edition[1] ? obj.edition[1] : ''; } // On edition end obj.dispatch('oneditionend', el, cell, x, y, value, save); // Remove editor class cell.classList.remove('editor'); // Finish edition obj.edition = null; } /** * Get the cell object * * @param object cell * @return string value */ obj.getCell = function(cell) { // Convert in case name is excel liked ex. A10, BB92 cell = jexcel.getIdFromColumnName(cell, true); var x = cell[0]; var y = cell[1]; return obj.records[y][x]; } /** * Get the column options * @param x * @param y * @returns {{type: string}} */ obj.getColumnOptions = function(x, y) { // Type var options = obj.options.columns[x]; // Cell type if (! options) { options = { type: 'text' }; } return options; } /** * Get the cell object from coords * * @param object cell * @return string value */ obj.getCellFromCoords = function(x, y) { return obj.records[y][x]; } /** * Get label * * @param object cell * @return string value */ obj.getLabel = function(cell) { // Convert in case name is excel liked ex. A10, BB92 cell = jexcel.getIdFromColumnName(cell, true); var x = cell[0]; var y = cell[1]; return obj.records[y][x].innerHTML; } /** * Get labelfrom coords * * @param object cell * @return string value */ obj.getLabelFromCoords = function(x, y) { return obj.records[y][x].innerHTML; } /** * Get the value from a cell * * @param object cell * @return string value */ obj.getValue = function(cell, processedValue) { if (typeof(cell) == 'object') { var x = cell.getAttribute('data-x'); var y = cell.getAttribute('data-y'); } else { cell = jexcel.getIdFromColumnName(cell, true); var x = cell[0]; var y = cell[1]; } var value = null; if (x != null && y != null) { if (obj.records[y] && obj.records[y][x] && (processedValue || obj.options.copyCompatibility == true)) { value = obj.records[y][x].innerHTML; } else { if (obj.options.data[y] && obj.options.data[y][x] != 'undefined') { value = obj.options.data[y][x]; } } } return value; } /** * Get the value from a coords * * @param int x * @param int y * @return string value */ obj.getValueFromCoords = function(x, y, processedValue) { var value = null; if (x != null && y != null) { if ((obj.records[y] && obj.records[y][x]) && processedValue || obj.options.copyCompatibility == true) { value = obj.records[y][x].innerHTML; } else { if (obj.options.data[y] && obj.options.data[y][x] != 'undefined') { value = obj.options.data[y][x]; } } } return value; } /** * Set a cell value * * @param mixed cell destination cell * @param string value value * @return void */ obj.setValue = function(cell, value, force) { var records = []; if (typeof(cell) == 'string') { var columnId = jexcel.getIdFromColumnName(cell, true); var x = columnId[0]; var y = columnId[1]; // Update cell records.push(obj.updateCell(x, y, value, force)); // Update all formulas in the chain obj.updateFormulaChain(x, y, records); } else { var x = null; var y = null; if (cell && cell.getAttribute) { var x = cell.getAttribute('data-x'); var y = cell.getAttribute('data-y'); } // Update cell if (x != null && y != null) { records.push(obj.updateCell(x, y, value, force)); // Update all formulas in the chain obj.updateFormulaChain(x, y, records); } else { var keys = Object.keys(cell); if (keys.length > 0) { for (var i = 0; i < keys.length; i++) { if (typeof(cell[i]) == 'string') { var columnId = jexcel.getIdFromColumnName(cell[i], true); var x = columnId[0]; var y = columnId[1]; } else { if (cell[i].x != null && cell[i].y != null) { var x = cell[i].x; var y = cell[i].y; // Flexible setup if (cell[i].newValue != null) { value = cell[i].newValue; } else if (cell[i].value != null) { value = cell[i].value; } } else { var x = cell[i].getAttribute('data-x'); var y = cell[i].getAttribute('data-y'); } } // Update cell if (x != null && y != null) { records.push(obj.updateCell(x, y, value, force)); // Update all formulas in the chain obj.updateFormulaChain(x, y, records); } } } } } // Update history obj.setHistory({ action:'setValue', records:records, selection:obj.selectedCell, }); // Update table with custom configurations if applicable obj.updateTable(); // On after changes obj.onafterchanges(el, records); } /** * Set a cell value based on coordinates * * @param int x destination cell * @param int y destination cell * @param string value * @return void */ obj.setValueFromCoords = function(x, y, value, force) { var records = []; records.push(obj.updateCell(x, y, value, force)); // Update all formulas in the chain obj.updateFormulaChain(x, y, records); // Update history obj.setHistory({ action:'setValue', records:records, selection:obj.selectedCell, }); // Update table with custom configurations if applicable obj.updateTable(); // On after changes obj.onafterchanges(el, records); } /** * Toogle */ obj.setCheckRadioValue = function() { var records = []; var keys = Object.keys(obj.highlighted); for (var i = 0; i < keys.length; i++) { var x = obj.highlighted[i].getAttribute('data-x'); var y = obj.highlighted[i].getAttribute('data-y'); if (obj.options.columns[x].type == 'checkbox' || obj.options.columns[x].type == 'radio') { // Update cell records.push(obj.updateCell(x, y, ! obj.options.data[y][x])); } } if (records.length) { // Update history obj.setHistory({ action:'setValue', records:records, selection:obj.selectedCell, }); // On after changes obj.onafterchanges(el, records); } } /** * Strip tags */ var stripScript = function(a) { var b = new Option; b.innerHTML = a; var c = null; for (a = b.getElementsByTagName('script'); c=a[0];) c.parentNode.removeChild(c); return b.innerHTML; } /** * Update cell content * * @param object cell * @return void */ obj.updateCell = function(x, y, value, force) { // Changing value depending on the column type if (obj.records[y][x].classList.contains('readonly') == true && ! force) { // Do nothing var record = { x: x, y: y, col: x, row: y } } else { // Security if ((''+value).substr(0,1) == '=' && obj.options.secureFormulas == true) { var val = secureFormula(value); if (val != value) { // Update the data container value = val; } } // On change var val = obj.dispatch('onbeforechange', el, obj.records[y][x], x, y, value); // If you return something this will overwrite the value if (val != undefined) { value = val; } if (obj.options.columns[x].editor && typeof(obj.options.columns[x].editor.updateCell) == 'function') { value = obj.options.columns[x].editor.updateCell(obj.records[y][x], value, force); } // History format var record = { x: x, y: y, col: x, row: y, newValue: value, oldValue: obj.options.data[y][x], } let editor = obj.options.columns[x].editor; if (editor) { // Update data and cell obj.options.data[y][x] = value; if (typeof(editor.setValue) === 'function') { editor.setValue(obj.records[y][x], value); } } else { // Native functions if (obj.options.columns[x].type == 'checkbox' || obj.options.columns[x].type == 'radio') { // Unchecked all options if (obj.options.columns[x].type == 'radio') { for (var j = 0; j < obj.options.data.length; j++) { obj.options.data[j][x] = false; } } // Update data and cell obj.records[y][x].children[0].checked = (value == 1 || value == true || value == 'true' || value == 'TRUE') ? true : false; obj.options.data[y][x] = obj.records[y][x].children[0].checked; } else if (obj.options.columns[x].type == 'dropdown' || obj.options.columns[x].type == 'autocomplete') { // Update data and cell obj.options.data[y][x] = value; obj.records[y][x].textContent = obj.getDropDownValue(x, value); } else if (obj.options.columns[x].type == 'calendar') { // Try formatted date var formatted = null; if (! validDate(value)) { var tmp = jSuites.calendar.extractDateFromString(value, obj.options.columns[x].options.format); if (tmp) { formatted = tmp; } } // Update data and cell obj.options.data[y][x] = value; obj.records[y][x].textContent = jSuites.calendar.getDateString(formatted ? formatted : value, obj.options.columns[x].options.format); } else if (obj.options.columns[x].type == 'color') { // Update color obj.options.data[y][x] = value; // Render if (obj.options.columns[x].render == 'square') { var color = document.createElement('div'); color.className = 'color'; color.style.backgroundColor = value; obj.records[y][x].textContent = ''; obj.records[y][x].appendChild(color); } else { obj.records[y][x].style.color = value; obj.records[y][x].textContent = value; } } else if (obj.options.columns[x].type == 'image') { value = ''+value; obj.options.data[y][x] = value; obj.records[y][x].innerHTML = ''; if (value && value.substr(0, 10) == 'data:image') { var img = document.createElement('img'); img.src = value; obj.records[y][x].appendChild(img); } } else { // Update data and cell obj.options.data[y][x] = value; // Label if (obj.options.columns[x].type == 'html') { obj.records[y][x].innerHTML = stripScript(obj.parseValue(x, y, value)); } else { if (obj.options.stripHTML === false || obj.options.columns[x].stripHTML === false) { obj.records[y][x].innerHTML = stripScript(obj.parseValue(x, y, value, obj.records[y][x])); } else { // TablePress: Add a div container inside each cell. // obj.records[y][x].textContent = obj.parseValue(x, y, value, obj.records[y][x]); var div = document.createElement('div'); div.textContent = obj.parseValue(x, y, value, obj.records[y][x]); obj.records[y][x].textContent = ''; obj.records[y][x].appendChild(div); } } // Handle big text inside a cell if (obj.options.columns[x].wordWrap != false && (obj.options.wordWrap == true || obj.options.columns[x].wordWrap == true || obj.records[y][x].innerHTML.length > 200)) { obj.records[y][x].style.whiteSpace = 'pre-wrap'; } else { obj.records[y][x].style.whiteSpace = ''; } } } // Overflow if (x > 0) { if (value) { obj.records[y][x-1].style.overflow = 'hidden'; } else { obj.records[y][x-1].style.overflow = ''; } } // On change obj.dispatch('onchange', el, (obj.records[y] && obj.records[y][x] ? obj.records[y][x] : null), x, y, value, record.oldValue); } return record; } /** * Helper function to copy data using the corner icon */ obj.copyData = function(o, d) { // Get data from all selected cells var data = obj.getData(true, true); // Selected cells var h = obj.selectedContainer; // Cells var x1 = parseInt(o.getAttribute('data-x')); var y1 = parseInt(o.getAttribute('data-y')); var x2 = parseInt(d.getAttribute('data-x')); var y2 = parseInt(d.getAttribute('data-y')); // Records var records = []; var breakControl = false; if (h[0] == x1) { // Vertical copy if (y1 < h[1]) { var rowNumber = y1 - h[1]; } else { var rowNumber = 1; } var colNumber = 0; } else { if (x1 < h[0]) { var colNumber = x1 - h[0]; } else { var colNumber = 1; } var rowNumber = 0; } // Copy data procedure var posx = 0; var posy = 0; for (var j = y1; j <= y2; j++) { // Skip hidden rows if (obj.rows[j] && obj.rows[j].style.display == 'none') { continue; } // Controls if (data[posy] == undefined) { posy = 0; } posx = 0; // Data columns if (h[0] != x1) { if (x1 < h[0]) { var colNumber = x1 - h[0]; } else { var colNumber = 1; } } // Data columns for (var i = x1; i <= x2; i++) { // Update non-readonly if (obj.records[j][i] && ! obj.records[j][i].classList.contains('readonly') && obj.records[j][i].style.display != 'none' && breakControl == false) { // Stop if contains value if (! obj.selection.length) { if (obj.options.data[j][i] != '') { breakControl = true; continue; } } // Column if (data[posy] == undefined) { posx = 0; } else if (data[posy][posx] == undefined) { posx = 0; } // Value var value = data[posy][posx]; if (value && ! data[1] && obj.options.autoIncrement == true) { if (obj.options.columns[i].type == 'text' || obj.options.columns[i].type == 'number') { if ((''+value).substr(0,1) == '=') { var tokens = value.match(/([A-Z]+[0-9]+)/g); if (tokens) { var affectedTokens = []; for (var index = 0; index < tokens.length; index++) { var position = jexcel.getIdFromColumnName(tokens[index], 1); position[0] += colNumber; position[1] += rowNumber; if (position[1] < 0) { position[1] = 0; } var token = jexcel.getColumnNameFromId([position[0], position[1]]); if (token != tokens[index]) { affectedTokens[tokens[index]] = token; } } // Update formula if (affectedTokens) { value = obj.updateFormula(value, affectedTokens) } } } else { if (value == Number(value)) { value = Number(value) + rowNumber; } } } else if (obj.options.columns[i].type == 'calendar') { var date = new Date(value); date.setDate(date.getDate() + rowNumber); value = date.getFullYear() + '-' + jexcel.doubleDigitFormat(parseInt(date.getMonth() + 1)) + '-' + jexcel.doubleDigitFormat(date.getDate()) + ' ' + '00:00:00'; } } records.push(obj.updateCell(i, j, value)); // Update all formulas in the chain obj.updateFormulaChain(i, j, records); } posx++; if (h[0] != x1) { colNumber++; } } posy++; rowNumber++; } // Update history obj.setHistory({ action:'setValue', records:records, selection:obj.selectedCell, }); // Update table with custom configuration if applicable obj.updateTable(); // On after changes obj.onafterchanges(el, records); } /** * Refresh current selection */ obj.refreshSelection = function() { if (obj.selectedCell) { obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } } /** * Move coords to A1 in case overlaps with an excluded cell */ obj.conditionalSelectionUpdate = function(type, o, d) { if (type == 1) { if (obj.selectedCell && ((o >= obj.selectedCell[1] && o <= obj.selectedCell[3]) || (d >= obj.selectedCell[1] && d <= obj.selectedCell[3]))) { obj.resetSelection(); return; } } else { if (obj.selectedCell && ((o >= obj.selectedCell[0] && o <= obj.selectedCell[2]) || (d >= obj.selectedCell[0] && d <= obj.selectedCell[2]))) { obj.resetSelection(); return; } } } /** * Clear table selection */ obj.resetSelection = function(blur) { // Remove style if (! obj.highlighted.length) { var previousStatus = 0; } else { var previousStatus = 1; for (var i = 0; i < obj.highlighted.length; i++) { obj.highlighted[i].classList.remove('highlight'); obj.highlighted[i].classList.remove('highlight-left'); obj.highlighted[i].classList.remove('highlight-right'); obj.highlighted[i].classList.remove('highlight-top'); obj.highlighted[i].classList.remove('highlight-bottom'); obj.highlighted[i].classList.remove('highlight-selected'); var px = parseInt(obj.highlighted[i].getAttribute('data-x')); var py = parseInt(obj.highlighted[i].getAttribute('data-y')); // Check for merged cells if (obj.highlighted[i].getAttribute('data-merged')) { var colspan = parseInt(obj.highlighted[i].getAttribute('colspan')); var rowspan = parseInt(obj.highlighted[i].getAttribute('rowspan')); var ux = colspan > 0 ? px + (colspan - 1) : px; var uy = rowspan > 0 ? py + (rowspan - 1): py; } else { var ux = px; var uy = py; } // Remove selected from headers for (var j = px; j <= ux; j++) { if (obj.headers[j]) { obj.headers[j].classList.remove('selected'); } } // Remove selected from rows for (var j = py; j <= uy; j++) { if (obj.rows[j]) { obj.rows[j].classList.remove('selected'); } } } } // Reset highlighted cells obj.highlighted = []; // Reset obj.selectedCell = null; // Hide corner obj.corner.style.top = '-2000px'; obj.corner.style.left = '-2000px'; if (blur == true && previousStatus == 1) { obj.dispatch('onblur', el); } return previousStatus; } /** * Update selection based on two cells */ obj.updateSelection = function(el1, el2, origin) { var x1 = el1.getAttribute('data-x'); var y1 = el1.getAttribute('data-y'); if (el2) { var x2 = el2.getAttribute('data-x'); var y2 = el2.getAttribute('data-y'); } else { var x2 = x1; var y2 = y1; } obj.updateSelectionFromCoords(x1, y1, x2, y2, origin); } /** * Update selection from coords */ obj.updateSelectionFromCoords = function(x1, y1, x2, y2, origin) { // Reset Selection var updated = null; var previousState = obj.resetSelection(); // select column if (y1 == null) { y1 = 0; y2 = obj.rows.length - 1; } // Same element if (x2 == null) { x2 = x1; } if (y2 == null) { y2 = y1; } // Selection must be within the existing data if (x1 >= obj.headers.length) { x1 = obj.headers.length - 1; } if (y1 >= obj.rows.length) { y1 = obj.rows.length - 1; } if (x2 >= obj.headers.length) { x2 = obj.headers.length - 1; } if (y2 >= obj.rows.length) { y2 = obj.rows.length - 1; } // Keep selected cell obj.selectedCell = [x1, y1, x2, y2]; // Select cells if (x1 != null) { // Add selected cell if (obj.records[y1][x1]) { obj.records[y1][x1].classList.add('highlight-selected'); } // Origin & Destination if (parseInt(x1) < parseInt(x2)) { var px = parseInt(x1); var ux = parseInt(x2); } else { var px = parseInt(x2); var ux = parseInt(x1); } if (parseInt(y1) < parseInt(y2)) { var py = parseInt(y1); var uy = parseInt(y2); } else { var py = parseInt(y2); var uy = parseInt(y1); } // Verify merged columns for (var i = px; i <= ux; i++) { for (var j = py; j <= uy; j++) { if (obj.records[j][i] && obj.records[j][i].getAttribute('data-merged')) { var x = parseInt(obj.records[j][i].getAttribute('data-x')); var y = parseInt(obj.records[j][i].getAttribute('data-y')); var colspan = parseInt(obj.records[j][i].getAttribute('colspan')); var rowspan = parseInt(obj.records[j][i].getAttribute('rowspan')); if (colspan > 1) { if (x < px) { px = x; } if (x + colspan > ux) { ux = x + colspan - 1; } } if (rowspan) { if (y < py) { py = y; } if (y + rowspan > uy) { uy = y + rowspan - 1; } } } } } // Limits var borderLeft = null; var borderRight = null; var borderTop = null; var borderBottom = null; // Vertical limits for (var j = py; j <= uy; j++) { if (obj.rows[j].style.display != 'none') { if (borderTop == null) { borderTop = j; } borderBottom = j; } } // Redefining styles for (var i = px; i <= ux; i++) { for (var j = py; j <= uy; j++) { if (obj.rows[j].style.display != 'none' && obj.records[j][i].style.display != 'none') { obj.records[j][i].classList.add('highlight'); obj.highlighted.push(obj.records[j][i]); } } // Horizontal limits if (obj.options.columns[i].type != 'hidden') { if (borderLeft == null) { borderLeft = i; } borderRight = i; } } // Create borders if (! borderLeft) { borderLeft = 0; } if (! borderRight) { borderRight = 0; } for (var i = borderLeft; i <= borderRight; i++) { if (obj.options.columns[i].type != 'hidden' && obj.colgroup[i].style && obj.colgroup[i].style.display != 'none') { // Top border if (obj.records[borderTop] && obj.records[borderTop][i]) { obj.records[borderTop][i].classList.add('highlight-top'); } // Bottom border if (obj.records[borderBottom] && obj.records[borderBottom][i]) { obj.records[borderBottom][i].classList.add('highlight-bottom'); } // Add selected from headers obj.headers[i].classList.add('selected'); } } for (var j = borderTop; j <= borderBottom; j++) { if (obj.rows[j] && obj.rows[j].style.display != 'none') { // Left border obj.records[j][borderLeft].classList.add('highlight-left'); // Right border obj.records[j][borderRight].classList.add('highlight-right'); // Add selected from rows obj.rows[j].classList.add('selected'); } } obj.selectedContainer = [ borderLeft, borderTop, borderRight, borderBottom ]; } // Handle events if (previousState == 0) { obj.dispatch('onfocus', el); obj.removeCopyingSelection(); } obj.dispatch('onselection', el, borderLeft, borderTop, borderRight, borderBottom, origin); // Find corner cell obj.updateCornerPosition(); } /** * Remove copy selection * * @return void */ obj.removeCopySelection = function() { // Remove current selection for (var i = 0; i < obj.selection.length; i++) { obj.selection[i].classList.remove('selection'); obj.selection[i].classList.remove('selection-left'); obj.selection[i].classList.remove('selection-right'); obj.selection[i].classList.remove('selection-top'); obj.selection[i].classList.remove('selection-bottom'); } obj.selection = []; } /** * Update copy selection * * @param int x, y * @return void */ obj.updateCopySelection = function(x3, y3) { // Remove selection obj.removeCopySelection(); // Get elements first and last var x1 = obj.selectedContainer[0]; var y1 = obj.selectedContainer[1]; var x2 = obj.selectedContainer[2]; var y2 = obj.selectedContainer[3]; if (x3 != null && y3 != null) { if (x3 - x2 > 0) { var px = parseInt(x2) + 1; var ux = parseInt(x3); } else { var px = parseInt(x3); var ux = parseInt(x1) - 1; } if (y3 - y2 > 0) { var py = parseInt(y2) + 1; var uy = parseInt(y3); } else { var py = parseInt(y3); var uy = parseInt(y1) - 1; } if (ux - px <= uy - py) { var px = parseInt(x1); var ux = parseInt(x2); } else { var py = parseInt(y1); var uy = parseInt(y2); } for (var j = py; j <= uy; j++) { for (var i = px; i <= ux; i++) { if (obj.records[j][i] && obj.rows[j].style.display != 'none' && obj.records[j][i].style.display != 'none') { obj.records[j][i].classList.add('selection'); obj.records[py][i].classList.add('selection-top'); obj.records[uy][i].classList.add('selection-bottom'); obj.records[j][px].classList.add('selection-left'); obj.records[j][ux].classList.add('selection-right'); // Persist selected elements obj.selection.push(obj.records[j][i]); } } } } } /** * Update corner position * * @return void */ obj.updateCornerPosition = function() { // If any selected cells if (! obj.highlighted.length) { obj.corner.style.top = '-2000px'; obj.corner.style.left = '-2000px'; } else { // Get last cell var last = obj.highlighted[obj.highlighted.length-1]; var lastX = last.getAttribute('data-x'); var contentRect = obj.content.getBoundingClientRect(); var x1 = contentRect.left; var y1 = contentRect.top; var lastRect = last.getBoundingClientRect(); var x2 = lastRect.left; var y2 = lastRect.top; var w2 = lastRect.width; var h2 = lastRect.height; var x = (x2 - x1) + obj.content.scrollLeft + w2 - 4; var y = (y2 - y1) + obj.content.scrollTop + h2 - 4; // Place the corner in the correct place obj.corner.style.top = y + 'px'; obj.corner.style.left = x + 'px'; if (obj.options.freezeColumns) { var width = obj.getFreezeWidth(); // Only check if the last column is not part of the merged cells if (lastX > obj.options.freezeColumns-1 && x2 - x1 + w2 < width) { obj.corner.style.display = 'none'; } else { if (obj.options.selectionCopy == true) { obj.corner.style.display = ''; } } } else { if (obj.options.selectionCopy == true) { obj.corner.style.display = ''; } } } } /** * Update scroll position based on the selection */ obj.updateScroll = function(direction) { // Jspreadsheet Container information var contentRect = obj.content.getBoundingClientRect(); var x1 = contentRect.left; var y1 = contentRect.top; var w1 = contentRect.width; var h1 = contentRect.height; // Direction Left or Up var reference = obj.records[obj.selectedCell[3]][obj.selectedCell[2]]; // Reference var referenceRect = reference.getBoundingClientRect(); var x2 = referenceRect.left; var y2 = referenceRect.top; var w2 = referenceRect.width; var h2 = referenceRect.height; // Direction if (direction == 0 || direction == 1) { var x = (x2 - x1) + obj.content.scrollLeft; var y = (y2 - y1) + obj.content.scrollTop - 2; } else { var x = (x2 - x1) + obj.content.scrollLeft + w2; var y = (y2 - y1) + obj.content.scrollTop + h2; } // Top position check if (y > (obj.content.scrollTop + 30) && y < (obj.content.scrollTop + h1)) { // In the viewport } else { // Out of viewport if (y < obj.content.scrollTop + 30) { obj.content.scrollTop = y - h2; } else { obj.content.scrollTop = y - (h1 - 2); } } // Freeze columns? var freezed = obj.getFreezeWidth(); // Left position check - TODO: change that to the bottom border of the element if (x > (obj.content.scrollLeft + freezed) && x < (obj.content.scrollLeft + w1)) { // In the viewport } else { // Out of viewport if (x < obj.content.scrollLeft + 30) { obj.content.scrollLeft = x; if (obj.content.scrollLeft < 50) { obj.content.scrollLeft = 0; } } else if (x < obj.content.scrollLeft + freezed) { obj.content.scrollLeft = x - freezed - 1; } else { obj.content.scrollLeft = x - (w1 - 20); } } } /** * Get the column width * * @param int column column number (first column is: 0) * @return int current width */ obj.getWidth = function(column) { if (typeof column === 'undefined') { // Get all headers var data = []; for (var i = 0; i < obj.headers.length; i++) { data.push(obj.options.columns[i].width); } } else { // In case the column is an object if (typeof(column) == 'object') { column = column.getAttribute('data-x'); } data = obj.colgroup[column].getAttribute('width') } return data; } /** * Set the column width * * @param int column number (first column is: 0) * @param int new column width * @param int old column width */ obj.setWidth = function (column, width, oldWidth) { if (width) { if (Array.isArray(column)) { // Oldwidth if (! oldWidth) { var oldWidth = []; } // Set width for (var i = 0; i < column.length; i++) { if (! oldWidth[i]) { oldWidth[i] = obj.colgroup[column[i]].getAttribute('width'); } var w = Array.isArray(width) && width[i] ? width[i] : width; obj.colgroup[column[i]].setAttribute('width', w); obj.options.columns[column[i]].width = w; } } else { // Oldwidth if (! oldWidth) { oldWidth = obj.colgroup[column].getAttribute('width'); } // Set width obj.colgroup[column].setAttribute('width', width); obj.options.columns[column].width = width; } // Keeping history of changes obj.setHistory({ action:'setWidth', column:column, oldValue:oldWidth, newValue:width, }); // On resize column obj.dispatch('onresizecolumn', el, column, width, oldWidth); // Update corner position obj.updateCornerPosition(); } } /** * Set the row height * * @param row - row number (first row is: 0) * @param height - new row height * @param oldHeight - old row height */ obj.setHeight = function (row, height, oldHeight) { if (height > 0) { // In case the column is an object if (typeof(row) == 'object') { row = row.getAttribute('data-y'); } // Oldwidth if (! oldHeight) { oldHeight = obj.rows[row].getAttribute('height'); if (! oldHeight) { var rect = obj.rows[row].getBoundingClientRect(); oldHeight = rect.height; } } // Integer height = parseInt(height); // TablePress: Add a custom CSS variable to adjust the number of shown lines of text to the row height. obj.rows[row].style.setProperty( '--table-editor-line-clamp', Math.floor( (height-10) / 14 ) ); // Set width // TablePress: Don't set the row height as a CSS property. This makes the resizing behavior lose its preview when making a row smaller. //obj.rows[row].style.height = height + 'px'; // Keep options updated if (! obj.options.rows[row]) { obj.options.rows[row] = {}; } obj.options.rows[row].height = height; // Keeping history of changes obj.setHistory({ action:'setHeight', row:row, oldValue:oldHeight, newValue:height, }); // On resize column obj.dispatch('onresizerow', el, row, height, oldHeight); // Update corner position obj.updateCornerPosition(); } } /** * Get the row height * * @param row - row number (first row is: 0) * @return height - current row height */ obj.getHeight = function(row) { if (typeof row === 'undefined') { // Get height of all rows var data = []; for (var j = 0; j < obj.rows.length; j++) { var h = obj.rows[j].style.height; if (h) { data[j] = h; } } } else { // In case the row is an object if (typeof(row) == 'object') { row = $(row).getAttribute('data-y'); } var data = obj.rows[row].style.height; } return data; } obj.setFooter = function(data) { if (data) { obj.options.footers = data; } if (obj.options.footers) { if (! obj.tfoot) { obj.tfoot = document.createElement('tfoot'); obj.table.appendChild(obj.tfoot); } for (var j = 0; j < obj.options.footers.length; j++) { if (obj.tfoot.children[j]) { var tr = obj.tfoot.children[j]; } else { var tr = document.createElement('tr'); var td = document.createElement('td'); tr.appendChild(td); obj.tfoot.appendChild(tr); } for (var i = 0; i < obj.headers.length; i++) { if (! obj.options.footers[j][i]) { obj.options.footers[j][i] = ''; } if (obj.tfoot.children[j].children[i+1]) { var td = obj.tfoot.children[j].children[i+1]; } else { var td = document.createElement('td'); tr.appendChild(td); // Text align var colAlign = obj.options.columns[i].align ? obj.options.columns[i].align : 'center'; td.style.textAlign = colAlign; } td.textContent = obj.parseValue(+obj.records.length + i, j, obj.options.footers[j][i]); // Hide/Show with hideColumn()/showColumn() td.style.display = obj.colgroup[i].style.display; } } } } /** * Get the column title * * @param column - column number (first column is: 0) * @param title - new column title */ obj.getHeader = function(column) { return obj.headers[column].textContent; } /** * Set the column title * * @param column - column number (first column is: 0) * @param title - new column title */ obj.setHeader = function(column, newValue) { if (obj.headers[column]) { var oldValue = obj.headers[column].textContent; if (! newValue) { newValue = prompt(obj.options.text.columnName, oldValue) } if (newValue) { obj.headers[column].textContent = newValue; // Keep the title property obj.headers[column].setAttribute('title', newValue); // Update title obj.options.columns[column].title = newValue; } obj.setHistory({ action: 'setHeader', column: column, oldValue: oldValue, newValue: newValue }); // On onchange header obj.dispatch('onchangeheader', el, column, oldValue, newValue); } } /** * Get the headers * * @param asArray * @return mixed */ obj.getHeaders = function (asArray) { var title = []; for (var i = 0; i < obj.headers.length; i++) { title.push(obj.getHeader(i)); } return asArray ? title : title.join(obj.options.csvDelimiter); } /** * Get meta information from cell(s) * * @return integer */ obj.getMeta = function(cell, key) { if (! cell) { return obj.options.meta; } else { if (key) { return obj.options.meta[cell] && obj.options.meta[cell][key] ? obj.options.meta[cell][key] : null; } else { return obj.options.meta[cell] ? obj.options.meta[cell] : null; } } } /** * Set meta information to cell(s) * * @return integer */ obj.setMeta = function(o, k, v) { if (! obj.options.meta) { obj.options.meta = {} } if (k && v) { // Set data value if (! obj.options.meta[o]) { obj.options.meta[o] = {}; } obj.options.meta[o][k] = v; } else { // Apply that for all cells var keys = Object.keys(o); for (var i = 0; i < keys.length; i++) { if (! obj.options.meta[keys[i]]) { obj.options.meta[keys[i]] = {}; } var prop = Object.keys(o[keys[i]]); for (var j = 0; j < prop.length; j++) { obj.options.meta[keys[i]][prop[j]] = o[keys[i]][prop[j]]; } } } obj.dispatch('onchangemeta', el, o, k, v); } /** * Update meta information * * @return integer */ obj.updateMeta = function(affectedCells) { if (obj.options.meta) { var newMeta = {}; var keys = Object.keys(obj.options.meta); for (var i = 0; i < keys.length; i++) { if (affectedCells[keys[i]]) { newMeta[affectedCells[keys[i]]] = obj.options.meta[keys[i]]; } else { newMeta[keys[i]] = obj.options.meta[keys[i]]; } } // Update meta information obj.options.meta = newMeta; } } /** * Get style information from cell(s) * * @return integer */ obj.getStyle = function(cell, key) { // Cell if (! cell) { // Control vars var data = {}; // Column and row length var x = obj.options.data[0].length; var y = obj.options.data.length; // Go through the columns to get the data for (var j = 0; j < y; j++) { for (var i = 0; i < x; i++) { // Value var v = key ? obj.records[j][i].style[key] : obj.records[j][i].getAttribute('style'); // Any meta data for this column? if (v) { // Column name var k = jexcel.getColumnNameFromId([i, j]); // Value data[k] = v; } } } return data; } else { cell = jexcel.getIdFromColumnName(cell, true); return key ? obj.records[cell[1]][cell[0]].style[key] : obj.records[cell[1]][cell[0]].getAttribute('style'); } }, obj.resetStyle = function(o, ignoreHistoryAndEvents) { var keys = Object.keys(o); for (var i = 0; i < keys.length; i++) { // Position var cell = jexcel.getIdFromColumnName(keys[i], true); if (obj.records[cell[1]] && obj.records[cell[1]][cell[0]]) { obj.records[cell[1]][cell[0]].setAttribute('style', ''); } } obj.setStyle(o, null, null, null, ignoreHistoryAndEvents); } /** * Set meta information to cell(s) * * @return integer */ obj.setStyle = function(o, k, v, force, ignoreHistoryAndEvents) { var newValue = {}; var oldValue = {}; // Apply style var applyStyle = function(cellId, key, value) { // Position var cell = jexcel.getIdFromColumnName(cellId, true); if (obj.records[cell[1]] && obj.records[cell[1]][cell[0]] && (obj.records[cell[1]][cell[0]].classList.contains('readonly')==false || force)) { // Current value var currentValue = obj.records[cell[1]][cell[0]].style[key]; // Change layout if (currentValue == value && ! force) { value = ''; obj.records[cell[1]][cell[0]].style[key] = ''; } else { obj.records[cell[1]][cell[0]].style[key] = value; } // History if (! oldValue[cellId]) { oldValue[cellId] = []; } if (! newValue[cellId]) { newValue[cellId] = []; } oldValue[cellId].push([key + ':' + currentValue]); newValue[cellId].push([key + ':' + value]); } } if (k && v) { // Get object from string if (typeof(o) == 'string') { applyStyle(o, k, v); } else { // Avoid duplications var oneApplication = []; // Apply that for all cells for (var i = 0; i < o.length; i++) { var x = o[i].getAttribute('data-x'); var y = o[i].getAttribute('data-y'); var cellName = jexcel.getColumnNameFromId([x, y]); // This happens when is a merged cell if (! oneApplication[cellName]) { applyStyle(cellName, k, v); oneApplication[cellName] = true; } } } } else { var keys = Object.keys(o); for (var i = 0; i < keys.length; i++) { var style = o[keys[i]]; if (typeof(style) == 'string') { style = style.split(';'); } for (var j = 0; j < style.length; j++) { if (typeof(style[j]) == 'string') { style[j] = style[j].split(':'); } // Apply value if (style[j][0].trim()) { applyStyle(keys[i], style[j][0].trim(), style[j][1]); } } } } var keys = Object.keys(oldValue); for (var i = 0; i < keys.length; i++) { oldValue[keys[i]] = oldValue[keys[i]].join(';'); } var keys = Object.keys(newValue); for (var i = 0; i < keys.length; i++) { newValue[keys[i]] = newValue[keys[i]].join(';'); } if (! ignoreHistoryAndEvents) { // Keeping history of changes obj.setHistory({ action: 'setStyle', oldValue: oldValue, newValue: newValue, }); } obj.dispatch('onchangestyle', el, o, k, v); } /** * Get cell comments, null cell for all */ obj.getComments = function(cell, withAuthor) { if (cell) { if (typeof(cell) == 'string') { var cell = jexcel.getIdFromColumnName(cell, true); } if (withAuthor) { return [obj.records[cell[1]][cell[0]].getAttribute('title'), obj.records[cell[1]][cell[0]].getAttribute('author')]; } else { return obj.records[cell[1]][cell[0]].getAttribute('title') || ''; } } else { var data = {}; for (var j = 0; j < obj.options.data.length; j++) { for (var i = 0; i < obj.options.columns.length; i++) { var comments = obj.records[j][i].getAttribute('title'); if (comments) { var cell = jexcel.getColumnNameFromId([i, j]); data[cell] = comments; } } } return data; } } /** * Set cell comments */ obj.setComments = function(cellId, comments, author) { if (typeof(cellId) == 'string') { var cell = jexcel.getIdFromColumnName(cellId, true); } else { var cell = cellId; } // Keep old value var title = obj.records[cell[1]][cell[0]].getAttribute('title'); var author = obj.records[cell[1]][cell[0]].getAttribute('data-author'); var oldValue = [ title, author ]; // Set new values obj.records[cell[1]][cell[0]].setAttribute('title', comments ? comments : ''); obj.records[cell[1]][cell[0]].setAttribute('data-author', author ? author : ''); // Remove class if there is no comment if (comments) { obj.records[cell[1]][cell[0]].classList.add('jexcel_comments'); } else { obj.records[cell[1]][cell[0]].classList.remove('jexcel_comments'); } // Save history obj.setHistory({ action:'setComments', column: cellId, newValue: [ comments, author ], oldValue: oldValue, }); // Set comments obj.dispatch('oncomments', el, comments, title, cell, cell[0], cell[1]); } /** * Get table config information */ obj.getConfig = function() { var options = obj.options; options.style = obj.getStyle(); options.mergeCells = obj.getMerge(); options.comments = obj.getComments(); return options; } /** * Sort data and reload table */ obj.orderBy = function(column, order) { if (column >= 0) { // Merged cells if (Object.keys(obj.options.mergeCells).length > 0) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { // Remove merged cells obj.destroyMerged(); } } // Direction if (order == null) { order = obj.headers[column].classList.contains('arrow-down') ? 1 : 0; } else { order = order ? 1 : 0; } // Test order var temp = []; if (obj.options.columns[column].type == 'number' || obj.options.columns[column].type == 'numeric' || obj.options.columns[column].type == 'percentage' || obj.options.columns[column].type == 'autonumber' || obj.options.columns[column].type == 'color') { for (var j = 0; j < obj.options.data.length; j++) { temp[j] = [ j, Number(obj.options.data[j][column]) ]; } } else if (obj.options.columns[column].type == 'calendar' || obj.options.columns[column].type == 'checkbox' || obj.options.columns[column].type == 'radio') { for (var j = 0; j < obj.options.data.length; j++) { temp[j] = [ j, obj.options.data[j][column] ]; } } else { for (var j = 0; j < obj.options.data.length; j++) { temp[j] = [ j, obj.records[j][column].textContent.toLowerCase() ]; } } // Default sorting method if (typeof(obj.options.sorting) !== 'function') { obj.options.sorting = function(direction) { return function(a, b) { var valueA = a[1]; var valueB = b[1]; if (! direction) { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? 1 : (valueA < valueB) ? -1 : 0; } else { return (valueA === '' && valueB !== '') ? 1 : (valueA !== '' && valueB === '') ? -1 : (valueA > valueB) ? -1 : (valueA < valueB) ? 1 : 0; } } } } temp = temp.sort(obj.options.sorting(order)); // Save history var newValue = []; for (var j = 0; j < temp.length; j++) { newValue[j] = temp[j][0]; } // TablePress: Keep Table Head and Table Foot row at the top/bottom, if used. if ( window?.tp?.table?.options?.table_head > 0 ) { const last_head_row_idx = window?.tp?.table?.options?.table_head - 1; newValue = newValue.filter( ( row_idx ) => ( row_idx > last_head_row_idx ) ); newValue.unshift( ...Array( window?.tp?.table?.options?.table_head ).keys() ); } if ( window?.tp?.table?.options?.table_foot > 0) { const last_row_idx = newValue.length - 1; const first_foot_row_idx = newValue.length - window?.tp?.table?.options?.table_foot; newValue = newValue.filter( ( row_idx ) => ( row_idx < first_foot_row_idx ) ); for ( let idx = first_foot_row_idx; idx <= last_row_idx; idx++ ) { newValue.push( idx ); } } // Save history obj.setHistory({ action: 'orderBy', rows: newValue, column: column, order: order, }); // Update order obj.updateOrderArrow(column, order); obj.updateOrder(newValue); // On sort event obj.dispatch('onsort', el, column, order); return true; } } /** * Update order arrow */ obj.updateOrderArrow = function(column, order) { // Remove order for (var i = 0; i < obj.headers.length; i++) { obj.headers[i].classList.remove('arrow-up'); obj.headers[i].classList.remove('arrow-down'); } // No order specified then toggle order if (order) { obj.headers[column].classList.add('arrow-up'); } else { obj.headers[column].classList.add('arrow-down'); } } /** * Update rows position */ obj.updateOrder = function(rows) { // History var data = [] for (var j = 0; j < rows.length; j++) { data[j] = obj.options.data[rows[j]]; } obj.options.data = data; var data = [] for (var j = 0; j < rows.length; j++) { data[j] = obj.records[rows[j]]; } obj.records = data; var data = [] for (var j = 0; j < rows.length; j++) { data[j] = obj.rows[rows[j]]; } obj.rows = data; // Update references obj.updateTableReferences(); // Redo search if (obj.results && obj.results.length) { if (obj.searchInput.value) { obj.search(obj.searchInput.value); } else { obj.closeFilter(); } } else { // Create page obj.results = null; obj.pageNumber = 0; if (obj.options.pagination > 0) { obj.page(0); } else if (obj.options.lazyLoading == true) { obj.loadPage(0); } else { for (var j = 0; j < obj.rows.length; j++) { obj.tbody.appendChild(obj.rows[j]); } } } } /** * Move row * * @return void */ obj.moveRow = function(o, d, ignoreDom) { if (Object.keys(obj.options.mergeCells).length > 0) { if (o > d) { var insertBefore = 1; } else { var insertBefore = 0; } if (obj.isRowMerged(o).length || obj.isRowMerged(d, insertBefore).length) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } } if (obj.options.search == true) { if (obj.results && obj.results.length != obj.rows.length) { if (confirm(obj.options.text.thisActionWillClearYourSearchResultsAreYouSure)) { obj.resetSearch(); } else { return false; } } obj.results = null; } if (! ignoreDom) { if (Array.prototype.indexOf.call(obj.tbody.children, obj.rows[d]) >= 0) { if (o > d) { obj.tbody.insertBefore(obj.rows[o], obj.rows[d]); } else { obj.tbody.insertBefore(obj.rows[o], obj.rows[d].nextSibling); } } else { obj.tbody.removeChild(obj.rows[o]); } } // Place references in the correct position obj.rows.splice(d, 0, obj.rows.splice(o, 1)[0]); obj.records.splice(d, 0, obj.records.splice(o, 1)[0]); obj.options.data.splice(d, 0, obj.options.data.splice(o, 1)[0]); // Respect pagination if (obj.options.pagination > 0 && obj.tbody.children.length != obj.options.pagination) { obj.page(obj.pageNumber); } // Keeping history of changes obj.setHistory({ action:'moveRow', oldValue: o, newValue: d, }); // Update table references obj.updateTableReferences(); // Events obj.dispatch('onmoverow', el, o, d); } /** * Insert a new row * * @param mixed - number of blank lines to be insert or a single array with the data of the new row * @param rowNumber * @param insertBefore * @return void */ obj.insertRow = function(mixed, rowNumber, insertBefore) { // Configuration if (obj.options.allowInsertRow == true) { // Records var records = []; // Data to be insert var data = []; // The insert could be lead by number of rows or the array of data if (mixed > 0) { var numOfRows = mixed; } else { var numOfRows = 1; if (mixed) { data = mixed; } } // Direction var insertBefore = insertBefore ? true : false; // Current column number var lastRow = obj.options.data.length - 1; if (rowNumber == undefined || rowNumber >= parseInt(lastRow) || rowNumber < 0) { rowNumber = lastRow; } // Onbeforeinsertrow if (obj.dispatch('onbeforeinsertrow', el, rowNumber, numOfRows, insertBefore) === false) { return false; } // Merged cells if (Object.keys(obj.options.mergeCells).length > 0) { if (obj.isRowMerged(rowNumber, insertBefore).length) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } } // Clear any search if (obj.options.search == true) { if (obj.results && obj.results.length != obj.rows.length) { if (confirm(obj.options.text.thisActionWillClearYourSearchResultsAreYouSure)) { obj.resetSearch(); } else { return false; } } obj.results = null; } // Insertbefore var rowIndex = (! insertBefore) ? rowNumber + 1 : rowNumber; // Keep the current data var currentRecords = obj.records.splice(rowIndex); var currentData = obj.options.data.splice(rowIndex); var currentRows = obj.rows.splice(rowIndex); // Adding lines var rowRecords = []; var rowData = []; var rowNode = []; for (var row = rowIndex; row < (numOfRows + rowIndex); row++) { // Push data to the data container obj.options.data[row] = []; for (var col = 0; col < obj.options.columns.length; col++) { obj.options.data[row][col] = data[col] ? data[col] : ''; } // Create row var tr = obj.createRow(row, obj.options.data[row]); // Append node if (currentRows[0]) { if (Array.prototype.indexOf.call(obj.tbody.children, currentRows[0]) >= 0) { obj.tbody.insertBefore(tr, currentRows[0]); } } else { if (Array.prototype.indexOf.call(obj.tbody.children, obj.rows[rowNumber]) >= 0) { obj.tbody.appendChild(tr); } } // Record History rowRecords.push(obj.records[row]); rowData.push(obj.options.data[row]); rowNode.push(tr); } // Copy the data back to the main data Array.prototype.push.apply(obj.records, currentRecords); Array.prototype.push.apply(obj.options.data, currentData); Array.prototype.push.apply(obj.rows, currentRows); // Respect pagination if (obj.options.pagination > 0) { obj.page(obj.pageNumber); } // Keep history obj.setHistory({ action: 'insertRow', rowNumber: rowNumber, numOfRows: numOfRows, insertBefore: insertBefore, rowRecords: rowRecords, rowData: rowData, rowNode: rowNode, }); // Remove table references obj.updateTableReferences(); // Events obj.dispatch('oninsertrow', el, rowNumber, numOfRows, rowRecords, insertBefore); } } /** * Delete a row by number * * @param integer rowNumber - row number to be excluded * @param integer numOfRows - number of lines * @return void */ obj.deleteRow = function(rowNumber, numOfRows) { // Global Configuration if (obj.options.allowDeleteRow == true) { if (obj.options.allowDeletingAllRows == true || obj.options.data.length > 1) { // Delete row definitions if (rowNumber == undefined) { var number = obj.getSelectedRows(); if (! number[0]) { rowNumber = obj.options.data.length - 1; numOfRows = 1; } else { rowNumber = parseInt(number[0].getAttribute('data-y')); numOfRows = number.length; } } // Last column var lastRow = obj.options.data.length - 1; if (rowNumber == undefined || rowNumber > lastRow || rowNumber < 0) { rowNumber = lastRow; } if (! numOfRows) { numOfRows = 1; } // Do not delete more than the number of records if (rowNumber + numOfRows >= obj.options.data.length) { numOfRows = obj.options.data.length - rowNumber; } // Onbeforedeleterow if (obj.dispatch('onbeforedeleterow', el, rowNumber, numOfRows) === false) { return false; } if (parseInt(rowNumber) > -1) { // Merged cells var mergeExists = false; if (Object.keys(obj.options.mergeCells).length > 0) { for (var row = rowNumber; row < rowNumber + numOfRows; row++) { if (obj.isRowMerged(row, false).length) { mergeExists = true; } } } if (mergeExists) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } // Clear any search if (obj.options.search == true) { if (obj.results && obj.results.length != obj.rows.length) { if (confirm(obj.options.text.thisActionWillClearYourSearchResultsAreYouSure)) { obj.resetSearch(); } else { return false; } } obj.results = null; } // If delete all rows, and set allowDeletingAllRows false, will stay one row if (obj.options.allowDeletingAllRows == false && lastRow + 1 === numOfRows) { numOfRows--; console.error('Jspreadsheet: It is not possible to delete the last row'); } // Remove node for (var row = rowNumber; row < rowNumber + numOfRows; row++) { if (Array.prototype.indexOf.call(obj.tbody.children, obj.rows[row]) >= 0) { obj.rows[row].className = ''; obj.rows[row].parentNode.removeChild(obj.rows[row]); } } // Remove data var rowRecords = obj.records.splice(rowNumber, numOfRows); var rowData = obj.options.data.splice(rowNumber, numOfRows); var rowNode = obj.rows.splice(rowNumber, numOfRows); // Respect pagination if (obj.options.pagination > 0 && obj.tbody.children.length != obj.options.pagination) { obj.page(obj.pageNumber); } // Remove selection obj.conditionalSelectionUpdate(1, rowNumber, (rowNumber + numOfRows) - 1); // Keep history obj.setHistory({ action: 'deleteRow', rowNumber: rowNumber, numOfRows: numOfRows, insertBefore: 1, rowRecords: rowRecords, rowData: rowData, rowNode: rowNode }); // Remove table references obj.updateTableReferences(); // Events obj.dispatch('ondeleterow', el, rowNumber, numOfRows, rowRecords); } } else { console.error('Jspreadsheet: It is not possible to delete the last row'); } } } /** * Move column * * @return void */ obj.moveColumn = function(o, d) { if (Object.keys(obj.options.mergeCells).length > 0) { if (o > d) { var insertBefore = 1; } else { var insertBefore = 0; } if (obj.isColMerged(o).length || obj.isColMerged(d, insertBefore).length) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } } var o = parseInt(o); var d = parseInt(d); if (o > d) { obj.headerContainer.insertBefore(obj.headers[o], obj.headers[d]); obj.colgroupContainer.insertBefore(obj.colgroup[o], obj.colgroup[d]); for (var j = 0; j < obj.rows.length; j++) { obj.rows[j].insertBefore(obj.records[j][o], obj.records[j][d]); } } else { obj.headerContainer.insertBefore(obj.headers[o], obj.headers[d].nextSibling); obj.colgroupContainer.insertBefore(obj.colgroup[o], obj.colgroup[d].nextSibling); for (var j = 0; j < obj.rows.length; j++) { obj.rows[j].insertBefore(obj.records[j][o], obj.records[j][d].nextSibling); } } obj.options.columns.splice(d, 0, obj.options.columns.splice(o, 1)[0]); obj.headers.splice(d, 0, obj.headers.splice(o, 1)[0]); obj.colgroup.splice(d, 0, obj.colgroup.splice(o, 1)[0]); for (var j = 0; j < obj.rows.length; j++) { obj.options.data[j].splice(d, 0, obj.options.data[j].splice(o, 1)[0]); obj.records[j].splice(d, 0, obj.records[j].splice(o, 1)[0]); } // Update footers position if (obj.options.footers) { for (var j = 0; j < obj.options.footers.length; j++) { obj.options.footers[j].splice(d, 0, obj.options.footers[j].splice(o, 1)[0]); } } // Keeping history of changes obj.setHistory({ action:'moveColumn', oldValue: o, newValue: d, }); // Update table references obj.updateTableReferences(); // Events obj.dispatch('onmovecolumn', el, o, d); } /** * Insert a new column * * @param mixed - num of columns to be added or data to be added in one single column * @param int columnNumber - number of columns to be created * @param bool insertBefore * @param object properties - column properties * @return void */ obj.insertColumn = function(mixed, columnNumber, insertBefore, properties) { // Configuration if (obj.options.allowInsertColumn == true) { // Records var records = []; // Data to be insert var data = []; // The insert could be lead by number of rows or the array of data if (mixed > 0) { var numOfColumns = mixed; } else { var numOfColumns = 1; if (mixed) { data = mixed; } } // Direction var insertBefore = insertBefore ? true : false; // Current column number var lastColumn = obj.options.columns.length - 1; // Confirm position if (columnNumber == undefined || columnNumber >= parseInt(lastColumn) || columnNumber < 0) { columnNumber = lastColumn; } // Onbeforeinsertcolumn if (obj.dispatch('onbeforeinsertcolumn', el, columnNumber, numOfColumns, insertBefore) === false) { return false; } // Merged cells if (Object.keys(obj.options.mergeCells).length > 0) { if (obj.isColMerged(columnNumber, insertBefore).length) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } } // Create default properties if (! properties) { properties = []; } for (var i = 0; i < numOfColumns; i++) { if (! properties[i]) { properties[i] = { type:'text', source:[], options:[], width:obj.options.defaultColWidth, align:obj.options.defaultColAlign }; } } // Insert before var columnIndex = (! insertBefore) ? columnNumber + 1 : columnNumber; obj.options.columns = jexcel.injectArray(obj.options.columns, columnIndex, properties); // Open space in the containers var currentHeaders = obj.headers.splice(columnIndex); var currentColgroup = obj.colgroup.splice(columnIndex); // History var historyHeaders = []; var historyColgroup = []; var historyRecords = []; var historyData = []; var historyFooters = []; // Add new headers for (var col = columnIndex; col < (numOfColumns + columnIndex); col++) { obj.createCellHeader(col); obj.headerContainer.insertBefore(obj.headers[col], obj.headerContainer.children[col+1]); obj.colgroupContainer.insertBefore(obj.colgroup[col], obj.colgroupContainer.children[col+1]); historyHeaders.push(obj.headers[col]); historyColgroup.push(obj.colgroup[col]); } // Add new footer cells if (obj.options.footers) { for (var j = 0; j < obj.options.footers.length; j++) { historyFooters[j] = []; for (var i = 0; i < numOfColumns; i++) { historyFooters[j].push(''); } obj.options.footers[j].splice(columnIndex, 0, historyFooters[j]); } } // Adding visual columns for (var row = 0; row < obj.options.data.length; row++) { // Keep the current data var currentData = obj.options.data[row].splice(columnIndex); var currentRecord = obj.records[row].splice(columnIndex); // History historyData[row] = []; historyRecords[row] = []; for (var col = columnIndex; col < (numOfColumns + columnIndex); col++) { // New value var value = data[row] ? data[row] : ''; obj.options.data[row][col] = value; // New cell var td = obj.createCell(col, row, obj.options.data[row][col]); obj.records[row][col] = td; // Add cell to the row if (obj.rows[row]) { obj.rows[row].insertBefore(td, obj.rows[row].children[col+1]); } // Record History historyData[row].push(value); historyRecords[row].push(td); } // Copy the data back to the main data Array.prototype.push.apply(obj.options.data[row], currentData); Array.prototype.push.apply(obj.records[row], currentRecord); } Array.prototype.push.apply(obj.headers, currentHeaders); Array.prototype.push.apply(obj.colgroup, currentColgroup); // Adjust nested headers if (obj.options.nestedHeaders && obj.options.nestedHeaders.length > 0) { // Flexible way to handle nestedheaders if (obj.options.nestedHeaders[0] && obj.options.nestedHeaders[0][0]) { for (var j = 0; j < obj.options.nestedHeaders.length; j++) { var colspan = parseInt(obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan) + numOfColumns; obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan = colspan; obj.thead.children[j].children[obj.thead.children[j].children.length-1].setAttribute('colspan', colspan); var o = obj.thead.children[j].children[obj.thead.children[j].children.length-1].getAttribute('data-column'); o = o.split(','); for (var col = columnIndex; col < (numOfColumns + columnIndex); col++) { o.push(col); } obj.thead.children[j].children[obj.thead.children[j].children.length-1].setAttribute('data-column', o); } } else { var colspan = parseInt(obj.options.nestedHeaders[0].colspan) + numOfColumns; obj.options.nestedHeaders[0].colspan = colspan; obj.thead.children[0].children[obj.thead.children[0].children.length-1].setAttribute('colspan', colspan); } } // Keep history obj.setHistory({ action: 'insertColumn', columnNumber:columnNumber, numOfColumns:numOfColumns, insertBefore:insertBefore, columns:properties, headers:historyHeaders, colgroup:historyColgroup, records:historyRecords, footers:historyFooters, data:historyData, }); // Remove table references obj.updateTableReferences(); // Events obj.dispatch('oninsertcolumn', el, columnNumber, numOfColumns, historyRecords, insertBefore); } } /** * Delete a column by number * * @param integer columnNumber - reference column to be excluded * @param integer numOfColumns - number of columns to be excluded from the reference column * @return void */ obj.deleteColumn = function(columnNumber, numOfColumns) { // Global Configuration if (obj.options.allowDeleteColumn == true) { if (obj.headers.length > 1) { // Delete column definitions if (columnNumber == undefined) { var number = obj.getSelectedColumns(true); if (! number.length) { // Remove last column columnNumber = obj.headers.length - 1; numOfColumns = 1; } else { // Remove selected columnNumber = parseInt(number[0]); numOfColumns = parseInt(number.length); } } // Lasat column var lastColumn = obj.options.data[0].length - 1; if (columnNumber == undefined || columnNumber > lastColumn || columnNumber < 0) { columnNumber = lastColumn; } // Minimum of columns to be delete is 1 if (! numOfColumns) { numOfColumns = 1; } // Can't delete more than the limit of the table if (numOfColumns > obj.options.data[0].length - columnNumber) { numOfColumns = obj.options.data[0].length - columnNumber; } // onbeforedeletecolumn if (obj.dispatch('onbeforedeletecolumn', el, columnNumber, numOfColumns) === false) { return false; } // Can't remove the last column if (parseInt(columnNumber) > -1) { // Merged cells var mergeExists = false; if (Object.keys(obj.options.mergeCells).length > 0) { for (var col = columnNumber; col < columnNumber + numOfColumns; col++) { if (obj.isColMerged(col, false).length) { mergeExists = true; } } } if (mergeExists) { if (! confirm(obj.options.text.thisActionWillDestroyAnyExistingMergedCellsAreYouSure)) { return false; } else { obj.destroyMerged(); } } // Delete the column properties var columns = obj.options.columns.splice(columnNumber, numOfColumns); for (var col = columnNumber; col < columnNumber + numOfColumns; col++) { obj.colgroup[col].className = ''; obj.headers[col].className = ''; obj.colgroup[col].parentNode.removeChild(obj.colgroup[col]); obj.headers[col].parentNode.removeChild(obj.headers[col]); } var historyHeaders = obj.headers.splice(columnNumber, numOfColumns); var historyColgroup = obj.colgroup.splice(columnNumber, numOfColumns); var historyRecords = []; var historyData = []; var historyFooters = []; for (var row = 0; row < obj.options.data.length; row++) { for (var col = columnNumber; col < columnNumber + numOfColumns; col++) { obj.records[row][col].className = ''; obj.records[row][col].parentNode.removeChild(obj.records[row][col]); } } // Delete headers for (var row = 0; row < obj.options.data.length; row++) { // History historyData[row] = obj.options.data[row].splice(columnNumber, numOfColumns); historyRecords[row] = obj.records[row].splice(columnNumber, numOfColumns); } // Delete footers if (obj.options.footers) { for (var row = 0; row < obj.options.footers.length; row++) { historyFooters[row] = obj.options.footers[row].splice(columnNumber, numOfColumns); } } // Remove selection obj.conditionalSelectionUpdate(0, columnNumber, (columnNumber + numOfColumns) - 1); // Adjust nested headers if (obj.options.nestedHeaders && obj.options.nestedHeaders.length > 0) { // Flexible way to handle nestedheaders if (obj.options.nestedHeaders[0] && obj.options.nestedHeaders[0][0]) { for (var j = 0; j < obj.options.nestedHeaders.length; j++) { var colspan = parseInt(obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan) - numOfColumns; obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan = colspan; obj.thead.children[j].children[obj.thead.children[j].children.length-1].setAttribute('colspan', colspan); } } else { var colspan = parseInt(obj.options.nestedHeaders[0].colspan) - numOfColumns; obj.options.nestedHeaders[0].colspan = colspan; obj.thead.children[0].children[obj.thead.children[0].children.length-1].setAttribute('colspan', colspan); } } // Keeping history of changes obj.setHistory({ action:'deleteColumn', columnNumber:columnNumber, numOfColumns:numOfColumns, insertBefore: 1, columns:columns, headers:historyHeaders, colgroup:historyColgroup, records:historyRecords, footers:historyFooters, data:historyData, }); // Update table references obj.updateTableReferences(); // Delete obj.dispatch('ondeletecolumn', el, columnNumber, numOfColumns, historyRecords); } } else { console.error('Jspreadsheet: It is not possible to delete the last column'); } } } /** * Get selected rows numbers * * @return array */ obj.getSelectedRows = function(asIds) { var rows = []; // Get all selected rows for (var j = 0; j < obj.rows.length; j++) { if (obj.rows[j].classList.contains('selected')) { if (asIds) { rows.push(j); } else { rows.push(obj.rows[j]); } } } return rows; }, /** * Get selected column numbers * * @return array */ obj.getSelectedColumns = function() { var cols = []; // Get all selected cols for (var i = 0; i < obj.headers.length; i++) { if (obj.headers[i].classList.contains('selected')) { cols.push(i); } } return cols; } /** * Get highlighted * * @return array */ obj.getHighlighted = function() { return obj.highlighted; } /** * Update cell references * * @return void */ obj.updateTableReferences = function() { // Update headers for (var i = 0; i < obj.headers.length; i++) { var x = obj.headers[i].getAttribute('data-x'); if (x != i) { // Update coords obj.headers[i].setAttribute('data-x', i); // Title if (! obj.headers[i].getAttribute('title')) { obj.headers[i].innerHTML = jexcel.getColumnName(i); } } } // Update all rows for (var j = 0; j < obj.rows.length; j++) { if (obj.rows[j]) { var y = obj.rows[j].getAttribute('data-y'); if (y != j) { // Update coords obj.rows[j].setAttribute('data-y', j); obj.rows[j].children[0].setAttribute('data-y', j); // Row number obj.rows[j].children[0].innerHTML = j + 1; } } } // Regular cells affected by this change var affectedTokens = []; var mergeCellUpdates = []; // Update cell var updatePosition = function(x,y,i,j) { if (x != i) { obj.records[j][i].setAttribute('data-x', i); } if (y != j) { obj.records[j][i].setAttribute('data-y', j); } // Other updates if (x != i || y != j) { var columnIdFrom = jexcel.getColumnNameFromId([x, y]); var columnIdTo = jexcel.getColumnNameFromId([i, j]); affectedTokens[columnIdFrom] = columnIdTo; } } for (var j = 0; j < obj.records.length; j++) { for (var i = 0; i < obj.records[0].length; i++) { if (obj.records[j][i]) { // Current values var x = obj.records[j][i].getAttribute('data-x'); var y = obj.records[j][i].getAttribute('data-y'); // Update column if (obj.records[j][i].getAttribute('data-merged')) { var columnIdFrom = jexcel.getColumnNameFromId([x, y]); var columnIdTo = jexcel.getColumnNameFromId([i, j]); if (mergeCellUpdates[columnIdFrom] == null) { if (columnIdFrom == columnIdTo) { mergeCellUpdates[columnIdFrom] = false; } else { var totalX = parseInt(i - x); var totalY = parseInt(j - y); mergeCellUpdates[columnIdFrom] = [ columnIdTo, totalX, totalY ]; } } } else { updatePosition(x,y,i,j); } } } } // Update merged if applicable var keys = Object.keys(mergeCellUpdates); if (keys.length) { for (var i = 0; i < keys.length; i++) { if (mergeCellUpdates[keys[i]]) { var info = jexcel.getIdFromColumnName(keys[i], true) var x = info[0]; var y = info[1]; updatePosition(x,y,x + mergeCellUpdates[keys[i]][1],y + mergeCellUpdates[keys[i]][2]); var columnIdFrom = keys[i]; var columnIdTo = mergeCellUpdates[keys[i]][0]; for (var j = 0; j < obj.options.mergeCells[columnIdFrom][2].length; j++) { var x = parseInt(obj.options.mergeCells[columnIdFrom][2][j].getAttribute('data-x')); var y = parseInt(obj.options.mergeCells[columnIdFrom][2][j].getAttribute('data-y')); obj.options.mergeCells[columnIdFrom][2][j].setAttribute('data-x', x + mergeCellUpdates[keys[i]][1]); obj.options.mergeCells[columnIdFrom][2][j].setAttribute('data-y', y + mergeCellUpdates[keys[i]][2]); } obj.options.mergeCells[columnIdTo] = obj.options.mergeCells[columnIdFrom]; delete(obj.options.mergeCells[columnIdFrom]); } } } // Update formulas obj.updateFormulas(affectedTokens); // Update meta data obj.updateMeta(affectedTokens); // Refresh selection obj.refreshSelection(); // Update table with custom configuration if applicable obj.updateTable(); } /** * Custom settings for the cells */ obj.updateTable = function() { // Check for spare if (obj.options.minSpareRows > 0) { var numBlankRows = 0; for (var j = obj.rows.length - 1; j >= 0; j--) { var test = false; for (var i = 0; i < obj.headers.length; i++) { if (obj.options.data[j][i]) { test = true; } } if (test) { break; } else { numBlankRows++; } } if (obj.options.minSpareRows - numBlankRows > 0) { obj.insertRow(obj.options.minSpareRows - numBlankRows) } } if (obj.options.minSpareCols > 0) { var numBlankCols = 0; for (var i = obj.headers.length - 1; i >= 0 ; i--) { var test = false; for (var j = 0; j < obj.rows.length; j++) { if (obj.options.data[j][i]) { test = true; } } if (test) { break; } else { numBlankCols++; } } if (obj.options.minSpareCols - numBlankCols > 0) { obj.insertColumn(obj.options.minSpareCols - numBlankCols) } } // Customizations by the developer if (typeof(obj.options.updateTable) == 'function') { if (obj.options.detachForUpdates) { el.removeChild(obj.content); } for (var j = 0; j < obj.rows.length; j++) { for (var i = 0; i < obj.headers.length; i++) { obj.options.updateTable(el, obj.records[j][i], i, j, obj.options.data[j][i], obj.records[j][i].textContent, jexcel.getColumnNameFromId([i, j])); } } if (obj.options.detachForUpdates) { el.insertBefore(obj.content, obj.pagination); } } // Update footers if (obj.options.footers) { obj.setFooter(); } // Update corner position setTimeout(function() { obj.updateCornerPosition(); },0); } /** * Readonly */ obj.isReadOnly = function(cell) { if (cell = obj.getCell(cell)) { return cell.classList.contains('readonly') ? true : false; } } /** * Readonly */ obj.setReadOnly = function(cell, state) { if (cell = obj.getCell(cell)) { if (state) { cell.classList.add('readonly'); } else { cell.classList.remove('readonly'); } } } /** * Show row */ obj.showRow = function(rowNumber) { obj.rows[rowNumber].style.display = ''; } /** * Hide row */ obj.hideRow = function(rowNumber) { obj.rows[rowNumber].style.display = 'none'; } /** * Show column */ obj.showColumn = function(colNumber) { obj.headers[colNumber].style.display = ''; obj.colgroup[colNumber].style.display = ''; if (obj.filter && obj.filter.children.length > colNumber + 1) { obj.filter.children[colNumber + 1].style.display = ''; } for (var j = 0; j < obj.options.data.length; j++) { obj.records[j][colNumber].style.display = ''; } // Update footers if (obj.options.footers) { obj.setFooter(); } obj.resetSelection(); } /** * Hide column */ obj.hideColumn = function(colNumber) { obj.headers[colNumber].style.display = 'none'; obj.colgroup[colNumber].style.display = 'none'; if (obj.filter && obj.filter.children.length > colNumber + 1) { obj.filter.children[colNumber + 1].style.display = 'none'; } for (var j = 0; j < obj.options.data.length; j++) { obj.records[j][colNumber].style.display = 'none'; } // Update footers if (obj.options.footers) { obj.setFooter(); } obj.resetSelection(); } /** * Show index column */ obj.showIndex = function() { obj.table.classList.remove('jexcel_hidden_index'); } /** * Hide index column */ obj.hideIndex = function() { obj.table.classList.add('jexcel_hidden_index'); } /** * Update all related cells in the chain */ var chainLoopProtection = []; obj.updateFormulaChain = function(x, y, records) { var cellId = jexcel.getColumnNameFromId([x, y]); if (obj.formula[cellId] && obj.formula[cellId].length > 0) { if (chainLoopProtection[cellId]) { obj.records[y][x].innerHTML = '#ERROR'; obj.formula[cellId] = ''; } else { // Protection chainLoopProtection[cellId] = true; for (var i = 0; i < obj.formula[cellId].length; i++) { var cell = jexcel.getIdFromColumnName(obj.formula[cellId][i], true); // Update cell var value = ''+obj.options.data[cell[1]][cell[0]]; if (value.substr(0,1) == '=') { records.push(obj.updateCell(cell[0], cell[1], value, true)); } else { // No longer a formula, remove from the chain Object.keys(obj.formula)[i] = null; } obj.updateFormulaChain(cell[0], cell[1], records); } } } chainLoopProtection = []; } /** * Update formulas */ obj.updateFormulas = function(referencesToUpdate) { // Update formulas for (var j = 0; j < obj.options.data.length; j++) { for (var i = 0; i < obj.options.data[0].length; i++) { var value = '' + obj.options.data[j][i]; // Is formula if (value.substr(0,1) == '=') { // Replace tokens var newFormula = obj.updateFormula(value, referencesToUpdate); if (newFormula != value) { /* TablePress: Only setting the value is not enough. updateCell is needed to update the DOM. */ //obj.options.data[j][i] = newFormula; obj.updateCell( i, j, newFormula ); } } } } // Update formula chain var formula = []; var keys = Object.keys(obj.formula); for (var j = 0; j < keys.length; j++) { // Current key and values var key = keys[j]; var value = obj.formula[key]; // Update key if (referencesToUpdate[key]) { key = referencesToUpdate[key]; } // Update values formula[key] = []; for (var i = 0; i < value.length; i++) { var letter = value[i]; if (referencesToUpdate[letter]) { letter = referencesToUpdate[letter]; } formula[key].push(letter); } } obj.formula = formula; } /** * Update formula * * TablePress @TODO: This currently updates all references, including those outside of {} delimiters. * Replace this with the code of the existing implementation. */ obj.updateFormula = function(formula, referencesToUpdate) { var testLetter = /[A-Z]/; var testNumber = /[0-9]/; var newFormula = ''; var letter = null; var number = null; var token = ''; for (var index = 0; index < formula.length; index++) { if (testLetter.exec(formula[index])) { letter = 1; number = 0; token += formula[index]; } else if (testNumber.exec(formula[index])) { number = letter ? 1 : 0; token += formula[index]; } else { if (letter && number) { token = referencesToUpdate[token] ? referencesToUpdate[token] : token; } newFormula += token; newFormula += formula[index]; letter = 0; number = 0; token = ''; } } if (token) { if (letter && number) { token = referencesToUpdate[token] ? referencesToUpdate[token] : token; } newFormula += token; } return newFormula; } /** * Secure formula */ var secureFormula = function(oldValue) { var newValue = ''; var inside = 0; for (var i = 0; i < oldValue.length; i++) { if (oldValue[i] == '"') { if (inside == 0) { inside = 1; } else { inside = 0; } } if (inside == 1) { newValue += oldValue[i]; } else { newValue += oldValue[i].toUpperCase(); } } return newValue; } /** * Parse formulas */ obj.executeFormula = function(expression, x, y) { var formulaResults = []; var formulaLoopProtection = []; // Execute formula with loop protection var execute = function(expression, x, y) { // Parent column identification var parentId = jexcel.getColumnNameFromId([x, y]); // Code protection if (formulaLoopProtection[parentId]) { console.error('Reference loop detected'); return '#ERROR'; } formulaLoopProtection[parentId] = true; // Convert range tokens var tokensUpdate = function(tokens) { for (var index = 0; index < tokens.length; index++) { var f = []; var token = tokens[index].split(':'); var e1 = jexcel.getIdFromColumnName(token[0], true); var e2 = jexcel.getIdFromColumnName(token[1], true); if (e1[0] <= e2[0]) { var x1 = e1[0]; var x2 = e2[0]; } else { var x1 = e2[0]; var x2 = e1[0]; } if (e1[1] <= e2[1]) { var y1 = e1[1]; var y2 = e2[1]; } else { var y1 = e2[1]; var y2 = e1[1]; } for (var j = y1; j <= y2; j++) { for (var i = x1; i <= x2; i++) { f.push(jexcel.getColumnNameFromId([i, j])); } } expression = expression.replace(tokens[index], f.join(',')); } } // Range with $ remove $ expression = expression.replace(/\$?([A-Z]+)\$?([0-9]+)/g, "$1$2"); var tokens = expression.match(/([A-Z]+[0-9]+)\:([A-Z]+[0-9]+)/g); if (tokens && tokens.length) { tokensUpdate(tokens); } // Get tokens var tokens = expression.match(/([A-Z]+[0-9]+)/g); // Direct self-reference protection if (tokens && tokens.indexOf(parentId) > -1) { console.error('Self Reference detected'); return '#ERROR'; } else { // Expressions to be used in the parsing var formulaExpressions = {}; if (tokens) { for (var i = 0; i < tokens.length; i++) { // Keep chain if (! obj.formula[tokens[i]]) { obj.formula[tokens[i]] = []; } // Is already in the register if (obj.formula[tokens[i]].indexOf(parentId) < 0) { obj.formula[tokens[i]].push(parentId); } // Do not calculate again if (eval('typeof(' + tokens[i] + ') == "undefined"')) { // Coords var position = jexcel.getIdFromColumnName(tokens[i], 1); // Get value if (typeof(obj.options.data[position[1]]) != 'undefined' && typeof(obj.options.data[position[1]][position[0]]) != 'undefined') { var value = obj.options.data[position[1]][position[0]]; } else { var value = ''; } // Get column data if ((''+value).substr(0,1) == '=') { if (typeof formulaResults[tokens[i]] !== 'undefined') { value = formulaResults[tokens[i]]; } else { value = execute(value, position[0], position[1]); formulaResults[tokens[i]] = value; } } // Type! if ((''+value).trim() == '') { // Null formulaExpressions[tokens[i]] = null; } else { if (value == Number(value) && obj.options.autoCasting == true) { // Number formulaExpressions[tokens[i]] = Number(value); } else { // Trying any formatted number var number = obj.parseNumber(value, position[0]) if (obj.options.autoCasting == true && number) { formulaExpressions[tokens[i]] = number; } else { formulaExpressions[tokens[i]] = '"' + value + '"'; } } } } } } // Convert formula to javascript try { var res = jexcel.formula(expression.substr(1), formulaExpressions, x, y, obj); } catch (e) { var res = '#ERROR'; console.log(e) } return res; } } return execute(expression, x, y); } /** * Trying to extract a number from a string */ obj.parseNumber = function(value, columnNumber) { // Decimal point var decimal = columnNumber && obj.options.columns[columnNumber].decimal ? obj.options.columns[columnNumber].decimal : '.'; // Parse both parts of the number var number = ('' + value); number = number.split(decimal); number[0] = number[0].match(/[+-]?[0-9]/g); if (number[0]) { number[0] = number[0].join(''); } if (number[1]) { number[1] = number[1].match(/[0-9]*/g).join(''); } // Is a valid number if (number[0] && Number.isInteger(Number(number[0]))) { if (! number[1]) { var value = Number(number[0] + '.00'); } else { var value = Number(number[0] + '.' + number[1]); } } else { var value = null; } return value; } /** * Get row number */ obj.row = function(cell) { } /** * Get col number */ obj.col = function(cell) { } obj.up = function(shiftKey, ctrlKey) { if (shiftKey) { if (obj.selectedCell[3] > 0) { obj.up.visible(1, ctrlKey ? 0 : 1) } } else { if (obj.selectedCell[1] > 0) { obj.up.visible(0, ctrlKey ? 0 : 1) } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } // Update selection obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); // Change page if (obj.options.lazyLoading == true) { if (obj.selectedCell[1] == 0 || obj.selectedCell[3] == 0) { obj.loadPage(0); obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } else { if (obj.loadValidation()) { obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } else { var item = parseInt(obj.tbody.firstChild.getAttribute('data-y')); if (obj.selectedCell[1] - item < 30) { obj.loadUp(); obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } } } } else if (obj.options.pagination > 0) { var pageNumber = obj.whichPage(obj.selectedCell[3]); if (pageNumber != obj.pageNumber) { obj.page(pageNumber); } } obj.updateScroll(1); } obj.up.visible = function(group, direction) { if (group == 0) { var x = parseInt(obj.selectedCell[0]); var y = parseInt(obj.selectedCell[1]); } else { var x = parseInt(obj.selectedCell[2]); var y = parseInt(obj.selectedCell[3]); } if (direction == 0) { for (var j = 0; j < y; j++) { if (obj.records[j][x].style.display != 'none' && obj.rows[j].style.display != 'none') { y = j; break; } } } else { y = obj.up.get(x, y); } if (group == 0) { obj.selectedCell[0] = x; obj.selectedCell[1] = y; } else { obj.selectedCell[2] = x; obj.selectedCell[3] = y; } } obj.up.get = function(x, y) { var x = parseInt(x); var y = parseInt(y); for (var j = (y - 1); j >= 0; j--) { if (obj.records[j][x].style.display != 'none' && obj.rows[j].style.display != 'none') { if (obj.records[j][x].getAttribute('data-merged')) { if (obj.records[j][x] == obj.records[y][x]) { continue; } } y = j; break; } } return y; } obj.down = function(shiftKey, ctrlKey) { if (shiftKey) { if (obj.selectedCell[3] < obj.records.length - 1) { obj.down.visible(1, ctrlKey ? 0 : 1) } } else { if (obj.selectedCell[1] < obj.records.length - 1) { obj.down.visible(0, ctrlKey ? 0 : 1) } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); // Change page if (obj.options.lazyLoading == true) { if ((obj.selectedCell[1] == obj.records.length - 1 || obj.selectedCell[3] == obj.records.length - 1)) { obj.loadPage(-1); obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } else { if (obj.loadValidation()) { obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } else { var item = parseInt(obj.tbody.lastChild.getAttribute('data-y')); if (item - obj.selectedCell[3] < 30) { obj.loadDown(); obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } } } } else if (obj.options.pagination > 0) { var pageNumber = obj.whichPage(obj.selectedCell[3]); if (pageNumber != obj.pageNumber) { obj.page(pageNumber); } } obj.updateScroll(3); } obj.down.visible = function(group, direction) { if (group == 0) { var x = parseInt(obj.selectedCell[0]); var y = parseInt(obj.selectedCell[1]); } else { var x = parseInt(obj.selectedCell[2]); var y = parseInt(obj.selectedCell[3]); } if (direction == 0) { for (var j = obj.rows.length - 1; j > y; j--) { if (obj.records[j][x].style.display != 'none' && obj.rows[j].style.display != 'none') { y = j; break; } } } else { y = obj.down.get(x, y); } if (group == 0) { obj.selectedCell[0] = x; obj.selectedCell[1] = y; } else { obj.selectedCell[2] = x; obj.selectedCell[3] = y; } } obj.down.get = function(x, y) { var x = parseInt(x); var y = parseInt(y); for (var j = (y + 1); j < obj.rows.length; j++) { if (obj.records[j][x].style.display != 'none' && obj.rows[j].style.display != 'none') { if (obj.records[j][x].getAttribute('data-merged')) { if (obj.records[j][x] == obj.records[y][x]) { continue; } } y = j; break; } } return y; } obj.right = function(shiftKey, ctrlKey) { if (shiftKey) { if (obj.selectedCell[2] < obj.headers.length - 1) { obj.right.visible(1, ctrlKey ? 0 : 1) } } else { if (obj.selectedCell[0] < obj.headers.length - 1) { obj.right.visible(0, ctrlKey ? 0 : 1) } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); obj.updateScroll(2); } obj.right.visible = function(group, direction) { if (group == 0) { var x = parseInt(obj.selectedCell[0]); var y = parseInt(obj.selectedCell[1]); } else { var x = parseInt(obj.selectedCell[2]); var y = parseInt(obj.selectedCell[3]); } if (direction == 0) { for (var i = obj.headers.length - 1; i > x; i--) { if (obj.records[y][i].style.display != 'none') { x = i; break; } } } else { x = obj.right.get(x, y); } if (group == 0) { obj.selectedCell[0] = x; obj.selectedCell[1] = y; } else { obj.selectedCell[2] = x; obj.selectedCell[3] = y; } } obj.right.get = function(x, y) { var x = parseInt(x); var y = parseInt(y); for (var i = (x + 1); i < obj.headers.length; i++) { if (obj.records[y][i].style.display != 'none') { if (obj.records[y][i].getAttribute('data-merged')) { if (obj.records[y][i] == obj.records[y][x]) { continue; } } x = i; break; } } return x; } obj.left = function(shiftKey, ctrlKey) { if (shiftKey) { if (obj.selectedCell[2] > 0) { obj.left.visible(1, ctrlKey ? 0 : 1) } } else { if (obj.selectedCell[0] > 0) { obj.left.visible(0, ctrlKey ? 0 : 1) } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); obj.updateScroll(0); } obj.left.visible = function(group, direction) { if (group == 0) { var x = parseInt(obj.selectedCell[0]); var y = parseInt(obj.selectedCell[1]); } else { var x = parseInt(obj.selectedCell[2]); var y = parseInt(obj.selectedCell[3]); } if (direction == 0) { for (var i = 0; i < x; i++) { if (obj.records[y][i].style.display != 'none') { x = i; break; } } } else { x = obj.left.get(x, y); } if (group == 0) { obj.selectedCell[0] = x; obj.selectedCell[1] = y; } else { obj.selectedCell[2] = x; obj.selectedCell[3] = y; } } obj.left.get = function(x, y) { var x = parseInt(x); var y = parseInt(y); for (var i = (x - 1); i >= 0; i--) { if (obj.records[y][i].style.display != 'none') { if (obj.records[y][i].getAttribute('data-merged')) { if (obj.records[y][i] == obj.records[y][x]) { continue; } } x = i; break; } } return x; } obj.first = function(shiftKey, ctrlKey) { if (shiftKey) { if (ctrlKey) { obj.selectedCell[3] = 0; } else { obj.left.visible(1, 0); } } else { if (ctrlKey) { obj.selectedCell[1] = 0; } else { obj.left.visible(0, 0); } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } // Change page if (obj.options.lazyLoading == true && (obj.selectedCell[1] == 0 || obj.selectedCell[3] == 0)) { obj.loadPage(0); } else if (obj.options.pagination > 0) { var pageNumber = obj.whichPage(obj.selectedCell[3]); if (pageNumber != obj.pageNumber) { obj.page(pageNumber); } } obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); obj.updateScroll(1); } obj.last = function(shiftKey, ctrlKey) { if (shiftKey) { if (ctrlKey) { obj.selectedCell[3] = obj.records.length - 1; } else { obj.right.visible(1, 0); } } else { if (ctrlKey) { obj.selectedCell[1] = obj.records.length - 1; } else { obj.right.visible(0, 0); } obj.selectedCell[2] = obj.selectedCell[0]; obj.selectedCell[3] = obj.selectedCell[1]; } // Change page if (obj.options.lazyLoading == true && (obj.selectedCell[1] == obj.records.length - 1 || obj.selectedCell[3] == obj.records.length - 1)) { obj.loadPage(-1); } else if (obj.options.pagination > 0) { var pageNumber = obj.whichPage(obj.selectedCell[3]); if (pageNumber != obj.pageNumber) { obj.page(pageNumber); } } obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); obj.updateScroll(3); } obj.selectAll = function() { if (! obj.selectedCell) { obj.selectedCell = []; } obj.selectedCell[0] = 0; obj.selectedCell[1] = 0; obj.selectedCell[2] = obj.headers.length - 1; obj.selectedCell[3] = obj.records.length - 1; obj.updateSelectionFromCoords(obj.selectedCell[0], obj.selectedCell[1], obj.selectedCell[2], obj.selectedCell[3]); } /** * Go to a page in a lazyLoading */ obj.loadPage = function(pageNumber) { // Search if ((obj.options.search == true || obj.options.filters == true) && obj.results) { var results = obj.results; } else { var results = obj.rows; } // Per page var quantityPerPage = 100; // pageNumber if (pageNumber == null || pageNumber == -1) { // Last page pageNumber = Math.ceil(results.length / quantityPerPage) - 1; } var startRow = (pageNumber * quantityPerPage); var finalRow = (pageNumber * quantityPerPage) + quantityPerPage; if (finalRow > results.length) { finalRow = results.length; } startRow = finalRow - 100; if (startRow < 0) { startRow = 0; } // Appeding items for (var j = startRow; j < finalRow; j++) { if ((obj.options.search == true || obj.options.filters == true) && obj.results) { obj.tbody.appendChild(obj.rows[results[j]]); } else { obj.tbody.appendChild(obj.rows[j]); } if (obj.tbody.children.length > quantityPerPage) { obj.tbody.removeChild(obj.tbody.firstChild); } } } obj.loadUp = function() { // Search if ((obj.options.search == true || obj.options.filters == true) && obj.results) { var results = obj.results; } else { var results = obj.rows; } var test = 0; if (results.length > 100) { // Get the first element in the page var item = parseInt(obj.tbody.firstChild.getAttribute('data-y')); if ((obj.options.search == true || obj.options.filters == true) && obj.results) { item = results.indexOf(item); } if (item > 0) { for (var j = 0; j < 30; j++) { item = item - 1; if (item > -1) { if ((obj.options.search == true || obj.options.filters == true) && obj.results) { obj.tbody.insertBefore(obj.rows[results[item]], obj.tbody.firstChild); } else { obj.tbody.insertBefore(obj.rows[item], obj.tbody.firstChild); } if (obj.tbody.children.length > 100) { obj.tbody.removeChild(obj.tbody.lastChild); test = 1; } } } } } return test; } obj.loadDown = function() { // Search if ((obj.options.search == true || obj.options.filters == true) && obj.results) { var results = obj.results; } else { var results = obj.rows; } var test = 0; if (results.length > 100) { // Get the last element in the page var item = parseInt(obj.tbody.lastChild.getAttribute('data-y')); if ((obj.options.search == true || obj.options.filters == true) && obj.results) { item = results.indexOf(item); } if (item < obj.rows.length - 1) { for (var j = 0; j <= 30; j++) { if (item < results.length) { if ((obj.options.search == true || obj.options.filters == true) && obj.results) { obj.tbody.appendChild(obj.rows[results[item]]); } else { obj.tbody.appendChild(obj.rows[item]); } if (obj.tbody.children.length > 100) { obj.tbody.removeChild(obj.tbody.firstChild); test = 1; } } item = item + 1; } } } return test; } obj.loadValidation = function() { if (obj.selectedCell) { var currentPage = parseInt(obj.tbody.firstChild.getAttribute('data-y')) / 100; var selectedPage = parseInt(obj.selectedCell[3] / 100); var totalPages = parseInt(obj.rows.length / 100); if (currentPage != selectedPage && selectedPage <= totalPages) { if (! Array.prototype.indexOf.call(obj.tbody.children, obj.rows[obj.selectedCell[3]])) { obj.loadPage(selectedPage); return true; } } } return false; } /** * Reset search */ obj.resetSearch = function() { obj.searchInput.value = ''; obj.search(''); obj.results = null; } /** * Search */ obj.search = function(query) { // Reset any filter if (obj.options.filters) { obj.resetFilters(); } // Reset selection obj.resetSelection(); // Total of results obj.pageNumber = 0; obj.results = []; if (query) { // Search filter var search = function(item, query, index) { for (var i = 0; i < item.length; i++) { if ((''+item[i]).toLowerCase().search(query) >= 0 || (''+obj.records[index][i].innerHTML).toLowerCase().search(query) >= 0) { return true; } } return false; } // Result var addToResult = function(k) { if (obj.results.indexOf(k) == -1) { obj.results.push(k); } } var parsedQuery = query.replace(/[-[\]{}()*+?.,\\^$|#\s]/g, "\\$&"); parsedQuery = new RegExp(parsedQuery, "i"); // Filter obj.options.data.forEach(function(v, k) { if (search(v, parsedQuery, k)) { // Merged rows found var rows = obj.isRowMerged(k); if (rows.length) { for (var i = 0; i < rows.length; i++) { var row = jexcel.getIdFromColumnName(rows[i], true); for (var j = 0; j < obj.options.mergeCells[rows[i]][1]; j++) { addToResult(row[1]+j); } } } else { // Normal row found addToResult(k); } } }); } else { obj.results = null; } return obj.updateResult(); } obj.updateResult = function() { var total = 0; var index = 0; // Page 1 if (obj.options.lazyLoading == true) { total = 100; } else if (obj.options.pagination > 0) { total = obj.options.pagination; } else { if (obj.results) { total = obj.results.length; } else { total = obj.rows.length; } } // Reset current nodes while (obj.tbody.firstChild) { obj.tbody.removeChild(obj.tbody.firstChild); } // Hide all records from the table for (var j = 0; j < obj.rows.length; j++) { if (! obj.results || obj.results.indexOf(j) > -1) { if (index < total) { obj.tbody.appendChild(obj.rows[j]); index++; } obj.rows[j].style.display = ''; } else { obj.rows[j].style.display = 'none'; } } // Update pagination if (obj.options.pagination > 0) { obj.updatePagination(); } obj.updateCornerPosition(); return total; } /** * Which page the cell is */ obj.whichPage = function(cell) { // Search if ((obj.options.search == true || obj.options.filters == true) && obj.results) { cell = obj.results.indexOf(cell); } return (Math.ceil((parseInt(cell) + 1) / parseInt(obj.options.pagination))) - 1; } /** * Go to page */ obj.page = function(pageNumber) { var oldPage = obj.pageNumber; // Search if ((obj.options.search == true || obj.options.filters == true) && obj.results) { var results = obj.results; } else { var results = obj.rows; } // Per page var quantityPerPage = parseInt(obj.options.pagination); // pageNumber if (pageNumber == null || pageNumber == -1) { // Last page pageNumber = Math.ceil(results.length / quantityPerPage) - 1; } // Page number obj.pageNumber = pageNumber; var startRow = (pageNumber * quantityPerPage); var finalRow = (pageNumber * quantityPerPage) + quantityPerPage; if (finalRow > results.length) { finalRow = results.length; } if (startRow < 0) { startRow = 0; } // Reset container while (obj.tbody.firstChild) { obj.tbody.removeChild(obj.tbody.firstChild); } // Appeding items for (var j = startRow; j < finalRow; j++) { if ((obj.options.search == true || obj.options.filters == true) && obj.results) { obj.tbody.appendChild(obj.rows[results[j]]); } else { obj.tbody.appendChild(obj.rows[j]); } } if (obj.options.pagination > 0) { obj.updatePagination(); } // Update corner position obj.updateCornerPosition(); // Events obj.dispatch('onchangepage', el, pageNumber, oldPage); } /** * Update the pagination */ obj.updatePagination = function() { // Reset container obj.pagination.children[0].innerHTML = ''; obj.pagination.children[1].innerHTML = ''; // Start pagination if (obj.options.pagination) { // Searchable if ((obj.options.search == true || obj.options.filters == true) && obj.results) { var results = obj.results.length; } else { var results = obj.rows.length; } if (! results) { // No records found obj.pagination.children[0].innerHTML = obj.options.text.noRecordsFound; } else { // Pagination container var quantyOfPages = Math.ceil(results / obj.options.pagination); if (obj.pageNumber < 6) { var startNumber = 1; var finalNumber = quantyOfPages < 10 ? quantyOfPages : 10; } else if (quantyOfPages - obj.pageNumber < 5) { var startNumber = quantyOfPages - 9; var finalNumber = quantyOfPages; if (startNumber < 1) { startNumber = 1; } } else { var startNumber = obj.pageNumber - 4; var finalNumber = obj.pageNumber + 5; } // First if (startNumber > 1) { var paginationItem = document.createElement('div'); paginationItem.className = 'jexcel_page'; paginationItem.innerHTML = '<'; paginationItem.title = 1; obj.pagination.children[1].appendChild(paginationItem); } // Get page links for (var i = startNumber; i <= finalNumber; i++) { var paginationItem = document.createElement('div'); paginationItem.className = 'jexcel_page'; paginationItem.innerHTML = i; obj.pagination.children[1].appendChild(paginationItem); if (obj.pageNumber == (i-1)) { paginationItem.classList.add('jexcel_page_selected'); } } // Last if (finalNumber < quantyOfPages) { var paginationItem = document.createElement('div'); paginationItem.className = 'jexcel_page'; paginationItem.innerHTML = '>'; paginationItem.title = quantyOfPages; obj.pagination.children[1].appendChild(paginationItem); } // Text var format = function(format) { var args = Array.prototype.slice.call(arguments, 1); return format.replace(/{(\d+)}/g, function(match, number) { return typeof args[number] != 'undefined' ? args[number] : match ; }); }; obj.pagination.children[0].innerHTML = format(obj.options.text.showingPage, obj.pageNumber + 1, quantyOfPages) } } } /** * Download CSV table * * @return null */ obj.download = function(includeHeaders) { if (obj.options.allowExport == false) { console.error('Export not allowed'); } else { // Data var data = ''; // Get data data += obj.copy(false, obj.options.csvDelimiter, true, includeHeaders, true); // Download element var blob = new Blob(["\uFEFF"+data], {type: 'text/csv;charset=utf-8;'}); // IE Compatibility if (window.navigator && window.navigator.msSaveOrOpenBlob) { window.navigator.msSaveOrOpenBlob(blob, obj.options.csvFileName + '.csv'); } else { // Download element var pom = document.createElement('a'); var url = URL.createObjectURL(blob); pom.href = url; pom.setAttribute('download', obj.options.csvFileName + '.csv'); document.body.appendChild(pom); pom.click(); pom.parentNode.removeChild(pom); } } } /** * Initializes a new history record for undo/redo * * @return null */ obj.setHistory = function(changes) { if (obj.ignoreHistory != true) { // Increment and get the current history index var index = ++obj.historyIndex; // Slice the array to discard undone changes obj.history = (obj.history = obj.history.slice(0, index + 1)); // Keep history obj.history[index] = changes; } } /** * Copy method * * @param bool highlighted - Get only highlighted cells * @param delimiter - \t default to keep compatibility with excel * @return string value */ obj.copy = function(highlighted, delimiter, returnData, includeHeaders, download) { if (! delimiter) { delimiter = "\t"; } var div = new RegExp(delimiter, 'ig'); // Controls var header = []; var col = []; var colLabel = []; var row = []; var rowLabel = []; var x = obj.options.data[0].length; var y = obj.options.data.length; var tmp = ''; var copyHeader = false; var headers = ''; var nestedHeaders = ''; var numOfCols = 0; var numOfRows = 0; // Partial copy var copyX = 0; var copyY = 0; var isPartialCopy = true; // Go through the columns to get the data for (var j = 0; j < y; j++) { for (var i = 0; i < x; i++) { // If cell is highlighted if (! highlighted || obj.records[j][i].classList.contains('highlight')) { if (copyX <= i) { copyX = i; } if (copyY <= j) { copyY = j; } } } } if (x === copyX+1 && y === copyY+1) { isPartialCopy = false; } if ((download && obj.options.includeHeadersOnDownload == true) || (! download && obj.options.includeHeadersOnCopy == true && ! isPartialCopy) || (includeHeaders)) { // Nested headers if (obj.options.nestedHeaders && obj.options.nestedHeaders.length > 0) { // Flexible way to handle nestedheaders if (! (obj.options.nestedHeaders[0] && obj.options.nestedHeaders[0][0])) { tmp = [obj.options.nestedHeaders]; } else { tmp = obj.options.nestedHeaders; } for (var j = 0; j < tmp.length; j++) { var nested = []; for (var i = 0; i < tmp[j].length; i++) { var colspan = parseInt(tmp[j][i].colspan); nested.push(tmp[j][i].title); for (var c = 0; c < colspan - 1; c++) { nested.push(''); } } nestedHeaders += nested.join(delimiter) + "\r\n"; } } copyHeader = true; } // Reset container obj.style = []; // Go through the columns to get the data for (var j = 0; j < y; j++) { col = []; colLabel = []; for (var i = 0; i < x; i++) { // If cell is highlighted if (! highlighted || obj.records[j][i].classList.contains('highlight')) { if (copyHeader == true) { header.push(obj.headers[i].textContent); } // Values var value = obj.options.data[j][i]; // TablePress: Only escape quotation marks in cells if the cell has to be CSV escaped due to other characters, and make finding such characters more robust. /* if (value.match && (value.match(div) || value.match(/,/g) || value.match(/\n/) || value.match(/\"/))) { value = value.replace(new RegExp('"', 'g'), '""'); value = '"' + value + '"'; } */ if ( value.match && ( value.match( div ) || value.includes( delimiter ) || value.includes( "\n" ) ) ) { if ( value.includes( '"' ) ) { value = value.replace(new RegExp('"', 'g'), '""'); } value = '"' + value + '"'; } col.push(value); // Labels if (obj.options.columns[i].type == 'checkbox' || obj.options.columns[i].type == 'radio') { var label = value; } else { if (obj.options.stripHTMLOnCopy == true) { var label = obj.records[j][i].textContent; } else { var label = obj.records[j][i].innerHTML; } if (label.match && (label.match(div) || label.match(/,/g) || label.match(/\n/) || label.match(/\"/))) { // Scape double quotes label = label.replace(new RegExp('"', 'g'), '""'); label = '"' + label + '"'; } } colLabel.push(label); // Get style tmp = obj.records[j][i].getAttribute('style'); tmp = tmp.replace('display: none;', ''); obj.style.push(tmp ? tmp : ''); } } if (col.length) { if (copyHeader) { numOfCols = col.length; row.push(header.join(delimiter)); } row.push(col.join(delimiter)); } if (colLabel.length) { numOfRows++; if (copyHeader) { rowLabel.push(header.join(delimiter)); copyHeader = false; } rowLabel.push(colLabel.join(delimiter)); } } if (x == numOfCols && y == numOfRows) { headers = nestedHeaders; } // Final string var str = headers + row.join("\r\n"); var strLabel = headers + rowLabel.join("\r\n"); // Create a hidden textarea to copy the values if (! returnData) { if (obj.options.copyCompatibility == true) { obj.textarea.value = strLabel; } else { obj.textarea.value = str; } obj.textarea.select(); document.execCommand("copy"); } // Keep data if (obj.options.copyCompatibility == true) { obj.data = strLabel; } else { obj.data = str; } // Keep non visible information obj.hashString = obj.hash(obj.data); // Any exiting border should go if (! returnData) { obj.removeCopyingSelection(); // Border if (obj.highlighted) { for (var i = 0; i < obj.highlighted.length; i++) { obj.highlighted[i].classList.add('copying'); if (obj.highlighted[i].classList.contains('highlight-left')) { obj.highlighted[i].classList.add('copying-left'); } if (obj.highlighted[i].classList.contains('highlight-right')) { obj.highlighted[i].classList.add('copying-right'); } if (obj.highlighted[i].classList.contains('highlight-top')) { obj.highlighted[i].classList.add('copying-top'); } if (obj.highlighted[i].classList.contains('highlight-bottom')) { obj.highlighted[i].classList.add('copying-bottom'); } } } // Paste event obj.dispatch('oncopy', el, obj.options.copyCompatibility == true ? rowLabel : row, obj.hashString); } return obj.data; } /** * Jspreadsheet paste method * * @param integer row number * @return string value */ obj.paste = function(x, y, data) { var x_1 = parseInt(x), y_1 = parseInt(y), x_2 = parseInt(obj.selectedCell[2]), y_2 = parseInt(obj.selectedCell[3]), w = x_2 - x_1 + 1, h = y_2 - y_1 + 1; // change paste range if select is from right to left if (x_2 < x_1){ x = x_2.toString(); w = x_1 - x_2 + 1; } // change paste range if select is from down to up if (y_2 < y_1){ y = y_2.toString(); h = y_1 - y_2 + 1; } // Paste filter var ret = obj.dispatch('onbeforepaste', el, data, x, y); if (ret === false) { return false; } else if (ret) { var data = ret; } // Controls var hash = obj.hash(data); var style = (hash == obj.hashString) ? obj.style : null; // Depending on the behavior if (obj.options.copyCompatibility == true && hash == obj.hashString) { var data = obj.data; } // Split new line var data = obj.parseCSV(data, "\t"); // Modify data to allow wor extending paste range in multiples of input range if ((w > 1) & Number.isInteger(w / data[0].length)) { style = null; repeats = w / data[0].length; var arrayB = data.map(function (row, i) { var arrayC = Array.apply(null, { length: repeats * row.length }).map( function (e, i) { return row[i % row.length]; } ); return arrayC; }); data = arrayB; } if ((h > 1) & Number.isInteger(h / data.length)) { style = null; var repeats = h / data.length; var arrayB = Array.apply(null, { length: repeats * data.length }).map( function (e, i) { return data[i % data.length]; } ); data = arrayB; } if (x != null && y != null && data) { // Records var i = 0; var j = 0; var records = []; var newStyle = {}; var oldStyle = {}; var styleIndex = 0; // Index var colIndex = parseInt(x); var rowIndex = parseInt(y); var row = null; // Go through the columns to get the data while (row = data[j]) { i = 0; colIndex = parseInt(x); while (row[i] != null) { // Update and keep history var record = obj.updateCell(colIndex, rowIndex, row[i]); // Keep history records.push(record); // Update all formulas in the chain obj.updateFormulaChain(colIndex, rowIndex, records); // Style if (style && style[styleIndex]) { var columnName = jexcel.getColumnNameFromId([colIndex, rowIndex]); newStyle[columnName] = style[styleIndex]; oldStyle[columnName] = obj.getStyle(columnName); obj.records[rowIndex][colIndex].setAttribute('style', style[styleIndex]); styleIndex++ } i++; if (row[i] != null) { if (colIndex >= obj.headers.length - 1) { // If the pasted column is out of range, create it if possible if (obj.options.allowInsertColumn == true) { obj.insertColumn(); // Otherwise skip the pasted data that overflows } else { break; } } colIndex = obj.right.get(colIndex, rowIndex); } } j++; if (data[j]) { if (rowIndex >= obj.rows.length-1) { // If the pasted row is out of range, create it if possible if (obj.options.allowInsertRow == true) { obj.insertRow(); // Otherwise skip the pasted data that overflows } else { break; } } rowIndex = obj.down.get(x, rowIndex); } } // Select the new cells obj.updateSelectionFromCoords(x, y, colIndex, rowIndex); // Update history obj.setHistory({ action:'setValue', records:records, selection:obj.selectedCell, newStyle:newStyle, oldStyle:oldStyle, }); // Update table obj.updateTable(); // Paste event obj.dispatch('onpaste', el, data); // On after changes obj.onafterchanges(el, records); } obj.removeCopyingSelection(); } /** * Remove copying border */ obj.removeCopyingSelection = function() { var copying = document.querySelectorAll('.jexcel .copying'); for (var i = 0; i < copying.length; i++) { copying[i].classList.remove('copying'); copying[i].classList.remove('copying-left'); copying[i].classList.remove('copying-right'); copying[i].classList.remove('copying-top'); copying[i].classList.remove('copying-bottom'); } } /** * Process row */ obj.historyProcessRow = function(type, historyRecord) { var rowIndex = (! historyRecord.insertBefore) ? historyRecord.rowNumber + 1 : +historyRecord.rowNumber; if (obj.options.search == true) { if (obj.results && obj.results.length != obj.rows.length) { obj.resetSearch(); } } // Remove row if (type == 1) { var numOfRows = historyRecord.numOfRows; // Remove nodes for (var j = rowIndex; j < (numOfRows + rowIndex); j++) { obj.rows[j].parentNode.removeChild(obj.rows[j]); } // Remove references obj.records.splice(rowIndex, numOfRows); obj.options.data.splice(rowIndex, numOfRows); obj.rows.splice(rowIndex, numOfRows); obj.conditionalSelectionUpdate(1, rowIndex, (numOfRows + rowIndex) - 1); } else { // Insert data obj.records = jexcel.injectArray(obj.records, rowIndex, historyRecord.rowRecords); obj.options.data = jexcel.injectArray(obj.options.data, rowIndex, historyRecord.rowData); obj.rows = jexcel.injectArray(obj.rows, rowIndex, historyRecord.rowNode); // Insert nodes var index = 0 for (var j = rowIndex; j < (historyRecord.numOfRows + rowIndex); j++) { obj.tbody.insertBefore(historyRecord.rowNode[index], obj.tbody.children[j]); index++; } } // Respect pagination if (obj.options.pagination > 0) { obj.page(obj.pageNumber); } obj.updateTableReferences(); } /** * Process column */ obj.historyProcessColumn = function(type, historyRecord) { var columnIndex = (! historyRecord.insertBefore) ? historyRecord.columnNumber + 1 : historyRecord.columnNumber; // Remove column if (type == 1) { var numOfColumns = historyRecord.numOfColumns; obj.options.columns.splice(columnIndex, numOfColumns); for (var i = columnIndex; i < (numOfColumns + columnIndex); i++) { obj.headers[i].parentNode.removeChild(obj.headers[i]); obj.colgroup[i].parentNode.removeChild(obj.colgroup[i]); } obj.headers.splice(columnIndex, numOfColumns); obj.colgroup.splice(columnIndex, numOfColumns); for (var j = 0; j < historyRecord.data.length; j++) { for (var i = columnIndex; i < (numOfColumns + columnIndex); i++) { obj.records[j][i].parentNode.removeChild(obj.records[j][i]); } obj.records[j].splice(columnIndex, numOfColumns); obj.options.data[j].splice(columnIndex, numOfColumns); } // Process footers if (obj.options.footers) { for (var j = 0; j < obj.options.footers.length; j++) { obj.options.footers[j].splice(columnIndex, numOfColumns); } } } else { // Insert data obj.options.columns = jexcel.injectArray(obj.options.columns, columnIndex, historyRecord.columns); obj.headers = jexcel.injectArray(obj.headers, columnIndex, historyRecord.headers); obj.colgroup = jexcel.injectArray(obj.colgroup, columnIndex, historyRecord.colgroup); var index = 0 for (var i = columnIndex; i < (historyRecord.numOfColumns + columnIndex); i++) { obj.headerContainer.insertBefore(historyRecord.headers[index], obj.headerContainer.children[i+1]); obj.colgroupContainer.insertBefore(historyRecord.colgroup[index], obj.colgroupContainer.children[i+1]); index++; } for (var j = 0; j < historyRecord.data.length; j++) { obj.options.data[j] = jexcel.injectArray(obj.options.data[j], columnIndex, historyRecord.data[j]); obj.records[j] = jexcel.injectArray(obj.records[j], columnIndex, historyRecord.records[j]); var index = 0 for (var i = columnIndex; i < (historyRecord.numOfColumns + columnIndex); i++) { obj.rows[j].insertBefore(historyRecord.records[j][index], obj.rows[j].children[i+1]); index++; } } // Process footers if (obj.options.footers) { for (var j = 0; j < obj.options.footers.length; j++) { obj.options.footers[j] = jexcel.injectArray(obj.options.footers[j], columnIndex, historyRecord.footers[j]); } } } // Adjust nested headers if (obj.options.nestedHeaders && obj.options.nestedHeaders.length > 0) { // Flexible way to handle nestedheaders if (obj.options.nestedHeaders[0] && obj.options.nestedHeaders[0][0]) { for (var j = 0; j < obj.options.nestedHeaders.length; j++) { if (type == 1) { var colspan = parseInt(obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan) - historyRecord.numOfColumns; } else { var colspan = parseInt(obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan) + historyRecord.numOfColumns; } obj.options.nestedHeaders[j][obj.options.nestedHeaders[j].length-1].colspan = colspan; obj.thead.children[j].children[obj.thead.children[j].children.length-1].setAttribute('colspan', colspan); } } else { if (type == 1) { var colspan = parseInt(obj.options.nestedHeaders[0].colspan) - historyRecord.numOfColumns; } else { var colspan = parseInt(obj.options.nestedHeaders[0].colspan) + historyRecord.numOfColumns; } obj.options.nestedHeaders[0].colspan = colspan; obj.thead.children[0].children[obj.thead.children[0].children.length-1].setAttribute('colspan', colspan); } } obj.updateTableReferences(); } /** * Undo last action */ obj.undo = function() { // Ignore events and history var ignoreEvents = obj.ignoreEvents ? true : false; var ignoreHistory = obj.ignoreHistory ? true : false; obj.ignoreEvents = true; obj.ignoreHistory = true; // Records var records = []; // Update cells if (obj.historyIndex >= 0) { // History var historyRecord = obj.history[obj.historyIndex--]; if (historyRecord.action == 'insertRow') { obj.historyProcessRow(1, historyRecord); } else if (historyRecord.action == 'deleteRow') { obj.historyProcessRow(0, historyRecord); } else if (historyRecord.action == 'insertColumn') { obj.historyProcessColumn(1, historyRecord); } else if (historyRecord.action == 'deleteColumn') { obj.historyProcessColumn(0, historyRecord); } else if (historyRecord.action == 'moveRow') { obj.moveRow(historyRecord.newValue, historyRecord.oldValue); } else if (historyRecord.action == 'moveColumn') { obj.moveColumn(historyRecord.newValue, historyRecord.oldValue); } else if (historyRecord.action == 'setMerge') { obj.removeMerge(historyRecord.column, historyRecord.data); } else if (historyRecord.action == 'setStyle') { obj.setStyle(historyRecord.oldValue, null, null, 1); } else if (historyRecord.action == 'setWidth') { obj.setWidth(historyRecord.column, historyRecord.oldValue); } else if (historyRecord.action == 'setHeight') { obj.setHeight(historyRecord.row, historyRecord.oldValue); } else if (historyRecord.action == 'setHeader') { obj.setHeader(historyRecord.column, historyRecord.oldValue); } else if (historyRecord.action == 'setComments') { obj.setComments(historyRecord.column, historyRecord.oldValue[0], historyRecord.oldValue[1]); } else if (historyRecord.action == 'orderBy') { var rows = []; for (var j = 0; j < historyRecord.rows.length; j++) { rows[historyRecord.rows[j]] = j; } obj.updateOrderArrow(historyRecord.column, historyRecord.order ? 0 : 1); obj.updateOrder(rows); } else if (historyRecord.action == 'setValue') { // Redo for changes in cells for (var i = 0; i < historyRecord.records.length; i++) { records.push({ x: historyRecord.records[i].x, y: historyRecord.records[i].y, newValue: historyRecord.records[i].oldValue, }); if (historyRecord.oldStyle) { obj.resetStyle(historyRecord.oldStyle); } } // Update records obj.setValue(records); // Update selection if (historyRecord.selection) { obj.updateSelectionFromCoords(historyRecord.selection[0], historyRecord.selection[1], historyRecord.selection[2], historyRecord.selection[3]); } } } obj.ignoreEvents = ignoreEvents; obj.ignoreHistory = ignoreHistory; // Events obj.dispatch('onundo', el, historyRecord); } /** * Redo previously undone action */ obj.redo = function() { // Ignore events and history var ignoreEvents = obj.ignoreEvents ? true : false; var ignoreHistory = obj.ignoreHistory ? true : false; obj.ignoreEvents = true; obj.ignoreHistory = true; // Records var records = []; // Update cells if (obj.historyIndex < obj.history.length - 1) { // History var historyRecord = obj.history[++obj.historyIndex]; if (historyRecord.action == 'insertRow') { obj.historyProcessRow(0, historyRecord); } else if (historyRecord.action == 'deleteRow') { obj.historyProcessRow(1, historyRecord); } else if (historyRecord.action == 'insertColumn') { obj.historyProcessColumn(0, historyRecord); } else if (historyRecord.action == 'deleteColumn') { obj.historyProcessColumn(1, historyRecord); } else if (historyRecord.action == 'moveRow') { obj.moveRow(historyRecord.oldValue, historyRecord.newValue); } else if (historyRecord.action == 'moveColumn') { obj.moveColumn(historyRecord.oldValue, historyRecord.newValue); } else if (historyRecord.action == 'setMerge') { obj.setMerge(historyRecord.column, historyRecord.colspan, historyRecord.rowspan, 1); } else if (historyRecord.action == 'setStyle') { obj.setStyle(historyRecord.newValue, null, null, 1); } else if (historyRecord.action == 'setWidth') { obj.setWidth(historyRecord.column, historyRecord.newValue); } else if (historyRecord.action == 'setHeight') { obj.setHeight(historyRecord.row, historyRecord.newValue); } else if (historyRecord.action == 'setHeader') { obj.setHeader(historyRecord.column, historyRecord.newValue); } else if (historyRecord.action == 'setComments') { obj.setComments(historyRecord.column, historyRecord.newValue[0], historyRecord.newValue[1]); } else if (historyRecord.action == 'orderBy') { obj.updateOrderArrow(historyRecord.column, historyRecord.order); obj.updateOrder(historyRecord.rows); } else if (historyRecord.action == 'setValue') { obj.setValue(historyRecord.records); // Redo for changes in cells for (var i = 0; i < historyRecord.records.length; i++) { if (historyRecord.oldStyle) { obj.resetStyle(historyRecord.newStyle); } } // Update selection if (historyRecord.selection) { obj.updateSelectionFromCoords(historyRecord.selection[0], historyRecord.selection[1], historyRecord.selection[2], historyRecord.selection[3]); } } } obj.ignoreEvents = ignoreEvents; obj.ignoreHistory = ignoreHistory; // Events obj.dispatch('onredo', el, historyRecord); } /** * Get dropdown value from key */ obj.getDropDownValue = function(column, key) { var value = []; if (obj.options.columns[column] && obj.options.columns[column].source) { // Create array from source var combo = []; var source = obj.options.columns[column].source; for (var i = 0; i < source.length; i++) { if (typeof(source[i]) == 'object') { combo[source[i].id] = source[i].name; } else { combo[source[i]] = source[i]; } } // Guarantee single multiple compatibility var keys = Array.isArray(key) ? key : ('' + key).split(';'); for (var i = 0; i < keys.length; i++) { if (typeof(keys[i]) === 'object') { value.push(combo[keys[i].id]); } else { if (combo[keys[i]]) { value.push(combo[keys[i]]); } } } } else { console.error('Invalid column'); } return (value.length > 0) ? value.join('; ') : ''; } /** * From stack overflow contributions */ /** // TablePress: Replaced with more reliable parseCSV function below. obj.parseCSV = function(str, delimiter) { // Remove last line break str = str.replace(/\r?\n$|\r$|\n$/g, ""); // Last caracter is the delimiter if (str.charCodeAt(str.length-1) == 9) { str += "\0"; } // user-supplied delimeter or default comma delimiter = (delimiter || ","); var arr = []; var quote = false; // true means we're inside a quoted field // iterate over each character, keep track of current row and column (of the returned array) for (var row = 0, col = 0, c = 0; c < str.length; c++) { var cc = str[c], nc = str[c+1]; arr[row] = arr[row] || []; arr[row][col] = arr[row][col] || ''; // If the current character is a quotation mark, and we're inside a quoted field, and the next character is also a quotation mark, add a quotation mark to the current column and skip the next character if (cc == '"' && quote && nc == '"') { arr[row][col] += cc; ++c; continue; } // If it's just one quotation mark, begin/end quoted field if (cc == '"') { quote = !quote; continue; } // If it's a comma and we're not in a quoted field, move on to the next column if (cc == delimiter && !quote) { ++col; continue; } // If it's a newline (CRLF) and we're not in a quoted field, skip the next character and move on to the next row and move to column 0 of that new row if (cc == '\r' && nc == '\n' && !quote) { ++row; col = 0; ++c; continue; } // If it's a newline (LF or CR) and we're not in a quoted field, move on to the next row and move to column 0 of that new row if (cc == '\n' && !quote) { ++row; col = 0; continue; } if (cc == '\r' && !quote) { ++row; col = 0; continue; } // Otherwise, append the current character to the current column arr[row][col] += cc; } return arr; } */ // TablePress: This new function replaces the original parseCSV function above, which has problems with quotation marks. // This implementation was ported from the PHP implementation in /libraries/csv-parser.class.php to JS. obj.parseCSV = function(str, delimiter) { // Ensure that the string has a line break at the end, by first removing all line breaks, and then adding one. str = str.replace( /\r?\n$|\r$|\n$/g, '' ); str += "\n"; // Use user-supplied delimiter or use a comma as the default. delimiter = delimiter || ','; const white_spaces = new RegExp( '^' + " \t\x0B\0".replace( delimiter, '' ) + '*' ); let rows = []; // Complete rows. let row = []; // Row that is currently built. let column = 0; // Current column index. let cell_content = ''; // Content of the currently processed cell. let enclosed = false; let was_enclosed = false; // To determine if the cell content will be trimmed of whitespace (only for enclosed cells). // Walk through each character in the CSV string. const data_length = str.length; for ( let i = 0; i < data_length; i++ ) { let curr_char = str[ i ]; let next_char = ( i + 1 < data_length ) ? str[ i + 1 ] : ''; if ( curr_char === '"' ) { // Open/close quotes, and inline quotes. if ( ! enclosed ) { if ( '' === cell_content.replace( white_spaces, '' ) ) { enclosed = true; was_enclosed = true; } else { // Technically, there's a CSV syntax here now, but we ignore it. cell_content += curr_char; } } else if ( next_char === '"' ) { // Enclosure character within enclosed cell (" encoded as ""). cell_content += curr_char; ++i; // Skip next character. } else if ( next_char !== delimiter && "\r" !== next_char && "\n" !== next_char ) { // for-loop (instead of while-loop) that skips whitespace. let x = i + 1; for ( ; undefined !== str[ x ] && '' === str[ x ].replace( white_spaces, '' ); x++ ) { // Action is in iterator check. } if ( str[ x ] === delimiter ) { enclosed = false; i = x; } else { // Technically, there's a CSV syntax here now, but we ignore it. cell_content += curr_char; enclosed = false; } } else { // The " was the closing one for the cell. enclosed = false; } } else if ( ( curr_char === delimiter || "\n" === curr_char || "\r" === curr_char ) && ! enclosed ) { // End of cell (by delimiter), or end of line (by line break, and not enclosed!). row[ column ] = was_enclosed ? cell_content : cell_content.trim(); cell_content = ''; was_enclosed = false; ++column; // End of line. if ( "\n" === curr_char || "\r" === curr_char ) { // Append completed row. rows.push( row ); row = []; column = 0; if ( "\r" === curr_char && "\n" === next_char ) { // Skip next character in \r\n line breaks. ++i; } } } else { // Append character to current cell. cell_content += curr_char; } } return rows; } obj.hash = function(str) { var hash = 0, i, chr; if (str.length === 0) { return hash; } else { for (i = 0; i < str.length; i++) { chr = str.charCodeAt(i); hash = ((hash << 5) - hash) + chr; hash |= 0; } } return hash; } obj.onafterchanges = function(el, records) { // Events obj.dispatch('onafterchanges', el, records); } obj.destroy = function() { jexcel.destroy(el); } /** * Initialization method */ obj.init = function() { jexcel.current = obj; // Build handlers if (typeof(jexcel.build) == 'function') { if (obj.options.root) { jexcel.build(obj.options.root); } else { jexcel.build(document); jexcel.build = null; } } // Event el.setAttribute('tabindex', 1); el.addEventListener('focus', function(e) { if (jexcel.current && ! obj.selectedCell) { obj.updateSelectionFromCoords(0,0,0,0); obj.left(); } }); // Load the table data based on an CSV file if (obj.options.csv) { // Loading if (obj.options.loadingSpin == true) { jSuites.loading.show(); } // Load CSV file jSuites.ajax({ url: obj.options.csv, method: obj.options.method, data: obj.options.requestVariables, dataType: 'text', success: function(result) { // Convert data var newData = obj.parseCSV(result, obj.options.csvDelimiter) // Headers if (obj.options.csvHeaders == true && newData.length > 0) { var headers = newData.shift(); for(var i = 0; i < headers.length; i++) { if (! obj.options.columns[i]) { obj.options.columns[i] = { type:'text', align:obj.options.defaultColAlign, width:obj.options.defaultColWidth }; } // Precedence over pre-configurated titles if (typeof obj.options.columns[i].title === 'undefined') { obj.options.columns[i].title = headers[i]; } } } // Data obj.options.data = newData; // Prepare table obj.prepareTable(); // Hide spin if (obj.options.loadingSpin == true) { jSuites.loading.hide(); } } }); } else if (obj.options.url) { // Loading if (obj.options.loadingSpin == true) { jSuites.loading.show(); } jSuites.ajax({ url: obj.options.url, method: obj.options.method, data: obj.options.requestVariables, dataType: 'json', success: function(result) { // Data obj.options.data = (result.data) ? result.data : result; // Prepare table obj.prepareTable(); // Hide spin if (obj.options.loadingSpin == true) { jSuites.loading.hide(); } } }); } else { // Prepare table obj.prepareTable(); } } // Context menu if (options && options.contextMenu != null) { obj.options.contextMenu = options.contextMenu; } else { obj.options.contextMenu = function(el, x, y, e) { var items = []; if (y == null) { // Insert a new column if (obj.options.allowInsertColumn == true) { items.push({ title:obj.options.text.insertANewColumnBefore, onclick:function() { obj.insertColumn(1, parseInt(x), 1); } }); } if (obj.options.allowInsertColumn == true) { items.push({ title:obj.options.text.insertANewColumnAfter, onclick:function() { obj.insertColumn(1, parseInt(x), 0); } }); } // Delete a column if (obj.options.allowDeleteColumn == true) { items.push({ title:obj.options.text.deleteSelectedColumns, onclick:function() { obj.deleteColumn(obj.getSelectedColumns().length ? undefined : parseInt(x)); } }); } // Rename column if (obj.options.allowRenameColumn == true) { items.push({ title:obj.options.text.renameThisColumn, onclick:function() { obj.setHeader(x); } }); } // Sorting if (obj.options.columnSorting == true) { // Line items.push({ type:'line' }); items.push({ title:obj.options.text.orderAscending, onclick:function() { obj.orderBy(x, 0); } }); items.push({ title:obj.options.text.orderDescending, onclick:function() { obj.orderBy(x, 1); } }); } } else { // Insert new row if (obj.options.allowInsertRow == true) { items.push({ title:obj.options.text.insertANewRowBefore, onclick:function() { obj.insertRow(1, parseInt(y), 1); } }); items.push({ title:obj.options.text.insertANewRowAfter, onclick:function() { obj.insertRow(1, parseInt(y)); } }); } if (obj.options.allowDeleteRow == true) { items.push({ title:obj.options.text.deleteSelectedRows, onclick:function() { obj.deleteRow(obj.getSelectedRows().length ? undefined : parseInt(y)); } }); } if (x) { if (obj.options.allowComments == true) { items.push({ type:'line' }); var title = obj.records[y][x].getAttribute('title') || ''; items.push({ title: title ? obj.options.text.editComments : obj.options.text.addComments, onclick:function() { var comment = prompt(obj.options.text.comments, title); if (comment) { obj.setComments([ x, y ], comment); } } }); if (title) { items.push({ title:obj.options.text.clearComments, onclick:function() { obj.setComments([ x, y ], ''); } }); } } } } // Line items.push({ type:'line' }); // Copy items.push({ title:obj.options.text.copy, shortcut:'Ctrl + C', onclick:function() { obj.copy(true); } }); // Paste if (navigator && navigator.clipboard) { items.push({ title:obj.options.text.paste, shortcut:'Ctrl + V', onclick:function() { if (obj.selectedCell) { navigator.clipboard.readText().then(function(text) { if (text) { jexcel.current.paste(obj.selectedCell[0], obj.selectedCell[1], text); } }); } } }); } // Save if (obj.options.allowExport) { items.push({ title: obj.options.text.saveAs, shortcut: 'Ctrl + S', onclick: function () { obj.download(); } }); } // About if (obj.options.about) { items.push({ title:obj.options.text.about, onclick:function() { if (obj.options.about === true) { alert(Version().print()); } else { alert(obj.options.about); } } }); } return items; } } obj.scrollControls = function(e) { obj.wheelControls(); if (obj.options.freezeColumns > 0 && obj.content.scrollLeft != scrollLeft) { obj.updateFreezePosition(); } // Close editor if (obj.options.lazyLoading == true || obj.options.tableOverflow == true) { if (obj.edition && e.target.className.substr(0,9) != 'jdropdown') { obj.closeEditor(obj.edition[0], true); } } } obj.wheelControls = function(e) { if (obj.options.lazyLoading == true) { if (jexcel.timeControlLoading == null) { jexcel.timeControlLoading = setTimeout(function() { if (obj.content.scrollTop + obj.content.clientHeight >= obj.content.scrollHeight - 10) { if (obj.loadDown()) { if (obj.content.scrollTop + obj.content.clientHeight > obj.content.scrollHeight - 10) { obj.content.scrollTop = obj.content.scrollTop - obj.content.clientHeight; } obj.updateCornerPosition(); } } else if (obj.content.scrollTop <= obj.content.clientHeight) { if (obj.loadUp()) { if (obj.content.scrollTop < 10) { obj.content.scrollTop = obj.content.scrollTop + obj.content.clientHeight; } obj.updateCornerPosition(); } } jexcel.timeControlLoading = null; }, 100); } } } // Get width of all freezed cells together obj.getFreezeWidth = function() { var width = 0; if (obj.options.freezeColumns > 0) { for (var i = 0; i < obj.options.freezeColumns; i++) { width += parseInt(obj.options.columns[i].width); } } return width; } var scrollLeft = 0; obj.updateFreezePosition = function() { scrollLeft = obj.content.scrollLeft; var width = 0; if (scrollLeft > 50) { for (var i = 0; i < obj.options.freezeColumns; i++) { if (i > 0) { // Must check if the previous column is hidden or not to determin whether the width shoule be added or not! if (obj.options.columns[i-1].type !== "hidden") { width += parseInt(obj.options.columns[i-1].width); } } obj.headers[i].classList.add('jexcel_freezed'); obj.headers[i].style.left = width + 'px'; for (var j = 0; j < obj.rows.length; j++) { if (obj.rows[j] && obj.records[j][i]) { var shifted = (scrollLeft + (i > 0 ? obj.records[j][i-1].style.width : 0)) - 51 + 'px'; obj.records[j][i].classList.add('jexcel_freezed'); obj.records[j][i].style.left = shifted; } } } } else { for (var i = 0; i < obj.options.freezeColumns; i++) { obj.headers[i].classList.remove('jexcel_freezed'); obj.headers[i].style.left = ''; for (var j = 0; j < obj.rows.length; j++) { if (obj.records[j][i]) { obj.records[j][i].classList.remove('jexcel_freezed'); obj.records[j][i].style.left = ''; } } } } // Place the corner in the correct place obj.updateCornerPosition(); } el.addEventListener("DOMMouseScroll", obj.wheelControls); el.addEventListener("mousewheel", obj.wheelControls); el.jexcel = obj; el.jspreadsheet = obj; obj.init(); return obj; }); // Define dictionary jexcel.setDictionary = function(o) { jSuites.setDictionary(o); } // Define extensions jexcel.setExtensions = function(o) { var k = Object.keys(o); for (var i = 0; i < k.length; i++) { if (typeof(o[k[i]]) === 'function') { jexcel[k[i]] = o[k[i]]; if (jexcel.license && typeof(o[k[i]].license) == 'function') { o[k[i]].license(jexcel.license); } } } } /** * Formulas */ if (typeof(formula) !== 'undefined') { jexcel.formula = formula; } jexcel.version = Version; jexcel.current = null; jexcel.timeControl = null; jexcel.timeControlLoading = null; const destroyEvents = function(root) { root.removeEventListener("mouseup", jexcel.mouseUpControls); root.removeEventListener("mousedown", jexcel.mouseDownControls); root.removeEventListener("mousemove", jexcel.mouseMoveControls); root.removeEventListener("mouseover", jexcel.mouseOverControls); root.removeEventListener("dblclick", jexcel.doubleClickControls); root.removeEventListener("paste", jexcel.pasteControls); root.removeEventListener("contextmenu", jexcel.contextMenuControls); root.removeEventListener("touchstart", jexcel.touchStartControls); root.removeEventListener("touchend", jexcel.touchEndControls); root.removeEventListener("touchcancel", jexcel.touchEndControls); document.removeEventListener("keydown", jexcel.keyDownControls); } jexcel.destroy = function(element, destroyEventHandlers) { if (element.jexcel) { var root = element.jexcel.options.root ? element.jexcel.options.root : document; element.removeEventListener("DOMMouseScroll", element.jexcel.scrollControls); element.removeEventListener("mousewheel", element.jexcel.scrollControls); element.jexcel = null; element.innerHTML = ''; if (destroyEventHandlers) { destroyEvents(root); jexcel = null; } } } jexcel.build = function(root) { destroyEvents(root); root.addEventListener("mouseup", jexcel.mouseUpControls); root.addEventListener("mousedown", jexcel.mouseDownControls); root.addEventListener("mousemove", jexcel.mouseMoveControls); root.addEventListener("mouseover", jexcel.mouseOverControls); root.addEventListener("dblclick", jexcel.doubleClickControls); root.addEventListener("paste", jexcel.pasteControls); root.addEventListener("contextmenu", jexcel.contextMenuControls); root.addEventListener("touchstart", jexcel.touchStartControls); root.addEventListener("touchend", jexcel.touchEndControls); root.addEventListener("touchcancel", jexcel.touchEndControls); root.addEventListener("touchmove", jexcel.touchEndControls); document.addEventListener("keydown", jexcel.keyDownControls); } /** * Events */ jexcel.keyDownControls = function(e) { if (jexcel.current) { if (jexcel.current.edition) { if (e.which == 27) { // Escape if (jexcel.current.edition) { // Exit without saving jexcel.current.closeEditor(jexcel.current.edition[0], false); } e.preventDefault(); } else if (e.which == 13) { // Enter if (jexcel.current.options.columns[jexcel.current.edition[2]].type == 'calendar') { jexcel.current.closeEditor(jexcel.current.edition[0], true); } else if (jexcel.current.options.columns[jexcel.current.edition[2]].type == 'dropdown' || jexcel.current.options.columns[jexcel.current.edition[2]].type == 'autocomplete') { // Do nothing } else { // Alt enter -> do not close editor if ((jexcel.current.options.wordWrap == true || jexcel.current.options.columns[jexcel.current.edition[2]].wordWrap == true || jexcel.current.options.data[jexcel.current.edition[3]][jexcel.current.edition[2]].length > 200) && e.altKey) { // Add new line to the editor var editorTextarea = jexcel.current.edition[0].children[0]; var editorValue = jexcel.current.edition[0].children[0].value; var editorIndexOf = editorTextarea.selectionStart; editorValue = editorValue.slice(0, editorIndexOf) + "\n" + editorValue.slice(editorIndexOf); editorTextarea.value = editorValue; editorTextarea.focus(); editorTextarea.selectionStart = editorIndexOf + 1; editorTextarea.selectionEnd = editorIndexOf + 1; } else { // TablePress: Comment out next line (leaving the cell when hitting Enter), so that instead a line break is inserted. // jexcel.current.edition[0].children[0].blur(); } } } else if (e.which == 9) { // Tab if (['calendar', 'html'].includes( jexcel.current.options.columns[jexcel.current.edition[2]].type)) { jexcel.current.closeEditor(jexcel.current.edition[0], true); } else { jexcel.current.edition[0].children[0].blur(); } } } if (! jexcel.current.edition && jexcel.current.selectedCell) { // Which key if (e.which == 37) { jexcel.current.left(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 39) { jexcel.current.right(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 38) { jexcel.current.up(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 40) { jexcel.current.down(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 36) { jexcel.current.first(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 35) { jexcel.current.last(e.shiftKey, e.ctrlKey); e.preventDefault(); } else if (e.which == 46 || e.which == 8) { // Delete if (jexcel.current.options.editable == true) { if (jexcel.current.selectedRow) { if (jexcel.current.options.allowDeleteRow == true) { if (confirm(jexcel.current.options.text.areYouSureToDeleteTheSelectedRows)) { jexcel.current.deleteRow(); } } } else if (jexcel.current.selectedHeader) { if (jexcel.current.options.allowDeleteColumn == true) { if (confirm(jexcel.current.options.text.areYouSureToDeleteTheSelectedColumns)) { jexcel.current.deleteColumn(); } } } else { // Change value jexcel.current.setValue(jexcel.current.highlighted, ''); } } } else if (e.which == 13) { // Move cursor if (e.shiftKey) { jexcel.current.up(); } else { if (jexcel.current.options.allowInsertRow == true) { if (jexcel.current.options.allowManualInsertRow == true) { if (jexcel.current.selectedCell[1] == jexcel.current.options.data.length - 1) { // New record in case selectedCell in the last row jexcel.current.insertRow(); } } } jexcel.current.down(); } e.preventDefault(); } else if (e.which == 9) { // Tab if (e.shiftKey) { // TablePress: Instead of stopping at the left edge when tabbing, go to the previous row's last cell. // jexcel.current.left(); if ( jexcel.current.selectedCell[0] == 0 ) { jexcel.current.up(); jexcel.current.last(); } else { jexcel.current.left(); } } else { if (jexcel.current.options.allowInsertColumn == true) { if (jexcel.current.options.allowManualInsertColumn == true) { if (jexcel.current.selectedCell[0] == jexcel.current.options.data[0].length - 1) { // New record in case selectedCell in the last column jexcel.current.insertColumn(); } } } // TablePress: Instead of stopping at the right edge when tabbing, go to the next row's first cell. // jexcel.current.right(); if ( jexcel.current.selectedCell[0] == jexcel.current.options.data[0].length - 1 ) { jexcel.current.down(); jexcel.current.first(); } else { jexcel.current.right(); } } e.preventDefault(); } else { if ((e.ctrlKey || e.metaKey) && ! e.shiftKey) { if (e.which == 65) { // Ctrl + A jexcel.current.selectAll(); e.preventDefault(); } else if (e.which == 83) { // Ctrl + S jexcel.current.download(); e.preventDefault(); } else if (e.which == 89) { // Ctrl + Y jexcel.current.redo(); e.preventDefault(); } else if (e.which == 90) { // Ctrl + Z jexcel.current.undo(); e.preventDefault(); } else if (e.which == 67) { // Ctrl + C jexcel.current.copy(true); e.preventDefault(); } else if (e.which == 88) { // Ctrl + X if (jexcel.current.options.editable == true) { jexcel.cutControls(); } else { jexcel.copyControls(); } e.preventDefault(); } else if (e.which == 86) { // Ctrl + V jexcel.pasteControls(); } } else { if (jexcel.current.selectedCell) { if (jexcel.current.options.editable == true) { var rowId = jexcel.current.selectedCell[1]; var columnId = jexcel.current.selectedCell[0]; // If is not readonly if (jexcel.current.options.columns[columnId].type != 'readonly') { // Characters able to start a edition if (e.keyCode == 32) { // Space e.preventDefault() if (jexcel.current.options.columns[columnId].type == 'checkbox' || jexcel.current.options.columns[columnId].type == 'radio') { jexcel.current.setCheckRadioValue(); } else { // Start edition jexcel.current.openEditor(jexcel.current.records[rowId][columnId], true); } } else if (e.keyCode == 113) { // Start edition with current content F2 jexcel.current.openEditor(jexcel.current.records[rowId][columnId], false); } else if ((e.keyCode == 8) || (e.keyCode >= 48 && e.keyCode <= 57) || (e.keyCode >= 96 && e.keyCode <= 111) || (e.keyCode >= 187 && e.keyCode <= 190) || ((String.fromCharCode(e.keyCode) == e.key || String.fromCharCode(e.keyCode).toLowerCase() == e.key.toLowerCase()) && jexcel.validLetter(String.fromCharCode(e.keyCode)))) { // Start edition jexcel.current.openEditor(jexcel.current.records[rowId][columnId], true); // Prevent entries in the calendar if (jexcel.current.options.columns[columnId].type == 'calendar') { e.preventDefault(); } } } } } } } } else { if (e.target.classList.contains('jexcel_search')) { if (jexcel.timeControl) { clearTimeout(jexcel.timeControl); } jexcel.timeControl = setTimeout(function() { jexcel.current.search(e.target.value); }, 200); } } } } jexcel.isMouseAction = false; jexcel.mouseDownControls = function(e) { e = e || window.event; if (e.buttons) { var mouseButton = e.buttons; } else if (e.button) { var mouseButton = e.button; } else { var mouseButton = e.which; } // Get elements var jexcelTable = jexcel.getElement(e.target); if (jexcelTable[0]) { if (jexcel.current != jexcelTable[0].jexcel) { if (jexcel.current) { if (jexcel.current.edition) { jexcel.current.closeEditor(jexcel.current.edition[0], true); } jexcel.current.resetSelection(); } jexcel.current = jexcelTable[0].jexcel; } } else { if (jexcel.current) { if (jexcel.current.edition) { jexcel.current.closeEditor(jexcel.current.edition[0], true); } jexcel.current.resetSelection(true); jexcel.current = null; } } if (jexcel.current && mouseButton == 1) { if (e.target.classList.contains('jexcel_selectall')) { if (jexcel.current) { jexcel.current.selectAll(); } } else if (e.target.classList.contains('jexcel_corner')) { if (jexcel.current.options.editable == true) { jexcel.current.selectedCorner = true; } } else { // Header found if (jexcelTable[1] == 1) { var columnId = e.target.getAttribute('data-x'); if (columnId) { // Update cursor var info = e.target.getBoundingClientRect(); if (jexcel.current.options.columnResize == true && info.width - e.offsetX < 12) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region. // Resize helper jexcel.current.resizing = { mousePosition: e.pageX, column: columnId, width: info.width, }; // Border indication jexcel.current.headers[columnId].classList.add('resizing'); for (var j = 0; j < jexcel.current.records.length; j++) { if (jexcel.current.records[j][columnId]) { jexcel.current.records[j][columnId].classList.add('resizing'); } } } else if (jexcel.current.options.columnDrag == true && info.height - e.offsetY < 12) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region. if (jexcel.current.isColMerged(columnId).length) { console.error('Jspreadsheet: This column is part of a merged cell.'); } else { // Reset selection jexcel.current.resetSelection(); // Drag helper jexcel.current.dragging = { element: e.target, column: columnId, destination: columnId, }; // Border indication jexcel.current.headers[columnId].classList.add('dragging'); for (var j = 0; j < jexcel.current.records.length; j++) { if (jexcel.current.records[j][columnId]) { jexcel.current.records[j][columnId].classList.add('dragging'); } } } } else { if (jexcel.current.selectedHeader && (e.shiftKey || e.ctrlKey)) { var o = jexcel.current.selectedHeader; var d = columnId; } else { // Press to rename if (jexcel.current.selectedHeader == columnId && jexcel.current.options.allowRenameColumn == true) { jexcel.timeControl = setTimeout(function() { jexcel.current.setHeader(columnId); }, 800); } // Keep track of which header was selected first jexcel.current.selectedHeader = columnId; // Update selection single column var o = columnId; var d = columnId; } // Update selection jexcel.current.updateSelectionFromCoords(o, 0, d, jexcel.current.options.data.length - 1); } } else { if (e.target.parentNode.classList.contains('jexcel_nested')) { if (e.target.getAttribute('data-column')) { var column = e.target.getAttribute('data-column').split(','); var c1 = parseInt(column[0]); var c2 = parseInt(column[column.length-1]); } else { var c1 = 0; var c2 = jexcel.current.options.columns.length - 1; } jexcel.current.updateSelectionFromCoords(c1, 0, c2, jexcel.current.options.data.length - 1); } } } else { jexcel.current.selectedHeader = false; } // Body found if (jexcelTable[1] == 2) { var rowId = e.target.getAttribute('data-y'); if (e.target.classList.contains('jexcel_row')) { var info = e.target.getBoundingClientRect(); if (jexcel.current.options.rowResize == true && info.height - e.offsetY < 12) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region. // Resize helper jexcel.current.resizing = { element: e.target.parentNode, mousePosition: e.pageY, row: rowId, height: info.height, }; // Border indication e.target.parentNode.classList.add('resizing'); } else if (jexcel.current.options.rowDrag == true && info.width - e.offsetX < 18) { // TablePress: Changed from 6 to 18 pixels to increase draggable mouse region. if (jexcel.current.isRowMerged(rowId).length) { console.error('Jspreadsheet: This row is part of a merged cell'); } else if (jexcel.current.options.search == true && jexcel.current.results) { console.error('Jspreadsheet: Please clear your search before perform this action'); } else { // Reset selection jexcel.current.resetSelection(); // Drag helper jexcel.current.dragging = { element: e.target.parentNode, row:rowId, destination:rowId, }; // Border indication e.target.parentNode.classList.add('dragging'); } } else { if (jexcel.current.selectedRow && (e.shiftKey || e.ctrlKey)) { var o = jexcel.current.selectedRow; var d = rowId; } else { // Keep track of which header was selected first jexcel.current.selectedRow = rowId; // Update selection single column var o = rowId; var d = rowId; } // Update selection jexcel.current.updateSelectionFromCoords(0, o, jexcel.current.options.data[0].length - 1, d); } } else { // Jclose if (e.target.classList.contains('jclose') && e.target.clientWidth - e.offsetX < 50 && e.offsetY < 50) { jexcel.current.closeEditor(jexcel.current.edition[0], true); } else { var getCellCoords = function(element) { var x = element.getAttribute('data-x'); var y = element.getAttribute('data-y'); if (x && y) { return [x, y]; } else { if (element.parentNode) { return getCellCoords(element.parentNode); } } }; var position = getCellCoords(e.target); if (position) { var columnId = position[0]; var rowId = position[1]; // Close edition if (jexcel.current.edition) { if (jexcel.current.edition[2] != columnId || jexcel.current.edition[3] != rowId) { jexcel.current.closeEditor(jexcel.current.edition[0], true); } } if (! jexcel.current.edition) { // Update cell selection if (e.shiftKey) { jexcel.current.updateSelectionFromCoords(jexcel.current.selectedCell[0], jexcel.current.selectedCell[1], columnId, rowId); } else { jexcel.current.updateSelectionFromCoords(columnId, rowId); } } // No full row selected jexcel.current.selectedHeader = null; jexcel.current.selectedRow = null; } } } } else { jexcel.current.selectedRow = false; } // Pagination if (e.target.classList.contains('jexcel_page')) { if (e.target.textContent == '<') { jexcel.current.page(0); } else if (e.target.textContent == '>') { jexcel.current.page(e.target.getAttribute('title') - 1); } else { jexcel.current.page(e.target.textContent - 1); } } } if (jexcel.current.edition) { jexcel.isMouseAction = false; } else { jexcel.isMouseAction = true; } } else { jexcel.isMouseAction = false; } } jexcel.mouseUpControls = function(e) { if (jexcel.current) { // Update cell size if (jexcel.current.resizing) { // Columns to be updated if (jexcel.current.resizing.column) { // New width var newWidth = jexcel.current.colgroup[jexcel.current.resizing.column].getAttribute('width'); // Columns var columns = jexcel.current.getSelectedColumns(); if (columns.length > 1) { var currentWidth = []; for (var i = 0; i < columns.length; i++) { currentWidth.push(parseInt(jexcel.current.colgroup[columns[i]].getAttribute('width'))); } // Previous width var index = columns.indexOf(parseInt(jexcel.current.resizing.column)); currentWidth[index] = jexcel.current.resizing.width; jexcel.current.setWidth(columns, newWidth, currentWidth); } else { jexcel.current.setWidth(jexcel.current.resizing.column, newWidth, jexcel.current.resizing.width); } // Remove border jexcel.current.headers[jexcel.current.resizing.column].classList.remove('resizing'); for (var j = 0; j < jexcel.current.records.length; j++) { if (jexcel.current.records[j][jexcel.current.resizing.column]) { jexcel.current.records[j][jexcel.current.resizing.column].classList.remove('resizing'); } } } else { // Remove Class jexcel.current.rows[jexcel.current.resizing.row].children[0].classList.remove('resizing'); var newHeight = jexcel.current.rows[jexcel.current.resizing.row].getAttribute('height'); jexcel.current.setHeight(jexcel.current.resizing.row, newHeight, jexcel.current.resizing.height); // Remove border jexcel.current.resizing.element.classList.remove('resizing'); } // Reset resizing helper jexcel.current.resizing = null; } else if (jexcel.current.dragging) { // Reset dragging helper if (jexcel.current.dragging) { if (jexcel.current.dragging.column) { // Target var columnId = e.target.getAttribute('data-x'); // Remove move style jexcel.current.headers[jexcel.current.dragging.column].classList.remove('dragging'); for (var j = 0; j < jexcel.current.rows.length; j++) { if (jexcel.current.records[j][jexcel.current.dragging.column]) { jexcel.current.records[j][jexcel.current.dragging.column].classList.remove('dragging'); } } for (var i = 0; i < jexcel.current.headers.length; i++) { jexcel.current.headers[i].classList.remove('dragging-left'); jexcel.current.headers[i].classList.remove('dragging-right'); } // Update position if (columnId) { if (jexcel.current.dragging.column != jexcel.current.dragging.destination) { jexcel.current.moveColumn(jexcel.current.dragging.column, jexcel.current.dragging.destination); } } } else { if (jexcel.current.dragging.element.nextSibling) { var position = parseInt(jexcel.current.dragging.element.nextSibling.getAttribute('data-y')); if (jexcel.current.dragging.row < position) { position -= 1; } } else { var position = parseInt(jexcel.current.dragging.element.previousSibling.getAttribute('data-y')); } if (jexcel.current.dragging.row != jexcel.current.dragging.destination) { jexcel.current.moveRow(jexcel.current.dragging.row, position, true); } jexcel.current.dragging.element.classList.remove('dragging'); } jexcel.current.dragging = null; } } else { // Close any corner selection if (jexcel.current.selectedCorner) { jexcel.current.selectedCorner = false; // Data to be copied if (jexcel.current.selection.length > 0) { // Copy data jexcel.current.copyData(jexcel.current.selection[0], jexcel.current.selection[jexcel.current.selection.length - 1]); // Remove selection jexcel.current.removeCopySelection(); } } } } // Clear any time control if (jexcel.timeControl) { clearTimeout(jexcel.timeControl); jexcel.timeControl = null; } // Mouse up jexcel.isMouseAction = false; } // Mouse move controls jexcel.mouseMoveControls = function(e) { e = e || window.event; if (e.buttons) { var mouseButton = e.buttons; } else if (e.button) { var mouseButton = e.button; } else { var mouseButton = e.which; } if (! mouseButton) { jexcel.isMouseAction = false; } if (jexcel.current) { if (jexcel.isMouseAction == true) { // Resizing is ongoing if (jexcel.current.resizing) { if (jexcel.current.resizing.column) { var width = e.pageX - jexcel.current.resizing.mousePosition; if (jexcel.current.resizing.width + width > 0) { var tempWidth = jexcel.current.resizing.width + width; jexcel.current.colgroup[jexcel.current.resizing.column].setAttribute('width', tempWidth); jexcel.current.updateCornerPosition(); } } else { var height = e.pageY - jexcel.current.resizing.mousePosition; if (jexcel.current.resizing.height + height > 0) { var tempHeight = jexcel.current.resizing.height + height; jexcel.current.rows[jexcel.current.resizing.row].setAttribute('height', tempHeight); // TablePress: Add a custom CSS variable to adjust the number of shown lines of text to the row height. jexcel.current.rows[jexcel.current.resizing.row].style.setProperty( '--table-editor-line-clamp', Math.floor( (tempHeight-10) / 14 ) ); jexcel.current.updateCornerPosition(); } } } else if (jexcel.current.dragging) { if (jexcel.current.dragging.column) { var columnId = e.target.getAttribute('data-x'); if (columnId) { if (jexcel.current.isColMerged(columnId).length) { console.error('Jspreadsheet: This column is part of a merged cell.'); } else { for (var i = 0; i < jexcel.current.headers.length; i++) { jexcel.current.headers[i].classList.remove('dragging-left'); jexcel.current.headers[i].classList.remove('dragging-right'); } if (jexcel.current.dragging.column == columnId) { jexcel.current.dragging.destination = parseInt(columnId); } else { if (e.target.clientWidth / 2 > e.offsetX) { if (jexcel.current.dragging.column < columnId) { jexcel.current.dragging.destination = parseInt(columnId) - 1; } else { jexcel.current.dragging.destination = parseInt(columnId); } jexcel.current.headers[columnId].classList.add('dragging-left'); } else { if (jexcel.current.dragging.column < columnId) { jexcel.current.dragging.destination = parseInt(columnId); } else { jexcel.current.dragging.destination = parseInt(columnId) + 1; } jexcel.current.headers[columnId].classList.add('dragging-right'); } } } } } else { var rowId = e.target.getAttribute('data-y'); if (rowId) { if (jexcel.current.isRowMerged(rowId).length) { console.error('Jspreadsheet: This row is part of a merged cell.'); } else { var target = (e.target.clientHeight / 2 > e.offsetY) ? e.target.parentNode.nextSibling : e.target.parentNode; if (jexcel.current.dragging.element != target) { e.target.parentNode.parentNode.insertBefore(jexcel.current.dragging.element, target); jexcel.current.dragging.destination = Array.prototype.indexOf.call(jexcel.current.dragging.element.parentNode.children, jexcel.current.dragging.element); } } } } } } else { var x = e.target.getAttribute('data-x'); var y = e.target.getAttribute('data-y'); var rect = e.target.getBoundingClientRect(); if (jexcel.current.cursor) { jexcel.current.cursor.style.cursor = ''; jexcel.current.cursor = null; } if (e.target.parentNode.parentNode && e.target.parentNode.parentNode.className) { if (e.target.parentNode.parentNode.classList.contains('resizable')) { if (e.target && x && ! y && (rect.width - (e.clientX - rect.left) < 12)) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region. jexcel.current.cursor = e.target; jexcel.current.cursor.style.cursor = 'col-resize'; } else if (e.target && ! x && y && (rect.height - (e.clientY - rect.top) < 12)) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region. jexcel.current.cursor = e.target; jexcel.current.cursor.style.cursor = 'row-resize'; } } if (e.target.parentNode.parentNode.classList.contains('draggable')) { if (e.target && ! x && y && (rect.width - (e.clientX - rect.left) < 18)) { // TablePress: Changed from 6 to 18 pixels to increase draggable mouse region. jexcel.current.cursor = e.target; jexcel.current.cursor.style.cursor = 'move'; } else if (e.target && x && ! y && (rect.height - (e.clientY - rect.top) < 12) && (e.clientX - rect.left < rect.width - 24)) { // TablePress: Changed from 6 to 12 pixels to increase draggable mouse region, and extended to not span full width. jexcel.current.cursor = e.target; jexcel.current.cursor.style.cursor = 'move'; } } } } } } jexcel.mouseOverControls = function(e) { e = e || window.event; if (e.buttons) { var mouseButton = e.buttons; } else if (e.button) { var mouseButton = e.button; } else { var mouseButton = e.which; } if (! mouseButton) { jexcel.isMouseAction = false; } if (jexcel.current && jexcel.isMouseAction == true) { // Get elements var jexcelTable = jexcel.getElement(e.target); if (jexcelTable[0]) { // Avoid cross reference if (jexcel.current != jexcelTable[0].jexcel) { if (jexcel.current) { return false; } } var columnId = e.target.getAttribute('data-x'); var rowId = e.target.getAttribute('data-y'); if (jexcel.current.resizing || jexcel.current.dragging) { } else { // Header found if (jexcelTable[1] == 1) { if (jexcel.current.selectedHeader) { var columnId = e.target.getAttribute('data-x'); var o = jexcel.current.selectedHeader; var d = columnId; // Update selection jexcel.current.updateSelectionFromCoords(o, 0, d, jexcel.current.options.data.length - 1); } } // Body found if (jexcelTable[1] == 2) { if (e.target.classList.contains('jexcel_row')) { if (jexcel.current.selectedRow) { var o = jexcel.current.selectedRow; var d = rowId; // Update selection jexcel.current.updateSelectionFromCoords(0, o, jexcel.current.options.data[0].length - 1, d); } } else { // Do not select edtion is in progress if (! jexcel.current.edition) { if (columnId && rowId) { if (jexcel.current.selectedCorner) { jexcel.current.updateCopySelection(columnId, rowId); } else { if (jexcel.current.selectedCell) { jexcel.current.updateSelectionFromCoords(jexcel.current.selectedCell[0], jexcel.current.selectedCell[1], columnId, rowId); } } } } } } } } } // Clear any time control if (jexcel.timeControl) { clearTimeout(jexcel.timeControl); jexcel.timeControl = null; } } /** * Double click event handler: controls the double click in the corner, cell edition or column re-ordering. */ jexcel.doubleClickControls = function(e) { // Jexcel is selected if (jexcel.current) { // Corner action if (e.target.classList.contains('jexcel_corner')) { // Any selected cells if (jexcel.current.highlighted.length > 0) { // Copy from this var x1 = jexcel.current.highlighted[0].getAttribute('data-x'); var y1 = parseInt(jexcel.current.highlighted[jexcel.current.highlighted.length - 1].getAttribute('data-y')) + 1; // Until this var x2 = jexcel.current.highlighted[jexcel.current.highlighted.length - 1].getAttribute('data-x'); var y2 = jexcel.current.records.length - 1 // Execute copy jexcel.current.copyData(jexcel.current.records[y1][x1], jexcel.current.records[y2][x2]); } } else if (e.target.classList.contains('jexcel_column_filter')) { // Column var columnId = e.target.getAttribute('data-x'); // Open filter jexcel.current.openFilter(columnId); } else { // Get table var jexcelTable = jexcel.getElement(e.target); // Double click over header if (jexcelTable[1] == 1 && jexcel.current.options.columnSorting == true) { // Check valid column header coords var columnId = e.target.getAttribute('data-x'); if (columnId) { jexcel.current.orderBy(columnId); } } // Double click over body if (jexcelTable[1] == 2 && jexcel.current.options.editable == true) { if (! jexcel.current.edition) { var getCellCoords = function(element) { if (element.parentNode) { var x = element.getAttribute('data-x'); var y = element.getAttribute('data-y'); if (x && y) { return element; } else { return getCellCoords(element.parentNode); } } } var cell = getCellCoords(e.target); if (cell && cell.classList.contains('highlight')) { jexcel.current.openEditor(cell); } } } } } } jexcel.copyControls = function(e) { if (jexcel.current && jexcel.copyControls.enabled) { if (! jexcel.current.edition) { jexcel.current.copy(true); } } } jexcel.cutControls = function(e) { if (jexcel.current) { if (! jexcel.current.edition) { jexcel.current.copy(true); if (jexcel.current.options.editable == true) { jexcel.current.setValue(jexcel.current.highlighted, ''); } } } } jexcel.pasteControls = function(e) { if (jexcel.current && jexcel.current.selectedCell) { if (! jexcel.current.edition) { if (jexcel.current.options.editable == true) { if (e && e.clipboardData) { jexcel.current.paste(jexcel.current.selectedCell[0], jexcel.current.selectedCell[1], e.clipboardData.getData('text')); e.preventDefault(); } else if (window.clipboardData) { jexcel.current.paste(jexcel.current.selectedCell[0], jexcel.current.selectedCell[1], window.clipboardData.getData('text')); } } } } } jexcel.contextMenuControls = function(e) { e = e || window.event; if ("buttons" in e) { var mouseButton = e.buttons; } else { var mouseButton = e.which || e.button; } if (jexcel.current) { // TablePress: Don't prevent showing the context menu when editing a focussed cell. //if (jexcel.current.edition) { //e.preventDefault(); //} else if (jexcel.current.options.contextMenu) { jexcel.current.contextMenu.contextmenu.close(); if (jexcel.current) { // TablePress: Take into account the inserted DIV element and the editor TEXTAREA when opening the context menu. var target = (e.target.tagName === 'DIV' || e.target.tagName === 'TEXTAREA') ? e.target.parentNode : e.target; var x = target.getAttribute('data-x'); var y = target.getAttribute('data-y'); if (x || y) { /* // TablePress: Fix the comparison of the clicked cell with the selected cell range (e.g. when a right click happens outside of the selected range). if ((x < parseInt(jexcel.current.selectedCell[0])) || (x > parseInt(jexcel.current.selectedCell[2])) || (y < parseInt(jexcel.current.selectedCell[1])) || (y > parseInt(jexcel.current.selectedCell[3]))) */ const selection = jexcel.current.selectedCell; const selected_cell = { l: Math.min( parseInt( selection[0] ), parseInt( selection[2] ) ), r: Math.max( parseInt( selection[0] ), parseInt( selection[2] ) ), t: Math.min( parseInt( selection[1] ), parseInt( selection[3] ) ), b: Math.max( parseInt( selection[1] ), parseInt( selection[3] ) ), }; if ( x < selected_cell.l || x > selected_cell.r || y < selected_cell.t || y > selected_cell.b ) { jexcel.current.updateSelectionFromCoords(x, y, x, y); } // TablePress: End of change. // Table found var items = jexcel.current.options.contextMenu(jexcel.current, x, y, e); // The id is depending on header and body jexcel.current.contextMenu.contextmenu.open(e, items); // Avoid the real one e.preventDefault(); } } } } } jexcel.touchStartControls = function(e) { var jexcelTable = jexcel.getElement(e.target); if (jexcelTable[0]) { if (jexcel.current != jexcelTable[0].jexcel) { if (jexcel.current) { jexcel.current.resetSelection(); } jexcel.current = jexcelTable[0].jexcel; } } else { if (jexcel.current) { jexcel.current.resetSelection(); jexcel.current = null; } } if (jexcel.current) { if (! jexcel.current.edition) { var columnId = e.target.getAttribute('data-x'); var rowId = e.target.getAttribute('data-y'); if (columnId && rowId) { jexcel.current.updateSelectionFromCoords(columnId, rowId); jexcel.timeControl = setTimeout(function() { // Keep temporary reference to the element if (jexcel.current.options.columns[columnId].type == 'color') { jexcel.tmpElement = null; } else { jexcel.tmpElement = e.target; } jexcel.current.openEditor(e.target, false, e); }, 500); } } } } jexcel.touchEndControls = function(e) { // Clear any time control if (jexcel.timeControl) { clearTimeout(jexcel.timeControl); jexcel.timeControl = null; // Element if (jexcel.tmpElement && jexcel.tmpElement.children[0].tagName == 'INPUT') { jexcel.tmpElement.children[0].focus(); } jexcel.tmpElement = null; } } /** * Jexcel extensions */ jexcel.tabs = function(tabs, result) { var instances = []; // Create tab container if (! tabs.classList.contains('jexcel_tabs')) { tabs.innerHTML = ''; tabs.classList.add('jexcel_tabs') tabs.jexcel = []; var div = document.createElement('div'); var headers = tabs.appendChild(div); var div = document.createElement('div'); var content = tabs.appendChild(div); } else { var headers = tabs.children[0]; var content = tabs.children[1]; } var spreadsheet = [] var link = []; for (var i = 0; i < result.length; i++) { // Spreadsheet container spreadsheet[i] = document.createElement('div'); spreadsheet[i].classList.add('jexcel_tab'); var worksheet = jexcel(spreadsheet[i], result[i]); content.appendChild(spreadsheet[i]); instances[i] = tabs.jexcel.push(worksheet); // Tab link link[i] = document.createElement('div'); link[i].classList.add('jexcel_tab_link'); link[i].setAttribute('data-spreadsheet', tabs.jexcel.length-1); link[i].innerHTML = result[i].sheetName; link[i].onclick = function() { for (var j = 0; j < headers.children.length; j++) { headers.children[j].classList.remove('selected'); content.children[j].style.display = 'none'; } var i = this.getAttribute('data-spreadsheet'); content.children[i].style.display = 'block'; headers.children[i].classList.add('selected') } headers.appendChild(link[i]); } // First tab for (var j = 0; j < headers.children.length; j++) { headers.children[j].classList.remove('selected'); content.children[j].style.display = 'none'; } headers.children[headers.children.length - 1].classList.add('selected'); content.children[headers.children.length - 1].style.display = 'block'; return instances; } // Compability to older versions jexcel.createTabs = jexcel.tabs; jexcel.fromSpreadsheet = function(file, __callback) { var convert = function(workbook) { var spreadsheets = []; workbook.SheetNames.forEach(function(sheetName) { var spreadsheet = {}; spreadsheet.rows = []; spreadsheet.columns = []; spreadsheet.data = []; spreadsheet.style = {}; spreadsheet.sheetName = sheetName; // Column widths var temp = workbook.Sheets[sheetName]['!cols']; if (temp && temp.length) { for (var i = 0; i < temp.length; i++) { spreadsheet.columns[i] = {}; if (temp[i] && temp[i].wpx) { spreadsheet.columns[i].width = temp[i].wpx + 'px'; } } } // Rows heights var temp = workbook.Sheets[sheetName]['!rows']; if (temp && temp.length) { for (var i = 0; i < temp.length; i++) { if (temp[i] && temp[i].hpx) { spreadsheet.rows[i] = {}; spreadsheet.rows[i].height = temp[i].hpx + 'px'; } } } // Merge cells var temp = workbook.Sheets[sheetName]['!merges']; if (temp && temp.length > 0) { spreadsheet.mergeCells = []; for (var i = 0; i < temp.length; i++) { var x1 = temp[i].s.c; var y1 = temp[i].s.r; var x2 = temp[i].e.c; var y2 = temp[i].e.r; var key = jexcel.getColumnNameFromId([x1,y1]); spreadsheet.mergeCells[key] = [ x2-x1+1, y2-y1+1 ]; } } // Data container var max_x = 0; var max_y = 0; var temp = Object.keys(workbook.Sheets[sheetName]); for (var i = 0; i < temp.length; i++) { if (temp[i].substr(0,1) != '!') { var cell = workbook.Sheets[sheetName][temp[i]]; var info = jexcel.getIdFromColumnName(temp[i], true); if (! spreadsheet.data[info[1]]) { spreadsheet.data[info[1]] = []; } spreadsheet.data[info[1]][info[0]] = cell.f ? '=' + cell.f : cell.w; if (max_x < info[0]) { max_x = info[0]; } if (max_y < info[1]) { max_y = info[1]; } // Style if (cell.style && Object.keys(cell.style).length > 0) { spreadsheet.style[temp[i]] = cell.style; } if (cell.s && cell.s.fgColor) { if (spreadsheet.style[temp[i]]) { spreadsheet.style[temp[i]] += ';'; } spreadsheet.style[temp[i]] += 'background-color:#' + cell.s.fgColor.rgb; } } } var numColumns = spreadsheet.columns; for (var j = 0; j <= max_y; j++) { for (var i = 0; i <= max_x; i++) { if (! spreadsheet.data[j]) { spreadsheet.data[j] = []; } if (! spreadsheet.data[j][i]) { if (numColumns < i) { spreadsheet.data[j][i] = ''; } } } } spreadsheets.push(spreadsheet); }); return spreadsheets; } var oReq; oReq = new XMLHttpRequest(); oReq.open("GET", file, true); if(typeof Uint8Array !== 'undefined') { oReq.responseType = "arraybuffer"; oReq.onload = function(e) { var arraybuffer = oReq.response; var data = new Uint8Array(arraybuffer); var wb = XLSX.read(data, {type:"array", cellFormula:true, cellStyles:true }); __callback(convert(wb)) }; } else { oReq.setRequestHeader("Accept-Charset", "x-user-defined"); oReq.onreadystatechange = function() { if(oReq.readyState == 4 && oReq.status == 200) { var ff = convertResponseBodyToText(oReq.responseBody); var wb = XLSX.read(ff, {type:"binary", cellFormula:true, cellStyles:true }); __callback(convert(wb)) }}; } oReq.send(); } /** * Valid international letter */ jexcel.validLetter = function (text) { var regex = /([\u0041-\u005A\u0061-\u007A\u00AA\u00B5\u00BA\u00C0-\u00D6\u00D8-\u00F6\u00F8-\u02C1\u02C6-\u02D1\u02E0-\u02E4\u02EC\u02EE\u0370-\u0374\u0376\u0377\u037A-\u037D\u0386\u0388-\u038A\u038C\u038E-\u03A1\u03A3-\u03F5\u03F7-\u0481\u048A-\u0527\u0531-\u0556\u0559\u0561-\u0587\u05D0-\u05EA\u05F0-\u05F2\u0620-\u064A\u066E\u066F\u0671-\u06D3\u06D5\u06E5\u06E6\u06EE\u06EF\u06FA-\u06FC\u06FF\u0710\u0712-\u072F\u074D-\u07A5\u07B1\u07CA-\u07EA\u07F4\u07F5\u07FA\u0800-\u0815\u081A\u0824\u0828\u0840-\u0858\u08A0\u08A2-\u08AC\u0904-\u0939\u093D\u0950\u0958-\u0961\u0971-\u0977\u0979-\u097F\u0985-\u098C\u098F\u0990\u0993-\u09A8\u09AA-\u09B0\u09B2\u09B6-\u09B9\u09BD\u09CE\u09DC\u09DD\u09DF-\u09E1\u09F0\u09F1\u0A05-\u0A0A\u0A0F\u0A10\u0A13-\u0A28\u0A2A-\u0A30\u0A32\u0A33\u0A35\u0A36\u0A38\u0A39\u0A59-\u0A5C\u0A5E\u0A72-\u0A74\u0A85-\u0A8D\u0A8F-\u0A91\u0A93-\u0AA8\u0AAA-\u0AB0\u0AB2\u0AB3\u0AB5-\u0AB9\u0ABD\u0AD0\u0AE0\u0AE1\u0B05-\u0B0C\u0B0F\u0B10\u0B13-\u0B28\u0B2A-\u0B30\u0B32\u0B33\u0B35-\u0B39\u0B3D\u0B5C\u0B5D\u0B5F-\u0B61\u0B71\u0B83\u0B85-\u0B8A\u0B8E-\u0B90\u0B92-\u0B95\u0B99\u0B9A\u0B9C\u0B9E\u0B9F\u0BA3\u0BA4\u0BA8-\u0BAA\u0BAE-\u0BB9\u0BD0\u0C05-\u0C0C\u0C0E-\u0C10\u0C12-\u0C28\u0C2A-\u0C33\u0C35-\u0C39\u0C3D\u0C58\u0C59\u0C60\u0C61\u0C85-\u0C8C\u0C8E-\u0C90\u0C92-\u0CA8\u0CAA-\u0CB3\u0CB5-\u0CB9\u0CBD\u0CDE\u0CE0\u0CE1\u0CF1\u0CF2\u0D05-\u0D0C\u0D0E-\u0D10\u0D12-\u0D3A\u0D3D\u0D4E\u0D60\u0D61\u0D7A-\u0D7F\u0D85-\u0D96\u0D9A-\u0DB1\u0DB3-\u0DBB\u0DBD\u0DC0-\u0DC6\u0E01-\u0E30\u0E32\u0E33\u0E40-\u0E46\u0E81\u0E82\u0E84\u0E87\u0E88\u0E8A\u0E8D\u0E94-\u0E97\u0E99-\u0E9F\u0EA1-\u0EA3\u0EA5\u0EA7\u0EAA\u0EAB\u0EAD-\u0EB0\u0EB2\u0EB3\u0EBD\u0EC0-\u0EC4\u0EC6\u0EDC-\u0EDF\u0F00\u0F40-\u0F47\u0F49-\u0F6C\u0F88-\u0F8C\u1000-\u102A\u103F\u1050-\u1055\u105A-\u105D\u1061\u1065\u1066\u106E-\u1070\u1075-\u1081\u108E\u10A0-\u10C5\u10C7\u10CD\u10D0-\u10FA\u10FC-\u1248\u124A-\u124D\u1250-\u1256\u1258\u125A-\u125D\u1260-\u1288\u128A-\u128D\u1290-\u12B0\u12B2-\u12B5\u12B8-\u12BE\u12C0\u12C2-\u12C5\u12C8-\u12D6\u12D8-\u1310\u1312-\u1315\u1318-\u135A\u1380-\u138F\u13A0-\u13F4\u1401-\u166C\u166F-\u167F\u1681-\u169A\u16A0-\u16EA\u1700-\u170C\u170E-\u1711\u1720-\u1731\u1740-\u1751\u1760-\u176C\u176E-\u1770\u1780-\u17B3\u17D7\u17DC\u1820-\u1877\u1880-\u18A8\u18AA\u18B0-\u18F5\u1900-\u191C\u1950-\u196D\u1970-\u1974\u1980-\u19AB\u19C1-\u19C7\u1A00-\u1A16\u1A20-\u1A54\u1AA7\u1B05-\u1B33\u1B45-\u1B4B\u1B83-\u1BA0\u1BAE\u1BAF\u1BBA-\u1BE5\u1C00-\u1C23\u1C4D-\u1C4F\u1C5A-\u1C7D\u1CE9-\u1CEC\u1CEE-\u1CF1\u1CF5\u1CF6\u1D00-\u1DBF\u1E00-\u1F15\u1F18-\u1F1D\u1F20-\u1F45\u1F48-\u1F4D\u1F50-\u1F57\u1F59\u1F5B\u1F5D\u1F5F-\u1F7D\u1F80-\u1FB4\u1FB6-\u1FBC\u1FBE\u1FC2-\u1FC4\u1FC6-\u1FCC\u1FD0-\u1FD3\u1FD6-\u1FDB\u1FE0-\u1FEC\u1FF2-\u1FF4\u1FF6-\u1FFC\u2071\u207F\u2090-\u209C\u2102\u2107\u210A-\u2113\u2115\u2119-\u211D\u2124\u2126\u2128\u212A-\u212D\u212F-\u2139\u213C-\u213F\u2145-\u2149\u214E\u2183\u2184\u2C00-\u2C2E\u2C30-\u2C5E\u2C60-\u2CE4\u2CEB-\u2CEE\u2CF2\u2CF3\u2D00-\u2D25\u2D27\u2D2D\u2D30-\u2D67\u2D6F\u2D80-\u2D96\u2DA0-\u2DA6\u2DA8-\u2DAE\u2DB0-\u2DB6\u2DB8-\u2DBE\u2DC0-\u2DC6\u2DC8-\u2DCE\u2DD0-\u2DD6\u2DD8-\u2DDE\u2E2F\u3005\u3006\u3031-\u3035\u303B\u303C\u3041-\u3096\u309D-\u309F\u30A1-\u30FA\u30FC-\u30FF\u3105-\u312D\u3131-\u318E\u31A0-\u31BA\u31F0-\u31FF\u3400-\u4DB5\u4E00-\u9FCC\uA000-\uA48C\uA4D0-\uA4FD\uA500-\uA60C\uA610-\uA61F\uA62A\uA62B\uA640-\uA66E\uA67F-\uA697\uA6A0-\uA6E5\uA717-\uA71F\uA722-\uA788\uA78B-\uA78E\uA790-\uA793\uA7A0-\uA7AA\uA7F8-\uA801\uA803-\uA805\uA807-\uA80A\uA80C-\uA822\uA840-\uA873\uA882-\uA8B3\uA8F2-\uA8F7\uA8FB\uA90A-\uA925\uA930-\uA946\uA960-\uA97C\uA984-\uA9B2\uA9CF\uAA00-\uAA28\uAA40-\uAA42\uAA44-\uAA4B\uAA60-\uAA76\uAA7A\uAA80-\uAAAF\uAAB1\uAAB5\uAAB6\uAAB9-\uAABD\uAAC0\uAAC2\uAADB-\uAADD\uAAE0-\uAAEA\uAAF2-\uAAF4\uAB01-\uAB06\uAB09-\uAB0E\uAB11-\uAB16\uAB20-\uAB26\uAB28-\uAB2E\uABC0-\uABE2\uAC00-\uD7A3\uD7B0-\uD7C6\uD7CB-\uD7FB\uF900-\uFA6D\uFA70-\uFAD9\uFB00-\uFB06\uFB13-\uFB17\uFB1D\uFB1F-\uFB28\uFB2A-\uFB36\uFB38-\uFB3C\uFB3E\uFB40\uFB41\uFB43\uFB44\uFB46-\uFBB1\uFBD3-\uFD3D\uFD50-\uFD8F\uFD92-\uFDC7\uFDF0-\uFDFB\uFE70-\uFE74\uFE76-\uFEFC\uFF21-\uFF3A\uFF41-\uFF5A\uFF66-\uFFBE\uFFC2-\uFFC7\uFFCA-\uFFCF\uFFD2-\uFFD7\uFFDA-\uFFDC-\u0400-\u04FF']+)/g; return text.match(regex) ? 1 : 0; } /** * Helper injectArray */ jexcel.injectArray = function(o, idx, arr) { return o.slice(0, idx).concat(arr).concat(o.slice(idx)); } /** * Get letter based on a number * * @param {number} columnNumber * @return string letter */ jexcel.getColumnName = function(columnNumber) { return jexcel.helpers.getColumnName(columnNumber); } /** * Convert excel like column to jexcel id * * @param string id * @return string id */ jexcel.getIdFromColumnName = function (id, arr) { // Get the letters var t = /^[a-zA-Z]+/.exec(id); if (t) { // Base 26 calculation var code = 0; for (var i = 0; i < t[0].length; i++) { code += parseInt(t[0].charCodeAt(i) - 64) * Math.pow(26, (t[0].length - 1 - i)); } code--; // Make sure jexcel starts on zero if (code < 0) { code = 0; } // Number var number = parseInt(/[0-9]+$/.exec(id)); if (number > 0) { number--; } if (arr == true) { id = [ code, number ]; } else { id = code + '-' + number; } } return id; } /** * Convert jexcel id to excel like column name * * @param string id * @return string id */ jexcel.getColumnNameFromId = function (cellId) { if (! Array.isArray(cellId)) { cellId = cellId.split('-'); } return jexcel.getColumnName(parseInt(cellId[0])) + (parseInt(cellId[1]) + 1); } /** * Verify element inside jexcel table * * @param string id * @return string id */ jexcel.getElement = function(element) { var jexcelSection = 0; var jexcelElement = 0; function path (element) { if (element.className) { if (element.classList.contains('jexcel_container')) { jexcelElement = element; } } if (element.tagName == 'THEAD') { jexcelSection = 1; } else if (element.tagName == 'TBODY') { jexcelSection = 2; } if (element.parentNode) { if (! jexcelElement) { path(element.parentNode); } } } path(element); return [ jexcelElement, jexcelSection ]; } jexcel.doubleDigitFormat = function(v) { v = ''+v; if (v.length == 1) { v = '0'+v; } return v; } jexcel.createFromTable = function(el, options) { if (el.tagName != 'TABLE') { console.log('Element is not a table'); } else { // Configuration if (! options) { options = {}; } options.columns = []; options.data = []; // Colgroup var colgroup = el.querySelectorAll('colgroup > col'); if (colgroup.length) { // Get column width for (var i = 0; i < colgroup.length; i++) { var width = colgroup[i].style.width; if (! width) { var width = colgroup[i].getAttribute('width'); } // Set column width if (width) { if (! options.columns[i]) { options.columns[i] = {} } options.columns[i].width = width; } } } // Parse header var parseHeader = function(header) { // Get width information var info = header.getBoundingClientRect(); var width = info.width > 50 ? info.width : 50; // Create column option if (! options.columns[i]) { options.columns[i] = {}; } if (header.getAttribute('data-celltype')) { options.columns[i].type = header.getAttribute('data-celltype'); } else { options.columns[i].type = 'text'; } options.columns[i].width = width + 'px'; options.columns[i].title = header.innerHTML; options.columns[i].align = header.style.textAlign || 'center'; if (info = header.getAttribute('name')) { options.columns[i].name = info; } if (info = header.getAttribute('id')) { options.columns[i].id = info; } if (info = header.getAttribute('data-mask')) { options.columns[i].mask = info; } } // Headers var nested = []; var headers = el.querySelectorAll(':scope > thead > tr'); if (headers.length) { for (var j = 0; j < headers.length - 1; j++) { var cells = []; for (var i = 0; i < headers[j].children.length; i++) { var row = { title: headers[j].children[i].textContent, colspan: headers[j].children[i].getAttribute('colspan') || 1, }; cells.push(row); } nested.push(cells); } // Get the last row in the thead headers = headers[headers.length-1].children; // Go though the headers for (var i = 0; i < headers.length; i++) { parseHeader(headers[i]); } } // Content var rowNumber = 0; var mergeCells = {}; var rows = {}; var style = {}; var classes = {}; var content = el.querySelectorAll(':scope > tr, :scope > tbody > tr'); for (var j = 0; j < content.length; j++) { options.data[rowNumber] = []; if (options.parseTableFirstRowAsHeader == true && ! headers.length && j == 0) { for (var i = 0; i < content[j].children.length; i++) { parseHeader(content[j].children[i]); } } else { for (var i = 0; i < content[j].children.length; i++) { // WickedGrid formula compatibility var value = content[j].children[i].getAttribute('data-formula'); if (value) { if (value.substr(0,1) != '=') { value = '=' + value; } } else { var value = content[j].children[i].innerHTML; } options.data[rowNumber].push(value); // Key var cellName = jexcel.getColumnNameFromId([ i, j ]); // Classes var tmp = content[j].children[i].getAttribute('class'); if (tmp) { classes[cellName] = tmp; } // Merged cells var mergedColspan = parseInt(content[j].children[i].getAttribute('colspan')) || 0; var mergedRowspan = parseInt(content[j].children[i].getAttribute('rowspan')) || 0; if (mergedColspan || mergedRowspan) { mergeCells[cellName] = [ mergedColspan || 1, mergedRowspan || 1 ]; } // Avoid problems with hidden cells if (s = content[j].children[i].style && content[j].children[i].style.display == 'none') { content[j].children[i].style.display = ''; } // Get style var s = content[j].children[i].getAttribute('style'); if (s) { style[cellName] = s; } // Bold if (content[j].children[i].classList.contains('styleBold')) { if (style[cellName]) { style[cellName] += '; font-weight:bold;'; } else { style[cellName] = 'font-weight:bold;'; } } } // Row Height if (content[j].style && content[j].style.height) { rows[j] = { height: content[j].style.height }; } // Index rowNumber++; } } // Nested if (Object.keys(nested).length > 0) { options.nestedHeaders = nested; } // Style if (Object.keys(style).length > 0) { options.style = style; } // Merged if (Object.keys(mergeCells).length > 0) { options.mergeCells = mergeCells; } // Row height if (Object.keys(rows).length > 0) { options.rows = rows; } // Classes if (Object.keys(classes).length > 0) { options.classes = classes; } var content = el.querySelectorAll('tfoot tr'); if (content.length) { var footers = []; for (var j = 0; j < content.length; j++) { var footer = []; for (var i = 0; i < content[j].children.length; i++) { footer.push(content[j].children[i].textContent); } footers.push(footer); } if (Object.keys(footers).length > 0) { options.footers = footers; } } // TODO: data-hiddencolumns="3,4" // I guess in terms the better column type if (options.parseTableAutoCellType == true) { var pattern = []; for (var i = 0; i < options.columns.length; i++) { var test = true; var testCalendar = true; pattern[i] = []; for (var j = 0; j < options.data.length; j++) { var value = options.data[j][i]; if (! pattern[i][value]) { pattern[i][value] = 0; } pattern[i][value]++; if (value.length > 25) { test = false; } if (value.length == 10) { if (! (value.substr(4,1) == '-' && value.substr(7,1) == '-')) { testCalendar = false; } } else { testCalendar = false; } } var keys = Object.keys(pattern[i]).length; if (testCalendar) { options.columns[i].type = 'calendar'; } else if (test == true && keys > 1 && keys <= parseInt(options.data.length * 0.1)) { options.columns[i].type = 'dropdown'; options.columns[i].source = Object.keys(pattern[i]); } } } return options; } } // Helpers jexcel.helpers = (function() { var component = {}; /** * Get carret position for one element */ component.getCaretIndex = function(e) { if (this.config.root) { var d = this.config.root; } else { var d = window; } var pos = 0; var s = d.getSelection(); if (s) { if (s.rangeCount !== 0) { var r = s.getRangeAt(0); var p = r.cloneRange(); p.selectNodeContents(e); p.setEnd(r.endContainer, r.endOffset); pos = p.toString().length; } } return pos; } /** * Invert keys and values */ component.invert = function(o) { var d = []; var k = Object.keys(o); for (var i = 0; i < k.length; i++) { d[o[k[i]]] = k[i]; } return d; } /** * Get letter based on a number * * @param {number} columnNumber * @return string letter */ component.getColumnName = function(columnNumber) { let dividend = columnNumber+1; let columnName = ""; let modulo; while (dividend > 0) { modulo = (dividend - 1) % 26; columnName = String.fromCharCode(65 + modulo).toString() + columnName; dividend = parseInt((dividend - modulo) / 26); } return columnName; } /** * Get column name from coords */ component.getColumnNameFromCoords = function(x, y) { return component.getColumnName(parseInt(x)) + (parseInt(y) + 1); } component.getCoordsFromColumnName = function(columnName) { // Get the letters var t = /^[a-zA-Z]+/.exec(columnName); if (t) { // Base 26 calculation var code = 0; for (var i = 0; i < t[0].length; i++) { code += parseInt(t[0].charCodeAt(i) - 64) * Math.pow(26, (t[0].length - 1 - i)); } code--; // Make sure jspreadsheet starts on zero if (code < 0) { code = 0; } // Number var number = parseInt(/[0-9]+$/.exec(columnName)) || null; if (number > 0) { number--; } return [ code, number ]; } } /** * Extract json configuration from a TABLE DOM tag */ component.createFromTable = function() {} /** * Helper injectArray */ component.injectArray = function(o, idx, arr) { return o.slice(0, idx).concat(arr).concat(o.slice(idx)); } /** * Parse CSV string to JS array */ component.parseCSV = function(str, delimiter) { // user-supplied delimeter or default comma delimiter = (delimiter || ","); // Final data var col = 0; var row = 0; var num = 0; var data = [[]]; var limit = 0; var flag = null; var inside = false; var closed = false; // Go over all chars for (var i = 0; i < str.length; i++) { // Create new row if (! data[row]) { data[row] = []; } // Create new column if (! data[row][col]) { data[row][col] = ''; } // Ignore if (str[i] == '\r') { continue; } // New row if ((str[i] == '\n' || str[i] == delimiter) && (inside == false || closed == true || ! flag)) { // Restart flags flag = null; inside = false; closed = false; if (data[row][col][0] == '"') { var val = data[row][col].trim(); if (val[val.length-1] == '"') { data[row][col] = val.substr(1, val.length-2); } } // Go to the next cell if (str[i] == '\n') { // New line col = 0; row++; } else { // New column col++; if (col > limit) { // Keep the reference of max column limit = col; } } } else { // Inside quotes if (str[i] == '"') { inside = ! inside; } if (flag === null) { flag = inside; if (flag == true) { continue; } } else if (flag === true && ! closed) { if (str[i] == '"') { if (str[i+1] == '"') { inside = true; data[row][col] += str[i]; i++; } else { closed = true; } continue; } } data[row][col] += str[i]; } } // Make sure a square matrix is generated for (var j = 0; j < data.length; j++) { for (var i = 0; i <= limit; i++) { if (data[j][i] === undefined) { data[j][i] = ''; } } } return data; } return component; })(); /** * Jquery Support */ if (typeof(jQuery) != 'undefined') { (function($){ $.fn.jspreadsheet = $.fn.jexcel = function(mixed) { var spreadsheetContainer = $(this).get(0); if (! spreadsheetContainer.jexcel) { return jexcel($(this).get(0), arguments[0]); } else { if (Array.isArray(spreadsheetContainer.jexcel)) { return spreadsheetContainer.jexcel[mixed][arguments[1]].apply(this, Array.prototype.slice.call( arguments, 2 )); } else { return spreadsheetContainer.jexcel[mixed].apply(this, Array.prototype.slice.call( arguments, 1 )); } } }; })(jQuery); } return jexcel; })));