Premium plugin : Pivot Table - GBonnaire/jspreadsheet-plugins-and-editors GitHub Wiki

JSpreadsheet Plugin : Pivot Table

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

Dependencies

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

All librairies available on https://repo.gbonnaire.fr

⚠️ **GitHub.com Fallback** ⚠️