Hi,
We have a Handsontable setup with just over 1200 rows of data, we want to use formulas on the data and knew applying formulas to all the data would kill the table.
Therefore when editing a specific cell, we recalculated that particular row on the cell change to try to get around this.
The data displays quickly but then on edit, it works, but it is very slow to use (pretty much unusable) and can sometimes crash the browser (latest Chrome).
Do you have any recommendations for making the code below more efficient to try to speed this up?
// Once the DOM content has loaded
document.addEventListener("DOMContentLoaded", function() {
// Set the table data
var data = [
// My 1200+ items are looped through here.
];
// Define the container
var container = document.getElementById( 'spc-spreadsheet' );
// Percent Profit Custom Renderer
function percentProfitRenderer( instance, td, row, col, prop, value, cellProperties ) {
Handsontable.renderers.TextRenderer.apply( this, arguments );
// If value less than 0
if( value < 0 ) {
// Set cell purple
td.style.fontWeight = 'bold';
td.style.color = 'white';
td.style.background = 'purple';
}
// If value between and including 1 and 9
if( value > 0 && value < 10 ) {
// Set cell red
td.style.fontWeight = 'bold';
td.style.color = 'white';
td.style.background = 'red';
}
// If greater than 10 and lower than or equal to 15
if ( value > 10 && value <= 15 ) {
// Set cell orange
td.style.fontWeight = 'bold';
td.style.color = 'black';
td.style.background = 'orange';
}
// If value is greater than 15
if ( value > 15 ) {
// Set cell green
td.style.fontWeight = 'bold';
td.style.color = 'white';
td.style.background = 'green';
}
}
// Instantiate a new handson table using the variable hot
var hot = new Handsontable(container, {
// General configuration options
data: data,
stretchH: 'all',
rowHeaders: true,
colHeaders: true,
filters: true,
dropdownMenu: true,
formulas: true,
columnSorting: true,
sortIndicator: true,
// Column headers
colHeaders: [ 'Col 1', 'Col 2', 'Col 3', 'Col 4', 'Col 5', 'Col 6', 'Col 7', 'Col 8', 'Col 9', 'Col 10', 'Col 11', 'Col 12', 'Col 13', 'Col 14', 'Col 15', 'Col 16', 'Col 17', 'Col 18', 'Col 19', 'Col 20' , 'Col 21', 'Col 22', 'Col 23', 'Col 24', 'Col 25', 'Col 26' ],
// Columns with sorting enabled
columns: [
{data: 0},
{data: 1},
{
data: 2,
type: 'numeric',
format: '0.00',
},
{
data: 3,
type: 'numeric',
format: '0.00',
},
{
data: 4,
type: 'numeric',
format: '0.00',
},
{
data: 5,
type: 'numeric',
format: '0.00',
},
{
data: 6,
type: 'numeric',
format: '0.00',
},
{
data: 7,
type: 'numeric',
format: '0.00',
},
{
data: 8,
type: 'numeric',
format: '0.00',
},
{
data: 9,
type: 'numeric',
format: '0.00',
},
{
data: 10,
type: 'numeric',
format: '0.00',
},
{
data: 11,
type: 'numeric',
format: '0.00',
},
{
data: 12,
type: 'numeric',
format: '0.00',
},
{
data: 13,
type: 'numeric',
format: '0.00',
},
{
data: 14,
type: 'numeric',
format: '0.00',
},
{
data: 15,
type: 'numeric',
format: '0.00',
},
{
data: 16,
type: 'numeric',
format: '0.00',
},
{
data: 17,
type: 'numeric',
format: '0.00',
},
{
data: 18,
type: 'numeric',
format: '0.00',
},
{
data: 19,
type: 'numeric',
format: '0.00',
},
{
data: 20,
type: 'numeric',
format: '0.00',
},
{
data: 21,
type: 'numeric',
format: '0.00',
},
{
data: 22,
type: 'numeric',
format: '0.00',
},
{
data: 23,
type: 'numeric',
format: '0.00',
},
{data: 24},
{data: 25},
],
cells: function (row, col, prop) {
var cellProperties = {};
if( col === 16 || col === 19 || col === 22 ) {
// Use the percent profit renderer to conditionally format
cellProperties.renderer = percentProfitRenderer;
}
return cellProperties;
},
});
/*
Setter is false on load, after a cell is changed the setter becomes true, this allows the new data to be calculated and then the new data can be set on the cells, when they are set they trigger the afterChange function again, the setter is still true and therefore does not get stuck in an infinte loop. If this was only in place then it would not calculate row changes if you try to edit the same row again, therefore the setter needs to be set back to false periodically to allow this. This wants to be shorter time than it could take for a user to edit a row and then attempt to edit it again. If it is a higher number it will not allow the change to occur until the setter has been reset.
*/
// Set a variable which is false
afterChangeSetter = false;
// Every second turn the setter back to false
window.setInterval(function(){
afterChangeSetter = false;
}, 1000);
// Upon a cell value change
hot.addHook( 'afterChange', function( changes, src ) {
// If the setter is not false
if( afterChangeSetter == false ) {
// Set the setter to true
afterChangeSetter = true;
// Get the current changed row (this will return a number starting from 0 not 1)
var changedRow = changes[0][0];
// Set the current row variable to the change row starting zero number
var currentRow = changedRow;
// Get and store the cell data needed for calculations for the current changed row
var netTrade = ( parseFloat( hot.getDataAtCell( currentRow, 2 ) ) ).toFixed(2);
var delivery = ( parseFloat( hot.getDataAtCell( currentRow, 3 ) ) ).toFixed(2);
var web = ( parseFloat( hot.getDataAtCell( currentRow, 14 ) ) ).toFixed(2);
var ebay = ( parseFloat( hot.getDataAtCell( currentRow, 17 ) ) ).toFixed(2);
var amazon = ( parseFloat( hot.getDataAtCell( currentRow, 20 ) ) ).toFixed(2);
var dollarTradePrice = ( parseFloat( hot.getDataAtCell( currentRow, 23 ) ) ).toFixed(2);
var ebayFeeCategory = hot.getDataAtCell( currentRow, 24 );
var amazonFeeCategory = hot.getDataAtCell( currentRow, 25 );
if( isNaN( dollarTradePrice ) ) {
dollarTradePrice = ''; // If the $ Trade Price is NaN then set to blank, otherwise NaN appears in cell
}
var webPaypalFee = ( ( parseFloat( web ) * parseFloat( 0.012 ) ) + parseFloat( 0.20 ) ).toFixed(2);
var webTgcNett = ( parseFloat( netTrade ) + parseFloat( delivery ) + parseFloat( webPaypalFee ) ).toFixed(2);
var webCtc = ( parseFloat( webTgcNett ) * parseFloat( 1.2 ) ).toFixed(2);
var ebayPaypalFee = ( ( parseFloat( ebay ) * parseFloat( 0.014 ) ) + parseFloat( 0.20 ) ).toFixed(2);
var ebayFee = 0.00;
if( ebayFeeCategory == 'home_furniture_diy_appliances' ) {
if( parseFloat( ebay ) < parseFloat( 400 ) ) {
var ebayFee = ( parseFloat( ebay ) * parseFloat( 0.05 ) ).toFixed(2);
}
if( parseFloat( ebay ) == parseFloat( 400 ) ) {
var ebayFee = ( parseFloat( 20.00 ) ).toFixed(2);
}
if( parseFloat( ebay ) > parseFloat( 400 ) ) {
var ebayFee = ( parseFloat( 20.00 ) ).toFixed(2);
}
} else {
var ebayFee = ( parseFloat( ebay ) * parseFloat( 0.1 ) ).toFixed(2);
}
var ebayTgcNett = ( parseFloat( netTrade ) + parseFloat( delivery ) + parseFloat( ebayPaypalFee ) + parseFloat( ebayFee ) ).toFixed(2);
var ebayCtc = ( parseFloat( ebayTgcNett ) * parseFloat( 1.2 ) ).toFixed(2);
if( amazonFeeCategory == 'diy_tools' ) {
var amazonFee = ( parseFloat( amazon ) * parseFloat( 0.12 ) ).toFixed(2);
} else {
if( amazonFeeCategory == 'large_appliances' ) {
var amazonFee = ( parseFloat( amazon ) * parseFloat( 0.07 ) ).toFixed(2);
} else {
if( amazonFeeCategory == 'large_appliances_exceptions' ) {
var amazonFee = ( parseFloat( amazon ) * parseFloat( 0.15 ) ).toFixed(2);
} else {
var amazonFee = ( parseFloat( amazon ) * parseFloat( 0.15 ) ).toFixed(2);
}
}
}
var amazonTgcNett = ( parseFloat( netTrade ) + parseFloat( delivery ) + parseFloat( amazonFee ) ).toFixed(2);
var amazonCtc = ( parseFloat( amazonTgcNett ) * parseFloat( 1.2 ) ).toFixed(2);
var webProfit = ( ( parseFloat( web ) - parseFloat( webCtc ) ) / parseFloat( 1.2 ) ).toFixed(2);
var webPercent = ( ( parseFloat( webProfit ) / parseFloat( webCtc ) ) * 100 ).toFixed(2);
var ebayProfit = ( ( parseFloat( ebay ) - parseFloat( ebayCtc ) ) / parseFloat( 1.2 ) ).toFixed(2);
var ebayPercent = ( ( parseFloat( ebayProfit ) / parseFloat( ebayCtc ) ) * 100 ).toFixed(2);
var amazonProfit = ( ( parseFloat( amazon ) - parseFloat( amazonCtc ) ) / parseFloat( 1.2 ) ).toFixed(2);
var amazonPercent = ( ( parseFloat( amazonProfit ) / parseFloat( amazonCtc ) ) * 100 ).toFixed(2);
hot.setDataAtCell( currentRow, 2, netTrade );
hot.setDataAtCell( currentRow, 3, delivery );
hot.setDataAtCell( currentRow, 4, webPaypalFee );
hot.setDataAtCell( currentRow, 5, webTgcNett );
hot.setDataAtCell( currentRow, 6, webCtc );
hot.setDataAtCell( currentRow, 7, ebayPaypalFee );
hot.setDataAtCell( currentRow, 8, ebayFee );
hot.setDataAtCell( currentRow, 9, ebayTgcNett );
hot.setDataAtCell( currentRow, 10, ebayCtc );
hot.setDataAtCell( currentRow, 11, amazonFee );
hot.setDataAtCell( currentRow, 12, amazonTgcNett );
hot.setDataAtCell( currentRow, 13, amazonCtc );
hot.setDataAtCell( currentRow, 15, webProfit );
hot.setDataAtCell( currentRow, 16, webPercent );
hot.setDataAtCell( currentRow, 18, ebayProfit );
hot.setDataAtCell( currentRow, 19, ebayPercent );
hot.setDataAtCell( currentRow, 21, amazonProfit );
hot.setDataAtCell( currentRow, 22, amazonPercent);
hot.setDataAtCell( currentRow, 23, dollarTradePrice );
hot.setDataAtCell( currentRow, 24, ebayFeeCategory );
hot.setDataAtCell( currentRow, 25, amazonFeeCategory );
hot.render();
}
});
var exportPlugin = hot.getPlugin( 'exportFile' );
var exportButton = document.getElementById( 'spc-export' );
exportButton.addEventListener( 'click', function() {
exportPlugin.downloadFile( 'csv', {
columnHeaders: true,
filename: 'exported_file',
});
});
});