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 文件,簡單易用 IronXL C# 函式庫。

下載 a 來自 GitHub 的範例專案 或使用您自己的,並按照教程操作。

  1. 從 NuGet 安裝 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. 免費安裝 IronXL C# 程式庫

C# NuGet 程式庫用于 Excel

安裝與 NuGet

Install-Package IronXL.Excel
Java PDF JAR

下載 DLL

下載DLL

手動安裝到您的項目中

C# NuGet 程式庫用于 Excel

安裝與 NuGet

Install-Package IronXL.Excel
Java PDF JAR

下載 DLL

下載DLL

手動安裝到您的項目中

立即開始在您的專案中使用IronPDF,並享受免費試用。

第一步:
green arrow pointer

查看 IronXLNuget 方便快速安裝和部署。擁有超過 800 萬次下載,它正在使用 C# 改造 Excel。

C# NuGet 程式庫用于 Excel nuget.org/packages/IronXL.Excel/
Install-Package IronXL.Excel

請考慮安裝 IronXL DLL 直接下載並手動安裝到您的專案或GAC表單: IronXL.zip

Dll Img related to 1. 免費安裝 IronXL C# 程式庫

手動安裝到您的項目中

下載DLL

IronXL.Excel 提供了一個靈活且功能強大的庫,用於在 .NET 中打開、讀取、編輯和保存 Excel 文件。 它可以安裝並用於所有的 .NET 項目類型,如 Windows 應用程序、ASP.NET MVC 和 .NET Core 應用程序。

使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

首先要安裝 IronXL.Excel。 要在項目中添加 IronXL.Excel 庫,我們有兩種方法:NuGet 包管理器或 NuGet 包管理器控制台。

要使用 NuGet 將 IronXL.Excel 程式庫添加到我們的項目中,我們可以使用可視化界面,即 NuGet 包管理器:

  1. 使用滑鼠 -> 右鍵點擊項目名稱 -> 選擇管理 NuGet 套件

    Select Manage Nuget Package related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

  2. 從「瀏覽」標籤 -> 搜尋 IronXL.Excel -> 安裝

    Search For Ironxl related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

  3. 我們完成了

    And We Are Done related to 使用 NuGet 將 Excel 庫安裝到您的 Visual Studio 專案中

使用 NuGet 套件管理器控制台進行安裝

  1. 從工具 -> NuGet 套件管理器 -> 套件管理器控制台

    Package Manager Console related to 使用 NuGet 套件管理器控制台進行安裝

  2. 運行命令 -> Install-Package 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 控制台應用程式

選擇控制台應用程式 (.NET 框架)

Choose Console App related to 2.1. 範例專案:HelloWorld 控制台應用程式

將我們的範例命名為“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 文件中第一張工作表的第一個單元格,並打印。

: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 並傳遞給它兩個參數:第一個參數是文件名和路徑,第二個參數是分隔符。例如 (“,”或“ ” 或 “:”)

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

- 我們可以將變換函數應用於最大值函數的結果:

: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. 如果條件範例

使用同一個檔案,我們可以使用 Formula 屬性來設置或獲取儲存格的公式:

.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 文件:“Sheet1”、“Sheet2”

到目前為止,我們一直使用 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, identity),國家名稱(字串)

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 資料庫的 Country 表格中

: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 API 參考文檔

查看 IronXL 的 API 參考文件,詳述 IronXL 的所有功能、命名空間、類別、方法字段和列舉。

查看 API 參考文件
.NET Excel庫可革新地將資料導入和導出到網頁應用程式和商業系統中。

伊萊賈·威廉姆斯

產品開發工程師

Elijah 是一個負責大型公司財務報告系統的開發和測試團隊的工程師。Elijah 一直是 IronXL 庫的早期使用者,並將其用於他們的 Excel 報告接口的核心。