How to sum values in column and display in an external input box?
In certain cases, while dynamic editing you may want to sum all the values in a column and display it in an input text box in ASP.NET MVC Grid.
Solution
You can achieve the above requirement by using create and cellSave events or an external button click event with the Sum summary type.
The create event is triggered when the Grid has rendered completely. In the argument of the create event, you can obtain the following details.
Name | Description |
cancel | Returns the cancel option value |
model | Returns the Grid model |
type | Returns the name of the event |
The cellSave event is triggered when a cell is edited and saved on batchEdit mode. In the argument of the cellSave event, you can obtain the following details.
Name | Description |
model | Returns the Grid model |
type | Returns the name of the event |
data | Returns the deleted data |
columnName | Returns the column name |
rowData | Returns the row data object |
value | Returns the modified cell value |
previousValue | Returns the previous value of the cell |
columnObject | Returns the columnObject |
cell | Returns the cell object |
isForeignKey | Returns when the edited column is ForeignKey |
The Sum Summary type yields the sum of the data. The parameters of the ej.sum are listed in the following table
Parameter | Description |
DataSource | Json data where the sum operation is to be performed |
Column/Field Name | Corresponding field name for calculating the sum |
Example:
In the following example, the Grid is rendered with batch editing enabled.
- Render the Grid.
<div id="Grid"></div> <script type="text/javascript"> $(function () { $("#Grid").ejGrid({ dataSource: window.gridData, allowPaging: true, pageSettings: { pageSize: 7 }, editSettings: { allowEditing: true, editMode: "batch" }, toolbarSettings: { showToolbar: true, toolbarItems: [ej.Grid.ToolBarItems.Edit, ej.Grid.ToolBarItems.Update, ej.Grid.ToolBarItems.Cancel]}, columns: [ { field: "OrderID", isPrimaryKey: true, headerText: "Order ID", width: 100 }, { field: "CustomerID", headerText: 'Customer ID', width: 100}, { field: "EmployeeID", headerText: 'Employee ID', width: 100}, { field: "Freight", headerText: 'Freight', format: "{0:C}", width: 100 }, { field: "OrderDate", headerText: 'OrderDate', format: "{0:MM/dd/yyyy}", width: 100 }, { field: "ShipCountry", headerText: "Ship Country", width: 100 }, { field: "ShipName", headerText: 'Ship Name', width: 100} ], create: "create", cellSave: "cellSave" }); }) </script>
MVC
@(Html.EJ().Grid<object>("Grid") .Datasource((IEnumerable<object>)ViewBag.dataSource) .AllowPaging() .PageSettings(page=>page.PageSize(7)) .EditSettings(edit => { edit.AllowEditing().EditMode(EditMode.Batch); }) .ToolbarSettings(toolbar => { toolbar.ShowToolbar().ToolbarItems(items => { items.AddTool(ToolBarItems.Edit); items.AddTool(ToolBarItems.Update); items.AddTool(ToolBarItems.Cancel); }); }) .Columns(col => { col.Field("OrderID").HeaderText("Order ID").IsPrimaryKey(true).Width(100).Add(); col.Field("CustomerID").HeaderText("Customer ID").Width(100).Add(); col.Field("EmployeeID").HeaderText("Employee ID").Width(100).Add(); col.Field("Freight").HeaderText("Freight").Format("{0:c2}").Width(100).Add(); col.Field("OrderDate").HeaderText("Order Date").Format("{0:MMM dd,yyyy}").Width(100).Add(); col.Field("ShipCountry").HeaderText("Ship Country").Width(100).Add(); col.Field("ShipName").HeaderText("Ship Name").Width(100).Add(); }) .ClientSideEvents(eve=>eve.Create("create").CellSave("cellSave")) )
ASP.NET
<ej:Grid ID="OrdersGrid" runat="server" AllowPaging="True" Create="create" CellSave="cellSave"> <Columns> <ej:Column Field="OrderID" HeaderText="Order ID" IsPrimaryKey="True" Width="100" /> <ej:Column Field="CustomerID" HeaderText="Customer ID" Width="100" /> <ej:Column Field="EmployeeID" HeaderText="Employee ID" Width="100" /> <ej:Column Field="Freight" HeaderText="Freight" Format="{0:C}" Width="100" /> <ej:Column Field="OrderDate" HeaderText="Order Date" Format="{0:MM/dd/yyyy}" Width="100" /> <ej:Column Field="ShipCountry" HeaderText="Ship Country" Width="100" /> <ej:Column Field="ShipName" HeaderText="Ship Name" Width="100" /> </Columns> <EditSettings AllowEditing="True" EditMode="Batch"></EditSettings> <ToolbarSettings ShowToolbar="True" ToolbarItems="edit,update,cancel"></ToolbarSettings> <PageSettings PageSize="7" /> </ej:Grid>
- Render the input control for displaying the sum calculated.
<div> Total Freight: <input id="TotalFreight" type="text" width="200" /> </div>
- In the create event, the sum of all the values in the Freight column is obtained by using ej.Sum summary type and displayed in the input text box.
var total= diff = temp = 0; function create(args) { total = ej.sum(args.model.dataSource, "Freight"); $("#TotalFreight").val(total); }
- In the cellSave event, when the cell value is edited and saved, the edited value is obtained. The difference of the edited value compared to the default value of the cell is added to the original sum.
function cellSave(args) { if (args.columnName == "Freight") { var Temptotal = ej.sum(args.model.dataSource, "Freight"); var defaultValue = args.model.currentViewData[args.cell.closest("tr").index()].Freight; //getting default value of the target cell if (defaultValue != args.previousValue && args.previousValue != args.value) temp = Temptotal - parseInt(args.previousValue); //avoiding issue when same cell is edited again else if(temp != 0) temp = temp > Temptotal ? (temp - Temptotal) : (Temptotal - temp); if (parseInt(args.value) > parseInt(defaultValue)) { diff = parseInt(args.value) - parseInt(defaultValue); total = parseInt(Temptotal) + diff + temp; } else if (parseInt(args.value) < parseInt(defaultValue)) { diff = parseInt(defaultValue) - parseInt(args.value); total = parseInt(Temptotal) - diff + temp; } if (defaultValue == "" && args.value != "") { diff = parseInt(args.value); total = parseInt(Temptotal) + diff + temp; } else if (args.value == "" && defaultValue != "") { diff = defaultValue; total = parseInt(Temptotal) - diff + temp; } else if (args.value == "" && defaultValue == "") { diff = 0; total = parseInt(Temptotal) + diff + temp; } $("#TotalFreight").val(total) //updating the value of the input control with the total temp = total; } }
Result:
Figure 1: Initial Rendering
Note: The sum of the Freight column is calculated and displayed in the input text box.
Figure 2: After Editing and saving a cell
Conclusion
I hope you enjoyed learning about how to sum values in column and display in an external input box.
You can refer to our ASP.NET MVC Grid feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our ASP.NET MVC Grid example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!