效果：
描述：此功能是抓取外國的一個檢測PM2.5的網站。實時讀取網站的數據，然后保存到數據庫里面。每隔一小時刷新一次。
地址為：http://beijing.usembassy-china.org.cn/070109air.html
篩選后的地址為：http://utils.usembassy.gov/feed2js/feed2js.php?src=http%3A%2F%2Fwww.stateair.net%2Fweb%2Frss%2F1%2F1.xml&desc=1&num=7&targ=y&utf=y&pc=y&Words=40&
思路：先抓取到頁面的所有數據，保存到txt里面，再一行一行的讀取txt，然后用split，substring截取到自己想要的數據,最后保存到數據庫，在進行插入數據庫的時候查看一下是否已經存在，如果不存在則插入。
代碼：
using System;using System.Collections.Generic;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.IO;//using System.Linq;using System.Net;using System.Text;using System.Text.RegularExPRessions;//using System.Threading.Tasks;/******************************** * 創建人：青蘋果 * 創建時間：2015-12-28 * 描述：獲取美利堅合眾國的 PM2.5 * ******************************/namespace GetUSAData{ class Program { //public static string GetURL = System.Configuration.ConfigurationSettings.AppSettings["GetURL"];//獲取數據的地址 public static string GetURL = "http://utils.usembassy.gov/feed2js/feed2js.php?src=http%3A%2F%2Fwww.stateair.net%2Fweb%2Frss%2F1%2F1.xml&desc=1&num=7&targ=y&utf=y&pc=y&words=40&"; public static string txtURL = System.Configuration.ConfigurationSettings.AppSettings["txtURL"];//保存為txt文件的路徑 public static string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); static void Main(string[] args) { LoadGO(); } public static void LoadGO() { GetUSA(); List<string[]> getlist = Read(txtURL); //刪除txt if (File.Exists(txtURL)) { //如果存在則刪除 File.Delete(txtURL); } if (getlist.Count > 0) { for (int i = getlist.Count-1; i >-1; i--) { DateTime dtime = DateTime.Parse(getlist[i][0].ToString()); string getTime = dtime.ToString("yyyy-MM-dd HH:mm"); string controlTime = dtime.ToString("yyyy-MM-dd"); float LatestHourdata1 = float.Parse(getlist[i][2]); int LatestHourdata2 = Convert.ToInt32(getlist[i][3]); float Avgdata1 = 0; int Avgdata2 = 0; string Avgdata3 = getlist[i][4].ToString(); List<SqlParameter> listWhere = new List<SqlParameter>(); listWhere.Add(new SqlParameter("@strDatetime", controlTime)); string sqlSelect = @"SELECT count(*) as allcount,sum(LatestHourdata1) as LatestHourdata1_avg, sum(LatestHourdata2) as LatestHourdata2_avg FROM T_twitter where ([LatestHourdata1] is not null or [LatestHourdata2] is not null or [Avgdata1] is not null or [AvgData2] is not null) and CONVERT(varchar(100), [datetime], 23)=@strDatetime"; DataTable sumDT = ControlDB(sqlSelect, listWhere, "select"); //查詢總和用于計算日均值 if (sumDT.Rows.Count > 0) { foreach (DataRow itemDR in sumDT.Rows) { int allcount = Convert.ToInt32(itemDR["allcount"].ToString()); //數據庫中當前日期數量總和 if (allcount > 0) { if (itemDR["LatestHourdata1_avg"] != null) { Avgdata1 = float.Parse(itemDR["LatestHourdata1_avg"].ToString()); //數據庫中LatestHourdata1_avg總和 Avgdata1 = (Avgdata1 + LatestHourdata1) / (allcount + 1);//(數據庫的總和+最新的一條)/(數據庫的總和數量+1)=日平均值 } if (itemDR["LatestHourdata2_avg"] != null) { Avgdata2 = Convert.ToInt32(itemDR["LatestHourdata2_avg"].ToString()); //數據庫中LatestHourdata2_avg總和 Avgdata2 = (Avgdata2 + LatestHourdata2) / (allcount + 1);//(數據庫的總和+最新的一條)/(數據庫的總和數量+1)=日平均值 } //根據網站規則判斷PM2.5的平均嚴重性 if (Avgdata2 >= 0 && Avgdata2 <= 50) { Avgdata3 = " Good (at 24-hour exposure at this level)"; } else if (Avgdata2 >= 51 && Avgdata2 <= 100) { Avgdata3 = " Moderate (at 24-hour exposure at this level)"; } else if (Avgdata2 >= 101 && Avgdata2 <= 150) { Avgdata3 = " Unhealthy for Sensitive Groups (at 24-hour exposure at this level)"; } else if (Avgdata2 >= 151 && Avgdata2 <= 200) { Avgdata3 = " Unhealthy (at 24-hour exposure at this level)"; } else if (Avgdata2 >= 201 && Avgdata2 <= 300) { Avgdata3 = " Very Unhealthy (at 24-hour exposure at this level)"; } else { Avgdata3 = " Hazardous (at 24-hour exposure at this level)"; } } else { Avgdata1 = LatestHourdata1; Avgdata2 = LatestHourdata2; } } } List<SqlParameter> pars = new List<SqlParameter>(); pars.Add(new SqlParameter("@whereDatetime", getTime)); pars.Add(new SqlParameter("@datetime", getTime)); pars.Add(new SqlParameter("@LatestHourdata1", LatestHourdata1)); pars.Add(new SqlParameter("@LatestHourdata2", LatestHourdata2)); pars.Add(new SqlParameter("@LatestHourdata3", getlist[i][4].ToString())); pars.Add(new SqlParameter("@Avgdata1", Avgdata1)); pars.Add(new SqlParameter("@Avgdata2", Avgdata2)); pars.Add(new SqlParameter("@Avgdata3", Avgdata3)); string sql = @"if not exists(select * from dbo.T_twitter where datetime=@whereDatetime) begininsert T_twitter (datetime,LatestHourdata1,LatestHourdata2,LatestHourdata3,Avgdata1,AvgData2,AvgData3)VALUES(@datetime,@LatestHourdata1,@LatestHourdata2,@LatestHourdata3,@Avgdata1,@Avgdata2,@Avgdata3) end"; ControlDB(sql, pars, "");//插入數據 } } } /// <summary> /// 獲取頁面數據保存至txt /// </summary> public static void GetUSA() { WebRequest request = WebRequest.Create(GetURL); WebResponse response = request.GetResponse(); StreamReader reader = new StreamReader(response.GetResponseStream(), Encoding.GetEncoding("gb2312")); //reader.ReadToEnd() 表示取得網頁的源碼 FileStream fs = new FileStream(txtURL, FileMode.Create); byte[] data = System.Text.Encoding.Default.GetBytes(reader.ReadToEnd()); //開始寫入 fs.Write(data, 0, data.Length); //清空緩沖區、關閉流 fs.Flush(); fs.Close(); } /// <summary> /// 根據路徑讀取txt文件 /// </summary> /// <param name="path">txt路徑</param> /// <returns></returns> public static List<string[]> Read(string path) { List<string[]> list = new List<string[]>(); StreamReader sr = new StreamReader(path, Encoding.Default); String line; while ((line = sr.ReadLine()) != null) { int i = line.ToString().IndexOf("title"); if (i > 0) { string titleStr = line.ToString().Substring(i + 7); //截取到title后面的值 string[] titlelist = titleStr.Split('"'); //以" 截取 string titledata = titlelist[0]; string[] datalist = titledata.Split('&'); //以& 截取 string data = datalist[0]; string[] datastrlist = data.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);//以; 截取 list.Add(datastrlist); } } sr.Close(); return list; } /// <summary> /// 增查表 /// </summary> /// <returns></returns> public static DataTable ControlDB(string sql, List<SqlParameter> par, string type) { Dataaccess controData = new DataAccess(); DataTable resultDT = new DataTable(); if (type == "select") { resultDT = controData.GetDataTable(sql, par.ToArray()); } else { int result = controData.ExecuteSql(sql, par.ToArray()); } return resultDT; } }}
Demo下載：
http://files.VEVb.com/files/xinchun/GetUSAData.zip
新聞熱點
疑難解答
