Sigma Grid是一个采用纯javascript开发的Ajax数据表格。
特性包括:
1.基于CSS控制外观,内置Classic、Vista、Mac、XP四种风格。
2.提供单元格直接编辑功能,可以自定义编辑器,为单元格设置数学计算公式,自定义单元格表现方式。
3.提供灵活的表头控制功能比如:锁定表头,排序,拖动调整列宽。
4.支持分页,数据过滤,根据数据生成柱状图,饼状图,曲线图。
5.支持从多种数据源加载数据: JSON、XML、CSV等。
6.支持多种浏览器:IE6.0+FireFox2.0+Safari3.0+Opera9.0+。
使用步骤
使用sigma grid之前必须先引用相关的js文件和css文件,文件下载地址:1、
http://download.csdn.net/detail/wupd2014/9243683
http://download.csdn.net/detail/wupd2014/9243483
<link rel="stylesheet" type="text/css" href="../Scripts/sigma_grid/gt_grid.css" />
<script type="text/javascript" src="../Scripts/sigma_grid/test_data.js"></script>
<script type="text/javascript" src="../Scripts/sigma_grid/gt_msg_cn.js"></script>
<script type="text/javascript" src="../Scripts/sigma_grid/gt_const.js"></script>
<script type="text/javascript" src="../Scripts/sigma_grid/gt_grid_all.js"></script>
.mybutton-cls {background: url('../Scripts/sigma_grid/myImage/mybutton.png') no-repeat center center; }
引用上述文件之后接着进行使用的配置
<script>
var grid = "myGrid1";
var dsOption = {
fields: [
{ id: "用户姓名" },
{ id: "本省动态", type: 'float' },
{ id: "标准规范", type: 'float' },
{ id: "第二批创建单位", type: 'float' },
{ id: "第一批创建单位", type: 'float' },
{ id: "国内动态", type: 'float' },
{ id: "基本情况", type: 'float' },
{ id: "节能案例", type: 'float' },
{ id: "节能产品", type: 'float' },
{ id: "节能常识", type: 'float' },
{ id: "节能宣传周", type: 'float' },
{ id: "漂窗新闻", type: 'float' },
{ id: "通知公告", type: 'float' },
{ id: "新产品新技术", type: 'float' },
{ id: "要闻聚焦", type: 'float' },
{ id: "在线留言", type: 'float' },
{ id: "政策法规", type: 'float' },
{ id: "政策法规解读", type: 'float' },
{ id: "资料下载", type: 'float' }
],
recordType: 'object'
}
var colsOption = [
{ id: "用户姓名", header: "1", sortable: true, resizable: false, inChart: false, align: "center", width: 80 },
{ id: "本省动态", header: "本省动态", align: "center", width: 60, inChart: true, chartColor: 'eecc99' },
{ id: "标准规范", header: "标准规范", align: "center", width: 60, inChart: true, chartColor: '99bb88' },
{ id: "第二批创建单位", header: "第二批创建单位", align: "center", width: 60, inChart: true, chartColor: '77aa33' },
{ id: "第一批创建单位", header: "第一批创建单位", align: "center", width: 60, inChart: true, chartColor: 'bb0066' },
{ id: "国内动态", header: "国内动态", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "基本情况", header: "基本情况", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "节能案例", header: "基本情况", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "节能产品", header: "节能产品", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "节能常识", header: "节能常识", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "节能宣传周", header: "节能宣传周", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "漂窗新闻", header: "漂窗新闻", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "通知公告", header: "通知公告", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "新产品新技术", header: "新产品新技术", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "要闻聚焦", header: "要闻聚焦", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "在线留言", header: "在线留言", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "政策法规", header: "政策法规", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "政策法规解读", header: "政策法规解读", align: "center", width: 60, inChart: false, chartColor: 'eecc99' },
{ id: "资料下载", header: "资料下载", align: "center", width: 60, inChart: false, chartColor: 'eecc99' }
];
Sigma.ToolFactroy.register('mybutton', {
cls: 'mybutton-cls',
toolTip: '信息统计图',
action: function (event, grid) {
popupDiv('pop_div');
function CreateObj(lab, vl) //声明对象
{
this.label = lab;
this.value = vl;
}
var data = grid.dataset.data[grid.dataset.data.length - 1];
var jsonData = [];
var i = 0;
for (var key in data) {
if (key != '用户姓名' && data[key] != '' && key != '__gt_row_key__' && key != '__gt_sn__' && key != 'index') {
jsonData.push(new CreateObj(key, data[key]));
}
}
//利用JSON拼接字符串提供的静态数据来作为数据源
FusionCharts.printManager.enabled(true);
var chartObj = new FusionCharts('../Scripts/fusionCharts/swf/Column2D.swf', 'flash-chart', '700', '350');
chartObj.setJSONData({
"chart": { "caption": "数据上报统计图", "showpercentvalues": "1" },
"data": jsonData
});
chartObj.render('pop_div');
}
});
Sigma.ToolFactroy.register('mybuttonuser', {
cls: 'mybutton-userchart',
toolTip: '信息统计图',
action: function (event, grid) {
popupDiv('pop_div'); //弹出层,后面将图标渲染到该弹出层
function CreateObj(lab, vl) //声明对象
{
this.label = lab;
this.value = vl;
}
var jsonDataPie = [];
var data = grid.dataset.data;
for (var i = 0; i < data.length - 1; i++) {
var lbl = data[i]['用户姓名'];
var vl = 0;
for (var key in data[i]) {
if (key != '用户姓名' && data[key] != '' && key != '__gt_row_key__' && key != '__gt_sn__' && key != 'index') {
vl += parseInt(data[i][key]);
}
}
if (vl != 0) {
jsonDataPie.push(new CreateObj(lbl, vl));
}
}
//利用JSON拼接字符串提供的静态数据来作为数据源
FusionCharts.printManager.enabled(true);
var chartObj = new FusionCharts('../Scripts/fusionCharts/swf/Pie3D.swf', 'flash-pie', '700', '350');
chartObj.setJSONData({
"chart": { "caption": "数据上报统计图", "showpercentvalues": "1" },
"data": jsonDataPie
});
chartObj.render('pop_div');
}
});
var gridOption = {
id: grid,
width: "100%",
height: 530,
loadURL: '../Ajax/getTableJson.ashx?type=read',
exportURL: '../Ajax/getTableJson.ashx?type=excel',
exportFileName: 'test_export_xls',
container: 'gridbox',
replaceContainer: true,
encoding: 'UTF-8',
customHead: 'myHead1',
dataset: dsOption,
columns: colsOption,
pageSize: 100000,
pageSizeList: [5, 10, 15, 20, 1000],
SigmaGridPath: '../Scripts/sigma_grid',
toolbarPosition: 'bottom',
toolbarContent: 'nav |goto | pagesize | reload | print csv xls mybutton mybuttonuser | state',
};
var mygrid = new Sigma.Grid(gridOption);
Sigma.Util.onLoad(Sigma.Grid.render(mygrid));
$("#btnCX").click(function () {
mygrid.loadURL = '../Ajax/getTableJson.ashx?type=read&beginTime=' + $("#<%=beginHid.ClientID%>").val() + "&endTime=" + $("#<%=endHid.ClientID%>").val() + "&user=" + $("#<%=ddlAccount.ClientID%>").val();
mygrid.reload();
});
</script>
服务端数据:Ajax/getTableJson.ashx(Json数据格式)
首选汇总的是不同用户在不同栏目上发表或上传的资料数量,因此需要对文章表及目录表进行分组查找到各个用户对应的数据
文章表的基本数据结构为 文章ID、文章title、文章catgoryID 文章author
目录ID主要为 目录categoryID 目录名称
1、先查询出栏目名称、数量及作者
SELECT
a.loginid,c.name, cnt=COUNT(b.articleid) FROM dbo.T_USER A
left JOIN
T_NEWSARTICLES b ON A.loginid=b.createduser
LEFT JOIN
T_CATEGORY C on B.categoryid = c.categoryid
GROUP BY a.loginid,c.categoryid,c.name
2、在进行行专列
SELECT
用户姓名=loginid,
标准规范= ISNULL([标准规范],0),
....
from
(
SELECT
a.loginid,c.name, cnt=COUNT(b.articleid)
FROM
dbo.T_USER A
left JOIN
T_NEWSARTICLES b ON A.loginid=b.createduser
LEFT JOIN
T_CATEGORY C on B.categoryid = c.categoryid
GROUP BY a.loginid,c.categoryid,c.name
)a
PIVOT(MAX(cnt) FOR name IN(
标准规范,
...)
最后采用 union all
SELECT
用户姓名='全省统计',
标准规范= ISNULL([标准规范], 0),
....
FROM
(
select cnt=count(*),b.name from T_NEWSARTICLES a, T_CATEGORY b where a.categoryid=b.categoryid group by b.name) c
PIVOT(MAX(cnt) FOR name IN(
标准规范,
...
)
)b
返回的DataTable转换为Json,数据格式为: {data:[{column1:1,column2:2,column3:3},{column1:1,column2:2,column3:3}]}
public string DateTableToJson(string TableName, DataTable tb)
{
if (tb == null || tb.Rows.Count == 0) {return "";}
string strName = TableName;
StringBuilder sbJson = new StringBuilder();
sbJson.Append("{");
sbJson.Append("\"" + strName + "\":[");
Hashtable htColumns = new Hashtable();
for (int i = 0; i < tb.Columns.Count; i++){
htColumns.Add(i, tb.Columns[i].ColumnName.Trim());
}
for (int j = 0; j < tb.Rows.Count; j++){
if (j != 0){
sbJson.Append(",");
}
sbJson.Append("{");
for (int c = 0; c < tb.Columns.Count; c++){
sbJson.Append("\"" + htColumns[c].ToString() + "\"" + ":\"" + tb.Rows[j][c].ToString().Replace(",", ",").Replace(":", ":").Replace("\r\n", "<br />") + "\",");
}
sbJson.Append("\"index\":" + "\"" + j.ToString() + "\""); //序号
sbJson.Append("}");
}
sbJson.Append("],\"recordType\" : \"object\"}");
return sbJson.ToString();
}
然后调用 HttpContext.Current.Response.Write(str);将得到Json字符串返回
最终的效果图如下: