Data Settings

Pivot Table allows you to reflect the data source changes in the pivot table. It also allows you to edit the values in the data area of the pivot table in the following ways:

• Refresh Data Source

• Change Data Source

• Data Value Editing

Refresh Data Source

If the data source for a pivot table is changed at any point in time, the pivot table needs to be refreshed to reflect the latest information. SpreadJS allows you to refresh the data source and reflect the latest data in the pivot table by using the updateSource method of the pivot table.

The following GIF shows how to refresh the pivot table with any changes in the data source by using a "Refresh Datasource" button.

The following example code shows how to refresh the pivot table with any changes in the data source.

function refreshDataSource() {
    myPivotTable.updateSource();
}

You can also refresh the pivot table data using the SpreadJS designer. To do this, select the pivot table to show the PIVOTTABLE ANALYZE tab and select the Refresh button in the Data group.

Change Data Source

Pivot table allows you to change the data source and synchronize the existing layout information to the new data source, which will make it easier to work with the pivot table. This feature is useful when you need to add more rows or modify the existing dataset.

To change the current data source, use the updateSource method and specify the new data as shown in the following example code.

// Configure different data sources
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
sheet.name("Pivot Table");
spread.setActiveSheet("Pivot Table");
var sourceData = [["Date", "Buyer", "Type", "Amount"],
["01-Jan", "Mom", "Fuel", 74],
["15-Jan", "Mom", "Food", 235],
["17-Jan", "Dad", "Sports", 20],
["21-Jan", "Kelly", "Books", 125]];
sourceSheet.setArray(0, 0, sourceData);
sourceSheet.tables.add('sourceData', 0, 0, 5, 4);

var newSourceData = [["Date", "Buyer", "Type", "Amount", "Location", "Notes"],
["10-Feb", "Mom", "Fuel", 74, "Gas Station", "Paid in cash"],
["27-Mar", "Mom", "Food", 235, "Supermarket", "Bought snacks"],
["13-Jan", "Dad", "Sports", 20, "Sports Store", "Discount applied"],
["21-May", "Kelly", "Books", 125, "Bookstore", "Fiction novels"],
["03-Apr", "John", "Electronics", 340, "Electronics Store", "New phone case"],
["19-Jun", "Mom", "Clothing", 89, "Mall", "Sale purchase"],
["15-Jul", "Dad", "Tools", 75, "Hardware Store", "DIY project"],
["08-Aug", "Kelly", "Furniture", 450, "Furniture Shop", "Living room decor"],
["23-Sep", "John", "Gaming", 120, "Online Store", "Pre-ordered game"],
["11-Oct", "Mom", "Garden", 55, "Garden Center", "Flower seeds"],
["06-Nov", "Dad", "Fuel", 64, "Gas Station", "Highway stop"],
["20-Dec", "Kelly", "Toys", 92, "Toy Store", "Birthday gifts"]];
sourceSheet.setArray(10, 0, newSourceData);
sourceSheet.tables.add('newSourceData', 10, 0, 13, 6);


// Create the pivot table
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
var options = { showRowHeader: true, showColumnHeader: true };
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
var panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", pivotTable, document.getElementById("panel"));
panel.sectionVisibility(GC.Spread.Pivot.PivotPanelSection.fields + GC.Spread.Pivot.PivotPanelSection.area);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
sourceSheet.setValue(1, 3, 1000);


// Get the pivot data source
var pivotDataSource = pivotTable.getSource();
console.log(pivotDataSource);
// Update pivot data source
pivotTable.updateSource("newSourceData");

SpreadJS Designer also enables you to change the data source by following the below steps:

1. Select the pivot table and go to the PIVOTTABLE ANALYZE tab in the ribbon.

   
   

   
   

   
   

2. In the Data group, click Change Data Source.

3. In the Change PivotTable Data Source dialog, reselect or modify the data range.

   
   

   
   

   
   

4. Click OK to regenerate the pivot table with the new data.

Note:

• If a field name is found after changing the data source:

  • The information for that field will be retained. However, the items corresponding to the field will be updated to the new data source.

  • The slicer for that field will be retained, and the data will be updated.

  • If the field has a corresponding item group field, the items in the source field in SpreadJS will be updated with the latest data, while the group field will retain its original items.

• If a field name is not found after changing the data source:

  • Calculated fields will show null values.

  • Grouped fields will be removed.

  • Slicers for the missing field will be lost.

• If the new data source is already used by another pivot table, the existing data will be directly referenced.

• The change data source operation for pivot tables does not support an undo operation. However, pivot tables support undo operations after changing the data source.

Data Value Editing

SpreadJS pivot table allows you to edit or overwrite its data area values. Only numeric value types are allowed to be set as overwritten node values.

The Pivot Table supports only 5 types of calc into subtotal:

• GC.Pivot.SubtotalType.sum

• GC.Pivot.SubtotalType.count

• GC.Pivot.SubtotalType.countNums

• GC.Pivot.SubtotalType.max

• GC.Pivot.SubtotalType.min

In case the subtotal type is not included in the list above, then only the specific cell will be overwritten, and the subtotal result will not change.

For editing value in the pivot table at runtime, you can set enableDataValueEditing option of the pivot table to true. The default value of the enableDataValueEditing is false. However, for overwriting the pivot table data value through code, you can use the setNodeValue method of the pivot table which accepts the node info array and value to be overwritten. The node info array can be fetched either using getNodeInfo method or by creating a node array for the specific value. You can also use getNodeValue, getOverwriteList, clearOverwriteList methods to get the node value, overwritten cell value list and to clear the overwritten value list respectively. It is to be noted that the overwritten value list will be lost on updating the pivot table source.

Note: For JSON I/O, the list of overwritten values is included in it. However, for ExcelIO and Pivot Table serialization and deserialization, the overwritten values list is ignored.

The following GIF shows how the subtotal values can be edited at runtime.

The following code shows how to set the enableDataValueEditing option to true.

// enable data value editing
pivotTable.options.enableDataValueEditing = true;

The following code shows how to update the data values of the pivot table using the getNodeInfo and setNodeValue methods.

// you can also get the node info by below code
var nodeInfo = pivotTable.getNodeInfo(7, 5);
// change value of cell (7,5) using code
pivotTable.setNodeValue(nodeInfo, 5000);

The following image of the SpreadJS Designer depicts how to set the enableDataValueEditing property of the pivot table to true.