C# 写入 Excel [不使用 Interop] 代码示例教程

Translated

View the article in English

请按照步骤示例了解如何使用C#创建、打开和保存Excel文件，并执行基本操作，如获取总和、平均值、计数等。 IronXL.Excel 是一个独立的 .NET 软件库，用于读取多种电子表格格式。它不需要微软Excel安装，也不依赖 Interop。

如何用 C# 编写 Excel 文件

下载 Write Excel C# 库
创建并打开新的 CSV 或 XML Excel 文件作为 Excel 工作簿
保存并导出 Excel 工作簿
在多个 Excel 工作表中应用高级操作
与 Excel 数据库整合

概述

使用 IronXL 打开和写入 Excel 文件

使用简单易用的 Excel 软件打开、编写、保存和自定义 Excel 文件。 IronXL C# 库

下载来自 GitHub 的示例项目或使用您自己的，并按照教程操作。

安装 IronXL Excel 库 NuGet 或者DLL下载
使用 WorkBook.Load 方法读取任何 XLS、XLSX 或 CSV 文档。
使用直观的语法获取单元格值： 片材 ["A11"].DecimalValue
在本教程中，我们将指导您：
- 安装 IronXL.Excel：如何将 IronXL.Excel 安装到现有项目中。
- 基本操作：使用Excel创建或打开工作簿、选择工作表、选择单元格以及保存工作簿的基本操作步骤。
- 高级工作表操作：如何利用不同的操作功能，例如添加页眉或页脚、数学运算文件以及其他功能。
  打开 Excel 文件：快速代码

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-1.cs

using IronXL;

WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
IronXL.Range range = workSheet["A2:A8"];
decimal total = 0;

// iterate over range of cells
foreach (var cell in range)
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value);
    if (cell.IsNumeric)
    {
        // Get decimal value to avoid floating numbers precision issue
        total += cell.DecimalValue;
    }
}

// Check formula evaluation
if (workSheet["A11"].DecimalValue == total)
{
    Console.WriteLine("Basic Test Passed");
}

Imports IronXL

Private workBook As WorkBook = WorkBook.Load("test.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private range As IronXL.Range = workSheet("A2:A8")
Private total As Decimal = 0

' iterate over range of cells
For Each cell In range
	Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value)
	If cell.IsNumeric Then
		' Get decimal value to avoid floating numbers precision issue
		total += cell.DecimalValue
	End If
Next cell

' Check formula evaluation
If workSheet("A11").DecimalValue = total Then
	Console.WriteLine("Basic Test Passed")
End If

VB C#

写入并保存 Excel 文件中的更改：快速代码

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-2.cs

workSheet["B1"].Value = 11.54;

// Save Changes
workBook.SaveAs("test.xlsx");

workSheet("B1").Value = 11.54

' Save Changes
workBook.SaveAs("test.xlsx")

VB C#

步骤 1

1. 免费安装 IronXL C# 库

立即在您的项目中开始使用IronXL，并享受免费试用。

第一步：

IronXL.Excel 为 .NET 提供了一个灵活且强大的库，用于打开、阅读、编辑和保存 Excel 文件。它可以安装并用于所有的.NET项目类型，如Windows应用程序、ASP.NET MVC和.NET Core应用程序。

使用 NuGet 为 Visual Studio 项目安装 Excel 库

首先要安装 IronXL.Excel。要将 IronXL.Excel 库添加到项目中，我们有两种方法：NuGet 包管理器或 NuGet 包管理器控制台。

要使用 NuGet 将 IronXL.Excel 库添加到我们的项目中，我们可以使用可视化界面，即 NuGet 包管理器：

使用鼠标 -> 右键单击项目名称 -> 选择管理 NuGet 包
从浏览选项卡 -> 搜索 IronXL.Excel -> 安装
我们完成了

使用 NuGet 包管理器控制台安装

从工具 -> NuGet 包管理器 -> 包管理器控制台
运行命令 -> 安装软件包 IronXL.Excel -Version 2019.5.2

使用 DLL 手动安装

您也可以选择手动安装 DLL 到项目或全局程序集缓存中。


PM > Install-Package IronXL.Excel

教程

2.基本操作：创建、打开、保存

2.1.示例项目：HelloWorld 控制台应用程序

创建 HelloWorld 项目

.1.1.打开 Visual Studio

.1.2.选择创建新项目

.1.3.选择控制台应用程序（.NET 框架）

.1.4.给我们的示例命名为 "HelloWorld"，然后点击创建

.1.5.现在我们创建了控制台应用程序

.1.6.添加 IronXL.Excel => 点击安装

.1.7.添加我们的前几行，读取 Excel 文件中第 1 张工作表的第 1 个单元格，然后打印

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-3.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)

