Sigma Grid 表格的研究及使用

最新推荐文章于 2016-05-16 12:01:09 发布

weixin_34315189

最新推荐文章于 2016-05-16 12:01:09 发布

阅读量339

点赞数

文章标签： java php javascript ViewUI

原文链接：https://my.oschina.net/u/180932/blog/41858

版权

2019独角兽企业重金招聘Python工程师标准>>>

|--gt_msg_en.js

这里以master/details和Sigma Grid CRUD为例，说明Sigma Grid的使用方法，各个参数的作用，数据的传输，及数据的保存。

master/details的demo演示页面为example_master_details.html
此demo演示了父子关系的grid。子grid为明细信息，通过鼠标点击父grid的某条记录关联出子grid内容。
下面着重介绍此grid的使用方法：
首先在页面中定义两个grid的位置

Html代码

<div id="bigbox" style="margin:15px;display:!none;">
<div id="gridbox" style="border:0px solid #cccccc;background-color:#f3f3f3;padding:5px;height:200px;width:700px;" ></div>
</div>
<div id="bigboxDetails" style="margin:15px;display:!none;">
<div id="gridboxDetails" style="border:0px solid #cccccc;background-color:#f3f3f3;padding:5px;height:250px;width:700px;" ></div>
</div>

自定义grid列头

Html代码

<table id="myHead" style="display:none">
<tr>
<td rowspan="2" columnId='chk' resizable='false'>
<input id="g1_chk" type="checkbox"/></td>
<td rowspan="2" columnId='no' resizable='false'>Order No</td>
<td rowspan="2" columnId='employee' resizable='false'>Employee</td>
<td colspan="7">Order Info</td>
</tr>
<tr>
<td columnId='country'>Country</td>
<td columnId='customer'>Customer<img src="./images/customer.gif"/></td>
<td columnId='bill2005'>2005</td>
<td columnId='bill2006'>2006</td>
<td columnId='bill2007'>2007</td>
<td columnId='bill2008'>2008</td>
<td columnId='orderDate'>Ship Date</td>
</tr>
</table>

//定义两个grid

Js代码

var grid_demo_id = "myGrid1" ;
var grid_details_id = "myGrid2";

构建父grid数据结构

Js代码

var dsOption= {
fields :[
{name : 'no' },
{name : 'country' },
{name : 'customer' },
{name : 'employee' },
{name : 'bill2005' ,type: 'float' },
{name : 'bill2006' ,type: 'float' },
{name : 'bill2007' ,type: 'float' },
{name : 'bill2008' ,type: 'float' },
{name : 'orderDate' ,type:'date' }
],
uniqueField : 0 , //It could be unique field name or unique field index in fields
recordType : 'object' //记录类型只能是：'object' or 'array'
}

构建子grid数据结构

Js代码

var dsOptionDetails= {
fields :[
{name : 'no' },
{name : 'product' },
{name : 'unitPrice' ,type:'float' },
{name : 'quantity' ,type: 'int' },
{name : 'discount' ,type: 'float' },
{name : 'taxFree' ,type: 'int' },
{name : 'taxRate' ,type: 'float' },
{name : 'shipTo', type: 'int'},
{name : 'shipment'},
{name : 'note'},
{name : 'tax',type: 'float' , initValue : function(record){
var avg = record[5]*record[6];
return avg.toFixed(2);
}},
{name : 'totalPrice' ,type: 'float' , initValue : function(record){
var avg = record[2]*record[3]*record[4]*record[5]*record[6];
return avg.toFixed(2);
}}
],
recordType : 'array'
}
//渲染成checked.gif
function example_renderTaxFree(value ,record,columnObj,grid,colNo,rowNo){
if(value==0) return "<img src='./images/unchecked.gif'>";
return "<img src='./images/checked.gif'>";
}

//渲染成星星 five_star

Js代码

function example_renderDiscount(value ,record,columnObj,grid,colNo,rowNo){
var i = Math.round(value / 0.4 * 5);
if(i>5)i=5;if(i<1)i=1;
return "<IMG SRC=\"./images/five_star" + i + ".gif\" />"
}

构建父grid 列样式
/*
isCheckColumn:选项列
filterable：是否可以过滤此列
exportable：输出表格
id：id
header：显示的名称
width:宽度
inChart：是否在图表中统计
chartColor：图表颜色
*/

Js代码

var colsOption = [
{id: 'chk' ,isCheckColumn : true, filterable: false, exportable:false},
{id: 'no' , header: "Order No" , width :60 },
{id: 'employee' , header: "Employee" , width :80 },
{id: 'country' , header: "Country" , width :70 },
{id: 'customer' , header: "Customer" , width :80 },
{id: 'bill2005' , header: "2005" , width :60, inChart :true, chartColor : 'eecc99'},
{id: 'bill2006' , header: "2006" , width :60, inChart :true, chartColor : '66eeaa' },
{id: 'bill2007' , header: "2007" , width :60, inChart :true, chartColor : 'd65555' },
{id: 'bill2008' , header: "2008" , width :60, inChart :true, chartColor : 'eeaa33' },
{id: 'orderDate' , header: "Delivery Date" , width :100}
];

