C# 技巧 ：将数据写入 Excel

 代码下载地址：https://download.csdn.net/download/qq_36078699/89702269

首先，我们假设我们需要创建一个 WorksheetDataModel 实例，该实例将被传递给创建 Excel 文件本身的类（稍后将使用）。

public class WorksheetDataModel
{
    public Dictionary<string, string> HeaderMap { get; set; }
    public List<Dictionary<string, ExcelCellModel>> DataRows { get; set; }
    public string WorksheetName { get; set; }
    public bool RightToLeft { get; set; } = true;
}

public class ExcelCellModel
{
    public Type Type { get; set; }
    public object Value { get; set; }
}

现在让我们看看如何通过上述 3 种方式将对象列表转换为 WorksheetDataModel。

1. 传递标头名称列表：
   假设我们有以下类

public class ClassWithHeader  
{  
    public int Id { get; set; }  
          
    public string FirstName { get; set; }  
          
    public string LastName { get; set; }  
          
    public string Email { get; set; }  
}

// a list of object  
var list = new List<ClassWithHeader>  
{  
    new ClassWithHeader{Email = "a.b@mail.com",FirstName = "a",   
                        LastName = "b",Id = 1},  
    new ClassWithHeader{Email = "c.d@mail.com",FirstName = "c",   
                        LastName = "d",Id = 2},  
    new ClassWithHeader{Email = "e.f@mail.com",FirstName = "e",   
                        LastName = "f",Id = 2},  
};  

//a list of header names  
var headerNames = new List<string> { "id", "name", "second name", "email" }; 

//a method to create a WorksheetDataModel  
WorksheetDataModel model=WorksheetDataModelCreator  
            .CreateWorksheetDataModel(list, "ClassWithHeaderList", headerNames);

public static WorksheetDataModel CreateWorksheetDataModel<T>(List<T> model, WorksheetExportModel worksheet)
{
    var headerMap = GetHeaderMap(worksheet.ColumnHeaders, GetModelPropertiesNames<T>());

    //will talk about this a bit later
    var dataRows = GetExcelDictionary(model, headerMap);

    return new WorksheetDataModel
    {
        RightToLeft = worksheet.RightToLeft,
        WorksheetName = worksheet.WorksheetName,
        DataRows = dataRows,
        HeaderMap = headerMap
    };
}
private static List<string> GetModelPropertiesNames<T>()
{
    return typeof(T).GetProperties().Select(p => p.Name).ToList();
}

private static Dictionary<string, string> GetHeaderMap(List<string> columnHeaders, List<string> columnNames)
{
    if (columnHeaders.Count != columnNames.Count)
    {
        throw new Exception("the columnNames and columnHeaders lists must have the same length");
    }

    var headerMap = new Dictionary<string, string>();
    for (var i = 0; i < columnNames.Count; i++)
    {
        headerMap.Add(columnNames[i], columnHeaders[i]);
    }
    return headerMap;
}

这是创建 WorksheetDataModel 的代码（GetExcelDictionary 方法对于所有 3 种方法都是相同的，因此我稍后会讨论它_）。_

第一步是创建一个标题映射：一个将属性名称映射到列标题的字典，我们稍后将使用它从我们的对象列表中获取数据并将其放在正确的列中。
在这里，标题映射的创建很简单，我们有一个列标题列表，我们可以通过使用反射和获取我们正在使用的 Type 的属性列表来获取属性名称列表（请参阅 GetModelPropertiesNames 方法）。然后，我们只需遍历属性列表并将其作为键添加到字典中，并将列标题设置为值。

注意：这里有一个小问题，我们需要确保标题列表的顺序与类属性的顺序相同，否则我们将得到错误的列标题。

2. 设置类属性的属性。
让我们定义一个新属性 _ExcelColumnNameAttribute，_它所做的只是允许我们为类属性定义一个 Excel 名称。

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

现在，我们想要导出的类将如下所示

public class ClassWithAttributeName
{
    [ExcelColumnName("id")]
    public int Id { get; set; }
   
    [ExcelColumnName("given_name")]
    public string FirstName { get; set; }
    
    [ExcelColumnName("family_name")]
    public string LastName { get; set; }
   
    [ExcelColumnName("email")]
    public string Email { get; set; }
}

我们的出口代码将是

// a list of object  
var list = new List<ClassWithAttributeName>  
{  
    new ClassWithAttributeName{Email = "a.b@mail.com",FirstName = "a", LastName = "b",Id = 1},  
    new ClassWithAttributeName{Email = "c.d@mail.com",FirstName = "c", LastName = "d",Id = 2},  
    new ClassWithAttributeName{Email = "e.f@mail.com",FirstName = "e", LastName = "f",Id = 2},  
};  
  