VB C#

2.2.创建新的 Excel 文件

使用 IronXL 创建新 Excel 文件

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-4.cs

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";
WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed

VB C#

2.3.打开（CSV、XML、JSON 列表）为工作簿

.3.1.打开 CSV 文件

.3.2 新建一个文本文件，添加姓名和年龄列表（见示例），然后保存为 CSVList.csv

您的代码片段应如下所示

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-5.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\CSVList.csv");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\CSVList.csv")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)

VB C#

2.3.3.打开 XML 文件创建一个包含国家列表的 XML 文件：根元素 "countries"，子元素 "country"，每个国家都有定义国家的属性，如代码、大陆等。

<?xml version="1.0" encoding="utf-8"?>
<countries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <country code="ae" handle="united-arab-emirates" continent="asia" iso="784">United Arab Emirates</country>
        <country code="gb" handle="united-kingdom" continent="europe" alt="England Scotland Wales GB UK Great Britain Britain Northern" boost="3" iso="826">United Kingdom</country>
        <country code="us" handle="united-states" continent="north america" alt="US America USA" boost="2" iso="840">United States</country>
        <country code="um" handle="united-states-minor-outlying-islands" continent="north america" iso="581">United States Minor Outlying Islands</country>
</countries>

<?xml version="1.0" encoding="utf-8"?>
<countries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <country code="ae" handle="united-arab-emirates" continent="asia" iso="784">United Arab Emirates</country>
        <country code="gb" handle="united-kingdom" continent="europe" alt="England Scotland Wales GB UK Great Britain Britain Northern" boost="3" iso="826">United Kingdom</country>
        <country code="us" handle="united-states" continent="north america" alt="US America USA" boost="2" iso="840">United States</country>
        <country code="um" handle="united-states-minor-outlying-islands" continent="north america" iso="581">United States Minor Outlying Islands</country>
</countries>

HTML

.3.4.复制以下代码段，将 XML 作为工作簿打开

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-7.cs

DataSet xmldataset = new DataSet();
xmldataset.ReadXml($@"{Directory.GetCurrentDirectory()}\Files\CountryList.xml");
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();

Dim xmldataset As New DataSet()
xmldataset.ReadXml($"{Directory.GetCurrentDirectory()}\Files\CountryList.xml")
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()

VB C#

2.3.5.以工作簿形式打开 JSON 列表创建 JSON 国家列表

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-8.cs

[
    {
        "name": "United Arab Emirates",
        "code": "AE"
    },
    {
        "name": "United Kingdom",
        "code": "GB"
    },
    {
        "name": "United States",
        "code": "US"
    },
    {
        "name": "United States Minor Outlying Islands",
        "code": "UM"
    }
]

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[{ "name": "United Arab Emirates", "code": "AE" }, { "name": "United Kingdom", "code": "GB" }, { "name": "United States", "code": "US" }, { "name": "United States Minor Outlying Islands", "code": "UM" }]

VB C#

.3.6.创建映射到 JSON 的国家模型

以下是类代码片段

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-9.cs

public class CountryModel
    {
        public string name { get; set; }
        public string code { get; set; }
    }

Public Class CountryModel
		Public Property name() As String
		Public Property code() As String
End Class

VB C#

.3.8.添加 Newtonsoft 库，将 JSON 转换为国家模型列表

.3.9 要将列表转换为数据集，我们必须为列表创建一个新扩展。添加名为 "ListConvertExtension "的扩展类

然后添加以下代码段

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-10.cs

public static class ListConvertExtension
    {
        public static DataSet ToDataSet<T>(this IList<T> list)
        {
            Type elementType = typeof(T);
            DataSet ds = new DataSet();
            DataTable t = new DataTable();
            ds.Tables.Add(t);
            //add a column to table for each public property on T
            foreach (var propInfo in elementType.GetProperties())
            {
                Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;
                t.Columns.Add(propInfo.Name, ColType);
            }
            //go through each property on T and add each value to the table
            foreach (T item in list)
            {
                DataRow row = t.NewRow();
                foreach (var propInfo in elementType.GetProperties())
                {
                    row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;
                }
                t.Rows.Add(row);
            }
            return ds;
        }
    }

Public Module ListConvertExtension
		<System.Runtime.CompilerServices.Extension> _
		Public Function ToDataSet(Of T)(ByVal list As IList(Of T)) As DataSet
			Dim elementType As Type = GetType(T)
			Dim ds As New DataSet()