构建子grid 列样式
/*

editor：可以编辑此列 {type：类型
                   options：为下拉列表提供数据
                   defaultText：默认值
                   validRule：验证'R' - Required
                                                                   'N' - Number
                                 'E' - Email
                                 'F' - Float}
frozen：是否锁定此列如果被锁定在拖动滚动条时候此列不会发生变化
sortOrder：排序 asc 或 desc
renderer：渲染效果参数为function(value ,record,colObj,grid,colNo,rowNo)
align：位置

*/

Js代码

var colsOptionDetails = [
{id: 'no' , header: "Item No" , width :60 , editor:{type:"text"},
frozen : true},
{id: 'product' , header: "Product" , width :100,
grouped : true , frozen : true , sortOrder : 'asc',
editor : { type :"select" ,
options : {"Abalone":"Abalone","Amber":"Amber","Amethyst":"Amethyst","Aquamarine":"Aquamarine",
"Cameos":"Cameos","Citrine":"Citrine","Coral":"Coral","Crystal":"Crystal","Cubic Zirconia":"Cubic Zirconia",
"Emerald":"Emerald","Enamel":"Enamel","Garnet":"Garnet","Glass":"Glass","Moissanite":"Moissanite",
"Onyx":"Onyx","Opal":"Opal","Pearl":"Pearl"} ,defaultText : '' }},
{id: 'unitPrice' , header: "Unit Price" , width :60 ,
editor: { type :"text" ,validRule : ['R','F'] } },
{id: 'quantity' , header: "Quantity" , width :80,
editor: { type :"text" ,validRule : ['R'] } },
{id: 'discount' , header: "Discount" , width :100 ,renderer : example_renderDiscount,
editor: { type :"text" ,validRule : ['R','F'] } },
{id: 'taxFree' , header: "Tax Free" , width :60 ,renderer : example_renderTaxFree },
{id: 'taxRate' , header: "Tax Rate" , width :60 ,
editor: { type :"text" ,validRule : ['R','F'] } },
{id: 'tax' , header: "Tax" , width :60},
{id: 'totalPrice' , header: "Total Price" , width :60, align:"right"
},
{id: 'shipTo' , header: "Ship To" , width :100 },
{id: 'shipment' , header: "Shipment" , width :100 },
{id: 'note' , header: "Note" , width :150 }
];

父grid属性
/*
width:宽度
height：高度
container：grid创建的位置
dataset：数据
columns：列样式
pageSizeList：页面显示条数
customHead：自定义grid头
onClickCell：点击触发事件
SigmaGridPath：未知
loadURL：获取数据地址
exportURL：输出地址用于输出EXCEL XML CSV PDF
exportFileName：输出名称
remotePaging:指定服务器端不分页工作
defaultRecord：当点击添加按钮时默认的加入一行的数据
pageSize：页面显示记录数
toolbarContent：grid 工具条{
        nav goto - 分页
        pagesize - 显示记录数
        reload - 刷新
        print - 打印
        csv - 输出CSV格式文件
        xls - 输出xls格式文件
        pdf - 输出pdf格式文件
        filter - 过滤查询
        chart - 图表
        state - 状态
}
*/

Js代码

var gridOption={
id : grid_demo_id,
width: "700",
height: "350",
container : 'gridbox',
replaceContainer : true,
dataset : dsOption ,
columns : colsOption ,
pageSizeList : [5,10,15,20],
customHead : 'myHead',
onClickCell : function(value, record , cell, row, colNO, rowNO,columnObj,grid){
var no = record["no"]>"070-19"?"070-19":record["no"];
var grid=Sigma.$grid(grid_details_id);
grid.loadURL = "./data/" + no + ".js";
grid.reload();
},
SigmaGridPath : '../grid/',
loadURL : 'test.jsp?actionMethod=list',
saveURL : 'test.jsp?actionMethod=save' ,
exportURL : './export_php/testMasterList.php?export=true',
exportFileName : 'Sales Master Report',
remotePaging : false,
defaultRecord : ["","","","",0,0,0,0,"2008-01-01"],
pageSize:20,
toolbarContent : 'nav goto | pagesize | reload | print csv xls xml pdf filter chart | state'
};

