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

This article was translated from English: Does it need improvement?
Translated
View the article in English

伊莱贾·威廉姆斯

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


概述

使用 IronXL 打开和写入 Excel 文件

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

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

  1. 安装 IronXL Excel 库 NuGet 或者DLL下载

  2. 使用 WorkBook.Load 方法读取任何 XLS、XLSX 或 CSV 文档。

  3. 使用直观的语法获取单元格值: 片材 ["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,并享受免费试用。

第一步:
green arrow pointer


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 包管理器:

  1. 使用鼠标 -> 右键单击项目名称 -> 选择管理 NuGet 包

    Select Manage Nuget Package related to 使用 NuGet 为 Visual Studio 项目安装 Excel 库

  2. 从浏览选项卡 -> 搜索 IronXL.Excel -> 安装

    Search For Ironxl related to 使用 NuGet 为 Visual Studio 项目安装 Excel 库

  3. 我们完成了

    And We Are Done related to 使用 NuGet 为 Visual Studio 项目安装 Excel 库

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

  1. 从工具 -> NuGet 包管理器 -> 包管理器控制台

    Package Manager Console related to 使用 NuGet 包管理器控制台安装

  2. 运行命令 -> 安装软件包 IronXL.Excel -Version 2019.5.2

    Install Package Ironxl related to 使用 NuGet 包管理器控制台安装

使用 DLL 手动安装

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


PM > Install-Package IronXL.Excel

教程

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

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

创建 HelloWorld 项目

.1.1.打开 Visual Studio

Open Visual Studio related to 2.1.示例项目:HelloWorld 控制台应用程序

.1.2.选择创建新项目

Choose Create New Project related to 2.1.示例项目:HelloWorld 控制台应用程序

.1.3.选择控制台应用程序(.NET 框架)

Choose Console App related to 2.1.示例项目:HelloWorld 控制台应用程序

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

Give Our Sample Name related to 2.1.示例项目:HelloWorld 控制台应用程序

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

Console Application Created related to 2.1.示例项目:HelloWorld 控制台应用程序

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

Add Ironxl Click Install related to 2.1.示例项目:HelloWorld 控制台应用程序

.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

Code Snippet related to 2.3.打开(CSV、XML、JSON 列表)为工作簿

您的代码片段应如下所示

: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 的国家模型

Create Country Model related to 2.3.打开(CSV、XML、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 转换为国家模型列表

Add Newtonsoft Library To Convert Json related to 2.3.打开(CSV、XML、JSON 列表)为工作簿

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

Convert List To Dataset related to 2.3.打开(CSV、XML、JSON 列表)为工作簿

然后添加以下代码段

: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",然后手动添加了这个数字列表

Sum Example related to 3.1.总和示例

: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 文件中添加了这一列

Trim Example related to 3.8.修剪示例

并使用此代码

: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#

进一步阅读

要了解更多关于使用IronXL的信息,您可以查看本节中的其他教程,以及我们主页上的示例,这些通常足以帮助大多数开发者开始使用。

我们的API文档 包含对 工作簿 类。


教程快速访问

Brand Visual Studio related to 教程快速访问

下载本教程的 C# 源代码

本教程的完整免费 C# for Excel 源代码可作为压缩的 Visual Studio 2017 项目文件下载。

下载

在 GitHub 上探索此教程

GitHub 上有该项目的 C# 和 VB.NET 源代码。

使用此代码,只需几分钟就能轻松上手并运行。该项目保存为 Microsoft Visual Studio 2017 项目,但兼容任何 .NET IDE。

如何在 GitHub 上用 C# 打开和写入 Excel 文件
Github Icon related to 教程快速访问
Documentation related to 教程快速访问

IronXL 应用程序接口参考

探索 IronXL 的 API 参考,其中概述了 IronXL 的所有功能、命名空间、类、方法字段和枚举的详细信息。

查看应用程序接口参考
.NET Excel库可以彻底改变将数据引入和导出Web应用程序和业务系统的方式。

伊莱贾·威廉姆斯

产品开发工程师

埃利亚是一名开发和测试团队的工程师,负责一个大型企业财务报告系统。埃利亚是IronXL库的早期采用者,将其用在他们的Excel报告界面的核心。