使用ECharts綁定SQL?

ECharts是百度商業前端數據可視化團隊的一款開源圖表控件，下面介紹結合SQL Server 2008進行數據綁定顯示圖表。

方法/步驟

打開百度echarts首頁，點擊下載最新的開發包，下載下來的文件如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

打開SQL Server 2008新建數據庫表 T_EchartData1, 表字段和表數據如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

新建數據庫表 T_EchartData2 ,表字段和表數據如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

打開VS2010或VS2012,新建Web頁面 EchartDemo.aspx及EchartDemo.aspx.cs ，核心代碼如下：

新建Web頁面 GetChartData.aspx 及 GetChartData.aspx.cs ，用於從數據庫獲取數據，代碼如下：

protected void Page_Load(object sender, EventArgs e)

{

string type = Request["type"];

if (!string.IsNullOrEmpty(type))

{

switch (type)

{

case "energyData":

GetEnergyDataAjaxData("", "");

break;

case "reportData":

GetReportDataAjaxData("");

break;

}

}

}

private void GetEnergyDataAjaxData(string level, string code)

{

List categoryList = new List ();

List seriesList = new List ();

List legendList = new List ();

DataTable dt = null;

using (SqlConnection con = new SqlConnection("Data Source=.;User ID=sa;Password=111111;Database=XXXXX;"))

{

string strSQL = "select top 12 * from T_EchartData1";

using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con))

{

DataSet ds = new DataSet();

adapter.Fill(ds);

dt = ds.Tables[0];

}

}

legendList.Add("今年能耗");

legendList.Add("去年能耗");

Series thisSeriesObj = new Series();

thisSeriesObj.id = 0;

thisSeriesObj.name = "今年能耗";

thisSeriesObj.type = "line"; //線性圖呈現

thisSeriesObj.data = new List();

thisSeriesObj.itemStyle = new itemStyle { normal = new normal { color = "#0099FF" } };

Series lastSeriesObj = new Series();

lastSeriesObj.id = 1;

lastSeriesObj.name = "去年能耗";

lastSeriesObj.type = "line"; //線性圖呈現

lastSeriesObj.data = new List();

lastSeriesObj.itemStyle = new itemStyle { normal = new normal { color = "#00CC00" } };

foreach (DataRow dr in dt.Rows)

{

categoryList.Add(dr[0].ToString() + "月");

thisSeriesObj.data.Add(dr[1]);

lastSeriesObj.data.Add(dr[2]);

}

seriesList.Add(thisSeriesObj);

seriesList.Add(lastSeriesObj);

var newObj = new

{

category = categoryList,

series = seriesList,

legend = legendList

};

Response.Write(JsonConvert.SerializeObject(newObj, Formatting.None));

Response.End();

}

private void GetReportDataAjaxData(string code)

{

List categoryList = new List ();

List seriesList = new List ();

List legendList = new List ();

DataTable dt = null;

using (SqlConnection con = new SqlConnection("Data Source=.;User ID=sa;Password=111111;Database=AT_DB;"))

{

string strSQL = "select * from T_EchartData2";

using (SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con))

{

DataSet ds = new DataSet();

adapter.Fill(ds);

dt = ds.Tables[0];

}

}

//設置legend數組

legendList.Add("電");

legendList.Add("水");

legendList.Add("燃煤");

legendList.Add("天然氣");

legendList.Add("汽油");

legendList.Add("柴油");

legendList.Add("熱力");

legendList.Add("其他");

Series dianSeries = new Series();

dianSeries.id = 0;

dianSeries.name = "電";

dianSeries.type = "line"; //線性圖呈現

dianSeries.data = new List();

dianSeries.itemStyle = new itemStyle { normal = new normal { color = "#0099FF" } };

Series shuiSeries = new Series();

shuiSeries.id = 1;

shuiSeries.name = "水";

shuiSeries.type = "line"; //線性圖呈現

shuiSeries.data = new List();

shuiSeries.itemStyle = new itemStyle { normal = new normal { color = "#00CC00" } };

Series yuanmeiSeries = new Series();

yuanmeiSeries.id = 2;

yuanmeiSeries.name = "燃煤";

yuanmeiSeries.type = "line"; //線性圖呈現

yuanmeiSeries.data = new List();

yuanmeiSeries.itemStyle = new itemStyle { normal = new normal { color = "#008080" } };

Series tianranqiSeries = new Series();

tianranqiSeries.id = 3;

tianranqiSeries.name = "天然氣";

tianranqiSeries.type = "line"; //線性圖呈現

