Sigma Grid 研究及使用

Sigma Grid 纯javascript，Sigma grid 用Ajax做grid展现并可实现数据的编辑，支持滚动表格及排序。它是非常强大的并且简单易用并且能整合php，asp.net,jsp,RoR

研究版本：Sigma Grid 2.2

Grid目录结构：
grid
|--->calendar
| |--calendar.js
| |--calendar-blue.css
| |--calendar-cn.js
| |--calendar-cn-utf8.js
| |--calendar-en.js
| |--calendar-setup.js
| |--simple-1.html
|--> flashchart
| |---->fusioncharts
| | |--->charts
| | | |--FCF_Area2D.swf
| | | |--略……(.swf)
| | |--ChangeDataXML.html
| | |--Chart.html
| | |--ChartChange.html
| | |--FusionCharts.js
|--->skin
| |---->china
| | |--->images
| | | |--cell_index_bg.gif
| | | |--略……(.gif)
| | |--skinstyle.css
| |---->default
| | |--->images
| | | |--button_bg.gif
| | | |-- 略……(.gif)
| | |--skinstyle.css
| |---->mac
| | |--->images
| | | |--button_bg.gif
| | | |-- 略……(.gif)
| | |--skinstyle.css
| |---->vista
| | |--->images
| | | |--cell_index_bg.gif
| | | |-- 略……(.gif)
| | |--skinstyle.css
|--gt_grid_all.js
|--gt_grid_height.css
|--gt_grid.css
|--gt_msg_en.js


Sigma Grid 2.2特征：
[color=orange]CSS based themes [/color]
Sigma grid appearance is controled via a stand-alone CSS(style sheet). Developer can change grid skin by defining themes in different CSS files. Classic, Vista, Mac, XP Style built in 2.0.

[color=orange]Event handlers & user API [/color]
Plentiful attibutes and event handlers give you more flexibility without coding too much.

[color=orange]Inline cell editing [/color]
Inline cell editing just as you would in MS Excel. Navigate with the tab or enter key to move between cells . End user also can remove / add / append rows. Dropdownlist, calendar, text editor built in 2.0.

[color=orange]Linkable grids[/color]
Allow you to create multiple grid instances on one web page. With events and JavaScript API, Developers can implement one-to-many or many-to-many relationships (master-detail) at ease.

[color=orange]Lockable header and column [/color]
This feature enable you to keep some columns and/or header always visible in spite of user scrolling grid. Similar to the window freeze panes option in MS Excel.

[color=orange]Math formulas for cells [/color]
Developer can specify some cells to be a formula instead of a value. What is more, whether a cell is editable could depend on a boolean formula.

[color=orange]Flexible headers [/color]
One of the new features is the ability to enable developer to work out as complex header as they wish. Nested header, image header, dropdown list control embeded header are all supported.

[color=orange]Online one-key printing [/color]
Online printing, to make it easy to print tabular data in WYSIWYG way. Built in button, no coding.

[color=orange]Pure javascript codes [/color]
Seamless Integration with any server side solution, such as j2ee, .net, PHP, perl. v Cross browers

[color=orange]More browser supported[/color]
IE 6.0+ \ FireFox 2.0+ \ Safari 3.0+ \ Opera9.0+ supported.

[color=orange]Paging[/color]
This feature enable developer display data page by page. Data will not be retrieved from server side until end user come to the page it is at. Developer can control how many rows are displayed at one time.

[color=orange]Resizable Columns [/color]
Columns width could be changed by end user draging columns header.

[color=orange]Single selection & multi selection[/color]
Sigma grid has the ability to select non-contiguous rows. This allows for more robust manipulation of records in the Grid.

[color=orange]Slice rendering[/color]
You may load thousands rows of data into grid in one time.Slice rendering could help you increase user experience. When this mode activated, everytime the vertical scroll bar is dragged, SigmaGrid will detemine which rows are visible and paint them out.

[color=orange]Sorting & various data types [/color]
End user can sort records by clicking on the header of a column. Developer can specify sorting way for various types including string,number,boolean,link,select and percent. Developer may define a new data type and a new way to sort.

[color=orange]User defined editor & renderer [/color]
Extensibility enable you to replace built-in cell editor with custom external component, present cell data as your customers' wish.

[color=orange]Various load sources[/color]
Developers can export data to / load data from variouse sources, including in JSON, XML, CSV (Comma Separated Values) format, to support further editing within spreadsheet or any other applications.

[color=orange]Flash chart built[/color]
Bar diagram, line diagram and pie diagram built in. Translation tabular data into diagram without any server-side coding.

[color=orange]Data filter[/color]
Data filter enables sigma grid to present the data users are interested in only.

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

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

<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列头

<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

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

[b]构建父grid数据结构[/b]

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'
}

[b]构建子grid数据结构[/b]

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

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 列样式
[color=red]/*
isCheckColumn:选项列
filterable：是否可以过滤此列
exportable：输出表格
id：id
header：显示的名称
width:宽度
inChart：是否在图表中统计
chartColor：图表颜色
*/[/color]

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}

];

[b]构建子grid 列样式[/b]
[color=red]/*

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：位置

*/[/color]

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  }

 ];

[b]父grid属性[/b]
[color=red]/*
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 - 状态
}
*/[/color]

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'
};

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

*/[/color]

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对象并输出

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的数据格式即可
[b]controller.jsp[/b]

<%@ 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=0;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=0;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=0;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中的数据。

[color=blue]上传了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，)[/color]