datagrid动态生成列

Oracle将数据进行行转列：http://blog.csdn.net/u013533810/article/details/37956195

1、程序中的条件参数

                    string WhereStr = context.Request["WhereStr"].ToString();
                    string ColumnsStr = context.Request["ColumnsStr"].ToString();
                    PrisonPM.Model.Row_To_Col model = new Model.Row_To_Col();
                    model.tabname = "VW_DAYRPT_STDTSK_R";
                    if (string.IsNullOrEmpty(ColumnsStr))
                    {
                        model.group_col = "DPTKEY";
                    }
                    else 
                    {
                        model.group_col = "DPTKEY," + ColumnsStr;
                    }
                    
                    model.column_col = "DAYRPTDATE";
                    model.value_col = "PRODTIME";
                    model.Aggregate_func = "Sum";
                    model.colorder = "DAYRPTDATE asc";
                    model.roworder = "DPTKEY asc";
                    model.when_value_null = "";
                    model.where_str = WhereStr;
                    model.rowcount_str = "合计";

                    PrisonPM.BLL.PublicReport BLL = new BLL.PublicReport();

                    DataSet ds = BLL.Sp_BookStdtskReport(model);
                    strR=JSONhelper.GetAsyncDataGridData(ds.Tables[0]);

2、程序中调用Oracle中的函数

public DataSet Sp_BookStdtskReport(PrisonPM.Model.Row_To_Col model)
        {
            OracleParameter Cursor = new OracleParameter();
            Cursor.ParameterName = "cur";
            Cursor.OracleType = OracleType.Cursor;
            Cursor.Direction = System.Data.ParameterDirection.Output;
            OracleParameter[] parameters = { 
                                               new OracleParameter("tabname",OracleType.VarChar,200),
                                               new OracleParameter("group_col",OracleType.VarChar,2000),
                                               new OracleParameter("column_col",OracleType.VarChar,200),
                                               new OracleParameter("value_col",OracleType.VarChar,200),
                                               new OracleParameter("Aggregate_func",OracleType.VarChar,200),
                                               new OracleParameter("colorder",OracleType.VarChar,200),
                                               new OracleParameter("roworder",OracleType.VarChar,200),
                                               new OracleParameter("when_value_null",OracleType.VarChar,200),
                                               new OracleParameter("where_str",OracleType.VarChar,2000),
                                               new OracleParameter("rowcount_str",OracleType.VarChar,200),
                                               Cursor
                                           };
            parameters[0].Value = model.tabname;
            parameters[1].Value = model.group_col;
            parameters[2].Value = model.column_col;
            parameters[3].Value = model.value_col;
            parameters[4].Value = model.Aggregate_func;
            parameters[5].Value = model.colorder;
            parameters[6].Value = model.roworder;
            parameters[7].Value = model.when_value_null;
            parameters[8].Value = model.where_str;
            parameters[9].Value = model.rowcount_str;
            return PrisonPM.DBUtility.DbHelperOra.RunProcedure("PKG_STM_GETDATA_TABLE.Sp_Row_To_Col", parameters, "report");
        }

3、将数据转换成JSON类型

        public static string GetAsyncDataGridData(DataTable dt)
        {
            StringBuilder sb = new StringBuilder();
            //生成数据JSON
            JavaScriptSerializer jss = new JavaScriptSerializer();
            System.Collections.ArrayList dic = new System.Collections.ArrayList();
            foreach (DataRow dr in dt.Rows)
            {
                System.Collections.Generic.Dictionary<string, object> drow = new System.Collections.Generic.Dictionary<string, object>();
                foreach (DataColumn dc in dt.Columns)
                {
                    drow.Add(dc.ColumnName, dr[dc.ColumnName]);
                }
                dic.Add(drow);

            }
            //生成列名JSON
            sb.Append("[");
            foreach (DataColumn dc in dt.Columns)
            {
                sb.Append("{");
                sb.Append(string.Format("\"field\":\"{0}\",",dc.ColumnName));
                sb.Append(string.Format("\"title\":\"{0}\",", dc.ColumnName));
                sb.Append(string.Format("\"width\":\"{0}\",", 80));
                sb.Append(string.Format("\"align\":\"{0}\"","center"));
                sb.Append("},");
            }
            if (sb.ToString().EndsWith(","))
            {
                sb.Remove(sb.Length - 1, 1);
            }
            sb.Append("]");

            return "{\"total\":" + dt.Rows.Count + ",\"rows\":" + jss.Serialize(dic) +",\"columns\":"+sb.ToString()+ "}";
        }

4、前台界面布局

    <script type="text/javascript" language="javascript">
        $(function () {
            $('#Combo_DPT').combobox({
                url: '../PublicData/GetWorkShopData.aspx',
                valueField: 'DPTKEY',
                textField: 'DPTNAME'
            });
            $('#tt').datagrid({
                width: 600,
                height: 600,
                fit: true,
                toolbar: '#toolbar',
                rownumbers: true, //行号
                singleSelect: true, //单行选取  
                pagination: false, //显示分页               
                columns: [[]]
            });
            $('#txt_Colomns').combobox({
                url: "../Handler/STM/Stm_Menu.ashx?flag=txt_Colomns",
                multiple: true,
                valueField: 'COLUMN_NAME',
                textField: 'COLUMN_NAME'
            });


        });
        function WhereStr() {
            var WhereStr = " Where 1=1";
            if ($("#Combo_DPT").combobox("getValue")) {
                WhereStr = WhereStr + " and DPTKEY=" + $("#Combo_DPT").combobox("getValue");
            }
            if ($("#txt_LABOURCODE").val()) {
                WhereStr = WhereStr + " and LABOURCODE like'%" + $("#txt_LABOURCODE").val() + "%'";
            }
            if ($("#txt_LABOURNAME").val()) {
                WhereStr = WhereStr + " and LABOURNAME like'%" + $("#txt_LABOURNAME").val() + "%'";
            }
            if ($("#txt_PARTMCODE").val()) {
                WhereStr = WhereStr + " and PARTMCODE like'%" + $("#txt_PARTMCODE").val() + "%'";
            }

            return WhereStr;
        }

        function Fn_Search() {
            var ColumnsName = $("#txt_Colomns").combobox("getValues");
            var ColumnsStr = JSON.stringify(ColumnsName);
            ColumnsStr = ColumnsStr.substring(1, ColumnsStr.length - 1).replace(/\"/g, ""); ;
            $.ajax({
                url: '../Handler/STM/Stm_Menu.ashx?flag=ssssss&rand=' + Math.random(),
                type: "POST",
                dataType: "JSON", //可以为Json
                data: { WhereStr: WhereStr(), ColumnsStr: ColumnsStr },
                success: function (data) {
                    $('#tt').datagrid({ columns: [data.columns] }).datagrid("loadData", data);
                },
                error: function (XMLHttpRequest, textStatus, errorThrown) {
                    //window.parent.frameReturnByMes("提交失败2");
                    return;
                }
            });
        }
    </script>
</head>
<body>
    <table id="tt">
    </table>
    <div id="toolbar">
        所在车间：<input id="Combo_DPT" class="easyui-combobox" />
        客户款号：<input id="txt_PARTMCODE" class="textbox" />
        员工编号：<input id="txt_LABOURCODE" class="textbox" />
        员工姓名：<input id="txt_LABOURNAME" class="textbox" />
        显示字段：<input id="txt_Colomns" class="easyui-combobox" />
        <a href="#" class="easyui-linkbutton" iconcls="icon-search" οnclick="Fn_Search()"
            plain="false">查询</a>
    </div>
</body>