C# Excel可排序表头

引言

学习如何使用C#和NuGet包SpreadsheetLight在Excel工作表中通过创建表格来实现可排序的表头。

为什么选择SpreadsheetLight

它是免费的，提供可下载的帮助文件和大量示例代码。该包不提供读取工作表的方法，但可以使用其他包如ExcelMapper（示例代码中使用了该包）。

最佳学习方式

这取决于开发人员的经验和舒适度。大多数开发人员可以检查代码，配置数据库，然后运行控制台项目。从这里开始，研究具有有用类和方法名称的代码。

项目结构

主项目是一个包含EF Core代码的控制台项目，以及一个用于Excel操作的类项目。将Excel操作分离到类项目中允许其他项目类型使用Excel代码。与其在下面显示代码，最好的学习方式是先运行控制台项目，然后返回并逐步检查代码，代码中有充足的注释。

准备工作

使用SSMS在SQLEXPRESS或任何可用服务器下创建名为CustomerDatabase1的数据库。如果不在SQLEXPRESS下，请更改控制台项目中appsettings.json中的连接字符串。在控制台项目的Scripts文件夹中，在SSMS中打开Populate.sql并执行以用数据填充表。

源代码

类项目源代码控制台源代码

.NET框架

使用版本9，但应适用于版本8，并将适用于版本10。

如何在代码示例之外使用

使用类来填充可以展平到DataTable中的数据，或用数据填充DataTable。在以下示例中，table变量是一个System.Data.DataTable，DataColumn需要是第一列，SetOrdinal将列重新定位为工作表中的第一个单元格/第一行。

1

table.Columns["Identifier"].SetOrdinal(0);

接下来（可选）重命名DataColumn的名称，例如显示First Name而不是ContactFirstName。

1

table.Columns["ContactFirstName"].ColumnName = "First Name";

要创建Excel表格将以下方法添加到类中以执行Excel操作。没有选择工作表，因为编码中只有一个工作表。如果此代码要在现有文件中使用不同的工作表，以下代码显示如何创建新工作表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


public bool AddNewSheet(string fileName, string sheetName)
{
    using SLDocument document = new(fileName);

    if (!(document.GetSheetNames(false)
            .Any((workSheetName) => 
                string.Equals(workSheetName, sheetName, StringComparison.CurrentCultureIgnoreCase))))
    {
        document.AddWorksheet(sheetName);
        document.Save();
        return true;
    }
    else
    {
        return false;
    }
}

在保存Excel文件之前直接调用代码（参见源代码）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20


public static void CreateSortableHeader(SLDocument document)
{
    SLWorksheetStatistics? statistics = document.GetWorksheetStatistics();
    var lastColIndex = statistics.EndColumnIndex;

    string lastColLetter = SLConvert.ToColumnName(lastColIndex);
    List<string> columnNames = [];

    var headers = Headers(document, columnNames);

    // 设置表头
    for (var index = 0; index < headers.Length; index++)
        document.SetCellValue(1, index + 1, headers[index]);

    var table = document.CreateTable("A1", $"{lastColLetter}{statistics.EndRowIndex + 1}");

    table.HasTotalRow = true;

    document.InsertTable(table);
}

调整列顺序

日志记录

使用Serilog NuGet包将任何运行时异常记录到文件中，本例中为控制台项目。

涉及的步骤

使用EF Core从SQL Server数据库读取数据
使用NuGet包FastMember将数据转换为DataTable
在创建Excel文件之前，确保文件未打开
创建Excel
在DataTable中重新排序列
更改DataTable中的列名
将数据导入Excel文件
设置第一行表头样式
重命名工作表名称
冻结第一行/表头行
自动调整工作表列宽
为数据创建表格，提供筛选和排序功能
保存文件

为了验证Excel中的数据，使用NuGet包ExcelMapper读回工作表数据。

EF Core代码

以下代码读取要导出到Excel的数据。CustomerReportView类从三个表中展平数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29


public static class DataOperations
{
    public static async Task<(List<CustomerReportView> Data, bool IsSuccess)> GetCustomerReportData()
    {
        try
        {
            await using var context = new Context();
            var data = await context.Customers
                .Include(c => c.ContactTypeIdentifierNavigation)
                .Include(c => c.GenderIdentifierNavigation)
                .Select(c => new CustomerReportView
                {
                    Identifier = c.Identifier,
                    ContactFirstName = c.ContactFirstName,
                    ContactLastName = c.ContactLastName,
                    GenderType = c.GenderIdentifierNavigation.GenderType,
                    ContactType = c.ContactTypeIdentifierNavigation.ContactType
                })
                .ToListAsync();

            return (data, true);
        }
        catch (Exception ex)
        {
            Log.Error(ex, "Error fetching customer report data");
            return ([], false);
        }
    }
}

连接字符串存储在appsettings.json中。以下代码在DbContext中读取连接字符串。

1
2
3
4
5


private static string GetMainConnectionString()
    => Config.Configuration.JsonRoot()
        .GetSection(nameof(ConnectionStrings))
        .Get<ConnectionStrings>()
        .MainConnection;

JsonRoot、ConnectionStrings和MainConnection来自NuGet包ConsoleConfigurationLibrary。

Excel代码

导出DataTable的基本代码

创建SpreadSheetLight文档类SLDocument的实例使用ImportDataTable添加数据保存文件。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName, int row)
{
    using var document = new SLDocument();

    document.ImportDataTable(row, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // 给工作表起一个有用的名字
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    document.SaveAs(fileName);
}

导出日期没有正确格式化，日期将无法正确显示。在以下代码中，DateTime列位于位置6。变量dateStyle创建一个样式来格式化日期列。有关格式化数字和日期的信息，请参见以下内容。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName, int row)
{
    using var document = new SLDocument();

    document.ImportDataTable(row, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // 给工作表起一个有用的名字
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    SLStyle dateStyle = document.CreateStyle();
    dateStyle.FormatCode = "mm-dd-yyyy";
    // 使用上述样式格式化特定列
    int dateColumnIndex = 6;
    document.SetColumnStyle(dateColumnIndex, dateStyle);

    document.SaveAs(fileName);
}

总结

通过遵循上述说明并逐步查看提供的源代码，开发人员可以将数据导入Excel工作表，并提供筛选和排序功能。

另请参阅

C# Excel廉价读写使用Dapper和SpreadsheetLight从数据库导入数据到Excel

C#实现Excel可排序表头：使用SpreadsheetLight库高效操作Excel

本文详细介绍了如何使用C#和SpreadsheetLight库创建Excel可排序表头，包括数据从SQL Server导出、DataTable处理、Excel表格创建及样式设置等完整技术流程。