Premium plugin : Summary

JExcel Plugin : Summary

You can buy this plugin on Repo plugin summary Demo is available on demo

Dependencies

• JExcel Pro v7
• Free plugin : RowHeaderRename

Options of plugin

Option name	Description	Type	Default Value
columns	Defined all columns of summary like Jexcel	array	[]
formulas	List of formulas per row (automaticaly repliqued on all column)	Object {"YourTitleOnRow":"=YourFormula()"}	{}
freezeWidth	Freeze row header with minimum width	int|null	null
hideOnMobile	Hide summary on Mobile	Boolean	false
plugins	add plugins in summary JExcel	Array	[]
widthRowIndex	Width of the headers of rows	int	50

Methods of plugin

Method	Description	Example
getJExcelSummary() -> Instance of JExcel (Summary)	Return instance of Jexcel for manipulate programmaticaly	jexcel.current.plugins.summary.getJExcelSummary();

Get started

Header on page

<script src="https://jexcel.net/v7/jexcel.js"></script>
<script src="https://jexcel.net/v7/jsuites.js"></script>
<link rel="stylesheet" href="https://jexcel.net/v7/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jexcel.net/v7/jexcel.css" type="text/css" />

<script src="/path/to/jexcel.conditionalstyle.js"></script>
<script src="https://cdn.jsdelivr.net/gh/Guillaume-Bo/jexcel-plugins-and-editors@latest/plugins/rowHeaderRename/jexcel.rowHeaderRename.js"></script>

Initialize plugin on JExcel

jexcel(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
   	  { name:'summary', plugin:jexcel_summary, options:{
            columns: [
                { title: "0 - 19", width: 80 },
                { title: "20 - 29", width: 80 },
                { title: "30 - 39", width: 80 },
                { title: "40 - 49", width: 80 },
                { title: "50 - 59", width: 80 },
                { title: "+ 60", width: 80 },
            ],
            widthRowIndex: 200,
            formulas: {
                "Number of persons" : "=COUNT_AGE(TABLEPARENT(), COLUMN())",
                "Average of salaries" : "=AVG_SUMMARY_SALARIES_BY_AGE(TABLEPARENT(), COLUMN())",
            },
         }},
      ...  
    ],
    ...
});

// functions
var COUNT_AGE = function(instance, column) {
    switch(column) {
        case 1 :
            var ageStart = 0, ageEnd = 19;
            break;
        case 2 :
            var ageStart = 20, ageEnd = 29;
            break;
        case 3 :
            var ageStart = 30, ageEnd = 39;
            break;
        case 4 :
            var ageStart = 40, ageEnd = 49;
            break;
        case 5 :
            var ageStart = 50, ageEnd = 59;
            break;
        case 6 :
            var ageStart = 60, ageEnd = 1000;
            break;
    }
    var data = instance.getColumnData(3);
    var count = 0;
    for(var ite_data in data) {
        if(data[ite_data] >= ageStart && data[ite_data] <= ageEnd) {
            count++;
        }
    }
    return count;
}

var AVG_SUMMARY_SALARIES_BY_AGE = function(instance, column) {
    switch(column) {
        case 1 :
            var ageStart = 0, ageEnd = 19;
            break;
        case 2 :
            var ageStart = 20, ageEnd = 29;
            break;
        case 3 :
            var ageStart = 30, ageEnd = 39;
            break;
        case 4 :
            var ageStart = 40, ageEnd = 49;
            break;
        case 5 :
            var ageStart = 50, ageEnd = 59;
            break;
        case 6 :
            var ageStart = 60, ageEnd = 1000;
            break;
    }
    var data = instance.getColumnData(3);
    var dataSalary = instance.getColumnData(5);
    var salary = [];
    for(var ite_data in data) {
        if(data[ite_data] >= ageStart && data[ite_data] <= ageEnd) {
            salary.push(dataSalary[ite_data]);
        }
    }
    return numeral(AVGSALARY(salary)).format("$#,##0");
}

function AVGSALARY(data) {
    var values = [];
    for(var ite_data in data) {
        var value = data[ite_data];
        value = value.replace(/[^0-9\.]/gm, "");
        values.push(Number(value));
    }
    return AVERAGE(values);
}

Copyright and license

Copyright GBonnaire.fr and Code released under the commercail License. This plugin requiere license of Repo.gbonnaire.fr