Spreadsheet formulas

Jspreadsheet implements spreadsheet-like formulas providing great compatibility with the major spreadsheet software, such as Excel or Google Spreadsheet. In this section, you can find which formulas are available, how to create custom formulas, and some other features.
  • Automatic formula update on copy and paste and in the corner dragging.
  • The formula returning DOM objects to cells.
  • Cross worksheet calculations.

Formula extension distribution

In most recent versions, the formula extension has two different versions, the Basic and or Premium. The Basic is the default version for all Jspreadsheet distributions, while the Premium is available for purchase, and it brings some differences, such as:
  • Matrix calculations;
  • Range Variables;
  • Extended formulas;
  • Private scope;
  • Special internal properties, x, y and instance;
  • It runs in stand-alone applications;

Special properties

When the Jspreasheet Pro invoke the method from a valid cell, you would have access to the coordinates of the cell and the instance of the worksheet.
var YOUR_METHOD = function() {
    // Return the coordinates to the cell
    return this.x + ',' + this.y;
}



Available formulas

We are working to bring as many formulas as possible. Meanwhile, you can check for the existing implementation. For security reasons, all references should be using capital letters, including the implementation of custom methods.


Special formulas

To support calculations, Jspreadsheet brings a few special formulas as below:
Method Example
=TABLE() Return the jexcel table instance
=COLUMN() Return the column number where the formula has been executed
=ROW() Return the row number where the formula has been executed
=CELL() Return the cell string identification
=VALUE() Return the cell value based on the colNumber and rowNumber. You can get the raw or processed value.
=VALUE(col: Number, row: Number, processedValue: Boolean)


Roadmap for the formulas

We are constantly bringing more methods and features to bring Jspreadsheet in line with modern software.



Custom formulas

It is possible to create custom methods that would be available in the spreadsheet. The custom method should return the appropriate content for the cell. From version 7, Jspreadsheet accepts a DOM element as a return of a function.

IMPORTANT: All methods names should be created using capital letters.
// New custom formula
var COLORIZE = function(v) {
    var d = document.createElement('div');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Add to the correct scope
formula.setFormula({ COLORIZE });

// Create spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300' },
            { type: 'text', width:'200' },
        ]
    }]
});

Example

Basic spreadsheet with formulas.

A basic spreadsheet example using formulas, including currency, percentage and mask.

var style = 'background-color:orange; font-weight: bold;';

jspreadsheet(document.getElementById('spreadsheet'), {
    toolbar: true,
    worksheets: [{
        data: [
            [ 'Crayons Crayola only (No Rose Art)', 2, 5.01, 0.01, '=B1*C1*(1-D1)' ],
            [ 'Colored Pencils Crayola only', 2, 4.41, 0.02, '=B2*C2*(1-D2)' ],
            [ 'Expo Dry-erase Markers Wide', 4, 3.00, 0.1, '=B3*C3*(1-D3)' ],
            [ 'Index Cards Unlined', 3, 6.00, 0.03, '=B4*C4*(1-D4)' ],
            [ 'Tissues', 10, 1.90, 0.01, '=B5*C5*(1-D5)' ],
            [ 'Ziploc Sandwich-size Bags', 5, 1.00, 0.01, '=B6*C6*(1-D6)' ],
            [ 'Thin Markers Crayola only', 2, 3.00, 0.02, '=B7*C7*(1-D7)' ],
            [ 'Highlighter', 4, 1.20, 0.01, '=B8*C8*(1-D8)' ],
            [ 'Total', '=SUM(B1:B8)', '=ROUND(SUM(C1:C8), 2)', '', '=SUM(E1:E8)' ],
        ],
        columns: [
            { type: 'text', title:'Product', width:'300' },
            { type: 'text', title:'Qtd', width:'80', mask:'#.##0' },
            { type: 'text', title:'Price', width:'100px', mask:'$ #.##0,00'  },
            { type: 'text', title:'Discount', mask:'0.00%' },
            {
                type: 'number',
                title: 'Total',
                width: '100px',
                format: 'US #.##0,00;[Red](#.##0,00)',
            },
        ],
        style: {
            A9: style,
            B9: style,
            C9: style,
            D9: style,
            E9: style,
        },
        columnSorting:false,
        worksheetName: 'Calculations',
    }]
});



Cross worksheet formulas

From version 7, the cross-worksheets formulas has been implemented. The following example has cross-worksheet and cross-spreadsheet formula calculations.

jspreadsheet(document.getElementById('spreadsheet-1'), {
    worksheets: [{
            data: data1,
            minDimensions: [5,5],
            defaultColWidth: '50px',
            columns: [{ width: '300px' }],
        },
        {
            data: data2,
            minDimensions: [5,5],
        },
    ],
    debugFormulas: true,
});


Defined names Premium edition

From version 8 defined names are available on the premium edition.

This feature is only available in combination with JSS Formula Premium plugin.

jspreadsheet(document.getElementById('spreadsheet-2'), {
    worksheets: [
        {
            data: [
                [ 1, '=SUM(Test*10)' ],
                [ 2, '' ],
                [ 3, '' ],
                [ 4, '' ],
            ],
            minDimensions: [5,5],
            worksheetName: 'Named ranges',
        }
    ],
    // On JSS the defined names must be uppercase
    definedNames: {
        'TEST': 'A1:A4',
    },
});


Custom methods

All custom methods should be declared in capital letters.

var COLORIZE = function(v) {
    var d = document.createElement('span');
    d.style.color = v;
    d.innerText = v.toUpperCase();
    return d;
}

// Add formula to the necessary scope
formula.setFormula({ COLORIZE });

jspreadsheet(document.getElementById('spreadsheet-3'), {
    worksheets: [{
        data: [
            [ 'red', '=COLORIZE(A1)' ],
            [ 'green', '=COLORIZE(A2)' ],
            [ 'blue', '=COLORIZE(A3)' ],
        ],
        columns: [
            { type: 'text', width:'300px' },
            { type: 'text', width:'200px' },
        ],
        debugFormulas: true,
    }]
});