C# 技巧：从 Excel 文件中读取数据

概述：我们希望有一种更隐含的方式来读取数据，并通过在对象属性上使用属性将其添加到对象中。使用列字母的地图让我们看一下下面的 Excel 文件，因为我们可以看到它包含一个用户列表。我们可以用以下类来表示用户数据：public class ReadByColumnName{ [PropertyColumnAttribute(A)] public int Id { get; set; } [PropertyColumnAttribute(B)] public string FirstName { get; set; } [PropertyColumnAttrib

我们希望有一种更隐含的方式来读取数据，并通过在对象属性上使用属性将其添加到对象中。

使用列字母的地图

让我们看一下下面的 Excel 文件，因为我们可以看到它包含一个用户列表。
我们可以用以下类来表示用户数据：

public class ReadByColumnName
{
    [PropertyColumnAttribute("A")]
    public int Id { get; set; }
   
    [PropertyColumnAttribute("B")]
    public string FirstName { get; set; }
    
    [PropertyColumnAttribute("C")]
    public string LastName { get; set; }
   
    [PropertyColumnAttribute("D")]
    public string Email { get; set; }

    [PropertyColumnAttribute("E")]
    public double? Rank { get; set; }
}

如您所见，我们使用属性将属性映射到列字母。
属性本身非常简单，我们只是用它来保存列字母。

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]  
   public class PropertyColumnAttribute : Attribute  
   {  
       public string ColumnName;  
   
       public PropertyColumnAttribute(string columnName)  
       {  
           ColumnName = columnName;  
       }  
   }

让我们看看如何使用它来读取 Excel 数据：

public static class ExcelReader
{
  public static List<T> ReadExcel<T>(string filePath, 
  string worksheetName = null,
  int initialDataRow = 2) where T : class, new()
  {
      ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
  
      using var package = new ExcelPackage(filePath);
      //read the first worksheet or a specific one
      var sheet = worksheetName == null ?
          package.Workbook.Worksheets.FirstOrDefault() :
          package.Workbook.Worksheets.FirstOrDefault(s => s.Name == worksheetName);
  
      var list = new List<T>();
  
      var properties = GetProperties<T>();
  
      for (var i = initialDataRow; i < sheet.Dimension.End.Row + 1; i++)
      {
          var item = new T();
  
          foreach (var property in properties)
          {
              var value = sheet.Cells[$"{property.Column}{i}"].Value;
  
              property.PropertyInfo.SetValue(item,
                  value != null ?
                      Convert.ChangeType(value, property.Type) :
                      property.IsNullable ? null : default);
          }
  
          list.Add(item);
      }
  
      return list;
  }

  private static List<PropertyInfoModel> GetProperties<T>()
  {
      var list = new List<PropertyInfoModel>();
      foreach (var property in typeof(T).GetProperties())
      {
          var propertyColumnAttribute = property.GetCustomAttribute<PropertyColumnAttribute>();
          if (propertyColumnAttribute != null )
          {
              var isNullable = property.PropertyType.IsGenericType &&
                               property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);
  
              list.Add(new PropertyInfoModel
              {
                  PropertyInfo = property,
                  Name = property.Name,
                  Column = propertyColumnAttribute?.ColumnName ,
                  IsNullable = isNullable,
                  Type = isNullable ? property.PropertyType.GetGenericArguments()[0] : property.PropertyType
              });
          }
      }
  
      return list;
  }
}

 public class PropertyInfoModel
{
    public PropertyInfo PropertyInfo { get; set; }
    public string Name { get; set; }
    public string Column { get; set; }
    public Type Type { get; set; }
    public bool IsNullable { get; set; }
}

ReadExcel<T> 方法获取 3 个参数：

filePath：Excel 文件的路径
worksheetName：我们要读取的工作表的名称，如果为 NULL，则读取第一个工作表
initialDataRow：包含数据的第一行，如果未传递，将使用 2。