子grid属性
/*
editable:表格是否可编辑
groupable：true分组菜单将会出现在主菜单中，并且启动分组功能
clickStartEdit:点击鼠标出发编辑事件
showGridMenu:是否显示主菜单
allowCustomSkin：是否制定skin出现在菜单上
allowFreeze：是否被冻结栏出现在主菜单
allowHide：隐藏是否出现在主菜单
allowGroup：组是否出现在主菜单

*/

Js代码

var gridOptionDetails={
id : grid_details_id,
loadUrl : './data/010-0.js',
saveURL : 'test.jsp?actionMethod=save' ,
width: "700",
height: "260",
container : 'gridboxDetails',
replaceContainer : true,
editable : true,
groupable : true,
pageSizeList : [5,10,15,20],
dataset : dsOptionDetails ,
columns : colsOptionDetails ,
clickStartEdit : true ,
defaultRecord : ["","",0,0,0.0,0,0.0,"Customer","UPS",""],
pageSize:30,
toolbarContent : 'reload | add del save | print | state',
showGridMenu : true,
allowCustomSkin : true ,
allowFreeze : true ,
allowHide : true ,
allowGroup : true,
customRowAttribute : function(record,rn,grid){
if (record[11]>80){
return 'style="height:50px; background-color:#ffddcc"';
}
}
};

创建Grid对象并输出

Js代码

var mygrid=new Sigma.Grid( gridOption );
var mygridDetails = new Sigma.Grid(gridOptionDetails);
Sigma.Util.onLoad( Sigma.Grid.render(mygrid) );
Sigma.Util.onLoad( Sigma.Grid.render(mygridDetails) );

Sigma Grid CRUD的demo演示页面为list1.jsp
此例子用来说明前后台的数据交互及grid的增删改查及保存功能

获取数据属性：
loadURL
保存数据属性：
saveURL

例：
loadURL : APP_PATH+'/views/controller.jsp?actionMethod=list'
saveURL : APP_PATH+'/views/controller.jsp?actionMethod=save'

这里为了实现功能效果只简单的使用jsp直接访问数据库实现查询CRUD的工作。
当然也使用MVC结构来实现，只要返回的数据类型是JOSN并且符合Sigma Grid的数据格式即可
controller.jsp

