Premium plugin : Pivot Table

JSpreadsheet Plugin : Pivot Table

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

Dependencies

• JSpreadsheet Pro v7
• or JSpreadsheet Pro v8+

Options of plugin

Option name	Description	Type	Default Value
colsSortBy	Defined custom sort of columns in pivot	function|null	null
columns	List of name or column name ("A") or column index (0 = first column)	Array	null
data	if data null, data is get on instance	Array|null	null
filters	filters present of pivot "columnName":value/regexp/arrayofvalue	Object	{}
footers	Show footers of pivot (Total columns)	Boolean	false
hideSheetData	hide sheet with data	Boolean	false
plugins	Plugins for instance Jspreadsheet of pivot (deprecated on v8+)	Array|null	null
roleForPivot	Add role to sheet generate for pivot	Array|stirng|null	null
rows	List of name or column name ("A") or column index (0 = first column)	Array	[]
rowSortBy	Custom sort row (index, array of index, function)	int|array|function	null
showEmptyColumns	Show columns with no data	boolean	false
slicers	List of name or column name ("A") or column index (0 = first column)	Array	[]
slicersOptions	List of options for each column name ("A") or column index (0 = first column)	Object	{}
slicersSortBy	Custom sort slicers (function with indexSlicers)	function	null
values	Name or column name ("A") or column index (0 = first column)	string|int	null
valuesFormula	Results cross row/column	string(SUM/COUNT/AVG/MIN/MAX)|function	"SUM"

For translation

You can use jSuites translate to make translation

Option name	Default Value
text_allItems	All items
text_empty	(empty)
text_resultsOf	Results of
text_slicer_multiple	Multiple select
text_slicer_clear	Clear select
text_total	Total

Methods of plugin

Method	Description	Example
setData(Array) -> Void	Defined data for pivotTable	jspreadsheet.current.plugins.pivotTable.setData([[1,2,3],[4,5,6]]);
updatePivot(worksheet) -> Void	refresh pivot from data	jspreadsheet.current.plugins.pivotTable.updatePivot();

Get started

Header on page

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

<script src="/path/to/jexcel.pivotTable.js"></script>

Initialize plugin on JExcel

jspreadsheet(document.getElementById('spreadsheet'), {
	...
	plugins: [
      ...
     { name:'pivot', plugin:jss_pivotTable, options:{
                rows: ["A", "B", "C"], 
                columns: ["E", "F", "G"], 
                values: "H", 
                valuesFormula: "SUM", 
                filters: {"C": ["New York", "London"]},
                slicers: ["C", "E", "F", "G"],
                rowsSortBy: [2,1],
                colsSortBy: function(Worksheet, RowA, RowB) {
                  var a_value_col2 = RowA[2];
                  var b_value_col2 = RowB[2];
                  if(RowA[0] == RowB[0]) {
                      if(RowA[1] == RowB[1]) {
                          if(a_value_col2 == b_value_col2) {
                              return 0;
                          } else {
                            var a_index = orderReferential.indexOf(a_value_col2);
                            var b_index = orderReferential.indexOf(b_value_col2);
                            return a_index>b_index ? 1:-1;
                          }
                      } else {
                          return RowA[1]>RowB[1] ? 1:-1;
                      }
                  } else {
                      return RowA[0]>RowB[0] ? 1:-1;
                  }
                },
                slicersSortBy: function(Worksheet, a, b, indexSlicer) {
                    if(a==b) {
                        return 0;
                    }
                    if(indexSlicer==0) { // First slicer
                        var a_index = orderReferential.indexOf(a.toLowerCase());
                        var b_index = orderReferential.indexOf(b.toLowerCase());
                        return a_index>b_index ? 1:-1;
                    } else { // Default
                        return a>b ? 1:-1;
                    }
                },
                worksheetName: "Pivot Table",
                hideSheetData: false,
                plugins:null,
                footers: false,
            }},
      ...  
    ],
    ...
});

Copyright and license

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