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

• 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 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();
}

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 aslo 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);

Data Value Editing using Designer

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