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

查克尼特·宾
查克尼特·宾
2019年五月26日
更新 2024年十二月10日
分享:
This article was translated from English: Does it need improvement?
Translated
View the article in English

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


概述

使用 IronXL 打开和写入 Excel 文件

使用易于使用的[IronXL C# 库](/csharp/excel/" target="_blank)打开、编写、保存和自定义 Excel 文件。

从[GitHub 下载示例项目](https://github.com/magedo93/IronSoftware.git" target="_blank)或使用您自己的项目,并按照教程操作。

  1. 从[NuGet](https://www.nuget.org/packages/IronXL.Excel" target="_blank)或DLL下载安装IronXL Excel库

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

  3. 使用直观语法获取单元格值:sheet ["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
$vbLabelText   $csharpLabel

写入并保存 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")
$vbLabelText   $csharpLabel

步骤 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 包管理器控制台安装

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

    Package Manager Console related to 使用 NuGet 为 Visual Studio 项目安装 Excel 库

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

    Install Package Ironxl related to 使用 NuGet 为 Visual Studio 项目安装 Excel 库

    使用 DLL 手动安装

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

PM > Install-Package IronXL.Excel

<hr class="separator">
<h4 class="tutorial-segment-title">教程</h4>

## 2. 基本操作:创建、打开、保存 &num;&num;

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

<p class="list-description">创建 HelloWorld 项目</p>
<p class="list-decimal">2.1.1.打开 Visual Studio</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/open-visual-studio.png" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/open-visual-studio.png" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.2.选择创建新项目</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/choose-create-new-project.png" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/choose-create-new-project.png" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.3.选择控制台应用程序(.NET 框架)</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/choose-console-app.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/choose-console-app.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"> </a>
<p class="list-decimal">2.1.4.给我们的示例命名为 "HelloWorld",然后点击创建</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/give-our-sample-name.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/give-our-sample-name.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.5.现在我们创建了控制台应用程序</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/console-application-created.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/console-application-created.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"></a>
<p class="list-decimal">2.1.6. Add IronXL.Excel => click install</p>
<a rel="nofollow" href="/img/tutorials/csharp-open-write-excel-file/add-ironxl-click-install.jpg" target="_blank"><img src="/img/tutorials/csharp-open-write-excel-file/add-ironxl-click-install.jpg" alt="" class="img-responsive add-shadow img-margin" style="max-width:100%;"> </a>
<p class="list-decimal">2.1.7.添加我们的前几行,读取 Excel 文件中第 1 张工作表的第 1 个单元格,然后打印</p>

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

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
$vbLabelText   $csharpLabel

2.3. 以工作簿打开(CSV, XML, JSON 列表)###

2.3.1.打开 CSV 文件

2.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)
$vbLabelText   $csharpLabel

2.3.3. Open XML File Create an XML file that contains a countries list: the root element “countries”, with children elements “country”, and each country has properties that define the country like code, continent, etc.

<?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

2.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()
$vbLabelText   $csharpLabel

2.3.5. Open JSON List as workbook Create JSON country list

: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" }]
$vbLabelText   $csharpLabel

2.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
$vbLabelText   $csharpLabel

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

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

2.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
$vbLabelText   $csharpLabel

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

: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()
$vbLabelText   $csharpLabel

2.4. 保存和导出 ###

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

2.4.1. Save to “.xlsx” To Save to “.xlsx” use saveAs function

: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")
$vbLabelText   $csharpLabel

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

: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:= "|")
$vbLabelText   $csharpLabel

2.4.3. Save to JSON “.json” To save to Json “.json” use SaveAsJson as follow

: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")
$vbLabelText   $csharpLabel

结果文件应该如下所示

: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"], [""]]
$vbLabelText   $csharpLabel

2.4.4. Save to XML “.xml” To save to xml use SaveAsXml as follow

: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")
$vbLabelText   $csharpLabel

结果应该是这样的

<?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)
$vbLabelText   $csharpLabel

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)
$vbLabelText   $csharpLabel

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)
$vbLabelText   $csharpLabel

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)
$vbLabelText   $csharpLabel

- 我们可以对 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)
$vbLabelText   $csharpLabel

本例在控制台中写入 "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)
$vbLabelText   $csharpLabel

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")
$vbLabelText   $csharpLabel

条件示例3.7 ###

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

3.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")
$vbLabelText   $csharpLabel

7.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()
$vbLabelText   $csharpLabel

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")
$vbLabelText   $csharpLabel

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


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
$vbLabelText   $csharpLabel

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")
$vbLabelText   $csharpLabel

5. 与 Excel 数据库集成

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

我创建了名为“TestDb”的数据库,其中包含一个国家表,该表有两列:Id(整数,标识),CountryName(字符串)

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")
$vbLabelText   $csharpLabel

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()
$vbLabelText   $csharpLabel

进一步阅读

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

我们的API 参考包含对WorkBook类的具体引用。


教程快速访问

Brand Visual Studio related to 教程快速访问

Download this Tutorial as C# Source Code

The full free C# for Excel Source Code for this tutorial is available to download as a zipped Visual Studio 2017 project file.

Download

在 GitHub 上探索此教程

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

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

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

IronXL 应用程序接口参考

Explore the IronXL 应用程序接口参考, outlining the details of all of IronXL’s features, namespaces, classes, methods fields and enums.

查看 API 参考
查克尼特·宾
软件工程师
Chaknith 负责 IronXL 和 IronBarcode 的工作。他在 C# 和 .NET 方面拥有深厚的专业知识,帮助改进软件并支持客户。他从用户互动中获得的洞察力,有助于提升产品、文档和整体体验。