WorksheetDataModel model = WorksheetDataModelCreator= WorksheetDataModelCreator  
                    .CreateWorksheetDataModel(list, "ClassWithAttributeName");

public static WorksheetDataModel CreateWorksheetDataModel<T>(List<T> model, WorksheetExportModel worksheet)
{
    var headerMap =GetHeaderMapFromAttributes<T>();
    var dataRows = GetExcelDictionary(model, headerMap);
    return new WorksheetDataModel
    {
        RightToLeft = worksheet.RightToLeft,
        WorksheetName = worksheet.WorksheetName,
        DataRows = dataRows,
        HeaderMap = headerMap
    };
}

private static Dictionary<string, string> GetHeaderMapFromAttributes<T>()
{
    var columnNames = new Dictionary<string, string>();
    foreach (var property in typeof(T).GetProperties())
    {
        var columnNameAttribute = property.GetCustomAttribute<ExcelColumnNameAttribute>();
        if (columnNameAttribute != null)
        {
            columnNames.Add(property.Name, columnNameAttribute.ColumnName);
        }
    }
    return columnNames;
}

我们创建 WorksheetDataModel 的方式非常相似，不同之处在于我们通过使用反射从属性上的 ExcelColumnName 属性中获取列标题。

这种方式更简单，更不容易出错，但列的标题是硬编码的，因此对于需要支持多种语言的系统来说不太实用

3. 设置类属性的属性，该属性将用于从资源字典中获取值。
让我们定义一个新属性 ExcelColumnResourceKeyAttribute。它将允许我们为类属性定义资源键。

[AttributeUsage(AttributeTargets.Property, AllowMultiple = false)]  
public class ExcelColumnResourceKeyAttribute : Attribute  
{  
    public string ColumnKey;  
   
    public ExcelColumnResourceKeyAttribute(string columnKey)  
    {  
        ColumnKey = columnKey;  
    }  
}

所以我们的班级将是

public class ClassWithAttributeKey
{
    [ExcelColumnResourceKey("IdKey")]
    public int Id { get; set; }
       
    [ExcelColumnResourceKey("FirstNameKey")]
    public string FirstName { get; set; }
        
    [ExcelColumnResourceKey("LastNameKey")]
    public string LastName { get; set; }
       
    [ExcelColumnResourceKey("EmailKey")]
    public string Email { get; set; }
}

我们的出口代码将是

// a list of object
var list = new List<ClassWithAttributeKey>
{
    new ClassWithAttributeKey{Email = "a.b@mail.com",FirstName = "a", LastName = "b",Id = 1},
    new ClassWithAttributeKey{Email = "c.d@mail.com",FirstName = "c", LastName = "d",Id = 2},
    new ClassWithAttributeKey{Email = "e.f@mail.com",FirstName = "e", LastName = "f",Id = 2},
};

//get resources from a data base
var resources = new Dictionary<string, string>
{
    {"IdKey","id"},
    {"FirstNameKey","first_name"},
    {"LastNameKey","last_name"},
    {"EmailKey","email"}
};

 WorksheetDataModel model= WorksheetDataModelCreator
          .CreateWorksheetDataModel(list, "ClassWithAttributeKey", resources));

public static WorksheetDataModel CreateWorksheetDataModel<T>(List<T> model, WorksheetExportModel worksheet)
  {
      var headerMap = GetResourceHeaderMapFromAttributes<T>(worksheet.Resources);
      var dataRows = GetExcelDictionary(model, headerMap);
      return new WorksheetDataModel
      {
          RightToLeft = worksheet.RightToLeft,
          WorksheetName = worksheet.WorksheetName,
          DataRows = dataRows,
          HeaderMap = headerMap
      };
  }

  private static Dictionary<string, string> GetResourceHeaderMapFromAttributes<T>(Dictionary<string, string> resources)
  {
      var columnNames = new Dictionary<string, string>();
      foreach (var property in typeof(T).GetProperties())
      {
          var resourceKeyAttribute = property.GetCustomAttribute<ExcelColumnResourceKeyAttribute>();
          if (resourceKeyAttribute != null)
          {
              columnNames.Add(property.Name, resources[resourceKeyAttribute.ColumnKey]);
          }
      }
      return columnNames;
  }

这与前一种方式非常相似，不同之处在于我们使用反射从 ExcelColumnResourceKey 属性中获取资源键，并从单独的字典中获取其值。

这种方式很简单，支持多语言使用。

按照承诺使用 GetExcelDictionary。

现在我们有了标题映射，以显示的任何方式，我们都可以使用它来将我们的对象转换为更易于与 Excel 一起使用的内容。

