ASP.NET MVC+HighCharts开发统计图表
HighCharts是开源的Web图表js组件，与D3.js一样，经常用于数据可视化。HighCharts图表类型丰富，功能非常强大，是很好的数据可视化解决方案，其官方网站为：http://www.hcharts.cn/，感兴趣的读者可以自行去下载HighCharts并按照给出的Example尝试着开发，基本上按照示例中的例子，把数据部分替换成自己想要展示的数据就可以了，非常容易上手。本篇博客将结合ASP.NET MVC和HighCharts开发统计图表功能，为简单起见，这里只演示三个最基本、最常用的图表类型（柱状图、饼图）。
一、横向柱状图
前端cshtml代码：
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>退休单位统计</title> <script src='@Url.Content("~/Scripts/jquery-1.7.1.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/exporting.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts-zh_CN.js")'></script> </head> <body> <div id="container" style="min-width: 400px; height: 400px"> </div> <script type="text/javascript"> $(function () { $.ajax({ url: '@Url.Action("GetTXDW", "TJTB")', type: 'get', success: function (result) { var name = result.split(';')[0]; var data = result.split(';')[1]; var nname = eval('(' + name + ')'); var ddata = eval('(' + data + ')'); $('#container').highcharts({ chart: { type: 'bar' }, title: { text: '退休人数最多的十个单位' }, subtitle: { text: '' }, xAxis: { categories: nname, // [ // '自由职业', // 'xxx有限公司', // 'xxx工程总公司', // 'xxx有限公司', // 'xxx总公司', // 'xxx有限公司', // 'xxx有限公司', // 'xxx有限公司', // 'xxx厂', // 'xxx有限责任公司' // ], title: { text: null } }, yAxis: { min: 0, title: { text: '人数', align: 'high' }, labels: { overflow: 'justify' } }, tooltip: { valueSuffix: '人' }, plotOptions: { bar: { dataLabels: { enabled: true, allowOverlap: true } } }, legend: { layout: 'vertical', align: 'right', verticalAlign: 'top', x: -40, y: 100, floating: true, borderWidth: 1, backgroundColor: ((Highcharts.theme && Highcharts.theme.legendBackgroundColor) || '#FFFFFF'), shadow: true }, credits: { enabled: false }, series: [{ name: '人数', data: ddata //data: [23926, 2328, 1828, 1816, 1485, 1464, 1408, 1164, 1124, 1124] } ] }); } }); }) </script> </body> </html>
控制器cs代码：
/// <summary> /// 退休单位统计 /// </summary> /// <returns></returns> [HttpGet] public string GetTXDW() { string result1 = string.Empty; string result2 = string.Empty; string sql = @"with a as(select rownum rn,t.* from( select gzdw,count(gzdw) xl from T_RYXX group by gzdw order by xl desc) t) select gzdw,xl from a where a.rn>=1 and a.rn<=10"; result1 += "["; result2 += "["; DataSet ds = OracleHelper.Query(sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { result1 += "'" + ds.Tables[0].Rows[i]["GZDW"].ToString() + "'"; result2 += ds.Tables[0].Rows[i]["XL"].ToString(); if (i != ds.Tables[0].Rows.Count - 1) { result1 += ","; result2 += ","; } } result1 += "]"; result2 += "]"; return result1 + ";" + result2; }
二、饼图
前端cshtml代码：
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>性别统计</title> <script src='@Url.Content("~/Scripts/jquery-1.7.1.js")' type="text/javascript"></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts.js")' type="text/javascript"></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts-3d.js")' type="text/javascript"></script> <script src='@Url.Content("~/Scripts/hightcharts/exporting.js")' type="text/javascript"></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts-zh_CN.js")' type="text/javascript"></script> </head> <body> <div id="container" style="min-width: 400px; height: 400px"> </div> <script type="text/javascript"> $(function () { $.ajax({ url: '@Url.Action("GetXBTJ", "TJTB")', type: 'get', success: function (result) { var data = eval('(' + result + ')'); $('#container').highcharts({ chart: { plotBackgroundColor: null, plotBorderWidth: null, plotShadow: false }, title: { text: '退休人员性别比例' }, tooltip: { headerFormat: '{series.name}<br>', pointFormat: '{point.name}: <b>{point.percentage:.1f}%</b>' }, plotOptions: { pie: { allowPointSelect: true, cursor: 'pointer', dataLabels: { enabled: true, format: '<b>{point.name}</b>: {point.percentage:.1f} %', style: { color: (Highcharts.theme && Highcharts.theme.contrastTextColor) || 'black' } } } }, series: [{ type: 'pie', name: '退休人员性别比例', data: data }] }); } }); }); </script> </body> </html>
控制器cs代码：
/// <summary> /// 性别统计 /// </summary> /// <returns></returns> [HttpGet] public string GetXBTJ() { string result = string.Empty; string sql = @"with a as(select substr((case when length(sfzhm)=15 then idcard15to18(sfzhm) else sfzhm end),17,1) sfzhm from T_RYXX where regexp_replace(sfzhm,'^[-\+]?\d+(\.\d+)?$','') is null order by to_number(dah)) select t.xb,count(t.xb) xl from ( select (case when mod(to_number(sfzhm),2)=0 then '女' else '男' end) xb from a) t group by t.xb"; DataSet ds = OracleHelper.Query(sql); result += "["; for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { result += "["; result += "'" + ds.Tables[0].Rows[i]["XB"].ToString() + "'"; result += ","; result += ds.Tables[0].Rows[i]["XL"].ToString(); result += "]"; if (i != ds.Tables[0].Rows.Count - 1) { result += ","; } } result += "]"; return result; }
三、纵向柱状图
前端cshtml代码：
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>每日进出库统计</title> <script src='@Url.Content("~/Scripts/jquery-1.7.1.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts-3d.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/exporting.js")'></script> <script src='@Url.Content("~/Scripts/hightcharts/highcharts-zh_CN.js")'></script> </head> <body> <div id="container" style="min-width: 400px; height: 400px"> </div> <script type="text/javascript"> $(function () { $.ajax({ url: '@Url.Action("GETJCK", "TJTB")', type: 'get', success: function (result) { var date = result.split(';')[0]; var ink = result.split(';')[1]; var outk = result.split(';')[2]; var data1 = eval('(' + date + ')'); var data2 = eval('(' + ink + ')'); var data3 = eval('(' + outk + ')'); $('#container').highcharts({ chart: { type: 'column' }, title: { text: '每日档案进出库情况（最近10天）' }, subtitle: { text: '' }, xAxis: { categories: data1, crosshair: true }, yAxis: { min: 0, title: { text: '档案进出库' } }, tooltip: { headerFormat: '<span style="font-size:10px">{point.key}</span><table>', pointFormat: '<tr><td style="color:{series.color};padding:0">{series.name}: </td>' + '<td style="padding:0"><b>{point.y:.0f}</b></td></tr>', footerFormat: '</table>', shared: true, useHTML: true }, plotOptions: { column: { pointPadding: 0.2, borderWidth: 0 } }, series: [{ name: '进库', data: data3 }, { name: '出库', data: data2 }] }); } }); }); </script> </body> </html>
控制器cs代码：
/// <summary> /// 进出库统计 /// </summary> /// <returns></returns> [HttpGet] public string GETJCK() { string result1 = string.Empty; string result2 = string.Empty; string result3 = string.Empty; string sql = @"with a as(select to_char(czsj,'yyyy-mm-dd') jysj,count(*) jyxl from t_dajy where to_char(czsj,'yyyy-mm-dd')>to_char(sysdate-10,'yyyy-mm-dd') group by to_char(czsj,'yyyy-mm-dd')), b as(select to_char(ghsj,'yyyy-mm-dd') ghsj,count(*) ghxl from t_dagh where to_char(ghsj,'yyyy-mm-dd')>to_char(sysdate-10,'yyyy-mm-dd') group by to_char(ghsj,'yyyy-mm-dd')), c as(select to_char(to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -rownum+1,'yyyy-mm-dd') as d from dual connect by rownum <=to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') -to_date(to_char(sysdate-10,'yyyy-mm-dd'),'yyyy-mm-dd')) select c.d,(case when a.jyxl is null then 0 else a.jyxl end) jyxl, (case when b.ghxl is null then 0 else b.ghxl end) ghxl from c left join a on a.jysj = c.d left join b on b.ghsj = c.d order by d desc"; result1 += "["; result2 += "["; result3 += "["; DataSet ds = OracleHelper.Query(sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { result1 += "'" + ds.Tables[0].Rows[i]["D"].ToString() + "'"; result2 += ds.Tables[0].Rows[i]["JYXL"].ToString(); result3 += ds.Tables[0].Rows[i]["GHXL"].ToString(); if (i != ds.Tables[0].Rows.Count - 1) { result1 += ","; result2 += ","; result3 += ","; } } result1 += "]"; result2 += "]"; result3 += "]"; return result1 + ";" + result2 + ";" + result3; }