首先，我们需要获取要阅读的工作表。如果 worksheetName 参数为 null，我们将读取文件中的第一个工作表，如果它不是 NULL，我们将查找具有该名称的工作表。

其次，我们需要读取我们想要读入的类 T 的属性。GetProperties<T>（） 使用反射来循环访问类属性并查找具有 PropertyColumnAttribute 属性的类属性。对于这些属性，我们得到：

PropertyInfo 对象。
属性名称。
来自 PropertyColumnAttribute 的列字母。
属性类型。
如果该属性为 NULLABLE

现在我们有了元数据，我们遍历所有行（从 initialDataRow 开始)

对于每一行，我们遍历所有类属性，并尝试在该行中获取该属性的单元格值

var value = sheet.Cells[$"{property.Column}{i}"].Value;

现在，我们使用 propertyInfo 对象将此值添加到我们为该行创建的项中

property.PropertyInfo.SetValue(item,  
value != null ?  
    Convert.ChangeType(value, property.Type) :  
    property.IsNullable ? null : default);

请注意，我们检查值是否为 NULL，如果是，我们将进行第二次检查以查看属性是否为 null，并相应地设置 NULL 或默认值。

如果该值不是 NULL，则将其转换为正确的 Type。

使用列标题的地图

当您将单个工作表映射到单个类时，使用列字母进行映射非常有用，但是如果您有一个需要从不同 Excel 文件类型获取数据的类，该怎么办？例如，我们有一类 Users，但我们从几个供应商那里获得了包含用户数据的 Excel 文件，如果所有列的顺序可能不同。因此，通过标题将列映射到属性更为方便。

让我们看看我们的类会是什么样子：

public class ReadByColumnTitle
{
    [PropertyTitleName("Id")]
    public int Id { get; set; }
       
    [PropertyTitleName("FirstName")]
    public string FirstName { get; set; }
        
    [PropertyTitleName("LastName")]
    public string LastName { get; set; }
       
    [PropertyTitleName("Email")]
    public string Email { get; set; }
 
    [PropertyTitleName("Rank")]
    public double? Rank { get; set; }
}

PropertyTitleNameAttribute 也非常简单：

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]  
public class PropertyTitleNameAttribute : Attribute  
{  
    public string ColumnTitle;  
   
    public PropertyTitleNameAttribute(string columnTitle)  
    {  
        ColumnTitle = columnTitle;  
    }  
}

我们是这样使用它的：

public static class ExcelReader
{

public static List<T> ReadExcelUsingTitles<T>(
 string filePath,
 string worksheetName = null,
 Func<string, string, bool> titleCompareDelegate = null,
 int titleRow = 1,
 int initialDataRow = 2) where T : class, new()
  {
      ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

      using var package = new ExcelPackage(filePath);
      var sheet = worksheetName == null ?
          package.Workbook.Worksheets.FirstOrDefault() :
          package.Workbook.Worksheets.FirstOrDefault(s => s.Name == worksheetName);

      titleCompareDelegate ??= (a, b) => a.Trim() == b.Trim();

      var list = new List<T>();

      var properties = GetProperties<T>();

      var propertyToColumnMapping = new Dictionary<int, PropertyInfoModel>();
      //titles
      for (var col = 1; col < sheet.Dimension.End.Column + 1; col++)
      {
          var title = sheet.Cells[titleRow, col].Value?.ToString();
          if (title != null)
          {
              var property = properties.FirstOrDefault(s => titleCompareDelegate(s.Column, title));
              if (property != null)
              {
                  propertyToColumnMapping.Add(col, property);
              }
          }
      }

      //data
      for (var row = initialDataRow; row < sheet.Dimension.End.Row + 1; row++)
      {
          var item = new T();
          foreach (var (col, property) in propertyToColumnMapping)
          {
              var value = sheet.Cells[row, col].Value;

              property.PropertyInfo.SetValue(item,
                     value != null ?
                           Convert.ChangeType(value, property.Type) :
                           property.IsNullable ? null : default);
          }

          list.Add(item);
      }

      return list;
  }

