C# Excel转化为List(自定义Excel表头)

一、添加引用

using System.Data.OleDb;

二、创建CustomExecutingFilterAttribute

  public class CustomExecutingFilterAttribute: Attribute
  {
    public string Title { get; set; }

  }

三、创建一个Model

 public class test2
  {
    
    [CustomExecutingFilterAttribute(Title = "QD")]//Excel列头标题名称
    public string Name { get; set; }
    [CustomExecutingFilterAttribute(Title = "ID")]//Excel列头标题名称
    public string ID { get; set; }
  }

四、创建转化方法

 /// 

    /// 将Excel转化为List
    /// 
    /// 类型
    /// 文件路径
    /// sheet页
    /// 
    public static List ExcelLoadeToList(string FilePath, int SheetIndex) where T : class
    {
      #region 将excel加载为DataTable
      string strConn = $"Provider=Microsoft.Ace.OleDb.12.0;data source={FilePath};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
      OleDbConnection conn = new OleDbConnection(strConn);
      conn.Open();
      DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
      string TableName = schemaTable.Rows[SheetIndex][2].ToString().Trim();

      string strExcel = $"select * from [{TableName}]";
      OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
      DataSet ds = new DataSet();
      myCommand.Fill(ds, "table1");

      #endregion

      List tlist = Activator.CreateInstance>();
      var dType = typeof(T);

      for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
      {
        T t = Activator.CreateInstance();
        foreach (PropertyInfo dP in dType.GetProperties())
        {
          string ColumnName = dP.Name;
          //此字段是否使用了CustomExecutingFilterAttribute类
          if (dP.IsDefined(typeof(CustomExecutingFilterAttribute), false))
          {
            var attributes = dP.GetCustomAttributes();
            foreach (var attribute in attributes)
            {
              //这里的MaximumLength 最好用常量去做
              var AttriColumnName = (string)attribute.GetType().
                GetProperty("Title")?.
                GetValue(attribute);
              if (!string.IsNullOrWhiteSpace(AttriColumnName))
              {
                ColumnName = AttriColumnName;
              }
            }
            //Excel中是否包含此列名
            if (ds.Tables[0].Columns.Contains(ColumnName))
            {
              var val = ds.Tables[0].Rows[i][ColumnName]?.ToString();
              dP.SetValue(t, val);
            }
          }
        }
        tlist.Add(t);
      }

      return tlist;
    }

五、调用方法

        string filePath = @"C:\Users\Administrator\Desktop\test.xlsx";
        var srclist = ExcelHelper.ExcelLoadeToList(filePath,0);

六、数据格式