public class ExcelCellModel
{
    public Type Type { get; set; }
    public object? Value { get; set; }
}

private static List<Dictionary<string, ExcelCellModel>> GetExcelDictionary<T>(List<T> list, Dictionary<string, string> headerMap)
{
    var dict = new List<Dictionary<string, ExcelCellModel>>();
    var properties =  typeof(T).GetProperties();
    foreach (var item in list)
    {
        var row = new Dictionary<string, ExcelCellModel>();

        foreach (var prop in properties)
        {
            if (headerMap.ContainsKey(prop.Name))
            {
                row.Add(headerMap[prop.Name], new ExcelCellModel
                {
                    Type = prop.PropertyType,
                    Value = prop.GetValue(item) ?? null
                });
            }
        }
        dict.Add(row);
    }
    return dict;
}

首先，我们创建了一个新模型 ExcelCellModel，该模型将保存每个属性的值和 Type（我们稍后将使用 Type 进行格式设置）。
现在，我们循环访问列表并将每个对象转换为 _Dictionary<字符串 ExcelCellModel>，_键是该属性的列标题，值是属性值（保存值和类型的 ExcelCellModel）。

现在，我们只需将所有数据放入 WorksheetDataModel 的新实例中。
请注意，我们还获得了工作表名称和 RightToLeft 指示器，因此我们使用它们来创建 Excel 文件。

public static WorksheetDataModel CreateWorksheetDataModel<T>(List<T> model, WorksheetExportModel worksheet)
{
    var headerMap = GetResourceHeaderMapFromAttributes<T>(worksheet.Resources);

    var dataRows = GetExcelDictionary(model, headerMap);

    return new WorksheetDataModel
    {
        RightToLeft = worksheet.RightToLeft,
        WorksheetName = worksheet.WorksheetName,
        DataRows = dataRows,
        HeaderMap = headerMap
    };
}

实际创建 Excel 文件。

现在我们有了所需的所有数据，让我们专注于如何创建 Excel 文件。
我们可以先将 EPPlus nuget 添加到我们的项目中。EPPlus 是一个用于处理 Excel 文件的优秀库，它支持 Excel 的大部分选项。

public static class ExportToExcel
{
    public static byte[] ExportWorksheets(WorksheetDataModel worksheet)
    {
        return ExportWorksheets(new List<WorksheetDataModel> { worksheet });
    }

    public static byte[] ExportWorksheets(List<WorksheetDataModel> worksheetList)
    {
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        var package = new ExcelPackage();

        foreach (var worksheet in worksheetList)
        {

            var ws = package.Workbook.Worksheets.Add(worksheet.WorksheetName);
            ws.View.RightToLeft = worksheet.RightToLeft;

            const int titleRowsOffset = 0;

            //column headers
            var colIndex = 0;
            foreach (var x in worksheet.HeaderMap)
            {
                ws.SetValue(titleRowsOffset + 1, colIndex + 1, x.Value);
                ws.Cells[titleRowsOffset + 1, colIndex + 1].Style.Font.Bold = true;
                var col = ws.Column(colIndex + 1);
                col.AutoFit(15);

                colIndex++;
            }

            //data
            for (var row = 1; row <= worksheet.DataRows.Count; row++)
            {
                var currentRow = worksheet.DataRows[row - 1];
                var cell = 1;

                foreach (var header in worksheet.HeaderMap)
                {
                    var isExistInDictionary = currentRow.Keys.Contains(header.Value);

                    ws.SetValue(titleRowsOffset + row + 1, cell, isExistInDictionary ? currentRow[header.Value].Value : null);
                    using (var rng = ws.Cells[titleRowsOffset + row + 1, cell])
                    {
                        if (isExistInDictionary)
                        {
                            rng.Style.Numberformat.Format = GetStringFormat(currentRow[header.Value].Value);
                        }
                    }
                    cell++;
                }
            }
        }

        return package.GetAsByteArray();
    }

    private static string GetStringFormat(object obj)
    {
        if (obj != null)
        {
            switch (obj.GetType().ToString())
            {
                case "System.float":
                    {
                        return "#,###,##0.00";
                    }
                case "System.double":
                    {
                        return "#,###,##0.00";
                    }
                case "System.float?":
                    {
                        return "#,###,##0.00";
                    }
                case "System.double?":
                    {
                        return "#,###,##0.00";
                    }
                case "System.DateTime":
                    {
                        return "dd/MM/yyyy";
                    }
                case "System.DateTime?":
                    {
                        return "dd/MM/yyyy";
                    }
                case "System.Int32":
                    {
                        return "#";
                    }
                case "System.Int32?":
                    {
                        return "#";
                    }
                default: { return ""; }
            }

        }
        return "";
    }
}

