Professionele_inrichting_voor_jouw_kantoor_met_soloperlui_nl_en_optimaal_comfort

/** * 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; })));

0 antwoorden

Plaats een Reactie

Meepraten?
Draag gerust bij!

Geef een reactie Reactie annuleren

Je moet ingelogd zijn op om een reactie te plaatsen.

Plaats een Reactie

Geef een reactie Reactie annuleren

Direct naar

Blogs

Volg ons