Java代码

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,java.util.*"%>
<%@ page import="com.fins.gt.server.*"%>
<%!
Connection getConnection(){
String url="jdbc:mysql://localhost:3306/sigma_grid_server";
Connection conn= null;
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url,"root","");
}catch(Exception e){
}
return conn;
}
void closeConnection(Connection conn){
try{
conn.close();
}catch(Exception e){
}
}
List listOrders(){
Connection conn = getConnection();
if(conn==null)
return new ArrayList();
Statement stmt = null;
ResultSet rs = null;
List list = new ArrayList();
try{
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from orders");
while(rs.next()){
Map map = new HashMap();
map.put("order_no",new Long(rs.getLong("order_no")));
map.put("employee",rs.getString("employee"));
map.put("country",rs.getString("country"));
map.put("customer",rs.getString("customer"));
map.put("order2005",new Float(rs.getFloat("order2005")));
map.put("order2006",new Float(rs.getFloat("order2006")));
map.put("order2007",new Float(rs.getFloat("order2007")));
map.put("order2008",new Float(rs.getFloat("order2008")));
map.put("delivery_date",rs.getString("delivery_date"));
list.add(map);
}
rs.close();
stmt.close();
}catch(Exception e){
}
closeConnection(conn);
return list;
}
int[] insertOrders( List updatedList){
int[] opresults=null;
String sql="INSERT INTO orders (employee,country,customer,order2005,order2006,order2007,order2008,delivery_date) VALUES (?,?,?,?,?,?,?,?)";
Connection conn=null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i=;i<updatedList.size();i++){
Map record= (Map)updatedList.get(i);
pstmt.setString(1,String.valueOf(record.get("employee")));
pstmt.setString(2,String.valueOf(record.get("country")));
pstmt.setString(3,String.valueOf(record.get("customer")));
pstmt.setString(4,String.valueOf(record.get("order2005")));
pstmt.setString(5,String.valueOf(record.get("order2006")));
pstmt.setString(6,String.valueOf(record.get("order2007")));
pstmt.setString(7,String.valueOf(record.get("order2008")));
pstmt.setString(8,String.valueOf(record.get("delivery_date")));
pstmt.addBatch();
}
opresults = pstmt.executeBatch();
} catch (SQLException e) {
opresults=null;
}finally{
closeConnection(conn);
}
return opresults;
}
int[] updateOrders( List updatedList){
int[] opresults=null;
String sql="UPDATE orders SET customer=?, order2008=?, delivery_date=? WHERE order_no= ? ";
Connection conn=null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i=;i<updatedList.size();i++){
Map record= (Map)updatedList.get(i);
pstmt.setString(1,String.valueOf(record.get("customer")));
pstmt.setString(2,String.valueOf(record.get("order2008")));
pstmt.setString(3,String.valueOf(record.get("delivery_date")));
pstmt.setString(4,String.valueOf(record.get("order_no")));
pstmt.addBatch();
}
opresults = pstmt.executeBatch();
} catch (SQLException e) {
opresults=null;
}finally{
closeConnection(conn);
}
return opresults;
}
int[] deleteOrders( List updatedList){
int[] opresults=null;
String sql="DELETE FROM orders WHERE order_no= ? ";
Connection conn=null;
PreparedStatement pstmt = null;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
for (int i=;i<updatedList.size();i++){
Map record= (Map)updatedList.get(i);
pstmt.setString(1,String.valueOf(record.get("order_no")));
pstmt.addBatch();
}
opresults = pstmt.executeBatch();
} catch (SQLException e) {
opresults=null;
}finally{
closeConnection(conn);
}
return opresults;
}
boolean saveOrders(List insertedRecords , List updatedList, List deletedRecords){
//you can control transaction, commit, rollback here
int[] insertCodes = insertOrders(insertedRecords);
int[] updateCodes = updateOrders(updatedList);
int[] deleteCodes = deleteOrders(deletedRecords);
boolean success=insertCodes!=null && updateCodes!=null && deleteCodes!=null;
return success;
}
%>
<%
// GridServerHandler is server side wrapper, you can get all the info posted to server in your Java way instead of JavaScript
GridServerHandler gridServerHandler=new GridServerHandler(request,response);
String operation = request.getParameter("actionMethod");
if("save".equals(operation)){
System.out.println("----save-----");
boolean success=true;
//取得新增的数据集合, 每条数据记录在 map 里
List insertedRecords = gridServerHandler.getInsertedRecords();
//取得修改的数据集合, 每条数据记录在 map 里
List updatedList = gridServerHandler.getUpdatedRecords();
//取得删除的数据集合, 每条数据记录在 map 里
List deletedRecords = gridServerHandler.getDeletedRecords();
// 如果希望取得bean的集合那么请使用有参方法: xxx.getXXXXXXRecords(Class beanClass);
//例如: List updateList = gridServerHandler.getUpdatedRecords(StudentVO.class);
//调用"相应的方法" 来update delete insert数据
success = saveOrders(insertedRecords , updatedList, deletedRecords );
//设置该次操作是否成功.
gridServerHandler.setSuccess(success);
//如果操作不成功你也可以自定义一些异常信息发送给客户端.
// gridServerHandler.setSuccess(false);
// gridServerHandler.setException("... exception info ...");
//向客户端输出json字符串.
out.print(gridServerHandler.getSaveResponseText());
}else { //client is retrieving data
List list = listOrders();
System.out.println("----list-----");
int totalRowNum= list.size();
// 取得总记录数
gridServerHandler.setTotalRowNum(totalRowNum);
//调用"相应的方法" 来取得数据.下面4个方法通常对于进行分页查询很有帮助,根据需要使用即可.
// gridServerHandler.getStartRowNum() 当前页起始行号
// gridServerHandler.getEndRowNum() 当前页结束行号
// gridServerHandler.getPageSize() 每页大小
// gridServerHandler.getTotalRowNum() 记录总条数
// 本例中list里的元素是 map,
// 如果元素是bean, 请使用 gridServerHelp.setData(list,BeanClass.class);
gridServerHandler.setData(list);
// gridServerHandler.setException("your exception message");
//向客户端输出json字符串.
out.print(gridServerHandler.getLoadResponseText());
System.out.print(gridServerHandler.getLoadResponseText());
// 你也可以使用 gridServerHandler.getLoadResponseText() 来取得字符串.
// 然后自行向客户端输出, 这样做的好处是你可以自己来决定response的contentType和编码.
}
%>

通过上面的代码可以看出操作grid的对象是GridServerHandler
使用GridServerHandler对象，来操作grid中的数据。

上传了demo，部署后访问demo地址为：
http://localhost:8080/GtGridDemo/views/demo1.jsp
.................................................................demo2.html
.................................................................demo3.jsp
.................................................................demo4.html
.................................................................demo5.html
通过以上demo实现以下功能
简单载入一个结果集（select top 20 * from Orders），主要展示以下特点(如何load数据，如何锁定表头列头，跨列表头如何设定，如何设定客户端按列排序)
带2个条件查询Orders表（如何传入查询条件查询），
主从表的载入（orders, Order Details）
可以的编辑单表Grid（单元格的编辑框如何定义，如何add和delete一行，编辑后的数据如何提交给jsp）
可以的编辑主从表Grid（重点体现子表如何编辑，并保持主记录和子记录的关系，编辑后的数据如何提交给jsp）
打印功能的实现(pdf，excel，)

转载于:https://my.oschina.net/u/180932/blog/41858