让我们看一下 ExportWorksheets（List<ExportWorksheets（List<WorksheetDataModel> worksheetList）> worksheetList） 方法。
我们可以看到它获取了 WorksheetDataModel 的列表，因此我们可以创建一个包含多个工作表的 Excel 文件，每个工作表都使用不同的对象列表。

该方法循环访问 WorksheetDataModel 的列表，并为每个列表创建一个新的工作表。我们设置工作表名称和 RightToLeft 指示器。

foreach (var worksheet in worksheetList)  
{  
   var ws = package.Workbook.Worksheets.Add(worksheet.WorksheetName);  
   ws.View.RightToLeft = worksheet.RightToLeft;  
   const int titleRowsOffset = 0;  
  
....  
}

然后我们在第一行添加列标题，并将字体设置为**粗体，**并设置最小宽度

foreach (var x in worksheet.HeaderMap)  
{  
    ws.SetValue(titleRowsOffset + 1, colIndex + 1, x.Value);  
    ws.Cells[titleRowsOffset + 1, colIndex + 1].Style.Font.Bold = true;  
    var col = ws.Column(colIndex + 1);  
    col.AutoFit(MinimumWidth: 15);   
  
    colIndex++;  
}

现在剩下的就是添加我们的数据

//data
for (var row = 1; row <= worksheet.DataRows.Count; row++)
{
    var currentRow = worksheet.DataRows[row - 1];
    var cell = 1;

    foreach (var header in worksheet.HeaderMap)
    {
        var isExistInDictionary = currentRow.ContainsKey(header.Value);
        var currentValue = currentRow[header.Value];       
        ws.SetValue(titleRowsOffset + row + 1, cell, isExistInDictionary ? currentValue.Value : null);
        using (var excelRange = ws.Cells[titleRowsOffset + row + 1, cell])
        {
            if (isExistInDictionary)
            {
                excelRange.Style.Numberformat.Format = GetStringFormat(currentValue.Value);
            }
        }
        cell++;
    }
}

最后一部分是如何设置正确的单元格格式

private static string GetStringFormat(object obj)  
 {  
     if (obj != null)  
     {  
         switch (obj.GetType().ToString())  
         {  
             case "System.float":  
                 {  
                     return "#,###,##0.00";  
                 }  
             case "System.double":  
                 {  
                     return "#,###,##0.00";  
                 }  
             case "System.float?":  
                 {  
                     return "#,###,##0.00";  
                 }  
             case "System.double?":  
                 {  
                     return "#,###,##0.00";  
                 }  
             case "System.DateTime":  
                 {  
                     return "dd/MM/yyyy";  
                 }  
             case "System.DateTime?":  
                 {  
                     return "dd/MM/yyyy";  
                 }  
             case "System.Int32":  
                 {  
                     return "#";  
                 }  
             case "System.Int32?":  
                 {  
                     return "#";  
                 }  
             default: { return ""; }  
         }  
  
     }  
     return "";  
 }

在这里，我使用硬编码的 dateTime 格式，但您可以使用它来获取更动态的内容CultureInfo

//just pass the culture name with the worksheet name and RightToLeft  
CultureInfo us = new CultureInfo("en-US");  
string shortUsDateFormatString = us.DateTimeFormat.ShortDatePattern;  
string shortUsTimeFormatString = us.DateTimeFormat.ShortTimePattern;

我们将 Excel 数据以字节数组的形式返回，您可以查看有关如何将文件保存到磁盘的 EPPlus 文档，或者如果您正在构建 Web API，请使用以下代码通过 HTTP 返回文件。

[HttpGet("ClassWithAttributeName")]
public async Task<IActionResult> ClassWithAttributeName()
{
    var list = new List<ClassWithAttributeName>
    {
        new ClassWithAttributeName{Email = "a.b@mail.com",FirstName = "a", LastName = "b",Id = 1},
        new ClassWithAttributeName{Email = "c.d@mail.com",FirstName = "c", LastName = "d",Id = 2},
        new ClassWithAttributeName{Email = "e.f@mail.com",FirstName = "e", LastName = "f",Id = 2},
    };

    var excelFileBytes = ExportToExcel.ExportWorksheets(WorksheetDataModelCreator.CreateWorksheetDataModel(list, "ClassWithAttributeName"));

    //create a FileContentResult
    return new FileContentResult(excelFileBytes, 
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    {
        FileDownloadName = "ClassWithAttributeName.xlsx"
    };
}

我希望这将帮助您将数据导出到 Excel，并表明一旦您了解了基础知识，它就不会那么可怕了。