小編給大家分享一下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”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
