C#根据指定url下载文件并读取.csv数据转为json格式

绝地灬酷狼 2022-06-01 01:24 287阅读 0赞

从一个地址中下载Excel表格，然后读取这个Excel表格数据

注：表格是.csv格式的，我试了一下其他格式的这个程序不支持，晚点续更

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.IO;
    using System.Net;
    using System.Data;
    using System.Data.OleDb;
    using System.Text.RegularExpressions;
    
    namespace DownLoadExcel
    {
        public class DownLoadRead
        {  
            private static string directory = System.Configuration.ConfigurationManager.AppSettings["Download_Dic"];//存放地址
            private static string url = System.Configuration.ConfigurationManager.AppSettings["url"];//下载地址
            
    
            /// <summary>  
            /// 从指定url下载csv数据，然后给文件根据下载时间命名，返回文件名  
            /// </summary>  
            /// <param name="url">下载地址</param>  
            /// <returns>文件名称</returns>  
            public string DownloadFile()
            {
                try
                {
                    string fileName = CreateFileName(url);
                    if (!Directory.Exists(directory))//判断指定路径下是否存在指定的文件夹或文件
                    {
                        Directory.CreateDirectory(directory);//创建路径下的文件夹
                    }
                    WebClient client = new WebClient();
                    client.DownloadFile(url, directory + fileName);
                    string datajson=CsvRead(0, directory + fileName);//读取excel表中数据
                    //另一种方法，将文件地址封装
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    dic.Add("DownExcellocation", directory + fileName);
                    //csvRead(0, dic["DownExcellocation"]);
    
                    return fileName;
                }
                catch
                {
                    return "";
                }
            }
    
            /// <summary>  
            /// 创建文件名称  
            /// </summary>  
            public static string CreateFileName(string url)
            {
                string fileName = "";
                string fileExt = url.Substring(url.LastIndexOf(".")).Trim().ToLower();
                //Random rnd = new Random();
                //fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + rnd.Next(10, 99).ToString() + fileExt;//获取随机数
                fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff") + System.Guid.NewGuid().ToString().Replace("-", "") + fileExt;        
               return fileName;
                
            }
    
    
            
            /// <summary>  
            /// 将Csv读入DataTable  及返回json数据
            /// </summary>  
            /// <param name="filePath">csv文件路径</param>  
            /// <param name="n">表示第n行是字段title,第n+1行是记录开始</param>  
            /// <param name="k">可选参数表示最后K行不算记录默认0</param>  
            public string CsvRead(int n, string filePath) 
            {
                DataTable dt = new DataTable();
                String csvSplitBy = "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)";
                StreamReader reader = new StreamReader(filePath, System.Text.Encoding.Default, false);
                int i = 0, m = 0;
                reader.Peek();
                while (reader.Peek() > 0)
                {
                    m = m + 1;
                    string str = reader.ReadLine();
                    if (m >= n + 1)
                    {
                        if (m == n + 1) //如果是字段行，则自动加入字段。  
                        {
                            MatchCollection mcs = Regex.Matches(str, csvSplitBy);
                            foreach (Match mc in mcs)
                            {
                                dt.Columns.Add(mc.Value); //增加列标题  
                            }
    
                        }
                        else
                        {
                            MatchCollection mcs = Regex.Matches(str, "(?<=^|,)(\"(?:[^\"]|\"\")*\"|[^,]*)");
                            i = 0;
                            System.Data.DataRow dr = dt.NewRow();
                            foreach (Match mc in mcs)
                            {
                                dr[i] = mc.Value;
                                i++;
                            }
                            dt.Rows.Add(dr);  //DataTable 增加一行       
                        }
    
                    }
                }
                string excelJson = DataTableToJson(dt);
                Console.WriteLine(excelJson);
                return excelJson;
            }
    
            #region  帮助类DataSet转换为Json
            /// <summary>    
            /// DataSet转换为Json   
            /// </summary>    
            /// <param name="dataSet">DataSet对象</param>   
            /// <returns>Json字符串</returns>    
            public static string DataSetToJson(DataSet dataSet)
            {
                string jsonString = "{";
                foreach (DataTable table in dataSet.Tables)
                {
                    jsonString += "\"" + table.TableName + "\":" + DataTableToJson(table) + ",";
                }
                jsonString = jsonString.TrimEnd(',');
                return jsonString + "}";
            }
            #endregion
            #region 帮助类Datatable转换为Json
            /// <summary>     
            /// Datatable转换为Json     
            /// </summary>    
            /// <param name="table">Datatable对象</param>     
            /// <returns>Json字符串</returns>     
            public static string DataTableToJson(DataTable dt)
            {
                StringBuilder jsonString = new StringBuilder();
                jsonString.Append("[");
                DataRowCollection drc = dt.Rows;
                for (int i = 0; i < drc.Count; i++)
                {
                    jsonString.Append("{");
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        string strKey = dt.Columns[j].ColumnName;
                        string strValue = drc[i][j].ToString();
                        Type type = dt.Columns[j].DataType;
                        jsonString.Append("\"" + strKey + "\":");
                        strValue = StringFormat(strValue, type);
                        if (j < dt.Columns.Count - 1)
                        {
                            jsonString.Append(strValue + ",");
                        }
                        else
                        {
                            jsonString.Append(strValue);
                        }
                    }
                    jsonString.Append("},");
                }
                jsonString.Remove(jsonString.Length - 1, 1);
                jsonString.Append("]");
                return jsonString.ToString();
            } 
            #endregion
    
            #region 帮助类格式化字符型、日期型、布尔型StringFormat
            /// <summary>
            /// 格式化字符型、日期型、布尔型
            /// </summary>
            /// <param name="str"></param>
            /// <param name="type"></param>
            /// <returns></returns>
            private static string StringFormat(string str, Type type)
            {
                if (type == typeof(string))
                {
                    str = String2Json(str);
                    str = "\"" + str + "\"";
                }
                else if (type == typeof(DateTime))
                {
                    str = "\"" + str + "\"";
                }
                else if (type == typeof(bool))
                {
                    str = str.ToLower();
                }
                else if (type != typeof(string) && string.IsNullOrEmpty(str))
                {
                    str = "\"" + str + "\"";
                }
                return str;
            } 
            #endregion
    
            #region 过滤特殊字符String2Json(String s)
            
            /// <summary>
            /// 过滤特殊字符
            /// </summary>
            /// <param name="s">字符串</param>
            /// <returns>json字符串</returns>
            private static string String2Json(String s)
            {
                StringBuilder sb = new StringBuilder();
                for (int i = 0; i < s.Length; i++)
                {
                    char c = s.ToCharArray()[i];
                    switch (c)
                    {
                        case '\"':
                            sb.Append("\\\""); break;
                        case '\\':
                            sb.Append("\\\\"); break;
                        case '/':
                            sb.Append("\\/"); break;
                        case '\b':
                            sb.Append("\\b"); break;
                        case '\f':
                            sb.Append("\\f"); break;
                        case '\n':
                            sb.Append("\\n"); break;
                        case '\r':
                            sb.Append("\\r"); break;
                        case '\t':
                            sb.Append("\\t"); break;
                        default:
                            sb.Append(c); break;
                    }
                }
                return sb.ToString();
            }
            #endregion
        }
    }

以上是主要方法，下面提供main方法

using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    namespace DownLoadTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                DownLoadExcel.DownLoadRead dr = new DownLoadExcel.DownLoadRead();
                string filename = dr.DownloadFile();
                if (!string.IsNullOrEmpty(filename))
                {
                    Console.WriteLine("文件下载成功，文件名称：" + filename);
                }
                else
                {
                    Console.WriteLine("文件下载失败");
                }
                Console.ReadLine();
    
            }
    
                
    
            }
        }运行结果：