ｃ＃幾個常用的匯入方式和元件

  　　各位有發現EXCEL的格式有分為.CSV、.XLS、 .XLXS 等，但作法不儘相同，所以幫各位整理起來，希望各位網友可以善加利用。

一、CLOSEXML 套件　（目前不支援.CSV、.XLS）

匯入ｅｘｃｅｌ檔案，並轉換是物件Ｌｉｓｔ

public List<T> ReadExcelToList<T>(string fileName, string sheetName)         {             List<T> dataList = new List<T>();             Type typeObject = typeof(T);             using (XLWorkbook work = new XLWorkbook(fileName))             {                 var workSheet = work.Worksheets.Where(q => q.Name == sheetName).First();                 var properties = typeObject.GetProperties();                 var colums = workSheet.FirstRow().Cells().Select((v, i) => new { Value = v.Value, Index = i + 1 });                 foreach (IXLRow row in workSheet.RowsUsed().Skip(1))                 {                     T obj = (T)Activator.CreateInstance(typeObject);                      foreach (var prop in properties)                     {                         int colIndex = colums.SingleOrDefault(c => c.Value.ToString() == prop.Name.ToString()).Index;                         var val = row.Cell(colIndex).Value;                         var type = prop.PropertyType;                         prop.SetValue(obj, Convert.ChangeType(val, type));                     }                      dataList.Add(obj);                 }             }              return dataList;         }

二、ＮＰＯＩ　（支援.ＸＬＳ、．ＸＬＳＸ）

public List<T> ReadExcelToListForXLS<T>(string fileName)         {             try             {                 List<T> dataList = new List<T>();                 Type typeObject = typeof(T);                  IWorkbook workbook = null;  //新建IWorkbook物件                 FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read);                 workbook = new HSSFWorkbook(fileStream);  //xlsx資料讀入workbook                 ISheet sheet = workbook.GetSheetAt(0);  //獲取第一個工作表                  // 建立一個空的 List<string> 用來儲存轉換後的資料                 var properties = typeObject.GetProperties();                 var colums = sheet.GetRow(0).Cells.Select((v, i) => new { Value = v.StringCellValue, Index = i });                 IRow row; //新建當前工作表行資料                 for (int i = 1; i <= sheet.LastRowNum + 1; i++)  //對工作表每一行                 {                     row = sheet.GetRow(i);   //row讀入第i行資料                     if (row != null)                     {                         T obj = (T)Activator.CreateInstance(typeObject);                          foreach (var prop in properties)                         {                             int colIndex = colums.SingleOrDefault(c => c.Value.ToString() == prop.Name.ToString()).Index;                             var val = row.GetCell(colIndex);                             var type = prop.PropertyType;                             prop.SetValue(obj, Convert.ChangeType(val?.ToString(), type));                         }                         dataList.Add(obj);                     }                 }                  return dataList;             }             catch (Exception ex)             {                 throw ex;             }         }

三、ＣＳＶ的匯入

public List<T> ReadExcelToListForCSV<T>(string fileName)         {             try             {                 List<T> dataList = new List<T>();                 Type typeObject = typeof(T);                 var contents = File.ReadAllText(fileName, Encoding.GetEncoding("Big5")).Split('\n');                 var csv = from line in contents                           select line.Split(',').ToArray();                   var properties = typeObject.GetProperties();                 var colums = csv.ToList().First().Select((v, i) => new { Value = v.ToString().Replace("\r", string.Empty), Index = i });                  foreach (var row in csv.Skip(1).ToList())                 {                     T obj = (T)Activator.CreateInstance(typeObject);                      foreach (var prop in properties)                     {                         int colIndex = colums.SingleOrDefault(c => c.Value.ToString() == prop.Name.ToString()).Index;                         var val = row[colIndex];                         var type = prop.PropertyType;                         prop.SetValue(obj, Convert.ChangeType(val?.ToString(), type));                     }                     dataList.Add(obj);                 }                  return dataList;             }             catch (Exception ex)             {                 throw ex;             }         }