Documentation

Examples

Including formulas on your jquery spreadsheet

The example below shows how to use Excel like formulas on your jquery spreadsheet.



Source code

<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>
<script src="http://cdn.bossanova.uk/js/excel-formula.min.js"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/js/jquery.jexcel.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/css/jquery.jexcel.min.css" type="text/css" />

<div id="my"></div>

<script>
var data = [
    ['Furnace',1,10000,'=B1*C1','=IF(C1 > 5000, true, false)'],
    ['Tower',2,6000,'=B2*C2','=IF(C2 > 5000, true, false)'],
    ['Drum',3,5000,'=B3*C3','=IF(C3 > 5000, true, false)'],
    ['Pump',4,4000,'=B4*C4','=IF(C4 > 5000, true, false)'],
    ['Total','=SUM(B1:B4)','=(C1+C2+C3+C4)','=SUM(D1:D4)','']
]

$('#my').jexcel({
    data:data,
    columns: [
        { type:'text' },
        { type:'numeric' },
        { type:'numeric' },
        { type:'numeric' },
    ],
    colHeaders: ['Equipment','Quantity', 'Price', 'Total', '>5000?'],
    colWidths: [ 200, 80, 100, 100, 100 ]
});

$('#my').jexcel('updateSettings', {
    table: function (instance, cell, col, row, val, id) {
        // Format numbers
        if (col == 2 || col == 3) {
            // Get text
            txt = $(cell).text();
            // Format text
            txt = numeral(txt).format('0,0.00');
            // Update cell value
            $(cell).html(' $ ' + txt);
        }

        // Bold the total row
        if ($(cell).text() == 'Total') {
            $('.r' + row).css('font-weight', 'bold');
            $('.r' + row).css('background-color', '#fffaa3');
        }
    }
});
</script>
</html>


Third party formula implementations

You can use a third part library to excute various excel like formulas.


Source code

<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.9.0/underscore-min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/underscore.string/3.3.4/underscore.string.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.22.1/moment.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jstat/1.7.1/jstat.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/numeric/1.2.6/numeric.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>

<script src="https://bossanova.uk/components/jexcel/dist/js/excel-formula.min.js"></script>
<script src="https://bossanova.uk/components/jexcel/dist/js/formulas.js"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/js/jquery.jexcel.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/css/jquery.jexcel.min.css" type="text/css" />

<div id="my1"></div>

<script>
var data1 = [
    ['Furnace', 10, 'AVERAGE', '=AVERAGE(B1:B4)'],
    ['Tower', 9, 'STDEVA', '=STDEVA(B1:B4)'],
    ['Drum', 44, 'MAX', '=MAX(B1,B2,B3,B4)'],
    ['Pump', 12, 'COUNT', '=COUNT(B1:B4)'],
];

$('#my1').jexcel({
    data:data1,
    colWidths: [ 400, 80, 100, 200 ],
});
</script>
</html>


Custom javascript formulas

The example below shows how to create your own method in javascript to apply as a Excel like formula



Source code

<html>
<script src="http://cdn.bossanova.uk/js/excel-formula.min.js"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/js/jquery.jexcel.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/css/jquery.jexcel.min.css" type="text/css" />

<div id="my2"></div>

<script>
var data2 = [
    ['Furnace','=CUSTOM(A1)', 10],
    ['Tower','=CONSOLE(A2)', 9],
    ['Drum','=CONCAT_COLUMNS(A1,A3)', 3],
    ['Pump','=CONSOLE(A4)', 7],
    ['Total','', '=AVG(C1:C4)'],
]

$('#my2').jexcel({
    data:data1,
    colWidths: [ 400, 200 ],
});

function CUSTOM(cell) {
    return 'alert: ' + cell;
}

function CONSOLE(cell) {
    console.log(cell);
    return cell;
}

function CONCAT_COLUMNS(a, b) {
    return a + ', ' + b;
}

function AVG(v)
{
    var sum = v.reduce(function(a, b) { return a + b; });
    var avg = sum / v.length;

    return avg;
}

</script>
</html>

Buy me a beer?