ClosedXML：强大的 Excel 操作库

ClosedXML 是一个用于创建和操作 Microsoft Excel 电子表格的 .NET 库。它提供了一个简单而直观的 API，使得在 C# 中处理 Excel 文件变得轻而易举。本文将详细介绍 ClosedXML 的使用方法，并提供多个实用的例子。

安装

首先，通过 NuGet 包管理器安装 ClosedXML：

PowerShell
Install-Package ClosedXML

基本用法

创建新的工作簿和工作表

C#
static void Main(string[] args)
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Sample Sheet");

        worksheet.Cell("A1").Value = "Hello World!";

        workbook.SaveAs("HelloWorld.xlsx");
    }

    Console.WriteLine("Excel file created successfully!");
}

读取现有的 Excel 文件

C#
static void Main(string[] args)
{
    using (var workbook = new XLWorkbook("HelloWorld.xlsx"))
    {
        var worksheet = workbook.Worksheet(1);
        var cellValue = worksheet.Cell("A1").Value;
        Console.WriteLine($"Value in A1: {cellValue}");
    }
}

高级用法

填充数据

C#
using ClosedXML.Excel;
using System.Collections.Generic;

class Program
{
    public class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
    }

    static void Main(string[] args)
    {
        var people = new List<Person>
        {
            new Person { Name = "John Doe", Age = 30 },
            new Person { Name = "Jane Smith", Age = 25 },
            new Person { Name = "Bob Johnson", Age = 35 }
        };

        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("People");

            // Add headers
            worksheet.Cell(1, 1).Value = "Name";
            worksheet.Cell(1, 2).Value = "Age";

            // Add data
            worksheet.Cell(2, 1).InsertData(people);

            workbook.SaveAs("People.xlsx");
        }

        Console.WriteLine("Excel file with people data created successfully!");
    }
}

设置单元格样式

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Styled Sheet");

    var cell = worksheet.Cell("A1");
    cell.Value = "Styled Cell";
    
    cell.Style.Font.Bold = true;
    cell.Style.Font.FontColor = XLColor.Red;
    cell.Style.Fill.BackgroundColor = XLColor.Yellow;
    cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    cell.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;

    workbook.SaveAs("StyledSheet.xlsx");
}

使用公式

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Formulas");

    worksheet.Cell("A1").Value = 10;
    worksheet.Cell("A2").Value = 20;
    worksheet.Cell("A3").FormulaA1 = "=SUM(A1:A2)";

    workbook.SaveAs("Formulas.xlsx");
}

合并单元格

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Merged Cells");

    var range = worksheet.Range("A1:C3");
    range.Merge();
    range.Value = "Merged Range";
    range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
    range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;

    workbook.SaveAs("MergedCells.xlsx");
}

添加和格式化表格

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Table");

    // Add headers
    worksheet.Cell("A1").Value = "Name";
    worksheet.Cell("B1").Value = "Age";
    worksheet.Cell("C1").Value = "City";

    // Add data
    worksheet.Cell("A2").Value = "John";
    worksheet.Cell("B2").Value = 30;
    worksheet.Cell("C2").Value = "New York";

    worksheet.Cell("A3").Value = "Jane";
    worksheet.Cell("B3").Value = 25;
    worksheet.Cell("C3").Value = "London";

    // Create table
    var table = worksheet.Range("A1:C3").CreateTable("MyTable");
    
    // Format table
    table.Theme = XLTableTheme.TableStyleMedium2;

    workbook.SaveAs("TableExample.xlsx");
}

添加页眉和页脚

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Headers and Footers");

    worksheet.PageSetup.Header.Left.AddText("Left Header");
    worksheet.PageSetup.Header.Center.AddText("Center Header");
    worksheet.PageSetup.Header.Right.AddText("Right Header");

    worksheet.PageSetup.Footer.Left.AddText("Left Footer");
    worksheet.PageSetup.Footer.Center.AddText("Center Footer");
    worksheet.PageSetup.Footer.Right.AddText("Right Footer");

    workbook.SaveAs("HeadersAndFooters.xlsx");
}

设置打印区域和页面设置

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Print Setup");

    // Add some data
    for (int i = 1; i <= 100; i++)
    {
        worksheet.Cell(i, 1).Value = $"Row {i}";
    }

    // Set print area
    worksheet.PageSetup.PrintAreas.Add("A1:B50");

    // Page setup
    worksheet.PageSetup.PaperSize = XLPaperSize.A4Paper;
    worksheet.PageSetup.PageOrientation= XLPageOrientation.Landscape;
    worksheet.PageSetup.Scale = 85; // 85% of normal size

    workbook.SaveAs("PrintSetup.xlsx");
}

数据验证

C#
static void Main(string[] args)
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Data Validation");

        // Create a list of items in a separate range  
        worksheet.Cell("E1").Value = "Apple";
        worksheet.Cell("E2").Value = "Banana";
        worksheet.Cell("E3").Value = "Cherry";
        worksheet.Cell("E4").Value = "Date";

        // List validation  
        var listRange = worksheet.Range("A1:A10");
        listRange.CreateDataValidation().List(worksheet.Range("E1:E4"), true);

        // Number range validation  
        var numberRange = worksheet.Range("B1:B10");
        numberRange.CreateDataValidation().WholeNumber.Between(1, 100);

        // Date validation  
        var dateRange = worksheet.Range("C1:C10");
        dateRange.CreateDataValidation().Date.EqualOrGreaterThan(DateTime.Today);

        workbook.SaveAs("DataValidation.xlsx");
    }
}

条件格式

C#
using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Conditional Formatting");

    // Add some data
    for (int i = 1; i <= 10; i++)
    {
        worksheet.Cell(i, 1).Value = i * 10;
    }

    // Add conditional formatting
    var range = worksheet.Range("A1:A10");
    var cf1 = range.AddConditionalFormat();
    cf1.WhenLessThan(50).Fill.SetBackgroundColor(XLColor.Red);

    var cf2 = range.AddConditionalFormat();
    cf2.WhenBetween(50, 80).Fill.SetBackgroundColor(XLColor.Yellow);

    var cf3 = range.AddConditionalFormat();
    cf3.WhenGreaterThan(80).Fill.SetBackgroundColor(XLColor.Green);

    workbook.SaveAs("ConditionalFormatting.xlsx");
}

错误处理

在使用 ClosedXML 时，应该适当地处理可能出现的异常：

C#
try
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Error Handling");
        worksheet.Cell("A1").Value = "Test";
        workbook.SaveAs("ErrorHandling.xlsx");
    }
    Console.WriteLine("Excel file created successfully!");
}
catch (IOException ex)
{
    Console.WriteLine($"An IO error occurred: {ex.Message}");
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"An invalid operation occurred: {ex.Message}");
}
catch (Exception ex)
{
    Console.WriteLine($"An error occurred: {ex.Message}");
}

结论

ClosedXML 是一个功能强大且易于使用的 Excel 操作库。它提供了广泛的功能，从基本的单元格操作到复杂的图表创建和条件格式设置。通过本文提供的示例，你应该能够处理大多数 Excel 相关的任务。

目录

安装