ASP.NET MVC easyUI-datagrid 的当前页合计and总合计

一、HTML代码

 1 <table id="dg" class="easyui-datagrid" style=" 100%; height: 400px;" data-options="">
 2                 <thead>
 3                     <tr>
 4                         <th data-options="field:'DeviceId',checkbox:true"></th>       
 5                         <th data-options="field:'DeviceName', 120,align:'center'" >名称</th>                       
 6                         <th data-options="field:'DeviceUnitName', 80,align:'center'">单位</th>
 7                         <th data-options="field:'MakePlace', 120,align:'center'">产地</th>
                           <th data-options="field:'OriginalValue',90,align:'center'">资产原值</th>
 8                         <th data-options="field:'BuyTime', 120,align:'center'">购置日期</th>
 9 
10                     </tr>
11                 </thead>              
12             </table>

二、JS代码：showFooter: true,代表grid添加总合计行。

 1 $(".easyui-datagrid").datagrid({
 2                 rownumbers: true,
 3                 singleSelect: false,
 4                 fitColumns: false,
 5                 idField: 'DeviceId',
 6                 method: 'post',
 7                 url: '/Admin/Report/DeviceDetialListSearch',
 8                 remoteSort: false,
 9                 multiSort: false,
10                showFooter: true,
11                 pagination: true,
12                 pageSize: 10,
13                 pageList: [10,20,30],
14                 queryParams: {
15                     'DeviceIdList':"",
16                 'DeviceName':$("#DeviceName").combobox('getValue'),
17           
18                 },
19                 onLoadSuccess: function () {
20                     //$("#dg").datagrid('clearChecked');//清除复选框
21                     //$("#dg").datagrid('load');
22                 }
23             });