'INSTANT VB NOTE: The variable t was renamed since Visual Basic does not allow local variables with the same name as method-level generic type parameters:
			Dim t_Conflict As New DataTable()
			ds.Tables.Add(t_Conflict)
			'add a column to table for each public property on T
			For Each propInfo In elementType.GetProperties()
				Dim ColType As Type = If(Nullable.GetUnderlyingType(propInfo.PropertyType), propInfo.PropertyType)
				t_Conflict.Columns.Add(propInfo.Name, ColType)
			Next propInfo
			'go through each property on T and add each value to the table
			For Each item As T In list
				Dim row As DataRow = t_Conflict.NewRow()
				For Each propInfo In elementType.GetProperties()
					row(propInfo.Name) = If(propInfo.GetValue(item, Nothing), DBNull.Value)
				Next propInfo
				t_Conflict.Rows.Add(row)
			Next item
			Return ds
		End Function
End Module

VB C#

最后将该数据集加载到工作簿中

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-11.cs

StreamReader jsonFile = new StreamReader($@"{Directory.GetCurrentDirectory()}\Files\CountriesList.json");
var countryList = Newtonsoft.Json.JsonConvert.DeserializeObject<CountryModel[]>(jsonFile.ReadToEnd());
var xmldataset = countryList.ToDataSet();
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();

Dim jsonFile As New StreamReader($"{Directory.GetCurrentDirectory()}\Files\CountriesList.json")
Dim countryList = Newtonsoft.Json.JsonConvert.DeserializeObject(Of CountryModel())(jsonFile.ReadToEnd())
Dim xmldataset = countryList.ToDataSet()
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()

VB C#

2.4.保存并导出

我们可以使用以下命令之一将 Excel 文件保存或导出为多种文件格式，如".xlsx"、".csv"、".html"。

2.4.1.保存为".xlsx 要保存为".xlsx"，请使用 saveAs 函数

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-12.cs

WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";

WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;

workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");

Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"

Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed

workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")

VB C#

2.4.2.保存为 csv ".csv" 文件要保存为".csv"，我们可以使用 SaveAsCsv，并向其传递 2 个参数，第一个参数是文件名和路径，第二个参数是分隔符，如 (“,” 或者 “ ” 或 “:”)

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-13.cs

workBook.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter: "|");

workBook.SaveAsCsv($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter:= "|")

VB C#

2.4.3.保存为 JSON ".json" 格式要保存为 Json ".json"，请使用 SaveAsJson，如下所示

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-14.cs

workBook.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");

workBook.SaveAsJson($"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json")

VB C#

结果文件应如下所示

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-15.cs

[
    [
        "Hello World"
    ],
    [
        ""
    ]
]

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[["Hello World"], [""]]

VB C#

2.4.4.保存为 XML".xml "格式要保存为 xml 格式，请使用 SaveAsXml，如下所示

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-16.cs

workBook.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");

workBook.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")

VB C#

结果应该是这样的

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>

HTML

3. 高级操作：求和、平均、计数等。

让我们应用常见的Excel函数，如SUM, AVG, Count，并查看每个代码片段。

3.1.总和示例

让我们求出这个列表的总和。我创建了一个 Excel 文件并将其命名为 "Sum.xlsx"，然后手动添加了这个数字列表

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-18.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal sum = workSheet["A2:A4"].Sum();
Console.WriteLine(sum);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim sum As Decimal = workSheet("A2:A4").Sum()
Console.WriteLine(sum)

VB C#

3.2.平均值示例

使用同一文件，我们可以得到平均值：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-19.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal avg = workSheet["A2:A4"].Avg();
Console.WriteLine(avg);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim avg As Decimal = workSheet("A2:A4").Avg()
Console.WriteLine(avg)

VB C#

3.3.计数示例

使用同一个文件，我们还可以获得序列中元素的数量：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-20.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal count = workSheet["A2:A4"].Count();
Console.WriteLine(count);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim count As Decimal = workSheet("A2:A4").Count()
Console.WriteLine(count)

VB C#

3.4.最大示例

使用同一个文件，我们可以得到单元格区域的最大值：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-21.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal max = workSheet["A2:A4"].Max();
Console.WriteLine(max);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max As Decimal = workSheet("A2:A4").Max()
Console.WriteLine(max)

VB C#

- 我们可以对 max 函数的结果应用变换函数：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-22.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
bool max2 = workSheet["A1:A4"].Max(c => c.IsFormula);
Console.WriteLine(max2);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max2 As Boolean = workSheet("A1:A4").Max(Function(c) c.IsFormula)
Console.WriteLine(max2)

VB C#

本例在控制台中写入 "false"。

3.5.最小示例

使用同一个文件，我们可以得到单元格区域的最小值：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-23.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal min = workSheet["A1:A4"].Min();
Console.WriteLine(min);

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim min As Decimal = workSheet("A1:A4").Min()
Console.WriteLine(min)

VB C#

3.6.阶梯单元格示例

