如何在 C# 中创建、读取或更新 Excel 文档

Source: Dev.to

入门使用 Spire.XLS for .NET

在编写代码之前，先设置好开发环境。Spire.XLS for .NET 可以通过 NuGet 添加到 C# 项目中。

通过 NuGet 安装

1. 在 Visual Studio 中打开你的项目。
2. 在 Solution Explorer 中右键单击项目 → Manage NuGet Packages…。
3. 搜索 Spire.XLS 并安装该包。

基本初始化

using Spire.Xls;
using System;
using System.IO;

// ... inside your method or class
Workbook workbook = new Workbook();
// Your Excel operations will go here
workbook.Dispose(); // Always dispose of the workbook object

在 C# 中创建 Excel 文档

Spire.XLS 让创建新工作簿、添加工作表、填充数据以及应用样式变得非常简单。

示例：创建带数据和样式的基础 Excel 文件

using Spire.Xls;
using System;
using System.IO;
using System.Drawing; // For Color

class ExcelCreator
{
    public static void CreateExcelFile()
    {
        // 1. Create a new workbook
        Workbook workbook = new Workbook();

        // 2. Get the first worksheet and rename it
        Worksheet sheet = workbook.Worksheets[0];
        sheet.Name = "Product Sales";

        // 3. Write header row
        sheet.Range["A1"].Text = "Product ID";
        sheet.Range["B1"].Text = "Product Name";
        sheet.Range["C1"].Text = "Quantity";
        sheet.Range["D1"].Text = "Unit Price";
        sheet.Range["E1"].Text = "Total";

        // Apply basic styling to header
        CellStyle headerStyle = workbook.Styles.Add("HeaderStyle");
        headerStyle.Font.IsBold = true;
        headerStyle.KnownColor = ExcelColors.LightYellow;
        headerStyle.HorizontalAlignment = HorizontalAlignType.Center;
        sheet.Range["A1:E1"].Style = headerStyle;

        // 4. Populate data rows
        sheet.Range["A2"].Value = "P001";
        sheet.Range["B2"].Text = "Laptop";
        sheet.Range["C2"].NumberValue = 10;
        sheet.Range["D2"].NumberValue = 1200.50;
        sheet.Range["E2"].Formula = "=C2*D2";

        sheet.Range["A3"].Value = "P002";
        sheet.Range["B3"].Text = "Mouse";
        sheet.Range["C3"].NumberValue = 50;
        sheet.Range["D3"].NumberValue = 25.00;
        sheet.Range["E3"].Formula = "=C3*D3";

        // 5. Auto‑fit columns for readability
        for (int i = 1; i <= 5; i++)
            sheet.AutoFitColumn(i);

        // 6. Save the workbook
        string filePath = "ProductSales.xlsx";
        workbook.SaveToFile(filePath, ExcelVersion.Version2016);
        Console.WriteLine($"Excel file created at: {Path.GetFullPath(filePath)}");

        // 7. Clean up
        workbook.Dispose();
    }
}

此示例演示了如何：

• 创建工作簿和工作表
• 写入字符串和数值数据
• 应用基础样式（粗体、背景色、对齐）
• 添加公式
• 保存文件

从 Excel 文档读取数据

Spire.XLS 能加载已有工作簿，并让你访问单元格的值、公式和批注。

示例：读取已有 Excel 文件中的数据

using Spire.Xls;
using System;
using System.IO;

class ExcelReader
{
    public static void ReadExcelFile(string filePath)
    {
        if (!File.Exists(filePath))
        {
            Console.WriteLine($"Error: File not found at {filePath}");
            return;
        }

        // 1. Load the workbook
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(filePath);

        // 2. Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];
        Console.WriteLine($"--- Reading data from sheet: {sheet.Name} ---");

        // 3. Read specific cells
        Console.WriteLine($"Product ID (A2): {sheet.Range["A2"].Text}");
        Console.WriteLine($"Product Name (B2): {sheet.Range["B2"].Text}");
        Console.WriteLine($"Quantity (C2): {sheet.Range["C2"].NumberValue}");
        Console.WriteLine($"Unit Price (D2): {sheet.Range["D2"].NumberValue}");
        Console.WriteLine($"Total (E2) – Value: {sheet.Range["E2"].NumberValue}");
        Console.WriteLine($"Total (E2) – Formula: {sheet.Range["E2"].Formula}");

        // 4. Iterate through all used cells
        Console.WriteLine("\n--- All Data ---");
        for (int row = 1; row <= sheet.LastRow; row++)
        {
            for (int col = 1; col <= sheet.LastColumn; col++)
            {
                Console.Write($"{sheet.Range[row, col].DisplayText}\t");
            }
            Console.WriteLine();
        }

        // 5. Clean up
        workbook.Dispose();
    }
}

关键要点：

• LoadFromFile 用于加载已有工作簿。
• Range["A2"]（或 Range[row, col]）访问单个单元格。
• DisplayText 返回单元格值的格式化表示。

更新已有 Excel 文档

修改工作簿的步骤相同：加载 → 更改 → 保存。

示例：更新已有 Excel 文件

using Spire.Xls;
using System;
using System.IO;

class ExcelUpdater
{
    public static void UpdateExcelFile(string filePath)
    {
        if (!File.Exists(filePath))
        {
            Console.WriteLine($"Error: File not found at {filePath}");
            return;
        }

        // 1. Load the workbook
        Workbook workbook = new Workbook();
        workbook.LoadFromFile(filePath);

        // 2. Get the first worksheet
        Worksheet sheet = workbook.Worksheets[0];
        Console.WriteLine($"--- Updating data in sheet: {sheet.Name} ---");

        // 3. Modify a specific cell (e.g., change quantity of Laptop from 10 to 15)
        sheet.Range["C2"].NumberValue = 15;
        Console.WriteLine($"Updated Quantity for Laptop (C2) to: {sheet.Range["C2"].NumberValue}");

        // 4. Save the changes (overwrite or save as a new file)
        string updatedPath = "ProductSales_Updated.xlsx";
        workbook.SaveToFile(updatedPath, ExcelVersion.Version2016);
        Console.WriteLine($"Updated file saved at: {Path.GetFullPath(updatedPath)}");

        // 5. Clean up
        workbook.Dispose();
    }
}

此代码片段展示了如何：

• 加载已有工作簿
• 更改单元格的值（或公式、样式等）
• 使用 SaveToFile 保存修改