如何在 C# 中打开 Excel 文件并写入数据

IronXL - An Excel Library

IronXL is a .NET Excel Library that facilitates the creation, reading, and editing of Excel files in C# applications. It offers exceptional performance and accurate output. The library supports all Excel workbook file formats, including XLS, XLSX, XLSM, CSV, and TSV. Additionally, it allows data to be saved or exported in formats such as JSON, HTML, Binary, Byte Array, DataSet, or DataTable.

With IronXL, developers can work with worksheets and cell ranges seamlessly. It offers the ability to edit formulas and easily recalculate them within a sheet. Sorting data based on range, column, or row is straightforward. The library provides features to modify layouts, such as freezing panes, auto-sizing rows/columns, and adding/removing rows/columns.

IronXL also enables the protection of Excel files with user passwords and permissions for editing. Another notable feature is the ability to add, remove, and extract images from Excel worksheets. The library offers a wide range of Excel functions, supporting various cell data formats. These features make IronXL one of the most user-friendly APIs for working with Excel files.

One of the significant advantages of IronXL is that it does not require Microsoft Excel to be installed on the machine, eliminating the need for Office Interop or any other dependencies. It is compatible with multiple platforms and supports .NET 7, 6, and 5. It is also compatible with .NET Core 2 and 3, as well as .NET Framework 4.5 and later versions for working with Excel spreadsheets.

Create a Console Application

The latest version of Visual Studio IDE is recommended to create the application. Visual Studio is the official IDE for C# development, and it is assumed that you have already installed it. If you haven't installed Visual Studio, you can download it from the official Microsoft Visual Studio website.

Follow these steps to create a new project named "DemoApp".

1. Open Visual Studio and click on Create a New Project

   New Project

2. Select Console Application and Click Next

   New Project Type

3. Enter the name of the Project

   New Project Name

4. Select the .NET version. Choose the stable version .NET 6.0.

   New Project Additional Info

Install IronXL Library

Once the project is created, the IronXL library needs to be installed in the project to use it. Follow these steps to install it.

1. Open Manage NuGet Packages for Solutions either from Solution Explorer or Tools.

   NuGet Package Manager

2. Browse for IronXL Library and select the current project. Click install.

   Search and install the IronXL package in NuGet Package Manager UI

Add the following namespace at the top of Program.cs file

using IronXL;

using IronXL;

Imports IronXL

Open an Existing Excel File in C#

IronXL provides the facility to open an existing Excel file, or you can create a new Excel file. This example is going to open an existing file using C# IronXL.

// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");

' Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")

Now, let's select its first worksheet. You can select a worksheet by index number or by name. The DefaultWorkSheet property can help to get the first sheet.

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Select worksheet by name 
WorkSheet ws = workBook.GetWorkSheet("Sheet1"); 

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];

// Select worksheet by name 
WorkSheet ws = workBook.GetWorkSheet("Sheet1"); 

// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;

' Select worksheet at index 0
Dim workSheet As WorkSheet = workBook.WorkSheets(0)

' Select worksheet by name 
Dim ws As WorkSheet = workBook.GetWorkSheet("Sheet1")

' Get any existing worksheet
Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet

The code above gets the first sheet from the Excel workbook. For creating a new Excel file with data, check this code example page.

Now, let's write data to the Excel file using the IronXL object library.

Write Data to Excel File in C#

Writing data to an Excel file using IronXL is very easy. There are multiple ways to achieve it, but the simplest method is to use the Excel cell reference.

// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";

// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";

' Access A1 cell and write the value
workSheet("A1").Value = "Value using cell reference"

It is also possible to write data to a range of cells. The following code writes data from cell B1 to B5.

// Write the same value to cells from B1 to B5
workSheet["B1:B5"].Value = "Range value";

// Write the same value to cells from B1 to B5
workSheet["B1:B5"].Value = "Range value";

' Write the same value to cells from B1 to B5
workSheet("B1:B5").Value = "Range value"

We can also fill in the range with a for loop, making it dynamic. The code goes as follows:

// Specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
    // Write the Dynamic value in column C
    workSheet["C" + i].Value = "Value: " + i;

    // Write the Dynamic value in column D
    workSheet["D" + i].Value = "Value: " + i;
}

// Specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
    // Write the Dynamic value in column C
    workSheet["C" + i].Value = "Value: " + i;

    // Write the Dynamic value in column D
    workSheet["D" + i].Value = "Value: " + i;
}

' Specify range in which we want to write the values
For i As Integer = 1 To 5
	' Write the Dynamic value in column C
	workSheet("C" & i).Value = "Value: " & i

	' Write the Dynamic value in column D
	workSheet("D" & i).Value = "Value: " & i
Next i

Another way of writing data to an Excel file is by using the Replace method.

// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");

// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");

' Replace the value in cell D5
workSheet("D5").Replace("Value: 5", "Replaced Value")

Save an Excel File in C#

This section explains how to save the Excel file with newly written content to it.

// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");

// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");

' Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx")

The complete code goes as follows:

using System;
using IronXL;

class Program
{
    static void Main(string[] args)
    {
        // Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Select worksheet at index 0
        WorkSheet workSheet = workBook.WorkSheets[0];

        // Access A1 cell and write the value
        workSheet["A1"].Value = "Value using cell reference";

        // Write the same value to cells from B1 to B5
        workSheet["B1:B5"].Value = "Range value";

        // Specify range in which we want to write the values
        for (int i = 1; i <= 5; i++)
        {
            // Write the Dynamic value in column C
            workSheet["C" + i].Value = "Value: " + i;

            // Write the Dynamic value in column D
            workSheet["D" + i].Value = "Value: " + i;
        }

        // Replace the value in cell D5
        workSheet["D5"].Replace("Value: 5", "Replaced Value");

        // Save the updated Excel workbook to a file
        workBook.SaveAs("sample.xlsx");
        Console.WriteLine("Successfully written to Excel File");
    }
}

using System;
using IronXL;

class Program
{
    static void Main(string[] args)
    {
        // Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
        WorkBook workBook = WorkBook.Load("sample.xlsx");

        // Select worksheet at index 0
        WorkSheet workSheet = workBook.WorkSheets[0];

        // Access A1 cell and write the value
        workSheet["A1"].Value = "Value using cell reference";

        // Write the same value to cells from B1 to B5
        workSheet["B1:B5"].Value = "Range value";

        // Specify range in which we want to write the values
        for (int i = 1; i <= 5; i++)
        {
            // Write the Dynamic value in column C
            workSheet["C" + i].Value = "Value: " + i;

            // Write the Dynamic value in column D
            workSheet["D" + i].Value = "Value: " + i;
        }

        // Replace the value in cell D5
        workSheet["D5"].Replace("Value: 5", "Replaced Value");

        // Save the updated Excel workbook to a file
        workBook.SaveAs("sample.xlsx");
        Console.WriteLine("Successfully written to Excel File");
    }
}

Imports System
Imports IronXL

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
		Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")

		' Select worksheet at index 0
		Dim workSheet As WorkSheet = workBook.WorkSheets(0)

		' Access A1 cell and write the value
		workSheet("A1").Value = "Value using cell reference"

		' Write the same value to cells from B1 to B5
		workSheet("B1:B5").Value = "Range value"

		' Specify range in which we want to write the values
		For i As Integer = 1 To 5
			' Write the Dynamic value in column C
			workSheet("C" & i).Value = "Value: " & i

			' Write the Dynamic value in column D
			workSheet("D" & i).Value = "Value: " & i
		Next i

		' Replace the value in cell D5
		workSheet("D5").Replace("Value: 5", "Replaced Value")

		' Save the updated Excel workbook to a file
		workBook.SaveAs("sample.xlsx")
		Console.WriteLine("Successfully written to Excel File")
	End Sub
End Class

For more detailed information on how to read Excel file data in C#, have a look at this example.

Output

The output of the file is:

The output Excel file

Summary

This article demonstrated how to write data to Excel files in C# using IronXL. IronXL provides the facility to work with existing Excel files without any hassle. It also allows you to create new Excel files and write data to it with easy syntax. IronXL can also be used to read Excel files without the Microsoft Excel application installed. To read data from Excel files you can see this code example page.

IronXL is free for development and can be licensed for commercial use. You can also try the IronXL free trial for commercial use.