tianranqiSeries.data = new List();

tianranqiSeries.itemStyle = new itemStyle { normal = new normal { color = "#CC6600" } };

Series qiyouSeries = new Series();

qiyouSeries.id = 4;

qiyouSeries.name = "汽油";

qiyouSeries.type = "line"; //線性圖呈現

qiyouSeries.data = new List();

qiyouSeries.itemStyle = new itemStyle { normal = new normal { color = "#CC00CC" } };

Series chaiyouSeries = new Series();

chaiyouSeries.id = 5;

chaiyouSeries.name = "柴油";

chaiyouSeries.type = "line"; //線性圖呈現

chaiyouSeries.data = new List();

chaiyouSeries.itemStyle = new itemStyle { normal = new normal { color = "#0033CC" } };

Series reliSeries = new Series();

reliSeries.id = 6;

reliSeries.name = "熱力";

reliSeries.type = "line"; //線性圖呈現

reliSeries.data = new List();

reliSeries.itemStyle = new itemStyle { normal = new normal { color = "#003300" } };

Series qitaSeries = new Series();

qitaSeries.id = 7;

qitaSeries.name = "其他";

qitaSeries.type = "line"; //線性圖呈現

qitaSeries.data = new List();

qitaSeries.itemStyle = new itemStyle { normal = new normal { color = "#FF0000" } };

foreach (DataRow dr in dt.Rows)

{

categoryList.Add(string.Format("{0}年{1}月", dr["ReportYear"], dr["ReportMonth"]));

dianSeries.data.Add(string.IsNullOrEmpty(dr["DIAN"].ToString()) ? 0 : dr["DIAN"]);

shuiSeries.data.Add(string.IsNullOrEmpty(dr["SHUI"].ToString()) ? 0 : dr["SHUI"]);

yuanmeiSeries.data.Add(string.IsNullOrEmpty(dr["YUANMEI"].ToString()) ? 0 : dr["YUANMEI"]);

tianranqiSeries.data.Add(string.IsNullOrEmpty(dr["TIANRQ"].ToString()) ? 0 : dr["TIANRQ"]);

qiyouSeries.data.Add(string.IsNullOrEmpty(dr["QIYOU"].ToString()) ? 0 : dr["QIYOU"]);

chaiyouSeries.data.Add(string.IsNullOrEmpty(dr["CHAIYOU"].ToString()) ? 0 : dr["CHAIYOU"]);

reliSeries.data.Add(string.IsNullOrEmpty(dr["RELI"].ToString()) ? 0 : dr["RELI"]);

qitaSeries.data.Add(string.IsNullOrEmpty(dr["QTNY"].ToString()) ? 0 : dr["QTNY"]);

}

//將sereis對象壓入sereis數組列表內

seriesList.Add(dianSeries);

seriesList.Add(shuiSeries);

seriesList.Add(yuanmeiSeries);

seriesList.Add(tianranqiSeries);

seriesList.Add(qiyouSeries);

seriesList.Add(chaiyouSeries);

seriesList.Add(reliSeries);

seriesList.Add(qitaSeries);

//因為我們需要返回category和series、legend多個對象 這裡我們自己在new一個新的對象來封裝這兩個對象

var newObj = new

{

category = categoryList,

series = seriesList,

legend = legendList

};

//Response返回新對象的json數據

Response.Write(JsonConvert.SerializeObject(newObj, Formatting.None));

Response.End();

}

}

///

/// 定義一個Series類 設置其每一組sereis的一些基本屬性

///

class Series

{

///

/// sereis序列組id

///

public int id

{

get;

set;

}

///

/// series序列組名稱

///

public string name

{

get;

set;

}

///

/// series序列組呈現圖表類型(line、column、bar等)

///

public string type

{

get;

set;

}

///

/// series序列組的itemstyle

///

public object itemStyle

{

get;

set;

}

///

/// series序列組的數據為數據類型數組

///

public List data

{

get;

set;

}

}

class normal

{

///

/// color

///

public string color

{

get;

set;

}

}

class itemStyle

{

///

/// normal

///

public object normal

{

get;

set;

}

系統在 VS目錄 下的 結構 如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

右鍵“ EchartDemo.aspx ”，選擇“ 在瀏覽器中查看 ”，如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

默認顯示“ 能耗動態 ”，運行效果如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

點擊 Tab 切換到“ 填報動態 ”，運行效果如下圖所示：

使用ECharts綁定SQL 2008數據庫數據顯示圖表

相關問題答案

Have any Question?

Let us answer it!