範例連接檔,需自行修改「紅色字」部分:
CSV檔:
https://societydata.taichung.gov.tw/System/DataSetDownload.aspx?idf=387120000J-000027&folder=RFXQ40397EQFQ49011&datadate=2015-07-01&format=CSV
JSON檔:
https://societydata.taichung.gov.tw/System/DataSetDownload.aspx?idf=387120000J-000027&folder=RFXQ40397EQFQ49011&datadate=2015-01-01&format=JSON
XML檔:
https://societydata.taichung.gov.tw/System/DataSetDownload.aspx?idf=387120000J-000027&folder=RFXQ40397EQFQ49011&datadate=2015-07-01&format=XML
這個範例只能用在「JSON、XML」為一層的狀態下。因為,JSON、XML可以很多層進行資料的編寫。
using System;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.Xml;
using System.Net;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
string s = "";
DataTable dt = new DataTable();
using (WebClient wc = new WebClient())
{
try
{
wc.Encoding = Encoding.UTF8;
string url = "https://societydata.taichung.gov.tw/System/DataSetDownload.aspx?idf=387120000J-000027&folder=RFXQ40397EQFQ49011&datadate=2015-07-01&format=CSV";
byte[] b = wc.DownloadData(url);
s = Encoding.UTF8.GetString(b);
dt = CsvStringToDataTable(s);
//dt = JsonStringToDataTable(s);
//dt = XmlStringToDataTable(s);
}
catch (WebException ex)
{
context.Response.ContentType = "text/plain";
context.Response.Write(ex.Message.ToString());
}
}
context.Response.ContentType = "text/plain";
context.Response.Write("資料數量:" + dt.Rows.Count);
============ function
/// <summary>
/// Json To DataTable
/// </summary>
/// <param name="json"></param>
/// <returns></returns>
public DataTable JsonStringToDataTable(string s)
{
var jsonLinq = JObject.Parse(s);
var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();
var trgArray = new JArray();
foreach (JObject row in srcArray.Children<JObject>())
{
var cleanRow = new JObject();
foreach (JProperty column in row.Properties())
{
if (column.Value is JValue)
{
cleanRow.Add(column.Name, column.Value);
}
}
trgArray.Add(cleanRow);
}
return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
}
/// <summary>
/// XML To DataTable
/// </summary>
/// <param name="Xmlstring"></param>
/// <returns></returns>
public DataTable XmlStringToDataTable(string s)
{
XmlDocument Xmldoc = new XmlDocument();
Xmldoc.LoadXml(s);
XmlReader Xmlreader = XmlReader.Create(new System.IO.StringReader(Xmldoc.OuterXml));
DataSet ds = new DataSet();
ds.ReadXml(Xmlreader);
DataTable dt = ds.Tables[0];
return dt;
}
/// <summary>
/// CSV To DataTable
/// </summary>
/// <param name="s"></param>
/// <returns></returns>
public static DataTable CsvStringToDataTable(string s)
{
DataTable dt = new DataTable();
string[] tableData = s.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
var col = from cl in tableData[0].Split(",".ToCharArray())
select new DataColumn(cl);
dt.Columns.AddRange(col.ToArray());
(from st in tableData.Skip(1)
select dt.Rows.Add(st.Split(",".ToCharArray()))).ToList();
return dt;
}
沒有留言:
張貼留言