使用同一个文件，我们可以按升序或降序排列单元格：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-24.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
workSheet["A1:A4"].SortAscending();
// workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx");

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
workSheet("A1:A4").SortAscending()
' workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx")

VB C#

3.7.如果条件示例

使用同一个文件，我们可以使用公式属性来设置或获取单元格的公式：

.7.1.保存为 XML".xml "格式

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-25.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["B1:B4"])
{
    cell.Formula = "=IF(A" + i + ">=20,\" Pass\" ,\" Fail\" )";
    i++;
}
workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("B1:B4")
	cell.Formula = "=IF(A" & i & ">=20,"" Pass"" ,"" Fail"" )"
	i += 1
Next cell
workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")

VB C#

.2.使用上一个示例中生成的文件，我们可以得到单元格的公式：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-26.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
foreach (var cell in workSheet["B1:B4"])
{
    Console.WriteLine(cell.Formula);
}
Console.ReadKey();

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
For Each cell In workSheet("B1:B4")
	Console.WriteLine(cell.Formula)
Next cell
Console.ReadKey()

VB C#

3.8.修剪示例

为了应用修剪功能（消除单元格中所有多余的空格），我在 sum.xlsx 文件中添加了这一列

并使用此代码

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-27.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["f1:f4"])
{
    cell.Formula = "=trim(D" + i + ")";
    i++;
}
workBook.SaveAs("editedFile.xlsx");

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("f1:f4")
	cell.Formula = "=trim(D" & i & ")"
	i += 1
Next cell
workBook.SaveAs("editedFile.xlsx")

VB C#

因此，您可以以同样的方式应用公式。

4. 使用多工作表的工作簿

我们将介绍如何处理具有多个工作表的工作簿。

4.1.从同一工作簿中的多个工作表读取数据

我创建了一个 xlsx 文件，其中包含两张工作表："工作表 1"、"工作表 2

到目前为止，我们使用 WorkSheets.First() 来处理第一张工作表。在本例中，我们将指定工作表名称并使用它

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-28.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.GetWorkSheet("Sheet2");
var range = workSheet["A2:D2"];
foreach (var cell in range)
{
    Console.WriteLine(cell.Text);
}

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.GetWorkSheet("Sheet2")
Dim range = workSheet("A2:D2")
For Each cell In range
	Console.WriteLine(cell.Text)
Next cell

VB C#

4.2.为工作簿添加新工作表

我们还可以在工作簿中添加新工作表：

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-29.cs

WorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");
workSheet["A1"].Value = "Hello World";
workBook.SaveAs(@"F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx");

Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
workSheet("A1").Value = "Hello World"
workBook.SaveAs("F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx")

VB C#

5. 与Excel数据库集成

让我们看看如何将数据导出/导入到数据库。

我创建了一个包含一个有两个列的国家表的 "TestDb" 数据库：Id(int，身份), 国名(字符串)

5.1.将数据库中的数据填入 Excel 表格

在此，我们将创建一个新工作表，并将国家表中的数据填入其中

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-30.cs

TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.CreateWorkSheet("FromDb");
List<Country> countryList = dbContext.Countries.ToList();
sheet.SetCellValue(0, 0, "Id");
sheet.SetCellValue(0, 1, "Country Name");
int row = 1;
foreach (var item in countryList)
{
    sheet.SetCellValue(row, 0, item.id);
    sheet.SetCellValue(row, 1, item.CountryName);
    row++;
}
workbook.SaveAs("FilledFile.xlsx");

Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.CreateWorkSheet("FromDb")
Dim countryList As List(Of Country) = dbContext.Countries.ToList()
sheet.SetCellValue(0, 0, "Id")
sheet.SetCellValue(0, 1, "Country Name")
Dim row As Integer = 1
For Each item In countryList
	sheet.SetCellValue(row, 0, item.id)
	sheet.SetCellValue(row, 1, item.CountryName)
	row += 1
Next item
workbook.SaveAs("FilledFile.xlsx")

VB C#

5.2.用 Excel 表中的数据填充数据库

在 TestDb 数据库的国家表中插入数据

:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-31.cs

TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet3");
System.Data.DataTable dataTable = sheet.ToDataTable(true);
foreach (DataRow row in dataTable.Rows)
{
    Country c = new Country();
    c.CountryName = row[1].ToString();
    dbContext.Countries.Add(c);
}
dbContext.SaveChanges();

Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.GetWorkSheet("Sheet3")
Dim dataTable As System.Data.DataTable = sheet.ToDataTable(True)
For Each row As DataRow In dataTable.Rows
	Dim c As New Country()
	c.CountryName = row(1).ToString()
	dbContext.Countries.Add(c)
Next row
dbContext.SaveChanges()

VB C#