HighCharts获取EXCLE数据 c#

在阅读本文前，需要了解MVC(因为我的demo使用的mvc4 web 框架)，Jquery,ajax,json数据格式

第一步：获取EXCLE数据，封装到Json中(它会获取excle有记录的表,如sheet1$,sheet2$的各前hours行汇总到一个json中)

变量说明：hours:x坐标；nums:曲线条数；

excle表结构: 

 

 1         public ActionResult GetData(string hours, string nums) {
 2             int Nums = Convert.ToInt16(nums);
 3             string strConn = @"Provider = Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source =" + "d:\\sample.xls";//当然你可以通过传值来设置数据源
 4             OleDbConnection conn = new OleDbConnection(strConn);
 5             conn.Open();
 6             System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
 7             conn.Close();
 8             List<string> tblName = new List<string>();
 9             for (int i = dt.Rows.Count - 1; i >= 0; i--)
10             {
11                 tblName.Add(dt.Rows[i]["TABLE_NAME"].ToString());
12             }
13             string strCom;
14             DataSet ds = new DataSet();
15             OleDbCommand cmd = new OleDbCommand();
16             for (int i = 0; i < Nums; i++)
17             {
18                 strCom = " SELECT top "+hours+" * FROM [" + tblName[i] + "];";
19                 cmd.Connection = conn;
20                 cmd.CommandText = strCom;
21                 OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
22                 conn.Open();
23                 adapter.Fill(ds, "table");
24                 conn.Close();
25             }
26             string data = ToJson(ds.Tables[0]);
27             return Json(data);
28         }

 将DataTable 的每行转换为[{"hours":"1","batchNo":"001AA","value":0.3937007874},{"hours":"1","batchNo":"001AA","value":0.3937007874},]

 1         public string ToJson(DataTable dt)
 2         {
 3             StringBuilder jsonString = new StringBuilder();
 4             jsonString.Append("[");
 5             DataRowCollection drc = dt.Rows;
 6             for (int i = 0; i < drc.Count; i++)
 7             {
 8                 jsonString.Append("{");
 9                 for (int j = 0; j < dt.Columns.Count; j++)
10                 {
11                     string strKey = dt.Columns[j].ColumnName;
12                     string strValue = drc[i][j].ToString();
13                     if (strKey != "value")
14                     {                        
15                         jsonString.Append("\"" + strKey + "\":\"");
16                         if (j < dt.Columns.Count - 1)
17                         {
18                             jsonString.Append(strValue + "\",");
19                         }
20                         else
21                         {
22                             jsonString.Append(strValue + "\"");
23                         }
24                     }
25                     else
26                     {
27                         jsonString.Append("\"" + strKey + "\":");
28                         if (j < dt.Columns.Count - 1)
29                         {
30                             jsonString.Append(strValue + ",");
31                         }
32                         else
33                         {
34                             jsonString.Append(strValue + "");
35                         }
36                     }
37                     
38                 }
39                 jsonString.Append("},");
40             }
41             jsonString.Remove(jsonString.Length - 1, 1);
42             jsonString.Append("]");
43             return jsonString.ToString();
44         }

 第二步：index.cshtml 的脚本引用

 1     <script src="~/Scripts/jquery-1.7.1.min.js"></script>
 2     <script src="~/Scripts/jquery.easyui.min.js"></script>
 3     <script src="~/Scripts/highcharts.js"></script>
 4     <script src="~/Scripts/exporting.js"></script>
 5     <script src="~/Scripts/HighChart.js"></script>

HighChart.js 内容

 1 var HighChart = {
 2     ChartDataFormate: {
 3         FormatGroupData: function (data) {//处理分组数据，数据格式：name：XXX，group：XXX，value：XXX用于折线图、柱形图（分组，堆积）
 4             var names = new Array();
 5             var groups = new Array();
 6             var series = new Array();
 7             for (var i = 0; i < data.length; i++) {
 8                 // if (!names.contains(data[i].name)) {//括号内的方法有错误
 9                 if (jQuery.inArray(data[i].hours, names) == -1)
10                     names.push(data[i].hours);
11                 if (jQuery.inArray(data[i].batchNo, groups) == -1)
12                     groups.push(data[i].batchNo);
13             } 
14             for (var i = 0; i < groups.length; i++) {
15                 var temp_series = {};
16                 var temp_data = new Array();
17                 for (var j = 0; j < data.length; j++) {
18                     for (var k = 0; k < names.length; k++)
19                         if (groups[i] == data[j].batchNo && data[j].hours == names[k])
20                         {
21                             temp_data.push(data[j].value);
22                         }                               
23                 }
24                 temp_series = { name: groups[i], data: temp_data };
25                 series.push(temp_series);
26             }
27             return { category: names, series: series };
28         }
29     },
30     ChartOptionTemplates: {        
31         Line: function (data, name, title) {
32             var line_datas = HighChart.ChartDataFormate.FormatGroupData(data);            
33             var option = {
34                 title: {
35                     text: title || '',
36                     x: -20
37                 },
38                 subtitle: {
39                     text: '',
40                     x: -20
41                 },
42                 xAxis: {
43                     categories: line_datas.category
44                 },
45                 yAxis: {
46                     title: {
47                         text: name || ''
48                     },
49                     plotLines: [{
50                         value: 0,
51                         width: 1,
52                         color: '#808080'
53                     }]
54                 },
55                 tooltip: {
56                     valueSuffix: ''
57                 },
58                 legend: {
59                     layout: 'horizontal',
60                     align: 'center',
61                     verticalAlign: 'bottom'
62                 },
63                 series: line_datas.series
64             };
65             return option;
66         }
67     },
68     RenderChart: function (option, container) {
69         container.highcharts(option);
70     }
71 };

第三步：index.cshtml界面内容

 1 <script type="text/javascript">
 2         function getData() {
 3             var hours = $("#hours").val();
 4             var nums = $("#nums").val();
 5             var title = $("#title").val();
 6             $.ajax({
 7                 url: "/Charts/GetData/",
 8                 type: "POST",
 9                 datatype: "json",
10                 data: { Hours: hours, Nums: nums },                
11                 success: function (data) {
12                     var data = data;
13                     var json = JSON.parse(data);
14                     //绘制
15                     var opt = HighChart.ChartOptionTemplates.Line(json, '淘汰率', title);
16                     var container = $("#container");
17                     HighChart.RenderChart(opt, container);
18                 }
19             });
20             
21         }
22 </script>
23     
24 </head>    
25 <body>
26     <div>
27         <fieldset>
28             <legend>信息设置</legend>
29             筛选时间：<input class="easyui-validatebox" type="text" id="hours" name="hours" /> h&nbsp;&nbsp;&nbsp;&nbsp;曲线条数：<input class="easyui-validatebox" type="text" id="nums" name="nums" /> 条&nbsp;&nbsp;&nbsp;&nbsp;标题：<input class="easyui-validatebox" type="text" id="title" name="title" /> 
30             <a href="javascript:void(0)" class="easyui-linkbutton" id="btnLogin" iconcls="icon-ok" onclick="getData()">绘制</a>
31         </fieldset>
32     </div>
33     <div id="container" style="min-width:700px;height:400px"></div>
34 </body>
35 </html>

 效果预览：

 

 

  　　

 

转载于:https://www.cnblogs.com/ketai/p/4974390.html