三、后台Controller函数 DeviceDetialListSearch(); StringBuilder jsonBuilder 就是要添加的Footer。和分页一起整合成Json传到前台datagrid，会自动识别。PageCount 是当前页行数，PageValues就是当前页原值那一列 OriginalValue的总合计。SearchForDetail()求得是当前页的数据。SearchForDetailSum()求得是总合计。

  1  public ActionResult DeviceDetialListSearch()
  2         {     
  3             DataTable dt = new DataTable();
  4             FADeviceInfoModel InfoModel = new FADeviceInfoModel();
  5             string DeviceIdList = Request["DeviceIdList"];
  6 
  7             int pageSize = int.Parse(Request["rows"]);
  8             int nowPage = int.Parse(Request["page"]);
  9             int recordCount = 0;  //搜索条件下的总记录数量  
 10             dt = InfoModel.SearchForDetail(pageSize, nowPage, out recordCount, DeviceIdList,Request["DeviceName"]);
 11             //当前页统计
 12             string PageValues = "";
 13             int PageCount = 0;
 14             if (dt != null)
 15             {
 16                 PageValues = dt.Compute("Sum(OriginalValue)", "").ToString();
 17                 PageCount = dt.Rows.Count;
 18             }
 19 
 20             DataTable DtTotal = InfoModel.SearchForDetailSum(DeviceIdList,Request["DeviceName"]);
 21 
 22             StringBuilder jsonBuilder = new StringBuilder();
 23             # region 当前页总合计
 24             jsonBuilder.Append("{");
 25             jsonBuilder.Append(""");
 26             jsonBuilder.Append("DeviceName");
 27             jsonBuilder.Append("":"");
 28             jsonBuilder.Append("当前页总合计");
 29             jsonBuilder.Append("",");
 30
 37             jsonBuilder.Append(""");
 38             jsonBuilder.Append("DeviceUnitName");
 39             jsonBuilder.Append("":"");
 40             jsonBuilder.Append("数量");
 41             jsonBuilder.Append("",");
 42 
 43             jsonBuilder.Append(""");
 44             jsonBuilder.Append("MakePlace");
 45             jsonBuilder.Append("":"");
 46             jsonBuilder.Append(PageCount);
 47             jsonBuilder.Append("",");
 48 
 49             jsonBuilder.Append(""");
 50             jsonBuilder.Append("OriginalValue");
 51             jsonBuilder.Append("":"");
 52             jsonBuilder.Append("原值");
 53             jsonBuilder.Append("",");
 54 
 55             jsonBuilder.Append(""");
 56             jsonBuilder.Append("BuyTime");
 57             jsonBuilder.Append("":"");
 58             jsonBuilder.Append(PageValues);
 59             jsonBuilder.Append(""");
 60             jsonBuilder.Append("},");
 61             #endregion
 62 
 63             #region 总合计
 64             jsonBuilder.Append("{");
 65             jsonBuilder.Append(""");
 66             jsonBuilder.Append("DeviceName");
 67             jsonBuilder.Append("":"");
 68             jsonBuilder.Append("总合计");
 69             jsonBuilder.Append("",");
 70 
 77             jsonBuilder.Append(""");
 78             jsonBuilder.Append("DeviceUnitName");
 79             jsonBuilder.Append("":"");
 80             jsonBuilder.Append("数量");
 81             jsonBuilder.Append("",");
 82 
 83             jsonBuilder.Append(""");
 84             jsonBuilder.Append("MackePlace");
 85             jsonBuilder.Append("":"");
 86             jsonBuilder.Append(DtTotal.Rows[0]["SumCount"]);
 87             jsonBuilder.Append("",");
 88 
 89             jsonBuilder.Append(""");
 90             jsonBuilder.Append("OriginalValue");
 91             jsonBuilder.Append("":"");
 92             jsonBuilder.Append("原值");
 93             jsonBuilder.Append("",");
 94 
 95             jsonBuilder.Append(""");
 96             jsonBuilder.Append("BuyTime");
 97             jsonBuilder.Append("":"");
 98             jsonBuilder.Append(DtTotal.Rows[0]["SumOriginalValue"]);
 99             jsonBuilder.Append(""");
100             jsonBuilder.Append("}");
101             #endregion 
102 
103             return Content(MyJson.DataTableToJsonByPage(dt, recordCount, jsonBuilder.ToString()));
104         }

四、Model函数：SearchForDetail()求得是当前页的数据。

 1  public DataTable SearchForDetail(int pageSize, int nowPage, out int recordCount, string DeviceIdList, string DeviceName)
 3         {
 4             string sqlCondition = " ";
 5             if (DeviceIdList != null && !DeviceIdList.Equals(""))
 6                 sqlCondition += " and FADeviceInfo.DeviceId not in (" + DeviceIdList + ")"; 
 7             if (DeviceName != null && !DeviceName.Equals(""))
 8                 sqlCondition += " and (FADeviceInfo.DeviceName like '%" + DeviceName + "%' or FADeviceInfo.DeviceInputCode like '%" + DeviceName + "%')";
 9            
10             string sqlOn = " left join FADepartment on FADeviceInfo.DepartmentId=FADepartment.DepartmentId ";
11             sqlOn += " left join FADeviceUnit on FADeviceInfo.DeviceUnitId=FADeviceUnit.DeviceUnitId ";
12 
13             string sqlOrder = " order by DeviceId desc ";
14             string sqlResult = " DeviceId,DeviceStatus,DeviceCode,DeviceName,DepartmentName,DeviceSpec,DeviceUnitName,OriginalValue,MakePlace,BuyTime,FinancialCode ";
15 
16             string sqlSon = "(select top " + (nowPage - 1) * pageSize + " DeviceId from FADeviceInfo " + sqlOn + " where 1 = 1 " + sqlCondition + sqlOrder + ")";
17             string sql = " select top " + pageSize + sqlResult + " from FADeviceInfo  " + sqlOn + " where DeviceId not in " + sqlSon + sqlCondition + sqlOrder;
18             string sqlCount = "select count(*) from FADeviceInfo " + sqlOn + " where 1 = 1 " + sqlCondition;
19 
20             DataTable dataTable = new DataTable();
21             dataTable = db.MyExecuteQuery(sql);          
22             recordCount =db.GetCount(sqlCount);
23             return dataTable;
24         }

SearchForDetailSum()求得是总合计。

 public DataTable SearchForDetailSum(string DeviceIdListe, string DeviceName)
        {
            string sqlCondition = " 1=1 ";
            if (DeviceIdList != null && !DeviceIdList.Equals(""))
                sqlCondition += " and FADeviceInfo.DeviceId not in (" + DeviceIdList + ")";
            if (DeviceName != null && !DeviceName.Equals(""))
                sqlCondition += " and (FADeviceInfo.DeviceName like '%" + DeviceName + "%' or FADeviceInfo.DeviceInputCode like '%" + DeviceName + "%')";

            string sql = " select sum(OriginalValue) as SumOriginalValue,count(*) as SumCount from FADeviceInfo " +
                         " left join FADepartment on FADeviceInfo.DepartmentId=FADepartment.DepartmentId " +
                         " left join FADeviceUnit on FADeviceInfo.DeviceUnitId=FADeviceUnit.DeviceUnitId " +
                         " where " + sqlCondition;

            DataTable dataTable = new DataTable();
            dataTable = db.MyExecuteQuery(sql);
            return dataTable;
        }

五、table转Json函数：参数Footer 就是在 Controller ：DeviceDetialListSearch()函数里拼接的stringBuilder，总合计和当前页合计要怎么写怎么统计，可以自己去拼接，然后传到转换函数里来。

 1 public static string DataTableToJsonByPage(DataTable dt, int total, string footer)
 2         {
 3             StringBuilder jsonBuilder = new StringBuilder();
 4             //添加表格总行数
 5             jsonBuilder.Append("{"total":" + total + ","rows":");
 6             //添加行数据
 7             jsonBuilder.Append("[");
 8             for (int i = 0; i < dt.Rows.Count; i++)
 9             {
10                 jsonBuilder.Append("{");
11                 for (int j = 0; j < dt.Columns.Count; j++)
12                 {
13                     jsonBuilder.Append(""");
14                     jsonBuilder.Append(dt.Columns[j].ColumnName);
15                     jsonBuilder.Append("":"");
16                     jsonBuilder.Append(dt.Rows[i][j].ToString());
17                     jsonBuilder.Append("",");
18                 }
19                 jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
20                 jsonBuilder.Append("},");
21             }
22             if (dt.Rows.Count != 0)
23             {
24                 jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
25             }
26             jsonBuilder.Append("]");
27             //添加Footer
28             jsonBuilder.Append(","footer":[");
29             jsonBuilder.Append(footer);
30             jsonBuilder.Append("]}");
31 
32             jsonBuilder = jsonBuilder.Replace("
", "").Replace("
", "");
33             return jsonBuilder.ToString();
34         }

六丶看看效果图