  private static List<PropertyInfoModel> GetProperties<T>()
  {
     var list = new List<PropertyInfoModel>();
     foreach (var property in typeof(T).GetProperties())
     {
         var propertyColumnAttribute = property.GetCustomAttribute<PropertyColumnAttribute>();
         var propertyTitleNameAttribute = property.GetCustomAttribute<PropertyTitleNameAttribute>();
         if (propertyColumnAttribute != null || propertyTitleNameAttribute != null)
         {
             var isNullable = property.PropertyType.IsGenericType &&
                              property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);
  
             list.Add(new PropertyInfoModel
             {
                 PropertyInfo = property,
                 Name = property.Name,             
                 IsNullable = isNullable,
                 Type = isNullable ?
                       property.PropertyType.GetGenericArguments()[0] :
                       property.PropertyType,
                 Column = propertyColumnAttribute?.ColumnName ??
                          propertyTitleNameAttribute.ColumnTitle,
             });
         }
     }
  
     return list;
  }
}

public class PropertyInfoModel
{
    public PropertyInfo PropertyInfo { get; set; }
    public string Name { get; set; }
    public string Column { get; set; }
    public Type Type { get; set; }
    public bool IsNullable { get; set; }
}

ReadExcelUsingTitles<T> 方法获取 3 个参数：

filePath：Excel 文件的路径
worksheetName：我们要读取的工作表的名称，如果为 NULL，则读取第一个工作表
titleCompareDelegate：这是一个代表，我们用来将列标题与属性属性进行比较，如果未传递任何内容，则将使用内置委托。
titleRow：标题行的编号，如果未传递，将使用 1。
initialDataRow：包含数据的第一行，如果未传递，将使用 2。

我们还检查 titleCompareDelegate 是否为 NULL，如果是，我们将使用以下委托

titleCompareDelegate ??= (a, b) => a.Trim() == b.Trim();

其次，我们需要读取我们想要读入的类 T 的属性。GetProperties<T>（） 使用反射来循环访问类属性，并查找具有 PropertyColumnAttribut 属性或 PropertyTitleNameAttribute 属性的类属性。对于这些属性，我们得到：

PropertyInfo 对象。
属性名称。
来自 PropertyColumnAttribute 的列字母或_来自 PropertyTitleNameAttribute_ 的列标题。
属性类型。
如果该属性为 NULLABLE

处理的第一步是遍历标题行，将属性映射到标题，并创建列号到属性信息的字典

var propertyToColumnMapping = new Dictionary<int, PropertyInfoModel>();
 //titles
 for (var col = 1; col < sheet.Dimension.End.Column + 1; col++)
 {
     var title = sheet.Cells[titleRow, col].Value?.ToString();
     if (title != null)
     {
         var property = properties.FirstOrDefault(s => titleCompareDelegate(s.Column, title));
         if (property != null)
         {
             propertyToColumnMapping.Add(col, property);
         }
     }
 }

然后，我们遍历所有数据行，并针对每一行遍历列到属性字典中的每个项目。

//data  
for (var row = initialDataRow; row < sheet.Dimension.End.Row + 1; row++)  
{  
   var item = new T();  
   foreach (var (col, property) in propertyToColumnMapping)  
   {  
       var value = sheet.Cells[row, col].Value;  
  
       property.PropertyInfo.SetValue(item,  
              value != null ?  
                    Convert.ChangeType(value, property.Type) :  
                    property.IsNullable ? null : default);  
   }  
  
   list.Add(item);  
}

对于每个列到属性的链接，我们获取单元格值并将其添加到项目中。请注意，我们处理 NULL 值和对象转换，就像我们按列字母读取数据时所做的那样.

源代码获取：公众号回复消息【code：69873】

本文PDF版本下载

C# 技巧 ：从 Excel 文件中读取数据

C# 技巧：从 Excel 文件中读取数据