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

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

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

立即在您的專案中使用IronXL，並享受免費試用。

第一步：

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

使用滑鼠 -> 右鍵點擊項目名稱 -> 選擇管理 NuGet 套件
從「瀏覽」標籤 -> 搜尋 IronXL.Excel -> 安裝
我們完成了

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

從工具 -> NuGet 套件管理器 -> 套件管理器控制台
運行命令 -> Install-Package IronXL.Excel -Version 2019.5.2

手動使用DLL安裝

您也可以選擇手動安裝 DLL 到您的項目或全域程序集快取。請提供內容以進行翻譯。

PM > Install-Package IronXL.Excel 請提供內容以進行翻譯。

操作教程

2. 基本操作：建立、開啟、儲存

2.1. 範例專案：HelloWorld 控制台應用程式

創建一個HelloWorld專案

.1.1. 打開 Visual Studio

.1.2. 選擇建立新專案

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

將我們的範例命名為“HelloWorld”，然後點擊創建。

.1.5. 現在我們已經建立了控制台應用程序

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

.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

您的代碼片段應該看起來像這樣

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

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

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

: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 文件中。

請使用以下代碼

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