2019年9月2日

C# JSON、XML、CSV 轉換成 DataTable 使用 WebClient 取得資料。

範例連接檔,需自行修改「紅色字」部分:

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;
    }

沒有留言:

張貼留言