C#如何使用NPOI將excel導入到list

小編給大家分享一下C#如何使用NPOI將excel導入到list，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

具體內容如下

這個是確定是實體類接收

/// <summary>
/// 將excel導入到list
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="fs">Stream 文件流</param>
/// <param name="list">轉換的Dictionary：例如 ("昵稱","nickname")</param>
/// <returns></returns>
public static List<T> ExcelToList<T>(this Stream fs, Dictionary<string, string> list) where T : class, new()
        {
            List<T> ts = new List<T>();
            IWorkbook workbook = null;
            ISheet sheet = null;
            T t = new T();
            List<string> listName = new List<string>();
            try
            {   // 獲得此模型的公共屬性
                var propertys = t.GetType().GetProperties().ToList();
                workbook = new HSSFWorkbook(fs);

                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);//讀取第一個sheet，當然也可以循環讀取每個sheet

                    if (sheet != null)
                    {
                        int rowCount = sheet.LastRowNum;//總行數
                        if (rowCount > 0)
                        {
                            IRow firstRow = sheet.GetRow(0);//第一行
                            int cellCount = firstRow.LastCellNum;//列數
                            //循環列數
                            for (int i = 0; i < cellCount; i++)
                            {
                                //循環需要轉換的值
                                foreach (var item in list)
                                {
                                    if (item.Key.Equals(firstRow.GetCell(i).StringCellValue))
                                    {
                                        //替換表頭
                                        firstRow.GetCell(i).SetCellValue(item.Value);
                                    }
                                }
                                //獲取已經替換的表頭
                                var s = firstRow.GetCell(i).StringCellValue;
                                //添加到listname
                                listName.Add(s);
                            }

                            for (int i = 1; i <= rowCount; i++)
                            {
                                t = new T();
                                IRow currRow = sheet.GetRow(i);//第i行

                                for (int k = 0; k < cellCount; k++)
                                {   //取值
                                    object value = null;
                                    if (currRow.GetCell(k) != null)
                                    {
                                        firstRow.GetCell(0).SetCellType(CellType.String);
                                        currRow.GetCell(k).SetCellType(CellType.String);
                                        value = currRow.GetCell(k).StringCellValue;
                                    }
                                    else
                                    {
                                        continue;
                                    }

                                    var Name = string.Empty;
                                    //獲取第表頭的值
                                    Name = listName[k];
                                    //循環屬性
                                    foreach (var pi in propertys)
                                    {
                                        if (pi.Name.Equals(Name))
                                        {
                                            //獲取屬性類型名稱
                                            var s = pi.PropertyType.Name;

                                            //如果非空，則賦給對象的屬性
                                            if (value != DBNull.Value)
                                            {
                                                //判斷屬性的類型(可以自行添加)
                                                switch (s)
                                                {
                                                    case "Guid":
                                                        pi.SetValue(t, new Guid(value.ToString()), null);
                                                        break;

                                                    case "Int32":
                                                        pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToInt32(value.ToString()), null);
                                                        break;

                                                    case "Decimal":
                                                        pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToDecimal(value.ToString()), null);
                                                        break;

                                                    case "DateTime":
                                                        pi.SetValue(t, Convert.ToDateTime(value.ToString()), null);
                                                        break;

                                                    case "Double":
                                                        pi.SetValue(t, value.ToString() == "" ? 0 : Convert.ToDouble(value.ToString()), null);
                                                        break;

                                                    case "String":
                                                        pi.SetValue(t, value, null);
                                                        break;

                                                    default:
                                                        break;
                                                }
                                            }
                                        }
                                    }
                                }
                                //對象添加到泛型集合中
                                ts.Add(t);
                            }
                        }
                    }
                }
                return ts;
            }
            catch (Exception ex)
            {
                if (fs != null)
                {
                    fs.Close();
                }
                return null;
            }
        }

調用

 var list = new Dictionary<string, string>();
            list.Add("ID", "TradeAccountId");
            list.Add("簡介", "Intro");
            list.Add("昵稱1", "Nickname");
            list.Add("限制人數", "SubscribeLimit");
            list.Add("模式", "SubscribeMode");
            list.Add("收益率", "ProfitRate");
            list.Add("收益", "ProfitLossAmount");
            list.Add("頭像", "Img");
            list.Add("平臺名稱", "Name");
            list.Add("用戶昵稱", "UserNickname");

            FileStream fs = new FileStream(@"C:\Users\Administrator\Desktop\Test\Test\bin\Debug\Export\2021-04-27-14-46-36.xls", FileMode.Open);
            var list3 = fs.ExcelToList<Res_Signal>(list);

實體類

[Serializable]
public class Res_Signal
    {
        /// <summary>
        /// 交易賬號ID
        /// </summary>
        public Guid TradeAccountId { get; set; }

        /// <summary>
        /// 交易賬號簡介
        /// </summary>
        public String Intro { get; set; }

        /// <summary>
        /// 交易賬號昵稱
        /// </summary>
        public String Nickname { get; set; }

        /// <summary>
        /// 訂閱限制值
        /// </summary>
        public Int32 SubscribeLimit { get; set; }

        /// <summary>
        /// 訂閱模式 目前都是免費  0免費  1收費
        /// </summary>
        public Int32 SubscribeMode { get; set; }

        /// <summary>
        /// 訂閱模式名稱
        /// </summary>
        public String SubscribeMode_Des { get; set; }

        /// <summary>
        /// 平臺名稱
        /// </summary>
        public String Name { get; set; }

        /// <summary>
        /// 頭像
        /// </summary>
        public String HeadImg { get; set; }

        /// <summary>
        /// 用戶昵稱
        /// </summary>
        public String UserNickname { get; set; }

        /// <summary>
        /// 訂閱人數
        /// </summary>
        public Int32 SubscribeCount { get; set; }

        /// <summary>
        /// 平臺圖片
        /// </summary>
        public String Img { get; set; }

        /// <summary>
        /// 收益率
        /// </summary>
        public decimal ProfitRate { get; set; }

        /// <summary>
        /// 總收益
        /// </summary>
        public decimal ProfitLossAmount { get; set; }

}

看完了這篇文章，相信你對“C#如何使用NPOI將excel導入到list”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！