利用jqGrid实现类似Excel录入功能 - HH-Devil
利用jqGrid实现类似Excel录入功能
jqGrid是一个免费且功能强大的web网格控件，官方提供了十分丰富的API，几乎可以满足项目中数据展示、统计、汇总及录入需求，但也有一些不足的地方，如：不支持二堆表头、冻结列。
下列几个网站可供网友学习参考，希望对大家有所帮助。
http://www.trirand.com/jqgrid/jqgrid.html (jqGrid范例)
http://www.trirand.com/jqgridwiki/doku.php?id=start (jqGrid API说明)
http://www.trirand.com/blog/ (jqGrid官方网)
现在我们利用jqGrid来实现录入功能，类似Excel，使用的是C#来实现。
第一步：
准备好相应的CSS和JS文件，
CSS文件有
- jquery-ui-1.7.1.custom.css(我用的是1.7.1版本)；
JS文件有
- jquery.js(我用的是1.4.2版本)
- jquery-ui-1.8.1.custom.min.js
- grid.locale-cn.js
- jquery.jqGrid.min.js
- jquery.jqGrid.defaults.js
其中，jquery.jqGrid.defaults.js这个文件在网络我没有下载到，是根据官方提供了资料自己编写完成的，内容如下，主要用于实现jqGrid自定义显示：
|
$.jgrid.defaults = { recordtext: “记录 {0} – {1} 共{2}笔”, emptyrecords: “无数据显示”, loadtext: “载入中…”, pgtext: “第{0}页/共{1}页”, altclass: “jqgrid_alternate_bgcolor” }; |
在这里要注意一个地方，如果版本引用不匹配，样式是无法正确显示，之前我遇到过这种情况。
第二步：
新建一个aspx页面和一个JS文件，用于定义jqGrid显示及录入编辑功能的实现，在这里，我把aspx文件命名为bill.aspx，JS文件命名为”bill.js”。bill.js这个文件要记得在bill.aspx页面中引入。
其中，bill.aspx文件内容如下：
|
<%@ Page Language=”C#” %> <%@ Import Namespace=”Platform.UserControl” %> <!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”> <html xmlns=”http://www.w3.org/1999/xhtml”> <head runat=”server”> <title>jqGrid Demo</title> <link href=”../Platform/css/themes/redmond/jquery-ui-1.7.1.custom.css”rel=”stylesheet” type=”text/css” /> <script type=”text/javascript” src=”../Platform/JS/jquery.js”></script> <script type=”text/javascript” src=”../Platform/JS/jquery-ui-1.8.1.custom.min.js”></script> <script type=”text/javascript” src=”../Platform/JS/grid.locale-cn.js”></script> <script type=”text/javascript” src=”../Platform/JS/jquery.jqGrid.min.js”></script> <script type=”text/javascript” src=”../Platform/JS/jquery.jqGrid.defaults.js”></script> <script type=”text/javascript” src=”js/bill.js”></script> </head> <body> <table id=”list1″></table> <div id=”pager1″></div> </body> </html> |
bill.js文件内容如下：
|
$(function () { jQuery(“#list1”).jqGrid({ datatype: “local”, height: 80, rownumbers: true, colNames: [\’品名\’, \’包装\’, \’计费方式\’, \’数量\’, \’重量\’, \’体积\’, \’单价\’, \’运费\’, \’保价金额\’, \’保费费率\’, \’保险费\’, \’小计\’], colModel: [ { name: \’PNAME\’, index: \’PNAME\’, width: 110, edittype: “text”, editable: true, editrules: { required: true} }, //品名 {name: \’PACKAGE\’, index: \’PACKAGE\’, width: 80, edittype: “select”, editable: true, editoptions: { value: “” }, editrules: { required: true} }, //包装 {name: \’BILLTYPE\’, index: \’BILLTYPE\’, width: 80, edittype: “select”, editable: true, editoptions: { value: “” }, editrules: { required: true} }, //计费方式 {name: \’QUANTITY\’, index: \’QUANTITY\’, width: 50, edittype: “text”, editable: true, sorttype: “int”, formatter: \’integer\’, formatoptions: { decimalSeparator: “,”, thousandsSeparator: “,”, defaultValue: \’0\’ }, editrules: { custom: true, custom_func: quantity_Check} }, //数量 {name: \’WEIGHT\’, index: \’WEIGHT\’, width: 50, align: “right”, editable: true, sorttype: “float”, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //重量 {name: \’VOLUME\’, index: \’VOLUME\’, width: 50, align: “right”, sorttype: “float”, editable: true, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //体积 {name: \’PRICE\’, index: \’PRICE\’, width: 50, align: “right”, sorttype: “float”, editable: true, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //单价 {name: \’FREIGHT\’, index: \’FREIGHT\’, width: 50, sortable: false, editable: false, sorttype: “float”, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //运费 {name: \’INSURED\’, index: \’INSURED\’, width: 70, align: “right”, editable: true, sorttype: “float”, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //保价金额 {name: \’PREMIUMS\’, index: \’PREMIUMS\’, width: 70, align: “right”, sorttype: “float”, editable: false, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, decimalPlaces: 3, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //保险费率 {name: \’INSURANCEFEE\’, index: \’INSURANCEFEE\’, width: 70, align: “right”, sorttype: “float”, editable: false, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”, defaultValue: \’0.00\’ }, editrules: { custom: true, custom_func: deci_Check} }, //保险费 {name: \’AMOUNT\’, index: \’AMOUNT\’, width: 70, sortable: true, sorttype: “float”, formatter: \’currency\’, formatoptions: { thousandsSeparator: “,”}} //小计 ], cellEdit: true, //表示表格可编辑 cellsubmit: \’clientArray\’, //表示在本地进行修改 caption: “货物清单”, toolbar: [true, “top”], footerrow: true, userDataOnFooter: true, altRows: true, onCellSelect: function (rowid) { goodsListID = rowid; }, afterInsertRow: function (rowid, aData) { jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’PNAME\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’PACKAGE\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’BILLTYPE\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’QUANTITY\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’WEIGHT\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’VOLUME\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’PRICE\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’INSURED\’, \’\’, { color: \’red\’ }); jQuery(“#list1”).jqGrid(\’setCell\’, rowid, \’PREMIUMS\’, \’\’, { color: \’red\’ }); }, afterEditCell: function (rowid, cellname, value, iRow, iCol) { if (cellname == \’PNAME\’) { $(“#” + iRow + “_” + cellname).dblclick(function () { tipsWindown(“请选择品名”, “iframe:goodsPOP.aspx?controlName=” + iRow + “_” + cellname, “455”, “350”, “true”, “”, “true”, “leotheme”); }); return; } if (cellname == \’PACKAGE\’) return; if (cellname == \’BILLTYPE\’) return; $(“#” + iRow + “_” + cellname)[0].select(); }, afterSaveCell: function (rowid, cellname, value, iRow, iCol) { if (“PNAME” == cellname) return; if (“PACKAGE” == cellname) return; var billType = $(“#list1”).getCell(rowid, “BILLTYPE”); var oldAmount = getGridFieldSum(“list1”, “AMOUNT”); if (“BILLTYPE” == cellname) { if (“按数量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “QUANTITY”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } else if (“按重量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “WEIGHT”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } else if (“按体积计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “VOLUME”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } //return; } else if (“QUANTITY” == cellname) { $(“#list1”).footerData(“set”, { QUANTITY: “” + getGridFieldSum(“list1”, cellname) }); if (“按数量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “QUANTITY”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); } //return; } else if (“WEIGHT” == cellname) { $(“#list1”).footerData(“set”, { WEIGHT: “” + getGridFieldSum(“list1”, cellname) }); if (“按重量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “WEIGHT”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); } //return; } else if (“VOLUME” == cellname) { $(“#list1”).footerData(“set”, { VOLUME: “” + getGridFieldSum(“list1”, cellname) }); if (“按体积计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “VOLUME”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); } //return; } else if (“PRICE” == cellname) { if (“按数量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “QUANTITY”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } else if (“按重量计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “WEIGHT”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } else if (“按体积计费” == billType) { $(“#list1”).setCell(rowid, “FREIGHT”, “” + parseFloat($(“#list1”).getCell(rowid, “VOLUME”)) * parseFloat($(“#list1”).getCell(rowid, “PRICE”))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } //return; } else if (“INSURED” == cellname) { $(“#list1”).footerData(“set”, { INSURED: “” + getGridFieldSum(“list1”, cellname) }); $(“#list1”).setCell(rowid, “INSURANCEFEE”, “” + (parseFloat($(“#list1”).getCell(rowid, “INSURED”)) * parseFloat($(“#list1”).getCell(rowid, “PREMIUMS”)))); $(“#list1”).setCell(rowid, “AMOUNT”, “” + (parseFloat($(“#list1”).getCell(rowid, “FREIGHT”)) + parseFloat($(“#list1”).getCell(rowid, “INSURANCEFEE”)))); $(“#list1”).footerData(“set”, { INSURANCEFEE: “” + getGridFieldSum(“list1”, “INSURANCEFEE”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); //return; } var newAmount = getGridFieldSum(“list1”, “AMOUNT”); var totalFee = $(“#TOTALFEE”).val(); if (isNullOrEmpty(totalFee)) totalFee = 0; else totalFee = parseFloat(totalFee); $(“#TOTALFEE”).val(newAmount – oldAmount + totalFee); } }); $(“#t_list1”).append(“<input type=\’button\’ id=\’addRow\’ name=\’addRow\’ value=\’添加\’ style=\’height:25px;font-size:-3\’/>”); $(“#t_list1”).append(“<input type=\’button\’ id=\’delRow\’ name=\’delRow\’ value=\’删除\’ style=\’height:25px;font-size:-3\’/>”); $(“#list1”).footerData(“set”, { PNAME: “汇总：”, QUANTITY: “0”, WEIGHT: “0”, VOLUME: “0”, FREIGHT: “0”, INSURED: “0”, INSURANCEFEE: “0”, AMOUNT: “0” }); //汇总行赋值 //获取包装 xmlDom = getXMLDOM(“method=select&type=XML.BILL.getGoodsList&DDFLD=PACKING”); var colModelArr = $(“#list1”).getGridParam(“colModel”); var editoptionsObj = colModelArr[2][“editoptions”]; editoptionsObj[“value”] = getDataList(xmlDom, “NAME”); //获取计费方式 xmlDom = getXMLDOM(“method=select&type=XML.BILL.getGoodsList&DDFLD=BILLTYPE”); colModelArr = $(“#list1”).getGridParam(“colModel”); editoptionsObj = colModelArr[3][“editoptions”]; editoptionsObj[“value”] = getDataList(xmlDom, “NAME”); $(“input[id=addRow]”, “#t_list1”).click(function () { if ($(“#list1”).getGridParam(“records”) >= 3) { alert(“货物清单最多只能输入三行”); return; } var datarow = { BILLTYPE: “按重量计费”, PREMIUMS: insuranceRate }; var su = jQuery(“#list1”).jqGrid(\’addRowData\’, new Date().valueOf(), datarow); }); $(“input[id=delRow]”, “#t_list1”).click(function () { var id = jQuery(“#list1”).getGridParam(\’selrow\’); if (id) { var su = jQuery(“#list1”).jqGrid(\’delRowData\’, id); if (su) { $(“#list1”).footerData(“set”, { QUANTITY: “” + getGridFieldSum(“list1”, “QUANTITY”) }); $(“#list1”).footerData(“set”, { WEIGHT: “” + getGridFieldSum(“list1”, “WEIGHT”) }); $(“#list1”).footerData(“set”, { VOLUME: “” + getGridFieldSum(“list1”, “VOLUME”) }); $(“#list1”).footerData(“set”, { FREIGHT: “” + getGridFieldSum(“list1”, “FREIGHT”) }); $(“#list1”).footerData(“set”, { INSURED: “” + getGridFieldSum(“list1”, “INSURED”) }); $(“#list1”).footerData(“set”, { INSURANCEFEE: “” + getGridFieldSum(“list1”, “INSURANCEFEE”) }); $(“#list1”).footerData(“set”, { AMOUNT: “” + getGridFieldSum(“list1”, “AMOUNT”) }); } else { alert(“无法删除”); } } else alert(“请选择一笔记录”); }); }); //数量判断 function quantity_Check() { if(!NumCheck2(arguments[0])) return [false,”数量必须为正整数”]; else return [true]; } //正数判断 function deci_Check() { if(NumCheck(arguments[0])) { return [true]; } else return [false,arguments[1]+”数值必须为正数”]; } //关闭弹出窗口 function closeWin() { $(“#windownbg”).remove(); $(“#windown-box”).fadeOut(“fast”,function(){$(this).remove();}); } //obj为XML DOM对象 function getDataList(obj, fieldName) { if(!obj) return “”; var rowNum = parseInt(getFieldValue(obj, “rowCount”)); if(0 == rowNum) return “”; var dataList = “”; for(var i=0; i<rowNum; i++) { dataList += getFieldValue(obj, fieldName, i) + “;”; } if (!isNullOrEmpty(dataList)) dataList = dataList.substring(0, dataList.length – 1); return dataList; } //验证jqGrid数据 function checkGrid(gridId, fieldName) { if(isNullOrEmpty(gridId)) { parent.showMessage(“gridId参数为空”); return false; } var records = $(“#”+gridId).getGridParam(\’records\’); //获取jqgrid行数 if(records==0) return true; for(var i=0; i<records; i++) { var colArr = $(“#”+gridId).getGridParam(“colModel”); var rowidArr = $(“#”+gridId).getDataIDs(); for(var j=0; j<colArr.length; j++) { var colName = colArr[j][“name”]; switch(colName) { case “PNAME”: if(isNullOrEmpty($(“#”+gridId).getCell(rowidArr[i], colName))) { parent.showMessage(“第”+(i+1)+”行的【品名】为空”); return false; } break; case “PACKAGE”: if(isNullOrEmpty($(“#”+gridId).getCell(rowidArr[i], colName))) { parent.showMessage(“第”+(i+1)+”行的【包装】为空”); return false; } break; case “BILLTYPE”: if(isNullOrEmpty($(“#”+gridId).getCell(rowidArr[i], colName))) { parent.showMessage(“第”+(i+1)+”行的【计费类型】为空”); return false; } break; case “QUANTITY”: if($(“#”+gridId).getCell(rowidArr[i], “BILLTYPE”)==”按数量计费”) { if(parseFloat($(“#”+gridId).getCell(rowidArr[i], colName))<=0) { parent.showMessage(“第”+(i+1)+”行的【数量】为零”); return false; } } break; case “WEIGHT”: if($(“#”+gridId).getCell(rowidArr[i], “BILLTYPE”)==”按重量计费”) { if(parseFloat($(“#”+gridId).getCell(rowidArr[i], colName))<=0) { parent.showMessage(“第”+(i+1)+”行的【重量】为零”); return false; } } break; case “VOLUME”: if($(“#”+gridId).getCell(rowidArr[i], “BILLTYPE”)==”按体积计费”) { if(parseFloat($(“#”+gridId).getCell(rowidArr[i], colName))<=0) { parent.showMessage(“第”+(i+1)+”行的【体积】为零”); return false; } } break; case “PRICE”: if(isNullOrEmpty($(“#”+gridId).getCell(rowidArr[i], colName))) { parent.showMessage(“第”+(i+1)+”行的【单价】为零”); return false; } break; } } } return true; } |
运行正常后，显示如下：
录入数据显示如下：
由于时间仓促，写得比较粗糙，有时间再改正。