jiance-analysis/DPTools/components/CsvToListData.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Uninpho.DBOperation.Model;

namespace Uninpho.Tools.components
{
    /// <summary>
    /// CSV文件导入数据库工具类
    /// </summary>
    static class CsvToListData
    {
        public static List<T_xxxw_csvdata> Listdata_csvdata { get; set; }
        public static List<T_xxxw_csvlist> Listdata_csvlist { get; set; }
        /// <summary>
        /// csv文件转datatable
        /// </summary>
        public static void CsvToDataTable(TableName tablename)
        {
            OpenFileDialog fd = new OpenFileDialog();//首先根据打开文件对话框，选择excel表格
            fd.Filter = "表格|*.csv";//打开文件对话框筛选器
            string strPath;//文件完整的路径名
            if (fd.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    strPath = fd.FileName;
                    DataTable datatable = new DataTable();
                    FileStream fileStream = new FileStream(strPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                    StreamReader streamReader = new StreamReader(strPath, Encoding.GetEncoding("gb2312"));
                    string strLine = ""; //记录每次读取的一行记录
                    string[] arrayLine = null; //每行字段的内容
                    string[] tableHead = null; //表头内容
                    int columnCount = 0;//记录列数
                    bool IsFrist = true;  //判断表头
                    while ((strLine = streamReader.ReadLine()) != null)
                    {
                        if (IsFrist == true)
                        {
                            tableHead = strLine.Split(',');
                            IsFrist = false;
                            columnCount = tableHead.Length;
                            //创建列
                            for (int i = 0; i < columnCount; i++)
                            {
                                DataColumn dataColumn = new DataColumn(tableHead[i]);
                                datatable.Columns.Add(dataColumn);
                            }
                        }
                        else
                        {
                            arrayLine = strLine.Split(',');
                            DataRow dataRow = datatable.NewRow();
                            for (int j = 0; j < columnCount; j++)
                            {
                                dataRow[j] = arrayLine[j];
                            }
                            datatable.Rows.Add(dataRow);
                        }
                    }
                    if (arrayLine != null && arrayLine.Length > 0)
                    {
                        datatable.DefaultView.Sort = tableHead[0];
                    }
                    streamReader.Close();
                    streamReader.Dispose();
                    fileStream.Close();
                    fileStream.Dispose();
                    //此处是导入到数据表中，导入dev中需要修改，读取到的数据是datatable;
                    //转成list导入数据库参考三张表导入功能
                    switch (tablename)
                    {
                        case TableName.T_xxxw_csvdata:
                            Listdata_csvdata = DataTableToList<T_xxxw_csvdata>(datatable, TableName.T_xxxw_csvdata);
                            //生成唯一编码id
                            foreach (var item in Listdata_csvdata)
                            {
                                string uuid = System.Guid.NewGuid().ToString("N");
                                item.Id = uuid;
                            }
                            break;
                        case TableName.T_xxxw_csvlist:
                            Listdata_csvlist = DataTableToList<T_xxxw_csvlist>(datatable, TableName.T_xxxw_csvlist);
                            //生成唯一编码id
                            foreach (var item in Listdata_csvlist)
                            {
                                string uuid = System.Guid.NewGuid().ToString("N");
                                item.Id = uuid;
                            }
                            break;
                        default: break;
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);//捕捉异常
                }
            }
        }


        /// <summary>
        /// datatable转成实体类
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="dt">datatable数据</param>
        /// <returns></returns>
        private static List<T> DataTableToList<T>(DataTable dt, TableName tablename) where T : class, new()
        {
            //需要建立辅助类
            Assist_xxxwcsvdata assist_xxxwcsvdata = new Assist_xxxwcsvdata();
            Assist_xxxwcsvlist assist_xxxwcsvlist = new Assist_xxxwcsvlist();
            if (dt == null || dt.Rows.Count == 0)
                return null;
            List<T> result = new List<T>();
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    T res = new T();
                    for (int i = 0; i < dr.Table.Columns.Count; i++)
                    {
                        PropertyInfo propertyInfo = null;
                        switch (tablename)
                        {
                            case TableName.T_xxxw_csvdata:
                                propertyInfo = res.GetType().GetProperty(GetTypeValue(dr.Table.Columns[i].ColumnName, assist_xxxwcsvdata), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                                break;
                            case TableName.T_xxxw_csvlist:
                                propertyInfo = res.GetType().GetProperty(GetTypeValue(dr.Table.Columns[i].ColumnName, assist_xxxwcsvlist), BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance);
                                break;
                            default: break;
                        }

                        if (propertyInfo != null)
                        {
                            var value = dr[i];
                            switch (propertyInfo.PropertyType.FullName)
                            {
                                case "System.Decimal":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0.0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToDecimal(value), null); break;
                                case "System.String":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, "-", null); break; }
                                    propertyInfo.SetValue(res, value.ToString(), null); break;
                                case "System.Int32":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToInt32(value), null); break;
                                case "System.Double":
                                    if (value == DBNull.Value) { propertyInfo.SetValue(res, 0.0, null); break; }
                                    propertyInfo.SetValue(res, Convert.ToDouble(value), null); break;
                                default:
                                    propertyInfo.SetValue(res, value, null); break;
                            }
                        }
                    }
                    result.Add(res);
                }
                catch (Exception ex)
                {
                    ex = new Exception(string.Format("请检查第{0}行数据", result.Count + 2));
                    throw ex;
                }

            }
            return result;
        }

        /// <summary>
        /// 辅助类转化器
        /// </summary>
        /// <typeparam name="T">类</typeparam>
        /// <param name="s">属性值</param>
        /// <param name="t">实例化类</param>
        /// <returns></returns>
        private static string GetTypeValue<T>(string s, T t)
        {
            string columnName = "";
            Type assistType = t.GetType();
            PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Static);
            for (int i = 0; i < assistType.GetFields().Length; i++)
            {
                if (assistType.GetFields()[i] != null)
                {
                    if (assistType.GetFields()[i].GetValue(t) != null && assistType.GetFields()[i].GetValue(t).ToString() == s)
                    {
                        columnName = assistType.GetFields()[i].Name;
                        //Debug.LogError(assistType.GetFields()[i].Name);
                        break;
                    }
                }
            }
            return columnName;
        }
    }
}